[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select organization_code
into l_org_code
from org_organization_definitions
where organization_id = p_org_id;
select organization_code
into l_org_code
from mtl_parameters
where organization_id = p_org_id;
SELECT nvl(op_seq_num_increment, 10),
allow_backward_move_flag,
charge_jump_from_queue
INTO g_prev_op_seq_incr,
g_allow_bkw_move,
g_param_jump_fm_q
FROM wsm_parameters
WHERE organization_id = p_org_id;
/* SELECT MAX(ACCT_PERIOD_ID)
INTO g_acct_period_id
FROM ORG_ACCT_PERIODS
WHERE PERIOD_CLOSE_DATE IS NULL
AND ORGANIZATION_ID = p_org_id
AND TRUNC(NVL(p_txn_date,SYSDATE))
BETWEEN PERIOD_START_DATE and SCHEDULE_CLOSE_DATE;*/
SELECT wip_entity_id
INTO l_wip_entity_id
FROM wsm_lot_move_txn_interface
WHERE header_id = p_header_id;
SELECT wip_entity_name, entity_type
INTO l_wip_entity_name_temp, l_entity_type
FROM wip_entities
WHERE organization_id = p_org_id
AND wip_entity_id = l_wip_entity_id;
UPDATE wsm_lot_move_txn_interface
SET wip_entity_name = l_wip_entity_name_temp
WHERE header_id = p_header_id;
SELECT wip_entity_id, entity_type
INTO l_wip_entity_id, l_entity_type
FROM wip_entities
WHERE organization_id = p_org_id
-- AND wip_entity_name = p_wip_entity_name;
SELECT status_type, primary_item_id
INTO l_status_type, l_primary_item_id
FROM wip_discrete_jobs
WHERE wip_entity_id = l_wip_entity_id
AND organization_id = p_org_id;
SELECT nvl(wip_entity_id, -1)
INTO l_wlmti_wip_entity_id
FROM wsm_lot_move_txn_interface
WHERE header_id = p_header_id
AND wip_entity_name = NVL(p_wip_entity_name, l_wip_entity_name_temp); -- Fix for bug #2095035
update wsm_lot_move_txn_interface
set wip_entity_id = l_wip_entity_id,
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
SELECT nvl(routing_revision_date, SYSDATE)
INTO l_rtg_revision_date
FROM wip_discrete_jobs
WHERE wip_entity_id = l_wip_entity_id;
SELECT group_id,
entity_type,
nvl(primary_item_id, -1),
organization_code,
nvl(last_updated_by, -1),
last_updated_by_name,
nvl(created_by, -1),
created_by_name,
nvl(acct_period_id, -1),
nvl(reason_id, -1),
reason_name,
nvl(scrap_account_id, -1),
scrap_at_operation_flag,
-- scrap_quantity
nvl(scrap_quantity, 0),
source_code
INTO l_group_id,
l_entity_type,
l_wlmti_primary_item_id,
l_wlmti_org_code,
l_wlmti_last_upd_by,
l_wlmti_last_upd_name,
l_wlmti_created_by,
l_wlmti_created_by_name,
l_wlmti_acct_period_id,
l_reason_id,
l_reason_name,
l_wlmti_scrap_acct_id,
l_scrap_at_operation_flag,
l_scrap_qty,
l_source_code
FROM wsm_lot_move_txn_interface
WHERE wip_entity_id = l_wip_entity_id
AND header_id = p_header_id;
update wsm_lot_move_txn_interface
set primary_item_id = l_primary_item_id,
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
update wsm_lot_move_txn_interface
set organization_code = g_prev_org_code, --l_org_code,
--***VJ Changed for Performance Upgrade***--
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
l_msg_tokens.delete;
select user_name
into l_user_name
from fnd_user
where user_id = l_wlmti_last_upd_by
and sysdate between START_DATE and NVL(END_DATE,SYSDATE+1);
FND_MESSAGE.SET_TOKEN('FLD_NAME', 'last_updated_by');
l_msg_tokens.delete;
FND_MESSAGE.SET_TOKEN('FLD_NAME', 'last_updated_by_name');
update wsm_lot_move_txn_interface
set last_updated_by_name = g_prev_upd_user_name, --l_user_name,
--***VJ Changed for Performance Upgrade***--
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
FND_MESSAGE.SET_TOKEN('FLD_NAME', 'last_updated_by_name');
select user_name
into l_user_name
from fnd_user
where user_id = l_wlmti_created_by
and sysdate BETWEEN START_DATE and NVL(END_DATE,SYSDATE+1);
update wsm_lot_move_txn_interface
set created_by_name = g_prev_cr_user_name, --l_user_name,
--***VJ Changed for Performance Upgrade***--
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
SELECT acct_period_id
INTO l_temp
FROM org_acct_periods
WHERE acct_period_id = l_wlmti_acct_period_id
-- begin bugfix 1631484: check if the acct period is open.
AND open_flag = 'Y'
AND organization_id = p_org_id
AND period_start_date <= p_txn_date;
SELECT reason_name
INTO l_mtr_reason_name
FROM mtl_transaction_reasons
where reason_id = l_reason_id;
update wsm_lot_move_txn_interface
set reason_name = l_mtr_reason_name,
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
update wsm_lot_move_txn_interface
set reason_name = l_mtr_reason_name,
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
SELECT msi.primary_uom_code,
msi.serial_number_control_code
INTO l_primary_uom,
l_serial_ctrl_code
FROM mtl_system_items msi,
wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = l_wip_entity_id
AND wdj.primary_item_id = msi.inventory_item_id
AND msi.organization_id = wdj.organization_id;
update wsm_lot_move_txn_interface
set primary_uom = l_primary_uom,
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
select max(1)
into l_dummy
from wip_shop_floor_statuses ws,
wip_shop_floor_status_codes wsc
where wsc.organization_id = p_org_id
and ws.organization_id = p_org_id
and ws.wip_entity_id = l_wip_entity_id
and ws.line_id is null
and ws.operation_seq_num = p_fm_op_seq_num
and ws.intraoperation_step_type = p_fm_intraop_step_type
and ws.shop_floor_status_code = wsc.shop_floor_status_code
and wsc.status_move_flag = 2
and nvl(wsc.disable_date, sysdate + 1) > sysdate;
select moves_over_no_move_statuses
into l_override_no_move_no_skip
from wip_parameters
where organization_id = p_org_id;
l_msg_tokens.delete;
select max(OPERATION_SEQ_NUM)
into l_max_qty_op_seq_num
from wip_operations
where organization_id = p_org_id
and wip_entity_id = l_wip_entity_id
and ((QUANTITY_IN_QUEUE > 0) or
(QUANTITY_RUNNING > 0) or
(QUANTITY_WAITING_TO_MOVE > 0) or
(QUANTITY_SCRAPPED > 0));
select operation_seq_num,
operation_sequence_id,
standard_operation_id,
department_id,
decode(sign(QUANTITY_IN_QUEUE),1,QUANTITY_IN_QUEUE,0),
decode(sign(QUANTITY_RUNNING),1,QUANTITY_RUNNING,0),
decode(sign(QUANTITY_WAITING_TO_MOVE),1,QUANTITY_WAITING_TO_MOVE,0),
decode(sign(QUANTITY_SCRAPPED),1,QUANTITY_SCRAPPED,0),
nvl(wsm_op_seq_num, -1)
into l_wo_op_seq_num,
l_wo_op_seq_id,
l_wo_std_op_id,
l_wo_dept_id,
l_wo_qty_in_queue,
l_wo_qty_in_running,
l_wo_qty_in_tomove,
l_wo_qty_in_scrap,
l_wo_rtg_op_seq_num
from wip_operations
where organization_id = p_org_id
and wip_entity_id = l_wip_entity_id
and OPERATION_SEQ_NUM = l_max_qty_op_seq_num; --***VJ Changed for Performance Upgrade***--
SELECT operation_code
INTO l_wo_op_code
FROM bom_standard_operations
WHERE standard_operation_id = l_wo_std_op_id;
UPDATE wsm_lot_move_txn_interface
SET fm_operation_code = l_wo_op_code
WHERE header_id = p_header_id;
select 1
into l_temp
from bom_operation_sequences
where operation_sequence_id = l_wo_op_seq_id
and routing_sequence_id = l_routing_seq_id;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET (FM_DEPARTMENT_ID,
FM_DEPARTMENT_CODE) =
(SELECT bd.department_id,
bd.department_code
FROM BOM_DEPARTMENTS bd
WHERE bd.department_id = l_wo_dept_id)
WHERE WLMTI.header_id = p_header_id;
SELECT nvl(common_routing_sequence_id, routing_reference_id)
INTO l_routing_seq_id
FROM wip_discrete_jobs
WHERE wip_entity_id = l_wip_entity_id;
SELECT operation_sequence_id,
department_id,
standard_operation_id
INTO l_op_seq_id, -- CZH.I_OED-2: since select from BOS, replacement is already considered!
l_dept_id,
l_std_op_id
FROM bom_operation_sequences
WHERE operation_seq_num = p_to_op_seq_num
AND routing_sequence_id = l_routing_seq_id
-- BC: CZH.I_OED-1, use wdj.routing_revision_date instead of SYSDATE
--and nvl(disable_date, sysdate+1) > sysdate
--and nvl(effectivity_date, sysdate) <= sysdate;
SELECT operation_code
INTO l_op_code
FROM bom_standard_operations
WHERE NVL(operation_type, 1) = 1 -- Standard operation
AND organization_id = p_org_id
AND standard_operation_id = l_std_op_id;
update wsm_lot_move_txn_interface
set to_operation_code = l_op_code
where header_id = p_header_id;
select standard_operation_id,
department_id
into l_std_op_id,
l_dept_id
from bom_standard_operations
where nvl(operation_type, 1) = 1
and organization_id = p_org_id
and operation_code = p_to_op_code;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET ( TO_DEPARTMENT_ID, -- Fix bug #1501376
TO_DEPARTMENT_CODE )=
(SELECT bd.department_id, -- Fix bug #1501376
bd.department_code
FROM BOM_DEPARTMENTS bd
WHERE bd.department_id = l_dept_id) -- Fix bug #1501376
WHERE WLMTI.header_id = p_header_id;
select 1
into l_temp
from bom_operation_networks
--where WSMPUTIL.replacement_op_seq_id (
-- from_op_seq_id,
-- l_rtg_revision_date) = l_wo_op_seq_id
where from_op_seq_id IN (
select bos.operation_sequence_id
from bom_operation_sequences bos,
bom_operation_sequences bos2
where bos.operation_seq_num = bos2.operation_seq_num
AND bos.routing_sequence_id = bos2.routing_sequence_id
AND bos2.operation_sequence_id = l_wo_op_seq_id
)
and WSMPUTIL.replacement_op_seq_id (
to_op_seq_id,
l_rtg_revision_date) = l_op_seq_id;
SELECT nvl(standard_operation_id, -1),
nvl(department_id, -1),
operation_sequence_id
INTO l_jmp_std_op_id,
l_jmp_dept_id,
l_jmp_op_seq_id
FROM bom_operation_sequences
WHERE operation_seq_num = p_to_op_seq_num
AND routing_sequence_id = l_routing_seq_id
-- BC: CZH.I_OED-1, use wdj.routing_revision_date instead of SYSDATE
--and nvl(disable_date, sysdate+1) >= sysdate
--and nvl(effectivity_date, sysdate) <= sysdate;
SELECT operation_code
INTO l_jmp_op_code
FROM bom_standard_operations
WHERE standard_operation_id = l_jmp_std_op_id
AND organization_id = p_org_id;
update wsm_lot_move_txn_interface
set to_operation_code = l_jmp_op_code
where header_id = p_header_id;
SELECT nvl(standard_operation_id, -1),
nvl(department_id, -1)
INTO l_jmp_std_op_id,
l_jmp_dept_id
FROM bom_standard_operations
WHERE operation_code = p_to_op_code
AND organization_id = p_org_id;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET ( TO_DEPARTMENT_ID, -- Fix bug #1501376
TO_DEPARTMENT_CODE )=
(SELECT bd.department_id, -- Fix bug #1501376
bd.department_code
FROM BOM_DEPARTMENTS bd
WHERE bd.department_id = l_jmp_dept_id)
WHERE WLMTI.header_id = p_header_id;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET (FM_DEPARTMENT_ID,
FM_DEPARTMENT_CODE) =
(SELECT WCO.department_id,
WCO.department_code
FROM WSM_COPY_OPERATIONS WCO
WHERE WCO.wip_entity_id = l_wip_entity_id
AND WCO.operation_sequence_id = l_op_seq_id)
WHERE WLMTI.header_id = p_header_id;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET (FM_DEPARTMENT_ID,
FM_DEPARTMENT_CODE) =
(SELECT bd.department_id,
bd.department_code
FROM BOM_DEPARTMENTS bd
WHERE bd.department_id = l_wo_dept_id)
WHERE WLMTI.header_id = p_header_id;
SELECT operation_sequence_id,
standard_operation_id,
department_id,
operation_seq_num,
standard_operation_code
INTO l_end_op_seq_id,
l_end_std_op_id,
l_end_dept_id,
l_end_op_seq_num,
l_end_op_code
FROM WSM_COPY_OPERATIONS WCO
WHERE WCO.wip_entity_id=l_wip_entity_id
AND WCO.network_start_end='E';
SELECT operation_sequence_id,
department_id,
department_code,
standard_operation_id,
standard_operation_code,
nvl(WCO.recommended, 'N'),
scrap_account,
backflush_flag
INTO l_op_seq_id,
l_dept_id,
l_to_dept_code,
l_std_op_id,
l_op_code,
l_recommended,
--bug 3571019 changed l_scrap_acc_id to l_to_scrap_id for clarity
-- l_scrap_acc_id,
l_to_scrap_id,
l_to_op_bkflsh_flag
FROM WSM_COPY_OPERATIONS WCO
WHERE WCO.wip_entity_id=l_wip_entity_id
AND WCO.operation_seq_num = p_to_op_seq_num;
update wsm_lot_move_txn_interface
set to_operation_code = l_op_code
where header_id = p_header_id;
select standard_operation_id,
department_id
into l_std_op_id,
l_dept_id
from bom_standard_operations
where nvl(operation_type, 1) = 1
and organization_id = p_org_id
and operation_code = p_to_op_code;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET ( TO_DEPARTMENT_ID, -- Fix bug #1501376
TO_DEPARTMENT_CODE )=
(SELECT bd.department_id, -- Fix bug #1501376
bd.department_code
FROM BOM_DEPARTMENTS bd
WHERE bd.department_id = l_dept_id) -- Fix bug #1501376
WHERE WLMTI.header_id = p_header_id
RETURNING TO_DEPARTMENT_CODE
INTO l_to_dept_code;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET TO_DEPARTMENT_CODE=l_to_dept_code
WHERE WLMTI.header_id = p_header_id;
SELECT 1
INTO l_temp
FROM WSM_COPY_OP_NETWORKS WCON
WHERE WCON.wip_entity_id=l_wip_entity_id
AND WCON.from_op_seq_id=l_wo_op_seq_id
AND WCON.to_op_seq_num=p_to_op_seq_num;
SELECT 1
INTO l_temp
FROM WSM_COPY_OP_NETWORKS WCON
WHERE WCON.wip_entity_id=l_wip_entity_id
AND WCON.from_op_seq_num = l_wo_rtg_op_seq_num
AND WCON.to_op_seq_num=p_to_op_seq_num;
SELECT operation_sequence_id,
department_id,
department_code,
standard_operation_id,
standard_operation_code,
nvl(WCO.recommended, 'N'),
scrap_account,
backflush_flag
INTO l_jmp_op_seq_id,
l_jmp_dept_id,
l_jmp_to_dept_code,
l_jmp_std_op_id,
l_jmp_op_code,
l_recommended,
--bug 3571019 changed l_scrap_acc_id to l_to_scrap_id for clarity
-- l_scrap_acc_id,
l_to_scrap_id,
l_to_op_bkflsh_flag
FROM WSM_COPY_OPERATIONS WCO
WHERE WCO.wip_entity_id=l_wip_entity_id
AND WCO.operation_seq_num = p_to_op_seq_num;
update wsm_lot_move_txn_interface
set to_operation_code = l_jmp_op_code
where header_id = p_header_id;
SELECT nvl(standard_operation_id, -1),
nvl(department_id, -1)
INTO l_jmp_std_op_id,
l_jmp_dept_id
FROM bom_standard_operations
WHERE operation_code = p_to_op_code
AND organization_id = p_org_id;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET ( TO_DEPARTMENT_ID, -- Fix bug #1501376
TO_DEPARTMENT_CODE )=
(SELECT bd.department_id, -- Fix bug #1501376
bd.department_code
FROM BOM_DEPARTMENTS bd
WHERE bd.department_id = l_jmp_dept_id)
WHERE WLMTI.header_id = p_header_id
RETURNING TO_DEPARTMENT_CODE
INTO l_to_dept_code;
SELECT standard_operation_id
INTO l_std_op_id
FROM bom_standard_operations
WHERE operation_code = l_op_code
AND organization_id = p_org_id;
select job_type
into l_job_type
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id;
SELECT WCO.scrap_account
INTO l_scrap_acc_id
FROM WSM_COPY_OPERATIONS WCO
WHERE WCO.wip_entity_id = l_wip_entity_id
AND WCO.operation_seq_num = l_wo_rtg_op_seq_num;
SELECT nvl(scrap_account, -1)
INTO l_wlmti_scrap_acct_id
FROM BOM_DEPARTMENTS
WHERE DEPARTMENT_ID=decode(l_scrap_at_operation_flag,
2, l_to_dept_id,
l_wo_dept_id) --p_to_dept_id --Fixed bug #1928993
AND ORGANIZATION_ID=p_org_id;
update wsm_lot_move_txn_interface
set scrap_account_id = l_wlmti_scrap_acct_id,
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
SELECT bd.department_id
INTO l_temp
FROM gl_code_combinations gcc,
bom_departments bd
WHERE gcc.code_combination_id = l_wlmti_scrap_acct_id
AND bd.scrap_account = l_wlmti_scrap_acct_id
-- AND bd.department_id = l_dept_id;
SELECT code_combination_id
INTO l_temp
FROM gl_code_combinations gl
WHERE gl.code_combination_id = l_wlmti_scrap_acct_id
and gl.enabled_flag = 'Y'
and gl.summary_flag = 'N'
and NVL(gl.start_date_active, sysdate) <= sysdate
and NVL(gl.end_date_active, sysdate) >= sysdate;
select job_type
into l_job_type
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id;
SELECT WCO.scrap_account
INTO l_from_scrap_id
FROM WSM_COPY_OPERATIONS WCO
WHERE WCO.wip_entity_id = l_wip_entity_id
AND WCO.operation_seq_num = l_wo_rtg_op_seq_num;
fnd_file.put_line(fnd_file.log, 'in no data found after selecting from wco');
SELECT nvl(scrap_account, -1)
INTO l_wlmti_scrap_acct_id
FROM BOM_DEPARTMENTS
WHERE DEPARTMENT_ID=decode(l_scrap_at_operation_flag,
2, l_to_dept_id,
l_wo_dept_id)
AND ORGANIZATION_ID=p_org_id;
update wsm_lot_move_txn_interface
set scrap_account_id = l_wlmti_scrap_acct_id,
error = l_error_msg
where header_id = p_header_id;
SELECT gcc.code_combination_id
INTO l_temp
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = l_wlmti_scrap_acct_id;
SELECT gcc.code_combination_id
INTO l_temp
FROM gl_code_combinations gcc,
WSM_COPY_OPERATIONS WCO
WHERE gcc.code_combination_id = l_wlmti_scrap_acct_id
AND WCO.scrap_account = gcc.code_combination_id
AND WCO.wip_entity_id = l_wip_entity_id
AND WCO.operation_seq_num = decode(l_scrap_at_operation_flag,
2, p_to_op_seq_num,
l_wo_rtg_op_seq_num);
SELECT gcc.code_combination_id
INTO l_temp
FROM gl_code_combinations gcc,
bom_departments bd
WHERE gcc.code_combination_id = l_wlmti_scrap_acct_id
AND bd.scrap_account = gcc.code_combination_id
AND bd.department_id = decode(l_scrap_at_operation_flag,
2, l_dept_id,
l_wo_dept_id);
SELECT nvl(scrap_account, -1)
INTO l_wlmti_scrap_acct_id
FROM BOM_DEPARTMENTS
WHERE DEPARTMENT_ID=decode(l_scrap_at_operation_flag,
2, l_to_dept_id,
l_wo_dept_id)
AND ORGANIZATION_ID=p_org_id;
update wsm_lot_move_txn_interface
set scrap_account_id = l_wlmti_scrap_acct_id,
error = l_error_msg
where header_id = p_header_id;
SELECT gcc.code_combination_id
INTO l_temp
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = l_wlmti_scrap_acct_id;
SELECT gcc.code_combination_id
INTO l_temp
FROM gl_code_combinations gcc,
bom_departments bd
WHERE gcc.code_combination_id = l_wlmti_scrap_acct_id
AND bd.scrap_account = gcc.code_combination_id
AND bd.department_id = decode(l_scrap_at_operation_flag,
2, l_dept_id,
l_wo_dept_id);
SELECT code_combination_id
INTO l_temp
FROM gl_code_combinations gl
WHERE gl.code_combination_id = l_wlmti_scrap_acct_id
and gl.enabled_flag = 'Y'
and gl.summary_flag = 'N'
and NVL(gl.start_date_active, sysdate) <= sysdate
and NVL(gl.end_date_active, sysdate) >= sysdate;
SELECT operation_seq_num
INTO l_temp -- <> 0, if moving from the last op
FROM bom_operation_sequences
WHERE operation_seq_num = p_fm_op_seq_num
AND operation_sequence_id = l_end_op_seq_id;
SELECT operation_sequence_id, backflush_flag
INTO l_temp, l_fm_op_bkflsh_flag
FROM wip_operations
WHERE operation_seq_num = p_fm_op_seq_num
AND wip_entity_id = l_wip_entity_id
AND ORGANIZATION_ID = p_org_id;
SELECT standard_operation_id,
department_id,
operation_seq_num
INTO l_end_std_op_id,
l_end_dept_id,
l_end_op_seq_num
FROM bom_operation_sequences
WHERE operation_sequence_id = l_end_op_seq_id;
select completion_subinventory
into l_cmp_subinv
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id
and organization_id = p_org_id;
SELECT operation_code
INTO l_end_op_code
FROM bom_standard_operations
WHERE standard_operation_id = l_end_std_op_id;
UPDATE wsm_lot_move_txn_interface
SET transaction_type = 2,
--error = 'WARNING: Changing transaction_type to 2' -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
WHERE header_id = p_header_id;
UPDATE wsm_lot_move_txn_interface
SET transaction_type = 1,
--error = 'WARNING: Changing transaction_type to 1' -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
WHERE header_id = p_header_id;
UPDATE wsm_lot_move_txn_interface
SET transaction_type = 2,
--error = 'WARNING: Changing transaction_type to 2' -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
WHERE header_id = p_header_id;
UPDATE wsm_lot_move_txn_interface
SET transaction_type = 1,
--error = 'WARNING: Changing transaction_type to 1' -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
WHERE header_id = p_header_id;
select max(transaction_id)
into l_max_txn_id
from wip_move_transactions
where organization_id = p_org_id
and wip_entity_id = l_wip_entity_id
-- and to_operation_seq_num = g_prev_last_op -- l_wsm_last_op --**VJ: Deleted for removal of 9999**
--***VJ Changed for Performance Upgrade***--
and to_intraoperation_step_type = 3
--move enh --FP bug 5178168 (base bug 5168406) changed the line below
--and transaction_id = batch_id;
select FM_OPERATION_SEQ_NUM,
FM_OPERATION_CODE,
FM_INTRAOPERATION_STEP_TYPE,
FM_DEPARTMENT_ID,
TO_OPERATION_SEQ_NUM, --**VJ: Added for Removal of 9999**
TO_OPERATION_CODE,
TO_DEPARTMENT_ID,
TRANSACTION_QUANTITY,
--FP bug 5178168 (base bug 5168406) changed the line below
--BATCH_ID,
nvl(BATCH_ID, transaction_id),
--bug 5185751 get scrap account id from scrap txn only
--scrap_account_id,
source_code
into l_cmp_fm_op_seq_num,
l_cmp_fm_op_code,
l_cmp_fm_intra_op_step,
l_cmp_fm_dept_id,
l_cmp_to_op_seq_num, --**VJ: Added for Removal of 9999**
l_cmp_to_op_code,
l_cmp_to_dept_id,
l_cmp_txn_qty,
l_cmp_batch_id,
--l_wmt_scrap_acc, --bug 4090866 get scrap_account_id from WMT
x_undo_source_code
from wip_move_transactions
where transaction_id = l_max_txn_id; --***VJ Changed for Performance Upgrade***--
UPDATE WSM_LOT_BASED_JOBS
SET current_job_op_seq_num = l_cmp_fm_op_seq_num,
current_rtg_op_seq_num =
(SELECT wsm_op_seq_num
FROM WIP_OPERATIONS WO
WHERE WO.wip_entity_id = l_wip_entity_id
AND WO.operation_seq_num = l_cmp_fm_op_seq_num)
WHERE WIP_ENTITY_ID = l_wip_entity_id;
update wsm_lot_move_txn_interface
set scrap_account_id = l_wmt_scrap_acc,
error = l_error_msg
where header_id = p_header_id;
update wsm_lot_move_txn_interface
set TO_OPERATION_SEQ_NUM = l_cmp_fm_op_seq_num,
TO_OPERATION_CODE = l_cmp_fm_op_code,
TO_INTRAOPERATION_STEP_TYPE = l_cmp_fm_intra_op_step,
TRANSACTION_QUANTITY = l_cmp_txn_qty,
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
SELECT transaction_quantity,
primary_quantity,
decode(WMT.to_operation_seq_num, l_cmp_fm_op_seq_num, 1, 2),
transaction_id,
--bug 5185751 get scrap account id from scrap txn only
scrap_account_id
INTO l_wmt_scrap_qty,
l_wmt_pri_scrap_qty,
l_wmt_scrap_at_op_flag,
x_old_scrap_transaction_id,
l_wmt_scrap_acc
FROM WIP_MOVE_TRANSACTIONS WMT
WHERE organization_id = p_org_id
and wip_entity_id = l_wip_entity_id
--FP bug 5178168 (base bug 5168406) changed the line below
--and WMT.batch_id=l_max_txn_id
and nvl(WMT.batch_id, wmt.transaction_id)=l_max_txn_id
AND WMT.transaction_id <> l_max_txn_id
AND to_intraoperation_step_type = 5;
update wsm_lot_move_txn_interface
set scrap_account_id = l_wmt_scrap_acc,
error = l_error_msg
where header_id = p_header_id;
SELECT nvl(WCO.recommended, 'N')
INTO l_recommended
FROM WSM_COPY_OPERATIONS WCO,
WIP_OPERATIONS WO
WHERE WO.wip_entity_id = l_wip_entity_id
AND WO.organization_id = p_org_id
AND WO.operation_seq_num = l_cmp_fm_op_seq_num
AND WCO.wip_entity_id = WO.wip_entity_id
AND WCO.operation_sequence_id = WO.operation_sequence_id;
/* update wsm_lot_move_txn_interface
set FM_OPERATION_SEQ_NUM = NULL,
FM_OPERATION_CODE = NULL,
FM_INTRAOPERATION_STEP_TYPE = NULL
where header_id = p_header_id;*/
select PRIMARY_ITEM_ID,
COMPLETION_SUBINVENTORY,
COMPLETION_LOCATOR_ID,
LOT_NUMBER,
class_code, --bug 2484294
job_type, --bug 2484294
kanban_card_id, -- abbKanban
quantity_completed -- Fix for bug #2095267
into l_cmp_primary_item_id,
l_cmp_subinv,
l_cmp_loc_id,
l_cmp_lot_number,
l_class_code,
l_job_type,
l_kanban_card_id,
l_qty_completed -- Fix for bug #2095267
from wip_discrete_jobs
where organization_id = p_org_id
and wip_entity_id = l_wip_entity_id;
select nvl(sum(transaction_quantity),0)
into l_onhand_qty
--bug 3324825 change to mtl_onhand_quantities_detail
-- from mtl_onhand_quantities
from mtl_onhand_quantities_detail
where organization_id = p_org_id
and inventory_item_id = l_cmp_primary_item_id
and subinventory_code = l_cmp_subinv
and nvl(locator_id, -1) = nvl(l_cmp_loc_id, nvl(locator_id, -1)) --Fix for bug 1495104
and lot_number = nvl(l_cmp_lot_number, lot_number);
update wsm_lot_move_txn_interface
set FM_OPERATION_SEQ_NUM = l_cmp_to_op_seq_num,
FM_OPERATION_CODE = l_cmp_to_op_code,
FM_INTRAOPERATION_STEP_TYPE = WIP_CONSTANTS.TOMOVE,
SCRAP_QUANTITY = l_wmt_scrap_qty
where header_id = p_header_id;
** select supply_status
** into l_cur_supply_status
** from mtl_kanban_card_activity
** where kanban_card_id = l_kanban_card_id
** and kanban_activity_id =
** (select max(kanban_activity_id)
** from mtl_kanban_card_activity
** where kanban_card_id = l_kanban_card_id);
** inv_kanban_pvt.Update_Card_Supply_Status
** (X_Return_Status => l_returnStatus,
** p_Kanban_Card_Id => l_kanban_card_id,
** p_Supply_Status => inv_kanban_pvt.g_supply_status_InProcess);
select est_scrap_account, est_scrap_var_account
into p_est_scrap_account, p_est_scrap_var_account
from wip_accounting_classes
where class_code = l_class_code
and organization_id = p_org_id;
update wip_discrete_jobs wdj
set wdj.est_scrap_account = nvl(p_est_scrap_account, wdj.est_scrap_account),
wdj.est_scrap_var_account = nvl(p_est_scrap_var_account, wdj.est_scrap_var_account)
where wip_entity_id = l_wip_entity_id;
select scrap_account, est_absorption_account
into l_scrap_account, l_est_scrap_abs_account
from bom_departments
where department_id = l_cmp_to_dept_id;
UPDATE WIP_OPERATION_YIELDS WOY
SET SCRAP_ACCOUNT = nvl(l_scrap_account, WOY.SCRAP_ACCOUNT),
EST_SCRAP_ABSORB_ACCOUNT = nvl(l_est_scrap_abs_account, WOY.EST_SCRAP_ABSORB_ACCOUNT)
WHERE WIP_ENTITY_ID = l_wip_entity_id
--** VJ: Start Changes for removal of 9999 **--
-- and operation_seq_num = g_prev_last_op;
select ALLOW_BACKWARD_MOVE_FLAG
into l_allow_bkw_move
from wsm_parameters
where organization_id = p_org_id; */
select max(OPERATION_SEQ_NUM)
into l_max_qty_op_seq_num
from wip_operations
where organization_id = p_org_id
and wip_entity_id = l_wip_entity_id
and ((QUANTITY_IN_QUEUE > 0) or
(QUANTITY_RUNNING > 0) or
(QUANTITY_WAITING_TO_MOVE > 0) or
(QUANTITY_SCRAPPED > 0));
select OPERATION_SEQ_NUM,
decode(sign(QUANTITY_IN_QUEUE),1,QUANTITY_IN_QUEUE,0),
decode(sign(QUANTITY_RUNNING),1,QUANTITY_RUNNING,0),
decode(sign(QUANTITY_WAITING_TO_MOVE),1,QUANTITY_WAITING_TO_MOVE,0),
decode(sign(QUANTITY_SCRAPPED),1,QUANTITY_SCRAPPED,0)
into l_wo_op_seq_num,
l_wo_qty_in_queue,
l_wo_qty_in_running,
l_wo_qty_in_tomove,
l_wo_qty_in_scrap
from wip_operations
where organization_id = p_org_id
and wip_entity_id = l_wip_entity_id
and OPERATION_SEQ_NUM = l_max_qty_op_seq_num; --***VJ Changed for Performance Upgrade***--
SELECT max(transaction_id)
INTO l_txn_id
FROM wip_move_transactions
WHERE organization_id = p_org_id
AND wip_entity_id = l_wip_entity_id
--move enh added transaction_id = batch_id --FP bug 5178168 (base bug 5168406) changed the line below
--AND transaction_id = batch_id;
select max(transaction_id)
into l_txn_id
from wip_move_transactions
where organization_id = p_org_id
and wip_entity_id = l_wip_entity_id
and to_operation_seq_num = l_wo_op_seq_num
and to_intraoperation_step_type IN (l_wo_qty_iop_step, l_wo_qty_scrap_step)
and (fm_operation_seq_num < to_operation_seq_num OR
(fm_operation_seq_num = to_operation_seq_num AND
fm_intraoperation_step_type < to_intraoperation_step_type)
)
--move enh added transaction_id = batch_id --FP bug 5178168 (base bug 5168406) changed the line below
--AND transaction_id = batch_id;
select FM_OPERATION_SEQ_NUM,
FM_OPERATION_CODE,
FM_INTRAOPERATION_STEP_TYPE,
FM_DEPARTMENT_ID,
TO_OPERATION_SEQ_NUM,
TO_OPERATION_CODE,
TO_INTRAOPERATION_STEP_TYPE,
TO_DEPARTMENT_ID,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
--bug 5349187 reversed part of the fix for bug 5185751 by uncommenting following line since
--l_wmt_scrap_acct_id was not getting populated for undo of scrap only transaction
NVL(SCRAP_ACCOUNT_ID, -1),
source_code
into l_fm_op_seq_num,
l_fm_op_code,
l_fm_intraop_step,
l_fm_dept_id,
l_to_op_seq_num,
l_to_op_code,
l_to_intraop_step,
l_to_dept_id,
l_wmt_txn_qty,
l_wmt_pri_txn_qty,
--bug 5349187 reversed part of the fix for bug 5185751 by uncommenting following line since
--l_wmt_scrap_acct_id was not getting populated for undo of scrap only transaction
l_wmt_scrap_acct_id,
x_undo_source_code
from wip_move_transactions
where transaction_id = l_txn_id;
UPDATE WSM_LOT_BASED_JOBS
SET current_job_op_seq_num = l_fm_op_seq_num,
current_rtg_op_seq_num =
(SELECT wsm_op_seq_num
FROM WIP_OPERATIONS WO
WHERE WO.wip_entity_id = l_wip_entity_id
AND WO.operation_seq_num = l_fm_op_seq_num)
WHERE WIP_ENTITY_ID = l_wip_entity_id;
SELECT transaction_quantity,
primary_quantity,
decode(to_operation_seq_num,FM_OPERATION_SEQ_NUM, 1, 2),
transaction_id,
--bug 5185751 SCRAP_ACCOUNT_ID should be obtained from scrap txn only
scrap_account_id
INTO l_wmt_scrap_qty,
l_wmt_pri_scrap_qty,
l_wmt_scrap_at_op_flag,
x_old_scrap_transaction_id,
l_wmt_scrap_acct_id
FROM WIP_MOVE_TRANSACTIONS
--move enh 115.135 added wip_entity_id after perf check
WHERE wip_entity_id = l_wip_entity_id
--FP bug 5178168 (base bug 5168406) changed the line below
--AND batch_id=l_txn_id
AND nvl(batch_id, transaction_id) =l_txn_id
AND transaction_id<>l_txn_id;
l_msg_tokens.delete;
p_msg_text => 'After select on WIP_MOVE_TRANSACTIONS: '||
'l_wmt_scrap_qty '||l_wmt_scrap_qty||
'; l_wmt_pri_scrap_qty '||l_wmt_pri_scrap_qty||
SELECT nvl(WCO.recommended, 'N')
INTO l_recommended
FROM WSM_COPY_OPERATIONS WCO,
WIP_OPERATIONS WO
WHERE WO.wip_entity_id = l_wip_entity_id
AND WO.organization_id = p_org_id
AND WO.operation_seq_num = l_fm_op_seq_num
AND WCO.wip_entity_id = WO.wip_entity_id
AND WCO.operation_sequence_id = WO.operation_sequence_id;
update wsm_lot_move_txn_interface
set FM_OPERATION_SEQ_NUM = l_to_op_seq_num,
FM_OPERATION_CODE = l_to_op_code,
FM_INTRAOPERATION_STEP_TYPE = l_to_intraop_step,
FM_DEPARTMENT_ID = l_to_dept_id,
SCRAP_QUANTITY = l_wmt_scrap_qty,
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
l_msg_tokens.delete;
update wsm_lot_move_txn_interface
set TO_OPERATION_SEQ_NUM = l_fm_op_seq_num,
TO_OPERATION_CODE = l_fm_op_code,
TO_INTRAOPERATION_STEP_TYPE = l_fm_intraop_step,
TO_DEPARTMENT_ID = l_fm_dept_id,
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
l_msg_tokens.delete;
select nvl(scrap_account_id,-1)
into l_wlmti_scrap_acct_id
from wsm_lot_move_txn_interface
where header_id = p_header_id;
select job_type
into l_job_type
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id;
update wsm_lot_move_txn_interface
set scrap_account_id = l_wmt_scrap_acct_id,
--ERROR = 'WARNING:'||x_err_buf -- CZH.BUG2135538
error = l_error_msg -- CZH.BUG2135538
where header_id = p_header_id;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET FM_DEPARTMENT_CODE = (select department_code
from bom_departments
where department_id = l_to_dept_id)
WHERE WLMTI.header_id = p_header_id;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET TO_DEPARTMENT_CODE = (select department_code
from bom_departments
where department_id = l_fm_dept_id)
WHERE WLMTI.header_id = p_header_id;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET acct_period_id = g_acct_period_id --***VJ Changed for Performance Upgrade***--
WHERE WLMTI.header_id = p_header_id;
SELECT msi.primary_uom_code
INTO l_uom
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_primary_item_id
AND msi.organization_id = p_org_id;
SELECT wip_transactions_s.nextval INTO l_scrap_txn_id from dual;
l_msg_tokens.delete;
p_msg_text => 'B4 UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI'||
';l_converted_scrap_qty '||
UPDATE WSM_LOT_MOVE_TXN_INTERFACE WLMTI
SET primary_quantity = NVL(l_converted_txn_qty,0),
primary_scrap_quantity = NVL(l_converted_scrap_qty,0),
scrap_at_operation_flag = l_scrap_at_operation_flag,
internal_scrap_txn_id = l_scrap_txn_id
WHERE WLMTI.header_id = p_header_id;
FND_MESSAGE.SET_NAME('WSM', 'WSM_UPDATE_INVALID');
SELECT operation_seq_num
INTO l_temp -- <> 0, if moving to the last op
FROM bom_operation_sequences
WHERE operation_seq_num = p_to_op_seq_num
AND operation_sequence_id = l_end_op_seq_id;
SELECT standard_operation_id
INTO l_std_operation_id
FROM bom_standard_operations
WHERE operation_code = l_op_code
AND organization_id = p_org_id;
SELECT unique max(operation_seq_num)
INTO l_max_op_seq
FROM wip_operations
WHERE WIP_ENTITY_ID = l_wip_entity_id;
update wip_operations
set skip_flag=l_yes, -- Set skip_flag to Yes
disable_date =p_txn_date -- Added this line for bug 5367603
where organization_id = p_org_id
and wip_entity_id = l_wip_entity_id
and operation_seq_num= p_fm_op_seq_num;
select job_type
into l_job_type
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id;
select bom_reference_id,
bom_revision_date,
alternate_bom_designator
into l_bom_reference_id,
l_bom_revision_date,
l_alt_bom_desig
from wip_discrete_jobs
where organization_id = p_org_id
and wip_entity_id = l_wip_entity_id;
UPDATE WSM_LOT_BASED_JOBS wlbj
SET wlbj.on_rec_path = l_recommended
WHERE wlbj.wip_entity_id = l_wip_entity_id
AND wlbj.organization_id = p_org_id
AND wlbj.on_rec_path <> l_recommended;
select nvl(COMMON_ROUTING_SEQUENCE_ID, ROUTING_REFERENCE_ID),
PRIMARY_ITEM_ID
into l_current_rtg_seq_id,
l_current_pri_item_id
from wip_discrete_jobs
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id;
select distinct(wip_entity_id)
into l_temp
from wip_move_transactions
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and FM_OPERATION_SEQ_NUM = p_to_op_seq_num
and nvl(FM_OPERATION_CODE, '&&!!@@') = nvl(p_to_op_code, '&&!!@@')
--NSO modification by abedajna
and FM_INTRAOPERATION_STEP_TYPE = p_to_intraop_step_type
and TO_OPERATION_SEQ_NUM = p_from_op_seq_num
and nvl(TO_OPERATION_CODE, '&&!!@@') = nvl(p_from_op_code, '&&!!@@')
--NSO modification by abedajna
and TO_INTRAOPERATION_STEP_TYPE = p_from_intraop_step_type;
select PRIMARY_QUANTITY, -- CZH: BUG2154720
transaction_date -- ADD: BUG2804111 use txn time
into l_orig_mv_txn_qty,
l_wmt_time -- ADD: BUG2804111 use txn time
from wip_move_transactions
where transaction_id = ( select max(wmt1.transaction_id)
from wip_move_transactions wmt1
where wmt1.organization_id = p_org_id
and wmt1.wip_entity_id = p_wip_entity_id
and wmt1.wsm_undo_txn_id IS NULL
--move enh --FP bug 5178168 (base bug 5168406) changed the line below
--and wmt1.transaction_id = wmt1.batch_id);
select max(wsmt.transaction_date)
into l_wlt_time
from wsm_split_merge_transactions wsmt,
wsm_sm_starting_jobs wssj
where wsmt.organization_id = p_org_id
and wsmt.transaction_id = wssj.transaction_id
and wssj.wip_entity_id = p_wip_entity_id;
l_msg_tokens.delete;
select max(nvl(batch_id, transaction_id))
-- into x_undone_txn_id
into x_undone_batch_id
from wip_move_transactions
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and wsm_undo_txn_id IS NULL
--FP bug 5178168 (base bug 5168406) changed the line below
--and batch_id < p_undo_txn_id;
l_msg_tokens.delete;
update wip_move_transactions
--FP bug 5178168 (base bug 5168406) changed the line below
--set wsm_undo_txn_id = decode(batch_id,
set wsm_undo_txn_id = decode(nvl(batch_id, transaction_id),
p_undo_txn_id, x_undone_batch_id,
p_undo_txn_id)
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
-- and transaction_id in (p_undo_txn_id, x_undone_txn_id);
/* update wip_move_transactions
set wsm_undo_txn_id = x_undone_batch_id
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
-- and transaction_id in (p_undo_txn_id, x_undone_txn_id);
update wip_move_transactions
set wsm_undo_txn_id = p_undo_txn_id
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
-- and transaction_id in (p_undo_txn_id, x_undone_txn_id);
update wip_operations
set skip_flag=l_no,
disable_date = null -- Added this line for bug 5367603
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num=p_to_op_seq_num
and skip_flag=l_yes;
INSERT INTO WSM_INTERFACE_ERRORS (
HEADER_ID,
TRANSACTION_ID,
MESSAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
MESSAGE_TYPE )
VALUES (p_header_id
, p_transaction_id
, p_error_message
, SYSDATE
, g_user_id
, SYSDATE
, g_user_id
, g_login_id
, g_request_id
, g_program_id
, g_program_application_id
, 1);
PROCEDURE update_txn_status(x_error_code OUT NOCOPY NUMBER
, x_error_msg OUT NOCOPY VARCHAR2
, p_group_id IN NUMBER
, p_wmti_group_id IN NUMBER)
IS
l_stmt_num NUMBER;
UPDATE wsm_lot_move_txn_interface WLMTI
SET status = 4
WHERE WLMTI.internal_group_id=p_wmti_group_id
AND WLMTI.group_id=p_group_id;
x_error_msg := substrb('WSMPLMTI.update_txn_status' ||'(stmt_num='||l_stmt_num||') : '||sqlerrm, 1,4000);
END update_txn_status;
UPDATE wsm_lot_move_txn_interface
SET status = 3,
ERROR = g_fnd_generic_err_msg
WHERE header_id = p_header_id;
Procedure update_int_grp_id(x_error_code OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2,
p_header_id IN NUMBER,
p_wmti_group_id IN NUMBER)
IS
BEGIN
UPDATE wsm_lot_move_txn_interface
SET internal_group_id = p_wmti_group_id
WHERE header_id = p_header_id;
SELECT resource_code
INTO l_resource_code
FROM BOM_RESOURCES
WHERE resource_id = p_resource_id
AND organization_id = p_organization_id;
select decode(p_resource_serial_number,
NULL, msik.concatenated_segments,
msik.concatenated_segments||':'||p_resource_serial_number)
into l_resource_instance
from bom_resource_equipments bre, mtl_system_items_kfv msik
where bre.inventory_item_id = msik.inventory_item_id
and bre.organization_id = msik.organization_id
and bre.resource_id = p_resource_id
and bre.instance_id = p_resource_instance_id
and bre.organization_id = p_organization_id;
SELECT ML.meaning
INTO x_reason_code
FROM MFG_LOOKUPS ML
WHERE ML.lookup_type = 'BOM_SCRAP_CODES'
AND ML.lookup_code = p_reason_code_num;
SELECT ML.meaning
INTO x_reason_code
FROM MFG_LOOKUPS ML
WHERE ML.lookup_type = 'BOM_BONUS_CODES'
AND ML.lookup_code = p_reason_code_num;
l_inserted_wmti NUMBER := 0;
SELECT rowid,
header_id,
transaction_id,
transaction_quantity,
transaction_date,
transaction_uom,
transaction_type,
fm_operation_seq_num,
fm_operation_code,
fm_intraoperation_step_type,
to_operation_seq_num,
to_operation_code,
to_intraoperation_step_type,
to_department_id,
primary_uom,
wip_entity_id,
wip_entity_name,
organization_id,
nvl(jump_flag, 'N'), --bug 5469479 added nvl
scrap_at_operation_flag,
scrap_quantity,
serial_start_flag -- ST : Serial Support Project --
FROM wsm_lot_move_txn_interface
WHERE group_id = p_group_id
AND status = g_running -- WIP_CONSTANTS.RUNNING --Added condition to fix bug #1815584
ORDER BY transaction_date, organization_id, wip_entity_id, processing_order;
l_msg_tokens.delete;
l_param_tbl.delete;
l_msg_tokens.delete;
p_msg_text => 'B4 UPDATE wsm_lot_move_txn_interface wlmti '||
';g_user_id '||
UPDATE wsm_lot_move_txn_interface wlmti
/* LOTATTR: Changed the following so that transaction_id is
updated ONLY if it wasn't populated by the user */
SET transaction_id=nvl(transaction_id,wip_transactions_s.nextval),
wlmti.error = NULL,
wlmti.status = WIP_CONSTANTS.RUNNING,
wlmti.last_update_date = SYSDATE,
wlmti.last_updated_by = decode(nvl(g_user_id, -1),
-1, wlmti.last_updated_by,
g_user_id),
wlmti.last_update_login = decode(nvl(g_login_id, -1),
-1, wlmti.last_update_login,
g_login_id),
wlmti.request_id = g_request_id,
wlmti.program_id = g_program_id,
wlmti.program_application_id = g_program_application_id
WHERE group_id = p_group_id
AND wlmti.status = WIP_CONSTANTS.PENDING
AND wlmti.transaction_date <= SYSDATE+1;
FND_MESSAGE.SET_NAME('WSM', 'WSM_UPDATE_INVALID');
fnd_file.put_line(fnd_file.log, 'updated status to running');
l_inserted_wmti := 0;
SELECT wip_transactions_s.nextval
INTO l_wmti_group_id
FROM dual;
SELECT 1
INTO l_job_exists
FROM wip_move_txn_interface
WHERE group_id=l_wmti_group_id
AND wip_entity_id=l_wip_entity_id
AND process_status = g_running;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT charge_jump_from_queue
INTO l_charge_jump_from_queue
FROM wsm_parameters
WHERE organization_id = l_organization_id;
fnd_file.put_line(fnd_file.log, 'Before Calling WSM_LotAttr_PVT.create_update_lotattr');
WSM_LotAttr_PVT.create_update_lotattr(
x_err_code => x_err_code,
x_err_msg => x_error_msg,
p_wip_entity_id => l_wip_entity_id,
p_org_id => l_organization_id,
p_intf_txn_id => l_header_id, /*l_transaction_id, Bug 5372863. Should pass header id for lot attributes package to upd lot attributes. */
p_intf_src_code => 'WSM');
fnd_file.put_line(fnd_file.log, 'No Error reported from WSM_LotAttr_PVT.create_update_lotattr');
SELECT TRANSACTION_TYPE,
wip_entity_id,
transaction_quantity,
fm_operation_seq_num,
fm_intraoperation_step_type,
to_intraoperation_step_type,
organization_id,
nvl(primary_quantity, 0),
nvl(scrap_quantity, 0),
nvl(primary_scrap_quantity, 0),
scrap_at_operation_flag,
fm_operation_code,
reason_id,
transaction_date,
reference,
internal_scrap_txn_id,
to_operation_seq_num,
wip_entity_name
INTO l_transaction_type,
l_wip_entity_id,
l_txn_qty,
l_fm_op_seq_num,
l_fm_intraoperation_step_type,
l_to_intraoperation_step_type,
l_organization_id,
l_primary_quantity,
l_scrap_qty,
l_converted_scrap_qty,
l_scrap_at_operation_flag,
l_fm_operation_code,
l_reason_id,
l_transaction_date,
l_transaction_reference,
l_scrap_txn_id,
l_job_to_op_seq_num,
l_wip_entity_name
FROM wsm_lot_move_txn_interface
WHERE rowid=l_rowid;
l_msg_tokens.delete;
p_msg_text => 'SELECT from WLMTI after custom_validations '||
';l_transaction_type '||
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT max_acceptable_scrap_qty
INTO l_max_acceptable_scrap_qty
FROM BOM_STD_OP_SCRAP_CODES BSOSC, BOM_STANDARD_OPERATIONS BSO
WHERE BSO.operation_code = l_fm_operation_code
AND BSO.organization_id = l_organization_id
AND BSOSC.standard_operation_id = BSO.standard_operation_id
AND BSOSC.scrap_code = p_scrap_codes_tbls(l_header_id)(i);
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;
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;
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;
SELECT nvl(common_routing_sequence_id, routing_reference_id),
TO_CHAR(nvl(routing_revision_date, SYSDATE), 'YYYY/MM/DD HH24:MI:SS'), /* CHG: BUG2380517 add SS */
/* ADD: CZH.I_OED-1 */
primary_item_id
INTO l_routing_seq_id,
l_rtg_revision_date, /* ADD: CZH.I_OED-1 */
l_primary_item_id
FROM wip_discrete_jobs
WHERE wip_entity_id = l_wip_entity_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_update_flag BOOLEAN:=FALSE;
SELECT completion_subinventory
INTO l_subinventory
FROM wip_discrete_jobs
WHERE wip_entity_id = l_wip_entity_id;
l_msg_tokens.delete;
l_new_name := WSMPOPRN.update_job_name(l_wip_entity_id,
l_subinventory,
l_organization_id,
l_transaction_type,
l_update_flag,
l_dup_name,
x_error_code,
x_error_msg);
l_msg_tokens.delete;
UPDATE wsm_lot_move_txn_interface
SET new_wip_entity_name=l_new_name
where rowid=l_rowid;
fnd_file.put_line(fnd_file.log, 'update_job_name returned success');
l_msg_tokens.delete;
l_update_flag BOOLEAN:=FALSE;
SELECT completion_subinventory
INTO l_subinventory
FROM wip_discrete_jobs
WHERE wip_entity_id = l_wip_entity_id;
l_msg_tokens.delete;
l_new_name := WSMPOPRN.update_job_name(l_wip_entity_id,
l_subinventory,
l_organization_id,
l_temp_txn_type,
l_update_flag,
l_dup_name,
x_error_code,
x_error_msg);
l_msg_tokens.delete;
UPDATE wip_entities
SET wip_entity_name = l_new_name
WHERE wip_entity_id = l_wip_entity_id;
UPDATE wsm_lot_move_txn_interface
SET wip_entity_name=l_new_name
where rowid=l_rowid;
fnd_file.put_line(fnd_file.log, 'update_job_name returned success');
SELECT NVL(operation_sequence_id, -2)
INTO l_to_op_seq_id
FROM bom_operation_sequences
WHERE operation_seq_num = l_to_op_seq_num
AND routing_sequence_id = l_routing_seq_id
/* BC: CZH.I_OED-1 compare against rtg_rev_date */
AND nvl(disable_date, TO_DATE(l_rtg_revision_date, 'YYYY/MM/DD HH24:MI:SS')+1)
>= TO_DATE(l_rtg_revision_date, 'YYYY/MM/DD HH24:MI:SS') /* CHG: BUG2380517 add SS, > to >= */
AND nvl(effectivity_date, TO_DATE(l_rtg_revision_date, 'YYYY/MM/DD HH24:MI:SS')) <= TO_DATE(l_rtg_revision_date, 'YYYY/MM/DD HH24:MI:SS'); /* CHG: BUG2380517 add SS */
SELECT nvl(operation_sequence_id, -2)
INTO l_to_op_seq_id
FROM WSM_COPY_OPERATIONS
WHERE wip_entity_id=l_wip_entity_id
AND operation_seq_num = l_to_op_seq_num;
select NVL(operation_sequence_id, -1) -- CZH.bug2393850 in op outside routing
into l_fm_op_seq_id
from wip_operations
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_fm_op_seq_num;
SELECT unique max(operation_seq_num)
INTO l_max_op_seq -- will be the newly added row in WO
FROM wip_operations
WHERE WIP_ENTITY_ID = l_wip_entity_id;
* insert record into WMTI, will call WIP user_exit to process it *
******************************************************************/
--move enh To determine the WIP move txns we need to insert we need the data in the variables we pass into LBMIB.
l_count := 0;
l_msg_tokens.delete;
p_msg_text => 'Inserting WMTI row for scrap qty',
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 wip_move_txn_interface(
TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
GROUP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
PROCESS_PHASE,
PROCESS_STATUS ,
TRANSACTION_TYPE,
ORGANIZATION_ID,
ORGANIZATION_CODE,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ENTITY_TYPE,
PRIMARY_ITEM_ID,
LINE_ID,
LINE_CODE,
REPETITIVE_SCHEDULE_ID,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
FM_OPERATION_SEQ_NUM,
FM_OPERATION_CODE,
FM_DEPARTMENT_ID,
FM_DEPARTMENT_CODE,
FM_INTRAOPERATION_STEP_TYPE,
TO_OPERATION_SEQ_NUM,
TO_OPERATION_CODE,
TO_DEPARTMENT_ID,
TO_DEPARTMENT_CODE,
TO_INTRAOPERATION_STEP_TYPE,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM ,
SCRAP_ACCOUNT_ID,
REASON_ID,
REASON_NAME,
REFERENCE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
QA_COLLECTION_ID,
KANBAN_CARD_ID,
OVERCOMPLETION_TRANSACTION_QTY,
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID,
PROCESSING_ORDER,
BATCH_ID,
EMPLOYEE_ID)
(SELECT
decode(l_transaction_quantity,
0, l_transaction_id,
--bug 3615826
-- wip_transactions_s.nextval),
internal_scrap_txn_id),
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
decode(SOURCE_CODE,
--bug 5446252 match transaction_id with group_id for WIP single move api
--'move out oa page', internal_scrap_txn_id,
'move out oa page', decode(l_transaction_quantity,
0, l_transaction_id,
internal_scrap_txn_id),
--'undo oa page', internal_scrap_txn_id,
'undo oa page', decode(l_transaction_quantity,
0, l_transaction_id,
internal_scrap_txn_id),
l_wmti_group_id), -- GROUP_ID
SOURCE_CODE,
SOURCE_LINE_ID,
decode(SOURCE_CODE,
'move out oa page', WIP_CONSTANTS.MOVE_PROC,
'undo oa page', WIP_CONSTANTS.MOVE_PROC,
g_move_val), --1 Process_Phase, --2721366: Replaced 1 with constant
g_running, --1 Process_Status , --2721366: Replaced 1 with constant
g_move_txn,
ORGANIZATION_ID,
ORGANIZATION_CODE,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ENTITY_TYPE,
PRIMARY_ITEM_ID,
LINE_ID,
LINE_CODE,
REPETITIVE_SCHEDULE_ID,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
decode(l_op_flag,
1, fm_operation_seq_num,
2, to_operation_seq_num),
decode(l_op_flag,
1, fm_operation_code,
2, to_operation_code),
decode(l_op_flag,
1, fm_department_id,
2, to_department_id),
decode(l_op_flag,
1, fm_department_code,
2, to_department_code),
decode(TRANSACTION_TYPE,
4, g_scrap,
g_ret_txn, g_scrap,
FM_INTRAOPERATION_STEP_TYPE),
decode(TRANSACTION_TYPE,
g_ret_txn, TO_OPERATION_SEQ_NUM,
4, TO_OPERATION_SEQ_NUM,
decode(SCRAP_AT_OPERATION_FLAG,
1, fm_operation_seq_num,
l_max_op_seq)),
decode(TRANSACTION_TYPE,
g_ret_txn, TO_OPERATION_CODE,
4, TO_OPERATION_CODE,
decode(SCRAP_AT_OPERATION_FLAG,
1, fm_OPERATION_CODE,
to_OPERATION_CODE)),
decode(TRANSACTION_TYPE,
g_ret_txn, TO_DEPARTMENT_ID,
4, TO_DEPARTMENT_ID,
decode(SCRAP_AT_OPERATION_FLAG,
1, fm_DEPARTMENT_ID,
to_DEPARTMENT_ID)),
decode(TRANSACTION_TYPE,
g_ret_txn, TO_DEPARTMENT_CODE,
4, TO_DEPARTMENT_CODE,
decode(SCRAP_AT_OPERATION_FLAG,
1, fm_DEPARTMENT_CODE,
TO_DEPARTMENT_CODE)),
decode(TRANSACTION_TYPE,
g_move_txn, g_scrap,
g_comp_txn, g_scrap,
TO_INTRAOPERATION_STEP_TYPE),
SCRAP_QUANTITY,
TRANSACTION_UOM,
PRIMARY_SCRAP_QUANTITY,
PRIMARY_UOM ,
SCRAP_ACCOUNT_ID,
REASON_ID,
REASON_NAME,
REFERENCE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
QA_COLLECTION_ID,
--move enh?
KANBAN_CARD_ID, --move enh?
OVERCOMPLETION_TRANSACTION_QTY,
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID,
--The decode below is not required and is incorrect, but decided not to remove since code is stable
decode(TRANSACTION_TYPE,
g_comp_txn, g_ret_txn,
1), /*processing_order*/
TRANSACTION_ID,
p_employee_id
FROM wsm_lot_move_txn_interface
WHERE header_id = l_header_id);
Inserted Scrap Txn');
SELECT fm_operation_seq_num, to_operation_seq_num
INTO l_fm_op_seq_num, l_job_to_op_seq_num
FROM WIP_MOVE_TXN_INTERFACE
WHERE transaction_id = l_transaction_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'Inserting WMTI row for move qty',
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 wip_move_txn_interface(
TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
GROUP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
PROCESS_PHASE,
PROCESS_STATUS ,
TRANSACTION_TYPE,
ORGANIZATION_ID,
ORGANIZATION_CODE,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ENTITY_TYPE,
PRIMARY_ITEM_ID,
LINE_ID,
LINE_CODE,
REPETITIVE_SCHEDULE_ID,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
FM_OPERATION_SEQ_NUM,
FM_OPERATION_CODE,
FM_DEPARTMENT_ID,
FM_DEPARTMENT_CODE,
FM_INTRAOPERATION_STEP_TYPE,
TO_OPERATION_SEQ_NUM,
TO_OPERATION_CODE,
TO_DEPARTMENT_ID,
TO_DEPARTMENT_CODE,
TO_INTRAOPERATION_STEP_TYPE,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM ,
SCRAP_ACCOUNT_ID,
REASON_ID,
REASON_NAME,
REFERENCE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
QA_COLLECTION_ID,
KANBAN_CARD_ID,
OVERCOMPLETION_TRANSACTION_QTY,
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID,
PROCESSING_ORDER,
BATCH_ID,
EMPLOYEE_ID)
(SELECT
TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
decode(SOURCE_CODE,
'move in oa page', TRANSACTION_ID,
'move out oa page', TRANSACTION_ID,
'move to next op oa page', TRANSACTION_ID,
'jump oa page', TRANSACTION_ID,
'undo oa page', TRANSACTION_ID,
l_wmti_group_id), -- GROUP_ID
SOURCE_CODE,
SOURCE_LINE_ID,
decode(SOURCE_CODE,
'move in oa page', WIP_CONSTANTS.MOVE_PROC,
'move out oa page', WIP_CONSTANTS.MOVE_PROC,
'move to next op oa page', WIP_CONSTANTS.MOVE_PROC,
'jump oa page', WIP_CONSTANTS.MOVE_PROC,
'undo oa page', WIP_CONSTANTS.MOVE_PROC,
g_move_val), --1 Process_Phase, --2721366: Replaced 1 with constant
g_running, --1 Process_Status , --2721366: Replaced 1 with constant
decode(TRANSACTION_TYPE,4, 1, TRANSACTION_TYPE),
ORGANIZATION_ID,
ORGANIZATION_CODE,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ENTITY_TYPE,
PRIMARY_ITEM_ID,
LINE_ID,
LINE_CODE,
REPETITIVE_SCHEDULE_ID,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
FM_OPERATION_SEQ_NUM,
FM_OPERATION_CODE,
FM_DEPARTMENT_ID,
FM_DEPARTMENT_CODE,
FM_INTRAOPERATION_STEP_TYPE,
decode(TRANSACTION_TYPE, 1, l_max_op_seq, 2, l_max_op_seq, TO_OPERATION_SEQ_NUM),
TO_OPERATION_CODE,
TO_DEPARTMENT_ID,
TO_DEPARTMENT_CODE,
TO_INTRAOPERATION_STEP_TYPE,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
PRIMARY_UOM ,
--bug 5092117 added decode statements
decode(TO_INTRAOPERATION_STEP_TYPE,
WIP_CONSTANTS.SCRAP, SCRAP_ACCOUNT_ID,
decode(FM_INTRAOPERATION_STEP_TYPE,
WIP_CONSTANTS.SCRAP, SCRAP_ACCOUNT_ID,
NULL)
),
REASON_ID,
REASON_NAME,
REFERENCE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
QA_COLLECTION_ID,
KANBAN_CARD_ID,
OVERCOMPLETION_TRANSACTION_QTY,
OVERCOMPLETION_PRIMARY_QTY,
OVERCOMPLETION_TRANSACTION_ID,
decode(TRANSACTION_TYPE, 3, 1, 2), /*processing_order*/
TRANSACTION_ID,
p_employee_id
FROM wsm_lot_move_txn_interface
WHERE header_id = l_header_id
);
fnd_file.put_line(fnd_file.log, 'Inserted '||l_count||' row(s) in WMTI, group_id '|| l_wmti_group_id||', txn_id '
||l_transaction_id||' scrap txn id '||l_scrap_txn_id);
SELECT fm_operation_seq_num, to_operation_seq_num
INTO l_fm_op_seq_num, l_job_to_op_seq_num
FROM WIP_MOVE_TXN_INTERFACE
WHERE transaction_id = l_transaction_id;
fnd_file.put_line(fnd_file.log, 'WARNING: Could not insert into WMTI');
l_inserted_wmti := l_inserted_wmti + l_count;
select net_quantity,primary_item_id
into l_net_quantity,l_primary_item_id
from wip_discrete_jobs
where wip_entity_id=l_wip_entity_id
and organization_id=l_organization_id;
WSMPLBMI.update_costed_qty_compl(
p_transaction_type => l_transaction_type
, p_job_fm_op_seq_num => l_fm_op_seq_num
, p_job_to_op_seq_num => l_job_to_op_seq_num
, p_wip_entity_id => l_wip_entity_id
, p_fm_intraoperation_step_type => l_fm_intraoperation_step_type
, p_to_intraoperation_step_type => l_to_intraoperation_step_type
, p_primary_move_qty => l_primary_quantity
, p_primary_scrap_qty => l_converted_scrap_qty
, p_scrap_at_op => l_scrap_at_operation_flag
);
SELECT nvl(include_component_yield, 1)
INTO l_wip_include_comp_yield
FROM WIP_PARAMETERS
WHERE organization_id = l_organization_id;
UPDATE wip_requirement_operations wro
SET QUANTITY_RELIEVED = NVL(wro.QUANTITY_RELIEVED, 0) +
decode(l_converted_scrap_qty,
0, decode(wro.basis_type, 2, 1, l_txn_qty),
decode(wro.basis_type, 2, 1, l_converted_scrap_qty)) * decode(l_wip_include_comp_yield,
2, wro.quantity_per_assembly,
(wro.quantity_per_assembly / NVL(wro.component_yield_factor,1)))
WHERE wro.wip_entity_id = l_wip_entity_id
AND wro.organization_id = l_organization_id
AND wro.operation_seq_num <= l_wro_op_seq_num
-- since scrap can be done only at curr op
AND wro.quantity_per_assembly <> 0
AND wro.wip_supply_type <> 6
AND wro.wip_supply_type <> 4
AND wro.wip_supply_type <> 5
AND NOT EXISTS
(SELECT 1
FROM wip_operations wo
WHERE wo.organization_id = wro.organization_id
AND wo.wip_entity_id = wro.wip_entity_id
AND wo.operation_seq_num = wro.operation_seq_num
AND wo.count_point_type = 3);
Updated '||SQL%ROWCOUNT||' rows in WRO');
update_int_grp_id(x_error_code,
x_error_msg,
l_header_id,
l_transaction_id);
update_int_grp_id(x_error_code,
x_error_msg,
l_header_id,
l_wmti_group_id);
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'B4 calling WSM_Serial_support_PVT.Insert_MOVE_attr',
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
);
WSM_Serial_support_PVT.Insert_MOVE_attr ( p_group_id => null ,
p_move_txn_id => l_transaction_id ,
p_scrap_txn_id => l_scrap_txn_id ,
x_return_status => l_return_status ,
x_error_count => l_error_msg ,
x_error_msg => l_error_count
);
fnd_file.put_line(fnd_file.log, 'WSM_Serial_support_PVT.Insert_attr_WSTI returned Success');
l_msg_tokens.delete;
p_msg_text => 'WSM_Serial_support_PVT.Insert_MOVE_attr returned successfully',
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 => 'WSM_Serial_support_PVT.Insert_MOVE_attr returned error',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
l_error_msg := 'WSM_Serial_support_PVT.Insert_attr_WSTI returned failure';
fnd_file.put_line(fnd_file.log, 'WSM_Serial_support_PVT.Insert_attr_WSTI returned failure');
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT mtl_material_transactions_s.nextval
INTO l_cpl_txn_id
FROM dual;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE (
SELECT quantity_completed
FROM WIP_OPERATIONS
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_fm_op_seq_num
AND skip_flag = 1
)
SET quantity_completed = 0;
l_msg_tokens.delete;
p_msg_text => 'Begin inserting MES data',
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_op_secondary_quantities
set MOVE_OUT_QUANTITY = p_sec_move_out_qty_tbls(l_header_id)(i),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_fm_op_seq_num
and uom_code = p_sec_uom_code_tbls(l_header_id)(i);
update wsm_job_secondary_quantities
set CURRENT_QUANTITY = p_sec_move_out_qty_tbls(l_header_id)(i),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
where wip_entity_id = l_wip_entity_id
and uom_code = p_sec_uom_code_tbls(l_header_id)(i);
l_msg_tokens.delete;
p_msg_text => 'Updated secondary quantities successfully',
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_op_reason_codes
set QUANTITY = p_scrap_code_qty_tbls(l_header_id)(i),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATED_LOGIN = g_login_id
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_fm_op_seq_num
and CODE_TYPE = 1
and REASON_CODE = p_scrap_codes_tbls(l_header_id)(i);
l_msg_tokens.delete;
p_msg_text => 'Updated scrap codes successfully',
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;
update wsm_op_reason_codes
set QUANTITY = p_bonus_code_qty_tbls(l_header_id)(i),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATED_LOGIN = g_login_id
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_fm_op_seq_num
and CODE_TYPE = 2
and REASON_CODE = p_bonus_codes_tbls(l_header_id)(i);
l_msg_tokens.delete;
p_msg_text => 'Updated bonus codes successfully',
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;
insert into WIP_RESOURCE_ACTUAL_TIMES values p_jobop_resource_usages_tbls(l_header_id)(i);
l_msg_tokens.delete;
insert into WIP_RESOURCE_ACTUAL_TIMES values p_jobop_resource_usages_tbls(l_header_id)(i);
select resource_id
from wip_operation_resources wor
where wor.wip_entity_id = l_wip_entity_id
and wor.operation_seq_num = l_fm_op_seq_num;
UPDATE WIP_OPERATION_RESOURCES
SET actual_start_date =
(SELECT min(start_date)
FROM WIP_RESOURCE_ACTUAL_TIMES wrat
WHERE wrat.wip_entity_id = l_wip_entity_id
AND wrat.operation_seq_num = l_fm_op_seq_num
AND wrat.resource_id = l_wor_resource_id(i)),
projected_completion_date =
(SELECT max(projected_completion_date)
FROM WIP_RESOURCE_ACTUAL_TIMES wrat
WHERE wrat.wip_entity_id = l_wip_entity_id
AND wrat.operation_seq_num = l_fm_op_seq_num
AND wrat.resource_id = l_wor_resource_id(i)),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_fm_op_seq_num
AND resource_id = l_wor_resource_id(i);
l_msg_tokens.delete;
p_msg_text => 'Updated resource usage successfully',
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 actual_start_date = p_operation_start_date,
projected_completion_date = p_expected_completion_date,
employee_id = p_employee_id,
wsm_bonus_quantity = p_bonus_quantity,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_fm_op_seq_num;
l_msg_tokens.delete;
p_msg_text => 'Begin inserting resource usage'
||'; l_wip_entity_id: '
DELETE FROM WIP_RESOURCE_ACTUAL_TIMES
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_fm_op_seq_num;
l_msg_tokens.delete;
insert into WIP_RESOURCE_ACTUAL_TIMES values p_jobop_resource_usages_tbls(l_header_id)(i);
l_msg_tokens.delete;
select resource_id
from wip_operation_resources wor
where wor.wip_entity_id = l_wip_entity_id
and wor.operation_seq_num = l_fm_op_seq_num;
UPDATE WIP_OPERATION_RESOURCES
SET actual_start_date =
(SELECT min(start_date)
FROM WIP_RESOURCE_ACTUAL_TIMES wrat
WHERE wrat.wip_entity_id = l_wip_entity_id
AND wrat.operation_seq_num = l_fm_op_seq_num
AND wrat.resource_id = rec.resource_id),
actual_completion_date =
(SELECT max(end_date)
FROM WIP_RESOURCE_ACTUAL_TIMES wrat
WHERE wrat.wip_entity_id = l_wip_entity_id
AND wrat.operation_seq_num = l_fm_op_seq_num
AND wrat.resource_id = rec.resource_id),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_fm_op_seq_num
AND resource_id = rec.resource_id;
l_msg_tokens.delete;
p_msg_text => 'Updated resource usage successfully',
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 actual_start_date = p_operation_start_date,
actual_completion_date = p_operation_completion_date,
employee_id = p_employee_id,
wsm_bonus_quantity = p_bonus_quantity,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_fm_op_seq_num;
UPDATE WSM_OP_SECONDARY_QUANTITIES
SET move_in_quantity = p_sec_move_out_qty_tbls(l_header_id)(i)
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_job_to_op_seq_num
AND uom_code = p_sec_uom_code_tbls(l_header_id)(i);
UPDATE WIP_DISCRETE_JOBS wdj
SET actual_start_date = p_operation_start_date,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = l_wip_entity_id
and not exists (select 1 from wip_move_transactions wmt
where wmt.wip_entity_id = wdj.wip_entity_id
and wmt.organization_id = wdj.organization_id
and wmt.transaction_id <> l_transaction_id
and wmt.wsm_undo_txn_id is NULL);
update wsm_op_secondary_quantities
set MOVE_OUT_QUANTITY = NULL,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_job_to_op_seq_num;
UPDATE WSM_JOB_SECONDARY_QUANTITIES WJSQ
SET WJSQ.CURRENT_QUANTITY =
(SELECT MOVE_IN_QUANTITY
FROM WSM_OP_SECONDARY_QUANTITIES WOSC
WHERE WJSQ.UOM_CODE = WOSC.UOM_CODE
AND WJSQ.wip_entity_id = WOSC.wip_entity_id
AND WOSC.wip_entity_id = l_wip_entity_id
AND WOSC.operation_seq_num = l_job_to_op_seq_num),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE WJSQ.wip_entity_id = l_wip_entity_id;
update wsm_op_reason_codes
set QUANTITY = NULL,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATED_LOGIN = g_login_id
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_job_to_op_seq_num;
DELETE FROM WIP_RESOURCE_ACTUAL_TIMES
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_job_to_op_seq_num;
UPDATE WIP_OPERATIONS
SET actual_start_date = null,
actual_completion_date = null,
employee_id = null,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_job_to_op_seq_num;
UPDATE WIP_OPERATION_RESOURCES
SET actual_start_date = null,
actual_completion_date = null
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_job_to_op_seq_num;
UPDATE WIP_OPERATIONS
SET actual_completion_date = null
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_job_to_op_seq_num;
UPDATE WIP_RESOURCE_ACTUAL_TIMES
SET end_date = NULL
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_job_to_op_seq_num;
UPDATE WIP_OPERATION_RESOURCES
SET actual_completion_date = null
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_job_to_op_seq_num;
DELETE FROM WIP_RESOURCE_ACTUAL_TIMES
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_job_to_op_seq_num
and projected_completion_date IS NULL;
DELETE FROM WIP_RESOURCE_ACTUAL_TIMES
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_job_to_op_seq_num;
UPDATE WIP_OPERATIONS
SET actual_start_date = null,
projected_completion_date = null,
employee_id = null,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_fm_op_seq_num;
UPDATE WIP_OPERATION_RESOURCES
SET actual_start_date = null
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_job_to_op_seq_num;
UPDATE WSM_LOT_MOVE_TXN_INTERFACE
SET status = 4,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE header_id = l_header_id;
l_wltx_transactions_rec.TRANSACTION_TYPE_ID := WSMPCNST.UPDATE_QUANTITY;
select wsm_split_merge_transactions_s.nextval
into l_wltx_transactions_rec.TRANSACTION_ID
from dual;
l_wltx_resulting_job_tbl(0).SPLIT_HAS_UPDATE_ASSY := 0;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT start_quantity
INTO l_start_qty
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id = l_wip_entity_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE WIP_OPERATIONS
SET WSM_UPDATE_QUANTITY_TXN_ID = l_wltx_transactions_rec.TRANSACTION_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_fm_op_seq_num;
UPDATE WIP_DISCRETE_JOBS
SET STATUS_TYPE = WIP_CONSTANTS.HOLD,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = l_wip_entity_id;
WSM_Serial_support_PVT.Update_attr_move( p_group_id => NULL ,
p_internal_group_id => NULL ,
p_move_txn_id => l_move_txn_id ,
p_scrap_txn_id => l_scrap_id ,
p_organization_id => null ,
x_return_status => l_return_status ,
x_error_count => l_error_count ,
x_error_msg => l_error_msg
);
fnd_file.put_line(fnd_file.log, 'WSM_Serial_support_PVT.Update_attr_move returned Success');
l_error_msg := 'WSM_Serial_support_PVT.Update_attr_move returned failure';
fnd_file.put_line(fnd_file.log, 'WSM_Serial_support_PVT.Update_attr_move returned failure');
IF (l_inserted_wmti=0) THEN
IF (g_mrp_debug='Y') THEN
fnd_file.put_line(fnd_file.log, 'No txns inserted in WMTI');
fnd_file.put_line(fnd_file.log, 'Inserted '||l_inserted_wmti||' row(s) in WMTI');
l_msg_tokens.delete;
p_msg_text => 'B4 calling WSM_Serial_support_PVT.Insert_MOVE_attr',
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
);
WSM_Serial_support_PVT.Insert_MOVE_attr ( p_group_id => l_wmti_group_id ,
x_return_status => l_return_status ,
x_error_count => l_error_msg ,
x_error_msg => l_error_count
);
fnd_file.put_line(fnd_file.log, 'WSM_Serial_support_PVT.Insert_attr_WSTI returned Success');
l_msg_tokens.delete;
p_msg_text => 'WSM_Serial_support_PVT.Insert_MOVE_attr returned successfully',
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 => 'WSM_Serial_support_PVT.Insert_MOVE_attr returned error',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
l_error_msg := 'WSM_Serial_support_PVT.Insert_attr_WSTI returned failure';
fnd_file.put_line(fnd_file.log, 'WSM_Serial_support_PVT.Insert_attr_WSTI returned failure');
l_msg_tokens.delete;
SELECT WRO.wip_entity_id,
WRO.operation_seq_num,
WRO.segment1,
WRO.INVENTORY_ITEM_ID,
WRO.REQUIRED_QUANTITY,
WRO.QUANTITY_ISSUED,
WRO.QUANTITY_PER_ASSEMBLY,
WRO.QUANTITY_RELIEVED,
WRO.COMPONENT_YIELD_FACTOR,
WRO.basis_type
FROM wsm_lot_move_txn_interface WLMTI,
WIP_REQUIREMENT_OPERATIONS WRO
WHERE WLMTI.group_id = p_group_id
AND WLMTI.wip_entity_id = 1439883
AND WLMTI.wip_entity_id = WRO.wip_entity_id
AND WRO.operation_seq_num IN (10, 20)
ORDER BY WRO.wip_entity_id,
WRO.operation_seq_num,
WRO.segment1;
l_msg_tokens.delete;
SELECT WRO.wip_entity_id,
WRO.operation_seq_num,
WRO.segment1,
WRO.INVENTORY_ITEM_ID,
WRO.REQUIRED_QUANTITY,
WRO.QUANTITY_ISSUED,
WRO.QUANTITY_PER_ASSEMBLY,
WRO.QUANTITY_RELIEVED,
WRO.COMPONENT_YIELD_FACTOR,
WRO.basis_type
FROM wsm_lot_move_txn_interface WLMTI,
WIP_REQUIREMENT_OPERATIONS WRO
WHERE WLMTI.group_id = p_group_id
AND WLMTI.wip_entity_id = WRO.wip_entity_id
AND WRO.operation_seq_num IN (WLMTI.FM_OPERATION_SEQ_NUM, WLMTI.TO_OPERATION_SEQ_NUM)
ORDER BY WRO.wip_entity_id,
WRO.operation_seq_num,
WRO.segment1;
l_msg_tokens.delete;
SELECT WLMTI.header_id
, wtie.transaction_id
, wtie.error_message
FROM WIP_TXN_INTERFACE_ERRORS wtie,
WSM_LOT_MOVE_TXN_INTERFACE WLMTI,
WIP_MOVE_TXN_INTERFACE WMTI
WHERE WTIE.transaction_id = WMTI.transaction_id
--FP bug 5178168 (base bug 5168406) changed the line below
--AND WMTI.batch_id = WLMTI.transaction_id
AND nvl(WMTI.batch_id, wmti.transaction_id) = WLMTI.transaction_id
AND WMTI.group_id = l_wmti_group_id
AND WLMTI.group_id = p_group_id;
l_msg_tokens.delete;
SELECT count(*)
INTO l_wmti_err_txns
FROM WIP_MOVE_TXN_INTERFACE
WHERE GROUP_ID=l_wmti_group_id
AND PROCESS_STATUS = 3;
l_msg_tokens.delete;
update_txn_status(x_error_code,
x_error_msg,
p_group_id,
l_wmti_group_id);
l_msg_tokens.delete;
UPDATE WIP_OPERATIONS
SET quantity_completed = 0
WHERE rowid IN
(
SELECT WO.rowid
FROM WIP_OPERATIONS WO,
WSM_LOT_MOVE_TXN_INTERFACE WLMTI
WHERE WLMTI.group_id = p_group_id
AND WLMTI.internal_group_id = l_wmti_group_id
AND WLMTI.status = 4
AND WO.wip_entity_id = WLMTI.wip_entity_id
AND WO.operation_seq_num = WLMTI.fm_operation_seq_num
AND WO.skip_flag = 1
);
l_msg_tokens.delete;
p_msg_text => 'B4 UPDATE WIP_OPERATION_YIELDS woy '
||';p_group_id '
UPDATE WIP_OPERATION_YIELDS woy
SET status = 1,
last_update_date = sysdate,
last_update_login = g_login_id,
request_id = g_request_id,
program_application_id= g_program_application_id,
program_id = g_program_id,
program_update_date = DECODE(g_request_id,NULL,NULL,SYSDATE)
WHERE woy.rowid IN ((
SELECT woy2.rowid
FROM WIP_OPERATIONS wop2,
WIP_OPERATION_YIELDS woy2,
WSM_PARAMETERS wp, -- ESA
WIP_DISCRETE_JOBS wdj, -- NSLBJ
WIP_MOVE_TRANSACTIONS wmt,
WSM_LOT_MOVE_TXN_INTERFACE wlmti
--bug 3615826
-- WHERE wmt.group_id = l_wmti_group_id
WHERE wlmti.group_id = p_group_id
--mes commented out the following and added subsequent lines
-- AND wlmti.internal_group_id = l_wmti_group_id;
SELECT woy2.rowid
FROM WIP_OPERATIONS wop2,
WIP_OPERATION_YIELDS woy2,
WSM_PARAMETERS wp, -- ESA
WIP_DISCRETE_JOBS wdj, -- NSLBJ
WIP_MOVE_TRANSACTIONS wmt,
WSM_LOT_MOVE_TXN_INTERFACE wlmti
--bug 3615826
-- WHERE wmt.group_id = l_wmti_group_id
WHERE wlmti.group_id = p_group_id
--mes commented out the following and added subsequent lines
-- AND wlmti.internal_group_id = l_wmti_group_id;
Updated '||SQL%ROWCOUNT||' rows in WOY');
UPDATE WIP_OPERATION_YIELDS woy
SET status = 1,
last_update_date = sysdate,
last_update_login = g_login_id,
request_id = g_request_id,
program_application_id= g_program_application_id,
program_id = g_program_id,
program_update_date = DECODE(g_request_id,NULL,NULL,SYSDATE)
WHERE woy.rowid IN (
SELECT woy2.rowid
FROM WIP_OPERATIONS wop2,
WIP_OPERATION_YIELDS woy2,
WSM_PARAMETERS wp, -- ESA
WIP_DISCRETE_JOBS wdj, -- NSLBJ
WIP_MOVE_TXN_INTERFACE wti
WHERE wti.group_id = l_wmti_group_id
AND wti.process_phase = g_move_proc
AND wti.process_status = g_running
-- AND TRUNC(wti.transaction_date) = to_date(:l_temp_date, WCD_CANONICAL_DATE) --bugfix 2856703
AND wp.organization_id = wti.organization_id -- ESA
AND nvl(wp.ESTIMATED_SCRAP_ACCOUNTING, 1) = 1 -- ESA
AND wdj.wip_entity_id = wti.wip_entity_id -- NSLBJ
AND wdj.job_type <>3 -- NSLBJ
AND wop2.organization_id = wti.organization_id
AND wop2.wip_entity_id = wti.wip_entity_id
AND woy2.organization_id = wti.organization_id
AND woy2.wip_entity_id = wti.wip_entity_id
AND woy2.operation_seq_num= wop2.operation_seq_num
AND ( -- Forward Move
( wop2.operation_seq_num >= wti.fm_operation_seq_num
+ DECODE(sign(wti.fm_intraoperation_step_type-g_run), 0,0,-1,0,1,1)
AND
wop2.operation_seq_num < wti.to_operation_seq_num
+ DECODE(sign(wti.to_intraoperation_step_type-g_run), 0,0,-1,0,1,1)
AND
( wti.to_operation_seq_num > wti.fm_operation_seq_num
OR
(wti.to_operation_seq_num = wti.fm_operation_seq_num
AND wti.fm_intraoperation_step_type<=g_run
AND wti.to_intraoperation_step_type>g_run)
)
AND
( wop2.count_point_type < g_no_manual
OR wop2.operation_seq_num = wti.fm_operation_seq_num
OR (wop2.operation_seq_num = wti.to_operation_seq_num
AND wti.to_intraoperation_step_type > g_run)
)
)
OR --Backward Move
( wop2.operation_seq_num < wti.fm_operation_seq_num
+ DECODE(sign(wti.fm_intraoperation_step_type-g_run), 0,0,-1,0,1,1)
AND
wop2.operation_seq_num >= wti.to_operation_seq_num
+ DECODE(sign(wti.to_intraoperation_step_type-g_run), 0,0,-1,0,1,1)
AND
( wti.fm_operation_seq_num > wti.to_operation_seq_num
OR (wti.fm_operation_seq_num = wti.to_operation_seq_num
AND wti.to_intraoperation_step_type<=g_run
AND wti.fm_intraoperation_step_type>g_run)
)
AND
( wop2.count_point_type < g_no_manual
OR(wop2.operation_seq_num = wti.to_operation_seq_num
AND wop2.count_point_type < g_no_manual)
OR(wop2.operation_seq_num = wti.fm_operation_seq_num
AND wti.fm_intraoperation_step_type > g_run)
)
)
)
);
SELECT wlmti.transaction_id,
wlmti.organization_id,
wlmti.wip_entity_id,
wlmti.fm_operation_seq_num,
wlmti.to_operation_seq_num,
wlmti.to_intraoperation_step_type,
wlmti.fm_intraoperation_step_type,
wlmti.scrap_quantity,
wlmti.source_code --Added for bug 5480482
FROM wsm_lot_move_txn_interface wlmti
WHERE wlmti.group_id = p_group_id
AND wlmti.status = 4
AND wlmti.transaction_type = 4
--move enh 115.135 changed the AND clause for performance
--mes commented out the following and added subsequent lines
-- AND wlmti.internal_group_id = l_wmti_group_id;
UPDATE wip_discrete_jobs wdj
SET wdj.serialization_start_op = null
where wdj.wip_entity_id = rec.wip_entity_id
and wdj.wip_entity_id IN (select wlbj.wip_entity_id
from wsm_lot_based_jobs wlbj
where wlbj.wip_entity_id = rec.wip_entity_id
and first_serial_txn_id IS NULL);
UPDATE WIP_DISCRETE_JOBS wdj
SET actual_start_date = null,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = rec.wip_entity_id
and not exists (select 1 from wip_move_transactions wmt
where wmt.wip_entity_id = wdj.wip_entity_id
and wmt.organization_id = wdj.organization_id
and wmt.wsm_undo_txn_id is NULL);
l_msg_tokens.delete;
SELECT wlmti.new_wip_entity_name,
wlmti.organization_id, -- CZH.BUG2282570
wlmti.wip_entity_id,
wlmti.header_id,
wlmti.transaction_type, -- CZH.BUG2282570
wlmti.transaction_id,
wlmti.to_operation_seq_num,
wlmti.fm_intraoperation_step_type,
wlmti.scrap_quantity,
wlmti.transaction_quantity,
wlmti.source_code
FROM wsm_lot_move_txn_interface wlmti
WHERE wlmti.group_id = p_group_id
AND wlmti.status = 4
/* Added condition to fix bug #1815584 */
AND wlmti.transaction_type in (2,3)
/* Assy Completion/return */
AND wlmti.new_wip_entity_name is NOT NULL
--move enh 115.135 changed the AND clause for performance
--mes commented out the following and added subsequent lines
-- AND wlmti.internal_group_id = l_wmti_group_id;
l_msg_tokens.delete;
p_msg_text => 'b4 UPDATE wip_entities '||
' rec.new_wip_entity_name '||rec.new_wip_entity_name||
' rec.wip_entity_id '||rec.wip_entity_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
);
UPDATE wip_entities
SET wip_entity_name = rec.new_wip_entity_name
WHERE wip_entity_id = rec.wip_entity_id;
fnd_file.put_line(fnd_file.log, 'wip_entities : Update failed '||l_new_jobname);
fnd_file.put_line(fnd_file.log, 'updated wip_entity_id= '||l_wip_entity_id||' with the new_job_name '||l_new_jobname);
UPDATE wip_discrete_jobs wdj
SET wdj.serialization_start_op = null
where wdj.wip_entity_id = rec.wip_entity_id
and wdj.wip_entity_id IN (select wlbj.wip_entity_id
from wsm_lot_based_jobs wlbj
where wlbj.wip_entity_id = rec.wip_entity_id
and first_serial_txn_id IS NULL);
UPDATE WIP_DISCRETE_JOBS wdj
SET actual_start_date = null,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = g_user_id
WHERE wip_entity_id = rec.wip_entity_id
and not exists (select 1 from wip_move_transactions wmt
where wmt.wip_entity_id = wdj.wip_entity_id
and wmt.organization_id = wdj.organization_id
and wmt.wsm_undo_txn_id is NULL);
select net_quantity,primary_item_id
into l_net_quantity,l_primary_item_id
from wip_discrete_jobs
where wip_entity_id=rec.wip_entity_id
and organization_id=rec.organization_id;
WSM_Serial_support_PVT.Update_attr_move( p_group_id => l_group_id ,
p_internal_group_id => l_internal_group_id ,
p_move_txn_id => l_move_txn_id ,
p_scrap_txn_id => l_scrap_id ,
-- Pass the org as NULL since can process across orgs...
p_organization_id => null ,
x_return_status => l_return_status ,
x_error_count => l_error_count ,
x_error_msg => l_error_msg
);
fnd_file.put_line(fnd_file.log, 'WSM_Serial_support_PVT.Update_attr_move returned Success');
l_error_msg := 'WSM_Serial_support_PVT.Update_attr_move returned failure';
fnd_file.put_line(fnd_file.log, 'WSM_Serial_support_PVT.Update_attr_move returned failure');
DELETE wsm_lot_move_txn_interface wlmti
WHERE status = WIP_CONSTANTS.COMPLETED
AND transaction_date <= decode(l_del_profile_value, NULL,
transaction_date-1, SYSDATE-l_del_profile_value)
RETURNING header_id BULK COLLECT INTO l_header_id_tbl;
DELETE wsm_serial_txn_interface
WHERE header_id = l_header_id_tbl(l_index)
AND transaction_type_id = 2;
UPDATE WIP_OPERATIONS
SET wsm_costed_quantity_completed = quantity_completed
WHERE ROWID IN
(SELECT WO.ROWID
FROM WIP_OPERATIONS WO, WIP_MOVE_TRANSACTIONS WMT, WSM_LOT_MOVE_TXN_INTERFACE WLMTI
WHERE WLMTI.group_id = p_group_id
AND WMT.transaction_id = WLMTI.transaction_id
AND WO.wip_entity_id = WMT.wip_entity_id
AND (WO.operation_seq_num IN (WMT.fm_operation_seq_num, WMT.to_operation_seq_num))
AND NOT (
(nvl(WO.quantity_waiting_to_move, 0) <> 0)
AND
(EXISTS (SELECT WSMT.ROWID
FROM WSM_SPLIT_MERGE_TRANSACTIONS WSMT,
WSM_SM_RESULTING_JOBS WSRJ
WHERE WSRJ.wip_entity_id = WO.wip_entity_id
AND WSRJ.starting_operation_seq_num = WO.operation_seq_num
AND WSRJ.starting_intraoperation_step = g_tomove
AND WSRJ.transaction_id = WSMT.transaction_id
)
)
)
);
SELECT count(*)
INTO l_err_count
FROM wsm_lot_move_txn_interface
WHERE group_id = p_group_id
AND status = 3;
DELETE from BOM_EXPLOSION_TEMP
WHERE group_id = x_group_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE wsm_lot_move_txn_interface WLMTI
SET WLMTI.ERROR = 'Error WSMPLBMI.MoveTransaction' ||'(stmt_num='||l_stmt_num||')',
WLMTI.STATUS = g_error,
WLMTI.LAST_UPDATE_DATE = SYSDATE
WHERE WLMTI.GROUP_ID = p_group_id
AND WLMTI.STATUS in (g_pending, g_running) ;
l_msg_tokens.delete;
UPDATE wsm_lot_move_txn_interface WLMTI
SET WLMTI.ERROR = 'Error WSMPLBMI.MoveTransaction' ||'(stmt_num='||l_stmt_num||')',
WLMTI.STATUS = g_error,
WLMTI.LAST_UPDATE_DATE = SYSDATE
WHERE WLMTI.GROUP_ID = p_group_id
AND WLMTI.STATUS in (g_pending, g_running) ;
UPDATE wsm_lot_move_txn_interface WLMTI
SET WLMTI.ERROR = substrb('Error:' ||errbuf, 1, 2000),
WLMTI.STATUS = g_error,
WLMTI.LAST_UPDATE_DATE = SYSDATE
WHERE WLMTI.GROUP_ID = p_group_id
AND WLMTI.STATUS in (g_pending, g_running) ;
fnd_file.put_line(fnd_file.log, 'Updated # of txns: '||SQL%ROWCOUNT||' (set WLMTI.error = ErrorMsg)'); /* bugfix 2721366 */
UPDATE wsm_lot_move_txn_interface WLMTI
SET WLMTI.ERROR = substrb('Unexpected SQL Error:' ||errbuf, 1, 2000),
WLMTI.STATUS = g_error,
WLMTI.LAST_UPDATE_DATE = SYSDATE
WHERE WLMTI.GROUP_ID = p_group_id
AND WLMTI.STATUS in (g_pending, g_running) ;
fnd_file.put_line(fnd_file.log, 'Updated # of txns: '||SQL%ROWCOUNT||' (set WLMTI.error = ErrorMsg)'); /* bugfix 2721366 */
Called from Move Out page via Rosetta wrapper for processing Move Transaction. This in turn inserts
the data into the interface table and calls the overloaded MoveTransaction procedure.
****************************************************************************/
Procedure MoveTransaction(
p_group_id IN NUMBER,
p_transaction_id IN NUMBER,
p_source_code IN VARCHAR2,
p_TRANSACTION_TYPE IN NUMBER,
p_ORGANIZATION_ID IN NUMBER,
p_WIP_ENTITY_ID IN NUMBER,
p_WIP_ENTITY_NAME IN VARCHAR2,
p_primary_item_id IN NUMBER,
p_TRANSACTION_DATE IN DATE,
p_FM_OPERATION_SEQ_NUM IN NUMBER,
p_FM_OPERATION_CODE IN VARCHAR2,
p_FM_DEPARTMENT_ID IN NUMBER,
p_FM_DEPARTMENT_CODE IN VARCHAR2,
p_FM_INTRAOPERATION_STEP_TYPE IN NUMBER,
p_TO_OPERATION_SEQ_NUM IN NUMBER,
p_TO_OPERATION_CODE IN VARCHAR2,
p_TO_DEPARTMENT_ID IN NUMBER,
p_TO_DEPARTMENT_CODE IN VARCHAR2,
p_TO_INTRAOPERATION_STEP_TYPE IN NUMBER,
p_PRIMARY_QUANTITY IN NUMBER,
p_low_yield_trigger_limit IN NUMBER,
p_primary_uom IN VARCHAR2,
p_SCRAP_ACCOUNT_ID IN NUMBER,
p_REASON_ID IN NUMBER,
p_REASON_NAME IN VARCHAR2,
p_REFERENCE IN VARCHAR2,
p_QA_COLLECTION_ID IN NUMBER,
p_JUMP_FLAG IN VARCHAR2,
p_HEADER_ID IN NUMBER,
p_PRIMARY_SCRAP_QUANTITY IN NUMBER,
p_bonus_quantity IN NUMBER,
p_SCRAP_AT_OPERATION_FLAG IN NUMBER,
p_bonus_account_id IN NUMBER,
p_employee_id IN NUMBER,
p_operation_start_date IN DATE,
p_operation_completion_date IN DATE,
p_expected_completion_date IN DATE,
p_mtl_txn_hdr_id IN NUMBER,
p_sec_uom_code_tbl IN t_sec_uom_code_tbl_type,
p_sec_move_out_qty_tbl IN t_sec_move_out_qty_tbl_type,
p_jobop_scrap_serials_tbl IN WSM_Serial_support_GRP.WSM_SERIAL_NUM_TBL,
p_jobop_bonus_serials_tbl IN WSM_Serial_support_GRP.WSM_SERIAL_NUM_TBL,
p_scrap_codes_tbl IN t_scrap_codes_tbl_type,
p_scrap_code_qty_tbl IN t_scrap_code_qty_tbl_type,
p_bonus_codes_tbl IN t_bonus_codes_tbl_type,
p_bonus_code_qty_tbl IN t_bonus_code_qty_tbl_type,
p_jobop_resource_usages_tbl IN t_jobop_res_usages_tbl_type,
x_wip_move_api_sucess_msg OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_sec_uom_code_tbls t_sec_uom_code_tbls_type;
l_msg_tokens.delete;
l_param_tbl.delete;
DELETE FROM WSM_INTERFACE_ERRORS WHERE header_id = p_header_id;
select count(*)
into l_mti_rows
from MTL_TRANSACTIONS_INTERFACE
where transaction_header_id = p_mtl_txn_hdr_id;
select count(*)
into l_mtli_rows
from mtl_serial_numbers_interface
where transaction_header_id = p_mtl_txn_hdr_id;
select count(*)
into l_mti_rows
from mtl_serial_numbers_interface
where transaction_header_id = p_mtl_txn_hdr_id;
SELECT MTLI.TRANSACTION_INTERFACE_ID, MTLI.LOT_NUMBER, MTLI.TRANSACTION_QUANTITY,
MTLI.PRIMARY_QUANTITY
FROM mtl_transaction_lots_interface MTLI,
mtl_transactions_interface MTI
WHERE MTI.TRANSACTION_HEADER_ID = p_mtl_txn_hdr_id
AND MTI.TRANSACTION_INTERFACE_ID = MTLI.TRANSACTION_INTERFACE_ID;
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE MTL_TRANSACTIONS_INTERFACE MTL
SET MTL.wip_entity_type = WIP_CONSTANTS.LOTBASED,
--bug 5584140 operation_seq_num is already stamped in the UI and there could be multiple
--operation seq nums so don't overwrite. Get department_id for each operation
--operation_seq_num = p_fm_operation_seq_num,
--department_id = p_fm_department_id
MTL.department_id = (SELECT department_id
FROM WIP_OPERATIONS WO
WHERE WO.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = MTL.operation_seq_num)
WHERE MTL.transaction_header_id = p_mtl_txn_hdr_id;
select count(*)
into l_numErrRows
from mtl_transactions_interface
where transaction_header_id = p_mtl_txn_hdr_id;
select count(*)
into l_numTempRows
from mtl_material_transactions_temp
where transaction_header_id = p_mtl_txn_hdr_id;
l_msg_tokens.delete;
select msik.concatenated_segments, mti.error_explanation
bulk collect into l_itemNameTbl,l_errExplTbl
from mtl_transactions_interface mti,
mtl_system_items_kfv msik
where mti.transaction_header_id = p_mtl_txn_hdr_id
and mti.error_explanation is not null
and mti.inventory_item_id = msik.inventory_item_id
and mti.organization_id = msik.organization_id;
fnd_message.set_name('WIP', 'WIP_TMPINSERT_ERR');
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT count(*)
INTO l_mmtt_rows
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_mtl_txn_hdr_id;
SELECT count(*)
INTO l_mtlt_rows
FROM mtl_transaction_lots_temp mtlt, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_mtl_txn_hdr_id
AND mtlt.TRANSACTION_TEMP_ID = mmtt.TRANSACTION_TEMP_ID;
SELECT count(*)
INTO l_msnt_rows
FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_mtl_txn_hdr_id
AND msnt.TRANSACTION_TEMP_ID = mmtt.TRANSACTION_TEMP_ID;
l_msg_tokens.delete;
DELETE FROM WSM_LOT_MOVE_TXN_INTERFACE WHERE header_id = p_header_id;
INSERT into WSM_LOT_MOVE_TXN_INTERFACE
(
-- INTERFACE_ID -- commented for bugfix 7163496
TRANSACTION_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATED_BY_NAME
, CREATION_DATE
, CREATED_BY
, CREATED_BY_NAME
, LAST_UPDATE_LOGIN
, GROUP_ID
, SOURCE_CODE
, STATUS
, TRANSACTION_TYPE
, ORGANIZATION_ID
, ORGANIZATION_CODE
, WIP_ENTITY_ID
, WIP_ENTITY_NAME
, ENTITY_TYPE
, PRIMARY_ITEM_ID
, TRANSACTION_DATE
, FM_OPERATION_SEQ_NUM
, FM_OPERATION_CODE
, FM_DEPARTMENT_ID
, FM_DEPARTMENT_CODE
, FM_INTRAOPERATION_STEP_TYPE
, TO_OPERATION_SEQ_NUM
, TO_OPERATION_CODE
, TO_DEPARTMENT_ID
, TO_DEPARTMENT_CODE
, TO_INTRAOPERATION_STEP_TYPE
, TRANSACTION_QUANTITY
, TRANSACTION_UOM
, PRIMARY_QUANTITY
, PRIMARY_UOM
, SCRAP_ACCOUNT_ID
, REASON_ID
, REASON_NAME
, REFERENCE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, QA_COLLECTION_ID
, JUMP_FLAG
, HEADER_ID
, PRIMARY_SCRAP_QUANTITY
, SCRAP_QUANTITY
, SCRAP_AT_OPERATION_FLAG
)
VALUES
(
-- NULL -- commented for bugfix 7163496
p_transaction_id
, sysdate --LAST_UPDATE_DATE
, g_user_id --LAST_UPDATED_BY
, fnd_global.user_name --LAST_UPDATED_BY_NAME
, sysdate --CREATION_DATE
, g_user_id --CREATED_BY
, fnd_global.user_name--CREATED_BY_NAME
, g_login_id --LAST_UPDATE_LOGIN
, WIP_TRANSACTIONS_S.NEXTVAL --p_group_id --GROUP_ID
, p_source_code --source_code
, WIP_CONSTANTS.PENDING --STATUS
, p_TRANSACTION_TYPE
, p_ORGANIZATION_ID
, null --!! ORGANIZATION_CODE
, p_WIP_ENTITY_ID
, p_WIP_ENTITY_NAME
, WIP_CONSTANTS.LOTBASED --!!ENTITY_TYPE
, null --PRIMARY_ITEM_ID
, nvl(p_TRANSACTION_DATE, sysdate)
, p_FM_OPERATION_SEQ_NUM
, p_FM_OPERATION_CODE
, p_FM_DEPARTMENT_ID
, p_FM_DEPARTMENT_CODE
, p_FM_INTRAOPERATION_STEP_TYPE
, p_TO_OPERATION_SEQ_NUM
, p_TO_OPERATION_CODE
, p_TO_DEPARTMENT_ID
, p_TO_DEPARTMENT_CODE
, p_TO_INTRAOPERATION_STEP_TYPE
, p_PRIMARY_QUANTITY --TRANSACTION_QUANTITY
, p_PRIMARY_UOM --TRANSACTION_UOM
, p_PRIMARY_QUANTITY
, p_PRIMARY_UOM
, p_SCRAP_ACCOUNT_ID
, p_REASON_ID
, p_REASON_NAME
, p_REFERENCE
, null --ATTRIBUTE_CATEGORY
, null --ATTRIBUTE1
, null --ATTRIBUTE2
, null --ATTRIBUTE3
, null --ATTRIBUTE4
, null --ATTRIBUTE5
, null --ATTRIBUTE6
, null --ATTRIBUTE7
, null --ATTRIBUTE8
, null --ATTRIBUTE9
, null --ATTRIBUTE10
, null --ATTRIBUTE11
, null --ATTRIBUTE12
, null --ATTRIBUTE13
, null --ATTRIBUTE14
, null --ATTRIBUTE15
, p_QA_COLLECTION_ID
, p_JUMP_FLAG
, nvl(p_HEADER_ID, wsm_lot_move_txn_interface_s.nextval) --HEADER_ID
, p_PRIMARY_SCRAP_QUANTITY --PRIMARY_SCRAP_QUANTITY
, p_PRIMARY_SCRAP_QUANTITY --SCRAP_QUANTITY
, decode(p_primary_scrap_quantity, --bug 5584140 Added decode so that SCRAP_AT_OPERATION_FLAG=null when there is no scrap
null, null,
0, null,
1) --SCRAP_AT_OPERATION_FLAG
)
RETURNING transaction_id, group_id, header_id INTO l_transaction_id, l_group_id, l_header_id;
l_msg_tokens.delete;
p_msg_text => 'B4 UPDATE wsm_lot_move_txn_interface wlmti '||
';g_user_id '||
l_msg_tokens.delete;
SELECT message
FROM WSM_INTERFACE_ERRORS
WHERE transaction_id = l_transaction_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT DECODE(SUM(DECODE(WVIS.STEP_LOOKUP_TYPE,1,1,0)),0,2,1),
DECODE(SUM(DECODE(WVIS.STEP_LOOKUP_TYPE,2,1,0)),0,2,1),
DECODE(SUM(DECODE(WVIS.STEP_LOOKUP_TYPE,3,
DECODE(WVIS.RECORD_CREATOR,'USER',1,0),0)),0,2,1),
DECODE(SUM(DECODE(WVIS.STEP_LOOKUP_TYPE,5,1,0)),0,2,1)
INTO l_wip_queue_enabled_flag, l_wip_run_enabled_flag, l_wip_to_move_enabled_flag, l_wip_scrap_enabled_flag
--FROM WIP_PARAMETERS_V
FROM WIP_VALID_INTRAOPERATION_STEPS WVIS
WHERE organization_id = p_organization_id;
SELECT nvl(show_next_op_by_default, 0),
nvl(track_multi_usage_dates, 0), nvl(move_in_option, 2), nvl(move_to_next_op_option, 2)
INTO l_wsm_show_next_op_by_default,
l_wsm_mul_res_usage_dates, l_wsm_move_in, l_wsm_move_to_next_op
FROM WSM_PARAMETERS
WHERE organization_id = p_organization_id;
SELECT nvl(BOS.use_org_settings, 1), nvl(BOS.show_next_op_by_default, 0),
nvl(BOS.show_scrap_code, 0), nvl(BOS.show_lot_attrib, 0),
nvl(BOS.track_multiple_res_usage_dates, 0), nvl(BOS.to_move_mandatory_flag, 0),
reference_flag
INTO l_bos_use_org_settings, l_bos_show_next_op_by_default,
x_show_scrap_codes, l_bos_show_lot_attrib, l_bos_mul_res_usage_dates,
l_bos_to_move_mandatory_flag, l_reference_flag
FROM BOM_OPERATION_SEQUENCES BOS, WIP_OPERATIONS WO
WHERE WO.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = p_operation_seq_num
AND BOS.operation_sequence_id = WO.operation_sequence_id;
SELECT nvl(BSO.use_org_settings, 1), nvl(BSO.show_next_op_by_default, 0),
nvl(BSO.show_scrap_code, 0), nvl(BSO.show_lot_attrib, 0),
nvl(BSO.track_multiple_res_usage_dates, 0), nvl(BSO.to_move_mandatory_flag, 0)
INTO l_bos_use_org_settings, l_bos_show_next_op_by_default,
x_show_scrap_codes, l_bos_show_lot_attrib, l_bos_mul_res_usage_dates,
l_bos_to_move_mandatory_flag
FROM BOM_STANDARD_OPERATIONS BSO, WIP_OPERATIONS WO
WHERE WO.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = p_operation_seq_num
AND BSO.standard_operation_id = WO.standard_operation_id
AND BSO.organization_id = WO.organization_id;
SELECT nvl(BSO.use_org_settings, 1), nvl(BSO.show_next_op_by_default, 0),
nvl(BSO.show_scrap_code, 0), nvl(BSO.show_lot_attrib, 0),
nvl(BSO.track_multiple_res_usage_dates, 0), nvl(BSO.to_move_mandatory_flag, 0)
INTO l_bos_use_org_settings, l_bos_show_next_op_by_default,
x_show_scrap_codes, l_bos_show_lot_attrib, l_bos_mul_res_usage_dates,
l_bos_to_move_mandatory_flag
FROM BOM_STANDARD_OPERATIONS BSO, WIP_OPERATIONS WO
WHERE WO.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = p_operation_seq_num
AND BSO.standard_operation_id = WO.standard_operation_id
AND BSO.organization_id = WO.organization_id;
SELECT operation_seq_num
INTO l_end_routing_operation
FROM WSM_COPY_OPERATIONS WCO
WHERE WCO.wip_entity_id = p_wip_entity_id
AND WCO.network_start_end = 'E';
SELECT WO.actual_start_date, WO.employee_id, nvl(WO.actual_completion_date, sysdate)
INTO x_default_start_date, x_employee_id, x_default_completion_date
FROM WIP_OPERATIONS WO
WHERE WO.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = p_operation_seq_num;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT 1
INTO x_show_secondary_quantities
FROM dual
WHERE EXISTS(
SELECT 'secondary quantities exist'
FROM WSM_OP_SECONDARY_QUANTITIES
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_operation_seq_num
);
/********bug 5182689 modified the SELECT below to account for the case when serialization_start_op is null
SELECT decode(first_serial_txn_id,
null, decode(serialization_start_op,
p_routing_operation, 1,
0), 1)
************/
/****bug 5444062 Replaced the sql below with a sql getting the values and logic in pl/sql for determining
l_serialization_started
SELECT decode(first_serial_txn_id,
null, decode(serialization_start_op,
null, 0,
decode(serialization_start_op,
p_routing_operation, 1,
0)),
1)
INTO l_first_serial_txn_id
FROM WSM_LOT_BASED_JOBS
WHERE wip_entity_id = p_wip_entity_id;
SELECT first_serial_txn_id, serialization_start_op
INTO l_first_serial_txn_id, l_serialization_start_op
FROM WSM_LOT_BASED_JOBS
WHERE wip_entity_id = p_wip_entity_id;
SELECT 1
INTO x_scrap_codes_defined
FROM dual
WHERE EXISTS(
SELECT 'scrap codes exist'
FROM WSM_OP_REASON_CODES
WHERE wip_entity_id = p_wip_entity_id
--bug 5191223 Added the condition operation_seq_num = p_operation_seq_num
AND operation_seq_num = p_operation_seq_num
AND code_type = 1
);
SELECT 1
INTO x_bonus_codes_defined
FROM dual
WHERE EXISTS(
SELECT 'bonus codes exist'
FROM WSM_OP_REASON_CODES
WHERE wip_entity_id = p_wip_entity_id
--bug 5191223 Added the condition operation_seq_num = p_operation_seq_num
AND operation_seq_num = p_operation_seq_num
AND code_type = 2
);
SELECT 1
INTO l_next_links_exist
FROM dual
WHERE EXISTS
(SELECT 'next_links_exist'
FROM wsm_copy_op_networks wcon
WHERE wcon.wip_entity_id = p_wip_entity_id
AND wcon.from_op_seq_num = p_routing_operation
);
SELECT FU.employee_id
INTO x_employee_id
FROM FND_USER FU
WHERE FU.user_id = p_user_id;
SELECT DISTINCT(PPF.FULL_NAME) FULL_NAME
INTO x_operator
FROM PER_PEOPLE_F PPF
WHERE PPF.person_id = x_employee_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT decode(first_serial_txn_id,
null, decode(p_routing_operation,
null, 0,
decode(serialization_start_op,
p_routing_operation, 1,
0)
),
1),
current_job_op_seq_num
INTO x_show_serial_region, l_current_job_op_seq_num
FROM WSM_LOT_BASED_JOBS
WHERE wip_entity_id = p_wip_entity_id;
SELECT first_serial_txn_id, serialization_start_op, current_job_op_seq_num
INTO l_first_serial_txn_id, l_serialization_start_op, l_current_job_op_seq_num
FROM WSM_LOT_BASED_JOBS
WHERE wip_entity_id = p_wip_entity_id;
SELECT operation_seq_num
INTO l_end_routing_operation
FROM WSM_COPY_OPERATIONS WCO
WHERE WCO.wip_entity_id = p_wip_entity_id
AND WCO.network_start_end = 'E';
SELECT decode(bitand(l_move_codemask, 65536), 65536, 1, 0),
decode(bitand(l_move_codemask, 131072), 131072, 1, 0),
decode(bitand(l_move_codemask, 262144), 262144, 1, 0)
INTO x_show_move_in, x_show_move_out, x_show_move_to_next_op
FROM dual;
SELECT 1
INTO x_show_scrap_codes
FROM dual
WHERE EXISTS(
SELECT 'scrap codes exist'
FROM WSM_OP_REASON_CODES
WHERE wip_entity_id = p_wip_entity_id
--bug 5191223 Added the condition operation_seq_num = p_operation_seq_num
AND operation_seq_num = p_operation_seq_num
AND code_type = 1
);
SELECT 1
INTO x_show_bonus_codes
FROM dual
WHERE EXISTS(
SELECT 'bonus codes exist'
FROM WSM_OP_REASON_CODES
WHERE wip_entity_id = p_wip_entity_id
--bug 5191223 Added the condition operation_seq_num = p_operation_seq_num
AND operation_seq_num = p_operation_seq_num
AND code_type = 2
);
SELECT 1
INTO x_show_secondary_quantities
FROM dual
WHERE EXISTS(
SELECT 'secondary quantities exist'
FROM WSM_OP_SECONDARY_QUANTITIES
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_operation_seq_num
);
SELECT nvl(BOS.show_lot_attrib, 0)
INTO x_show_lot_attrib
FROM BOM_OPERATION_SEQUENCES BOS, WIP_OPERATIONS WO
WHERE WO.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = p_operation_seq_num
AND BOS.operation_sequence_id = WO.operation_sequence_id;
SELECT nvl(BSO.show_lot_attrib, 0)
INTO x_show_lot_attrib
FROM BOM_STANDARD_OPERATIONS BSO
WHERE BSO.standard_operation_id = p_standard_op_id;
l_param_tbl.delete;
Updates the WO.costed_quantity_completed column from Move form, interface and OA page
****************************************************************************/
--mes
Procedure update_costed_qty_compl(
p_transaction_type NUMBER
, p_job_fm_op_seq_num NUMBER
, p_job_to_op_seq_num NUMBER
, p_wip_entity_id NUMBER
, p_fm_intraoperation_step_type NUMBER
, p_to_intraoperation_step_type NUMBER
, p_primary_move_qty NUMBER
, p_primary_scrap_qty NUMBER
, p_scrap_at_op NUMBER
)
IS
l_costed_quantity_completed NUMBER := 0;
l_module VARCHAR2(100) := 'wsm.plsql.WSMPLBMI.update_costed_qty_compl';
l_msg_tokens.delete;
p_msg_text => 'Begin procedure update_costed_qty_compl'
||';p_transaction_type '
UPDATE WIP_OPERATIONS
SET wsm_costed_quantity_completed = nvl(wsm_costed_quantity_completed, 0) +
l_fm_costed_quantity_completed
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_job_fm_op_seq_num;
UPDATE WIP_OPERATIONS
SET wsm_costed_quantity_completed = nvl(wsm_costed_quantity_completed, 0) +
l_to_costed_quantity_completed
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_job_to_op_seq_num;
UPDATE WIP_OPERATIONS
SET wsm_costed_quantity_completed = nvl(wsm_costed_quantity_completed, 0) + l_costed_quantity_completed
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_job_fm_op_seq_num;
UPDATE WIP_OPERATIONS
SET wsm_costed_quantity_completed = nvl(wsm_costed_quantity_completed, 0) -
l_fm_costed_quantity_completed
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_job_fm_op_seq_num;
UPDATE WIP_OPERATIONS
SET wsm_costed_quantity_completed = nvl(wsm_costed_quantity_completed, 0) -
l_to_costed_quantity_completed
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_job_to_op_seq_num;
UPDATE WIP_OPERATIONS
SET wsm_costed_quantity_completed = nvl(wsm_costed_quantity_completed, 0) - l_costed_quantity_completed
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_job_fm_op_seq_num;
END update_costed_qty_compl;
select uom_class
into l_hrUOM_class
from mtl_units_of_measure
where uom_code = l_hrUOM;
select uom_class
into l_resUOM_class
from mtl_units_of_measure
where uom_code = p_to_uom;