[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_param_tbl.delete;
l_msg_tokens.delete;
l_wsm_sj_sec_qty_tbl.delete;
l_sj_we_id_tbl.delete;
l_job_qty_tbl.delete;
select *
bulk collect into l_job_qty_tbl
from wsm_job_secondary_quantities
where wip_entity_id = p_wltx_starting_jobs_tbl(l_counter).wip_entity_id
and currently_active= 1;
l_msg_tokens.delete;
l_wsm_sj_sec_qty_tbl(l_sj_tbl_counter).last_update_date := sysdate;
l_wsm_sj_sec_qty_tbl(l_sj_tbl_counter).last_update_login := fnd_global.login_id;
l_wsm_sj_sec_qty_tbl(l_sj_tbl_counter).last_updated_by := fnd_global.user_id;
insert into wsm_sj_secondary_quantities
values l_wsm_sj_sec_qty_tbl(i);
l_msg_tokens.delete;
IF p_wltx_header.transaction_type_id in (WSMPCNST.SPLIT,WSMPCNST.MERGE,WSMPCNST.UPDATE_ASSEMBLY,WSMPCNST.UPDATE_QUANTITY) then
-- For all the new resulting jobs (non-SpUA jobs)
-- bulk insert data into wsm_op_secondary_quantities by reading from wsm_job_secondary_quantities
--Assumes that the wsm_job_secondary_quantities is already populated in lbj build_header_info proc
IF p_wltx_header.transaction_type_id in (WSMPCNST.SPLIT,WSMPCNST.MERGE) then
l_op_tbl_counter := 1;
l_job_qty_tbl.delete;
p_wltx_resulting_jobs_tbl(l_counter).split_has_update_assy = 1
)
THEN
select *
bulk collect
into l_job_qty_tbl
from wsm_job_secondary_quantities
where wip_entity_id = p_wltx_resulting_jobs_tbl(l_counter).wip_entity_id
and currently_active = 1;
l_wsm_op_sec_qty_tbl(l_op_tbl_counter).last_update_date := sysdate;
l_wsm_op_sec_qty_tbl(l_op_tbl_counter).last_update_login:= fnd_global.login_id;
l_wsm_op_sec_qty_tbl(l_op_tbl_counter).last_updated_by := fnd_global.user_id;
insert into wsm_op_secondary_quantities
values l_wsm_op_sec_qty_tbl(i);
p_wltx_resulting_jobs_tbl(p_sj_also_rj_index).split_has_update_assy = 1
)
OR
(p_wltx_header.transaction_type_id = WSMPCNST.UPDATE_ASSEMBLY)
THEN
l_stmt_num := 80.1;
update wsm_job_secondary_quantities
set currently_active =2,
current_quantity=null
where wip_entity_id = l_sj_rj_wip_entity_id
AND uom_code not in (select uom_code
from wsm_secondary_uoms
where inventory_item_id =l_sj_rj_inventory_item_id);
l_msg_tokens.delete;
p_msg_text => 'Updated ' || SQL%ROWCOUNT || ' rows to be inactive in job secondary quantities',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
update wsm_job_secondary_quantities
set currently_active = 1,
current_quantity= null
where wip_entity_id = l_sj_rj_wip_entity_id
AND uom_code in (select uom_code
from wsm_secondary_uoms
where inventory_item_id =l_sj_rj_inventory_item_id)
AND currently_active = 2; --Bugfix 4765660, left out of fix for 5046332
l_msg_tokens.delete;
p_msg_text => 'Updated ' || SQL%ROWCOUNT || ' rows to be active in job secondary quantities',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
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
l_sj_rj_wip_entity_id,
organization_id,
uom_code,
null,
null,
1,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
sysdate,
fnd_global.user_id
from wsm_secondary_uoms
where inventory_item_id = l_sj_rj_inventory_item_id -- (resulting job's item id..)
-- ST : Bug Fix 5046332 : Commenting out the below condition ---
-- and uom_code not in (select uom_code
-- from wsm_secondary_uoms
-- where inventory_item_id= p_wltx_starting_jobs_tbl(p_rep_job_index).primary_item_id)
-- -- ST : Sec. UOM Fix : Use the starting rep jobs's item id --
-- ST : Bug Fix 5046332 : Added the below condition ---
and uom_code not in (select uom_code
from wsm_job_secondary_quantities
where wip_entity_id = l_sj_rj_wip_entity_id)
-- ST : Bug Fix 5046332 : End --
);
l_msg_tokens.delete;
p_msg_text => 'Inserted ' || SQL%ROWCOUNT || ' new rows in in job secondary quantities tables',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
update wsm_job_secondary_quantities
set current_quantity = l_cur_qty_tbl(i)
where wip_entity_id = l_wip_entity_id_tbl(i)
and uom_code = l_cur_uom_tbl(i);
l_msg_tokens.delete;
p_msg_text => 'Processing for Split Merge, Update Assembly Done',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
update wsm_job_secondary_quantities
set currently_active = 2,
current_quantity=null
where wip_entity_id = l_sj_we_id_tbl(i)
and wip_entity_id <> nvl(l_sj_rj_wip_entity_id,0);
select *
bulk collect
into l_sj_op_reason_codes_tbl
from wsm_op_reason_codes
where operation_seq_num = p_wltx_starting_jobs_tbl(p_rep_job_index).operation_seq_num
and wip_entity_id = p_wltx_starting_jobs_tbl(p_rep_job_index).wip_entity_id;
l_msg_tokens.delete;
l_rj_op_reason_codes_tbl.delete;
( p_wltx_header.transaction_type_id = WSMPCNST.SPLIT AND p_wltx_resulting_jobs_tbl(l_counter).split_has_update_assy <> 1)
) AND
( l_counter <> nvl(p_sj_also_rj_index,-1) )
-- Resulting Job shouldnt be a starting job
THEN
l_index := l_sj_op_reason_codes_tbl.first;
l_rj_op_reason_codes_tbl(l_rj_tbl_counter).Last_update_date := sysdate;
l_rj_op_reason_codes_tbl(l_rj_tbl_counter).Last_updated_by := fnd_global.user_id;
l_rj_op_reason_codes_tbl(l_rj_tbl_counter).Last_updated_login := fnd_global.login_id;
l_msg_tokens.delete;
insert into wsm_op_reason_codes
values l_rj_op_reason_codes_tbl(i);
l_msg_tokens.delete;
IF p_wltx_header.transaction_type_id IN (WSMPCNST.BONUS,WSMPCNST.UPDATE_ASSEMBLY,WSMPCNST.UPDATE_ROUTING,WSMPCNST.SPLIT) THEN
l_counter := p_wltx_resulting_jobs_tbl.first;
(p_wltx_header.transaction_type_id = WSMPCNST.SPLIT and p_wltx_resulting_jobs_tbl(l_counter).split_has_update_assy = 1)
THEN
l_stmt_num := 130;
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_wltx_resulting_jobs_tbl(l_counter).wip_entity_id,
p_wltx_resulting_jobs_tbl(l_counter).organization_id,
uom_code,
null,
null,
1,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
sysdate,
fnd_global.user_id
from wsm_secondary_uoms
where inventory_item_id = p_wltx_resulting_jobs_tbl(l_counter).primary_item_id
);
l_msg_tokens.delete;
p_msg_text => 'Inserted ' || SQL%ROWCOUNT || ' new rows in in job secondary quantities tables for bonus',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
l_wsm_rj_sec_qty_tbl.delete;
l_job_qty_tbl.delete;
select *
bulk collect into l_job_qty_tbl
from wsm_job_secondary_quantities
where wip_entity_id = p_wltx_resulting_jobs_tbl(l_counter).wip_entity_id
and currently_active= 1;
l_wsm_rj_sec_qty_tbl(l_rj_tbl_counter).last_update_date := sysdate;
l_wsm_rj_sec_qty_tbl(l_rj_tbl_counter).last_update_login := fnd_global.login_id;
l_wsm_rj_sec_qty_tbl(l_rj_tbl_counter).last_updated_by := fnd_global.user_id;
insert into wsm_rj_secondary_quantities
values l_wsm_rj_sec_qty_tbl(i);
l_msg_tokens.delete;
if p_wltx_resulting_jobs_tbl(i).split_has_update_assy <>1 then
l_wsm_op_reason_codes_tbl.delete;
select *
bulk collect into l_wsm_op_reason_codes_tbl
from wsm_op_reason_codes
where operation_seq_num = p_wltx_resulting_jobs_tbl(i).starting_operation_seq_num
and wip_entity_id = l_sj_we_id
and p_wltx_resulting_jobs_tbl(i).wip_entity_id <> l_sj_we_id;
l_wsm_op_reason_codes_tbl(j).Last_update_date := sysdate;
l_wsm_op_reason_codes_tbl(j).Last_updated_by := fnd_global.user_id;
l_wsm_op_reason_codes_tbl(j).Last_updated_login := fnd_global.login_id;
insert into wsm_op_reason_codes
values l_wsm_op_reason_codes_tbl(k);
insert into wsm_op_reason_codes
(Organization_id,
Wip_entity_id ,
Operation_seq_num,
Code_Type ,
Reason_Code ,
Quantity ,
Created_by ,
Last_update_date,
Last_updated_by ,
Creation_date ,
Last_updated_login
)
(select
p_wltx_resulting_jobs_tbl(l_counter).organization_id,
p_wltx_resulting_jobs_tbl(l_counter).wip_entity_id,
bsobc.sequence_number,
1,
bsobc.bonus_code,
null,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from bom_std_op_bonus_codes bsobc
where sequence_number = p_wltx_resulting_jobs_tbl(l_counter).starting_operation_seq_num
and STANDARD_OPERATION_ID = p_wltx_resulting_jobs_tbl(l_counter).starting_std_op_id
);
insert into wsm_op_reason_codes
(Organization_id,
Wip_entity_id ,
Operation_seq_num,
Code_Type ,
Reason_Code ,
Quantity ,
Created_by ,
Last_update_date,
Last_updated_by ,
Creation_date ,
Last_updated_login
)
(select
p_wltx_resulting_jobs_tbl(l_counter).organization_id,
p_wltx_resulting_jobs_tbl(l_counter).wip_entity_id,
bsosc.sequence_num,
2,
bsosc.scrap_code,
null,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from bom_std_op_scrap_codes bsosc
where sequence_num = p_wltx_resulting_jobs_tbl(l_counter).starting_operation_seq_num
and STANDARD_OPERATION_ID = p_wltx_resulting_jobs_tbl(l_counter).starting_std_op_id
);
if p_wltx_resulting_jobs_tbl (i).split_has_update_assy <>1 then
BEGIN
select *
bulk collect into l_wsm_copy_requirement_ops_tbl
from wsm_copy_requirement_ops
where wip_entity_id = p_wltx_resulting_jobs_tbl(i).wip_entity_id
and recommended = 'N'
and operation_seq_num = p_wltx_resulting_jobs_tbl(i).starting_operation_seq_num
and wip_entity_id <> nvl(l_sj_we_id,-1);
l_wsm_subst_comp_tbl.delete;
l_wsm_subst_comp_tbl(j).DELETE_GROUP_NAME := l_wsm_copy_requirement_ops_tbl(j).DELETE_GROUP_NAME ;
l_wsm_subst_comp_tbl(j).LAST_UPDATE_DATE := l_wsm_copy_requirement_ops_tbl(j).LAST_UPDATE_DATE ;
l_wsm_subst_comp_tbl(j).LAST_UPDATED_BY := l_wsm_copy_requirement_ops_tbl(j).LAST_UPDATED_BY ;
l_wsm_subst_comp_tbl(j).LAST_UPDATED_LOGIN := l_wsm_copy_requirement_ops_tbl(j).LAST_UPDATE_LOGIN ;
insert into WSM_SUBSTITUTE_COMPONENTS
values l_wsm_subst_comp_tbl(k);
SELECT max(operation_seq_num)
INTO p_op_seq_num
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)
OR (quantity_in_queue = 0
and quantity_running = 0
and quantity_waiting_to_move = 0
and quantity_scrapped = quantity_completed -- this picks up te max op seq, if only scraps at ops
and quantity_completed > 0));
SELECT operation_sequence_id,
standard_operation_id,
department_id,
quantity_in_queue,
quantity_running,
quantity_waiting_to_move,
first_unit_start_date,
last_unit_completion_date
INTO p_op_seq_id,
p_std_op_id,
p_dept_id,
l_qty_Q,
l_qty_RUN,
l_qty_TM,
p_op_start_date,
p_op_completion_date
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_op_seq_num;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
INSERT INTO WIP_OPERATIONS
(wip_entity_id,
operation_seq_num,
organization_id,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
operation_sequence_id,
standard_operation_id,
department_id,
description,
scheduled_quantity,
quantity_in_queue,
quantity_running,
quantity_waiting_to_move,
quantity_rejected,
quantity_scrapped,
quantity_completed,
wsm_costed_quantity_completed,
first_unit_start_date,
first_unit_completion_date,
last_unit_start_date,
last_unit_completion_date,
previous_operation_seq_num,
next_operation_seq_num,
count_point_type,
backflush_flag,
minimum_transfer_quantity,
date_last_moved,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
operation_yield_enabled,
operation_yield,
previous_operation_seq_id,
skip_flag, -- Added for I_PROJECT: JUMP_ENH
disable_date, -- bug 2931071
wsm_op_seq_num -- Added to fix bug 3452913
)
(
select
p_new_rj_we_id_tbl(l_job_counter),
wo.operation_seq_num,
wo.organization_id,
wo.repetitive_schedule_id,
wo.last_update_date,
wo.last_updated_by,
wo.creation_date,
wo.created_by,
wo.last_update_login,
wo.request_id,
wo.program_application_id,
wo.program_id,
wo.program_update_date,
wo.operation_sequence_id,
wo.standard_operation_id,
wo.department_id,
wo.description,
-- ST : Added for bug fix 4619823 (Found in UT)
-- (Update Scheduled qty only in case of Current op + Queue)
decode(wo.operation_seq_num, p_curr_op_seq_num,
decode(p_txn_job_intraop,1,p_new_rj_qty_tbl(l_job_counter) ,0),
0),
--scheduled qty behavior change as part of MES, also updated in CHANGE_QUANTITY procedure
decode(wo.quantity_in_queue, 0, 0, p_new_rj_qty_tbl(l_job_counter)),
wo.quantity_running,
decode(wo.quantity_waiting_to_move, 0, 0, p_new_rj_qty_tbl(l_job_counter)),
0, --reject
0, --scrap
0, --qty_completed
-- decode(wo.quantity_completed, 0, 0, p_new_rj_qty_tbl(l_job_counter)),
-- decode(wo.quantity_waiting_to_move, 0, 0, p_new_rj_qty_tbl(l_job_counter)), --Fixed bug #2790626
decode(wo.operation_seq_num, p_curr_op_seq_num,
decode(wo.quantity_waiting_to_move, 0, 0, p_new_rj_qty_tbl(l_job_counter)),
decode(wo.wsm_costed_quantity_completed, 0, 0, p_new_rj_qty_tbl(l_job_counter))),
--wsm_costed_qty_completed (MES change)
wo.first_unit_start_date,
wo.first_unit_completion_date,
wo.last_unit_start_date,
wo.last_unit_completion_date,
wo.previous_operation_seq_num,
wo.next_operation_seq_num,
wo.count_point_type,
wo.backflush_flag,
wo.minimum_transfer_quantity,
wo.date_last_moved,
wo.attribute_category,
wo.attribute1,
wo.attribute2,
wo.attribute3,
wo.attribute4,
wo.attribute5,
wo.attribute6,
wo.attribute7,
wo.attribute8,
wo.attribute9,
wo.attribute10,
wo.attribute11,
wo.attribute12,
wo.attribute13,
wo.attribute14,
wo.attribute15,
wo.operation_yield_enabled,
wo.operation_yield,
wo.previous_operation_seq_id,
wo.skip_flag, -- Added for I_PROJECT: JUMP_ENH
wo.disable_date, -- bug 2931071
wo.wsm_op_seq_num -- Added to fix bug 3452913.
from wip_operations wo
where wo.wip_entity_id = p_rep_we_id
AND wo.operation_seq_num <= p_curr_op_seq_num
);
UPDATE wip_operations wo
SET wo.previous_operation_seq_num =(SELECT max(operation_seq_num)
FROM wip_operations
WHERE wip_entity_id = p_new_rj_we_id_tbl(l_job_counter)
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_new_rj_we_id_tbl(l_job_counter)
AND operation_seq_num > wo.operation_seq_num
)
WHERE wo.wip_entity_id = p_new_rj_we_id_tbl(l_job_counter);
select operation_sequence_id,
operation_seq_num
bulk collect into l_op_seq_id_tbl,l_op_seq_num_tbl
from wip_operations wo
where wip_entity_id = p_rep_we_id
AND wo.operation_seq_num <= p_curr_op_seq_num;
x_last_update_login => g_user_login_id,
x_program_application_id => g_program_appl_id,
x_program_id => g_program_id,
x_request_id => g_request_id
);
x_last_update_login => g_user_login_id,
x_program_application_id => g_program_appl_id,
x_program_id => g_program_id,
x_request_id => g_request_id
);
INSERT INTO WIP_OPERATION_RESOURCES
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_ID,
scheduled_flag,
assigned_units,
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
start_date,
completion_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
schedule_seq_num,
substitute_group_num,
principle_flag,
setup_id ,
-- ST : Detailed Scheduling --
maximum_assigned_units ,
firm_flag ,
parent_resource_seq
-- ST : Detailed Scheduling --
)
(
select
p_new_rj_we_id_tbl(l_job_counter),
wor.operation_seq_num,
wor.resource_seq_num,
wor.organization_id,
wor.repetitive_schedule_id,
wor.last_update_date,
wor.last_updated_by,
wor.creation_date,
wor.created_by,
wor.last_update_login,
wor.request_id,
wor.program_application_id,
wor.program_id,
wor.program_update_date,
wor.resource_id,
wor.uom_code,
wor.basis_type,
wor.usage_rate_or_amount,
wor.activity_id,
wor.scheduled_flag,
wor.assigned_units,
wor.autocharge_type,
wor.standard_rate_flag,
0, --applied_resource_units
0, --applied_resource_value
wor.start_date,
wor.completion_date,
wor.attribute_category,
wor.attribute1,
wor.attribute2,
wor.attribute3,
wor.attribute4,
wor.attribute5,
wor.attribute6,
wor.attribute7,
wor.attribute8,
wor.attribute9,
wor.attribute10,
wor.attribute11,
wor.attribute12,
wor.attribute13,
wor.attribute14,
wor.attribute15,
wor.schedule_seq_num,
wor.substitute_group_num,
wor.principle_flag,
wor.setup_id ,
-- ST : Detailed Scheduling --
wor.maximum_assigned_units ,
0 ,
wor.parent_resource_seq
-- ST : Detailed Scheduling --
from wip_operation_resources wor
WHERE wor.wip_entity_id = p_rep_we_id
AND wor.operation_seq_num <= p_curr_op_seq_num
);
INSERT INTO WIP_SUB_OPERATION_RESOURCES
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_ID,
scheduled_flag,
assigned_units,
maximum_assigned_units, -- ST : Detailed Scheduling --
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,
relieved_res_completion_units,
relieved_res_scrap_units,
relieved_res_completion_value,
relieved_res_scrap_value,
relieved_variance_value,
temp_relieved_value,
relieved_res_final_comp_units,
department_id,
phantom_flag,
phantom_op_seq_num,
phantom_item_id,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
principle_flag,
setup_id
) (
select
p_new_rj_we_id_tbl(l_job_counter),
wsor.operation_seq_num,
wsor.resource_seq_num,
wsor.organization_id,
wsor.repetitive_schedule_id,
wsor.last_update_date,
wsor.last_updated_by,
wsor.creation_date,
wsor.created_by,
wsor.last_update_login,
wsor.request_id,
wsor.program_application_id,
wsor.program_id,
wsor.program_update_date,
wsor.resource_id,
wsor.uom_code,
wsor.basis_type,
wsor.usage_rate_or_amount,
wsor.activity_id,
wsor.scheduled_flag,
wsor.assigned_units,
wsor.maximum_assigned_units, -- ST : Detailed Scheduling --
wsor.autocharge_type,
wsor.standard_rate_flag,
0, --applied_resource_units
0, --applied_resource_value
wsor.start_date,
wsor.completion_date,
wsor.attribute_category,
wsor.attribute1,
wsor.attribute2,
wsor.attribute3,
wsor.attribute4,
wsor.attribute5,
wsor.attribute6,
wsor.attribute7,
wsor.attribute8,
wsor.attribute9,
wsor.attribute10,
wsor.attribute11,
wsor.attribute12,
wsor.attribute13,
wsor.attribute14,
wsor.attribute15,
wsor.relieved_res_completion_units,
wsor.relieved_res_scrap_units,
wsor.relieved_res_completion_value,
wsor.relieved_res_scrap_value,
wsor.relieved_variance_value,
wsor.temp_relieved_value,
wsor.relieved_res_final_comp_units,
wsor.department_id,
wsor.phantom_flag,
wsor.phantom_op_seq_num,
wsor.phantom_item_id,
wsor.schedule_seq_num,
wsor.substitute_group_num,
wsor.replacement_group_num,
wsor.principle_flag,
wsor.setup_id
from wip_sub_operation_resources wsor
WHERE wsor.wip_entity_id = p_rep_we_id
AND wsor.operation_seq_num <= p_curr_op_seq_num);
INSERT INTO WIP_OP_RESOURCE_INSTANCES
( WIP_ENTITY_ID ,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM ,
ORGANIZATION_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_LOGIN,
INSTANCE_ID ,
SERIAL_NUMBER,
START_DATE ,
COMPLETION_DATE,
BATCH_ID
)
(
select
p_new_rj_we_id_tbl(l_job_counter),
wori.OPERATION_SEQ_NUM,
wori.RESOURCE_SEQ_NUM ,
wori.ORGANIZATION_ID ,
wori.LAST_UPDATE_DATE ,
wori.LAST_UPDATED_BY ,
wori.CREATION_DATE,
wori.CREATED_BY ,
wori.LAST_UPDATE_LOGIN,
wori.INSTANCE_ID ,
wori.SERIAL_NUMBER,
wori.START_DATE ,
wori.COMPLETION_DATE,
wori.BATCH_ID
FROM
wip_op_resource_instances wori
WHERE
wori.wip_entity_id = p_rep_we_id
AND wori.operation_seq_num <= p_curr_op_seq_num
);
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,
basis_type, --LBM enh
date_required,
required_quantity,
quantity_issued,
quantity_per_assembly,
component_yield_factor,--R12:Comp Shrinkage project
supply_subinventory,
supply_locator_id,
mrp_net_flag,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
department_id
)
(
select
wro.inventory_item_id,
wro.organization_id,
p_new_rj_we_id_tbl(l_job_counter),
wro.operation_seq_num,
wro.repetitive_schedule_id,
wro.last_update_date,
wro.last_updated_by,
wro.creation_date,
wro.created_by,
wro.last_update_login,
wro.component_sequence_id,
wro.wip_supply_type,
wro.basis_type, --LBM enh
wro.date_required,
-- ST : Added the below for bug fix : 4619823
(case
when (wro.operation_seq_num = p_curr_op_seq_num)
then decode(p_txn_intraop_step,
WIP_CONSTANTS.QUEUE,
ROUND( ( (wro.quantity_per_assembly/nvl(wro.component_yield_factor,1)) --LBM enh
*
decode(p_new_rj_qty_tbl(l_job_counter),
0, 0,
decode(wro.basis_type,
2, 1,
p_new_rj_qty_tbl(l_job_counter)
)
)
)
, 6
), --LBM enh
WIP_CONSTANTS.TOMOVE,0)
else
0
end
),
-- ST : Added the below for bug fix : 4619823
-- ST : Commented out the below for bug fix : 4619823
-- (case when (wro.operation_seq_num>=0 and wo.count_point_type = 3 and wo.scheduled_quantity = 0)
-- then
-- 0
-- else
-- ROUND((wro.quantity_per_assembly/nvl(wro.component_yield_factor,1)) --LBM enh
-- * decode(p_new_rj_qty_tbl(l_job_counter), 0, 0, decode(wro.basis_type, 2, 1, p_new_rj_qty_tbl(l_job_counter))), 6) --LBM enh
-- end),
-- ST : Commented out the below for bug fix : 4619823
0, --quantity_issued,
wro.quantity_per_assembly,
nvl(wro.component_yield_factor,1),--R12:Comp Shrinkage project
wro.supply_subinventory,
wro.supply_locator_id,
wro.mrp_net_flag,
wro.comments,
wro.attribute_category,
wro.attribute1,
wro.attribute2,
wro.attribute3,
wro.attribute4,
wro.attribute5,
wro.attribute6,
wro.attribute7,
wro.attribute8,
wro.attribute9,
wro.attribute10,
wro.attribute11,
wro.attribute12,
wro.attribute13,
wro.attribute14,
wro.attribute15,
wro.segment1,
wro.segment2,
wro.segment3,
wro.segment4,
wro.segment5,
wro.segment6,
wro.segment7,
wro.segment8,
wro.segment9,
wro.segment10,
wro.segment11,
wro.segment12,
wro.segment13,
wro.segment14,
wro.segment15,
wro.segment16,
wro.segment17,
wro.segment18,
wro.segment19,
wro.segment20,
wro.department_id
from wip_requirement_operations wro
, wip_operations wo
WHERE wro.wip_entity_id = p_rep_we_id
AND wro.operation_seq_num <= p_curr_op_seq_num
AND wro.operation_seq_num >= 0-p_curr_op_seq_num --To take care of phantoms bug #2681370
and wo.wip_entity_id = p_rep_we_id
and wo.operation_seq_num = wro.operation_seq_num
);
INSERT INTO WIP_OPERATION_YIELDS
(wip_entity_id,
operation_seq_num,
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,
scrap_account,
est_scrap_absorb_account,
status
)
(
select
p_new_rj_we_id_tbl(l_job_counter),
woy.operation_seq_num,
woy.organization_id,
woy.last_update_date,
woy.last_updated_by,
woy.creation_date,
woy.created_by,
woy.last_update_login,
woy.request_id,
woy.program_application_id,
woy.program_id,
woy.program_update_date,
woy.scrap_account,
woy.est_scrap_absorb_account,
NULL
from wip_operation_yields woy
WHERE wip_entity_id = p_rep_we_id
AND operation_seq_num <= p_curr_op_seq_num
);
This procedure will be called for split/merge to update the starting jobs and also for Update quantity transaction...
Parameters : p_txn_id : Transaction id
p_txn_type : transaction type
p_wip_entity_id_tbl : Table of Wip entity ids of the starting jobs...
p_new_job_qty_tbl : New job qty tbl... will be 0 for the completely merged/split jobs
p_new_net_qty_tbl : Net quantity ... will be 0 for the completely merged/split jobs
p_txn_job_op_seq_tbl : table of operation seq num of the job
p_txn_job_intraop : intraop step....
p_sj_st_qty_tbl : table of Old start qty of the starting jobs...
p_sj_avail_qty_tbl : table of Old start_quantity - quantity_scrapped of the starting jobs
p_sj_scrap_qty_tbl : table of quantity_scrapped
*/
PROCEDURE CHANGE_QUANTITY(p_txn_id IN NUMBER,
p_txn_type IN NUMBER,
p_wip_entity_id_tbl IN t_number,
p_new_job_qty_tbl IN t_number,
p_new_net_qty_tbl IN t_number, -- User given/defaulted Bug# 3181486 - Net Planned Qty
p_txn_job_op_seq_tbl IN t_number,
p_txn_job_intraop IN NUMBER, -- will be number as only one intraop is possible..
p_sj_st_qty_tbl IN t_number,
p_sj_avail_qty_tbl IN t_number,
p_sj_scrap_qty_tbl IN t_number,
x_err_code OUT NOCOPY NUMBER,
x_err_buf OUT NOCOPY VARCHAR2
)
IS
-- Logging variables.....
l_msg_tokens WSM_Log_PVT.token_rec_tbl;
cursor c_job_wo(p_wip_entity_id number,p_op_seq_num NUMBER) is select quantity_in_queue + quantity_running + quantity_completed total_quantity,
operation_seq_num
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_op_seq_num;
UPDATE wip_discrete_jobs
SET start_quantity = (p_sj_st_qty_tbl(l_job_counter) - p_sj_avail_qty_tbl(l_job_counter) + p_new_job_qty_tbl(l_job_counter)),
net_quantity = nvl(p_new_net_qty_tbl(l_job_counter), 0)
WHERE wip_entity_id = p_wip_entity_id_tbl(l_job_counter);
UPDATE wip_operations
SET quantity_in_queue = decode(quantity_in_queue, 0, 0, p_new_job_qty_tbl(l_job_counter)),
quantity_waiting_to_move = decode(quantity_waiting_to_move, 0, 0, p_new_job_qty_tbl(l_job_counter))
WHERE wip_entity_id = p_wip_entity_id_tbl(l_job_counter)
AND operation_seq_num = p_txn_job_op_seq_tbl(l_job_counter);
UPDATE wip_operations wo
-- ST : Added for bug fix 4619823 (Found in UT)
-- SET scheduled_quantity = (scheduled_quantity - p_sj_avail_qty_tbl(l_job_counter) + p_new_job_qty_tbl(l_job_counter))
SET scheduled_quantity = (nvl(quantity_scrapped,0) + quantity_in_queue)
WHERE wip_entity_id = p_wip_entity_id_tbl(l_job_counter)
-- ST : Added for bug fix 4619823...
-- Should update the current operation only...
AND operation_seq_num = p_txn_job_op_seq_tbl(l_job_counter);
UPDATE wip_operations wo
SET wsm_costed_quantity_completed = decode(wo.count_point_type,3,0, --changed to costed qty completed as part of MES actual qty changes(AH)
wsm_costed_quantity_completed - p_sj_avail_qty_tbl(l_job_counter) + p_new_job_qty_tbl(l_job_counter))
WHERE wip_entity_id = p_wip_entity_id_tbl(l_job_counter)
AND ( (p_txn_job_intraop = WIP_CONSTANTS.QUEUE
AND operation_seq_num < p_txn_job_op_seq_tbl(l_job_counter))
OR
(p_txn_job_intraop = WIP_CONSTANTS.TOMOVE
AND operation_seq_num <= p_txn_job_op_seq_tbl(l_job_counter))
);
UPDATE wip_requirement_operations wro
SET required_quantity = ROUND( (wro.quantity_per_assembly/nvl(wro.component_yield_factor,1)
* decode(l_job_wo_rec.total_quantity
, 0,0
, decode(wro.basis_type,
2,1,
(l_job_wo_rec.total_quantity)
-- ST : Added for bug fix 4619823 (Found in UT)
-- nvl(wro.quantity_relieved,0)) -- Or is it Quantity_completed.
)
)
),6)
WHERE wro.wip_entity_id = p_wip_entity_id_tbl(l_job_counter)
and nvl(abs(wro.quantity_issued),0) >= nvl(abs(wro.quantity_relieved),0) -- Added abs() on quantity_issued for bug 6053122(fp for 5843039)
-- ST : Commenting out for bug fix 4619823
-- Actual Quantity Changes : Should update the current operation only
-- and ( (wro.operation_seq_num = l_job_wo_rec.operation_seq_num)
-- or
-- (wro.operation_seq_num = 0-l_job_wo_rec.operation_seq_num)
-- )
-- ST : Added for bug fix 4619823...
-- Should update the current operation only...
AND operation_seq_num = p_txn_job_op_seq_tbl(l_job_counter);
INV_Kanban_PVT.Update_Card_Supply_Status (X_Return_Status => l_ret_status,
p_Kanban_Card_Id => p_kanban_card_id,
p_Supply_Status => INV_Kanban_PVT.G_Supply_Status_Exception
);
| Name : UPDATE_QTY_ISSUED
---------------------------------------------------------------*/
/*
-- This procedure is called only for Split/Merge/Update Quantity transactions
Parameters :
p_txn_id : Transaction id
p_txn_type : Transaction type...
p_rep_we_id : Wip entity id of the representative starting job...
p_rep_op_seq_num : Job Operation seq num of the rep. starting job...
p_rep_avail_qty : Available qty of the rep. starting job...
p_new_rep_job_qty : New qty of the rep. starting job ( will be non-zero if the starting job is also a resulting job....)
p_non_rep_sj_we_id_tbl : Table containing the wip_entity_id of the non-representative starting jobs..
p_new_rj_we_id_tbl : Table containing the wip_entity_id of the new resulting jobs..
*/
PROCEDURE UPDATE_QTY_ISSUED(p_txn_id IN NUMBER,
p_txn_type IN NUMBER,
p_rep_we_id IN NUMBER,
p_rep_op_seq_num IN NUMBER,
p_rep_avail_qty IN NUMBER,
p_rep_new_job_qty IN NUMBER,
p_txn_job_intraop IN NUMBER,
p_non_rep_sj_we_id_tbl IN t_number,
p_new_rj_we_id_tbl IN t_number,
p_new_rj_start_qty IN t_number,
x_err_code OUT NOCOPY NUMBER,
x_err_buf OUT NOCOPY VARCHAR2
)
IS
-- Logging variables.....
l_msg_tokens WSM_Log_PVT.token_rec_tbl;
l_module VARCHAR2(100) := 'wsm.plsql.WSMJUPDB.UPDATE_QTY_ISSUED';
UPDATE wip_requirement_operations wro
-- ST : Commenting out the below for bug fix : 4619823 --
--SET wro.quantity_issued = decode(p_txn_job_intraop,1,round(NVL(wro.quantity_relieved, 0), 6)
-- ,3,wro.quantity_issued
-- )
-- ST : Added the below for bug fix : 4619823 --
SET wro.quantity_issued = round(NVL(wro.quantity_relieved, 0), 6)
WHERE wro.wip_entity_id = p_non_rep_sj_we_id_tbl(l_job_counter)
AND nvl(abs(wro.quantity_issued), 0) >= nvl(abs(wro.quantity_relieved), 0)
-- ST : Added the below for bug fix : 4619823 --
-- Should be updating only the op at which the TXN took place...
-- That Op will be the MAX Non-Obsoleted Operation
AND wro.operation_seq_num = (select max(operation_seq_num)
from wip_operations wo
where wo.wip_entity_id = p_non_rep_sj_we_id_tbl(l_job_counter)
and wo.count_point_type <> 3);
UPDATE wip_requirement_operations wro
-- ST : Commenting for bug fix 4619823
-- SET wro.quantity_issued = decode(p_txn_job_intraop,1,(SELECT round(decode(sign(nvl(wro1.quantity_issued, 0) - nvl(wro1.quantity_relieved, 0)), 1, 1, 0)
-- *(nvl(wro1.quantity_issued,0) - nvl(wro1.quantity_relieved, 0))
-- * p_new_rj_start_qty(l_job_counter)/p_rep_avail_qty, 6)
-- FROM wip_requirement_operations wro1
-- WHERE wro1.wip_entity_id = p_rep_we_id
-- AND wro1.inventory_item_id = wro.inventory_item_id
-- AND wro1.organization_id = wro.organization_id
-- AND wro1.operation_seq_num = wro.operation_seq_num
-- AND p_new_rj_we_id_tbl(l_job_counter) = wro.wip_entity_id),
-- 3,0)
-- ST : Commenting for bug fix 4619823 --
-- ST : Added the below for bug fix 4619823 --
SET wro.quantity_issued = (SELECT round(decode(sign(nvl(abs(wro1.quantity_issued), 0) - nvl(abs(wro1.quantity_relieved), 0)), 1, 1, 0)
*(nvl(wro1.quantity_issued,0) - nvl(wro1.quantity_relieved, 0))
* p_new_rj_start_qty(l_job_counter)/p_rep_avail_qty, 6)
FROM wip_requirement_operations wro1
WHERE wro1.wip_entity_id = p_rep_we_id
AND wro1.inventory_item_id = wro.inventory_item_id
AND wro1.organization_id = wro.organization_id
AND wro1.operation_seq_num = wro.operation_seq_num
AND p_new_rj_we_id_tbl(l_job_counter) = wro.wip_entity_id)
WHERE wro.wip_entity_id = p_new_rj_we_id_tbl(l_job_counter)
and wro.operation_seq_num = p_rep_op_seq_num
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = wro.wip_entity_id
and wo.organization_id = wro.organization_id
and wo.operation_seq_num = wro.operation_seq_num
and wo.count_point_type = 3);
UPDATE wip_requirement_operations wro
-- ST : Commenting for bug fix 4619823
-- SET wro.quantity_issued = decode(p_txn_job_intraop,1,round( (decode(sign(nvl(wro.quantity_issued, 0) - nvl(wro.quantity_relieved, 0)),
-- 1, 1
-- , 0)
-- *(nvl(wro.quantity_issued,0) - nvl(wro.quantity_relieved, 0))
-- * p_rep_new_job_qty/p_rep_avail_qty
-- + nvl(wro.quantity_relieved, 0)
-- ), 6)
-- -- What for 3..?
-- ,3,wro.quantity_issued
-- )
-- ST : Added the below for bug fix 4619823
SET wro.quantity_issued = round((decode(sign(nvl(abs(wro.quantity_issued), 0) - nvl(abs(wro.quantity_relieved), 0)),
1, 1
, 0)
*(nvl(wro.quantity_issued,0) - nvl(wro.quantity_relieved, 0))
* p_rep_new_job_qty/p_rep_avail_qty
+ nvl(wro.quantity_relieved, 0)
), 6)
WHERE wro.wip_entity_id = p_rep_we_id
AND nvl(abs(wro.quantity_issued),0) >= NVL(abs(wro.quantity_relieved), 0) -- Added to fix bug #2797647
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = wro.wip_entity_id
and wo.organization_id = wro.organization_id
and wo.operation_seq_num = wro.operation_seq_num
and wo.count_point_type = 3)
-- AND operation_seq_num <= p_rep_op_seq_num;
x_err_buf := 'WSMPJUPD.UPDATE_QTY_ISSUED('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
END UPDATE_QTY_ISSUED;
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_request_id IN NUMBER,
p_program_app_id IN NUMBER,
p_program_id IN NUMBER,
p_program_update_date IN DATE,
x_err_code OUT NOCOPY NUMBER,
x_err_buf OUT NOCOPY VARCHAR2)
IS
-- Logging variables.....
l_msg_tokens WSM_Log_PVT.token_rec_tbl;
INSERT INTO WIP_SUB_OPERATION_RESOURCES
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
SUBSTITUTE_GROUP_NUM,
REPLACEMENT_GROUP_NUM,
START_DATE,
COMPLETION_DATE,
RESOURCE_ID,
ACTIVITY_ID,
STANDARD_RATE_FLAG,
ASSIGNED_UNITS,
MAXIMUM_ASSIGNED_UNITS, -- ST : Detailed Scheduling --
USAGE_RATE_OR_AMOUNT,
UOM_CODE,
BASIS_TYPE,
SCHEDULED_FLAG,
AUTOCHARGE_TYPE,
SCHEDULE_SEQ_NUM,
PRINCIPLE_FLAG,
SETUP_ID,
DEPARTMENT_ID,
PHANTOM_FLAG,
PHANTOM_OP_SEQ_NUM,
PHANTOM_ITEM_ID,
applied_resource_units,
applied_resource_value,
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 wo.WIP_ENTITY_ID,
wo.OPERATION_SEQ_NUM,
rownum + (SELECT nvl(max(resource_seq_num), 10)
FROM WIP_OPERATION_RESOURCES
WHERE wip_entity_id = p_wip_entity_id
AND OPERATION_SEQ_NUM = wo.OPERATION_SEQ_NUM),
wo.ORGANIZATION_ID,
bsor.SUBSTITUTE_GROUP_NUM,
bsor.REPLACEMENT_GROUP_NUM,
wo.first_unit_start_date, --START_DATE
wo.first_unit_completion_date, --COMPLETION_DATE
bsor.RESOURCE_ID,
bsor.ACTIVITY_ID,
bsor.STANDARD_RATE_FLAG,
bsor.ASSIGNED_UNITS,
bsor.assigned_units, -- ST : Detailed Scheduling --
bsor.USAGE_RATE_OR_AMOUNT,
br.UNIT_OF_MEASURE,
bsor.BASIS_TYPE,
bsor.SCHEDULE_FLAG,
bsor.AUTOCHARGE_TYPE,
bsor.SCHEDULE_SEQ_NUM,
bsor.PRINCIPLE_FLAG,
bsor.SETUP_ID,
NULL, --DEPARTMENT_ID
NULL, --PHANTOM_FLAG
NULL, --PHANTOM_OP_SEQ_NUM
NULL, --PHANTOM_ITEM_ID
0, --applied_resource_units
0, --applied_resource_value
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_request_id,
p_program_app_id,
p_program_id,
p_program_update_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
FROM BOM_RESOURCES br,
BOM_OPERATION_RESOURCES bor,
BOM_SUB_OPERATION_RESOURCES bsor,
WIP_OPERATIONS wo
WHERE wo.WIP_ENTITY_ID = p_wip_entity_id
AND wo.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
AND nvl(p_only_wo_op_seq, WO.operation_seq_num) = WO.operation_seq_num
AND bor.OPERATION_SEQUENCE_ID = bsor.OPERATION_SEQUENCE_ID
AND bor.SUBSTITUTE_GROUP_NUM = bsor.SUBSTITUTE_GROUP_NUM
AND bsor.RESOURCE_ID = br.RESOURCE_ID
AND br.ORGANIZATION_ID = wo.ORGANIZATION_ID;
SELECT nvl(op_seq_num_increment, 10)
INTO l_wsm_param_seq_incr
FROM wsm_parameters
WHERE organization_id = p_org_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE WIP_OPERATIONS
SET COUNT_POINT_TYPE = 3,
SCHEDULED_QUANTITY = 0,
QUANTITY_IN_QUEUE = 0,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_user_login_id,
REQUEST_ID = g_request_id,
PROGRAM_APPLICATION_ID = g_program_appl_id,
PROGRAM_ID = g_program_id,
PROGRAM_UPDATE_DATE = SYSDATE,
DISABLE_DATE = SYSDATE -- bug 2931071
WHERE WIP_ENTITY_ID = p_wip_entity_id
AND ORGANIZATION_ID = p_org_id
AND OPERATION_SEQ_NUM >= l_job_op_seq_num;
UPDATE WIP_REQUIREMENT_OPERATIONS
SET required_quantity = 0
WHERE WIP_ENTITY_ID = p_wip_entity_id
AND ORGANIZATION_ID = p_org_id
AND (OPERATION_SEQ_NUM >= l_job_op_seq_num
OR
OPERATION_SEQ_NUM <= 0-l_job_op_seq_num
);
UPDATE WIP_OPERATION_RESOURCES
SET autocharge_type = 2
WHERE WIP_ENTITY_ID = p_wip_entity_id
AND ORGANIZATION_ID = p_org_id
AND OPERATION_SEQ_NUM >= l_job_op_seq_num;
UPDATE WIP_OPERATIONS
SET QUANTITY_WAITING_TO_MOVE = 0,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_LOGIN = g_user_login_id,
REQUEST_ID = g_request_id,
PROGRAM_APPLICATION_ID = g_program_appl_id,
PROGRAM_ID = g_program_id,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE WIP_ENTITY_ID = p_wip_entity_id
AND ORGANIZATION_ID = p_org_id
AND OPERATION_SEQ_NUM = l_job_op_seq_num;
SELECT max(operation_seq_num)
INTO l_job_max_op_seq_num
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id;
l_msg_tokens.delete;
p_msg_text => 'Calling WSMPLBJI.insert_procedure :' ||p_rj_job_rec.starting_operation_seq_id,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
WSMPLBJI.insert_procedure
(p_seq_id => p_rj_job_rec.starting_operation_seq_id,
p_job_seq_num => p_new_op_added,
p_common_routing_sequence_id => p_rj_job_rec.common_routing_sequence_id,
p_supply_type => p_rj_job_rec.wip_supply_type,
p_wip_entity_id => p_wip_entity_id,
p_organization_id => p_org_id,
p_quantity => p_rj_job_rec.start_quantity,
p_job_type => p_rj_job_rec.job_type,
p_bom_reference_id => p_rj_job_rec.bom_reference_id,
p_rtg_reference_id => p_rj_job_rec.routing_reference_id,
p_assembly_item_id => p_rj_job_rec.primary_item_id,
p_alt_bom_designator => p_rj_job_rec.alternate_bom_designator,
p_alt_rtg_designator => p_rj_job_rec.alternate_routing_designator,
--Bug 3318382 p_fusd => l_sch_st_dt,
--Bug 3318382 p_lucd => l_sch_comp_dt,
--Bug 3318382
p_fusd => l_job_op_start_dt,
--Bug 3318382
p_lucd => l_job_op_comp_dt,
p_rtg_revision_date => p_rj_job_rec.routing_revision_date,
p_bom_revision_date => p_rj_job_rec.bom_revision_date,
p_last_updt_date => sysdate,
p_last_updt_by => g_user_id,
p_creation_date => sysdate,
p_created_by => g_user_id,
p_last_updt_login => g_user_login_id,
p_request_id => g_request_id,
p_program_application_id => g_program_appl_id,
p_program_id => g_program_id,
p_prog_updt_date => sysdate,
p_error_code => x_err_code,
p_error_msg => x_err_buf
);
l_msg_tokens.delete;
p_msg_text => 'Returned failure from WSMPLBJI.insert_procedure.Error:'|| x_err_buf,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
UPDATE wip_operations
SET wsm_op_seq_num = l_op_seq_num
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_new_op_added;
p_last_update_date => sysdate,
p_last_updated_by => g_user_id,
p_last_update_login => g_user_login_id,
p_creation_date => sysdate,
p_created_by => g_user_id,
p_request_id => g_request_id,
p_program_app_id => g_program_appl_id,
p_program_id => g_program_id,
p_program_update_date => sysdate,
x_err_code => x_err_code,
x_err_buf => x_err_buf
);
l_msg_tokens.delete;
l_msg_tokens.delete;
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)
WHERE WO.WIP_ENTITY_ID = p_wip_entity_id;
UPDATE WIP_OPERATIONS
SET quantity_in_queue = l_job_qty
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_new_op_added;
| Name : UPDATE_ASSEMBLY_OR_ROUTING
-------------------------------------------------------------------*/
--SpUA begin: Moved Update Assembly/Routing code from Process_Wip_Lot_Txns here.
-- To be used by Split and Update Assy, Update Assy, Update Routing txns.
PROCEDURE UPDATE_ASSEMBLY_OR_ROUTING(p_txn_id IN NUMBER,
p_txn_type_id IN NUMBER,
p_job_kanban_card_id IN NUMBER, --abbKanban
p_po_creation_time IN NUMBER, --osp
p_request_id IN NUMBER, --osp
p_sj_compl_subinventory IN VARCHAR2,
p_sj_compl_locator_id IN NUMBER,
p_rj_job_rec IN OUT NOCOPY WSM_WIP_LOT_TXN_PVT.WLTX_RESULTING_JOBS_REC_TYPE,
x_err_code OUT NOCOPY NUMBER,
x_err_buf OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER)
IS
l_txn_type_id NUMBER;
l_module VARCHAR2(100) := 'wsm.plsql.WSMJUPDB.UPDATE_ASSEMBLY_OR_ROUTING';
IF l_txn_type_id = WSMPCNST.UPDATE_ROUTING THEN
l_stmt_num := 190;
l_msg_tokens.delete;
UPDATE wip_discrete_jobs wdj
SET routing_reference_id = p_rj_job_rec.routing_reference_id,
alternate_routing_designator = p_rj_job_rec.alternate_routing_designator,
common_routing_sequence_id = p_rj_job_rec.common_routing_sequence_id,
routing_revision = p_rj_job_rec.routing_revision,
routing_revision_date = p_rj_job_rec.routing_revision_date,
completion_subinventory = p_rj_job_rec.completion_subinventory,
completion_locator_id = p_rj_job_rec.completion_locator_id,
kanban_card_id = l_job_kanban_card_id,
-- ST : Fix for bug 5254137 : Update the BOM Info as well for Update Rtg as Bom data can be changed during upd rtg..
--Bug 5491020:bom_reference_id is updated by p_rj_job_rec.bom_reference_id
bom_reference_id = p_rj_job_rec.bom_reference_id, --routing_reference_id,
alternate_bom_designator = p_rj_job_rec.alternate_bom_designator,
common_bom_sequence_id = p_rj_job_rec.common_bom_sequence_id,
bom_revision = p_rj_job_rec.bom_revision,
bom_revision_date = p_rj_job_rec.bom_revision_date,
-- ST : Fix for bug 5254137 end --
last_update_date = sysdate,
last_updated_by = g_user_id
WHERE wdj.wip_entity_id = l_wip_entity_id;
ELSIF l_txn_type_id IN (WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.SPLIT) THEN
l_stmt_num := 200;
INV_Kanban_PVT.Update_Card_Supply_Status(X_Return_Status => l_return_status,
p_Kanban_Card_Id => l_job_kanban_card_id,
p_Supply_Status => INV_Kanban_PVT.G_Supply_Status_Exception
);
l_msg_tokens.delete;
UPDATE wip_discrete_jobs wdj
SET primary_item_id = p_rj_job_rec.primary_item_id,
kanban_card_id = l_job_kanban_card_id,
routing_reference_id = p_rj_job_rec.routing_reference_id,
alternate_routing_designator = p_rj_job_rec.alternate_routing_designator,
common_routing_sequence_id = p_rj_job_rec.common_routing_sequence_id,
routing_revision = p_rj_job_rec.routing_revision,
routing_revision_date = p_rj_job_rec.routing_revision_date,
completion_subinventory = p_rj_job_rec.completion_subinventory,
completion_locator_id = p_rj_job_rec.completion_locator_id,
--Bug 5491020:bom_reference_id is updated by p_rj_job_rec.bom_reference_id
bom_reference_id = p_rj_job_rec.bom_reference_id, --routing_reference_id,
alternate_bom_designator = p_rj_job_rec.alternate_bom_designator,
common_bom_sequence_id = p_rj_job_rec.common_bom_sequence_id,
bom_revision = p_rj_job_rec.bom_revision,
bom_revision_date = p_rj_job_rec.bom_revision_date,
last_update_date = sysdate,
last_updated_by = g_user_id
WHERE wdj.wip_entity_id = l_wip_entity_id;
UPDATE wip_entities
SET primary_item_id = p_rj_job_rec.primary_item_id
WHERE wip_entity_id = l_wip_entity_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT operation_seq_num
into l_rtg_op_seq_num
from bom_operation_sequences
where operation_sequence_id = p_rj_job_rec.starting_operation_seq_id;
update wsm_lot_based_jobs
set current_rtg_op_seq_num = l_rtg_op_seq_num,
current_job_op_seq_num = l_new_op_added
where wip_entity_id = l_wip_entity_id;
x_err_buf := ' WSMPJUPD.UPDATE_ASSEMBLY_OR_ROUTING('||l_stmt_num||'): '||x_err_buf;
x_err_buf := ' WSMPJUPD.UPDATE_ASSEMBLY_OR_ROUTING('||l_stmt_num||'): '||x_err_buf;
x_err_buf := ' WSMPJUPD.UPDATE_ASSEMBLY_OR_ROUTING('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
END UPDATE_ASSEMBLY_OR_ROUTING;
Procedure Insert_MMT_record ( p_txn_id IN NUMBER,
p_txn_org_id IN NUMBER,
p_txn_date IN DATE,
p_txn_type_id IN NUMBER,
p_sj_wip_entity_id IN NUMBER,
p_sj_wip_entity_name IN VARCHAR2,
p_sj_avail_quantity IN NUMBER,
p_rj_wip_entity_id IN NUMBER,
p_rj_wip_entity_name IN VARCHAR2,
p_rj_start_quantity IN NUMBER,
p_sj_item_id IN number,
p_sj_op_seq_num IN number,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_wms_org mtl_parameters.wms_enabled_flag%type;
l_module VARCHAR2(100) := 'wsm.plsql.WSMJUPDB.Insert_MMT_record';
SELECT wms_enabled_flag,
default_cost_group_id
INTO l_wms_org,
l_def_cost_grp_id
FROM mtl_parameters
WHERE organization_id = p_txn_org_id;
l_msg_tokens.delete;
INSERT INTO mtl_material_transactions
(TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ORGANIZATION_ID,
TRANSACTION_TYPE_ID,
INVENTORY_ITEM_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
SOURCE_LINE_ID,
OPERATION_SEQ_NUM,
ACCT_PERIOD_ID,
COSTED_FLAG,
COST_GROUP_ID --VJ: Added to fix bug #2828376
)
SELECT mtl_material_transactions_s.nextval,
sysdate,
g_USER_ID,
g_user_LOGIN_ID,
sysdate,
g_USER_ID,
g_REQUEST_ID,
g_program_appl_id,
g_PROGRAM_ID,
sysdate,
p_txn_org_id,
MTT.transaction_type_id,
-- Start : Changes as required by CST for SpUA --
p_sj_item_id,
-- End : Changes as required by CST for SpUA --
decode(p_txn_type_id, WSMPCNST.SPLIT, 40,
WSMPCNST.MERGE, 41,
WSMPCNST.BONUS, 42,
WSMPCNST.UPDATE_QUANTITY, 43,
0),
MTT.transaction_source_type_id,
decode(p_txn_type_id, WSMPCNST.SPLIT, p_sj_wip_entity_id, p_rj_wip_entity_id),
decode(p_txn_type_id, WSMPCNST.SPLIT, p_sj_wip_entity_name, p_rj_wip_entity_name),
decode(p_txn_type_id, WSMPCNST.SPLIT, p_sj_avail_quantity, p_rj_start_quantity),
decode(p_txn_type_id, WSMPCNST.SPLIT, p_sj_avail_quantity, p_rj_start_quantity),
MSI.primary_uom_code,
p_txn_date,
p_txn_id,
p_sj_op_seq_num,
OAP.acct_period_id,
'N',
decode(l_wms_org, 'Y', l_def_cost_grp_id, NULL) --VJ: Added to fix bug #2828376
FROM mtl_system_items MSI,
org_acct_periods OAP,
mtl_transaction_types MTT
-- Start : Changes as required by CST for SpUA --
WHERE p_sj_item_id = MSI.inventory_item_id
AND p_txn_org_id = MSI.organization_id
-- End : Changes as required by CST for SpUA --
AND p_txn_org_id = OAP.organization_id
AND trunc(p_txn_date) between period_start_date and schedule_close_date
-- Fixed bug #2828278
-- Added trunc above: sch_close_date doesnt have timestamp so a problem for end of month
AND MTT.transaction_action_id IN(decode(p_txn_type_id, WSMPCNST.SPLIT, 40,
WSMPCNST.MERGE, 41,
WSMPCNST.BONUS, 42,
WSMPCNST.UPDATE_QUANTITY, 43,
0)
)
AND MTT.transaction_source_type_id = 5;
l_msg_tokens.delete;
l_msg_tokens.delete;
WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING)) THEN
l_stmt_num := 11;
select 1 into l_phantom_exists
from bom_inventory_components
where bill_sequence_id = p_wltx_resulting_jobs_tbl(l_rj_counter).common_bom_sequence_id
and p_wltx_resulting_jobs_tbl(l_rj_counter).bom_revision_date between effectivity_date and
nvl(disable_date,p_wltx_resulting_jobs_tbl(l_rj_counter).bom_revision_date+1)
and wip_supply_type = 6
and rownum = 1;
p_last_update_date => sysdate,
p_last_updated_by => g_USER_ID,
p_last_update_login => g_user_LOGIN_ID,
p_creation_date => sysdate,
p_created_by => g_USER_ID,
p_request_id => g_REQUEST_ID,
p_program_app_id => g_program_appl_id,
p_program_id => g_PROGRAM_ID,
p_program_update_date => sysdate,
p_inf_sch_flag => 'Y',
p_inf_sch_mode => NULL, -- Create_JobCopies to figure out
p_inf_sch_date => NULL, --Bug #3348704 l_inf_sch_date
--OPTII-PERF:Following parameters are added
p_charges_exist => 1,
p_phantom_exists => l_phantom_exists
);
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE wsm_lot_based_jobs
SET internal_copy_type = 2
WHERE wip_entity_id = p_wltx_resulting_jobs_tbl(l_rj_counter).wip_entity_id;
l_msg_tokens.delete;
SELECT nvl(internal_copy_type, 0),
copy_parent_wip_entity_id
INTO l_new_job_int_copy_type,
l_rep_job_par_we_id
FROM wsm_lot_based_jobs
WHERE wip_entity_id = l_rep_wip_entity_id;
IF (nvl(p_wltx_resulting_jobs_tbl(l_job_counter).split_has_update_assy,0) = 0) THEN -- No assembly change
l_stmt_num := 59;
l_msg_tokens.delete;
p_last_update_date => sysdate,
p_last_updated_by => g_USER_ID,
p_last_update_login => g_user_LOGIN_ID,
p_creation_date => sysdate,
p_created_by => g_USER_ID,
p_request_id => g_REQUEST_ID,
p_program_app_id => g_program_appl_id,
p_program_id => g_PROGRAM_ID,
p_program_update_date => sysdate,
p_inf_sch_flag => 'Y',
p_inf_sch_mode => NULL,
p_inf_sch_date => NULL
);
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE wsm_lot_based_jobs
SET internal_copy_type = l_new_job_int_copy_type
WHERE wip_entity_id = p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id;
l_msg_tokens.delete;
UPDATE wsm_lot_based_jobs
SET copy_parent_wip_entity_id = nvl(l_rep_job_par_we_id, l_rep_wip_entity_id)
WHERE wip_entity_id = p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id;
l_msg_tokens.delete;
ELSIF (p_wltx_resulting_jobs_tbl(l_job_counter).split_has_update_assy = 1) THEN -- Assembly has changed
l_stmt_num := 108;
select 1 into l_phantom_exists
from bom_inventory_components
where bill_sequence_id = p_wltx_resulting_jobs_tbl(l_job_counter).common_bom_sequence_id
and p_wltx_resulting_jobs_tbl(l_job_counter).bom_revision_date between effectivity_date and
nvl(disable_date,p_wltx_resulting_jobs_tbl(l_job_counter).bom_revision_date+1)
and wip_supply_type = 6
and rownum = 1;
p_last_update_date => sysdate,
p_last_updated_by => g_USER_ID,
p_last_update_login => g_user_LOGIN_ID,
p_creation_date => sysdate,
p_created_by => g_USER_ID,
p_request_id => g_REQUEST_ID,
p_program_app_id => g_program_appl_id,
p_program_id => g_PROGRAM_ID,
p_program_update_date => sysdate,
p_inf_sch_flag => 'Y',
p_inf_sch_mode => NULL, -- Create_JobCopies to figure out
p_inf_sch_date => NULL, --Bug #3348704 c_sm_new_jobs_rec.inf_sch_date
--OPTII-PERF:Following parameters are added
p_charges_exist => 1,
p_phantom_exists => l_phantom_exists
);
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE wsm_lot_based_jobs
SET internal_copy_type = 2
WHERE wip_entity_id = p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id;
l_msg_tokens.delete;
IF (p_wltx_resulting_jobs_tbl(p_sj_as_rj_index).split_has_update_assy = 1) THEN
l_stmt_num := 138;
select 1 into l_phantom_exists
from bom_inventory_components
where bill_sequence_id = p_wltx_resulting_jobs_tbl(p_sj_as_rj_index).common_bom_sequence_id
and p_wltx_resulting_jobs_tbl(p_sj_as_rj_index).bom_revision_date between effectivity_date and
nvl(disable_date,p_wltx_resulting_jobs_tbl(p_sj_as_rj_index).bom_revision_date+1)
and wip_supply_type = 6
and rownum = 1;
p_last_update_date => sysdate,
p_last_updated_by => g_USER_ID,
p_last_update_login => g_user_LOGIN_ID,
p_creation_date => sysdate,
p_created_by => g_USER_ID,
p_request_id => g_REQUEST_ID,
p_program_app_id => g_program_appl_id,
p_program_id => g_PROGRAM_ID,
p_program_update_date => sysdate,
p_inf_sch_flag => 'Y',
p_inf_sch_mode => NULL, -- Create_JobCopies to figure out
p_inf_sch_date => NULL, --Bug #3348704 l_inf_sch_date
--OPTII-PERF:Following parameters are added
p_charges_exist => 1,
p_phantom_exists => l_phantom_exists
);
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE wsm_lot_based_jobs
SET internal_copy_type = 2
WHERE wip_entity_id = l_rep_wip_entity_id;
l_msg_tokens.delete;
SELECT decode(on_rec_path, 'Y', WIP_CONSTANTS.FORWARDS, WIP_CONSTANTS.CURRENT_OP)
INTO l_schedule_mode
FROM WSM_LOT_BASED_JOBS
WHERE wip_entity_id = p_par_we_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE wsm_lot_based_jobs
SET infinite_schedule = 'Y'
WHERE wip_entity_id = p_par_we_id;
select meaning
into g_translated_meaning
from mfg_lookups
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
and lookup_code = 7
and upper(enabled_flag) = 'Y';
SELECT po_creation_time
INTO l_po_creation_time
FROM wip_parameters
WHERE organization_id = p_txn_org_id;
select wsm_split_merge_transactions_s.nextval
into p_wltx_header.transaction_id
from dual;
l_non_rep_sj_tbl.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'Calling INV_Kanban_PVT.Update_Card_Supply_Status',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
INV_Kanban_PVT.Update_Card_Supply_Status( X_Return_Status => l_ret_status,
p_Kanban_Card_Id => l_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_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id,
p_Document_detail_Id => '',
p_replenish_quantity => p_wltx_resulting_jobs_tbl(l_job_counter).start_quantity
);
l_msg_tokens.delete;
update wip_discrete_jobs
set completion_subinventory = p_wltx_resulting_jobs_tbl(l_job_counter).completion_subinventory,
completion_locator_id = p_wltx_resulting_jobs_tbl(l_job_counter).completion_locator_id,
kanban_card_id = p_wltx_resulting_jobs_tbl(l_job_counter).kanban_card_id
where wip_entity_id = p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
if p_wltx_header.transaction_type_id = WSMPCNST.SPLIT and p_wltx_resulting_jobs_tbl(l_sj_also_rj_index).split_has_update_assy = 1 then
l_stmt_num := 120;
l_msg_tokens.delete;
p_msg_text => ' Calling INV_Kanban_PVT.Update_Card_Supply_Status...',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
INV_Kanban_PVT.Update_Card_Supply_Status ( X_Return_Status => l_ret_status,
p_Kanban_Card_Id => p_wltx_starting_jobs_tbl(l_rep_sj_index).kanban_card_id,
p_Supply_Status => INV_Kanban_PVT.G_Supply_Status_Exception
);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
if l_sub_loc_change = 0 then -- reflect the updated quantity in the card
l_stmt_num := 147;
l_msg_tokens.delete;
p_msg_text => ' Calling INV_Kanban_PVT.Update_Card_Supply_Status...',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
INV_Kanban_PVT.Update_Card_Supply_Status( x_return_status => l_ret_Status,
p_Kanban_Card_Id => l_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_wltx_starting_jobs_tbl(l_job_counter).wip_entity_id,
p_Document_detail_Id => '',
p_replenish_quantity => p_wltx_resulting_jobs_tbl(l_sj_also_rj_index).start_quantity
);
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE wip_discrete_jobs
SET completion_subinventory = p_wltx_resulting_jobs_tbl(l_sj_also_rj_index).completion_subinventory,
completion_locator_id = p_wltx_resulting_jobs_tbl(l_sj_also_rj_index).completion_locator_id ,
kanban_card_id = p_wltx_resulting_jobs_tbl(l_sj_also_rj_index).kanban_card_id ,
-- ST : Fix for bug 5122500
coproducts_supply = p_wltx_resulting_jobs_tbl(l_sj_also_rj_index).coproducts_supply
WHERE wip_entity_id = p_wltx_starting_jobs_tbl(l_job_counter).wip_entity_id;
l_msg_tokens.delete;
INV_Kanban_PVT.Update_Card_Supply_Status ( x_return_status => l_ret_status,
p_Kanban_Card_Id => p_wltx_starting_jobs_tbl(l_job_counter).kanban_card_id,
p_Supply_Status => INV_Kanban_PVT.G_Supply_Status_Exception
);
l_msg_tokens.delete;
UPDATE wip_discrete_jobs
SET STATUS_TYPE = 4
,kanban_card_id = l_kanban_card_id
,date_completed = sysdate
,last_updated_by = g_user_id
,last_update_date = sysdate
,last_update_login = g_user_login_id
,program_application_id = g_program_appl_id
,program_id = g_PROGRAM_ID
,program_update_date = sysdate
,request_id = g_REQUEST_ID
WHERE wip_entity_id = p_wltx_starting_jobs_tbl(l_job_counter).wip_entity_id;
l_new_name := WSMPOPRN.update_job_name ( p_wip_entity_id => p_wltx_starting_jobs_tbl(l_job_counter).wip_entity_id,
p_subinventory => p_wltx_starting_jobs_tbl(l_job_counter).completion_subinventory,
p_org_id => p_wltx_starting_jobs_tbl(l_job_counter).organization_id,
p_txn_type => 2, -- COMPLETION
p_update_flag => TRUE,
p_dup_job_name => l_dup_job_name,
x_error_code => l_err_code,
x_error_msg => l_err_buf
);
l_msg_tokens.delete;
l_err_buf := 'Returned failure from WSMPOPRN.update_job_name';
l_msg_tokens.delete;
p_msg_text => 'WSMPOPRN.update_job_name success.Got new job name',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'Calling UPDATE_QTY_ISSUED',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
UPDATE_QTY_ISSUED(p_txn_id => null,
p_txn_type => p_wltx_header.transaction_type_id,
p_rep_we_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id,
p_rep_op_seq_num => p_wltx_starting_jobs_tbl(l_rep_sj_index).operation_seq_num,
p_rep_avail_qty => p_wltx_starting_jobs_tbl(l_rep_sj_index).quantity_available,
p_rep_new_job_qty => l_rep_new_qty,
p_txn_job_intraop => p_wltx_starting_jobs_tbl(l_rep_sj_index).intraoperation_step,
p_non_rep_sj_we_id_tbl => l_non_rep_sj_tbl,
p_new_rj_we_id_tbl => l_new_rj_we_id_tbl,
p_new_rj_start_qty => l_new_rj_qty_tbl,
x_err_code => l_err_code,
x_err_buf => l_err_buf
);
l_msg_tokens.delete;
p_msg_text => 'UPDATE_QTY_ISSUED returned failure' ,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
if p_wltx_resulting_jobs_tbl(l_job_counter).split_has_update_assy = 1 then
l_stmt_num := 290;
l_msg_tokens.delete;
p_msg_text => 'Calling UPDATE_ASSEMBLY_OR_ROUTING',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
UPDATE_ASSEMBLY_OR_ROUTING( p_txn_id => null,
p_txn_type_id => WSMPCNST.SPLIT,
-- p_rep_wip_entity_id => p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id,
p_job_kanban_card_id => null, -- no need already handled within the code.. .....
p_po_creation_time => l_po_creation_time,
p_sj_compl_subinventory => p_wltx_starting_jobs_tbl(l_rep_sj_index).completion_subinventory,
p_sj_compl_locator_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).completion_locator_id,
p_rj_job_rec => p_wltx_resulting_jobs_tbl(l_job_counter),
p_request_id => g_request_id,
x_err_code => l_err_code,
x_err_buf => l_err_buf ,
x_msg_count => x_msg_count
);
l_msg_tokens.delete;
p_msg_text => 'UPDATE_ASSEMBLY_OR_ROUTING returned failure' ,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
select operation_seq_num
into l_current_rtg_op_seq_num
from bom_operation_sequences
where operation_sequence_id = p_wltx_starting_jobs_tbl(l_rep_sj_index).operation_seq_id ;
update wsm_lot_based_jobs
set CURRENT_RTG_OP_SEQ_NUM = l_current_rtg_op_seq_num,
CURRENT_JOB_OP_SEQ_NUM = l_current_job_op_seq_num
where wip_entity_id = l_new_rj_we_id_tbl(i)
and CURRENT_RTG_OP_SEQ_NUM is null
and CURRENT_JOB_OP_SEQ_NUM is null;
l_msg_tokens.delete;
l_msg_tokens.delete;
select gen_object_id
into l_sj_gen_object_id
from wip_entities we
where we.wip_entity_id = p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id;
select gen_object_id
into l_rj_gen_object_id
from wip_entities we
where we.wip_entity_id = p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id;
inv_genealogy_pub.insert_genealogy( p_api_version =>1.0,
p_object_type =>5,
p_object_id =>l_sj_gen_object_id,
p_object_number =>p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_name,
p_inventory_item_id =>p_wltx_starting_jobs_tbl(l_rep_sj_index).primary_item_id,
p_org_id =>p_wltx_starting_jobs_tbl(l_rep_sj_index).organization_id,
p_parent_object_type => 5,
p_parent_object_id => l_rj_gen_object_id,
p_parent_object_number => p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_name,
p_parent_inventory_item_id=> p_wltx_resulting_jobs_tbl(l_job_counter).primary_item_id,
p_parent_org_id => p_wltx_resulting_jobs_tbl(l_job_counter).organization_id,
p_genealogy_origin => 3, -- for WIP parent
p_genealogy_type => 4, -- for WIP/Inv Split/Merge/Translate
p_origin_txn_id => p_wltx_header.transaction_id,
x_return_status =>l_ret_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data
);
l_msg_tokens.delete;
p_msg_text => 'inv_genealogy_pub.insert_genealogy returned success',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
p_msg_text => 'inv_genealogy_pub.insert_genealogy failed',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
l_err_buf := 'Error in inv_genealogy_pub.insert_genealogy: '||l_msg_data;
l_err_buf := 'Multiple errors in inv_genealogy_pub.insert_genealogy - populated in the log file';
select gen_object_id
into l_rj_gen_object_id
from wip_entities we
where we.wip_entity_id = p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_id;
select gen_object_id
into l_sj_gen_object_id
from wip_entities we
where we.wip_entity_id = p_wltx_starting_jobs_tbl(l_job_counter).wip_entity_id;
inv_genealogy_pub.insert_genealogy( p_api_version =>1.0,
p_object_type =>5,
p_object_id =>l_sj_gen_object_id,
p_object_number =>p_wltx_starting_jobs_tbl(l_job_counter).wip_entity_name,
p_inventory_item_id =>p_wltx_starting_jobs_tbl(l_job_counter).primary_item_id,
p_org_id =>p_wltx_starting_jobs_tbl(l_job_counter).organization_id,
p_parent_object_type => 5,
p_parent_object_id => l_rj_gen_object_id,
p_parent_object_number => p_wltx_resulting_jobs_tbl(p_wltx_resulting_jobs_tbl.first).wip_entity_name,
p_parent_inventory_item_id=> p_wltx_resulting_jobs_tbl(p_wltx_resulting_jobs_tbl.first).primary_item_id,
p_parent_org_id => p_wltx_resulting_jobs_tbl(p_wltx_resulting_jobs_tbl.first).organization_id,
p_genealogy_origin => 3, -- for WIP parent
p_genealogy_type => 4, -- for WIP/Inv Split/Merge/Translate
p_origin_txn_id => p_wltx_header.transaction_id,
x_return_status =>l_ret_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data
);
l_msg_tokens.delete;
p_msg_text => 'inv_genealogy_pub.insert_genealogy returned success',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
p_msg_text => 'inv_genealogy_pub.insert_genealogy failed',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
l_err_buf := 'Error in inv_genealogy_pub.insert_genealogy: '||l_msg_data;
l_err_buf := 'Multiple errors in inv_genealogy_pub.insert_genealogy - populated in the log file';
Insert_MMT_record ( p_txn_id => p_wltx_header.transaction_id,
p_txn_org_id => p_txn_org_id,
p_txn_date => p_wltx_header.transaction_date,--sysdate, --l_txn_date,
p_txn_type_id => p_wltx_header.transaction_type_id,
p_sj_wip_entity_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id,
p_sj_wip_entity_name => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_name,
p_sj_avail_quantity => p_wltx_starting_jobs_tbl(l_rep_sj_index).quantity_available,
p_rj_wip_entity_id => p_wltx_resulting_jobs_tbl(p_wltx_resulting_jobs_tbl.first).wip_entity_id,
p_rj_wip_entity_name => p_wltx_resulting_jobs_tbl(p_wltx_resulting_jobs_tbl.first).wip_entity_name,
p_rj_start_quantity => p_wltx_resulting_jobs_tbl(p_wltx_resulting_jobs_tbl.first).start_quantity,
p_sj_item_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).primary_item_id,
p_sj_op_seq_num => p_wltx_starting_jobs_tbl(l_rep_sj_index).operation_seq_num,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_msg_tokens.delete;
p_msg_text => 'Insert_MMT_record failed',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
elsif p_wltx_header.transaction_type_id in (WSMPCNST.UPDATE_ASSEMBLY,WSMPCNST.UPDATE_ROUTING) then
l_stmt_num := 500;
UPDATE_ASSEMBLY_OR_ROUTING( p_txn_id => null,
p_txn_type_id => p_wltx_header.transaction_type_id,
-- p_rep_wip_entity_id => p_wltx_resulting_jobs_tbl(l_job_counter).wip_entity_id,
p_job_kanban_card_id => p_wltx_starting_jobs_tbl(p_wltx_starting_jobs_tbl.first).kanban_card_id,
p_po_creation_time => l_po_creation_time,
p_sj_compl_subinventory => p_wltx_starting_jobs_tbl(p_wltx_starting_jobs_tbl.first).completion_subinventory,
p_sj_compl_locator_id => p_wltx_starting_jobs_tbl(p_wltx_starting_jobs_tbl.first).completion_locator_id,
p_rj_job_rec => p_wltx_resulting_jobs_tbl(p_wltx_resulting_jobs_tbl.first),
p_request_id => g_request_id,
x_err_code => l_err_code,
x_err_buf => l_err_buf ,
x_msg_count => x_msg_count
);
l_msg_tokens.delete;
p_msg_text => 'UPDATE_ASSEMBLY_OR_ROUTING failed',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
p_msg_text => 'UPDATE_ASSEMBLY_OR_ROUTING failed',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
elsif p_wltx_header.transaction_type_id = WSMPCNST.UPDATE_QUANTITY then
l_stmt_num := 520;
p_txn_type => WSMPCNST.UPDATE_QUANTITY,
p_wip_entity_id_tbl => l_sj_we_id_tbl,
p_new_job_qty_tbl => l_sj_new_qty_tbl,
p_new_net_qty_tbl => l_sj_new_net_qty_tbl,
p_txn_job_op_seq_tbl => l_sj_op_seq_tbl,
p_txn_job_intraop => p_wltx_starting_jobs_tbl(l_rep_sj_index).intraoperation_step,
p_sj_st_qty_tbl => l_sj_old_st_qty_tbl,
p_sj_avail_qty_tbl => l_sj_avail_qty_tbl,
p_sj_scrap_qty_tbl => l_sj_scrap_qty_tbl,
x_err_code => l_err_code,
x_err_buf => l_err_buf
);
l_msg_tokens.delete;
l_non_rep_sj_tbl.delete;
l_new_rj_we_id_tbl.delete;
l_new_rj_qty_tbl.delete;
UPDATE_QTY_ISSUED(p_txn_id => null,
p_txn_type => WSMPCNST.UPDATE_QUANTITY,
p_rep_we_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id,
p_rep_op_seq_num => p_wltx_starting_jobs_tbl(l_rep_sj_index).operation_seq_num,
p_rep_avail_qty => p_wltx_starting_jobs_tbl(l_rep_sj_index).quantity_available,
p_rep_new_job_qty => p_wltx_resulting_jobs_tbl(l_rj_index).start_quantity,
p_txn_job_intraop => p_wltx_starting_jobs_tbl(l_rep_sj_index).intraoperation_step,
p_non_rep_sj_we_id_tbl => l_non_rep_sj_tbl,
p_new_rj_we_id_tbl => l_new_rj_we_id_tbl,
p_new_rj_start_qty => l_new_rj_qty_tbl,
x_err_code => l_err_code,
x_err_buf => l_err_buf
);
l_msg_tokens.delete;
p_msg_text => 'UPDATE_QTY_ISSUED failed',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
if l_sub_loc_change = 0 then -- reflect the updated quantity in the card
l_stmt_num := 147;
INV_Kanban_PVT.Update_Card_Supply_Status( x_return_status => l_ret_Status,
p_Kanban_Card_Id => l_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_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id,
p_Document_detail_Id => '',
p_replenish_quantity => p_wltx_resulting_jobs_tbl(l_rj_index).start_quantity
);
l_msg_tokens.delete;
update wip_discrete_jobs
SET completion_subinventory = p_wltx_resulting_jobs_tbl(l_rj_index).completion_subinventory,
completion_locator_id = p_wltx_resulting_jobs_tbl(l_rj_index).completion_locator_id,
kanban_card_id = l_kanban_card_id
where wip_entity_id = p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id;
l_msg_tokens.delete;
Insert_MMT_record ( p_txn_id => p_wltx_header.transaction_id,
p_txn_org_id => p_txn_org_id,
p_txn_date => sysdate, --l_txn_date, /* has to be txn date... */
p_txn_type_id => WSMPCNST.update_quantity,
p_sj_wip_entity_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id,
p_sj_wip_entity_name => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_name,
p_sj_avail_quantity => p_wltx_starting_jobs_tbl(l_rep_sj_index).quantity_available,
p_rj_wip_entity_id => p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_id,
p_rj_wip_entity_name => p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_name,
p_rj_start_quantity => p_wltx_resulting_jobs_tbl(l_rj_index).start_quantity,
p_sj_item_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).primary_item_id,
p_sj_op_seq_num => p_wltx_starting_jobs_tbl(l_rep_sj_index).operation_seq_num,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_msg_tokens.delete;
p_msg_text => 'Insert_MMT_record failed',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
elsif p_wltx_header.transaction_type_id = WSMPCNST.UPDATE_LOT_NAME then
l_stmt_num := 550;
l_msg_tokens.delete;
p_msg_text => 'Transaction type is Update Lotname:',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
select wip_entity_id
into p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_id
from wip_entities
where wip_entity_name like p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_name;
update wip_discrete_jobs
set lot_number = p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_name,
description = p_wltx_resulting_jobs_tbl(l_rj_index).description,
/* Start Bugfix 5531371 csi/loc is updatable in upd lot name */
completion_subinventory = p_wltx_resulting_jobs_tbl(l_rj_index).completion_subinventory,
completion_locator_id = p_wltx_resulting_jobs_tbl(l_rj_index).completion_locator_id
/* End Bugfix 5531371*/
where wip_entity_id = p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_id;
update wip_entities
set wip_entity_name = p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_name,
description = p_wltx_resulting_jobs_tbl(l_rj_index).description
where wip_entity_id = p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_last_update_date => sysdate,
p_last_updated_by => g_user_id,
p_last_update_login => g_user_login_id,
p_creation_date => sysdate,
p_created_by => g_user_id,
p_request_id => g_request_id,
p_program_app_id => g_program_appl_id,
p_program_id => g_program_id,
p_program_update_date => sysdate,
x_err_code => l_err_code,
x_err_buf => l_err_buf
);
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT max(operation_seq_num)
INTO l_bonus_job_st_op_seq
FROM wip_operations
WHERE wip_entity_id = l_new_we_id;
l_msg_tokens.delete;
p_msg_text => 'Updated the job seq num in resulting job record : ' || l_bonus_job_st_op_seq,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
UPDATE wip_operations
SET wsm_op_seq_num = l_bonus_rtg_st_op_seq
WHERE wip_entity_id = l_new_we_id
AND operation_seq_num = l_bonus_job_st_op_seq;
l_msg_tokens.delete;
p_msg_text => 'Updated the op seq num in wip_operations',
p_stmt_num => l_stmt_num,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
/*insert into WLBJ here as not handled in new CREATE_WSOR_WLBJ_RECORDS,the new MES columns are also handled here*/
l_stmt_num:=105;
INSERT into WSM_LOT_BASED_JOBS
(WIP_ENTITY_ID,
ORGANIZATION_ID,
ON_REC_PATH,
INTERNAL_COPY_TYPE,
COPY_PARENT_WIP_ENTITY_ID,
INFINITE_SCHEDULE,
CURRENT_JOB_OP_SEQ_NUM, --MES add
CURRENT_RTG_OP_SEQ_NUM, --MES add
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_id,
p_wltx_resulting_jobs_tbl(l_rj_index).organization_id,
'Y', -- ON_REC_PATH
0,
NULL, -- COPY_PARENT_WIP_ENTITY_ID
NULL, -- INFINITE_SCHEDULE
p_wltx_resulting_jobs_tbl(l_rj_index).job_operation_seq_num,
p_wltx_resulting_jobs_tbl(l_rj_index).starting_operation_seq_num,
sysdate,
g_user_id,
g_user_login_id,
sysdate,
g_user_id,
g_request_id,
g_program_appl_id,
g_program_id,
sysdate
);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'Inserting MMT record',
p_stmt_num => l_stmt_num,
p_msg_tokens => l_msg_tokens,
p_fnd_log_level => g_log_level_statement,
p_run_log_level => l_log_level
);
Insert_MMT_record ( p_txn_id => p_wltx_header.transaction_id,
p_txn_org_id => p_wltx_resulting_jobs_tbl(l_rj_index).organization_id,
p_txn_date => p_wltx_header.transaction_date,--sysdate, --l_txn_date,
p_txn_type_id => p_wltx_header.transaction_type_id,
p_sj_wip_entity_id => null,
p_sj_wip_entity_name => null,
p_sj_avail_quantity => null,
p_rj_wip_entity_id => p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_id,
p_rj_wip_entity_name => p_wltx_resulting_jobs_tbl(l_rj_index).wip_entity_name,
p_rj_start_quantity => p_wltx_resulting_jobs_tbl(l_rj_index).start_quantity,
p_sj_item_id => p_wltx_resulting_jobs_tbl(l_rj_index).primary_item_id,
p_sj_op_seq_num => p_wltx_resulting_jobs_tbl(l_rj_index).starting_operation_seq_num,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_msg_tokens.delete;
p_msg_text => 'Insert_MMT_record failed:'||l_msg_data,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
l_msg_tokens.delete;
If p_wltx_header.transaction_type_id in (WSMPCNST.SPLIT,WSMPCNST.UPDATE_ASSEMBLY,
WSMPCNST.UPDATE_QUANTITY,WSMPCNST.UPDATE_ROUTING,WSMPCNST.UPDATE_LOT_NAME) then
l_rep_sj_index := p_wltx_starting_jobs_tbl.first;
If p_wltx_header.transaction_type_id in (WSMPCNST.UPDATE_QUANTITY,WSMPCNST.UPDATE_ROUTING,WSMPCNST.UPDATE_LOT_NAME) then
l_rj_index := p_wltx_resulting_jobs_tbl.first;
WSM_RESERVATIONS_PVT.Modify_reservations_jobupdate(p_wip_entity_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).wip_entity_id,
P_old_net_qty => p_wltx_starting_jobs_tbl(l_rep_sj_index).net_quantity,
P_new_net_qty => p_wltx_resulting_jobs_tbl(l_rj_index).net_quantity,
P_inventory_item_id => p_wltx_starting_jobs_tbl(l_rep_sj_index).primary_item_id,
P_org_id => p_wltx_header.organization_id,
P_status_type => p_wltx_resulting_jobs_tbl(l_rj_index).status_type,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
); --this is to handle the change in net qty if any.
l_msg_tokens.delete;
p_msg_text => 'WSM_RESERVATIONS_PVT.Modify_reservations_jobupdate failed:'||l_msg_data,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
l_msg_tokens.delete;
DELETE FROM BOM_EXPLOSION_TEMP
WHERE GROUP_ID = WSMPWROT.EXPLOSION_GROUP_ID;