[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
type t_wlji_last_updt_date is table of wsm_lot_job_interface.last_update_date%type;
type t_wlji_last_updt_by is table of wsm_lot_job_interface.last_updated_by%type;
type t_wlji_last_updt_login is table of wsm_lot_job_interface.last_update_login%type;
type t_wlji_prog_updt_date is table of wsm_lot_job_interface.program_update_date%type;
type t_wlji_last_updt_by_name is table of wsm_lot_job_interface.last_updated_by_name%type;
v_insert_wip NUMBER := NULL; --OPTII-PERF 1-Insert wip data in create_jobcopies procedure
type t_wsli_last_updated_by is table of wsm_starting_lots_interface.last_updated_by%type;
v_wsli_last_updated_by t_wsli_last_updated_by := t_wsli_last_updated_by();
last_updated_by wsm_starting_lots_interface.last_updated_by%type,
created_by wsm_starting_lots_interface.created_by%type,
primary_uom_code mtl_system_items.primary_uom_code%type,
comp_issue_quantity wsm_starting_lots_interface.component_issue_quantity%type
);
type t_wljdi_last_updt_by is table of wsm_lot_job_dtl_interface.last_updated_by %type;
type t_wljdi_last_updt_login is table of wsm_lot_job_dtl_interface.last_update_login %type;
type t_wljdi_last_updt_date is table of wsm_lot_job_dtl_interface.last_update_date %type;
type t_wljdi_prog_updt_date is table of wsm_lot_job_dtl_interface.program_update_date %type;
select wco.operation_seq_num,
wco.operation_sequence_id
into x_start_op_seq_num,
x_start_op_seq_id
from wsm_copy_operations wco
where wco.wip_entity_id = p_wip_entity_id
and NVL(wco.network_start_end, 'X') = l_char_temp; -- donot use 'S'
select wco.operation_seq_num,
wco.operation_sequence_id
into x_end_op_seq_num,
x_end_op_seq_id
from wsm_copy_operations wco
where wco.wip_entity_id = p_wip_entity_id
and NVL(wco.network_start_end, 'X') = l_char_temp; -- donot use 'E'
select nvl(operation_sequence_id, -1)
into x_op_seq_id
from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_rtg_op_seq_num;
select organization_id
into p_org_id
from MTL_PARAMETERS
where organization_code = p_org_code;
p_load_type in number, -- 5 job creation, 6 job update
p_org_id in number,
p_wip_entity_id in out nocopy number,
p_job_name in out nocopy varchar2,
x_error_code out nocopy number,
x_error_msg out nocopy varchar2) is
l_temp_num number;
select FND_Profile.value('WIP_JOB_PREFIX') || wip_job_number_s.nextval
into p_job_name
from dual;
select 1
into l_temp_num
from wip_entities
where wip_entity_name = p_job_name
and organization_id = p_org_id;
elsif p_load_type = 6 then -- Job update
l_xst := false;
select wip_entity_id
into p_wip_entity_id
from wip_entities
where wip_entity_name = p_job_name
and organization_id = p_org_id;
select decode(wdj.status_type, WIP_CONSTANTS.UNRELEASED,NVL(p_job_name,we.wip_entity_name),we.wip_entity_name),we.wip_entity_name
into p_job_name,l_old_job_name
from wip_discrete_jobs wdj,
wip_entities we
where wdj.wip_entity_id = p_wip_entity_id
and wdj.status_type in (
WIP_CONSTANTS.UNRELEASED,
WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.HOLD,
WIP_CONSTANTS.CANCELLED)
and wdj.wip_entity_id = we.wip_entity_id
and we.organization_id = p_org_id;
select count(wip_entity_id)
into l_temp_num
from wip_entities
where wip_entity_name = p_job_name
and organization_id = p_org_id
and wip_entity_id <> p_wip_entity_id;
end if; -- Job update
select record_id,
-- ST Fix for bug 4285032 :
-- APS will be populating 8 for resource instance information
-- and 9 resource instance usage information..
-- Map it to WSM load type 7 --> resource instance
-- 4 --> resource instance usage..
decode(load_type,8,7,
9,4,
load_type) load_type,
substitution_type,
job_op_seq_num,
routing_op_seq_num,
next_routing_op_seq_num,
inventory_item_id_new,
inventory_item_new,
primary_item,
primary_item_id,
src_phantom_item,
src_phantom_item_id,
component_sequence_id,
date_required,
scheduled_quantity,
required_quantity,
batch_id,
assigned_units,
instance_id_new,
operation_completion_date,
operation_start_date,
operation_yield,
resource_code_new,
resource_id_new,
resource_seq_num,
substitute_group_num,
replacement_group_num,
serial_number_new,
start_date,
completion_date,
-- ST : Detailed scheduling : start
firm_type,
setup_id,
setup_code,
group_sequence_id,
group_sequence_num,
max_assigned_units,
parent_resource_seq_num,
resource_hours,
-- ST : Detailed Scheduling : Added for resource adding changes
department_id ,
department_code ,
-- ST : Detailed Scheduling : Added for resource adding changes : end
-- Added For LBJ Interface Alcoa Enhancement
recommended,
standard_operation_code,
standard_operation_id,
operation_yield_enabled,
minimum_transfer_quantity,
backflush_flag,
count_point_type,
description,
include_in_rollup,
option_dependent_flag,
inventory_item_id_old,
inventory_item_old,
wip_supply_type,
cumulative_yield,/*Added for bugfix:7248992 */
reverse_cumulative_yield/*Added for bugfix:7248992 */
from wsm_lot_job_dtl_interface
where parent_header_id = p_parent_header_id
and process_status = WIP_CONSTANTS.RUNNING;
select record_id,
-- ST Fix for bug 4285032 :
-- APS will be populating 8 for resource instance information
-- and 9 resource instance usage information..
-- Map it to WSM load type 7 --> resource instance
-- 4 --> resource instance usage..
decode(load_type,8,7,
9,4,
load_type) load_type,
substitution_type,
job_op_seq_num,
routing_op_seq_num,
next_routing_op_seq_num,
inventory_item_id_new,
inventory_item_new,
primary_item,
primary_item_id,
src_phantom_item,
src_phantom_item_id,
component_sequence_id,
date_required,
scheduled_quantity,
required_quantity,
batch_id,
assigned_units,
instance_id_new,
operation_completion_date,
operation_start_date,
operation_yield,
resource_code_new,
resource_id_new,
resource_seq_num,
substitute_group_num,
replacement_group_num,
serial_number_new,
start_date,
completion_date,
/* ST : Detailed scheduling : start */
firm_type,
setup_id,
setup_code,
group_sequence_id,
group_sequence_num,
max_assigned_units,
parent_resource_seq_num,
resource_hours,
/* ST : Detailed scheduling : END */
/* ST : Detailed Scheduling : Added for resource adding changes */
department_id ,
department_code ,
/* ST : Detailed Scheduling : Added for resource adding changes : end */
-- Added For LBJ Interface Alcoa Enhancement
recommended,
standard_operation_code,
standard_operation_id,
operation_yield_enabled,
minimum_transfer_quantity,
backflush_flag,
count_point_type,
description,
include_in_rollup,
option_dependent_flag,
inventory_item_id_old,
inventory_item_old,
wip_supply_type,
cumulative_yield,/*Added for bugfix:7248992 */
reverse_cumulative_yield/*Added for bugfix:7248992 */
from wsm_lot_job_dtl_interface
where parent_header_id IS NULL
and wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and process_status = WIP_CONSTANTS.RUNNING
and NVL(group_id, -1) = NVL(p_group_id, -1);
l_v_sub_grp_num.delete;
l_v_rpl_grp_num.delete;
l_v_res_seq_num.delete;
l_v_ins_id.delete;
l_v_ser_num.delete;
l_v_item_id.delete;
l_v_prm_item_id.delete;
l_v_phm_item_id.delete;
l_v_res_id.delete;
l_v_record_id.delete;
l_v_dept_id.delete;
l_v_res_code.delete;
l_v_setup_id.delete; -- Added for bug fix 4704289
l_v_op_start_date.delete;
l_v_op_completion_date.delete;
l_v_min_trf_qty.delete;
l_v_count_point_type.delete;
l_v_description.delete;
l_v_backflush_flag.delete;
l_v_op_yield_enable.delete;
l_v_option_dep_flag.delete;
l_v_inc_in_rollup.delete;
l_v_std_op_id.delete;
l_v_std_op_code.delete;
l_v_item_old_id.delete;
l_v_recommended.delete;
select 1
into l_xst
from wip_operations
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = v_wljdi_job_op_seq_num(l_d);
select unique(resource_id)
into v_wljdi_resource_id_new(l_d)
from bom_resources
where resource_code = v_wljdi_resource_code_new(l_d)
and organization_id = p_org_id
and cost_code_type in (3,4);
select unique(resource_code)
into v_wljdi_resource_code_new(l_d)
from bom_resources
where resource_id = v_wljdi_resource_id_new(l_d)
and organization_id = p_org_id
and cost_code_type in (3,4);
select resource_id
into v_wljdi_resource_id_new(l_d)
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = l_job_op_seq_num
and wor.resource_seq_num = v_wljdi_resource_seq_num(l_d);
select resource_id
into v_wljdi_resource_id_new(l_d)
from wip_sub_operation_resources wsor
where wsor.wip_entity_id = p_wip_entity_id
and wsor.operation_seq_num = l_job_op_seq_num
and wsor.resource_seq_num = v_wljdi_resource_seq_num(l_d);
select resource_id
into v_wljdi_resource_id_new(l_d)
from wsm_copy_op_resources wcor
where wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = l_rtg_op_seq_num
and wcor.resource_seq_num = v_wljdi_resource_seq_num(l_d);
select unique(department_id)
into l_dept_id
from bom_departments
where department_code = v_wljdi_department_code(l_d)
and department_id = nvl(v_wljdi_department_id(l_d),department_id)
and organization_id = p_org_id;
select bst.setup_id
into l_setup_id
from BOM_SETUP_TYPES bst
where bst.setup_code = v_wljdi_setup_code(l_d)
and bst.organization_id = p_org_id;
select unique(inventory_item_id)
into v_wljdi_inventory_item_id_old(l_d)
from mtl_system_items_kfv
where concatenated_segments = v_wljdi_inventory_item_old(l_d)
and organization_id = p_org_id;
select concatenated_segments
into v_wljdi_inventory_item_old(l_d)
from mtl_system_items_kfv
where inventory_item_id = v_wljdi_inventory_item_id_old(l_d)
and organization_id = p_org_id;
select unique(inventory_item_id)
into v_wljdi_inventory_item_id_new(l_d)
from mtl_system_items_kfv
where concatenated_segments = v_wljdi_inventory_item_new(l_d)
and organization_id = p_org_id;
select concatenated_segments
into v_wljdi_inventory_item_new(l_d)
from mtl_system_items_kfv
where inventory_item_id = v_wljdi_inventory_item_id_new(l_d)
and organization_id = p_org_id;
select unique(inventory_item_id)
into v_wljdi_primary_item_id(l_d)
from mtl_system_items_kfv
where concatenated_segments = v_wljdi_primary_item(l_d)
and organization_id = p_org_id;
select unique(inventory_item_id)
into v_wljdi_src_phantom_item_id(l_d)
from mtl_system_items_kfv
where concatenated_segments = v_wljdi_src_phantom_item(l_d)
and organization_id = p_org_id;
select concatenated_segments
into v_wljdi_src_phantom_item(l_d)
from mtl_system_items_kfv
where inventory_item_id = v_wljdi_src_phantom_item_id(l_d)
and organization_id = p_org_id;
select source_phantom_id
into v_wljdi_src_phantom_item_id(l_d)
from wsm_copy_requirement_ops
where wip_entity_id=p_wip_entity_id
and component_item_id = v_wljdi_inventory_item_id_old(l_d)
and operation_seq_num in (v_wljdi_routing_op_seq_num(l_d), (-1)*v_wljdi_routing_op_seq_num(l_d));
select standard_operation_id
into v_wljdi_std_op_id(l_d)
from bom_standard_operations
where organization_id = p_org_id
and operation_code = v_wljdi_std_op_code(l_d)
and standard_operation_id = nvl(v_wljdi_std_op_id(l_d),standard_operation_id);
select operation_code
into v_wljdi_std_op_code(l_d)
from bom_standard_operations
where organization_id = p_org_id
and standard_operation_id = v_wljdi_std_op_id(l_d);
select department_id,
nvl(v_wljdi_min_trf_qty(l_d),minimum_transfer_quantity),
nvl(v_wljdi_count_point_type(l_d),count_point_type),
nvl(v_wljdi_description(l_d),operation_description),
nvl(v_wljdi_backflush_flag(l_d),backflush_flag),
nvl(v_wljdi_op_yield_enable(l_d),operation_yield_enabled),
nvl(v_wljdi_option_dep_flag(l_d),option_dependent_flag),
nvl(v_wljdi_inc_in_rollup(l_d),include_in_rollup)
into v_wljdi_department_id(l_d),
v_wljdi_min_trf_qty(l_d),
v_wljdi_count_point_type(l_d),
v_wljdi_description(l_d),
v_wljdi_backflush_flag(l_d),
v_wljdi_op_yield_enable(l_d),
v_wljdi_option_dep_flag(l_d),
v_wljdi_inc_in_rollup(l_d)
from bom_standard_operations
where standard_operation_id = v_wljdi_std_op_id(l_d)
and organization_id = p_org_id;
select unique(department_id)
into v_wljdi_department_id(l_d)
from bom_departments
where department_code = v_wljdi_department_code(l_d)
and department_id = nvl(v_wljdi_department_id(l_d),department_id)
and organization_id = p_org_id;
select 1
into l_temp
from bom_departments
where department_id = v_wljdi_department_id(l_d)
and organization_id = p_org_id;
select 1
into l_temp
from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = v_wljdi_routing_op_seq_num(l_d);
select scheduled_start_date,
scheduled_completion_date
into l_job_start_date,
l_job_compl_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
select unique(resource_id)
into l_resource_id
from bom_resources
where resource_code = v_wljdi_resource_code_new(l_d)
and organization_id = p_org_id;
select resource_seq_num
into l_res_seq_num
from wsm_copy_op_resources wcor
where wcor.wip_entity_id = p_wip_entity_id
and wcor.organization_id = p_org_id
and wcor.operation_seq_num = l_rtg_op_seq_num
and wcor.resource_id = l_resource_id
and NVL(wcor.substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
and NVL(wcor.replacement_group_num, 0) = NVL(l_rpl_grp_num, 0);
select resource_seq_num
into l_res_seq_num
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.organization_id = p_org_id
and wor.operation_seq_num = l_job_op_seq_num
and wor.resource_id = l_resource_id
and NVL(wor.substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
and NVL(wor.replacement_group_num, 0) = NVL(l_rpl_grp_num, 0);
update wsm_lot_job_dtl_interface
set substitute_group_num = l_v_sub_grp_num(i),
replacement_group_num = l_v_rpl_grp_num(i),
resource_id_new = l_v_res_id(i),
resource_seq_num = l_v_res_seq_num(i),
instance_id_new = l_v_ins_id(i),
serial_number_new = l_v_ser_num(i),
inventory_item_id_new = l_v_item_id(i),
primary_item_id = l_v_prm_item_id(i),
src_phantom_item_id = l_v_phm_item_id(i),
/* ST : Detailed Scheduling start */
resource_code_new = l_v_res_code(i),
/* Added NVL on department_id for Bug 5471556. It is done so that it's not cleared
out if user entered department id alone so that it can be validated at later stage. */
department_id = nvl(l_v_dept_id(i),department_id),
setup_id = l_v_setup_id(i), -- Added for bug fix 4704289
/* ST : Detailed Scheduling end */
-- Added For LBJ Interface Alcoa Enhancement
standard_operation_id = l_v_std_op_id(i),
operation_start_date = l_v_op_start_date(i),
operation_completion_date = l_v_op_completion_date(i),
minimum_transfer_quantity = l_v_min_trf_qty(i),
count_point_type = l_v_count_point_type(i),
description = l_v_description(i),
backflush_flag = l_v_backflush_flag(i),
operation_yield_enabled = l_v_op_yield_enable(i),
option_dependent_flag = l_v_option_dep_flag(i),
include_in_rollup = l_v_inc_in_rollup(i),
standard_operation_code = l_v_std_op_code(i),
recommended = l_v_recommended(i),
inventory_item_id_old = l_v_item_old_id(i)
where record_id = l_v_record_id(i);
select 1
into l_xst
from wsm_copy_op_networks
where wip_entity_id = p_wip_entity_id
and from_op_seq_num = l_fm_op
and to_op_seq_num = l_to_op;
select recommended,
reco_start_date,
reco_completion_date
into l_recommended,
l_op_start_date_old,
l_op_compl_date_old
from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_rtg_op_seq_num;
select first_unit_start_date,
last_unit_start_date
into l_op_start_date_old,
l_op_compl_date_old
from wip_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_job_op_seq_num;
select resource_id
from wsm_copy_op_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_rtg_op_seq_num
and NVL(substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
and NVL(replacement_group_num, 0) = NVL(l_rpl_grp_num, 0)
and parent_resource_seq_num IS NULL; -- ST : Detailed Scheduling.. Consider only the runtime resources
select resource_id
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_job_op_seq_num
and NVL(substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
and NVL(replacement_group_num, 0) = NVL(l_rpl_grp_num, 0)
and parent_resource_seq IS NULL -- -- ST : Detailed Scheduling.. Consider only the runtime resources
union
select resource_id
from wip_sub_operation_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_job_op_seq_num
and NVL(substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
and NVL(replacement_group_num, 0) = NVL(l_rpl_grp_num, 0);
select wo.first_unit_start_date,
wo.last_unit_completion_date
into l_op_sdate,
l_op_edate
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = l_job_op_seq_num;
select reco_start_date,
reco_completion_date
into l_op_sdate,
l_op_edate
from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_rtg_op_seq_num;
if(p_substitution_type = 3) then -- update only
l_resource_id := p_v_res(l_i).resource_id;
select 1
into l_xst
from dual
where exists (
select resource_id
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = l_job_op_seq_num
and wor.resource_id = l_resource_id
and NVL(wor.substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
and NVL(wor.replacement_group_num, 0) = NVL(l_rpl_grp_num, 0)
) or exists (
select resource_id
from wip_sub_operation_resources wsor
where wsor.wip_entity_id = p_wip_entity_id
and wsor.operation_seq_num = l_job_op_seq_num
and wsor.resource_id = l_resource_id
and NVL(wsor.substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
and NVL(wsor.replacement_group_num, 0) = NVL(l_rpl_grp_num, 0)
);
select 1
into l_xst
from wsm_copy_op_resources wcor
where wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = l_rtg_op_seq_num
and wcor.resource_id = l_resource_id
and NVL(wcor.substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
and NVL(wcor.replacement_group_num, 0) = NVL(l_rpl_grp_num, 0);
select bst.setup_id
into l_setup_id
from BOM_SETUP_TYPES bst
where bst.setup_code = l_setup_code
and bst.organization_id = p_org_id;
select 1
into l_xst
from bom_resource_setups brs
where brs.resource_id = l_resource_id
and brs.organization_id = p_org_id
and brs.setup_id = l_setup_id;
select br.batchable
into l_xst
from bom_resources br
where resource_id = l_resource_id;
select bst.setup_id
into l_setup_id
from bom_setup_types bst
where bst.setup_code = l_setup_code
and bst.organization_id = p_org_id;
select 1
into l_xst
from bom_resource_setups brs
where brs.resource_id = l_resource_id
and brs.organization_id = p_org_id
and brs.setup_id = l_setup_id;
select br.batchable
into l_xst
from bom_resources br
where resource_id = l_resource_id;
elsif(p_substitution_type = WSM_SUB_DEL) then -- Delete only
l_resource_id := p_v_res(l_i).resource_id;
select 1,applied_resource_units
into l_xst,l_app_res_units
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = l_job_op_seq_num
and wor.resource_seq_num = l_res_seq_num;
select 1,applied_resource_units
into l_xst,l_app_res_units
from wip_sub_operation_resources wsor
where wsor.wip_entity_id = p_wip_entity_id
and wsor.operation_seq_num = l_job_op_seq_num
and wsor.resource_seq_num = l_res_seq_num;
select 1
into l_xst
from dual
where exists ( select transaction_id
from wip_transactions wt
where wt.wip_entity_id = p_wip_entity_id
and wt.organization_id = p_org_id
AND wt.operation_seq_num = l_job_op_seq_num
and wt.resource_seq_num = l_res_seq_num
)
or exists ( select transaction_id
from wip_cost_txn_interface wcti
where wcti.wip_entity_id = p_wip_entity_id
AND wcti.organization_id = p_org_id
and wcti.operation_seq_num = l_job_op_seq_num
and wcti.resource_seq_num = l_res_seq_num
);
select 1
into l_xst
from dual
where exists ( select 'PO/REQ Linked'
from po_releases_all pr,
po_headers_all ph,
po_distributions_all pd,
po_line_locations_all pll
where pd.po_line_id IS NOT NULL
and pd.line_location_id IS NOT NULL
and pd.wip_entity_id = p_wip_entity_id
and pd.destination_organization_id = p_org_id
and pd.wip_operation_seq_num = l_job_op_seq_num
and pd.wip_resource_seq_num = l_res_seq_num
and ph.po_header_id = pd.po_header_id
and pll.line_location_id = pd.line_location_id
and pr.po_release_id (+) = pd.po_release_id
and ( pll.cancel_flag IS NULL OR
pll.cancel_flag = 'N')
and (pll.quantity_received < (pll.quantity-pll.quantity_cancelled))
and nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED'
UNION ALL
select 'PO/REQ Linked'
from po_requisition_lines_all prl
where prl.wip_entity_id = p_wip_entity_id
and prl.destination_organization_id = p_org_id
and prl.wip_operation_seq_num = l_job_op_seq_num
and prl.wip_resource_seq_num = l_res_seq_num
and nvl(PRL.cancel_flag, 'N') = 'N'
and prl.line_location_id is NULL
UNION ALL
select 'PO/REQ Linked'
from po_requisitions_interface_all pri
where pri.wip_entity_id = p_wip_entity_id
and pri.destination_organization_id = p_org_id
and pri.wip_operation_seq_num = l_job_op_seq_num
and pri.wip_resource_seq_num = l_res_seq_num
);
select 1
into l_xst
from wsm_copy_op_resources wcor
where wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = l_rtg_op_seq_num
and wcor.resource_seq_num = l_res_seq_num;
select unit_of_measure,
default_basis_type,
default_activity_id,
autocharge_type,
standard_rate_flag,
batchable,
resource_code
into l_res_unit_of_measure,
l_res_default_basis_type,
l_res_default_activity_id,
l_res_autocharge_type,
l_res_standard_rate_flag,
l_res_batchable,
l_res_code
from bom_resources
where resource_id=l_resource_id
and organization_id=p_org_id
and nvl(disable_date,sysdate+1) > sysdate;
select wo.first_unit_start_date,
wo.last_unit_completion_date
into l_op_sdate,
l_op_edate
from wip_operations wo,
bom_department_resources bdr
where wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = l_job_op_seq_num
and wo.organization_id = p_org_id
and bdr.resource_id = l_resource_id
and bdr.department_id = wo.department_id;
select greatest(nvl(max(wor.resource_seq_num),0),nvl(max(wsor.resource_seq_num),0))+10
into l_res_seq_num
from wip_operation_resources wor,
wip_sub_operation_resources wsor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = l_job_op_seq_num
and wor.organization_id = wsor.organization_id
and wor.wip_entity_id = wsor.wip_entity_id
and wor.operation_seq_num = wsor.operation_seq_num;
select 1
into l_xst
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = l_job_op_seq_num
and wor.resource_seq_num =l_res_seq_num;
select 'N'
into p_v_res(l_i).reco_flag
from dual
where exists( select 1
from wip_sub_operation_resources wsor
where wsor.organization_id = p_org_id
and wsor.wip_entity_id = p_wip_entity_id
and wsor.operation_seq_num = l_job_op_seq_num
and nvl(wsor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
and nvl(wsor.replacement_group_num,0) = nvl(p_v_res(l_i).rpl_grp_num,0));
select 'Y'
into p_v_res(l_i).reco_flag
from dual
where exists( select 1
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = l_job_op_seq_num
and nvl(wor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
and nvl(wor.replacement_group_num,0) = nvl(p_v_res(l_i).rpl_grp_num,0));
select 'N'
into p_v_res(l_i).reco_flag
from dual
where exists( select 1
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = l_job_op_seq_num
and nvl(wor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1));
select reco_start_date,
reco_completion_date
into l_op_sdate,
l_op_edate
from wsm_copy_operations wco,
bom_department_resources bdr
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_rtg_op_seq_num
and organization_id = p_org_id
and bdr.resource_id = l_resource_id
and bdr.department_id =wco.department_id;
select nvl(max(resource_seq_num),0)+10
into l_res_seq_num
from wsm_copy_op_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = l_rtg_op_seq_num;
select 1
into l_xst
from wsm_copy_op_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = l_rtg_op_seq_num
and wor.resource_seq_num =l_res_seq_num;
select 'N'
into p_v_res(l_i).reco_flag
from dual
where exists( select 1
from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = l_rtg_op_seq_num
and nvl(wcor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
and nvl(wcor.replacement_group_num,0) = nvl(p_v_res(l_i).rpl_grp_num,0)
and wcor.recommended = 'N');
select 'Y'
into p_v_res(l_i).reco_flag
from dual
where exists( select 1
from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = l_rtg_op_seq_num
and nvl(wcor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
and nvl(wcor.replacement_group_num,0) = nvl(p_v_res(l_i).rpl_grp_num,0)
and wcor.recommended = 'Y');
select 'N'
into p_v_res(l_i).reco_flag
from dual
where exists( select 1
from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = l_rtg_op_seq_num
and nvl(wcor.substitute_group_num,-1) = nvl(p_v_res(l_i).sub_grp_num,-1)
and wcor.recommended = 'Y');
select resource_seq_num
into l_res_seq_num
from wsm_copy_op_resources WCOR
where WCOR.wip_entity_id = p_wip_entity_id
and WCOR.organization_id = p_org_id
and WCOR.operation_seq_num = l_rtg_op_seq_num
and WCOR.parent_resource_seq_num = p_parent_res_seq_num
and WCOR.resource_id = p_resource_id;
select resource_seq_num
into l_res_seq_num
from wip_operation_resources WOR
where WOR.wip_entity_id = p_wip_entity_id
and WOR.organization_id = p_org_id
and WOR.operation_seq_num = l_job_op_seq_num
and WOR.parent_resource_seq = p_parent_res_seq_num
and WOR.resource_id = p_resource_id;
select 1
into l_exists
from wsm_copy_op_resource_instances
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_rtg_op_seq_num
and resource_seq_num = l_res_seq_num;
select 1
into l_exists
from wip_op_resource_instances
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and resource_seq_num = l_res_seq_num;
select wco.recommended,wco.department_id
into l_op_recommended,l_department_id
from wsm_copy_operations wco
where wco.wip_entity_id = p_wip_entity_id
and wco.operation_seq_num = l_rtg_op_seq_num;
select reco_start_date,
reco_completion_date,
resource_id,
recommended
into l_res_sdate,
l_res_edate,
l_resource_id, -- this is used for validate instance_id
l_res_recommended
from wsm_copy_op_resources
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_rtg_op_seq_num
and resource_seq_num = l_res_seq_num;
select wor.start_date,
wor.completion_date,
wor.resource_id,
wo.department_id
into l_res_sdate,
l_res_edate,
l_resource_id, -- this is used for validate instance_id
l_department_id /*Added Department Id for bug 5476647*/
from wip_operation_resources wor, wip_operations wo
where wor.wip_entity_id = p_wip_entity_id
and wor.organization_id = p_org_id
and wor.operation_seq_num = l_job_op_seq_num
and wor.resource_seq_num = l_res_seq_num
and wor.wip_entity_id = wo.wip_entity_id
and wor.organization_id = wo.organization_id
and wor.operation_seq_num = wo.operation_seq_num;
select 1
into l_exists
from bom_dept_res_instances bdri
where bdri.resource_id = l_resource_id
and bdri.department_id = l_department_id
and bdri.instance_id = l_instance_id
and NVL(bdri.serial_number, g_nvl_str) = NVL(l_serial_number, g_nvl_str);
select 1
into l_exists
from dual
where exists( select instance_id
from bom_dept_res_instances bdri
where bdri.resource_id = l_resource_id
and bdri.instance_id = l_instance_id
and NVL(bdri.serial_number, g_nvl_str) = NVL(l_serial_number, g_nvl_str) );
select resource_seq_num
into l_res_seq_num
from wsm_copy_op_resources WCOR
where WCOR.wip_entity_id = p_wip_entity_id
and WCOR.organization_id = p_org_id
and WCOR.operation_seq_num = l_rtg_op_seq_num
and WCOR.parent_resource_seq_num = p_parent_res_seq_num
and WCOR.resource_id = p_resource_id;
select resource_seq_num
into l_res_seq_num
from wip_operation_resources WOR
where WOR.wip_entity_id = p_wip_entity_id
and WOR.organization_id = p_org_id
and WOR.operation_seq_num = l_job_op_seq_num
and WOR.parent_resource_seq = p_parent_res_seq_num
and WOR.resource_id = p_resource_id;
select 1
into l_exists
from wsm_copy_op_resource_usage
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_rtg_op_seq_num
and resource_seq_num = l_res_seq_num
and NVL(instance_id, -1) = NVL(l_instance_id, -1)
and NVL(serial_number, g_nvl_str) = NVL(l_serial_num, g_nvl_str);
select 1
into l_exists
from wip_operation_resource_usage
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and resource_seq_num = l_res_seq_num
and NVL(instance_id, -1) = NVL(l_instance_id, -1)
and NVL(serial_number, g_nvl_str) = NVL(l_serial_num, g_nvl_str);
select wco.recommended,
wcor.recommended,
wcor.reco_start_date,
wcor.reco_completion_date
into l_op_recommended,
l_res_recommended,
l_the_sdate,
l_the_edate
from wsm_copy_operations wco,
wsm_copy_op_resources wcor
where wco.wip_entity_id = p_wip_entity_id
and wco.operation_seq_num = l_rtg_op_seq_num
and wcor.wip_entity_id = wco.wip_entity_id
and wcor.operation_seq_num = wco.operation_seq_num
and wcor.resource_seq_num = l_res_seq_num;
select 1
into l_exists
from wsm_copy_op_resource_instances wcori
where wcori.wip_entity_id = p_wip_entity_id
and wcori.organization_id = p_org_id
and wcori.operation_seq_num = l_rtg_op_seq_num
and wcori.resource_seq_num = l_res_seq_num;
select wcori.start_date,
wcori.completion_date
into l_the_sdate,
l_the_edate
from wsm_copy_op_resource_instances wcori
where wcori.wip_entity_id = p_wip_entity_id
and wcori.organization_id = p_org_id
and wcori.operation_seq_num = l_rtg_op_seq_num
and wcori.resource_seq_num = l_res_seq_num
and wcori.instance_id = l_instance_id
and NVL(wcori.serial_number, g_nvl_str) = NVL(l_serial_num, g_nvl_str);
select 1
into l_exists
from wip_op_resource_instances wori
where wori.wip_entity_id = p_wip_entity_id
and wori.organization_id = p_org_id
and wori.operation_seq_num = l_job_op_seq_num /* l_rtg_op_seq_num : ST : This has to be job op seq num */
and wori.resource_seq_num = l_res_seq_num;
select start_date,
completion_date
into l_the_sdate,
l_the_edate
from wip_op_resource_instances
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and resource_seq_num = l_res_seq_num
and instance_id = l_instance_id
and NVL(serial_number, g_nvl_str) = NVL(l_serial_num, g_nvl_str);
select start_date,
completion_date
into l_the_sdate,
l_the_edate
from wip_operation_resources wor
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and resource_seq_num = l_res_seq_num;
select component_sequence_id,
bill_quantity_per_assembly -- bug 3347985, was 'quantity_per_assembly'
from wsm_copy_requirement_ops
where wip_entity_id = c_wip_entity_id
and operation_seq_num = c_rtg_op_seq_num
and source_phantom_id = c_src_phm_item_id
order by component_sequence_id;
select wo.first_unit_start_date,
wo.last_unit_completion_date
into l_op_sdate,
l_op_edate
from wip_operations wo,
wip_requirement_operations wro
where wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = l_job_op_seq_num
and wro.wip_entity_id = wo.wip_entity_id
and wro.operation_seq_num = wo.operation_seq_num
and wro.inventory_item_id = l_item_id;
select wco.reco_start_date,
wco.reco_completion_date,
wco.recommended,
wco.reco_scheduled_quantity
into l_op_sdate,
l_op_edate,
l_op_recommended,
l_scheduled_qty
from wsm_copy_operations wco
where wco.wip_entity_id = p_wip_entity_id
and wco.operation_seq_num = l_rtg_op_seq_num;
select wco.reco_start_date,
wco.reco_completion_date,
wco.recommended,
wco.reco_scheduled_quantity,
wcro.bill_quantity_per_assembly -- bug 3347985 was 'quantity_per_assembly'
into l_op_sdate,
l_op_edate,
l_op_recommended,
l_scheduled_qty,
l_bom_qpa
from wsm_copy_operations wco,
wsm_copy_requirement_ops wcro
where wco.wip_entity_id = p_wip_entity_id
and wco.operation_seq_num = l_rtg_op_seq_num
and wcro.wip_entity_id = wco.wip_entity_id
and wcro.operation_seq_num = wco.operation_seq_num
and wcro.component_item_id = l_item_id
and wcro.primary_component_id = l_primary_item_id
and wcro.source_phantom_id = -1; -- is NULL;
select 1
into l_comp_exist
from mtl_system_items_kfv
where organization_id = p_org_id
and inventory_item_id=l_item_old_id;
select wro.quantity_issued
into l_quantity_issued
from wip_requirement_operations wro
where wro.wip_entity_id = p_wip_entity_id
and wro.operation_seq_num = l_job_op_seq_num
and wro.inventory_item_id = l_item_old_id
and wro.organization_id = p_org_id;
select 1
into l_temp
from dual
where exists
(select 1
from wip_requirement_operations wro
where wro.wip_entity_id = p_wip_entity_id
and wro.organization_id =p_org_id
and wro.operation_seq_num = l_job_op_seq_num
and wro.inventory_item_id = l_item_old_id
and wro.quantity_issued <> 0)
or exists
(select 1
from mtl_material_transactions_temp mmtt
where mmtt.transaction_source_id = p_wip_entity_id
and mmtt.organization_id = p_org_id
and mmtt.operation_seq_num = l_job_op_seq_num
and mmtt.inventory_item_id = l_item_old_id);
select 1
into l_comp_exist
from mtl_system_items_kfv
where organization_id = p_org_id
and inventory_item_id=l_item_old_id;
select 1
into l_phan_exist
from dual
where exists (select component_item_id
from wsm_copy_requirement_ops
where wip_entity_id = p_wip_entity_id
and source_phantom_id = l_item_old_id
and component_item_id <>l_item_old_id
and operation_seq_num = l_rtg_op_seq_num);
select 1
into l_comp_exist
from dual
where exists ( select 1
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and job_type = WIP_CONSTANTS.STANDARD
and primary_item_id = l_item_id );
select 1
into l_comp_exist
from mtl_system_items_kfv
where inventory_item_id=l_item_id
and organization_id=p_org_id;
select 1
into l_comp_duplicate
from wip_requirement_operations wro
where wro.wip_entity_id = p_wip_entity_id
and wro.operation_seq_num =l_job_op_seq_num
and wro.organization_id=p_org_id
and wro.inventory_item_id=l_item_id;
select wo.first_unit_start_date,
wo.last_unit_completion_date
into l_op_sdate,
l_op_edate
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = l_job_op_seq_num;
select wip_supply_subinventory, wip_supply_locator_id
into l_supply_subinventory, l_supply_locator_id
from mtl_system_items
where inventory_item_id = l_item_id
and organization_id = p_org_id;
select default_pull_supply_subinv, default_pull_supply_locator_id
into l_supply_subinventory, l_supply_locator_id
from wip_parameters
where organization_id = p_org_id;
select 1
into l_comp_exist
from mtl_system_items_kfv
where inventory_item_id=l_item_id
and organization_id=p_org_id;
select 1
into l_comp_duplicate
from wsm_copy_requirement_ops wcro
where wcro.wip_entity_id = p_wip_entity_id
and wcro.organization_id=p_org_id
and wcro.component_item_id=l_item_id
and (wcro.operation_seq_num =l_rtg_op_seq_num or
wcro.operation_seq_num = -1*l_rtg_op_seq_num);
select 1
into l_comp_duplicate
from wsm_copy_requirement_ops wcro
where wcro.wip_entity_id = p_wip_entity_id
and wcro.organization_id=p_org_id
and wcro.component_item_id=l_src_phm_item_id
and wcro.operation_seq_num = -1*l_rtg_op_seq_num;
select wco.reco_start_date,wco.reco_completion_date
into l_op_sdate,l_op_edate
from wsm_copy_operations wco
where wco.wip_entity_id = p_wip_entity_id
and wco.organization_id = p_org_id
and wco.operation_seq_num = l_rtg_op_seq_num;
select wip_supply_subinventory, wip_supply_locator_id
into l_supply_subinventory, l_supply_locator_id
from mtl_system_items
where inventory_item_id = l_item_id
and organization_id = p_org_id;
select default_pull_supply_subinv, default_pull_supply_locator_id
into l_supply_subinventory, l_supply_locator_id
from wip_parameters
where organization_id = p_org_id;
procedure update_cumulative_time (
p_wip_entity_id in number,
p_operation_seq_num in number,
p_resource_seq_num in number,
p_instance_id in number,
p_serial_num in varchar2,
p_handle_wip_cur_op in varchar2 )IS
cursor wip_res_usage (p_wip_entity_id number,
p_operation_seq_num number,
p_resource_seq_num number) is
select start_date,
completion_date,
cumulative_processing_time
from wip_operation_resource_usage
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num
and NVL(instance_id, -1) = NVL(p_instance_id, -1)
and NVL(serial_number, g_nvl_str) = NVL(p_serial_num, g_nvl_str)
order by start_date for update;
select start_date,
completion_date,
cumulative_processing_time
from wsm_copy_op_resource_usage
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num
and NVL(instance_id, -1) = NVL(p_instance_id, -1)
and NVL(serial_number, g_nvl_str) = NVL(p_serial_num, g_nvl_str)
order by start_date for update;
update wip_operation_resource_usage
set cumulative_processing_time = current_cpt
where current of wip_res_usage;
update wsm_copy_op_resource_usage
set cumulative_processing_time = current_cpt
where current of wsm_res_usage;
end update_cumulative_time;
select header_id,
group_id,
organization_id,
organization_code,
wip_entity_id,
job_name,
load_type,
process_status,
error_code,
error_msg,
num_of_children
from wsm_lot_job_interface
where process_status = WIP_CONSTANTS.PENDING
and NVL(transaction_date, creation_date) <= sysdate+1
and NVL(group_id, -99) = NVL(p_group_id, NVL(group_id, -99))
and load_type in (5,6)
order by load_type;
select record_id,
group_id,
organization_id,
organization_code,
wip_entity_id,
job_name,
process_status,
error_code,
error_msg
from wsm_lot_job_dtl_interface
where process_status = WIP_CONSTANTS.PENDING
and parent_header_id IS NULL
and NVL(group_id, -99) = NVL(p_group_id, NVL(group_id, -99))
and transaction_date <= sysdate+1;
6, -- Job update
v_wljdi_org_id(l_d),
v_wljdi_wip_entity_id(l_d),
v_wljdi_job_name(l_d),
l_err_code,
l_err_msg);
update wsm_lot_job_dtl_interface
set process_status = v_wlji_process_status(l_h),
group_id = p_group_id,
organization_id = v_wlji_org_id(l_h),
organization_code = v_wlji_org_code(l_h),
wip_entity_id = v_wlji_wip_entity_id(l_h),
job_name = v_wlji_job_name(l_h),
-- BUG3600450 commented
--error_code = v_wlji_err_code(l_h),
--error_msg = 'See header (header_id=' ||
-- v_wlji_header_id(l_h) || ') for error message',
program_id = l_prog_id,
program_update_date = sysdate
where parent_header_id = v_wlji_header_id(l_h);
update wsm_lot_job_interface
set process_status = v_wlji_process_status(l_h),
organization_id = v_wlji_org_id(l_h),
organization_code = v_wlji_org_code(l_h),
wip_entity_id = v_wlji_wip_entity_id(l_h),
job_name = v_wlji_job_name(l_h),
error_code = v_wlji_err_code(l_h),
error_msg = v_wlji_err_msg(l_h),
num_of_children = v_wlji_num_of_children(l_h),
program_id = l_prog_id,
program_update_date = sysdate
where header_id = v_wlji_header_id(l_h);
update wsm_lot_job_dtl_interface
set process_status = v_wljdi_process_status(l_d),
organization_id = v_wljdi_org_id(l_d),
organization_code = v_wljdi_org_code(l_d),
wip_entity_id = v_wljdi_wip_entity_id(l_d),
job_name = v_wljdi_job_name(l_d),
error_code = v_wljdi_err_code(l_d),
error_msg = v_wljdi_err_msg(l_d),
program_id = l_prog_id,
program_update_date = sysdate
where record_id = v_wljdi_record_id(l_d);
select wsm_lot_job_interface_s.NEXTVAL
into l_group_id
from dual;
select wsm_lot_job_interface_s.NEXTVAL
into l_group_id
from dual;
update wsm_lot_job_dtl_interface
set process_status = v_wlji_process_status(l_h),
group_id = v_wlji_group_id(l_h),
organization_id = v_wlji_org_id(l_h),
organization_code = v_wlji_org_code(l_h),
wip_entity_id = v_wlji_wip_entity_id(l_h),
job_name = v_wlji_job_name(l_h),
error_code = v_wlji_err_code(l_h),
error_msg = v_wlji_err_msg(l_h),
program_id = l_prog_id,
program_update_date = sysdate
where parent_header_id = v_wlji_header_id(l_h);
update wsm_lot_job_interface
set process_status = v_wlji_process_status(l_h),
group_id = v_wlji_group_id(l_h), -- assigned gid
organization_id = v_wlji_org_id(l_h),
organization_code = v_wlji_org_code(l_h),
wip_entity_id = v_wlji_wip_entity_id(l_h),
job_name = v_wlji_job_name(l_h),
error_code = v_wlji_err_code(l_h),
error_msg = v_wlji_err_msg(l_h),
num_of_children = v_wlji_num_of_children(l_h),
program_id = l_prog_id,
program_update_date = sysdate
where header_id = v_wlji_header_id(l_h);
update wsm_lot_job_dtl_interface
set process_status = v_wljdi_process_status(l_d),
group_id = v_wljdi_group_id(l_d), -- assigned gid
organization_id = v_wljdi_org_id(l_d),
organization_code = v_wljdi_org_code(l_d),
wip_entity_id = v_wljdi_wip_entity_id(l_d),
job_name = v_wljdi_job_name(l_d),
error_code = v_wljdi_err_code(l_d),
error_msg = v_wljdi_err_msg(l_d),
program_id = l_prog_id,
program_update_date = sysdate
where record_id = v_wljdi_record_id(l_d);
select error_code,
error_msg,
--error,
last_update_date,
request_id,
program_id,
program_application_id,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_update_date,
last_updated_by_name,
created_by_name,
organization_id,
organization_code,
wip_entity_id,
job_name,
lot_number,
primary_item_id,
header_id,
process_status,
--process_phase,
routing_reference_id,
completion_subinventory,
completion_locator_id,
mode_flag,
group_id,
load_type,
status_type,
--old_start_quantity,
--old_completion_date,
bom_reference_id,
bom_revision_date,
routing_revision_date,
wip_supply_type,
class_code,
description,
firm_planned_flag,
alternate_routing_designator,
alternate_bom_designator,
demand_class,
start_quantity,
first_unit_start_date,
--first_unit_completion_date,
--last_unit_start_date,
last_unit_completion_date,
scheduling_method,
routing_revision,
bom_revision,
schedule_group_id,
schedule_group_name,
build_sequence,
net_quantity,
allow_explosion,
--old_status_type,
--interface_id,
coproducts_supply,
source_code,
source_line_id,
process_type,
--processing_work_days,
--daily_production_rate,
line_id,
--line_code,
--lot_control_code,
--repetitive_schedule_id,
--parent_group_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
--primary_item_segments,
--bom_reference_segments,
--routing_reference_segments,
--completion_locator_segments,
--descriptive_flex_segments,
project_id,
--project_name,
--project_number,
--project_costed,
task_id,
--task_name,
--task_number,
delivery_id,
end_item_unit_number,
overcompletion_tolerance_type,
overcompletion_tolerance_value,
kanban_card_id,
priority,
due_date,
job_type,
date_released,
transaction_date,
num_of_children
--, NVL(infinite_schedule_flag, 'N')
from wsm_lot_job_interface
where group_id = p_group_id
and process_status = 2 -- WIP_CONSTANTS.RUNNING;
select unique
wip_entity_id,
organization_id
from wsm_lot_job_dtl_interface
where process_status = WIP_CONSTANTS.RUNNING
and parent_header_id IS NULL
and group_id = p_group_id
and transaction_date <= sysdate+1
order by organization_id;
l_skip_update_wo number;
e_update_job exception;
select mtl_material_transactions_s.nextval
into l_mmtt_txn_hdr_id
from dual;
select wsm_lot_job_interface_s.NEXTVAL
into l_batch_grp_id
from dual;
update wsm_lot_job_interface
set group_id = l_batch_grp_id
where header_id = v_wlji_header_id(indx);
update wsm_lot_job_dtl_interface
set group_id = l_batch_grp_id
where parent_header_id = v_wlji_header_id(l_h);
l_skip_update_wo:=0;
select nvl(OP_SEQ_NUM_INCREMENT, 10)
into g_op_seq_incr
from wsm_parameters
where ORGANIZATION_ID = v_wlji_org_id(v_idx);
elsif v_wlji_load_type(v_idx) = 6 then -- Job update
if (v_wlji_wip_entity_id(v_idx) is null) then
begin
l_stmt_num := 140.1;
select wip_entity_id
into v_wlji_wip_entity_id(v_idx)
from wip_entities
where wip_entity_name = v_wlji_job_name(v_idx)
and organization_id = v_wlji_org_id(v_idx);
select decode(wdj.status_type, WIP_CONSTANTS.UNRELEASED, NVL(v_wlji_job_name(v_idx),we.wip_entity_name), we.wip_entity_name)
into v_wlji_job_name(v_idx)
from wip_discrete_jobs wdj,
wip_entities we
where wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx)
and wdj.status_type in (
WIP_CONSTANTS.UNRELEASED,
WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.HOLD,
WIP_CONSTANTS.CANCELLED)
and wdj.wip_entity_id = we.wip_entity_id
and we.organization_id = v_wlji_org_id(v_idx);
select primary_item_id,
class_code,
scheduled_start_date,
scheduled_completion_date,
start_quantity,
net_quantity,
status_type,
firm_planned_flag,
job_type,
bom_reference_id,
alternate_bom_designator,
routing_reference_id,
alternate_routing_designator,
bom_revision_date,
routing_revision_date,
bom_revision,
routing_revision,
common_routing_sequence_id,
common_bom_sequence_id,
wip_supply_type,
scheduled_start_date,
scheduled_completion_date,
coproducts_supply,
kanban_card_id,
completion_subinventory,
completion_locator_id,
date_released,
schedule_group_id,
build_sequence,
line_id
into l_cur_primary_item_id,
l_cur_class_code,
l_cur_start_date,
l_cur_complete_date,
l_cur_start_qty,
l_cur_net_qty,
l_cur_status_type,
l_cur_firm_planned_flag,
l_cur_job_type,
l_cur_bom_ref_id,
l_cur_alt_bom,
l_cur_rtg_ref_id,
l_cur_alt_rtg,
l_cur_bom_rev_date,
l_cur_rtg_rev_date,
l_cur_bom_revision,
l_cur_rtg_revision,
l_cur_com_rtg_seq_id,
l_cur_com_bom_seq_id,
l_cur_wip_supply_type,
l_cur_schd_start_date,
l_cur_schd_compl_date,
l_cur_coprod_supply,
l_cur_kanban_card_id,
l_cur_compl_subinv,
l_cur_compl_locator,
l_cur_date_released,
l_cur_schedule_group_id,
l_cur_build_sequence,
l_cur_line_id
from wip_discrete_jobs
where wip_entity_id = v_wlji_wip_entity_id(v_idx)
and organization_id = v_wlji_org_id(v_idx);
insert into wip_schedule_groups (
schedule_group_id,
schedule_group_name,
organization_id,
description,
created_by,
last_updated_by,
creation_date,
last_update_date)
select wip_schedule_groups_s.nextval,
wds.name,
v_wlji_org_id(v_idx),
to_char(sysdate),
l_user,
l_user,
sysdate,
sysdate
from wsh_new_deliveries wds
where wds.delivery_id = v_wlji_delivery_id(v_idx)
and not exists (
select 1
from wip_schedule_groups wsg
where wsg.organization_id = v_wlji_org_id(v_idx)
and wsg.schedule_group_name = wds.name);
select wsg.schedule_group_name,
wsg.schedule_group_id
into v_wlji_schedule_group_name(v_idx),
v_wlji_schedule_group_id(v_idx)
from wip_schedule_groups wsg,
wsh_new_deliveries wds
where wds.delivery_id = v_wlji_delivery_id(v_idx)
and wsg.schedule_group_name = wds.name
and wsg.organization_id = v_wlji_org_id(v_idx);
select 1
into l_temp_num
from wip_schedule_groups_val_v wsg
where wsg.schedule_group_id = v_wlji_schedule_group_id(v_idx)
and wsg.organization_id = v_wlji_org_id(v_idx);
select 1
into l_num_records
from wsm_starting_lots_interface wsli,
wsm_lot_split_merges_interface wlsmi
where wsli.lot_number = v_wlji_job_name(v_idx)
and wsli.header_id = wlsmi.header_id
and wlsmi.process_status in (1,2);
select 1
into l_num_records
from fnd_user
where user_id = v_wlji_last_updt_by(v_idx)
and sysdate between start_date and nvl(end_date,sysdate+1);
'Last Updated By', l_aux_mesg, l_stmt_num);
select 1
into l_num_records
from fnd_user
where user_id = v_wlji_created_by(v_idx)
and sysdate between start_date and nvl(end_date,sysdate+1);
select 1
into l_num_records
from mtl_system_items_kfv msi
where msi.inventory_item_id = v_wlji_item_id(v_idx)
and msi.organization_id = v_wlji_org_id(v_idx)
and msi.lot_control_code = 2;
select 1
into l_num_records
from mtl_system_items_kfv msi
where msi.inventory_item_id = v_wlji_item_id(v_idx)
and msi.organization_id = v_wlji_org_id(v_idx)
-- ST : Serial Support Project -----------------------------
-- In OSFM we do support Serial controlled assemblies.......
and msi.serial_number_control_code IN (1,2);
select msi.serial_number_control_code
into l_serial_ctrl_code
from mtl_system_items_kfv msi
where msi.inventory_item_id = v_wlji_item_id(v_idx)
and msi.organization_id = v_wlji_org_id(v_idx);
select bor.routing_sequence_id,
bor.COMPLETION_SUBINVENTORY,
bor.COMPLETION_LOCATOR_ID
into l_rtg_seq_id,
l_default_compl_subinv,
l_default_compl_loc_id
from bom_operational_routings bor,
bom_alternate_designators bad
where ((bor.alternate_routing_designator is null and
bad.alternate_designator_code is null and
bad.organization_id = -1) or
(bor.alternate_routing_designator
= bad.alternate_designator_code and
bor.organization_id = bad.organization_id))
and bor.organization_id = v_wlji_org_id(v_idx)
and bor.assembly_item_id = v_wlji_item_id(v_idx)
and NVL(bor.alternate_routing_designator, '&*')
= NVL(v_wlji_alt_rtg(v_idx), '&*')
and bor.routing_type = 1
and bor.cfm_routing_flag = 3;
select locator_type
into l_mtl_locator_type
from mtl_secondary_inventories
where secondary_inventory_name
= v_wlji_compl_subinv(v_idx)
and organization_id = v_wlji_org_id(v_idx);
SELECT bom.common_bill_sequence_id
INTO l_common_bom_seq_id
FROM bom_bill_of_materials bom
WHERE bom.alternate_bom_designator is NULL
AND BOM.assembly_item_id = v_wlji_item_id(v_idx)
AND bom.organization_id = v_wlji_org_id(v_idx);
SELECT bom.common_bill_sequence_id
INTO l_common_bom_seq_id
FROM bom_bill_of_materials bom,
bom_alternate_designators bad
WHERE ((bom.alternate_bom_designator is null and
bad.alternate_designator_code is null and
bad.organization_id = -1) OR
(bom.alternate_bom_designator
= bad.alternate_designator_code and
bom.organization_id = bad.organization_id))
AND bom.alternate_bom_designator
= v_wlji_alt_bom(v_idx)
AND BOM.assembly_item_id = v_wlji_item_id(v_idx)
AND bom.organization_id = v_wlji_org_id(v_idx);
SELECT nvl(msub.locator_type, 1) sub_loc_control,
MP.stock_locator_control_code org_loc_control,
MS.restrict_locators_code,
MS.location_control_code item_loc_control
into l_sub_loc_control,
l_org_loc_control,
l_restrict_locators_code,
l_item_loc_control
FROM mtl_system_items MS,
mtl_secondary_inventories MSUB,
mtl_parameters MP
WHERE MP.organization_id = v_wlji_org_id(v_idx)
AND MS.organization_id = v_wlji_org_id(v_idx)
AND MS.inventory_item_id = v_wlji_item_id(v_idx)
AND MSUB.secondary_inventory_name
= v_wlji_compl_subinv(v_idx)
AND MSUB.organization_id = v_wlji_org_id(v_idx);
select wse.default_acct_class_code
into v_wlji_class_code(v_idx)
from wsm_sector_extensions wse,
wsm_item_extensions wie
where wie.inventory_item_id = v_wlji_item_id(v_idx)
and wie.organization_id = v_wlji_org_id(v_idx)
and wie.sector_extension_id = wse.sector_extension_id
and wie.organization_id = wse.organization_id;
select wse.default_acct_class_code
into v_wlji_class_code(v_idx)
from wsm_sector_extensions wse,
wsm_subinventory_extensions wve
where wve.secondary_inventory_name
= v_wlji_compl_subinv(v_idx)
and wve.organization_id = v_wlji_org_id(v_idx)
and wve.sector_extension_id = wse.sector_extension_id
and wve.organization_id = wse.organization_id;
v_wsli(v_wlji_src_line_id(v_idx)).last_updated_by);
select 1
into l_num_records
from fnd_user
where user_id = v_wsli(v_wlji_src_line_id(v_idx)).last_updated_by
and sysdate between start_date and nvl(end_date,sysdate+1);
'Last Updated By in WSM_STARTING_LOTS_INTERFACE',
l_aux_mesg,
l_stmt_num);
WSMPLCVA.v_user(v_wsli(v_wlji_src_line_id(v_idx)).last_updated_by)
:= v_wsli(v_wlji_src_line_id(v_idx)).last_updated_by;
select 1
into l_num_records
from fnd_user
where user_id = v_wsli(v_wlji_src_line_id(v_idx)).created_by
and sysdate between start_date and nvl(end_date,sysdate+1);
select 1
into l_temp_num
from mtl_transaction_lots_temp
where lot_number = v_wsli(v_wlji_src_line_id(v_idx)).lot_number
and rownum = 1;
select 0
into l_temp_num
from mtl_material_transactions_temp mmtt
where mmtt.organization_id = v_wsli(v_wlji_src_line_id(v_idx)).organization_id
and mmtt.inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
and nvl(mmtt.lot_number, '@#$') = v_wsli(v_wlji_src_line_id(v_idx)).lot_number
and mmtt.subinventory_code = v_wsli(v_wlji_src_line_id(v_idx)).subinventory_code
and nvl(mmtt.locator_id, -9999) = nvl(v_wsli(v_wlji_src_line_id(v_idx)).locator_id, -9999)
and mmtt.transaction_type_id = 42 -- miscellaneous receipt
and mmtt.transaction_action_id = 27 -- receipt into stores
and mmtt.transaction_source_type_id = 13 -- inventory
and v_wsli(v_wlji_src_line_id(v_idx)).quantity = ((-1) * mmtt.transaction_quantity)
and mmtt.transaction_date = (
select max(mmtt2.transaction_date)
from mtl_material_transactions_temp mmtt2
where mmtt2.organization_id = v_wsli(v_wlji_src_line_id(v_idx)).organization_id
and mmtt2.inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
and nvl(mmtt2.lot_number, '@#$') = nvl(v_wsli(v_wlji_src_line_id(v_idx)).lot_number, '@#$')
and mmtt2.subinventory_code = v_wsli(v_wlji_src_line_id(v_idx)).subinventory_code
and nvl(mmtt2.locator_id, -9999) = nvl(v_wsli(v_wlji_src_line_id(v_idx)).locator_id, -9999)
);
select kanban_size
into l_kanban_size
from mtl_kanban_cards
where kanban_card_id = v_wlji_kanban_card_id(v_idx);
select bor.routing_sequence_id,
bor.COMPLETION_SUBINVENTORY,
bor.COMPLETION_LOCATOR_ID
into l_rtg_seq_id,
l_default_compl_subinv,
l_default_compl_loc_id
from bom_operational_routings bor,
bom_alternate_designators bad
where ((bor.alternate_routing_designator is null and
bad.alternate_designator_code is null and
bad.organization_id = -1) OR
(bor.alternate_routing_designator
= bad.alternate_designator_code and
bor.organization_id = bad.organization_id))
and bor.organization_id = v_wlji_org_id(v_idx)
and bor.assembly_item_id = v_wlji_item_id(v_idx)
and NVL(bor.alternate_routing_designator, '&*')
= NVL(v_wlji_alt_rtg(v_idx), '&*')
and bor.routing_type = 1
and bor.cfm_routing_flag = 3;
select locator_type
into l_mtl_locator_type
from mtl_secondary_inventories
where secondary_inventory_name
= v_wlji_compl_subinv(v_idx)
and organization_id = v_wlji_org_id(v_idx);
select bom.common_bill_sequence_id
into l_common_bom_seq_id
from bom_bill_of_materials bom
where bom.alternate_bom_designator is null
and bom.assembly_item_id = v_wlji_item_id(v_idx)
and bom.organization_id = v_wlji_org_id(v_idx);
select bom.common_bill_sequence_id
into l_common_bom_seq_id
from bom_bill_of_materials bom,
bom_alternate_designators bad
where ((bom.alternate_bom_designator is null and
bad.alternate_designator_code is null and
bad.organization_id = -1) or
(bom.alternate_bom_designator
= bad.alternate_designator_code and
bom.organization_id = bad.organization_id))
and bom.alternate_bom_designator
= v_wlji_alt_bom(v_idx)
and bom.assembly_item_id = v_wlji_item_id(v_idx)
and bom.organization_id = v_wlji_org_id(v_idx);
SELECT nvl(msub.locator_type, 1) sub_loc_control,
MP.stock_locator_control_code org_loc_control,
MS.restrict_locators_code,
MS.location_control_code item_loc_control
into l_sub_loc_control,
l_org_loc_control,
l_restrict_locators_code,
l_item_loc_control
FROM mtl_system_items MS,
mtl_secondary_inventories MSUB,
mtl_parameters MP
WHERE MP.organization_id = v_wlji_org_id(v_idx)
AND MS.organization_id = v_wlji_org_id(v_idx)
AND MS.inventory_item_id = v_wlji_item_id(v_idx)
AND MSUB.secondary_inventory_name
= v_wlji_compl_subinv(v_idx)
AND MSUB.organization_id = v_wlji_org_id(v_idx);
select locator_type
into l_mtl_locator_type
from mtl_secondary_inventories
where secondary_inventory_name
= v_wlji_compl_subinv(v_idx)
and organization_id = v_wlji_org_id(v_idx);
SELECT nvl(msub.locator_type, 1) sub_loc_control,
MP.stock_locator_control_code org_loc_control,
MS.restrict_locators_code,
MS.location_control_code item_loc_control
into l_sub_loc_control,
l_org_loc_control,
l_restrict_locators_code,
l_item_loc_control
FROM mtl_system_items MS,
mtl_secondary_inventories MSUB,
mtl_parameters MP
WHERE MP.organization_id = v_wlji_org_id(v_idx)
AND MS.organization_id = v_wlji_org_id(v_idx)
AND MS.inventory_item_id = v_wlji_item_id(v_idx)
AND MSUB.secondary_inventory_name
= v_wlji_compl_subinv(v_idx)
AND MSUB.organization_id = v_wlji_org_id(v_idx);
'Update BOM/Routing alternates for standard job..');
'Update BOM/Routing references for non-standard job..');
'Update BOM/Routing revision or revision date ...');
l_skip_update_wo:= 1;
if l_skip_update_wo = 1 then
fnd_file.put_line(fnd_file.log,
'Planning to skip update of wo, wor, wro');
'Planning to update wo, wor, wro');
select est_scrap_account,
est_scrap_var_account
into l_est_scrap_account,
l_est_scrap_var_account
from wip_accounting_classes
where class_code = l_temp_class
and organization_id = v_wlji_org_id(v_idx);
select decode(wdj.primary_item_id, null, 0,
decode(wdj.net_quantity,
wdj.start_quantity,
v_wlji_start_quantity(v_idx),
least(wdj.net_quantity,
nvl(v_wlji_start_quantity(v_idx),
wdj.net_quantity))))
into v_wlji_net_quantity(v_idx)
from wip_discrete_jobs wdj
where wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx)
and wdj.organization_id = v_wlji_org_id(v_idx);
select msi.serial_number_control_code
into l_serial_ctrl_code
from mtl_system_items_kfv msi,wip_discrete_jobs wdj
where msi.inventory_item_id = wdj.primary_item_id
and msi.organization_id = v_wlji_org_id(v_idx)
and wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx)
and wdj.organization_id = v_wlji_org_id(v_idx);
delete from wip_operations
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
delete from wip_operation_yields
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
delete from wip_operation_resources
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
delete from wip_requirement_operations
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
delete from wip_sub_operation_resources
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
delete from wip_operation_resource_usage
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
delete from wip_op_resource_instances
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
v_insert_wip := 1; --OPTII-PERF:By default,it is 1 for new jobs.
select SERIAL_NUMBER_CONTROL_CODE,
revision_qty_control_code -- Add bug 2963225
into dummy_number,
l_rev_control_code -- Add bug 2963225
from mtl_system_items_kfv msi
where msi.inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
and msi.organization_id = v_wlji_org_id(v_idx);
SELECT 1,
bic.component_item_id,
bsc.substitute_item_quantity,
(bsc.substitute_item_quantity/bic.component_yield_factor)
INTO l_is_src_lot_sub,
l_primary_comp_item_id,
l_sub_comp_bill_qpa,
l_sub_comp_qpa
FROM bom_bill_of_materials bom,
bom_inventory_components bic,
bom_substitute_components bsc
WHERE bsc.SUBSTITUTE_COMPONENT_ID = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
AND bic.COMPONENT_SEQUENCE_ID = bsc.COMPONENT_SEQUENCE_ID
AND bom.organization_id =v_wlji_org_id(v_idx)
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND bom.common_bill_sequence_id =l_common_bom_seq_id
AND bic.implementation_date is not null
AND bic.effectivity_date <= sysdate and nvl(bic.disable_date,sysdate +1) > sysdate
AND (bic.operation_seq_num = l_start_op_seq_num OR bic.operation_seq_num = 1)
AND bic.item_num = (SELECT min(item_num)
FROM bom_bill_of_materials bom3,
bom_inventory_components bic3,
bom_substitute_components bsc3
WHERE bsc3.SUBSTITUTE_COMPONENT_ID = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
AND bic3.COMPONENT_SEQUENCE_ID = bsc3.COMPONENT_SEQUENCE_ID
AND bom3.organization_id =v_wlji_org_id(v_idx)
AND bic3.bill_sequence_id = bom3.common_bill_sequence_id
AND bom3.common_bill_sequence_id = l_common_bom_seq_id
AND bic3.implementation_date is not null
AND bic3.effectivity_date <= sysdate
AND nvl(bic3.disable_date,sysdate +1) > sysdate)
AND NOT EXISTS
( SELECT 1 FROM bom_bill_of_materials bom2,
bom_inventory_components bic2
WHERE bic2.component_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
AND bom2.organization_id =v_wlji_org_id(v_idx)
AND bic2.bill_sequence_id = bom2.common_bill_sequence_id
AND bom2.common_bill_sequence_id = l_common_bom_seq_id
AND bic.effectivity_date <= sysdate and nvl(bic.disable_date,sysdate +1) > sysdate
AND bic2.implementation_date is not null
AND (bic2.operation_seq_num = l_start_op_seq_num OR bic.operation_seq_num = 1));
--select sum(quantity_per_assembly)
/* CHANGED TO DECODE FOR 12.1 SUBSTITUTE COMPONENT PROJECT */
/* select quantity_per_assembly,BILL_QUANTITY_PER_ASSEMBLY, */
select decode(l_is_src_lot_sub,1,l_sub_comp_qpa,quantity_per_assembly),
decode(l_is_src_lot_sub,1,l_sub_comp_bill_qpa,BILL_QUANTITY_PER_ASSEMBLY),
basis_type
into l_required_qpa,l_bill_req_qpa,l_basis_type
from wsm_copy_requirement_ops wcro
where wcro.wip_entity_id = v_wlji_wip_entity_id(v_idx)
/* and wcro.component_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id */
and wcro.component_item_id = decode(l_is_src_lot_sub,1,l_primary_comp_item_id,v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id)
and source_phantom_id = -1 -- is null
and recommended = 'Y'
and wcro.operation_seq_num = l_start_op_seq_num;
select 1
into l_temp_num
from wsm_copy_requirement_ops wcro
where wcro.wip_entity_id = v_wlji_wip_entity_id(v_idx)
/*and wcro.component_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id */
and wcro.component_item_id = decode(l_is_src_lot_sub,1,l_primary_comp_item_id,v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id)
and recommended = 'Y'
and (wcro.operation_seq_num = l_start_op_seq_num or
wcro.operation_seq_num= 1)
and nvl(wcro.wip_supply_type, 1) = 6;
select include_component_yield
into l_include_yield
from wip_parameters
where organization_id = v_wlji_org_id(v_idx);
select revision
into l_start_lot_revision
from WSM_source_lots_v
where lot_number = v_wsli(v_wlji_src_line_id(v_idx)).lot_number
and organization_id = v_wlji_org_id(v_idx)
and inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id;
/****************ADDED FOLLOWING CODE FOR 12.1 SUBSTITUTE COMPONENT PROJECT TO UPDATE WIP_REQIREMENT_OPERATIONS TO REPLACE REQUIREMENT OF PRIMARY COMPONENT
***********************************************************************************************************/
IF (l_is_src_lot_sub = 1 AND v_wlji_mode_flag(v_idx) = 2 ) THEN
BEGIN
SELECT segment1,segment2,segment3,segment4,
segment5,segment6,segment7,segment8,
segment9,segment10,segment11,segment12,
segment13,segment14,segment15,segment16,
segment17,segment18,segment19,segment20
INTO
l_segment1,l_segment2,l_segment3,l_segment4,
l_segment5,l_segment6,l_segment7,l_segment8,
l_segment9,l_segment10,l_segment11,l_segment12,
l_segment13,l_segment14,l_segment15,l_segment16,
l_segment17,l_segment18,l_segment19,l_segment20
FROM mtl_system_items
WHERE inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id
AND organization_id=v_wlji_org_id(v_idx);
UPDATE wip_requirement_operations
SET inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id,
required_quantity = ((required_quantity * l_sub_comp_bill_qpa)/quantity_per_assembly),
quantity_per_assembly = l_sub_comp_bill_qpa,
segment1 = l_segment1,
segment2 = l_segment2,
segment3 = l_segment3,
segment4 = l_segment4,
segment5 = l_segment5,
segment6 = l_segment6,
segment7 = l_segment7,
segment8 = l_segment8,
segment9 = l_segment9,
segment10 = l_segment10,
segment11 = l_segment11,
segment12 = l_segment12,
segment13 = l_segment13,
segment14 = l_segment14,
segment15 = l_segment15,
segment16 = l_segment16,
segment17 = l_segment17,
segment18 = l_segment18,
segment19 = l_segment19,
segment20 = l_segment20,
released_quantity = ((released_quantity * l_sub_comp_bill_qpa)/quantity_per_assembly)
WHERE wip_entity_id = v_wlji_wip_entity_id(v_idx)
AND inventory_item_id = l_primary_comp_item_id;
if (l_skip_update_wo = 0 and
v_wlji_allow_explosion(v_idx) = 'Y' and
v_wlji_status_type(v_idx) <> 7 and
l_chg_alt_flag <> 1 and
l_chg_ref_flag <> 1 and
l_chg_rev_flag <> 1)
then
Begin
l_stmt_num := 900;
update wip_operations
set -- BD: BUG 3479666
-- in option C, we should not change operation start/completion
-- date based on job header level information
--
--first_unit_start_date
-- = decode(l_txnexist,
-- 0, nvl(v_wlji_fusd(v_idx), first_unit_start_date), -- bug 3394520
-- first_unit_start_date),
--first_unit_completion_date
-- = decode(l_txnexist,
-- 0, nvl(v_wlji_lucd(v_idx), first_unit_completion_date), -- bug 3394520
-- first_unit_completion_date),
--last_unit_start_date
-- = decode(l_txnexist,
-- 0, nvl(v_wlji_fusd(v_idx), last_unit_start_date), -- bug 3394520
-- last_unit_start_date),
--last_unit_completion_date
-- = decode(l_txnexist,
-- 0, nvl(v_wlji_lucd(v_idx), last_unit_completion_date), -- bug 3394520
-- last_unit_completion_date),
-- ED: BUG 3479666
scheduled_quantity
= round(v_wlji_start_quantity(v_idx),
wip_constants.max_displayed_precision),
quantity_in_queue
= decode(v_wlji_status_type(v_idx),
1, 0,
3, decode(l_cur_status_type,
1, decode(operation_seq_num,
g_op_seq_incr,
round(v_wlji_start_quantity(v_idx),
wip_constants.max_displayed_precision),
quantity_in_queue),
decode(l_qntydiff,
1, decode(operation_seq_num,
g_op_seq_incr,
round(v_wlji_start_quantity(v_idx),
wip_constants.max_displayed_precision),
quantity_in_queue),
quantity_in_queue)),
quantity_in_queue),
last_updated_by = v_wlji_last_updt_by(v_idx),
last_update_date = sysdate,
last_update_login = v_wlji_last_updt_login(v_idx),
program_update_date = sysdate,
request_id = v_wlji_request_id(v_idx),
program_application_id = v_wlji_program_appl_id(v_idx),
program_id = v_wlji_program_id(v_idx)
where organization_id = v_wlji_org_id(v_idx)
and wip_entity_id = v_wlji_wip_entity_id(v_idx);
'Updated '||SQL%ROWCOUNT||' rows into wo');
update wip_operation_resources
set start_date = decode(l_txnexist,
0, nvl(v_wlji_fusd(v_idx), start_date), -- bug 3394520
start_date),
completion_date = decode(l_txnexist,
0, nvl(v_wlji_lucd(v_idx), completion_date), -- bug 3394520
completion_date),
last_updated_by = v_wlji_last_updt_by(v_idx),
last_update_date = sysdate,
last_update_login = v_wlji_last_updt_login(v_idx),
program_update_date = sysdate,
request_id = v_wlji_request_id(v_idx),
program_application_id = v_wlji_program_appl_id(v_idx),
program_id = v_wlji_program_id(v_idx)
where organization_id = v_wlji_org_id(v_idx)
and wip_entity_id = v_wlji_wip_entity_id(v_idx);
'Updated '||SQL%ROWCOUNT||' rows into wor');
update wip_requirement_operations wro
set wro.date_required = (
select nvl(min(first_unit_start_date), v_wlji_fusd(v_idx))
from wip_operations
where organization_id = v_wlji_org_id(v_idx)
and wip_entity_id = v_wlji_wip_entity_id(v_idx)
and operation_seq_num = abs(wro.operation_seq_num)),
last_updated_by = v_wlji_last_updt_by(v_idx),
last_update_date = sysdate,
last_update_login = v_wlji_last_updt_login(v_idx),
request_id = v_wlji_request_id(v_idx),
program_update_date = sysdate,
program_id = v_wlji_program_id(v_idx),
program_application_id = v_wlji_program_appl_id(v_idx),
required_quantity = ((wro.quantity_per_assembly/wro.component_yield_factor) * decode(
wro.basis_type, 2, 1, round(v_wlji_start_quantity(v_idx), 6)))
where organization_id = v_wlji_org_id(v_idx)
and wip_entity_id = v_wlji_wip_entity_id(v_idx);
'Updated '||SQL%ROWCOUNT||' rows into wro');
select min(operation_seq_num)
into l_min_op_seq_num
from wip_operations
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
select bd.scrap_account,
bd.est_absorption_account,
wo.department_id
into l_scrap_account_10,
l_est_scrap_abs_account_10,
l_department_id
from bom_departments bd,
wip_operations wo
where wo.wip_entity_id = v_wlji_wip_entity_id(v_idx)
and wo.operation_seq_num = l_min_op_seq_num
and bd.department_id = wo.department_id;
update wip_operation_yields woy
set scrap_account = nvl(l_scrap_account_10, woy.scrap_account),
est_scrap_absorb_account = nvl(l_est_scrap_abs_account_10,
woy.est_scrap_absorb_account)
where wip_entity_id = v_wlji_wip_entity_id(v_idx)
and operation_seq_num = l_min_op_seq_num;
fnd_file.put_line(fnd_file.log, 'Updated '||SQL%ROWCOUNT||' rows into woy');
delete from wip_period_balances
where wip_entity_id = v_wlji_wip_entity_id(v_idx)
and organization_id = v_wlji_org_id(v_idx);
'Deleted '||SQL%ROWCOUNT||' rows from WIP_PERIOD_BALANCES');
wsm_reservations_pvt.modify_reservations_jobupdate (
p_wip_entity_id => v_wlji_wip_entity_id(v_idx),
p_old_net_qty => round(v_wlji_net_quantity(v_idx),wip_constants.max_displayed_precision),
p_new_net_qty => round(v_wlji_net_quantity(v_idx),wip_constants.max_displayed_precision),
p_inventory_item_id => v_wlji_item_id(v_idx),
p_org_id => v_wlji_org_id(v_idx),
p_status_type => v_wlji_status_type(v_idx),
x_return_status => l_return_status,
x_msg_count => l_error_count,
x_msg_data => l_error_msg
);
l_wip_entity_name := WSMPOPRN.update_job_name
(p_wip_entity_id => v_wlji_wip_entity_id(v_idx),
p_subinventory => v_wlji_compl_subinv(v_idx),
p_org_id => v_wlji_org_id(v_idx),
p_txn_type => 2, -- COMPLETION
p_update_flag => TRUE,
p_dup_job_name => l_dummy_job_name,
x_error_code => l_error_code,
x_error_msg => l_error_msg);
update wip_discrete_jobs wdj
set status_type = 7
where wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx);
'Updated status type to 7 in wdj');
inv_kanban_pvt.update_card_supply_status(
x_return_status => l_return_status,
p_kanban_card_id => v_wlji_kanban_card_id(v_idx),
p_supply_status => inv_kanban_pvt.g_supply_status_exception);
select meaning
into l_meaning
from mfg_lookups
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
and lookup_code = 7
and upper(enabled_flag) = 'Y';
update wip_discrete_jobs
set kanban_card_id = null
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
select net_quantity
into l_net_qty_old
from WIP_DISCRETE_JOBS
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
wsm_reservations_pvt.modify_reservations_jobupdate (
p_wip_entity_id => v_wlji_wip_entity_id(v_idx),
p_old_net_qty => l_net_qty_old ,
p_new_net_qty => round(v_wlji_net_quantity(v_idx),wip_constants.max_displayed_precision),
p_inventory_item_id => v_wlji_item_id(v_idx),
p_org_id => v_wlji_org_id(v_idx),
p_status_type => v_wlji_status_type(v_idx),
x_return_status => l_return_status,
x_msg_count => l_error_count,
x_msg_data => l_error_msg
);
UPDATE WIP_DISCRETE_JOBS WDJ
set last_updated_by = v_wlji_last_updt_by(v_idx),
last_update_login = v_wlji_last_updt_login(v_idx),
request_id = v_wlji_request_id(v_idx),
program_application_id = v_wlji_program_appl_id(v_idx),
program_id = v_wlji_program_id(v_idx),
program_update_date = sysdate,
last_update_date = sysdate,
bom_reference_id = v_wlji_bom_ref_id(v_idx),
routing_reference_id = v_wlji_rtg_ref_id(v_idx),
common_bom_sequence_id = l_common_bom_seq_id,
common_routing_sequence_id = l_common_rtg_seq_id,
bom_revision = v_wlji_bom_revision(v_idx),
routing_revision = v_wlji_rtg_revision(v_idx),
bom_revision_date = v_wlji_bom_rev_date(v_idx),
routing_revision_date = v_wlji_rtg_rev_date(v_idx),
alternate_bom_designator = v_wlji_alt_bom(v_idx),
alternate_routing_designator= v_wlji_alt_rtg(v_idx),
firm_planned_flag = v_wlji_firm_planned_flag(v_idx),
start_quantity = nvl(round(v_wlji_start_quantity(v_idx),
wip_constants.max_displayed_precision),
wdj.start_quantity),
net_quantity = nvl(round(v_wlji_net_quantity(v_idx),
wip_constants.max_displayed_precision),
wdj.net_quantity),
lot_number = decode(wdj.status_type, WIP_CONSTANTS.UNRELEASED,
v_wlji_job_name(v_idx),wdj.lot_number), -- fix for bug5990761
status_type = nvl(v_wlji_status_type(v_idx),wdj.status_type),
date_released = v_wlji_date_released(v_idx), -- bug 2697295
scheduled_start_date = decode(l_txnexist, 0,
trunc(NVL(v_wlji_fusd(v_idx), wdj.scheduled_start_date),'MI'),
wdj.scheduled_start_date),
scheduled_completion_date = trunc(NVL(v_wlji_lucd(v_idx),wdj.scheduled_completion_date), 'MI'),
completion_locator_id = v_wlji_compl_loc_id(v_idx),
completion_subinventory = v_wlji_compl_subinv(v_idx),
coproducts_supply = nvl(v_wlji_coproducts_supply(v_idx), wdj.coproducts_supply),
-- BA: BUG3272873
source_code = nvl(v_wlji_source_code(v_idx),wdj.source_code),
source_line_id = nvl(v_wlji_src_line_id(v_idx),wdj.source_line_id),
overcompletion_tolerance_type = nvl(v_wlji_overcompl_tol_type(v_idx),
wdj.overcompletion_tolerance_type),
overcompletion_tolerance_value = nvl(v_wlji_overcompl_tol_value(v_idx),
wdj.overcompletion_tolerance_value),
priority = nvl(v_wlji_priority(v_idx),wdj.priority),
due_date = nvl(v_wlji_due_date(v_idx),wdj.due_date),
attribute_category = nvl(v_wlji_attribute_category(v_idx),wdj.attribute_category),
attribute1 = nvl(v_wlji_attribute1(v_idx),wdj.attribute1),
attribute2 = nvl(v_wlji_attribute2(v_idx),wdj.attribute2),
attribute3 = nvl(v_wlji_attribute3(v_idx),wdj.attribute3),
attribute4 = nvl(v_wlji_attribute4(v_idx),wdj.attribute4),
attribute5 = nvl(v_wlji_attribute5(v_idx),wdj.attribute5),
attribute6 = nvl(v_wlji_attribute6(v_idx),wdj.attribute6),
attribute7 = nvl(v_wlji_attribute7(v_idx),wdj.attribute7),
attribute8 = nvl(v_wlji_attribute8(v_idx),wdj.attribute8),
attribute9 = nvl(v_wlji_attribute9(v_idx),wdj.attribute9),
attribute10 = nvl(v_wlji_attribute10(v_idx),wdj.attribute10),
attribute11 = nvl(v_wlji_attribute11(v_idx),wdj.attribute11),
attribute12 = nvl(v_wlji_attribute12(v_idx),wdj.attribute12),
attribute13 = nvl(v_wlji_attribute13(v_idx),wdj.attribute13),
attribute14 = nvl(v_wlji_attribute14(v_idx),wdj.attribute14),
attribute15 = nvl(v_wlji_attribute15(v_idx),wdj.attribute15),
-- EA: BUG3272873
est_scrap_account = nvl(l_est_scrap_account, wdj.est_scrap_account),
est_scrap_var_account = nvl(l_est_scrap_var_account, wdj.est_scrap_var_account),
description = nvl(RTRIM(v_wlji_description(v_idx)), wdj.description)
-- CZHDBG: how about fusd and lucd ???
where wdj.wip_entity_id = v_wlji_wip_entity_id(v_idx);
end if; -- update jobs for which status is not cancelled
'Updated '||SQL%ROWCOUNT||' rows of wdj');
UPDATE WIP_ENTITIES WE
set description = nvl(v_wlji_description(v_idx), we.description),
wip_entity_name = decode(nvl(v_wlji_status_type(v_idx),-1),7,wip_entity_name,
decode(l_cur_status_type,1, v_wlji_job_name(v_idx),wip_entity_name)), --fix for bug5990761
last_updated_by = v_wlji_last_updt_by(v_idx),
last_update_login = v_wlji_last_updt_login(v_idx),
request_id = v_wlji_request_id(v_idx),
program_application_id = v_wlji_program_appl_id(v_idx),
program_id = v_wlji_program_id(v_idx),
program_update_date = v_wlji_prog_updt_date(v_idx),
last_update_date = v_wlji_last_updt_date(v_idx)
where we.wip_entity_id = v_wlji_wip_entity_id(v_idx);
'Updated '||SQL%ROWCOUNT||' rows into we');
select on_rec_path
into l_on_reco_path
from wsm_lot_based_jobs
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
/* update wsm_lot_based_jobs
set (current_job_op_seq_num,
current_rtg_op_seq_num)
=(select wo.operation_seq_num,
wo.wsm_op_seq_num
from wip_operations wo
where organization_id = v_wlji_org_id(v_idx)
and wip_entity_id = v_wlji_wip_entity_id(v_idx)
and wo.quantity_in_queue
+ wo.quantity_running
+ wo.quantity_waiting_to_move > 0)
where wip_entity_id = v_wlji_wip_entity_id(v_idx)
and organization_id = v_wlji_org_id(v_idx); */
insert_into_period_balances (
p_wip_entity_id => v_wlji_wip_entity_id(v_idx),
p_organization_id => v_wlji_org_id(v_idx),
p_class_code => v_wlji_class_code(v_idx),
p_release_date => v_wlji_date_released(v_idx),
p_error_code => l_error_code,
p_err_msg => l_error_msg);
fnd_file.put_line(fnd_file.log, 'Before Calling WSM_LotAttr_PVT.create_update_lotattr');
WSM_LotAttr_PVT.create_update_lotattr(
x_err_code => l_error_code,
x_err_msg => l_error_msg,
p_wip_entity_id => v_wlji_wip_entity_id(v_idx),
p_org_id => v_wlji_org_id(v_idx),
p_intf_txn_id => v_wlji_header_id(v_idx),
p_intf_src_code => 'WSM',
p_src_lot_number => l_src_lot_number,
p_src_inv_item_id => l_src_inv_item_id);
fnd_file.put_line(fnd_file.log, 'WSM_LotAttr_PVT.create_update_lotattr returned Success');
insert into mtl_material_transactions_temp(
last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
transaction_header_id,
transaction_source_id,
inventory_item_id,
organization_id,
revision,
subinventory_code,
locator_id,
transaction_quantity,
primary_quantity,
transaction_uom,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_date,
acct_period_id,
source_code,
source_line_id,
wip_entity_type,
negative_req_flag,
operation_seq_num,
wip_supply_type,
wip_commit_flag,
process_flag,
posting_flag,
transaction_temp_id
) values (
v_wlji_last_updt_date(v_idx),
v_wlji_creation_date(v_idx),
v_wlji_last_updt_by(v_idx),
v_wlji_created_by(v_idx),
v_wlji_last_updt_login(v_idx),
l_mmtt_txn_hdr_id,
v_wlji_wip_entity_id(v_idx),
v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id,
v_wlji_org_id(v_idx),
v_wsli(v_wlji_src_line_id(v_idx)).revision,
v_wsli(v_wlji_src_line_id(v_idx)).subinventory_code,
v_wsli(v_wlji_src_line_id(v_idx)).locator_id,
-l_quantity_tobe_issued,
-l_quantity_tobe_issued,
v_wsli(v_wlji_src_line_id(v_idx)).primary_uom_code,
35, -- Transaction_Type_Id
1, -- Transaction_Action_Id
5, -- Transaction_Source_Type_Id
SYSDATE, -- Transaction_Date
WSMPLCVA.v_org(v_wlji_org_id(v_idx)).MAX_ORG_ACC_PERIODS,
'WSM', -- Source_Code
to_char(v_wlji_src_line_id(v_idx)),
5, -- Wip_Entity_Type
1, -- Neg Req Flag
10, -- Op Seq
'', -- Supply Type
'N', -- Wip_Commit_Flag
'Y', -- Process_Flag
'Y', -- Posting_Flag
mtl_material_transactions_s.nextval
) RETURNING transaction_temp_id into l_txn_tmp_hdr_id;
'Inserted '||SQL%ROWCOUNT||' rows into mmtt');
INSERT INTO MTL_TRANSACTION_LOTS_TEMP (
transaction_temp_id,
last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
transaction_quantity,
primary_quantity,
lot_number
) values (
l_txn_tmp_hdr_id,
v_wlji_last_updt_date(v_idx),
v_wlji_creation_date(v_idx),
v_wlji_last_updt_by(v_idx),
v_wlji_created_by(v_idx),
v_wlji_last_updt_login(v_idx),
-l_quantity_tobe_issued,
-l_quantity_tobe_issued,
v_wsli(v_wlji_src_line_id(v_idx)).lot_number);
'Inserted '||SQL%ROWCOUNT||' rows into mtlt');
UPDATE WIP_REQUIREMENT_OPERATIONS
set wip_supply_type = 1
where wip_entity_id = v_wlji_wip_entity_id(v_idx)
and operation_seq_num = 10
and inventory_item_id = v_wsli(v_wlji_src_line_id(v_idx)).inventory_item_id;
select organization_code
into l_org_code
from MTL_PARAMETERS
where organization_id = v_wlji_org_id(v_idx);
select meaning
into l_job_type_code
from mfg_lookups
where lookup_type = 'WIP_DISCRETE_JOB'
and lookup_code = v_wlji_job_type(v_idx);
select unique(concatenated_segments)
into l_assembly_name
from mtl_system_items_kfv
where inventory_item_id = v_wlji_item_id(v_idx)
and organization_id = v_wlji_org_id(v_idx);
inv_kanban_pvt.Update_Card_Supply_Status(
x_return_status => l_return_status,
p_Kanban_Card_Id => v_wlji_kanban_card_id(v_idx),
p_Supply_Status => inv_kanban_pvt.g_supply_status_Exception);
select meaning
into l_meaning
from mfg_lookups
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
and lookup_code = 7
and upper(enabled_flag) = 'Y';
update wip_discrete_jobs
set kanban_card_id = null
where wip_entity_id = v_wlji_wip_entity_id(v_idx);
update wsm_lot_job_interface
set process_status = v_wlji_process_status(i),
error_code = v_wlji_err_code(i),
error_msg = v_wlji_err_msg(i),
request_id = v_wlji_request_id(i),
program_id = v_wlji_program_id(i),
program_application_id = v_wlji_program_appl_id(i)
where header_id = v_wlji_header_id(i);
update WSM_SERIAL_TXN_INTERFACE
set process_status = v_wlji_process_status(i),
error_message = v_wlji_err_msg(i),
request_id = v_wlji_request_id(i),
program_id = v_wlji_program_id(i),
program_application_id = v_wlji_program_appl_id(i)
where header_id = v_wlji_header_id(i);
update wsm_lot_job_dtl_interface
set process_status = v_wlji_process_status(i),
error_code = v_wlji_err_code(i),
error_msg = decode(v_wlji_process_status(i), -- BUG3600450
3, 'See header (header_id=' ||
v_wlji_header_id(i) || ') for error message',
null),
request_id = v_wlji_request_id(i),
program_id = v_wlji_program_id(i),
program_application_id = v_wlji_program_appl_id(i)
where parent_header_id = v_wlji_header_id(i)
and v_wlji_num_of_children(i) > 0;
update wsm_lot_job_interface
set process_status = 4,
error_code = -2,
error_msg = l_error_msg
where mode_flag = 2;
delete from wsm_starting_lots_interface
where header_id IN (
select wlji.source_line_id
from wsm_lot_job_interface wlji
where wlji.process_status = 5
--and wlji.group_id = l_batch_grp_id
and NVL(transaction_date, creation_date)
<= decode(l_del_int_prof_value,
null,
NVL(transaction_date, creation_date) -1,
SYSDATE-l_del_int_prof_value)
);
'Deleted '||SQL%ROWCOUNT||' rows from wsli');
delete from wsm_lot_job_dtl_interface
where parent_header_id IN (
select wlji.header_id
from wsm_lot_job_interface wlji
where wlji.process_status = 5
--and wlji.group_id = l_batch_grp_id
and NVL(transaction_date, creation_date)
<= decode(l_del_int_prof_value,
null,
NVL(transaction_date, creation_date) -1,
SYSDATE-l_del_int_prof_value)
);
delete from WSM_SERIAL_TXN_INTERFACE
where header_id IN
(select wlji.header_id
from wsm_lot_job_interface wlji
where wlji.process_status = 5
and NVL(transaction_date, creation_date)
<= decode(l_del_int_prof_value,
null,
NVL(transaction_date, creation_date) -1,
SYSDATE-l_del_int_prof_value)
);
delete from wsm_lot_job_interface
where process_status = 5
--and group_id = l_batch_grp_id
and NVL(transaction_date, creation_date)
<= decode(l_del_int_prof_value,
null,
NVL(transaction_date, creation_date) -1,
SYSDATE-l_del_int_prof_value);
'Deleted '||SQL%ROWCOUNT||' rows from wlji');
delete from wsm_lot_job_dtl_interface
where process_status = 5
--and group_id = l_batch_grp_id
and NVL(transaction_date, creation_date)
<= decode(l_del_int_prof_value,
null,
NVL(transaction_date, creation_date) -1,
SYSDATE-l_del_int_prof_value);
'Deleted '||SQL%ROWCOUNT||' rows from wljdi');
update wsm_lot_job_dtl_interface
set process_status = 3,
error_code = l_v_d_ecode_err(i),
error_msg = l_v_d_emsg_err(i),
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id
where parent_header_id IS NULL
and process_status = WIP_CONSTANTS.RUNNING
and wip_entity_id = l_v_d_we_id_err(i)
and organization_id = l_v_d_org_id_err(i);
delete from wsm_lot_job_dtl_interface
where parent_header_id IS NULL
and process_status = WIP_CONSTANTS.RUNNING
and wip_entity_id = l_v_d_we_id_ok(i)
and organization_id = l_v_d_org_id_ok(i);
select to_number(ORG_INFORMATION3) into l_ou_id
from HR_ORGANIZATION_INFORMATION
where ORGANIZATION_ID = l_mo_org_id
and ORG_INFORMATION_CONTEXT = l_org_acct_ctxt;
delete from bom_explosion_temp
where group_id = wsmpwrot.explosion_group_id;
l_wjsq_uom_code.delete();
l_wjsq_start_quantity.delete();
select 1 into v_phantom_exists
from bom_inventory_components
where bill_sequence_id = l_common_bill_seq_id
and p_bom_revision_date between effectivity_date and nvl(disable_date,p_bom_revision_date+1)
and wip_supply_type = 6
and rownum = 1;
if v_insert_wip = 1 then
--if v_phantom_exists = 1 then
if v_phantom_exists = 1 then
v_insert_wip := 2;
v_insert_wip := 2;
p_last_update_date => p_last_updt_date,
p_last_updated_by => p_last_updt_by,
p_last_update_login => p_last_updt_login,
p_creation_date => p_creation_date,
p_created_by => p_created_by,
p_request_id => p_request_id,
p_program_app_id => p_program_appl_id,
p_program_id => p_program_id,
p_program_update_date => p_prog_updt_date,
p_inf_sch_flag => p_infinite_schedule,
p_inf_sch_mode => l_sch_mode,
p_inf_sch_date => l_sch_date,
--OPTII-PERF:Following new arguments are added.
p_new_job => v_new_job,
p_insert_wip => v_insert_wip,
p_phantom_exists => v_phantom_exists,
p_charges_exist => 2);
select 1
from wip_cost_txn_interface
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = p_op_seq_num
and resource_seq_num = p_res_op_seq_num
and rownum = 1;
select 1
from wip_transactions
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = p_op_seq_num
and resource_seq_num = p_res_op_seq_num
and rownum = 1;
Procedure to delete the setup info of a resource that is being replaced or deleted...
********************************************************************************************/
Procedure delete_setup_info(p_wip_entity_id in number,
p_org_id in number,
p_par_res_seq_num in number,
p_op_seq_num in number,
p_handle_curr_op in varchar2,
x_error_code out nocopy number,
x_error_msg out nocopy varchar2
)
is
e_res_costed exception;
select resource_id,resource_seq_num,operation_seq_num,applied_resource_units
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_op_seq_num
and wor.parent_resource_seq = c_par_res_seq_num;
fnd_file.put_line(fnd_file.log,'delete setup info: WIP table change.... : ');
fnd_file.put_line(fnd_file.log,'delete setup info:call to check_resource_costed .... : '
|| ' Resource seq num : ' || l_setup_res.resource_seq_num
|| ' Operation seq num : ' || p_op_seq_num
);
/* delete the instance information ... */
delete from wip_op_resource_instances wori
where wori.organization_id = p_org_id
and wori.wip_entity_id = p_wip_entity_id
and wori.operation_seq_num = p_op_seq_num
and wori.RESOURCE_SEQ_NUM in (select resource_seq_num
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_op_seq_num
and wor.parent_resource_seq = p_par_res_seq_num
);
/* delete the usage information .... */
delete from wip_operation_resource_usage woru
where woru.organization_id = p_org_id
and woru.wip_entity_id = p_wip_entity_id
and woru.operation_seq_num = p_op_seq_num
and woru.resource_seq_num in (select resource_seq_num
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_op_seq_num
and wor.parent_resource_seq = p_par_res_seq_num
);
/* delete the setup resources.... */
delete from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_op_seq_num
and wor.parent_resource_seq = p_par_res_seq_num;
/* delete the instance information ... */
l_stmt_num := 90;
select count(*)
into l_setup_exists
from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_op_seq_num
and wcor.parent_resource_seq_num = p_par_res_seq_num
and rownum = 1;
delete from wsm_copy_op_resource_instances wcori
where wcori.organization_id = p_org_id
and wcori.wip_entity_id = p_wip_entity_id
and wcori.operation_seq_num = p_op_seq_num
and wcori.resource_seq_num in (select resource_seq_num
from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_op_seq_num
and wcor.parent_resource_seq_num = p_par_res_seq_num
);
/* delete the usage information .... */
l_stmt_num := 100;
delete from wsm_copy_op_resource_usage wcoru
where wcoru.organization_id = p_org_id
and wcoru.wip_entity_id = p_wip_entity_id
and wcoru.operation_seq_num = p_op_seq_num
and wcoru.resource_seq_num in (select resource_seq_num
from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_op_seq_num
and wcor.parent_resource_seq_num = p_par_res_seq_num
);
/* delete the setup resources.... */
l_stmt_num := 110;
delete from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_op_seq_num
and wcor.parent_resource_seq_num = p_par_res_seq_num;
fnd_file.put_line(fnd_file.log,'delete_setup_info : ' || l_stmt_num || ' : ' || x_error_msg);
end delete_setup_info;
select resource_id,resource_seq_num,operation_seq_num
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_job_op_seq_num
and wor.parent_resource_seq = c_par_res_seq_num;
select max(resource_seq_num)
into l_res_num1
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_job_op_seq_num;
iii) Delete the setup resources...( if no cost incurred )
*/
l_stmt_num := 20;
select 1
into l_exists
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_job_op_seq_num
and resource_seq_num = l_par_res_seq_num;
select 1
into l_setup_exists
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_job_op_seq_num
and wor.parent_resource_seq = l_par_res_seq_num
and rownum =1 ;
fnd_file.put_line(fnd_file.log,'validate new resource : ' || l_stmt_num || ' calling delete setup info ...' );
delete_setup_info( p_wip_entity_id => p_wip_entity_id,
p_org_id => p_org_id,
p_par_res_seq_num => l_par_res_seq_num,
p_op_seq_num => p_job_op_seq_num,
p_handle_curr_op => 'Y',
x_error_code => l_error_code,
x_error_msg => l_error_msg
);
/* delete the setup resources.... */
delete from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_job_op_seq_num
and wor.parent_resource_seq = l_par_res_seq_num;
select max(resource_seq_num)
into l_res_num2
from wip_sub_operation_resources wsor
where wsor.organization_id = p_org_id
and wsor.wip_entity_id = p_wip_entity_id
and wsor.operation_seq_num = p_job_op_seq_num;
iii) Delete the setup resources...( if no cost incurred )
*/
l_stmt_num := 100;
select wcor.resource_id,wcor.recommended
into l_res_id,l_res_recommended
from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_rtg_op_seq_num
and wcor.resource_seq_num = l_par_res_seq_num;
delete_setup_info( p_wip_entity_id => p_wip_entity_id,
p_org_id => p_org_id,
p_par_res_seq_num => l_par_res_seq_num,
p_op_seq_num => p_rtg_op_seq_num,
p_handle_curr_op => 'N',
x_error_code => l_error_code,
x_error_msg => l_error_msg
);
select max(resource_seq_num)
into l_max_resource_seq_num
from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_rtg_op_seq_num;
/* unfortunately have to update the global table also.... */
v_wljdi_resource_seq_num(l_index) := l_max_resource_seq_num;
select (BR.autocharge_type),
(BR.unit_of_measure),
nvl(p_new_res_tbl(l_index).basis_type,BR.default_basis_type),
uom.uom_class,
br.batchable,
nvl(BR.standard_rate_flag,2)
into p_new_res_tbl(l_index).autocharge_type,
p_new_res_tbl(l_index).uom_code,
p_new_res_tbl(l_index).basis_type,
l_uom_class,
l_batchable,
p_new_res_tbl(l_index).standard_rate_flag
from bom_resources BR,
mtl_units_of_measure_vl uom
where br.resource_id = p_new_res_tbl(l_index).resource_id
and autocharge_type = nvl(p_new_res_tbl(l_index).autocharge_type,autocharge_type)
and nvl(p_new_res_tbl(l_index).uom_code,br.unit_of_measure) = br.unit_of_measure -- uom.uom_code
and br.unit_of_measure = uom.uom_code
and nvl(BR.standard_rate_flag,-1) = nvl(nvl(p_new_res_tbl(l_index).standard_rate_flag,BR.standard_rate_flag),-1);
select 1
into l_dummy
from mfg_lookups
where LOOKUP_TYPE = 'BOM_RESOURCE_SCHEDULE_TYPE'
and lookup_code = p_new_res_tbl(l_index).scheduled_flag;
select 1
into l_dummy
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_job_op_seq_num
and wor.resource_seq_num = p_new_res_tbl(l_index).res_seq_num;
select 1
into l_dummy
from wip_sub_operation_resources wsor
where wsor.organization_id = p_org_id
and wsor.wip_entity_id = p_wip_entity_id
and wsor.operation_seq_num = p_job_op_seq_num
and wsor.resource_seq_num = p_new_res_tbl(l_index).res_seq_num;
select nvl(p_new_res_tbl(l_index).dept_id,wo.department_id)
into p_new_res_tbl(l_index).dept_id
from bom_resources br,bom_department_resources bdr, wip_operations wo
where br.resource_id = p_new_res_tbl(l_index).resource_id
and br.resource_code = p_new_res_tbl(l_index).resource_code
and (br.disable_date > sysdate or br.disable_date is null)
and br.organization_id = p_org_id
and br.autocharge_type = p_new_res_tbl(l_index).autocharge_type
and bdr.resource_id = p_new_res_tbl(l_index).resource_id
and bdr.department_id = nvl(p_new_res_tbl(l_index).dept_id,wo.department_id)
and wo.organization_id = p_org_id
and wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_job_op_seq_num;
select 1
into l_dummy
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id
and wo.organization_id = p_org_id
and wo.operation_seq_num = p_job_op_seq_num
and p_new_res_tbl(l_index).start_date >= wo.first_unit_start_date
and p_new_res_tbl(l_index).start_date <= nvl(last_unit_start_date,p_new_res_tbl(l_index).start_date)
and p_new_res_tbl(l_index).completion_date <= nvl(wo.last_unit_completion_date,p_new_res_tbl(l_index).completion_date)
and p_new_res_tbl(l_index).completion_date >= nvl(wo.last_unit_start_date,p_new_res_tbl(l_index).completion_date);
select 1
into l_dummy
from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = p_rtg_op_seq_num
and wcor.resource_seq_num = p_new_res_tbl(l_index).res_seq_num;
select nvl(p_new_res_tbl(l_index).dept_id,wco.department_id)
into p_new_res_tbl(l_index).dept_id
from bom_resources br,bom_department_resources bdr, wsm_copy_operations wco
where br.resource_id = p_new_res_tbl(l_index).resource_id
and br.resource_code = p_new_res_tbl(l_index).resource_code
and (br.disable_date > sysdate or br.disable_date is null)
and br.organization_id = p_org_id
and bdr.resource_id = p_new_res_tbl(l_index).resource_id
and bdr.department_id = nvl(nvl(p_new_res_tbl(l_index).dept_id,wco.department_id),bdr.department_id)
and wco.organization_id = p_org_id
and wco.wip_entity_id = p_wip_entity_id
and wco.operation_seq_num = p_rtg_op_seq_num;
select 1
into l_dummy
from wsm_copy_operations wco
where wco.organization_id = p_org_id
and wco.wip_entity_id = p_wip_entity_id
and wco.operation_seq_num = p_rtg_op_seq_num
and p_new_res_tbl(l_index).start_date >= wco.reco_start_date
and p_new_res_tbl(l_index).start_date <= nvl(reco_completion_date,p_new_res_tbl(l_index).start_date)
-- and nvl(p_new_res_tbl(l_index).completion_date,nvl(reco_completion_date,sysdate)) <= nvl(reco_completion_date,sysdate)
and ( (p_new_res_tbl(l_index).completion_date IS NULL and reco_completion_date IS NULL)
OR
( (p_new_res_tbl(l_index).completion_date IS NOT NULL and reco_completion_date IS NOT NULL)
AND
(p_new_res_tbl(l_index).completion_date <= reco_completion_date)
)
OR
(p_new_res_tbl(l_index).completion_date IS NOT NULL and reco_completion_date IS NULL)
)
and nvl(p_new_res_tbl(l_index).completion_date,nvl(reco_completion_date,wco.reco_start_date)) >= wco.reco_start_date;
select bst.setup_id
into p_new_res_tbl(l_index).setup_id
from bom_setup_types bst
where bst.setup_code = p_new_res_tbl(l_index).setup_code
and bst.organization_id = p_org_id;
select 1
into l_dummy
from bom_resource_setups brs
where brs.resource_id = p_new_res_tbl(l_index).resource_id
and brs.organization_id = p_org_id
and brs.setup_id = p_new_res_tbl(l_index).setup_id;
/* This is primarily used to delete both the resource and its setup from the v_wldji... so that the Adding is once for the resource and
its setup resource
... */
l_new_res_tbl.delete;
l_setup_index_tbl.delete;
/* Update the WIP tables in this case..... */
--Bug 5474321: Setup resource should be imported even for previous operations.
--if ( ((p_job_op_seq_num IS NOT NULL) and (p_job_op_seq_num = p_cur_job_op_seq_num) )
if ( ((p_job_op_seq_num IS NOT NULL) )
OR
((p_rtg_op_seq_num IS NOT NULL))
)
then
l_stmt_num := 20;
Load the resources into local PL/SQL tables and delete them from v_wldji_
*/
if v_wljdi_parent_seq_num(p_global_index) is not null then /* Indicates that it is a setup resource....*/
l_stmt_num := 30;
/* issue the insert statement */
insert into wip_operation_resources
(
wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
department_id,
--repetitive_schedule_id,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_id,
scheduled_flag,
assigned_units,
maximum_assigned_units,
batch_id,
firm_flag,
group_sequence_id,
group_sequence_number,
parent_resource_seq,
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
start_date,
completion_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
--principle_flag,
setup_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
values
(
p_wip_entity_id,
p_job_op_seq_num,
v_wljdi_resource_seq_num(i),
p_org_id,
v_wljdi_department_id(i),
-- check,
v_wljdi_resource_id_new(i),
v_wljdi_uom_code(i),
v_wljdi_basis_type(i),
v_wljdi_usage_rate_or_amt(i),
v_wljdi_activity_id(i),
v_wljdi_scheduled_flag(i),
v_wljdi_assigned_units(i),
/* Modified Query by Adding NVL during insert of max_assg_units. Bug 5471266 */
nvl(v_wljdi_max_assg_units(i),v_wljdi_assigned_units(i)),
v_wljdi_batch_id(i),
/* Modified Query by Adding NVL during insert of firm type. Bug 5471266 */
nvl(v_wljdi_firm_type(i),0),
v_wljdi_grp_seq_id(i),
v_wljdi_grp_seq_num(i),
v_wljdi_parent_seq_num(i),
v_wljdi_res_autocharge_type(i),
v_wljdi_standard_rate_flag(i),
0,
0,
v_wljdi_start_date(i),
v_wljdi_completion_date(i),
v_wljdi_attribute_category(i),
v_wljdi_attribute1(i),
v_wljdi_attribute2(i),
v_wljdi_attribute3(i),
v_wljdi_attribute4(i),
v_wljdi_attribute5(i),
v_wljdi_attribute6(i),
v_wljdi_attribute7(i),
v_wljdi_attribute8(i),
v_wljdi_attribute9(i),
v_wljdi_attribute10(i),
v_wljdi_attribute11(i),
v_wljdi_attribute12(i),
v_wljdi_attribute13(i),
v_wljdi_attribute14(i),
v_wljdi_attribute15(i),
v_wljdi_schedule_seq_num(i),
v_wljdi_substitute_group_num(i),
v_wljdi_replacement_group_num(i),
v_wljdi_setup_id(i),
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date
);
/* issue the insert statement */
insert into wsm_copy_op_resources
(
wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
department_id,
--repetitive_schedule_id,
recommended,
resource_id,
resource_code,
uom_code,
basis_type,
usage_rate_or_amount,
usage_rate_or_amount_inverse,
activity_id,
schedule_flag,
assigned_units,
max_assigned_units,
batch_id,
firm_type,
group_sequence_id,
group_sequence_num,
parent_resource_seq_num,
autocharge_type,
standard_rate_flag,
reco_start_date,
reco_completion_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
--principle_flag,
setup_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
values
(
p_wip_entity_id,
p_rtg_op_seq_num,
v_wljdi_resource_seq_num(i),
p_org_id,
v_wljdi_department_id(i),
-- check,
'Y',
v_wljdi_resource_id_new(i),
v_wljdi_resource_code_new(i),
v_wljdi_uom_code(i),
v_wljdi_basis_type(i),
v_wljdi_usage_rate_or_amt(i),
round(1/v_wljdi_usage_rate_or_amt(i),6),
v_wljdi_activity_id(i),
v_wljdi_scheduled_flag(i),
v_wljdi_assigned_units(i),
/* Modified Query by Adding NVL during insert of max_assg_units. Bug 5471266 */
nvl(v_wljdi_max_assg_units(i),v_wljdi_assigned_units(i)),
v_wljdi_batch_id(i),
/* Modified Query by Adding NVL during insert of firm type. Bug 5471266 */
nvl(v_wljdi_firm_type(i),0),
v_wljdi_grp_seq_id(i),
v_wljdi_grp_seq_num(i),
v_wljdi_parent_seq_num(i),
v_wljdi_res_autocharge_type(i),
v_wljdi_standard_rate_flag(i),
v_wljdi_start_date(i),
v_wljdi_completion_date(i),
v_wljdi_attribute_category(i),
v_wljdi_attribute1(i),
v_wljdi_attribute2(i),
v_wljdi_attribute3(i),
v_wljdi_attribute4(i),
v_wljdi_attribute5(i),
v_wljdi_attribute6(i),
v_wljdi_attribute7(i),
v_wljdi_attribute8(i),
v_wljdi_attribute9(i),
v_wljdi_attribute10(i),
v_wljdi_attribute11(i),
v_wljdi_attribute12(i),
v_wljdi_attribute13(i),
v_wljdi_attribute14(i),
v_wljdi_attribute15(i),
v_wljdi_schedule_seq_num(i),
v_wljdi_substitute_group_num(i),
v_wljdi_replacement_group_num(i),
-- not there...
v_wljdi_setup_id(i),
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date
);
/* error out.... cannt delete previous op resources.... */
fnd_message.set_name('WSM','WSM_NULL_FIELD');
l_index := p_global_index; /* current position of p_global_index which will be deleted... */
/* delete the fetched records... */
l_index := l_setup_index_tbl.first;
v_wljdi_record_id.delete(l_index);
v_wljdi_load_type.delete(l_index);
v_wljdi_substitution_type.delete(l_index);
v_wljdi_job_op_seq_num.delete(l_index);
v_wljdi_routing_op_seq_num.delete(l_index);
v_wljdi_next_rtg_op_seq_num.delete(l_index);
v_wljdi_inventory_item_id_new.delete(l_index);
v_wljdi_inventory_item_new.delete(l_index);
v_wljdi_primary_item.delete(l_index);
v_wljdi_primary_item_id.delete(l_index);
v_wljdi_src_phantom_item.delete(l_index);
v_wljdi_src_phantom_item_id.delete(l_index);
v_wljdi_component_sequence_id.delete(l_index);
v_wljdi_date_required.delete(l_index);
v_wljdi_scheduled_quantity.delete(l_index);
v_wljdi_required_quantity.delete(l_index);
v_wljdi_batch_id.delete(l_index);
v_wljdi_assigned_units.delete(l_index);
v_wljdi_instance_id_new.delete(l_index);
v_wljdi_op_completion_date.delete(l_index);
v_wljdi_op_start_date.delete(l_index);
v_wljdi_op_yield.delete(l_index);
v_wljdi_resource_code_new.delete(l_index);
v_wljdi_resource_id_new.delete(l_index);
v_wljdi_resource_seq_num.delete(l_index);
v_wljdi_substitute_group_num.delete(l_index);
v_wljdi_replacement_group_num.delete(l_index);
v_wljdi_serial_number_new.delete(l_index);
v_wljdi_start_date.delete(l_index);
v_wljdi_completion_date.delete(l_index);
v_wljdi_firm_type.delete(l_index);
v_wljdi_setup_id.delete(l_index);
v_wljdi_grp_seq_id.delete(l_index);
v_wljdi_grp_seq_num.delete(l_index);
v_wljdi_max_assg_units.delete(l_index);
v_wljdi_parent_seq_num.delete(l_index);
v_wljdi_resource_hours.delete(l_index);
v_wljdi_department_id.delete(l_index);
v_wljdi_department_code.delete(l_index);
v_wljdi_activity_id.delete(l_index);
v_wljdi_standard_rate_flag.delete(l_index);
v_wljdi_usage_rate_or_amt.delete(l_index);
v_wljdi_basis_type.delete(l_index);
v_wljdi_uom_code.delete(l_index);
v_wljdi_scheduled_flag.delete(l_index);
v_wljdi_res_autocharge_type.delete(l_index);
v_wljdi_schedule_seq_num.delete(l_index);
v_wljdi_attribute_category.delete(l_index);
v_wljdi_attribute1.delete(l_index);
v_wljdi_attribute2.delete(l_index);
v_wljdi_attribute3.delete(l_index);
v_wljdi_attribute4.delete(l_index);
v_wljdi_attribute5.delete(l_index);
v_wljdi_attribute6.delete(l_index);
v_wljdi_attribute7.delete(l_index);
v_wljdi_attribute8.delete(l_index);
v_wljdi_attribute9.delete(l_index);
v_wljdi_attribute10.delete(l_index);
v_wljdi_attribute11.delete(l_index);
v_wljdi_attribute12.delete(l_index);
v_wljdi_attribute13.delete(l_index);
v_wljdi_attribute14.delete(l_index);
v_wljdi_attribute15.delete(l_index);
select record_id,
-- ST Fix for bug 4285032 :
-- APS will be populating 8 for resource instance information
-- and 9 resource instance usage information..
-- Map it to WSM load type 7 --> resource instance
-- 4 --> resource instance usage..
decode(load_type,8,7,
9,4,
load_type) load_type,
substitution_type,
job_op_seq_num,
routing_op_seq_num,
next_routing_op_seq_num,
inventory_item_id_new,
inventory_item_new,
primary_item,
primary_item_id,
src_phantom_item,
src_phantom_item_id,
component_sequence_id,
date_required,
scheduled_quantity,
required_quantity,
batch_id,
assigned_units,
instance_id_new,
operation_completion_date,
operation_start_date,
operation_yield,
resource_code_new,
resource_id_new,
resource_seq_num,
substitute_group_num,
replacement_group_num,
serial_number_new,
start_date,
completion_date,
/* ST : Detailed Scheduling */
firm_type,
setup_id,
setup_code,
group_sequence_id,
group_sequence_num,
max_assigned_units,
parent_resource_seq_num,
resource_hours,
/* ST : Detailed Scheduling */
/* ST : Detailed Scheduling : Added for resource adding changes */
department_id ,
department_code ,
activity_id ,
standard_rate_flag ,
usage_rate_or_amount ,
basis_type ,
uom_code ,
scheduled_flag ,
autocharge_type ,
schedule_seq_num ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
/* ST : Detailed Scheduling : Added for resource adding changes : end */
-- Added For LBJ Interface Alcoa Enhancement
recommended,
standard_operation_code,
standard_operation_id,
operation_yield_enabled,
minimum_transfer_quantity,
backflush_flag,
count_point_type,
description,
include_in_rollup,
option_dependent_flag,
inventory_item_id_old,
inventory_item_old,
wip_supply_type,
quantity_per_assembly,
supply_subinventory,
supply_locator_id,
mrp_net_flag,
auto_request_material,
comments,
applied_resource_units,
cumulative_yield, /*Added for bugfix:7248992 */
reverse_cumulative_yield/*Added for bugfix:7248992 */
from wsm_lot_job_dtl_interface
where parent_header_id = p_parent_header_id
and process_status = WIP_CONSTANTS.RUNNING
order by load_type,
NVL(parent_resource_seq_num,0), -- Detailed Scheduling Code review remark...
-- (Moved parent resource_seq_num before substitution_type so that setup resources are processed after runtime resource
-- ST : Detailed Scheduling Added to grpup the new setup resources for a runtime resource together
-- Or else we'll get a error in the BULK INSERT of add_resource procedure
substitution_type,
NVL(job_op_seq_num, 0), NVL(routing_op_seq_num, 0),
NVL(substitute_group_num, -1), NVL(replacement_group_num, 0),
NVL(resource_seq_num, 0),
NVL(instance_id_new, 0), NVL(serial_number_new, 0),
DECODE(load_type,WSM_SUB_DEL,NVL(src_phantom_item_id, 0)) desc,
DECODE(load_type,WSM_SUB_ADD,NVL(src_phantom_item_id, 0)
,WSM_SUB_REC,NVL(src_phantom_item_id, 0)
,WSM_SUB_DIS,NVL(src_phantom_item_id, 0)
,WSM_SUB_CHG,NVL(src_phantom_item_id, 0)) asc,
transaction_date;
select record_id,
-- ST Fix for bug 4285032 :
-- APS will be populating 8 for resource instance information
-- and 9 resource instance usage information..
-- Map it to WSM load type 7 --> resource instance
-- 4 --> resource instance usage..
decode(load_type,8,7,
9,4,
load_type) load_type,
substitution_type,
job_op_seq_num,
routing_op_seq_num,
next_routing_op_seq_num,
inventory_item_id_new,
inventory_item_new,
primary_item,
primary_item_id,
src_phantom_item,
src_phantom_item_id,
component_sequence_id,
date_required,
scheduled_quantity,
required_quantity,
batch_id,
assigned_units,
instance_id_new,
operation_completion_date,
operation_start_date,
operation_yield,
resource_code_new,
resource_id_new,
resource_seq_num,
substitute_group_num,
replacement_group_num,
serial_number_new,
start_date,
completion_date,
/* ST : Detailed Scheduling */
firm_type,
setup_id,
setup_code,
group_sequence_id,
group_sequence_num,
max_assigned_units,
parent_resource_seq_num,
resource_hours,
/* ST : Detailed Scheduling */
/* ST : Detailed Scheduling : Added for resource adding changes */
department_id ,
department_code ,
activity_id ,
standard_rate_flag ,
usage_rate_or_amount ,
--usage_rate_or_amount_inve ,
basis_type ,
uom_code ,
scheduled_flag ,
--resource_offset_percent ,
autocharge_type ,
schedule_seq_num ,
--principle_flag ,
--change_notice ,
--acd_type ,
--original_system_reference,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
/* ST : Detailed Scheduling : Added for resource adding changes : end */
-- Added For LBJ Interface Alcoa Enhancement
recommended,
standard_operation_code,
standard_operation_id,
operation_yield_enabled,
minimum_transfer_quantity,
backflush_flag,
count_point_type,
description,
include_in_rollup,
option_dependent_flag,
inventory_item_id_old,
inventory_item_old,
wip_supply_type,
quantity_per_assembly,
supply_subinventory,
supply_locator_id,
mrp_net_flag,
auto_request_material,
comments,
applied_resource_units,
cumulative_yield, /*Added for bugfix:7248992 */
reverse_cumulative_yield/*Added for bugfix:7248992 */
from wsm_lot_job_dtl_interface
where parent_header_id IS NULL
and wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and process_status = WIP_CONSTANTS.RUNNING
and NVL(group_id, -1) = NVL(p_group_id, -1)
order by load_type,
NVL(parent_resource_seq_num,0),
-- Detailed Scheduling Code review remark...
-- (Moved parent resource_seq_num before substitution_type so that setup resources are processed after runtime resource
-- ST : Detailed Scheduling Added to grpup the new setup resources for a runtime resource together
-- Or else we'll get a error in the BULK INSERT of add_resource procedure
substitution_type,
NVL(job_op_seq_num, 0), NVL(routing_op_seq_num, 0),
NVL(substitute_group_num, -1), NVL(replacement_group_num, 0),
NVL(resource_seq_num, 0),
NVL(instance_id_new, 0), NVL(serial_number_new, 0),
DECODE(load_type,WSM_SUB_DEL,NVL(src_phantom_item_id, 0)) desc,
DECODE(load_type,WSM_SUB_ADD,NVL(src_phantom_item_id, 0)
,WSM_SUB_REC,NVL(src_phantom_item_id, 0)
,WSM_SUB_DIS,NVL(src_phantom_item_id, 0)
,WSM_SUB_CHG,NVL(src_phantom_item_id, 0)) asc,
transaction_date;
select from_op_seq_num,
to_op_seq_num
from wsm_copy_op_networks
start with (to_op_seq_num = c_end_op_seq_num and
recommended = 'Y' and
wip_entity_id = c_wip_entity_id)
connect by (to_op_seq_num = prior from_op_seq_num and
recommended = 'Y' and
wip_entity_id = c_wip_entity_id);
select resource_id,
replacement_group_num,
resource_seq_num /* ST : Detailed Scheduling added.... */
from wsm_copy_op_resources
where wip_entity_id = c_wip_entity_id
and operation_seq_num = c_rtg_op_seq_num
and nvl(substitute_group_num, -1) = nvl(c_sub_grp_num, -1)
and nvl(replacement_group_num, 0) <> nvl(c_rpl_grp_num, 0);
select wcro.component_item_id,
wcro.component_sequence_id,
wcro.source_phantom_id
from wsm_copy_requirement_ops wcro
where wcro.wip_entity_id = c_wip_entity_id
and wcro.operation_seq_num = c_rtg_op_seq_num
and wcro.component_item_id <> c_item_id
and wcro.primary_component_id = c_primary_item_id
and wcro.source_phantom_id = -1 -- IS NULL
union
select wcro.component_item_id,
wcro.component_sequence_id,
wcro.source_phantom_id
from wsm_copy_requirement_ops wcro
where wcro.wip_entity_id = c_wip_entity_id
and wcro.operation_seq_num = c_rtg_op_seq_num
and wcro.primary_component_id = c_primary_item_id
and wcro.source_phantom_id <> -1 -- IS NOT NULL
and wcro.source_phantom_id <> c_src_phantom_id;
select status_type,
start_quantity,
quantity_scrapped
into l_job_status,
l_job_start_qty,
l_job_qty_scrapped
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
select operation_seq_num,
operation_sequence_id,
quantity_in_queue + quantity_running + quantity_waiting_to_move
into l_cur_job_op_seq_num,
l_cur_op_seq_id,
l_cur_job_qty
from wip_operations
where wip_entity_id = p_wip_entity_id
and (quantity_in_queue <> 0 or
quantity_running <> 0 or
quantity_waiting_to_move <> 0);
select operation_seq_num
into l_cur_rtg_op_seq_num
from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and operation_sequence_id = l_cur_op_seq_id;
select scheduled_start_date,
scheduled_completion_date
into l_job_start_date,
l_job_compl_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
select scrap_account,est_absorption_account,department_code
into l_scrap_account,l_est_absorption_account,l_department_code
from bom_departments
where department_id = v_wljdi_department_id(l_d)
and organization_id = p_org_id;
select decode(sign(min(operation_sequence_id)),-1,min(operation_sequence_id)-1,-2),
decode(v_wljdi_routing_op_seq_num(l_d),null,max(operation_seq_num)+g_op_seq_incr,v_wljdi_routing_op_seq_num(l_d))
into l_op_seq_id,v_wljdi_routing_op_seq_num(l_d)
from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
insert into wsm_copy_operations
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RECOMMENDED,
RECO_PATH_SEQ_NUM,
NETWORK_START_END,
RECO_SCHEDULED_QUANTITY,
RECO_START_DATE,
RECO_COMPLETION_DATE,
OPERATION_SEQUENCE_ID,
ROUTING_SEQUENCE_ID,
ORGANIZATION_ID,
STANDARD_OPERATION_ID,
STANDARD_OPERATION_CODE,
DEPARTMENT_ID,
DEPARTMENT_CODE,
SCRAP_ACCOUNT,
EST_ABSORPTION_ACCOUNT,
OPERATION_LEAD_TIME_PERCENT,
MINIMUM_TRANSFER_QUANTITY,
COUNT_POINT_TYPE,
OPERATION_DESCRIPTION,
EFFECTIVITY_DATE,
DISABLE_DATE,
BACKFLUSH_FLAG,
OPTION_DEPENDENT_FLAG,
OPERATION_TYPE,
REFERENCE_FLAG,
YIELD,
CUMULATIVE_YIELD,
REVERSE_CUMULATIVE_YIELD,
LABOR_TIME_CALC,
MACHINE_TIME_CALC,
TOTAL_TIME_CALC,
LABOR_TIME_USER,
MACHINE_TIME_USER,
TOTAL_TIME_USER,
NET_PLANNING_PERCENT,
X_COORDINATE,
Y_COORDINATE,
INCLUDE_IN_ROLLUP,
OPERATION_YIELD_ENABLED,
OLD_OPERATION_SEQUENCE_ID,
ACD_TYPE,
REVISED_ITEM_SEQUENCE_ID,
CHANGE_NOTICE,
ECO_FOR_PRODUCTION,
SHUTDOWN_TYPE,
ACTUAL_IPK,
CRITICAL_TO_QUALITY,
VALUE_ADDED,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORIGINAL_SYSTEM_REFERENCE)
values
(p_wip_entity_id,
v_wljdi_routing_op_seq_num(l_d),
v_wljdi_recommended(l_d), --recommended
null,
null,
v_wljdi_scheduled_quantity(l_d), --reco_scheduled_quantity
v_wljdi_op_start_date(l_d), --reco_start_date
v_wljdi_op_completion_date(l_d), --reco_completion_date
l_op_seq_id,
-1,
p_org_id,
v_wljdi_std_op_id(l_d),
v_wljdi_std_op_code(l_d),
v_wljdi_department_id(l_d),
l_department_code,
l_scrap_account,
l_est_absorption_account,
null,
v_wljdi_min_trf_qty(l_d),
v_wljdi_count_point_type(l_d),
v_wljdi_description(l_d),
p_creation_date,
null,
v_wljdi_backflush_flag(l_d),
v_wljdi_option_dep_flag(l_d),
1,
2,
1,
v_wljdi_cum_yield(l_d),
v_wljdi_rev_cum_yield(l_d),
null,
null,
null,
null,
null,
null,
null,
null,
null,
v_wljdi_inc_in_rollup(l_d),
v_wljdi_op_yield_enable(l_d),
null,
null,
null,
null,
null,
null,
null,
null,
null,
p_last_updt_date,
p_last_updt_by,
p_last_updt_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date,
v_wljdi_attribute_category(l_d),
v_wljdi_attribute1(l_d) ,
v_wljdi_attribute2(l_d) ,
v_wljdi_attribute3(l_d) ,
v_wljdi_attribute4(l_d) ,
v_wljdi_attribute5(l_d) ,
v_wljdi_attribute6(l_d) ,
v_wljdi_attribute7(l_d) ,
v_wljdi_attribute8(l_d) ,
v_wljdi_attribute9(l_d) ,
v_wljdi_attribute10(l_d),
v_wljdi_attribute11(l_d),
v_wljdi_attribute12(l_d),
v_wljdi_attribute13(l_d),
v_wljdi_attribute14(l_d),
v_wljdi_attribute15(l_d),
null);
select 1
into l_temp
from dual
where exists ( SELECT 1
FROM bom_std_op_resources bsor
WHERE bsor.standard_operation_id = v_wljdi_std_op_id(l_d)
AND bsor.autocharge_type = WIP_CONSTANTS.PO_MOVE)
or exists ( SELECT 1
FROM bom_std_sub_op_resources bsor
WHERE bsor.standard_operation_id = v_wljdi_std_op_id(l_d)
AND bsor.autocharge_type = WIP_CONSTANTS.PO_MOVE);
INSERT INTO WSM_COPY_OP_RESOURCES
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
substitute_group_num,
replacement_group_num,
recommended,
reco_start_date,
reco_completion_date,
resource_id,
resource_code,
department_id,
phantom_flag,
phantom_op_seq_num,
phantom_item_id,
activity_id,
standard_rate_flag,
assigned_units,
max_assigned_units,
firm_type,
usage_rate_or_amount,
usage_rate_or_amount_inverse,
uom_code,
basis_type,
schedule_flag,
resource_offset_percent,
autocharge_type,
schedule_seq_num,
principle_flag,
setup_id,
change_notice,
acd_type,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
original_system_reference
)
select
wco.wip_entity_id,
wco.operation_seq_num,
bsor.resource_seq_num,
wco.organization_id,
bsor.substitute_group_num,
0, -- repl_grp_no
'Y',
v_wljdi_op_start_date(l_d),
v_wljdi_op_completion_date(l_d),
bsor.resource_id,
br.resource_code,
v_wljdi_department_id(l_d),
null,
null,
null,
bsor.activity_id,
bsor.standard_rate_flag,
bsor.assigned_units,
bsor.assigned_units,
0, -- Unfirmed
bsor.usage_rate_or_amount,
bsor.usage_rate_or_amount_inverse,
br.unit_of_measure,
bsor.basis_type,
bsor.schedule_flag,
null,
bsor.autocharge_type,
null,
null,
null,
null,
null,
p_last_updt_date,
p_last_updt_by,
p_last_updt_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date,
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,
null
from wsm_copy_operations wco,
bom_resources br,
bom_std_op_resources bsor
where bsor.standard_operation_id = v_wljdi_std_op_id(l_d)
and br.resource_id = bsor.resource_id
and nvl(br.disable_date, SYSDATE + 1) > SYSDATE
and wco.organization_id = p_org_id
and wco.wip_entity_id = p_wip_entity_id
and wco.operation_seq_num = v_wljdi_routing_op_seq_num(l_d);
INSERT INTO WSM_COPY_OP_RESOURCES
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
substitute_group_num,
replacement_group_num,
recommended,
reco_start_date,
reco_completion_date,
resource_id,
resource_code,
department_id,
phantom_flag,
phantom_op_seq_num,
phantom_item_id,
activity_id,
standard_rate_flag,
assigned_units,
max_assigned_units,
firm_type,
usage_rate_or_amount,
usage_rate_or_amount_inverse,
uom_code,
basis_type,
schedule_flag,
resource_offset_percent,
autocharge_type,
schedule_seq_num,
principle_flag,
setup_id,
change_notice,
acd_type,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
original_system_reference
)
select
wco.wip_entity_id,
wco.operation_seq_num,
wcor.resource_seq_num+rownum,
wco.organization_id,
bsor.substitute_group_num,
bsor.replacement_group_num,
'N',
v_wljdi_op_start_date(l_d),
v_wljdi_op_completion_date(l_d),
bsor.resource_id,
br.resource_code,
v_wljdi_department_id(l_d),
null,
null,
null,
bsor.activity_id,
bsor.standard_rate_flag,
bsor.assigned_units,
bsor.assigned_units,
0, -- Unfirmed
bsor.usage_rate_or_amount,
bsor.usage_rate_or_amount_inverse,
br.unit_of_measure,
bsor.basis_type,
bsor.schedule_flag,
null,
bsor.autocharge_type,
bsor.schedule_seq_num,
null,
null,
null,
null,
p_last_updt_date,
p_last_updt_by,
p_last_updt_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date,
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,
null
from wsm_copy_operations wco,
bom_resources br,
bom_std_sub_op_resources bsor,
wsm_copy_op_resources wcor
where bsor.standard_operation_id = v_wljdi_std_op_id(l_d)
and br.resource_id = bsor.resource_id
and nvl(br.disable_date, SYSDATE + 1) > SYSDATE
and wco.organization_id = p_org_id
and wco.wip_entity_id = p_wip_entity_id
and wco.operation_seq_num = v_wljdi_routing_op_seq_num(l_d)
and exists (select 1
from bom_std_op_resources bsor1
where bsor1.standard_operation_id = bsor.standard_operation_id
and bsor1.substitute_group_num = bsor.substitute_group_num )
and wcor.wip_entity_id = wco.wip_entity_id
and wcor.operation_seq_num = wco.operation_seq_num
and wcor.resource_seq_num = (select max(resource_seq_num)
from wsm_copy_op_resources wcor1
where wip_entity_id = wco.wip_entity_id
and operation_seq_num = wco.operation_seq_num);
insert into wsm_copy_op_resource_usage
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
start_date,
completion_date,
assigned_units,
cumulative_processing_time,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select
wcor.wip_entity_id,
wcor.operation_seq_num,
wcor.resource_seq_num,
wcor.organization_id,
wcor.reco_start_date,
wcor.reco_completion_date,
wcor.assigned_units,
to_number(round((wcor.reco_completion_date - wcor.reco_start_date)*1440)), -- Convert to Minutes.
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date
from wsm_copy_op_resources wcor
where wcor.organization_id = p_org_id
and wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = v_wljdi_routing_op_seq_num(l_d)
and wcor.recommended = 'Y'
and wcor.schedule_flag <> WIP_CONSTANTS.SCHED_NO;
l_v_links.delete;
l_v_fm_ops.delete;
l_v_to_ops.delete;
l_v_path_ops.delete;
l_v_path_seq_num.delete;
delete wsm_copy_op_networks
where wip_entity_id= p_wip_entity_id
and from_op_seq_num= v_wljdi_routing_op_seq_num(l_d)
and to_op_seq_num = v_wljdi_next_rtg_op_seq_num(l_d);
select 1 into l_link_exists
from wsm_copy_op_networks
where wip_entity_id = p_wip_entity_id and
from_op_seq_num = v_wljdi_routing_op_seq_num(l_d) and
to_op_seq_num = v_wljdi_next_rtg_op_seq_num(l_d);
select operation_sequence_id
into l_from_op_seq_id
from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = v_wljdi_routing_op_seq_num(l_d);
select operation_sequence_id
into l_to_op_seq_id
from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = v_wljdi_next_rtg_op_seq_num(l_d);
insert into wsm_copy_op_networks(
wip_entity_id,
from_op_seq_num,
to_op_seq_num,
from_op_seq_id,
to_op_seq_id,
recommended,
routing_sequence_id,
transition_type,
planning_pct,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
original_system_reference)
values(
p_wip_entity_id,
v_wljdi_routing_op_seq_num(l_d),
v_wljdi_next_rtg_op_seq_num(l_d),
l_from_op_seq_id,
l_to_op_seq_id,
v_wljdi_recommended(l_d),
-1,
2,
null,
p_last_updt_date,
p_last_updt_by,
p_last_updt_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date,
v_wljdi_attribute_category(l_d),
v_wljdi_attribute1(l_d),
v_wljdi_attribute2(l_d),
v_wljdi_attribute3(l_d),
v_wljdi_attribute4(l_d),
v_wljdi_attribute5(l_d),
v_wljdi_attribute6(l_d),
v_wljdi_attribute7(l_d),
v_wljdi_attribute8(l_d),
v_wljdi_attribute9(l_d),
v_wljdi_attribute10(l_d),
v_wljdi_attribute11(l_d),
v_wljdi_attribute12(l_d),
v_wljdi_attribute13(l_d),
v_wljdi_attribute14(l_d),
v_wljdi_attribute15(l_d),
null
);
l_v_links.delete;
l_v_fm_ops.delete;
l_v_to_ops.delete;
l_v_path_ops.delete;
l_v_path_seq_num.delete;
l_v_links.delete(l_to_op);
update wsm_copy_op_networks
set recommended = decode(l_sub_type,WSM_SUB_DIS,'N',
WSM_SUB_REC,'Y',
WSM_SUB_CHG,'Y',recommended),
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and from_op_seq_num = l_fm_op
and to_op_seq_num = l_to_op;
update wsm_copy_op_networks
set recommended = 'N',
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id;
update wsm_copy_op_networks
set recommended = 'Y',
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and from_op_seq_num = l_v_fm_ops(i)
and to_op_seq_num = l_v_to_ops(i);
update wsm_copy_operations
set recommended = 'N',
reco_path_seq_num = null,
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id;
update wsm_copy_operations
set recommended = 'Y',
reco_path_seq_num = l_v_path_seq_num(i),
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_v_path_ops(i);
update wsm_lot_based_jobs
set on_rec_path = l_on_reco_path
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
l_v_links.delete;
l_v_fm_ops.delete;
l_v_to_ops.delete;
l_v_path_ops.delete;
l_v_path_seq_num.delete;
update wsm_copy_operations
set recommended = 'N',
reco_path_seq_num = null,
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id;
update wsm_copy_operations
set recommended = 'Y',
reco_path_seq_num = l_v_path_seq_num(i),
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_v_path_ops(i);
update wsm_copy_operations
set reco_start_date = null,
reco_completion_date = null
where wip_entity_id = p_wip_entity_id
and recommended = 'N';
update wsm_lot_based_jobs
set on_rec_path = l_on_reco_path
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
l_v_links.delete;
l_v_fm_ops.delete;
l_v_to_ops.delete;
l_v_path_ops.delete;
l_v_path_seq_num.delete;
l_v_rtg_op_seq.delete;
l_v_op_yield.delete;
l_v_op_qty.delete;
l_v_start_date.delete;
l_v_compl_date.delete;
l_v_cum_yield.delete;
l_v_rev_cum_yield.delete;
l_v_ops.delete;
-- update WO for current operation
UPDATE WIP_OPERATIONS
set scheduled_quantity = NVL(l_op_qty, scheduled_quantity),
first_unit_start_date = NVL(l_op_sdate, first_unit_start_date),
first_unit_completion_date = NVL(l_op_edate, first_unit_completion_date),
last_unit_start_date = NVL(l_op_sdate, last_unit_start_date),
last_unit_completion_date = NVL(l_op_edate, last_unit_completion_date),
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_job_op_seq_num;
-- }} Update WIP table
-- {{ Update WSM_COPY table
elsif(l_rtg_op_seq_num IS NOT NULL) then
l_stmt_num := 120;
-- }} Update WSM_COPY table
-- Added For LBJ Interface Alcoa Enhancement
else
if l_sub_type = WSM_SUB_DEL then
if l_rtg_op_seq_num is null then
fnd_message.set_name('WSM','WSM_NULL_FIELD');
select 1
into l_temp
from wsm_copy_operations wco
where wco.wip_entity_id = p_wip_entity_id
and wco.organization_id = p_org_id
and wco.operation_seq_num = l_rtg_op_seq_num
and wco.network_start_end is null;
select 1
into l_temp
from dual
where exists ( select 1
from wsm_copy_op_networks
where wip_entity_id = p_wip_entity_id
and from_op_seq_num = l_rtg_op_seq_num
UNION ALL
select 1
from wsm_copy_op_networks
where wip_entity_id = p_wip_entity_id
and to_op_seq_num = l_rtg_op_seq_num);
delete from wsm_copy_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_rtg_op_seq_num;
delete from wsm_copy_op_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_rtg_op_seq_num;
delete from wsm_copy_op_resource_instances
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_rtg_op_seq_num;
delete from wsm_copy_op_resource_usage
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_rtg_op_seq_num;
delete from wsm_copy_requirement_ops
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_rtg_op_seq_num;
UPDATE WSM_COPY_OPERATIONS
set yield = NVL(l_v_op_yield(i), yield),
reco_scheduled_quantity = NVL(l_v_op_qty(i), reco_scheduled_quantity),
--BC: bug 3545531 we will allow user to nullify the dates on WCO
--reco_start_date = NVL(l_v_start_date(i), reco_start_date),
--reco_completion_date = NVL(l_v_compl_date(i), reco_completion_date),
cumulative_yield = NVL(l_v_cum_yield(i), cumulative_yield),/*Added for bugfix:7248992 */
reverse_cumulative_yield= NVL(l_v_rev_cum_yield(i),reverse_cumulative_yield),/*Added for bugfix:7248992 */
reco_start_date = l_v_start_date(i),
reco_completion_date = l_v_compl_date(i),
--EC: bug 3545531
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_v_rtg_op_seq(i);
l_v_rtg_op_seq.delete;
l_v_start_date.delete;
l_v_compl_date.delete;
l_v_recoflag.delete;
l_v_res_ids.delete;
l_v_res_sub_grp.delete;
l_v_res_rpl_grp.delete;
l_v_res_seq_nums.delete;
l_v_res_firm_type.delete;
l_v_res_setup_id.delete;
l_v_res_grp_seq_id.delete;
l_v_res_grp_seq_num.delete;
l_v_res_max_assg_units.delete;
l_v_res_parent_seq_num.delete;
l_v_res_batch_id.delete;
l_v_res_assg_units.delete;
l_v_res_recom.delete;
l_v_setup_op_res_num.delete;
l_v_res.delete;
delete from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = l_job_op_seq_num
and wor.resource_seq_num = l_resource_seq_num
and wor.organization_id = p_org_id;
delete from wip_sub_operation_resources wsor
where wsor.wip_entity_id = p_wip_entity_id
and wsor.operation_seq_num = l_job_op_seq_num
and wsor.resource_seq_num = l_resource_seq_num
and wsor.organization_id = p_org_id;
delete from wip_op_resource_instances wori
where wori.wip_entity_id = p_wip_entity_id
and wori.operation_seq_num = l_job_op_seq_num
and wori.resource_seq_num = l_resource_seq_num
and wori.organization_id = p_org_id;
delete from wip_operation_resource_usage woru
where woru.wip_entity_id = p_wip_entity_id
and woru.operation_seq_num = l_job_op_seq_num
and woru.resource_seq_num = l_resource_seq_num
and woru.organization_id = p_org_id;
delete_setup_info( p_wip_entity_id => p_wip_entity_id,
p_org_id => p_org_id,
p_par_res_seq_num => l_resource_seq_num,
p_op_seq_num => l_job_op_seq_num,
p_handle_curr_op => 'Y',
x_error_code => p_error_code,
x_error_msg => p_error_msg);
/* Need to verify if primary resources in a substitution group are completely deleted,
substitute resources in the same substitution group should also be deleted as they will
be orphaned records.*/
if p_error_code <> 0 then
raise e_wljdi_common_error;
l_v_res.delete;
delete from wsm_copy_op_resource_usage wcoru
where wcoru.wip_entity_id = p_wip_entity_id
and wcoru.operation_seq_num = l_rtg_op_seq_num
and wcoru.resource_seq_num = l_resource_seq_num
and wcoru.organization_id = p_org_id ;
delete from wsm_copy_op_resources wcor
where wcor.wip_entity_id = p_wip_entity_id
and wcor.operation_seq_num = l_rtg_op_seq_num
and wcor.resource_seq_num = l_resource_seq_num
and wcor.organization_id = p_org_id;
delete from wsm_copy_op_resource_instances wcori
where wcori.wip_entity_id = p_wip_entity_id
and wcori.operation_seq_num = l_rtg_op_seq_num
and wcori.resource_seq_num = l_resource_seq_num
and wcori.organization_id = p_org_id;
delete_setup_info( p_wip_entity_id => p_wip_entity_id,
p_org_id => p_org_id,
p_par_res_seq_num => l_resource_seq_num,
p_op_seq_num => l_rtg_op_seq_num,
p_handle_curr_op => 'N',
x_error_code => p_error_code,
x_error_msg => p_error_msg);
l_v_res.delete;
insert into wip_operation_resources
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
department_id,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_id,
scheduled_flag,
assigned_units,
maximum_assigned_units,
batch_id,
firm_flag,
group_sequence_id,
group_sequence_number,
parent_resource_seq,
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
start_date,
completion_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
setup_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
values
(p_wip_entity_id,
l_job_op_seq_num,
l_v_res(l_resource_id).res_seq_num,
p_org_id,
v_wljdi_department_id(l_d),
v_wljdi_resource_id_new(l_d),
l_v_res(l_resource_id).uom_code,
l_v_res(l_resource_id).basis_type,
v_wljdi_usage_rate_or_amt(l_d),
l_v_res(l_resource_id).activity_id,
l_v_res(l_resource_id).scheduled_flag,
v_wljdi_assigned_units(l_d),
l_v_res(l_resource_id).max_assg_units,
v_wljdi_batch_id(l_d),
l_v_res(l_resource_id).firm_type,
v_wljdi_grp_seq_id(l_d),
v_wljdi_grp_seq_num(l_d),
null,
l_v_res(l_resource_id).autocharge_type,
l_v_res(l_resource_id).standard_rate,
0,
0,
l_v_res(l_resource_id).start_date,
l_v_res(l_resource_id).completion_date,
v_wljdi_attribute_category(l_d),
v_wljdi_attribute1(l_d),
v_wljdi_attribute2(l_d),
v_wljdi_attribute3(l_d),
v_wljdi_attribute4(l_d),
v_wljdi_attribute5(l_d),
v_wljdi_attribute6(l_d),
v_wljdi_attribute7(l_d),
v_wljdi_attribute8(l_d),
v_wljdi_attribute9(l_d),
v_wljdi_attribute10(l_d),
v_wljdi_attribute11(l_d),
v_wljdi_attribute12(l_d),
v_wljdi_attribute13(l_d),
v_wljdi_attribute14(l_d),
v_wljdi_attribute15(l_d),
v_wljdi_schedule_seq_num(l_d),
v_wljdi_substitute_group_num(l_d),
l_v_res(l_resource_id).rpl_grp_num,
l_v_res(l_resource_id).setup_id,
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date);
insert into wip_sub_operation_resources
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
department_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_id,
scheduled_flag,
assigned_units,
maximum_assigned_units,
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
completion_date,
start_date,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
setup_id,
request_id,
program_application_id,
program_id,
program_update_date
)
values
(p_wip_entity_id,
l_job_op_seq_num,
l_v_res(l_resource_id).res_seq_num,
p_org_id,
v_wljdi_department_id(l_d),
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
v_wljdi_resource_id_new(l_d),
l_v_res(l_resource_id).uom_code,
l_v_res(l_resource_id).basis_type,
v_wljdi_usage_rate_or_amt(l_d),
l_v_res(l_resource_id).activity_id,
l_v_res(l_resource_id).scheduled_flag,
v_wljdi_assigned_units(l_d),
l_v_res(l_resource_id).max_assg_units,
l_v_res(l_resource_id).autocharge_type,
l_v_res(l_resource_id).standard_rate,
0,
0,
v_wljdi_attribute_category(l_d),
v_wljdi_attribute1(l_d),
v_wljdi_attribute2(l_d),
v_wljdi_attribute3(l_d),
v_wljdi_attribute4(l_d),
v_wljdi_attribute5(l_d),
v_wljdi_attribute6(l_d),
v_wljdi_attribute7(l_d),
v_wljdi_attribute8(l_d),
v_wljdi_attribute9(l_d),
v_wljdi_attribute10(l_d),
v_wljdi_attribute11(l_d),
v_wljdi_attribute12(l_d),
v_wljdi_attribute13(l_d),
v_wljdi_attribute14(l_d),
v_wljdi_attribute15(l_d),
l_v_res(l_resource_id).start_date,
l_v_res(l_resource_id).completion_date,
v_wljdi_schedule_seq_num(l_d),
v_wljdi_substitute_group_num(l_d),
l_v_res(l_resource_id).rpl_grp_num,
l_v_res(l_resource_id).setup_id,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date);
insert into wip_operation_resource_usage
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
start_date,
completion_date,
assigned_units,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
cumulative_processing_time)
values
(p_wip_entity_id,
l_job_op_seq_num,
l_v_res(l_resource_id).res_seq_num,
p_org_id,
l_v_res(l_resource_id).start_date,
l_v_res(l_resource_id).completion_date,
v_wljdi_assigned_units(l_d),
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date,
to_number(round((l_v_res(l_resource_id).completion_date - l_v_res(l_resource_id).start_date)*1440)));
l_v_res.delete;
INSERT INTO WSM_COPY_OP_RESOURCES
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
substitute_group_num,
replacement_group_num,
recommended,
reco_start_date,
reco_completion_date,
resource_id,
resource_code,
department_id,
phantom_flag,
phantom_op_seq_num,
phantom_item_id,
activity_id,
standard_rate_flag,
assigned_units,
max_assigned_units,
firm_type,
usage_rate_or_amount,
usage_rate_or_amount_inverse,
uom_code,
basis_type,
schedule_flag,
resource_offset_percent,
autocharge_type,
schedule_seq_num,
principle_flag,
setup_id,
change_notice,
acd_type,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
original_system_reference
)
values
(p_wip_entity_id,
l_rtg_op_seq_num,
l_v_res(l_resource_id).res_seq_num,
p_org_id,
v_wljdi_substitute_group_num(l_d),
nvl(v_wljdi_replacement_group_num(l_d),0),
l_v_res(l_resource_id).reco_flag,
l_v_res(l_resource_id).start_date,
l_v_res(l_resource_id).completion_date,
v_wljdi_resource_id_new(l_d),
l_v_res(l_resource_id).resource_code,
v_wljdi_department_id(l_d),
null,
null,
null,
l_v_res(l_resource_id).activity_id,
l_v_res(l_resource_id).standard_rate,
v_wljdi_assigned_units(l_d),
l_v_res(l_resource_id).max_assg_units,
l_v_res(l_resource_id).firm_type,
v_wljdi_usage_rate_or_amt(l_d),
1/v_wljdi_usage_rate_or_amt(l_d),
l_v_res(l_resource_id).uom_code,
l_v_res(l_resource_id).basis_type,
l_v_res(l_resource_id).scheduled_flag,
null,
l_v_res(l_resource_id).autocharge_type,
v_wljdi_schedule_seq_num(l_d),
null,
l_v_res(l_resource_id).setup_id,
null,
null,
p_last_updt_date,
p_last_updt_by,
p_last_updt_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date,
v_wljdi_attribute_category(l_d),
v_wljdi_attribute1(l_d),
v_wljdi_attribute2(l_d),
v_wljdi_attribute3(l_d),
v_wljdi_attribute4(l_d),
v_wljdi_attribute5(l_d),
v_wljdi_attribute6(l_d),
v_wljdi_attribute7(l_d),
v_wljdi_attribute8(l_d),
v_wljdi_attribute9(l_d),
v_wljdi_attribute10(l_d),
v_wljdi_attribute11(l_d),
v_wljdi_attribute12(l_d),
v_wljdi_attribute13(l_d),
v_wljdi_attribute14(l_d),
v_wljdi_attribute15(l_d),
null);
insert into wsm_copy_op_resource_usage
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
start_date,
completion_date,
assigned_units,
cumulative_processing_time,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
values
(p_wip_entity_id,
l_rtg_op_seq_num,
l_v_res(l_resource_id).res_seq_num,
p_org_id,
l_v_res(l_resource_id).start_date,
l_v_res(l_resource_id).completion_date,
v_wljdi_assigned_units(l_d),
to_number(round((l_v_res(l_resource_id).completion_date - l_v_res(l_resource_id).start_date)*1440)),
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date);
/* This is primarily used to delete both the resource and its setup from the v_wldji... so that the Adding is once for the resource and
its setup resource
... */
l_stmt_num := 202;
l_v_res.delete;
fnd_file.put_line(fnd_file.log,'import job details : before calling delete setup resource '
|| ' parent res.... : ' || l_v_res(l_index).res_seq_num );
delete_setup_info( p_wip_entity_id => p_wip_entity_id,
p_org_id => p_org_id,
p_par_res_seq_num => l_v_res(l_index).res_seq_num,
p_op_seq_num => l_job_op_seq_num,
p_handle_curr_op => 'Y',
x_error_code => p_error_code,
x_error_msg => p_error_msg
);
select 0
into l_swap
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_job_op_seq_num
and NVL(substitute_group_num, -1) = NVL(l_sub_grp_num, -1)
and NVL(replacement_group_num, 0) = NVL(l_rpl_grp_num, 0);
select 1
into l_exists
from wip_cost_txn_interface
where wip_entity_id = p_wip_entity_id
and transaction_type = 1
and operation_seq_num = l_job_op_seq_num
and organization_id = p_org_id;
UPDATE WIP_OPERATION_RESOURCES
set start_date = NVL(l_v_res(l_resource_id).start_date,
start_date),
completion_date = NVL(l_v_res(l_resource_id).completion_date,
completion_date),
/* ST : Detailed Scheduling start */
firm_flag = NVL(l_v_res(l_resource_id).firm_type,firm_flag),
setup_id = NVL(l_v_res(l_resource_id).setup_id,setup_id),
group_sequence_id = NVL(l_v_res(l_resource_id).grp_seq_id, group_sequence_id),
group_sequence_number = NVL(l_v_res(l_resource_id).grp_seq_num, group_sequence_number),
assigned_units = NVL(l_v_res(l_resource_id).assigned_units,assigned_units),
maximum_assigned_units = NVL(l_v_res(l_resource_id).max_assg_units,maximum_assigned_units),
--parent_resource_seq = NVL(l_v_res(l_resource_id).parent_seq_num,parent_resource_seq),
batch_id = NVL(l_v_res(l_resource_id).batch_id,batch_id),
/* ST : Detailed Scheduling end */
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
creation_date = p_creation_date,
created_by = p_created_by,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and resource_id = l_resource_id
-- ST : Detailed Scheduling .. use resource seq num when possible...
and resource_seq_num = nvl(l_v_res(l_resource_id).res_seq_num,resource_seq_num)
-- ST : Detailed Scheduling end...
and nvl(substitute_group_num, -1) = nvl(l_sub_grp_num, -1)
and nvl(replacement_group_num, 0) = nvl(l_rpl_grp_num, 0);
UPDATE WIP_SUB_OPERATION_RESOURCES
set start_date = NVL(l_v_res(l_resource_id).start_date,
start_date),
completion_date = NVL(l_v_res(l_resource_id).completion_date,
completion_date),
/* ST : Detailed Scheduling start */
assigned_units = NVL(l_v_res(l_resource_id).assigned_units,assigned_units),
maximum_assigned_units = NVL(l_v_res(l_resource_id).max_assg_units,maximum_assigned_units),
/* ST : Detailed Scheduling end */
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
creation_date = p_creation_date,
created_by = p_created_by,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and resource_id = l_resource_id
-- ST : Detailed Scheduling .. use resource seq num when possible...
and resource_seq_num = nvl(l_v_res(l_resource_id).res_seq_num,resource_seq_num)
-- ST : Detailed Scheduling end...
and nvl(substitute_group_num, -1) = nvl(l_sub_grp_num, -1)
and nvl(replacement_group_num, 0) = nvl(l_rpl_grp_num, 0);
l_v_res.delete;
delete_setup_info( p_wip_entity_id => p_wip_entity_id,
p_org_id => p_org_id,
p_par_res_seq_num => l_v_res(l_index).res_seq_num,
p_op_seq_num => l_rtg_op_seq_num,
p_handle_curr_op => 'N',
x_error_code => p_error_code,
x_error_msg => p_error_msg
);
l_v_disco_res_ids.delete;
l_v_disco_res_rpl_grp.delete;
l_v_disco_res_seq_num.delete;
This shouldnt be a problem only that we will still insert setup resources and then delete it here...
*/
l_hash_str := to_char(p_wip_entity_id) || ':' || to_char(l_rtg_op_seq_num) || ':' || to_char(l_v_disco_res_ids(l_idx2));
/* Add code here to delete the setup resource info.....*/
p_error_code := 0;
delete_setup_info( p_wip_entity_id => p_wip_entity_id,
p_org_id => p_org_id,
p_par_res_seq_num => l_v_disco_res_seq_num(l_idx2),
p_op_seq_num => l_rtg_op_seq_num,
p_handle_curr_op => 'N',
x_error_code => p_error_code,
x_error_msg => p_error_msg
);
UPDATE WSM_COPY_OP_RESOURCES
set reco_start_date = l_v_start_date(i),
reco_completion_date = l_v_compl_date(i),
recommended = NVL(l_v_recoflag(i), recommended),
/* ST : Detailed Scheduling start */
/* Modified Query by Adding NVL during update for firm_type. bug 5471266
Already assig_units, max_assg_units were having nvl in it.*/
firm_type = NVL( l_v_res_firm_type(i) , firm_type),
setup_id = l_v_res_setup_id(i),
group_sequence_id = l_v_res_grp_seq_id(i),
group_sequence_num = l_v_res_grp_seq_id(i),
max_assigned_units = nvl( l_v_res_max_assg_units(i) , max_assigned_units),
assigned_units = nvl( l_v_res_assg_units(i) , assigned_units),
batch_id = l_v_res_batch_id(i),
/* ST : Detailed Scheduling end */
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_v_rtg_op_seq(i)
and resource_id = l_v_res_ids(i)
and resource_seq_num = nvl(l_v_res_seq_nums(i),resource_seq_num)
and nvl(substitute_group_num, -1) = nvl(l_v_res_sub_grp(i), -1)
and nvl(replacement_group_num, 0) = nvl(l_v_res_rpl_grp(i), 0);
l_v_rtg_op_seq.delete;
l_v_res_seq_num.delete;
l_v_start_date.delete;
l_v_compl_date.delete;
l_v_instance_id.delete;
l_v_serial_num.delete;
l_v_res_ins_batch_id.delete;
l_v_res_ins.delete;
delete from wip_op_resource_instances
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and resource_seq_num = l_res_seq_num;
delete from wip_operation_resource_usage
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and resource_seq_num = l_res_seq_num;
insert into wip_op_resource_instances(
wip_entity_id ,
organization_id,
operation_seq_num,
resource_seq_num,
instance_id,
serial_number,
start_date,
completion_date,
batch_id ,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
) values (
p_wip_entity_id,
p_org_id,
l_job_op_seq_num,
l_res_seq_num,
l_v_res_ins(l_in).instance_id,
l_v_res_ins(l_in).serial_number,
l_v_res_ins(l_in).start_date,
l_v_res_ins(l_in).completion_date,
l_v_res_ins(l_in).batch_id,
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login);
l_v_res_ins.delete;
delete from wsm_copy_op_resource_instances
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_rtg_op_seq_num
and resource_seq_num = l_res_seq_num;
delete from wsm_copy_op_resource_usage
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_rtg_op_seq_num
and resource_seq_num = l_res_seq_num;
insert into wsm_copy_op_resource_instances(
wip_entity_id ,
organization_id,
operation_seq_num,
resource_seq_num,
instance_id,
serial_number,
start_date ,
completion_date,
batch_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
) values (
p_wip_entity_id,
p_org_id,
l_v_rtg_op_seq(i),
l_v_res_seq_num(i),
l_v_instance_id(i),
l_v_serial_num(i),
l_v_start_date(i),
l_v_compl_date(i),
l_v_res_ins_batch_id(i),
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date);
fnd_file.put_line(fnd_file.log, 'Inserted resource instance ... OK.');
l_v_rtg_op_seq.delete;
l_v_res_seq_num.delete;
l_v_instance_id.delete;
l_v_serial_num.delete;
l_v_start_date.delete;
l_v_compl_date.delete;
l_v_res_use_asgn_units.delete;
l_v_resource_hours.delete;
l_v_res_use.delete;
l_v_dup_usage.delete;
delete from wip_operation_resource_usage
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and resource_seq_num = l_res_seq_num
and NVL(instance_id, -1) = NVL(l_instance_id, -1)
and NVL(serial_number, g_nvl_str) = NVL(l_serial_num, g_nvl_str);
insert into wip_operation_resource_usage(
wip_entity_id ,
organization_id,
operation_seq_num,
resource_seq_num,
instance_id,
serial_number,
start_date ,
completion_date,
assigned_units ,
-- resource_hours, /* ST : Added for Detailed Scheduling */
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
) values (
p_wip_entity_id,
p_org_id,
l_job_op_seq_num,
l_res_seq_num,
l_instance_id,
l_serial_num,
l_v_res_use(l_in).start_date,
l_v_res_use(l_in).completion_date,
l_v_res_use(l_in).assigned_units,
-- l_v_res_use(l_in).resource_hours, /* ST : Added for Detailed Scheduling */
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date);
update_cumulative_time(
p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => l_job_op_seq_num,
p_resource_seq_num => l_res_seq_num,
p_instance_id => l_instance_id,
p_serial_num => l_serial_num,
p_handle_wip_cur_op => 'Y' );
l_v_res_use.delete;
l_v_dup_usage.delete;
delete from wsm_copy_op_resource_usage
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_rtg_op_seq_num
and resource_seq_num = l_res_seq_num;
insert into wsm_copy_op_resource_usage(
wip_entity_id ,
organization_id,
operation_seq_num,
resource_seq_num,
instance_id,
serial_number,
start_date ,
completion_date,
assigned_units ,
resource_hours, /* ST : Added for Detailed Scheduling */
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
) values (
p_wip_entity_id,
p_org_id,
l_v_rtg_op_seq(i),
l_v_res_seq_num(i),
l_v_instance_id(i),
l_v_serial_num(i),
l_v_start_date(i),
l_v_compl_date(i),
l_v_res_use_asgn_units(i),
l_v_resource_hours(i), /* ST : Added for Detailed Scheduling */
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date);
update_cumulative_time(
p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => l_v_rtg_op_seq(i),
p_resource_seq_num => l_v_res_seq_num(i),
p_instance_id => l_v_instance_id(i),
p_serial_num => l_v_serial_num(i),
p_handle_wip_cur_op => 'N' );
l_v_rtg_op_seq.delete;
l_v_comp_item_id.delete;
l_v_comp_prm_item_id.delete;
l_v_comp_phm_item_id.delete;
l_v_comp_reqd_date.delete;
l_v_recoflag.delete;
l_v_comp_yield.delete;
l_v_comp_qpa.delete;
l_v_comp.delete;
delete wip_requirement_operations
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and inventory_item_id = l_item_old_id;
l_v_comp.delete;
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,
component_sequence_id,
wip_supply_type,
date_required,
required_quantity,
quantity_issued,
quantity_per_assembly,
supply_subinventory,
supply_locator_id,
mrp_net_flag,
auto_request_material,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
department_id,
costed_quantity_issued,
costed_quantity_relieved,
released_quantity)
select
l_v_comp(l_in).item_id,
p_org_id,
p_wip_entity_id,
l_v_comp(l_in).job_op_seq_num,
null,
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
null,
l_v_comp(l_in).wip_supply_type,
l_v_comp(l_in).date_required,
l_v_comp(l_in).required_qty,
0,
l_v_comp(l_in).qpa,
l_v_comp(l_in).supply_subinventory,
l_v_comp(l_in).supply_locator,
v_wljdi_mrp_net_flag(l_d),
v_wljdi_auto_request_material(l_d),
v_wljdi_comments(l_d),
v_wljdi_attribute_category(l_d),
v_wljdi_attribute1(l_d),
v_wljdi_attribute2(l_d),
v_wljdi_attribute3(l_d),
v_wljdi_attribute4(l_d),
v_wljdi_attribute5(l_d),
v_wljdi_attribute6(l_d),
v_wljdi_attribute7(l_d),
v_wljdi_attribute8(l_d),
v_wljdi_attribute9(l_d),
v_wljdi_attribute10(l_d),
v_wljdi_attribute11(l_d),
v_wljdi_attribute12(l_d),
v_wljdi_attribute13(l_d),
v_wljdi_attribute14(l_d),
v_wljdi_attribute15(l_d),
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date,
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,
wo.department_id,
null,
null,
round(l_cur_job_qty*l_v_comp(l_in).qpa, 6)
from wip_operations wo,
mtl_system_items msi
where wo.operation_seq_num = l_v_comp(l_in).job_op_seq_num
and wo.wip_entity_id= p_wip_entity_id
and msi.inventory_item_id = l_v_comp(l_in).item_id
and msi.organization_id = wo.organization_id
and msi.organization_id = p_org_id;
l_v_comp.delete;
delete wsm_copy_requirement_ops
where operation_seq_num=(-1)*l_rtg_op_seq_num
and wip_entity_id =p_wip_entity_id
and component_item_id =l_item_old_id;
delete wsm_copy_requirement_ops
where operation_seq_num=l_rtg_op_seq_num
and wip_entity_id =p_wip_entity_id
and component_item_id =l_item_old_id;
l_v_comp.delete;
INSERT INTO WSM_COPY_REQUIREMENT_OPS
(wip_entity_id,
operation_seq_num,
component_item_id,
primary_component_id,
component_sequence_id,
source_phantom_id,
bill_sequence_id,
effectivity_date,
planning_factor,
quantity_related,
include_in_cost_rollup,
check_atp,
bom_item_type,
recommended,
reco_date_required,
department_id,
organization_id,
wip_supply_type,
supply_subinventory,
supply_locator_id,
quantity_per_assembly,
bill_quantity_per_assembly,
component_yield_factor,
component_remarks,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
select
p_wip_entity_id,
decode(l_v_comp(l_in).wip_supply_type, WIP_CONSTANTS.PHANTOM,(-1*l_rtg_op_seq_num),l_rtg_op_seq_num),
l_v_comp(l_in).item_id,
decode(nvl(v_wljdi_src_phantom_item_id(l_d),-1),-1,l_v_comp(l_in).item_id,
0,l_v_comp(l_in).item_id, v_wljdi_src_phantom_item_id(l_d)),
-1, -- Since not in bill
decode(nvl(v_wljdi_src_phantom_item_id(l_d),-1), 0 , l_v_comp(l_in).item_id,
-1, -1, v_wljdi_src_phantom_item_id(l_d)),
-1, -- Since not in bill
p_creation_date, -- Effective as on created date for job
0, -- Since it's not a planned component
2, -- No quantity relation
1, -- Include in cost rollup
2, -- No ATP
4, -- Standard component
v_wljdi_recommended(l_d),
l_v_comp(l_in).date_required,
wco.department_id,
p_org_id,
l_v_comp(l_in).wip_supply_type,
l_v_comp(l_in).supply_subinventory,
l_v_comp(l_in).supply_locator,
l_v_comp(l_in).qpa,
1,
l_v_comp(l_in).component_yield,
v_wljdi_comments(l_d),
p_last_updt_date,
p_last_updt_by,
p_last_updt_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date,
v_wljdi_attribute_category(l_d),
v_wljdi_attribute1(l_d),
v_wljdi_attribute2(l_d),
v_wljdi_attribute3(l_d),
v_wljdi_attribute4(l_d),
v_wljdi_attribute5(l_d),
v_wljdi_attribute6(l_d),
v_wljdi_attribute7(l_d),
v_wljdi_attribute8(l_d),
v_wljdi_attribute9(l_d),
v_wljdi_attribute10(l_d),
v_wljdi_attribute11(l_d),
v_wljdi_attribute12(l_d),
v_wljdi_attribute13(l_d),
v_wljdi_attribute14(l_d),
v_wljdi_attribute15(l_d)
from
wsm_copy_operations wco
where wco.wip_entity_id = p_wip_entity_id
and wco.organization_id = p_org_id
and wco.operation_seq_num= l_rtg_op_seq_num;
l_v_comp.delete;
UPDATE WIP_REQUIREMENT_OPERATIONS
set date_required = l_v_comp(l_in).date_required,
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and operation_seq_num = l_job_op_seq_num
and inventory_item_id = l_item_id;
l_v_comp.delete;
SELECT nvl(use_phantom_routings, 0)
INTO l_use_phantom_routings
FROM BOM_PARAMETERS
WHERE ORGANIZATION_ID = p_org_id;
l_v_disco_item_id.delete;
l_v_disco_comp_seq_id.delete;
l_v_disco_src_phm_id.delete;
update wsm_copy_requirement_ops
set reco_date_required = NVL(l_v_comp_reqd_date(i), reco_date_required),
component_yield_factor = nvl(l_v_comp_yield(i), component_yield_factor),
quantity_per_assembly = nvl(l_v_comp_qpa(i), quantity_per_assembly), -- ADD bug 3347985
recommended = NVL(l_v_recoflag(i), recommended),
last_update_date = p_last_updt_date,
last_updated_by = p_last_updt_by,
last_update_login = p_last_updt_login,
request_id = p_request_id,
program_application_id = p_program_appl_id,
program_id = p_program_id,
program_update_date = p_prog_updt_date
where wip_entity_id = p_wip_entity_id
and operation_seq_num = l_v_rtg_op_seq(i)
and component_item_id = l_v_comp_item_id(i)
and primary_component_id = l_v_comp_prm_item_id(i)
--and nvl(source_phantom_id, -1)
-- = nvl(nvl(l_v_comp_phm_item_id(i), source_phantom_id), -1)
and source_phantom_id = nvl(l_v_comp_phm_item_id(i), -1)
and component_sequence_id = decode(l_v_comp_phm_item_id(i),
null, component_sequence_id,
-1, component_sequence_id,
l_v_comp_comp_seq_id(i));
select
wsli.header_id,
wsli.lot_number,
wsli.inventory_item_id,
wsli.organization_id,
wsli.quantity,
wsli.subinventory_code,
wsli.locator_id,
wsli.revision,
wsli.last_updated_by,
wsli.created_by,
msi.primary_uom_code,
wsli.component_issue_quantity
from wsm_starting_lots_interface wsli,
wsm_lot_job_interface wlji,
mtl_system_items msi
where wsli.header_id = wlji.source_line_id
and wlji.group_id = p_group_id
and wlji.process_status = 2 -- WIP_CONSTANTS.running
and wlji.mode_flag = 2
and msi.inventory_item_id = wsli.inventory_item_id
and msi.organization_id = wsli.organization_id;
v_wsli_last_updated_by,
v_wsli_created_by,
v_wsli_primary_uom_code,
v_wsli_comp_issue_qty;
v_wsli(v_wsli_hdr_id(v_wsli_idx)).last_updated_by := v_wsli_last_updated_by(v_wsli_idx);
select mmtt.transaction_temp_id,
mmtt.error_code,
mmtt.error_explanation,
wsli.header_id,
wlji.header_id
from mtl_material_transactions_temp mmtt,
wsm_starting_lots_interface wsli,
wsm_lot_job_interface wlji
where mmtt.transaction_header_id = p_header_id
and mmtt.source_line_id = wsli.header_id
and wsli.header_id = wlji.source_line_id;
update wsm_lot_job_interface wljia
set wljia.process_status = 4,
wljia.error_code = -2,
wljia.error_msg = substr(l_err_explanation,1,240)
where wljia.header_id = l_wlji_header_id;
select distinct 'x'
from wip_discrete_jobs wdj,
wip_period_balances wpb
where wdj.wip_entity_id = wpb.wip_entity_id
and wdj.organization_id = wpb.organization_id
and wdj.wip_entity_id = p_wip_entity_id
and wdj.organization_id = p_organization_id
and ( wdj.quantity_completed <> 0
or wdj.quantity_scrapped <> 0
or wpb.tl_resource_in <> 0
or wpb.tl_overhead_in <> 0
or wpb.tl_outside_processing_in <> 0
or wpb.pl_material_in <> 0
or wpb.pl_material_overhead_in <> 0
or wpb.pl_resource_in <> 0
or wpb.pl_overhead_in <> 0
or wpb.pl_outside_processing_in <> 0
or wpb.tl_material_out <> 0
or wpb.tl_resource_out <> 0
or wpb.tl_overhead_out <> 0
or wpb.tl_outside_processing_out <> 0
or wpb.pl_material_out <> 0
or wpb.pl_material_overhead_out <> 0
or wpb.pl_resource_out <> 0
or wpb.pl_overhead_out <> 0
or wpb.pl_outside_processing_out <> 0
or exists (
select 'x'
from wip_requirement_operations
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id
and quantity_issued <> 0)
or exists (
select 'x'
from wip_move_txn_interface
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id)
or exists (
select 'x'
from wsm_lot_move_txn_interface
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id)
or exists (
select 'x'
from wip_cost_txn_interface
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id)
or exists (
select 'x'
from mtl_material_transactions_temp
where organization_id = p_organization_id
and transaction_source_type_id = 5
and transaction_source_id = p_wip_entity_id)
or exists (
select 'x'
from wip_move_transactions
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id)
/* ST bug fix 3493984 : commented this code */
/*or exists (
select 'x'
from wsm_sm_resulting_jobs
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id) */
/* ST bug fix 3493984 : Added code to consider only successful WIP lot transactions */
or exists (
select 'x'
from wsm_sm_resulting_jobs wsrj
where wsrj.organization_id = p_organization_id
and wsrj.wip_entity_id = p_wip_entity_id
and exists (select transaction_id
from wsm_split_merge_transactions wsmt
where wsmt.transaction_id = wsrj.transaction_id
and wsmt.status = 4 )
)
/* ST bug fix 3493984 : end */
or exists (
select 'x'
from wip_operation_resources
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id
and applied_resource_units <> 0));
select distinct 'x'
from wip_discrete_jobs wdj,
wip_period_balances wpb
where wdj.wip_entity_id = wpb.wip_entity_id
and wdj.organization_id = wpb.organization_id
and wdj.wip_entity_id = p_wip_entity_id
and wdj.organization_id = p_organization_id
and ( wdj.quantity_completed <> 0
or wdj.quantity_scrapped <> 0
or wpb.tl_resource_in <> 0
or wpb.tl_overhead_in <> 0
or wpb.tl_outside_processing_in <> 0
or wpb.pl_resource_in <> 0
or wpb.pl_overhead_in <> 0
or wpb.pl_outside_processing_in <> 0
or wpb.tl_resource_out <> 0
or wpb.tl_overhead_out <> 0
or wpb.tl_outside_processing_out <> 0
or wpb.pl_resource_out <> 0
or wpb.pl_overhead_out <> 0
or wpb.pl_outside_processing_out <> 0
or exists (
select 'x'
from wip_move_txn_interface
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id)
or exists (
select 'x'
from wsm_lot_move_txn_interface
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id)
or exists (
select 'x'
from wip_cost_txn_interface
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id)
or exists (
select 'x'
from wip_move_transactions
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id)
or exists (
select 'x'
from wsm_sm_resulting_jobs
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id)
or exists (
select 'x'
from wip_operation_resources
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id
and applied_resource_units <> 0));
PROCEDURE insert_into_period_balances (
p_wip_entity_id in number,
p_organization_id in number,
p_class_code in varchar2,
p_release_date in date,
p_error_code out nocopy number,
p_err_msg out nocopy varchar2 ) is
x_user_id number := fnd_global.user_id;
insert into wip_period_balances (
acct_period_id,
wip_entity_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
class_type,
tl_resource_in,
tl_overhead_in,
tl_outside_processing_in,
pl_material_in,
pl_material_overhead_in,
pl_resource_in,
pl_overhead_in,
pl_outside_processing_in,
tl_material_out,
tl_resource_out,
tl_overhead_out,
tl_outside_processing_out,
pl_material_out,
pl_material_overhead_out,
pl_resource_out,
pl_overhead_out,
pl_outside_processing_out,
pl_material_overhead_var,
pl_material_var,
pl_outside_processing_var,
pl_overhead_var,
pl_resource_var,
tl_material_var,
tl_outside_processing_var,
tl_overhead_var,
tl_resource_var,
tl_material_overhead_out,
tl_material_overhead_var)
select oap.acct_period_id,
p_wip_entity_id,
sysdate, x_user_id,
sysdate, x_user_id, x_login_id,
p_organization_id, wc.class_type,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0
from org_acct_periods oap,
wip_accounting_classes wc
where wc.class_code = p_class_code
and wc.organization_id = p_organization_id
and oap.organization_id = p_organization_id
and oap.schedule_close_date >=
trunc(inv_le_timezone_pub.get_le_day_for_inv_org(
nvl(p_release_date, sysdate),
p_organization_id))
and oap.period_close_date is null
and not exists (
select 'balance record already there'
from wip_period_balances wpb
where wpb.wip_entity_id = p_wip_entity_id
and wpb.acct_period_id = oap.acct_period_id
and wpb.organization_id = oap.organization_id);
fnd_file.put_line(fnd_file.log, 'Inserted '||l_cnt||' rows into wip_period_balances');
p_err_msg := 'insert_into_period_balances: '|| substr(SQLERRM,1,200);
end insert_into_period_balances;
select from_op_seq_num,
from_op_seq_id,
level
from wsm_copy_op_networks
start with (to_op_seq_num = l_rtg_op_seq_num0 and
recommended = 'Y' and
wip_entity_id = p_wip_entity_id)
connect by (to_op_seq_num = prior from_op_seq_num and
recommended = 'Y' and
wip_entity_id = p_wip_entity_id)
union
select l_rtg_op_seq_num0 "from_op_seq_num",
l_op_seq_id,
-1
from dual
) order by 2 desc;
IF v_insert_wip is NULL or v_insert_wip = 2 THEN
l_job_op_seq_num := 0;
select max(operation_seq_num)
into l_max_op_seq_num
from wip_operations
where wip_entity_id = p_wip_entity_id;
update wip_operations wo
set wo.previous_operation_seq_num = (
select max(operation_seq_num)
from wip_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num < wo.operation_seq_num),
wo.next_operation_seq_num = (
select min(operation_seq_num)
from wip_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num > wo.operation_seq_num),
wo.quantity_in_queue =
decode(operation_seq_num,
l_max_op_seq_num,
(decode(p_status_type,
3, round(p_start_quantity,
wip_constants.max_displayed_precision),
0)),
0)
where wo.wip_entity_id = p_wip_entity_id;
fnd_file.put_line(fnd_file.log,'Update Quantity, Prev/Next Op in wo(l_stmt_num='
||l_stmt_num||'): '|| SQL%ROWCOUNT);
update wip_operations
set quantity_completed = p_start_quantity
where wip_entity_id = p_wip_entity_id
and operation_seq_num < l_max_op_seq_num;
fnd_file.put_line(fnd_file.log,'Update Quantity_Completed in wo(l_stmt_num='
||l_stmt_num||'): '|| SQL%ROWCOUNT);
update wip_requirement_operations
set quantity_issued = required_quantity
where wip_entity_id = p_wip_entity_id
and operation_seq_num < l_max_op_seq_num
and wip_supply_type not in (2, 4, 5, 6); -- Fix for bug #2685463
fnd_file.put_line(fnd_file.log,'Update Quantity_Issued in wro(l_stmt_num='
||l_stmt_num||'): '|| SQL%ROWCOUNT);
update wsm_lot_based_jobs
set (current_job_op_seq_num,
current_rtg_op_seq_num)
=(select wo.operation_seq_num,
wo.wsm_op_seq_num
from wip_operations wo
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and wo.quantity_in_queue
+ wo.quantity_running
+ wo.quantity_waiting_to_move > 0)
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
END IF;--Check on v_insert_wip
v_insert_wip := NULL;
update wsm_lot_based_jobs
set (current_job_op_seq_num,
current_rtg_op_seq_num)
=(select wo.operation_seq_num,
wo.wsm_op_seq_num
from wip_operations wo
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and wo.quantity_in_queue
+ wo.quantity_running
+ wo.quantity_waiting_to_move > 0)
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
select wip_entities_s.nextval
into p_wip_entity_id
from dual;
select wac.material_account,
wac.material_overhead_account,
wac.resource_account,
wac.outside_processing_account,
wac.material_variance_account,
wac.resource_variance_account,
wac.outside_proc_variance_account,
wac.std_cost_adjustment_account,
wac.overhead_account,
wac.overhead_variance_account,
params.po_creation_time,
wac.est_scrap_account,
wac.est_scrap_var_account
into l_material_account,
l_material_overhead_account,
l_resource_account,
l_outside_processing_account,
l_material_variance_account,
l_resource_variance_account,
l_outside_proc_var_acc,
l_std_cost_adjustment_account,
l_overhead_account,
l_overhead_variance_account,
l_po_creation_time,
l_est_scrap_account,
l_est_scrap_var_account
from wip_accounting_classes wac,
wip_parameters params
where wac.class_code(+)= p_class_code
and wac.organization_id(+)= p_org_id
and params.organization_id = p_org_id;
insert into wip_discrete_jobs (
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
source_line_id,
source_code,
description,
status_type,
date_released,
primary_item_id,
bom_reference_id,
routing_reference_id,
firm_planned_flag,
job_type,
wip_supply_type,
class_code,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
material_variance_account,
resource_variance_account,
outside_proc_variance_account,
std_cost_adjustment_account,
overhead_account,
overhead_variance_account,
scheduled_start_date,
scheduled_completion_date,
start_quantity,
quantity_completed,
quantity_scrapped,
net_quantity,
common_bom_sequence_id,
common_routing_sequence_id,
bom_revision,
routing_revision,
bom_revision_date,
routing_revision_date,
lot_number,
alternate_bom_designator,
alternate_routing_designator,
completion_subinventory,
completion_locator_id,
demand_class,
project_id,
task_id,
schedule_group_id,
build_sequence,
line_id,
kanban_card_id,
overcompletion_tolerance_type,
overcompletion_tolerance_value,
end_item_unit_number,
po_creation_time,
priority,
due_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
est_scrap_account,
est_scrap_var_account,
coproducts_supply
) values (
p_wip_entity_id,
p_org_id,
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date,
p_source_line_id,
p_source_code,
p_description,
p_status_type,
-- BC: bug 3382472
--NVL(p_date_released, decode(p_status_type, wip_constants.unreleased, null, sysdate)),
NVL(p_date_released, decode(p_status_type,
wip_constants.released, sysdate,
null)),
-- EC: bug 3382472
p_item,
decode(p_job_type, 3, p_bom_reference_id, null),
decode(p_job_type, 3, p_routing_reference_id, null),
p_firm_planned_flag,
decode(p_job_type, 3, wip_constants.nonstandard, wip_constants.standard),
p_wip_supply_type,
p_class_code,
l_material_account,
l_material_overhead_account,
l_resource_account,
l_outside_processing_account,
l_material_variance_account,
l_resource_variance_account,
l_outside_proc_var_acc,
l_std_cost_adjustment_account,
l_overhead_account,
l_overhead_variance_account,
trunc(p_job_scheduled_start_date,'mi'), --round(p_job_scheduled_start_date,'mi'),
trunc(p_job_scheduled_compl_date,'mi'), --round(p_job_scheduled_compl_date,'mi'),
round(p_start_quantity, wip_constants.max_displayed_precision),
0, -- quantity_completed
0, -- quantity_scrapped
round(p_net_quantity, wip_constants.max_displayed_precision),
p_common_bill_sequence_id,
p_common_routing_sequence_id,
p_bom_revision,
p_routing_revision,
p_bom_revision_date,
p_routing_revision_date,
p_lot_number,
p_alt_bom_designator,
p_alt_routing_designator,
p_completion_subinventory,
p_completion_locator_id,
p_demand_class,
p_project_id,
p_task_id,
p_schedule_group_id,
p_build_sequence,
p_line_id,
p_kanban_card_id,
p_overcompl_tol_type,
p_overcompl_tol_value,
p_end_item_unit_number,
l_po_creation_time,
p_priority,
p_due_date,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
l_est_scrap_account,
l_est_scrap_var_account,
p_coproducts_supply
);
fnd_file.put_line(fnd_file.log, 'Inserted '||SQL%ROWCOUNT||' rows into wdj');
insert into wsm_lot_based_jobs (
wip_entity_id,
organization_id,
on_rec_path,
internal_copy_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
) values (
p_wip_entity_id,
p_org_id,
'Y',
1,
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date
);
fnd_file.put_line(fnd_file.log, 'Inserted '||SQL%ROWCOUNT||' rows into wlbj');
insert into wip_entities (
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
wip_entity_name,
entity_type,
description,
primary_item_id,
gen_object_id
) values (
p_wip_entity_id,
p_org_id,
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_appl_id,
p_program_id,
p_prog_updt_date,
p_job_name,
5,
p_description,
p_item,
mtl_gen_object_id_s.nextval
);
fnd_file.put_line(fnd_file.log, 'Inserted '||SQL%ROWCOUNT||' rows into we');
inv_kanban_pvt.update_card_supply_status(
x_return_status => l_return_status,
p_kanban_card_id => p_kanban_card_id,
p_supply_status => inv_kanban_pvt.g_supply_status_InProcess,
p_document_type => inv_kanban_pvt.G_Doc_type_lot_job,
p_document_header_id => p_wip_entity_id,
p_Document_detail_Id => null,
p_replenish_quantity => p_start_quantity);
select meaning
into l_translated_meaning
from mfg_lookups
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
and lookup_code = 5
and upper(enabled_flag) = 'Y';
insert_into_period_balances (
p_wip_entity_id => p_wip_entity_id,
p_organization_id => p_org_id,
p_class_code => p_class_code,
p_release_date => p_date_released,
p_error_code => p_error_code,
p_err_msg => p_error_msg );
insert INTO WSM_JOB_SECONDARY_QUANTITIES (
WIP_ENTITY_ID,
ORGANIZATION_ID,
UOM_CODE,
START_QUANTITY,
CURRENT_QUANTITY,
CURRENTLY_ACTIVE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY
)
select
p_wip_entity_id,
p_org_id,
wsu.UOM_CODE,
null,
null,
1, -- CURRENTLY_ACTIVE = Yes
p_last_updt_date,
p_last_updt_by,
p_last_updt_login,
p_creation_date,
p_created_by
from WSM_SECONDARY_UOMS wsu
where INVENTORY_ITEM_ID = p_item
and ORGANIZATION_ID = p_org_id;
insert into WSM_JOB_SECONDARY_QUANTITIES
(
WIP_ENTITY_ID,
ORGANIZATION_ID,
UOM_CODE,
START_QUANTITY,
CURRENT_QUANTITY,
CURRENTLY_ACTIVE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY
) values (
p_wip_entity_id,
p_org_id,
p_wjsq_uom_code(i),
p_wjsq_start_quantity(i),
null,
1, -- CURRENTLY_ACTIVE = Yes
p_last_updt_date,
p_last_updt_by,
p_last_updt_login,
p_creation_date,
p_created_by
);