[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select wo.operation_seq_num,
wo.operation_sequence_id
from wip_operations wo
where wo.organization_id = p_org_id
and wo.wip_entity_id = p_wip_entity_id
and exists (select fad.pk1_value
from fnd_attached_documents fad
where fad.pk1_value = to_char(wo.operation_sequence_id)
and fad.entity_name = 'BOM_OPERATION_SEQUENCES');
select common_routing_sequence_id,
serialization_start_op
into l_routingSeqID,
x_serStartOp
from bom_operational_routings
where organization_id = p_orgID
and assembly_item_id = p_itemID
and nvl(alternate_routing_designator, '@@@^@@@') = nvl(p_altRouting, '@@@^@@@')
and nvl(cfm_routing_flag, 2) = 2;
wip_update_setup_resources.delete_setup_resources_pub(
p_wip_entity_id => p_wipEntityID,
p_organization_id => p_orgID);
delete from wip_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID;
delete from wip_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID;
delete from wip_sub_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID;
fnd_attached_documents2_pkg.delete_attachments(
x_entity_name => 'WIP_DISCRETE_OPERATIONS',
x_pk1_value => to_char(p_wipEntityID),
x_pk3_value => to_char(p_orgID),
x_delete_document_flag => 'Y');
insert into wip_operations
(wip_entity_id,
operation_seq_num,
organization_id,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
operation_sequence_id,
department_id,
scheduled_quantity,
quantity_in_queue,
quantity_running,
quantity_waiting_to_move,
quantity_rejected,
quantity_scrapped,
quantity_completed,
cumulative_scrap_quantity,
count_point_type,
backflush_flag,
minimum_transfer_quantity,
first_unit_start_date,
first_unit_completion_date,
last_unit_start_date,
last_unit_completion_date,
standard_operation_id,
description,
long_description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
check_skill)
select p_wipEntityID,
bos.operation_seq_num,
p_orgID,
p_repSchedID,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
min(bos.operation_sequence_id),
bos.department_id,
round(p_qty, 6),
0, 0, 0, 0, 0, 0, 0,
bos.count_point_type,
bos.backflush_flag,
nvl(bos.minimum_transfer_quantity, 0),
p_startDate, p_endDate,
p_startDate, p_endDate,
bos.standard_operation_id,
bos.operation_description,
bos.long_description,
bos.attribute_category,
bos.attribute1,
bos.attribute2,
bos.attribute3,
bos.attribute4,
bos.attribute5,
bos.attribute6,
bos.attribute7,
bos.attribute8,
bos.attribute9,
bos.attribute10,
bos.attribute11,
bos.attribute12,
bos.attribute13,
bos.attribute14,
bos.attribute15,
nvl(bos.check_skill,2)
from bom_operation_sequences bos
where bos.routing_sequence_id = l_routingSeqID
and nvl(bos.operation_type, 1) = 1
and bos.effectivity_date <= p_routingRevDate
and nvl(bos.disable_date, p_routingRevDate+1) >= p_routingRevDate
and ( bos.implementation_date is not null
or exists (select 1
from eng_revised_items eng
where eng.change_notice = bos.change_notice
and eng.organization_id = p_orgID
and eng.routing_sequence_id = l_routingSeqID
and ((eng.status_type in (4, 7) and l_excludeECO = 1) or
(eng.status_type in (1, 4, 7) and l_excludeECO = 2))))
and not exists (select 1
from bom_operation_sequences bos2
where bos2.routing_sequence_id = bos.routing_sequence_id
and bos2.effectivity_date <= p_routingRevDate
and bos2.operation_seq_num = bos.operation_seq_num
and exists
(select 1
from eng_revised_items eng
where eng.change_notice = bos2.change_notice
and eng.organization_id = p_orgID
and eng.routing_sequence_id = l_routingSeqID
and ((eng.status_type in (4, 7) and l_excludeECO = 1) or
(eng.status_type in (1, 4, 7) and l_excludeECO = 2)))
and bos2.acd_type = 3)
group by bos.operation_seq_num,
bos.department_id, bos.count_point_type, bos.backflush_flag, bos.minimum_transfer_quantity,
p_orgID, p_wipEntityID, p_repSchedID, p_qty, p_startDate, p_endDate, sysdate, fnd_global.user_id,
fnd_global.login_id, fnd_global.conc_request_id, fnd_global.prog_appl_id,
fnd_global.conc_program_id, bos.standard_operation_id, bos.operation_description,
bos.long_description, bos.attribute_category, bos.attribute1, bos.attribute2,
bos.attribute3, bos.attribute4, bos.attribute5, bos.attribute6, bos.attribute7, bos.attribute8,
bos.attribute9, bos.attribute10, bos.attribute11, bos.attribute12, bos.attribute13,
bos.attribute14, bos.attribute15, bos.check_skill;
update wip_operations wo
set previous_operation_seq_num = (select max(operation_seq_num)
from wip_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num < wo.operation_seq_num),
next_operation_seq_num = (select min(operation_seq_num)
from wip_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num > wo.operation_seq_num)
where wo.wip_entity_id = p_wipEntityID
and wo.organization_id = p_orgID;
insert into wip_operation_resources
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_id,
scheduled_flag,
assigned_units,
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
start_date,
completion_date,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
principle_flag,
setup_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
select p_wipEntityID,
bos.operation_seq_num,
bor.resource_seq_num,
p_orgID,
p_repSchedID,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
bor.resource_id,
br.unit_of_measure,
bor.basis_type,
bor.usage_rate_or_amount,
bor.activity_id,
bor.schedule_flag,
bor.assigned_units,
bor.autocharge_type,
bor.standard_rate_flag,
0, 0,
p_startDate,
p_endDate,
bor.schedule_seq_num,
bor.substitute_group_num,
0,
bor.principle_flag,
bor.setup_id,
bor.attribute_category,
bor.attribute1,
bor.attribute2,
bor.attribute3,
bor.attribute4,
bor.attribute5,
bor.attribute6,
bor.attribute7,
bor.attribute8,
bor.attribute9,
bor.attribute10,
bor.attribute11,
bor.attribute12,
bor.attribute13,
bor.attribute14,
bor.attribute15
from bom_operation_sequences bos,
bom_operation_resources bor,
bom_resources br
where bos.routing_sequence_id = l_routingSeqID
and bos.effectivity_date <= p_routingRevDate
and nvl(bos.disable_date, p_routingRevDate+1) >= p_routingRevDate
and bos.operation_sequence_id = bor.operation_sequence_id
and bor.resource_id = br.resource_id
and nvl(bor.acd_type, 0) <> 3
and bos.effectivity_date =
(select max(effectivity_date)
from bom_operation_sequences bos2,
bom_operation_resources bor2
where bos2.routing_sequence_id = l_routingSeqID
and bos2.operation_sequence_id = bor2.operation_sequence_id
and bos2.operation_seq_num = bos.operation_seq_num
and bor2.resource_seq_num = bor.resource_seq_num
and nvl(bos2.operation_type, 1) = 1
and bos2.effectivity_date <= p_routingRevDate
and ( bos2.implementation_date is not null
or exists (select 1
from eng_revised_items eng
where eng.change_notice = bos2.change_notice
and eng.organization_id = p_orgID
and eng.routing_sequence_id = l_routingSeqID
and ((eng.status_type in (4, 7) and l_excludeECO = 1) or
(eng.status_type in (1, 4, 7) and l_excludeECO = 2)))));
select max(resource_seq_num)
into l_maxSeq
from wip_operation_resources
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID;
insert into wip_sub_operation_resources
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_id,
scheduled_flag,
assigned_units,
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
start_date,
completion_date,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
principle_flag,
setup_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
select wo.wip_entity_id,
wo.operation_seq_num,
l_maxSeq + ROWNUM,
wo.organization_id,
wo.repetitive_schedule_id,
wo.last_update_date,
wo.last_updated_by,
wo.creation_date,
wo.created_by,
wo.last_update_login,
wo.request_id,
wo.program_application_id,
wo.program_id,
wo.program_update_date,
bsor.resource_id,
br.unit_of_measure,
bsor.basis_type,
bsor.usage_rate_or_amount,
bsor.activity_id,
bsor.schedule_flag,
bsor.assigned_units,
bsor.autocharge_type,
bsor.standard_rate_flag,
0, 0,
wo.first_unit_start_date,
wo.last_unit_completion_date,
bsor.schedule_seq_num,
bsor.substitute_group_num,
bsor.replacement_group_num,
bsor.principle_flag,
bsor.setup_id,
bsor.attribute_category,
bsor.attribute1,
bsor.attribute2,
bsor.attribute3,
bsor.attribute4,
bsor.attribute5,
bsor.attribute6,
bsor.attribute7,
bsor.attribute8,
bsor.attribute9,
bsor.attribute10,
bsor.attribute11,
bsor.attribute12,
bsor.attribute13,
bsor.attribute14,
bsor.attribute15
from bom_resources br,
bom_sub_operation_resources bsor,
wip_operations wo
where wo.organization_id = p_orgID
and wo.wip_entity_id = p_wipEntityID
and wo.operation_sequence_id = bsor.operation_sequence_id
and bsor.resource_id = br.resource_id
and nvl(bsor.acd_type, 0) <> 3;
x_last_update_login => fnd_global.login_id,
x_program_application_id => fnd_global.prog_appl_id,
x_program_id => fnd_global.conc_program_id,
x_request_id => fnd_global.conc_request_id);
DELETE FROM WIP_OPERATION_COMPETENCIES
WHERE WIP_ENTITY_ID = p_wipEntityID
AND ORGANIZATION_ID = p_orgID;
INSERT INTO WIP_OPERATION_COMPETENCIES
(LEVEL_ID, ORGANIZATION_ID,
WIP_ENTITY_ID, OPERATION_SEQ_NUM, OPERATION_SEQUENCE_ID,
STANDARD_OPERATION_ID, COMPETENCE_ID, RATING_LEVEL_ID,
QUALIFICATION_TYPE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE)
SELECT
3, WO.ORGANIZATION_ID,
WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, BOS.OPERATION_SEQUENCE_ID,
BOS.STANDARD_OPERATION_ID, BOS.COMPETENCE_ID, BOS.RATING_LEVEL_ID,
BOS.QUALIFICATION_TYPE_ID, WO.LAST_UPDATE_DATE, WO.LAST_UPDATED_BY,
WO.LAST_UPDATE_LOGIN, WO.CREATED_BY, WO.CREATION_DATE
FROM BOM_OPERATION_SKILLS BOS,
WIP_OPERATIONS WO,
WIP_ENTITIES WE
WHERE
WE.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WE.ENTITY_TYPE = 1
AND WO.ORGANIZATION_ID = p_orgID
AND WO.WIP_ENTITY_ID = p_wipEntityID
AND WO.ORGANIZATION_ID = BOS.ORGANIZATION_ID
AND BOS.OPERATION_SEQUENCE_ID = WO.OPERATION_SEQUENCE_ID
AND BOS.LEVEL_ID = 2;
select inventory_item_id,
-1*operation_seq_num operation_seq_num
from wip_requirement_operations
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID
and nvl(repetitive_schedule_id, -1) = nvl(p_repSchedID, -1)
and operation_seq_num < 0
and wip_supply_type = wip_constants.phantom;
select nvl(min(operation_seq_num), 1)
into l_minOp
from wip_operations
where wip_entity_id = p_wipEntityID;
select 1 into l_exists
from wip_operations
where wip_entity_id = p_wipEntityID
and operation_seq_num = l_compTbl(l_count).operation_seq_num;
select primary_item_id
into l_primary_item
from wip_discrete_jobs
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID;
select count(distinct nvl(basis_type, 1)), min(distinct nvl(basis_type, 1) )
into l_diff_basis, l_basis
from wip_requirement_operations wro
where wro.inventory_item_id = l_compTbl(l_count).inventory_item_id
and wro.organization_id = p_orgID
and wro.wip_entity_id = p_wipEntityID
and wro.operation_seq_num = l_wro_op;
update wip_requirement_operations wro
set wro.quantity_per_assembly = l_compTbl(l_count).primary_quantity + wro.quantity_per_assembly,/*Bug 13527184*/
wro.required_quantity = round( l_compTbl(l_count).primary_quantity*l_multipleFactor/l_compTbl(l_count).component_yield_factor,
wip_constants.max_displayed_precision) + wro.required_quantity /*Bug 13527184*/
where wro.inventory_item_id = l_compTbl(l_count).inventory_item_id
and wro.organization_id = p_orgID
and wro.wip_entity_id = p_wipEntityID
and wro.operation_seq_num = l_wro_op;
update wip_requirement_operations wro
/*Fix for bug 7486594*/
set wro.component_yield_factor = decode(wro.quantity_per_assembly,0,1,round( wro.quantity_per_assembly * l_multipleFactor / wro.required_quantity,
wip_constants.max_displayed_precision))
where wro.inventory_item_id = l_compTbl(l_count).inventory_item_id
and wro.organization_id = p_orgID
and wro.wip_entity_id = p_wipEntityID
and wro.operation_seq_num = l_wro_op;
insert into wip_requirement_operations
(inventory_item_id,
organization_id,
wip_entity_id,
operation_seq_num,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
component_sequence_id,
wip_supply_type,
date_required,
required_quantity,
quantity_issued,
quantity_per_assembly,
component_yield_factor, /*For Component Yield Enhancement(Bug 4369064) */
basis_type,
supply_subinventory,
supply_locator_id,
mrp_net_flag)
values(
l_compTbl(l_count).inventory_item_id,
p_orgID,
p_wipEntityID,
decode(l_compTbl(l_count).wip_supply_type,
wip_constants.phantom, -1*l_opSeq, l_opSeq),
null,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
l_compTbl(l_count).component_sequence_id,
l_compTbl(l_count).wip_supply_type,
p_jobStartDate,
round(l_compTbl(l_count).primary_quantity*l_multipleFactor/
l_compTbl(l_count).component_yield_factor, wip_constants.max_displayed_precision),/*Bug 13527184*/
/*For Component Yield Enhancement(Bug 4369064)->Always need to consider yield factor*/
0,
l_compTbl(l_count).primary_quantity,/*Bug 13527184*/
l_compTbl(l_count).component_yield_factor, /*For Component Yield Enhancement(Bug 4369064) */
decode(l_compTbl(l_count).basis_type,WIP_CONSTANTS.LOT_BASED_MTL,2,NULL),
l_compTbl(l_count).supply_subinventory,
l_compTbl(l_count).supply_locator_id,
decode(l_compTbl(l_count).wip_supply_type, 5, 2,
decode(sign(l_compTbl(l_count).primary_quantity), -1, 2, l_mrpFlag)));
end if; /* end insert */
update wip_requirement_operations wro
set (date_required,
department_id,
wip_supply_type) =
(select nvl(max(wo.first_unit_start_date), wro.date_required),
max(department_id),
decode(wro.wip_supply_type, wip_constants.assy_pull,
decode(nvl(max(wo.count_point_type), 0),
wip_constants.no_manual, wip_constants.op_pull,
wro.wip_supply_type),
wro.wip_supply_type)
from wip_operations wo
where wo.organization_id = wro.organization_id
and wo.wip_entity_id = wro.wip_entity_id
and nvl(wo.repetitive_schedule_id, -1) = nvl(wro.repetitive_schedule_id, -1)
and wo.operation_seq_num = abs(wro.operation_seq_num)),
(comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15) =
(select bic.component_remarks,
bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15
from bom_inventory_components bic
where bic.component_sequence_id = wro.component_sequence_id),
(segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20) =
(select msi.segment1,
msi.segment2,
msi.segment3,
msi.segment4,
msi.segment5,
msi.segment6,
msi.segment7,
msi.segment8,
msi.segment9,
msi.segment10,
msi.segment11,
msi.segment12,
msi.segment13,
msi.segment14,
msi.segment15,
msi.segment16,
msi.segment17,
msi.segment18,
msi.segment19,
msi.segment20
from mtl_system_items msi
where msi.inventory_item_id = wro.inventory_item_id
and msi.organization_id = wro.organization_id)
where wro.wip_entity_id = p_wipEntityID
and nvl(wro.repetitive_schedule_id, -1) = nvl(p_repSchedID, -1)
and wro.organization_id = p_orgID;
l_updateFlag number :=0;/* add for Bug 8413228 (FP of 8392916).*/
select start_quantity,
status_type
into l_jobQty,
l_jobStatus
from wip_discrete_jobs
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID;
select nvl(min(operation_seq_num), fnd_api.g_miss_num)
into l_minOp
from wip_operations
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID;
update wip_requirement_operations
set required_quantity = decode(basis_type,
2, /* basis is lot */
round(quantity_per_assembly/nvl(component_yield_factor,1), 6),/*For Component Yield Enhancement(Bug 4369064) */
round(quantity_per_assembly/nvl(component_yield_factor,1) * p_qty, 6))
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID;
update wip_operations
set quantity_in_queue = quantity_in_queue - (scheduled_quantity - p_qty)
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID
and operation_seq_num = l_minOp /* Fix for Bug 6639146 */
and quantity_in_queue <> 0 /*Bug 13030393: Reverted the fix for Bug 11686971*/
and (scheduled_quantity - p_qty) <= quantity_in_queue; /* add for Bug 8413228 (FP of 8392916).quantity_in_queue can be lower to 0 */
l_updateFlag :=1;
at an operation level, if overcompletion percent is specified. In that case this statement will not update
quantity_in_queue = 0. But idea is to just check for total scheduled quantity at the operation if everything
is moved out of first operation.
Also, the SQL for quantity_in_queue = 0, is split into 2.
1. Released jobs that have First Operation complete (quantity_completed+quantity_running+quantity_in_queue) > 0
2. Unreleased job is just now released with Start quantity change. Thus no quantity in any step of the operation
(quantity_completed+quantity_running+quantity_in_queue) = 0*/
update wip_operations
set quantity_in_queue = CASE WHEN p_qty -(quantity_completed+quantity_running+quantity_in_queue) <0 THEN 0
ELSE p_qty -(quantity_completed+quantity_running+quantity_in_queue) END --Bug#13655641- Qty_in_queue cannot be less than 0
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID
and operation_seq_num = l_minOp
and quantity_in_queue = 0
and scheduled_quantity <= p_qty /*Fix for Bug 8413228 (FP of 8392916). quantity_in_queue can be lower to 0 */
and (quantity_completed+quantity_running+quantity_in_queue) > 0; /*Moved away from first operation*/
l_updateFlag :=1;
update wip_operations
set quantity_in_queue = p_qty
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID
and operation_seq_num = l_minOp
and quantity_in_queue = 0
and (quantity_completed+quantity_running+quantity_in_queue) = 0; /*Unreleased job is just now released with Start quantity change*/
/* add for Bug 8413228 (FP of 8392916). If both update statements have no rows to update, that means
that the quantity was lowered below what was already past queue of the first op.
This is an error. */
if (SQL%NOTFOUND and l_updateFlag = 0) then
fnd_message.set_name('WIP', 'WIP_LOWER_JOB_QTY');
update wip_operations
set scheduled_quantity = p_qty
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID;
update wip_requirement_operations
set required_quantity = decode(basis_type,
2, /* basis is lot */
round(quantity_per_assembly/nvl(component_yield_factor,1), 6),/*For Component Yield Enhancement(Bug 4369064) */
round(quantity_per_assembly/nvl(component_yield_factor,1) * p_qty, 6))
where organization_id = p_orgID
and wip_entity_id = p_wipEntityID;