[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure last_updated_by;
/* Added following variables for Bug# 4184566 and also in update statement */
x_request_id number ;
select *
into wjsi_row
from wip_job_schedule_interface
where rowid = wip_jsi_utils.current_rowid;
select WDJ.build_sequence
into wjsi_row.build_sequence
from wip_discrete_jobs WDJ
where WDJ.wip_entity_id = wjsi_row.wip_entity_id ;
last_updated_by;
update wip_job_schedule_interface
set created_by = wjsi_row.created_by,
last_updated_by = wjsi_row.last_updated_by,
organization_id = wjsi_row.organization_id,
wip_entity_id = wjsi_row.wip_entity_id,
job_name = wjsi_row.job_name,
repetitive_schedule_id = wjsi_row.repetitive_schedule_id,
schedule_group_id = wjsi_row.schedule_group_id,
line_id = wjsi_row.line_id,
project_id = wjsi_row.project_id,
task_id = wjsi_row.task_id,
firm_planned_flag = wjsi_row.firm_planned_flag,
description = wjsi_row.description,
status_type = wjsi_row.status_type,
wip_supply_type = wjsi_row.wip_supply_type,
class_code = wjsi_row.class_code,
primary_item_id = wjsi_row.primary_item_id,
start_quantity = wjsi_row.start_quantity,
net_quantity = wjsi_row.net_quantity,
overcompletion_tolerance_type = wjsi_row.overcompletion_tolerance_type,
overcompletion_tolerance_value = wjsi_row.overcompletion_tolerance_value,
asset_number = wjsi_row.asset_number,--20
asset_group_id = wjsi_row.asset_group_id,
parent_job_name = wjsi_row.parent_job_name,
parent_wip_entity_id = wjsi_row.parent_wip_entity_id,
rebuild_item_id = wjsi_row.rebuild_item_id,
rebuild_serial_number = wjsi_row.rebuild_serial_number,
manual_rebuild_flag = wjsi_row.manual_rebuild_flag,
first_unit_start_date = wjsi_row.first_unit_start_date,
last_unit_start_date = wjsi_row.last_unit_start_date,
first_unit_completion_date = wjsi_row.first_unit_completion_date,
last_unit_completion_date = wjsi_row.last_unit_completion_date,
due_date = wjsi_row.due_date,
requested_start_date = wjsi_row.requested_start_date,
processing_work_days = wjsi_row.processing_work_days,--30
daily_production_rate = wjsi_row.daily_production_rate,
header_id = wjsi_row.header_id,
demand_class = wjsi_row.demand_class,
build_sequence = wjsi_row.build_sequence,
routing_reference_id = wjsi_row.routing_reference_id,
bom_reference_id = wjsi_row.bom_reference_id,
alternate_routing_designator = wjsi_row.alternate_routing_designator,
alternate_bom_designator = wjsi_row.alternate_bom_designator,
bom_revision = wjsi_row.bom_revision,
routing_revision = wjsi_row.routing_revision,--40
bom_revision_date = wjsi_row.bom_revision_date,
routing_revision_date = wjsi_row.routing_revision_date,
lot_number = wjsi_row.lot_number,
source_code = wjsi_row.source_code,
source_line_id = wjsi_row.source_line_id,
scheduling_method = wjsi_row.scheduling_method,
completion_subinventory = wjsi_row.completion_subinventory,
completion_locator_id = wjsi_row.completion_locator_id,
priority = wjsi_row.priority,
allow_explosion = wjsi_row.allow_explosion,
end_item_unit_number = wjsi_row.end_item_unit_number, --must be after primary_item_id
owning_department = wjsi_row.owning_department,
notification_required = wjsi_row.notification_required,--50
shutdown_type = wjsi_row.shutdown_type,
work_order_type = wjsi_row.work_order_type,
tagout_required = wjsi_row.tagout_required,
plan_maintenance = wjsi_row.plan_maintenance,
activity_type = wjsi_row.activity_type,
activity_cause = wjsi_row.activity_cause,
material_issue_by_mo = wjsi_row.material_issue_by_mo,
maintenance_object_id = wjsi_row.maintenance_object_id,
maintenance_object_type = wjsi_row.maintenance_object_type,
maintenance_object_source = wjsi_row.maintenance_object_source,
REQUEST_ID = decode(x_request_id,-1,REQUEST_ID,x_request_id),
PROGRAM_ID = decode(x_program_id,-1,PROGRAM_ID,x_program_id),
PROGRAM_UPDATE_DATE = SYSDATE,
PROGRAM_APPLICATION_ID = decode(x_application_id,-1,PROGRAM_APPLICATION_ID, x_application_id)
where rowid = wip_jsi_utils.current_rowid;
select operation_seq_num
from wip_operations
where wip_entity_id = v_wip_id;
select wip_entity_id, serialization_start_op, load_type, primary_item_id
into l_wipID, l_serialOp, l_loadType, l_primaryItemID
from wip_job_schedule_interface wjsi
where wjsi.rowid = wip_jsi_utils.current_rowid;
update wip_discrete_jobs wdj
set serialization_start_op = null
where wip_entity_id = l_wipID
and serialization_start_op <> 1
and not exists(select 1
from wip_operations wo
where wo.wip_entity_id = wdj.wip_entity_id
and wo.operation_seq_num = wdj.serialization_start_op);
select 1
into g_dummy
from wip_discrete_jobs wdj, mtl_system_items msi
where wdj.primary_item_id = msi.inventory_item_id
and wdj.organization_id = msi.organization_id
and wdj.wip_entity_id = l_wipID
and msi.serial_number_control_code = wip_constants.full_sn;
select 1
into g_dummy
from wip_discrete_jobs
where wip_entity_id = l_wipID
and status_type = wip_constants.unreleased;
select inventory_item_id,
nvl(eam_item_type, -1),
pick_components_flag,
build_in_wip_flag,
eng_item_flag,
inventory_asset_flag,
restrict_subinventories_code,
restrict_locators_code,
location_control_code,
fixed_lead_time,
variable_lead_time
into x_item_row.inventory_item_id,
x_item_row.eam_item_type,
x_item_row.pick_components_flag,
x_item_row.build_in_wip_flag,
x_item_row.eng_item_flag,
x_item_row.inventory_asset_flag,
x_item_row.restrict_subinventories_code,
x_item_row.restrict_locators_code,
x_item_row.location_control_code,
x_item_row.fixed_lead_time,
x_item_row.variable_lead_time
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_org_id;
select status_type,
entity_type,
job_type,
start_quantity,
quantity_completed,
firm_planned_flag,
wdj.primary_item_id,
bom_reference_id,
routing_reference_id,
line_id,
schedule_group_id,
scheduled_completion_date,
project_id,
task_id,
completion_subinventory,
completion_locator_id,
rebuild_item_id
into wdj_row.status_type,
wdj_row.entity_type,
wdj_row.job_type,
wdj_row.start_quantity,
wdj_row.quantity_completed,
wdj_row.firm_planned_flag,
wdj_row.primary_item_id,
wdj_row.bom_reference_id,
wdj_row.routing_reference_id,
wdj_row.line_id,
wdj_row.schedule_group_id,
wdj_row.scheduled_completion_date,
wdj_row.project_id,
wdj_row.task_id,
wdj_row.completion_subinventory,
wdj_row.completion_locator_id,
wdj_row.rebuild_item_id
from wip_discrete_jobs wdj, wip_entities we
where wdj.wip_entity_id = wjsi_row.wip_entity_id
and we.wip_entity_id = wjsi_row.wip_entity_id;
select ood.disable_date
into l_disable_date
from wip_parameters wp, mtl_parameters mp, org_organization_definitions ood
where wp.organization_id = mp.organization_id
and wp.organization_id = ood.organization_id
and wp.organization_id = wjsi_row.organization_id;
SELECT ood.date_to disable_date
INTO l_disable_date
FROM wip_parameters wp,
mtl_parameters mp ,
hr_organization_units ood
WHERE wp.organization_id = mp.organization_id
and wp.organization_id = ood.organization_id
and wp.organization_id = wjsi_row.organization_id;
select count(*)
into l_count
from wip_entities
where wip_entity_name = wjsi_row.job_name
and organization_id = wjsi_row.organization_id;
select count(*)
into l_count
from wip_entities
where wip_entity_id = wjsi_row.wip_entity_id;
l_msg := 'WIP_ML_KB_UPDATE_FAILED';
inv_kanban_pvt.update_card_supply_status (
x_return_status => l_status,
p_kanban_card_id => wjsi_row.kanban_card_id,
p_supply_status => INV_Kanban_PVT.G_Supply_Status_InProcess,
p_document_type => l_doc_type,
p_document_header_id => l_doc_header_id);
select 1
into g_dummy
from fnd_user
where user_id = wjsi_row.created_by
and sysdate between start_date and nvl(end_date, sysdate);
procedure last_updated_by is begin
select 1
into g_dummy
from fnd_user
where user_id = wjsi_row.last_updated_by
and sysdate between start_date and nvl(end_date, sysdate);
wip_jsi_utils.record_error('WIP_ML_LAST_UPDATED_BY');
end last_updated_by;
select sum(primary_quantity), count(*)
into l_reserved_qty, l_reservation_count
from wip_reservations_v
where wip_entity_id = wjsi_row.wip_entity_id
and organization_id = wjsi_row.organization_id;
select count(*)
into l_rep_sched_count
from wip_repetitive_schedules
where repetitive_schedule_id = wjsi_row.repetitive_schedule_id;
select 1
into g_dummy
from so_demand_classes_active_v
where demand_class_code = wjsi_row.demand_class;
select 1
into g_dummy
from wip_lines_val_v
where line_id = wjsi_row.line_id
and organization_id = wjsi_row.organization_id;
select 1
into g_dummy
from wip_schedule_groups_val_v
where schedule_group_id = wjsi_row.schedule_group_id
and organization_id = wjsi_row.organization_id;
SELECT NVL(SUM(PRIMARY_QUANTITY),0)
FROM WIP_RESERVATIONS_V
WHERE WIP_ENTITY_ID = wjsi_row.Wip_Entity_Id
AND INVENTORY_ITEM_ID = nvl(wjsi_row.primary_item_id,l_Primary_Item_Id )
AND ORGANIZATION_ID = wjsi_row.Organization_Id;
select primary_item_id /* for Bug2792736 */
into l_Primary_Item_Id
from wip_discrete_jobs
where wip_entity_id = wjsi_row.wip_entity_id
and organization_id = wjsi_row.organization_id;
SELECT propagate_job_change_to_po
INTO l_propagate_job_change_to_po
FROM wip_parameters wp
WHERE organization_id = wjsi_row.organization_id;
wip_jsi_utils.record_error('WIP_CANT_UPDATE_JOB');
select daily_maximum_rate
into l_max_line_rate
from wip_lines_val_v
where line_id = wjsi_row.line_id;
select 1
into g_dummy
from wip_repetitive_items
where line_id = wjsi_row.line_id
and primary_item_id = wjsi_row.primary_item_id
and organization_id = wjsi_row.organization_id;
select 1
into l_dummy
from MTL_SYSTEM_ITEMS msi
where msi.inventory_item_id = wjsi_row.primary_item_id
and msi.organization_id= wjsi_row.organization_id
and msi.replenish_to_order_flag = 'Y'
and msi.bom_item_type = 4
and not exists
(SELECT COMMON_BILL_SEQUENCE_ID
FROM BOM_BILL_OF_MATERIALS
WHERE ASSEMBLY_ITEM_ID = wjsi_row.primary_item_id
AND ORGANIZATION_ID = wjsi_row.organization_id
AND nvl(ALTERNATE_BOM_DESIGNATOR,'none') =
nvl(wjsi_row.alternate_bom_designator,'none')
AND (ASSEMBLY_TYPE = 1 OR X_Eng_Items_Flag = 1));
select start_date_active, end_date_active
into l_start_date, l_end_date
from mtl_eam_asset_activities
where asset_activity_id = wjsi_row.primary_item_id
and organization_id = wjsi_row.organization_id
and inventory_item_id = wjsi_row.asset_group_id
and serial_number = wjsi_row.asset_number;
select min(start_date_active), min(end_date_active)
into l_start_date, l_end_date
from mtl_eam_asset_activities
where asset_activity_id = wjsi_row.primary_item_id
and organization_id = wjsi_row.organization_id
and inventory_item_id = wjsi_row.rebuild_item_id
and serial_number = wjsi_row.rebuild_serial_number;
select min(start_date_active), min(end_date_active)
into l_start_date, l_end_date
from mtl_eam_asset_activities
where asset_activity_id = wjsi_row.primary_item_id
and organization_id = wjsi_row.organization_id
and inventory_item_id = wjsi_row.rebuild_item_id;
select min(start_date_active), min(end_date_active)
into l_start_date, l_end_date
from mtl_eam_asset_activities
where asset_activity_id = wjsi_row.primary_item_id
and organization_id = wjsi_row.organization_id
and inventory_item_id = wjsi_row.rebuild_item_id;
select count(*)
into l_routing_count
from bom_operational_routings
where organization_id = wjsi_row.organization_id
and assembly_item_id = decode(wjsi_row.load_type, wip_constants.create_ns_job, wjsi_row.routing_reference_id, wjsi_row.primary_item_id)
and nvl(alternate_routing_designator, '@@@') = nvl(wjsi_row.alternate_routing_designator, '@@@')
and nvl(cfm_routing_flag, 2) = 2; --ignore flow routings
select 1
into g_dummy
from mtl_system_items
where inventory_item_id = wjsi_row.bom_reference_id
and organization_id = wjsi_row.organization_id
and build_in_wip_flag = 'Y'
and pick_components_flag = 'N'
and eng_item_flag = 'N';
select 1
into g_dummy
from bom_routing_alternates_v
where assembly_item_id = l_rtg_item_id
and alternate_routing_designator = wjsi_row.alternate_routing_designator
and organization_id = wjsi_row.organization_id
and nvl(cfm_routing_flag, 2) = 2 --ignore flow routings
and routing_type = 1;
select 1
into g_dummy
from bom_bill_alternates_v
where assembly_item_id = l_bom_item_id
and alternate_bom_designator = wjsi_row.alternate_bom_designator
and organization_id = wjsi_row.organization_id
and assembly_type = 1;
select mpv.project_id --this query will return multiple rows if the project has tasks
into g_dummy
from pjm_projects_v mpv, pjm_project_parameters ppp, mtl_parameters mp
where mpv.project_id = ppp.project_id
and mpv.project_id = wjsi_row.project_id
and ppp.organization_id = wjsi_row.organization_id
and ppp.organization_id = mp.organization_id
and nvl(mp.project_reference_enabled, 2) = wip_constants.yes;
wip_utilities.get_message_stack(p_delete_stack => 'T',
p_msg => l_message);
select 1
into g_dummy
from bom_calendar_dates bcd, mtl_parameters mp
where mp.organization_id = wjsi_row.organization_id
and mp.calendar_code = bcd.calendar_code
and mp.calendar_exception_set_id = bcd.exception_set_id
and bcd.calendar_date = trunc(wjsi_row.first_unit_start_date);
select 1
into g_dummy
from bom_calendar_dates bcd, mtl_parameters mp
where mp.organization_id = wjsi_row.organization_id
and mp.calendar_code = bcd.calendar_code
and mp.calendar_exception_set_id = bcd.exception_set_id
and bcd.calendar_date = trunc(wjsi_row.last_unit_completion_date);
select count(*)
into l_line_count
from wip_lines
where organization_id = wjsi_row.organization_id
and line_id = wjsi_row.line_id
and line_schedule_type = 1; --fixed
select count(*)
into l_rtg_count
from bom_operational_routings bor, wip_repetitive_items wri
where wri.line_id = wjsi_row.line_id
and nvl(bor.cfm_routing_flag, 2) = 2 --ignore flow rtgs
and wri.primary_item_id = wjsi_row.primary_item_id
and wri.organization_id = wjsi_row.organization_id
and nvl(bor.alternate_routing_designator,'@@') = nvl(wri.alternate_routing_designator,'@@')
and bor.organization_id = wri.organization_id
and bor.assembly_item_id = wri.primary_item_id;
select count(*)
into l_rtg_count
from bom_operational_routings bor, wip_repetitive_items wri
where wri.line_id = wjsi_row.line_id
and nvl(bor.cfm_routing_flag,2) = 2 --ignore flow routings
and wri.primary_item_id = wjsi_row.primary_item_id
and wri.organization_id = wjsi_row.organization_id
and nvl(bor.alternate_routing_designator,'@@') = nvl(wri.alternate_routing_designator,'@@')
and bor.organization_id = wri.organization_id
and bor.assembly_item_id = wri.primary_item_id;
select count(*)
into l_line_count
from wip_lines_val_v
where organization_id = wjsi_row.organization_id
and line_id = wjsi_row.line_id
and line_schedule_type = 2;
select calendar_date
into wjsi_row.first_unit_start_date
from bom_calendar_dates bcd, mtl_parameters mp
where mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and seq_num = (select prior_seq_num - ceil(wjsi_row.processing_work_days)+1
from bom_calendar_dates b2
where b2.calendar_date = trunc(wjsi_row.last_unit_start_date)
and b2.calendar_code = bcd.calendar_code
and b2.exception_set_id = bcd.exception_set_id);
SELECT bcd.calendar_date
INTO wjsi_row.first_unit_start_date
FROM bom_calendar_dates bcd,
mtl_parameters mp,
bom_calendar_dates b2
WHERE mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and bcd.seq_num = b2.prior_seq_num - ceil(wjsi_row.processing_work_days)+1
and b2.calendar_date = trunc(wjsi_row.last_unit_start_date)
and b2.calendar_code = mp.calendar_code;
select calendar_date
into wjsi_row.last_unit_start_date
from bom_calendar_dates bcd, mtl_parameters mp
where mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and seq_num = (select next_seq_num + ceil(wjsi_row.processing_work_days)-1
from bom_calendar_dates b2
where b2.calendar_date = trunc(wjsi_row.first_unit_start_date)
and b2.calendar_code = bcd.calendar_code
and b2.exception_set_id = bcd.exception_set_id);
SELECT bcd.calendar_date
INTO wjsi_row.last_unit_start_date
FROM bom_calendar_dates bcd,
mtl_parameters mp,
bom_calendar_dates b2
WHERE mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and bcd.seq_num = b2.prior_seq_num + ceil(wjsi_row.processing_work_days)-1
and b2.calendar_date = trunc(wjsi_row.first_unit_start_date)
and b2.calendar_code = mp.calendar_code;
select calendar_date
into wjsi_row.first_unit_completion_date
from bom_calendar_dates bcd, mtl_parameters mp
where mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and seq_num = (select prior_seq_num - ceil(wjsi_row.processing_work_days)+1
from bom_calendar_dates b2
where b2.calendar_date = trunc(wjsi_row.last_unit_completion_date)
and b2.calendar_code = bcd.calendar_code
and b2.exception_set_id = bcd.exception_set_id);
SELECT bcd.calendar_date
INTO wjsi_row.first_unit_completion_date
FROM bom_calendar_dates bcd,
mtl_parameters mp,
bom_calendar_dates b2
WHERE mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and bcd.seq_num = b2.prior_seq_num - ceil(wjsi_row.processing_work_days)+1
and b2.calendar_date = trunc(wjsi_row.last_unit_completion_date)
and b2.calendar_code = mp.calendar_code;
select calendar_date
into wjsi_row.last_unit_completion_date
from bom_calendar_dates bcd, mtl_parameters mp
where mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and seq_num = (select next_seq_num + ceil(wjsi_row.processing_work_days)-1
from bom_calendar_dates b2
where b2.calendar_date = trunc(wjsi_row.first_unit_completion_date)
and b2.calendar_code = bcd.calendar_code
and b2.exception_set_id = bcd.exception_set_id) ;
SELECT bcd.calendar_date
INTO wjsi_row.last_unit_completion_date
FROM bom_calendar_dates bcd,
mtl_parameters mp,
bom_calendar_dates b2
WHERE mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and bcd.seq_num = b2.prior_seq_num + ceil(wjsi_row.processing_work_days)-1
and b2.calendar_date = trunc(wjsi_row.first_unit_completion_date)
and b2.calendar_code = mp.calendar_code;
select inventory_asset_flag, restrict_subinventories_code
into l_inv_asset_flag, l_restrict_subinv_code
from mtl_system_items
where inventory_item_id = l_inv_item_id
and organization_id = wjsi_row.organization_id;
select 1
into g_dummy
from mtl_item_sub_val_v
where inventory_item_id = l_inv_item_id
and organization_id = wjsi_row.organization_id
and secondary_inventory_name = wjsi_row.completion_subinventory;
select 1
into g_dummy
from mtl_item_sub_ast_trk_val_v
where inventory_item_id = l_inv_item_id
and organization_id = wjsi_row.organization_id
and secondary_inventory_name = wjsi_row.completion_subinventory;
select 1
into g_dummy
from mtl_subinventories_val_v
where secondary_inventory_name = wjsi_row.completion_subinventory
and organization_id = wjsi_row.organization_id
and asset;
select 1
into g_dummy
from mtl_sub_ast_trk_val_v
where secondary_inventory_name = wjsi_row.completion_subinventory
and organization_id = wjsi_row.organization_id;
select restrict_locators_code, location_control_code
into l_restrict_locs, l_item_loc_control
from mtl_system_items
where inventory_item_id = l_item_id
and organization_id = wjsi_row.organization_id;
select sub.locator_type, mp.stock_locator_control_code
into l_sub_loc_control, l_org_loc_control
from mtl_secondary_inventories sub, mtl_parameters mp
where sub.secondary_inventory_name = wjsi_row.completion_subinventory
and sub.organization_id = wjsi_row.organization_id
and mp.organization_id = wjsi_row.organization_id;
select 1
into g_dummy
from pjm_unit_numbers_lov_v pun,
mtl_parameters mp
where pun.unit_number = wjsi_row.end_item_unit_number
and mp.organization_id = wjsi_row.organization_id
and mp.master_organization_id = pun.master_organization_id;
select end_item_unit_number into wjsi_row.end_item_unit_number
from wip_discrete_jobs
where wip_entity_id =
(select wip_entity_id
from wip_job_schedule_interface
where rowid = WIP_JSI_UTILS.CURRENT_ROWID
);
select primary_item_id into wjsi_row.bom_reference_id
from wip_discrete_jobs
where wip_entity_id = wjsi_row.wip_entity_id;
select end_item_unit_number into wjsi_row.end_item_unit_number
from wip_discrete_jobs
where wip_entity_id =
(select wip_entity_id
from wip_job_schedule_interface
where rowid = WIP_JSI_UTILS.CURRENT_ROWID
);
select 1
into g_dummy
from dual
where exists(select 1
from cst_cg_wip_acct_classes_v
where class_code = wjsi_row.class_code
and organization_id = wjsi_row.organization_id
and class_type = wip_constants.discrete);
select 1
into g_dummy
from dual
where exists(select 1
from cst_cg_wip_acct_classes_v ccwac, mtl_parameters mp
where ccwac.class_code = wjsi_row.class_code
and ccwac.organization_id = wjsi_row.organization_id
and ccwac.class_type = wip_constants.discrete
and mp.organization_id = wjsi_row.organization_id
and ( mp.primary_cost_method = wip_constants.cost_std
or ccwac.cost_group_id = (select costing_group_id
from mrp_project_parameters mpp
where organization_id = wjsi_row.organization_id
and mpp.project_id = wjsi_row.project_id)
)
);
select 1
into g_dummy
from dual
where exists(select 1
from wip_non_standard_classes_val_v
where class_code = wjsi_row.class_code
and organization_id = wjsi_row.organization_id);
select 1
into g_dummy
from dual
where exists(select 1
from cst_cg_wip_acct_classes_v
where class_code = wjsi_row.class_code
and organization_id = wjsi_row.organization_id
and class_type = wip_constants.eam);
select 1
into g_dummy
from dual
where exists(select 1
from cst_cg_wip_acct_classes_v ccwac, mtl_parameters mp
where ccwac.class_code = wjsi_row.class_code
and ccwac.organization_id = wjsi_row.organization_id
and ccwac.class_type = wip_constants.eam
and mp.organization_id = wjsi_row.organization_id
and ( mp.primary_cost_method = wip_constants.cost_std
or ccwac.cost_group_id = (select costing_group_id
from mrp_project_parameters mpp
where organization_id = wjsi_row.organization_id
and mpp.project_id = wjsi_row.project_id)
)
);
select count(*)
into l_rtg_count
from bom_operational_routings
where assembly_item_id = wjsi_row.routing_reference_id
and organization_id = wjsi_row.organization_id
and nvl(alternate_routing_designator, '@@') = nvl(wjsi_row.alternate_routing_designator, '@@')
and nvl(cfm_routing_flag, 2) = 2;
select count(*)
into l_rtg_count
from bom_operational_routings
where assembly_item_id = wjsi_row.primary_item_id
and organization_id = wjsi_row.organization_id
and nvl(alternate_routing_designator, '@@') = nvl(wjsi_row.alternate_routing_designator, '@@')
and nvl(cfm_routing_flag, 2) = 2;
select count(*)
into l_rtg_count
from wip_operations
where wip_entity_id = wjsi_row.wip_entity_id;
select count(*)
into l_count
from bom_operational_routings
where assembly_item_id = decode(wjsi_row.load_type, wip_constants.create_ns_job, wjsi_row.routing_reference_id,
wjsi_row.primary_item_id)
and organization_id = wjsi_row.organization_id
and nvl(alternate_routing_designator, '@@') = nvl(wjsi_row.alternate_routing_designator, '@@');
select 1
into g_dummy
from mtl_system_items
where inventory_item_id = wjsi_row.asset_group_id
and organization_id = wjsi_row.organization_id
and eam_item_type = 1; -- asset group
select 1
into g_dummy
from mtl_serial_numbers
where inventory_item_id = wjsi_row.asset_group_id
and current_organization_id = wjsi_row.organization_id
and serial_number = wjsi_row.asset_number
and maintainable_flag = 'Y';
select 1
into g_dummy
from mtl_system_items
where inventory_item_id = wjsi_row.rebuild_item_id
and organization_id = wjsi_row.organization_id
and eam_item_type = 3;
select 1
into g_dummy
from mtl_serial_numbers
where serial_number = wjsi_row.rebuild_serial_number
and inventory_item_id = wjsi_row.rebuild_item_id
and current_organization_id = wjsi_row.organization_id
and current_status in (1,3,4);--defined not used, resides in stores, issued out nocopy of stores (consistent w/EAM UI)
select 1
into g_dummy
from mtl_serial_numbers
where serial_number = wjsi_row.rebuild_serial_number
and inventory_item_id = wjsi_row.rebuild_item_id
and current_organization_id = wjsi_row.organization_id;
select 1
into g_dummy
from wip_entities
where wip_entity_id = wjsi_row.parent_wip_entity_id
and entity_type = wip_constants.eam;
select disable_date
into l_disable_date
from bom_departments
where department_id = wjsi_row.owning_department
and organization_id = wjsi_row.organization_id;
select 1
into g_dummy
from mfg_lookups
where lookup_type = 'BOM_EAM_SHUTDOWN_TYPE'
and lookup_code = wjsi_row.shutdown_type
and enabled_flag = 'Y';
select 1
into g_dummy
from mfg_lookups
where lookup_type = 'WIP_EAM_WORK_ORDER_TYPE'
and lookup_code = wjsi_row.work_order_type
and enabled_flag = 'Y';
select 1
into g_dummy
from mfg_lookups
where lookup_type = 'MTL_EAM_ACTIVITY_TYPE'
and lookup_code = wjsi_row.activity_type
and enabled_flag = 'Y';
select 1
into g_dummy
from mfg_lookups
where lookup_type = 'MTL_EAM_ACTIVITY_CAUSE'
and lookup_code = wjsi_row.activity_cause
and enabled_flag = 'Y';
select serial_number_control_code
into l_serial_number_control_code
from mtl_system_items
where organization_id = wjsi_row.organization_id
and inventory_item_id = wjsi_row.rebuild_item_id;
select 1
into g_dummy
from mfg_lookups
where lookup_type = 'WIP_MAINTENANCE_OBJECT_SOURCE'
and lookup_code = wjsi_row.maintenance_object_source
and enabled_flag = 'Y';
select 1
into g_dummy
from mtl_serial_numbers
where current_organization_id = wjsi_row.organization_id
and inventory_item_id = wjsi_row.asset_group_id
and serial_number = wjsi_row.asset_number
and gen_object_id = wjsi_row.maintenance_object_id;
select 1
into g_dummy
from mtl_serial_numbers
where current_organization_id = wjsi_row.organization_id
and inventory_item_id = wjsi_row.rebuild_item_id
and serial_number = wjsi_row.rebuild_serial_number
and gen_object_id = wjsi_row.maintenance_object_id;
select 1
into g_dummy
from mfg_lookups
where lookup_type = 'MTL_EAM_ACTIVITY_SOURCE'
and lookup_code = wjsi_row.activity_source
and enabled_flag = 'Y';
update wip_job_schedule_interface
set date_released = sysdate
where rowid = WIP_JSI_Utils.current_rowid ;
select 'X'
into x_period_exists
from org_acct_periods
where organization_id = wjsi_row.organization_id
and trunc(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(nvl(wjsi_row.date_released,sysdate),wjsi_row.organization_id)) between PERIOD_START_DATE and SCHEDULE_CLOSE_DATE
and period_close_date is NULL;