[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT current_errors(error_no).transaction_id, -- transaction_id
current_errors(error_no).error_message, -- error_message
current_errors(error_no).error_column, -- error_column
SYSDATE, -- last_update_date
NVL(last_updated_by, -1),
SYSDATE, -- creation_date
NVL(created_by, -1),
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
FROM wip_move_txn_interface
WHERE transaction_id = current_errors(error_no).transaction_id
AND group_id = g_group_id;
current_errors.delete ;
UPDATE wip_move_txn_interface wmti
SET wmti.organization_id =
(SELECT mp.organization_id
FROM mtl_parameters mp
WHERE mp.organization_code = UPPER(wmti.organization_code))
WHERE wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.PENDING
AND wmti.organization_id IS NULL
AND wmti.organization_code IS NOT NULL;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.PENDING
AND ((wmti.organization_id IS NULL) -- cannot derive ORGANIZATION_ID
OR
(NOT EXISTS
(SELECT 'X'
FROM mtl_parameters mp
WHERE mp.organization_code = UPPER(NVL(wmti.organization_code,
mp.organization_code))
AND mp.organization_id = wmti.organization_id
)
))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET wmti.wip_entity_id =
(SELECT we.wip_entity_id
FROM wip_entities we
WHERE we.wip_entity_name = wmti.wip_entity_name
AND we.organization_id = wmti.organization_id
AND entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.REPETITIVE,
WIP_CONSTANTS.LOTBASED))
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.wip_entity_id IS NULL
AND wmti.wip_entity_name IS NOT NULL;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND ((wmti.wip_entity_id IS NULL) -- cannot derive WIP_ENTITY_ID
OR
(NOT EXISTS
(SELECT 'X'
FROM wip_entities we
WHERE we.wip_entity_name = NVL(wmti.wip_entity_name,
we.wip_entity_name)
AND we.wip_entity_id = wmti.wip_entity_id
AND we.organization_id = wmti.organization_id
AND entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.REPETITIVE,
WIP_CONSTANTS.LOTBASED)
)
))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET (wmti.entity_type, wmti.primary_item_id) =
(SELECT we.entity_type,
we.primary_item_id
FROM wip_entities we
WHERE we.wip_entity_id = wmti.wip_entity_id)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.primary_item_id IS NULL
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET wmti.line_id =
(SELECT wl.line_id
FROM wip_lines wl
WHERE wl.line_code = wmti.line_code
AND wl.organization_id = wmti.organization_id
AND NVL(wl.disable_date, SYSDATE) >= SYSDATE)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND wmti.line_id IS NULL
AND wmti.line_code IS NOT NULL;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND ((wmti.line_id IS NULL)
OR
(NOT EXISTS
(SELECT 'X'
FROM wip_lines wl
WHERE wl.line_code = NVL(wmti.line_code, wl.line_code)
AND wl.line_id = wmti.line_id
AND wl.organization_id = wmti.organization_id
AND NVL(wl.disable_date, SYSDATE) >= SYSDATE
)
))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET wmti.repetitive_schedule_id =
(SELECT wrs1.repetitive_schedule_id
FROM wip_repetitive_schedules wrs1
WHERE wrs1.wip_entity_id = wmti.wip_entity_id
AND wrs1.organization_id = wmti.organization_id
AND wrs1.line_id = wmti.line_id
AND wrs1.status_type IN (WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG)
AND wrs1.first_unit_start_date =
(SELECT MIN(wrs2.first_unit_start_date)
FROM wip_repetitive_schedules wrs2
WHERE wrs2.wip_entity_id = wmti.wip_entity_id
AND wrs2.organization_id = wmti.organization_id
AND wrs2.line_id = wmti.line_id
AND wrs2.status_type IN (WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG)))
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND wmti.repetitive_schedule_id IS NULL;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND wmti.repetitive_schedule_id IS NULL
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
AND (wmti.line_id IS NOT NULL OR
wmti.line_code IS NOT NULL OR
wmti.repetitive_schedule_id IS NOT NULL)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND ((wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND NOT EXISTS
(SELECT 'X'
FROM wip_repetitive_schedules wrs
WHERE wrs.wip_entity_id = wmti.wip_entity_id
AND wrs.organization_id = wmti.organization_id
AND wrs.line_id = wmti.line_id
AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG)))
OR
(wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
AND NOT EXISTS
(SELECT 'X'
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND wdj.organization_id = wmti.organization_id
AND wdj.status_type IN (WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND NOT EXISTS
(SELECT 'X'
FROM wip_operations wo
WHERE wo.wip_entity_id = wmti.wip_entity_id
AND wo.organization_id = wmti.organization_id
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET wmti.transaction_type = WIP_CONSTANTS.MOVE_TXN
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type IS NULL;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type NOT IN (WIP_CONSTANTS.MOVE_TXN,
WIP_CONSTANTS.COMP_TXN,
WIP_CONSTANTS.RET_TXN)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
WIP_CONSTANTS.RET_TXN)
AND EXISTS
(SELECT 'X'
FROM mtl_system_items msi,
wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND msi.inventory_item_id = wmti.primary_item_id
AND msi.organization_id = wmti.organization_id
AND wdj.serialization_start_op IS NULL
AND msi.serial_number_control_code IN (WIP_CONSTANTS.FULL_SN,
WIP_CONSTANTS.DYN_RCV_SN))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
WIP_CONSTANTS.RET_TXN)
AND ((wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND EXISTS
(SELECT 'X'
FROM wip_repetitive_items wri
WHERE wri.wip_entity_id = wmti.wip_entity_id
AND wri.organization_id = wmti.organization_id
AND wri.line_id = wmti.line_id
AND wri.completion_subinventory IS NULL))
OR
(wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
AND EXISTS
(SELECT 'X'
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND wdj.organization_id = wmti.organization_id
AND wdj.completion_subinventory IS NULL)))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
WIP_CONSTANTS.RET_TXN)
AND EXISTS
(SELECT 'X'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = wmti.primary_item_id
AND msi.organization_id = wmti.organization_id
AND msi.revision_qty_control_code =
WIP_CONSTANTS.REVISION_CONTROLLED)
AND ((wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND NOT EXISTS
(SELECT 'X'
FROM wip_repetitive_schedules wrs,
mtl_item_revisions mir
WHERE wrs.organization_id = wmti.organization_id
AND wrs.repetitive_schedule_id = wmti.repetitive_schedule_id
AND mir.organization_id = wmti.organization_id
AND mir.inventory_item_id = wmti.primary_item_id
-- Fixed bug 2387630
AND (wrs.bom_revision IS NULL OR
mir.revision = wrs.bom_revision)))
OR
(wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
AND NOT EXISTS
(SELECT 'X'
FROM wip_discrete_jobs wdj,
mtl_item_revisions mir
WHERE wdj.organization_id = wmti.organization_id
AND wdj.wip_entity_id = wmti.wip_entity_id
AND mir.organization_id = wmti.organization_id
AND mir.inventory_item_id = wmti.primary_item_id
-- Fixed bug 2387630
AND (wdj.bom_revision IS NULL OR
mir.revision = wdj.bom_revision))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
WIP_CONSTANTS.RET_TXN)
AND EXISTS
(SELECT 'X'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = wmti.primary_item_id
AND msi.organization_id = wmti.organization_id
AND msi.lot_control_code = WIP_CONSTANTS.LOT)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
WIP_CONSTANTS.RET_TXN)
AND EXISTS
(SELECT 'X'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = wmti.primary_item_id
AND msi.organization_id = wmti.organization_id
AND msi.lot_control_code = WIP_CONSTANTS.LOT)
AND EXISTS
(SELECT 'X'
FROM wip_discrete_jobs wdj
WHERE wdj.organization_id = wmti.organization_id
AND wdj.wip_entity_id = wmti.wip_entity_id
AND wdj.lot_number IS NULL)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
AND EXISTS -- lot control
(SELECT 'X'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = wmti.primary_item_id
AND msi.organization_id = wmti.organization_id
AND msi.lot_control_code = WIP_CONSTANTS.LOT)
-- This is the first time to complete this assembly to this lot number
AND NOT EXISTS
(SELECT 'X'
FROM mtl_lot_numbers mln,
wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND wdj.organization_id = wmti.organization_id
AND mln.inventory_item_id = wmti.primary_item_id
AND mln.organization_id = wmti.organization_id
AND mln.lot_number = wdj.lot_number)
-- This item or item category requires lot attributes
AND 2 = inv_lot_sel_attr.is_enabled(
'Lot Attributes', -- p_flex_name
wmti.organization_id, -- p_organization_id
wmti.primary_item_id) -- p_inventory_item_id
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
AND EXISTS -- lot control and expiration date is user-defined
(SELECT 'X'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = wmti.primary_item_id
AND msi.organization_id = wmti.organization_id
AND msi.lot_control_code = WIP_CONSTANTS.LOT
AND msi.shelf_life_code = WIP_CONSTANTS.USER_DEFINED_EXP)
-- This is the first time to complete this assembly to this lot number
AND NOT EXISTS
(SELECT 'X'
FROM mtl_lot_numbers mln,
wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND wdj.organization_id = wmti.organization_id
AND mln.inventory_item_id = wmti.primary_item_id
AND mln.organization_id = wmti.organization_id
AND mln.lot_number = wdj.lot_number)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_date > SYSDATE
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND NOT EXISTS
(SELECT 'X'
FROM ORG_ACCT_PERIODS OAP
WHERE OAP.ORGANIZATION_ID = WMTI.ORGANIZATION_ID
AND OAP.PERIOD_CLOSE_DATE IS NULL
AND OAP.OPEN_FLAG = 'Y'
AND TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(
WMTI.TRANSACTION_DATE, -- p_trxn_date
WMTI.ORGANIZATION_ID -- p_inv_org_id
))
BETWEEN OAP.PERIOD_START_DATE AND OAP.SCHEDULE_CLOSE_DATE)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND ((wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND EXISTS
(SELECT 'X'
FROM wip_repetitive_schedules wrs
WHERE wrs.repetitive_schedule_id = wmti.repetitive_schedule_id
AND wrs.organization_id = wmti.organization_id
AND wrs.date_released > wmti.transaction_date))
OR
(wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
AND EXISTS
(SELECT 'X'
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND wdj.organization_id = wmti.organization_id
AND wdj.date_released > wmti.transaction_date)))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET wmti.acct_period_id =
(SELECT oap.acct_period_id
FROM org_acct_periods oap
WHERE oap.organization_id = wmti.organization_id
-- modified the statement below for timezone project in J
AND TRUNC(inv_le_timezone_pub.get_le_day_for_inv_org(
wmti.transaction_date, -- p_trxn_date
wmti.organization_id -- p_inv_org_id
)) BETWEEN
oap.period_start_date AND oap.schedule_close_date)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND (wmti.acct_period_id IS NULL
OR
NOT EXISTS
(SELECT 'X'
FROM wip_period_balances wpb
WHERE wpb.acct_period_id = wmti.acct_period_id
AND wpb.wip_entity_id = wmti.wip_entity_id
AND wpb.organization_id = wmti.organization_id
AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
OR (wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND repetitive_schedule_id IN
(SELECT wrs.repetitive_schedule_id
FROM wip_repetitive_schedules wrs
WHERE wrs.wip_entity_id = wmti.wip_entity_id
AND wrs.organization_id = wmti.organization_id
AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG))))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET wmti.fm_operation_seq_num =
(SELECT wo.operation_seq_num
FROM wip_operations wo
WHERE wo.wip_entity_id = wmti.wip_entity_id
AND wo.organization_id = wmti.organization_id
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1)
AND wo.next_operation_seq_num IS NULL)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
AND wmti.fm_operation_seq_num IS NULL;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND (wmti.fm_operation_seq_num IS NULL
OR
(NOT EXISTS
(SELECT 'X'
FROM wip_operations wo
WHERE wo.wip_entity_id = wmti.wip_entity_id
AND wo.organization_id = wmti.organization_id
AND wo.operation_seq_num = wmti.fm_operation_seq_num
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
AND wmti.fm_operation_seq_num <>
(SELECT wo.operation_seq_num
FROM wip_operations wo
WHERE wo.wip_entity_id = wmti.wip_entity_id
AND wo.organization_id = wmti.organization_id
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1)
AND wo.next_operation_seq_num IS NULL)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET wmti.fm_intraoperation_step_type = WIP_CONSTANTS.TOMOVE
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
AND wmti.fm_intraoperation_step_type IS NULL;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND (wmti.fm_intraoperation_step_type IS NULL
OR
(NOT EXISTS
(SELECT 'X'
FROM wip_valid_intraoperation_steps wvis,
wip_operations wo
WHERE wvis.organization_id = wmti.organization_id
AND wvis.step_lookup_type = wmti.fm_intraoperation_step_type
AND wo.organization_id = wmti.organization_id
AND wo.wip_entity_id = wmti.wip_entity_id
AND wo.operation_seq_num = wmti.fm_operation_seq_num
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1)
AND ((wvis.record_creator = 'USER' OR
wvis.step_lookup_type = WIP_CONSTANTS.QUEUE)
OR
(wvis.record_creator = 'SYSTEM' AND
wo.next_operation_seq_num IS NULL)))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND EXISTS
(SELECT 'X'
FROM wip_shop_floor_status_codes wsc,
wip_shop_floor_statuses ws
WHERE wsc.organization_id = wmti.organization_id
AND ws.organization_id = wmti.organization_id
AND ws.wip_entity_id = wmti.wip_entity_id
AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
AND ws.operation_seq_num = wmti.fm_operation_seq_num
AND ws.intraoperation_step_type = wmti.fm_intraoperation_step_type
AND ws.shop_floor_status_code = wsc.shop_floor_status_code
AND wsc.status_move_flag = WIP_CONSTANTS.NO
AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE
AND (wmti.source_code IS NULL OR
wmti.source_code <> 'RCV' OR
(wmti.source_code = 'RCV' AND
NOT EXISTS
(SELECT 'X'
FROM wip_parameters wp
WHERE wp.organization_id = wmti.organization_id
AND wp.osp_shop_floor_status =
wsc.shop_floor_status_code))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
AND wmti.fm_intraoperation_step_type = WIP_CONSTANTS.TOMOVE
AND wmti.fm_operation_seq_num =
(SELECT wo.operation_seq_num
FROM wip_operations wo
WHERE wo.wip_entity_id = wmti.wip_entity_id
AND wo.organization_id = wmti.organization_id
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1)
AND wo.next_operation_seq_num IS NULL)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
AND wmti.fm_intraoperation_step_type <> WIP_CONSTANTS.TOMOVE
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET wmti.to_operation_seq_num =
(SELECT wo.operation_seq_num
FROM wip_operations wo
WHERE wo.wip_entity_id = wmti.wip_entity_id
AND wo.organization_id = wmti.organization_id
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1)
AND wo.next_operation_seq_num IS NULL)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
AND wmti.to_operation_seq_num IS NULL;
UPDATE wip_move_txn_interface wmti
SET wmti.to_operation_seq_num =
(SELECT MIN(wo.operation_seq_num)
FROM wip_operations wo
WHERE wo.organization_id = wmti.organization_id
AND wo.wip_entity_id = wmti.wip_entity_id
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1)
AND wo.operation_seq_num > wmti.fm_operation_seq_num
AND wo.count_point_type = 1)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.MOVE_TXN
AND wmti.to_operation_seq_num IS NULL;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND (wmti.to_operation_seq_num IS NULL
OR
(NOT EXISTS
(SELECT 'X'
FROM wip_operations wo
WHERE wo.wip_entity_id = wmti.wip_entity_id
AND wo.organization_id = wmti.organization_id
AND wo.operation_seq_num = wmti.to_operation_seq_num
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
AND wmti.to_operation_seq_num <>
(SELECT wo.operation_seq_num
FROM wip_operations wo
WHERE wo.wip_entity_id = wmti.wip_entity_id
AND wo.organization_id = wmti.organization_id
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1)
AND wo.next_operation_seq_num IS NULL)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
/*Bug Bug 4421485*/
SET wmti.to_intraoperation_step_type =
DECODE(wmti.transaction_type,
WIP_CONSTANTS.COMP_TXN,WIP_CONSTANTS.TOMOVE, WIP_CONSTANTS.QUEUE)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
/*Bug Bug 4421485*/
AND wmti.transaction_type IN (WIP_CONSTANTS.COMP_TXN,
WIP_CONSTANTS.MOVE_TXN)
AND wmti.to_intraoperation_step_type IS NULL;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND (wmti.to_intraoperation_step_type IS NULL
OR
(NOT EXISTS
(SELECT 'X'
FROM wip_valid_intraoperation_steps wvis,
wip_operations wo
WHERE wvis.organization_id = wmti.organization_id
AND wvis.step_lookup_type = wmti.to_intraoperation_step_type
AND wo.organization_id = wmti.organization_id
AND wo.wip_entity_id = wmti.wip_entity_id
AND wo.operation_seq_num = wmti.to_operation_seq_num
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1)
-- Fixed bug 5059521. Since OSFM build routing as it goes, we cannot rely on
-- wo.next_operation_seq_num IS NULL to determine that it is the last
-- operation or not.
AND (((wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.REPETITIVE)
OR
(wmti.entity_type = WIP_CONSTANTS.LOTBASED AND
wmti.transaction_type = WIP_CONSTANTS.COMP_TXN))
AND
((wvis.record_creator = 'USER' OR
wvis.step_lookup_type = WIP_CONSTANTS.QUEUE)
OR
(wvis.record_creator = 'SYSTEM' AND
wo.next_operation_seq_num IS NULL)))
OR
(wmti.entity_type = WIP_CONSTANTS.LOTBASED AND
wmti.transaction_type <> WIP_CONSTANTS.COMP_TXN AND
(wvis.record_creator = 'USER' OR
wvis.step_lookup_type = WIP_CONSTANTS.QUEUE))))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.fm_operation_seq_num = wmti.to_operation_seq_num
AND wmti.fm_intraoperation_step_type = wmti.to_intraoperation_step_type
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
AND wmti.to_intraoperation_step_type <> WIP_CONSTANTS.TOMOVE
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
AND EXISTS
(SELECT 'X'
FROM wip_shop_floor_status_codes wsc,
wip_shop_floor_statuses ws
WHERE wsc.organization_id = wmti.organization_id
AND ws.organization_id = wmti.organization_id
AND ws.wip_entity_id = wmti.wip_entity_id
AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
AND ws.operation_seq_num = wmti.to_operation_seq_num
AND ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
AND ws.shop_floor_status_code = wsc.shop_floor_status_code
AND wsc.status_move_flag = WIP_CONSTANTS.NO
AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wip_sf_status.count_no_move_statuses(
wmti.organization_id, -- p_org_id
wmti.wip_entity_id, -- p_wip_id
wmti.line_id, -- p_line_id
wmti.repetitive_schedule_id, -- p_sched_id
wmti.fm_operation_seq_num, -- p_fm_op
wmti.fm_intraoperation_step_type, -- p_fm_step
wmti.to_operation_seq_num, -- p_to_op
wmti.to_intraoperation_step_type, -- p_to_step
-- Fixed bug 2121222
wmti.source_code) > 0 -- p_source_code
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_quantity <= 0
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND NOT EXISTS
(SELECT 'X'
FROM mtl_item_uoms_view miuv
WHERE miuv.organization_id = wmti.organization_id
AND miuv.inventory_item_id = wmti.primary_item_id
AND miuv.uom_code = wmti.transaction_uom)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.overcompletion_transaction_qty IS NOT NULL
AND wmti.overcompletion_transaction_qty <= 0
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.overcompletion_transaction_qty IS NOT NULL
AND wmti.overcompletion_transaction_qty > wmti.transaction_quantity
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN
AND wmti.overcompletion_transaction_qty IS NOT NULL
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.fm_intraoperation_step_type IN (WIP_CONSTANTS.SCRAP,
WIP_CONSTANTS.REJECT)
AND wmti.overcompletion_transaction_qty IS NOT NULL
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND (wmti.to_operation_seq_num < wmti.fm_operation_seq_num OR
(wmti.to_operation_seq_num = wmti.fm_operation_seq_num AND
wmti.to_intraoperation_step_type <
wmti.fm_intraoperation_step_type))
AND wmti.overcompletion_transaction_qty IS NOT NULL
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET wmti.transaction_id = wip_transactions_s.nextval
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_id IS NULL;
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti1.transaction_id, -- transaction_id
l_errMsg, -- error_message
'TRANSACTION_ID', -- error_column
SYSDATE, -- last_update_date
NVL(wmti1.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti1.created_by, -1),
wmti1.last_update_login,
wmti1.request_id,
wmti1.program_application_id,
wmti1.program_id,
wmti1.program_update_date
FROM wip_move_txn_interface wmti1
WHERE wmti1.group_id = g_group_id
AND wmti1.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti1.process_status = WIP_CONSTANTS.RUNNING
AND (EXISTS
(SELECT 'X'
FROM wip_move_transactions wmt
WHERE wmt.transaction_id = wmti1.transaction_id)
OR
(1 <>
(SELECT count(*)
FROM wip_move_txn_interface wmti2
WHERE wmti2.transaction_id = wmti1.transaction_id)));
SELECT wmti.transaction_id txn_id,
wmti.organization_id org_id,
wmti.primary_item_id item_id,
wmti.primary_quantity primary_qty,
DECODE(msik.serial_number_control_code,
WIP_CONSTANTS.FULL_SN, fnd_api.g_true,
WIP_CONSTANTS.DYN_RCV_SN, fnd_api.g_true,
fnd_api.g_false) is_ser_ctrl,
DECODE(msik.lot_control_code,
WIP_CONSTANTS.LOT, fnd_api.g_true,
fnd_api.g_false) is_lot_ctrl,
DECODE(msik.revision_qty_control_code,
WIP_CONSTANTS.REV, fnd_api.g_true,
fnd_api.g_false) is_rev_ctrl,
DECODE(msik.revision_qty_control_code, -- revision
WIP_CONSTANTS.REV, NVL(wdj.bom_revision,
bom_revisions.get_item_revision_fn
('EXCLUDE_OPEN_HOLD', -- eco_status
'ALL', -- examine_type
wmti.organization_id, -- org_id
wmti.primary_item_id, -- item_id
wmti.transaction_date -- rev_date
)),
NULL) revision, -- revision
wdj.lot_number lot,
wmti.transaction_date txn_date,
wdj.completion_subinventory subinv,
wdj.completion_locator_id locID,
mp.negative_inv_receipt_code negative_allow,
msik.concatenated_segments assembly_name
FROM wip_discrete_jobs wdj,
mtl_system_items_kfv msik,
mtl_parameters mp,
wip_move_txn_interface wmti
WHERE wdj.primary_item_id = msik.inventory_item_id
AND wdj.organization_id = msik.organization_id
AND wdj.organization_id = mp.organization_id
AND wmti.wip_entity_id = wdj.wip_entity_id
AND wmti.organization_id = wdj.organization_id
AND wmti.entity_type <> WIP_CONSTANTS.REPETITIVE
AND wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN;
SELECT wmti.transaction_id txn_id,
wmti.organization_id org_id,
wmti.primary_item_id item_id,
wmti.primary_quantity primary_qty,
DECODE(msik.serial_number_control_code,
WIP_CONSTANTS.FULL_SN, fnd_api.g_true,
WIP_CONSTANTS.DYN_RCV_SN, fnd_api.g_true,
fnd_api.g_false) is_ser_ctrl,
DECODE(msik.lot_control_code,
WIP_CONSTANTS.LOT, fnd_api.g_true,
fnd_api.g_false) is_lot_ctrl,
DECODE(msik.revision_qty_control_code,
WIP_CONSTANTS.REV, fnd_api.g_true,
fnd_api.g_false) is_rev_ctrl,
DECODE(msik.revision_qty_control_code, -- revision
WIP_CONSTANTS.REV, NVL(wrs.bom_revision,
bom_revisions.get_item_revision_fn
('EXCLUDE_OPEN_HOLD', -- eco_status
'ALL', -- examine_type
wmti.organization_id, -- org_id
wmti.primary_item_id, -- item_id
wmti.transaction_date -- rev_date
)),
NULL) revision, -- revision
NULL lot,
wmti.transaction_date txn_date,
wri.completion_subinventory subinv,
wri.completion_locator_id locID,
mp.negative_inv_receipt_code negative_allow,
msik.concatenated_segments assembly_name
FROM wip_repetitive_schedules wrs,
wip_repetitive_items wri,
mtl_system_items_kfv msik,
mtl_parameters mp,
wip_move_txn_interface wmti
WHERE wmti.primary_item_id = msik.inventory_item_id
AND wmti.organization_id = msik.organization_id
AND wmti.organization_id = mp.organization_id
AND wrs.wip_entity_id = wmti.wip_entity_id
AND wrs.organization_id = wmti.organization_id
AND wrs.line_id = wmti.line_id
AND wrs.repetitive_schedule_id = wmti.repetitive_schedule_id
AND wri.organization_id = wmti.organization_id
AND wri.wip_entity_id = wmti.wip_entity_id
AND wri.line_id = wmti.line_id
AND wmti.entity_type = WIP_CONSTANTS.REPETITIVE
AND wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.RET_TXN;
/** Bug fix 5000113. primary_quantity should be updated in sync with
* transaction_quantity, and not just when primary_quantity is null.
*/
UPDATE wip_move_txn_interface wmti
SET wmti.primary_quantity =
(SELECT ROUND(wmti.transaction_quantity * mucv.conversion_rate,
WIP_CONSTANTS.INV_MAX_PRECISION)
FROM mtl_uom_conversions_view mucv
WHERE mucv.organization_id = wmti.organization_id
AND mucv.inventory_item_id = wmti.primary_item_id
AND mucv.uom_code = wmti.transaction_uom)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING;
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'PRIMARY_QUANTITY', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.primary_quantity = 0;
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'PRIMARY_QUANTITY', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti,
mtl_uom_conversions_view mucv
WHERE mucv.organization_id = wmti.organization_id
AND mucv.inventory_item_id = wmti.primary_item_id
AND mucv.uom_code = wmti.transaction_uom
AND wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
-- Fixed bug 4900010. Round both transaction_quantity and
-- primary_quantity before making comparison.
AND ROUND(wmti.transaction_quantity * mucv.conversion_rate,
WIP_CONSTANTS.INV_MAX_PRECISION) <>
ROUND(wmti.primary_quantity, WIP_CONSTANTS.INV_MAX_PRECISION);
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'PRIMARY_QUANTITY', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti,
wip_operations wo
WHERE wo.organization_id = wmti.organization_id
AND wo.wip_entity_id = wmti.wip_entity_id
AND NVL(wo.repetitive_schedule_id, -1) =
NVL(wmti.repetitive_schedule_id, -1)
AND wo.operation_seq_num = wmti.fm_operation_seq_num
AND wmti.fm_intraoperation_step_type NOT IN (WIP_CONSTANTS.SCRAP,
WIP_CONSTANTS.REJECT)
AND wmti.to_intraoperation_step_type NOT IN (WIP_CONSTANTS.SCRAP,
WIP_CONSTANTS.REJECT)
AND wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wo.minimum_transfer_quantity > wmti.primary_quantity;
UPDATE wip_move_txn_interface wmti
SET wmti.primary_uom =
(SELECT msi.primary_uom_code
FROM mtl_system_items msi
WHERE msi.organization_id = wmti.organization_id
AND msi.inventory_item_id = wmti.primary_item_id)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.primary_uom IS NULL;
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'PRIMARY_UOM', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti,
mtl_system_items msi
WHERE msi.organization_id = wmti.organization_id
AND msi.inventory_item_id = wmti.primary_item_id
AND wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND msi.primary_uom_code <> wmti.primary_uom;
/** Bug fix 5000113. overcompletion_primary_qty should be updated in sync with
* transaction_quantity, and not just when overcompletion_quantity is null.
*/
UPDATE wip_move_txn_interface wmti
SET wmti.overcompletion_primary_qty =
(SELECT ROUND(wmti.overcompletion_transaction_qty *
mucv.conversion_rate, WIP_CONSTANTS.INV_MAX_PRECISION)
FROM mtl_uom_conversions_view mucv
WHERE mucv.organization_id = wmti.organization_id
AND mucv.inventory_item_id = wmti.primary_item_id
AND mucv.uom_code = wmti.transaction_uom)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.overcompletion_transaction_qty IS NOT NULL;
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'OVERCOMPLETION_PRIMARY_QTY', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.overcompletion_primary_qty = 0;
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'OVERCOMPLETION_PRIMARY_QTY', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti,
mtl_uom_conversions_view mucv
WHERE mucv.organization_id = wmti.organization_id
AND mucv.inventory_item_id = wmti.primary_item_id
AND mucv.uom_code = wmti.transaction_uom
AND wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.overcompletion_transaction_qty IS NOT NULL
-- Fixed bug 4900010. Round both transaction_quantity and
-- primary_quantity before making comparison.
AND ROUND(wmti.overcompletion_transaction_qty * mucv.conversion_rate,
WIP_CONSTANTS.INV_MAX_PRECISION) <>
ROUND(wmti.overcompletion_primary_qty,
WIP_CONSTANTS.INV_MAX_PRECISION);
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'OVERCOMPLETION_TRANSACTION_ID', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.overcompletion_transaction_id IS NOT NULL;
UPDATE wip_move_txn_interface wmti
SET wmti.reason_id =
(SELECT mtr.reason_id
FROM mtl_transaction_reasons mtr
WHERE mtr.reason_name = wmti.reason_name
AND NVL(mtr.disable_date, SYSDATE) >= SYSDATE)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.reason_id IS NULL
AND wmti.reason_name IS NOT NULL;
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'REASON_ID/NAME', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND (wmti.reason_id IS NOT NULL OR wmti.reason_name IS NOT NULL)
AND NOT EXISTS
(SELECT 'X'
FROM mtl_transaction_reasons mtr
WHERE mtr.reason_id = NVL(wmti.reason_id, mtr.reason_id)
AND mtr.reason_name = NVL(wmti.reason_name, mtr.reason_name)
AND NVL(mtr.disable_date, SYSDATE) >= SYSDATE);
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'SCRAP_ACCOUNT_ID', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti,
wip_parameters wp
WHERE wp.organization_id = wmti.organization_id
AND wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND (wmti.fm_intraoperation_step_type = WIP_CONSTANTS.SCRAP OR
wmti.to_intraoperation_step_type = WIP_CONSTANTS.SCRAP)
AND ((wmti.scrap_account_id IS NULL
AND wp.mandatory_scrap_flag = WIP_CONSTANTS.YES
AND wmti.entity_type NOT IN(WIP_CONSTANTS.LOTBASED,
WIP_CONSTANTS.CLOSED_OSFM))
OR
(wmti.scrap_account_id IS NOT NULL AND
NOT EXISTS
(SELECT 'X'
FROM hr_organization_information hoi,
gl_sets_of_books gsob,
gl_code_combinations gcc
WHERE gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
and gsob.set_of_books_id =
to_number(decode(rtrim(translate(
hoi.org_information1,'0123456789',' ')),
null, hoi.org_information1,
-99999))
and (hoi.org_information_context || '') =
'Accounting Information'
AND hoi.organization_id = wmti.organization_id
AND gcc.code_combination_id = wmti.scrap_account_id
AND gcc.detail_posting_allowed_flag = 'Y'
AND gcc.summary_flag = 'N'
and gcc.enabled_flag = 'Y'
AND TRUNC(wmti.transaction_date) BETWEEN
NVL(gcc.start_date_active,
TRUNC(wmti.transaction_date))
AND NVL(gcc.end_date_active,
TRUNC(wmti.transaction_date)))));
PROCEDURE last_updated_by IS
l_errMsg VARCHAR2(240);
UPDATE wip_move_txn_interface wmti
SET wmti.last_updated_by =
(SELECT fu.user_id
FROM fnd_user fu
WHERE fu.user_name = wmti.last_updated_by_name
AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date, SYSDATE))
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.last_updated_by IS NULL
AND wmti.last_updated_by_name IS NOT NULL;
fnd_message.set_token('ENTITY1', 'LAST_UPDATED_BY');
fnd_message.set_token('ENTITY2', 'LAST_UPDATED_BY_NAME');
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'LAST_UPDATED_BY/BY_NAME', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.last_updated_by IS NULL; -- cannot derive LAST_UPDATED_BY
END last_updated_by;
UPDATE wip_move_txn_interface wmti
SET wmti.created_by =
(SELECT fu.user_id
FROM fnd_user fu
WHERE fu.user_name = wmti.created_by_name
AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date, SYSDATE))
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.created_by IS NULL
AND wmti.created_by_name IS NOT NULL;
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'CREATED_BY/BY_NAME', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND ((wmti.created_by IS NULL) -- cannot derive LAST_UPDATED_BY
OR
(NOT EXISTS
(SELECT 'X'
FROM fnd_user fu
WHERE fu.user_name = NVL(wmti.created_by_name,
fu.user_name)
AND fu.user_id = wmti.created_by
AND SYSDATE BETWEEN fu.start_date AND
NVL(fu.end_date, SYSDATE))));
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'TO_OP_SEQ_NUM/CREATED_BY', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND EXISTS
(SELECT 'X'
FROM bom_departments bd,
wip_operation_resources wor,
wip_operations wo1,
wip_operations wo2
WHERE wor.organization_id = wmti.organization_id
AND wor.wip_entity_id = wmti.wip_entity_id
AND wor.operation_seq_num = wmti.to_operation_seq_num
AND wmti.fm_operation_seq_num < wmti.to_operation_seq_num
AND wmti.to_intraoperation_step_type = WIP_CONSTANTS.QUEUE
AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
OR
(wmti.entity_type = WIP_CONSTANTS.REPETITIVE AND
wor.repetitive_schedule_id IN
(SELECT wrs.repetitive_schedule_id
FROM wip_repetitive_schedules wrs
WHERE wrs.wip_entity_id = wmti.wip_entity_id
AND wrs.organization_id = wmti.organization_id
AND wrs.line_id = wmti.line_id
AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG))))
AND wo1.organization_id = wor.organization_id
AND wo1.wip_entity_id = wor.wip_entity_id
AND NVL(wo1.repetitive_schedule_id,-1) =
NVL(wor.repetitive_schedule_id,-1)
AND wo1.operation_seq_num = wor.operation_seq_num
AND wo2.organization_id = wo1.organization_id
AND wo2.wip_entity_id = wo1.wip_entity_id
AND NVL(wo2.repetitive_schedule_id,-1) =
NVL(wo1.repetitive_schedule_id,-1)
AND ((wor.autocharge_type = WIP_CONSTANTS.PO_RECEIPT AND
wo2.operation_seq_num = wor.operation_seq_num)
OR
(wor.autocharge_type = WIP_CONSTANTS.PO_MOVE AND
((wo1.next_operation_seq_num IS NOT NULL AND
wo1.next_operation_seq_num = wo2.operation_seq_num)
OR
(wo1.next_operation_seq_num IS NULL AND
wo2.operation_seq_num = wor.operation_seq_num))))
AND bd.organization_id = wmti.organization_id
AND wo2.department_id = bd.department_id
AND bd.location_id IS NULL);
INSERT INTO wip_txn_interface_errors(
transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT wmti.transaction_id, -- transaction_id
l_errMsg, -- error_message
'TO_OP_SEQ_NUM/CREATED_BY', -- error_column
SYSDATE, -- last_update_date
NVL(wmti.last_updated_by, -1),
SYSDATE, -- creation_date
NVL(wmti.created_by, -1),
wmti.last_update_login,
wmti.request_id,
wmti.program_application_id,
wmti.program_id,
wmti.program_update_date
FROM wip_move_txn_interface wmti
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND EXISTS
(SELECT 'Outside processing resources exist'
FROM wip_operation_resources wor
WHERE wor.organization_id = wmti.organization_id
AND wor.wip_entity_id = wmti.wip_entity_id
AND wor.operation_seq_num = wmti.to_operation_seq_num
AND wmti.fm_operation_seq_num < wmti.to_operation_seq_num
AND wmti.to_intraoperation_step_type = WIP_CONSTANTS.QUEUE
AND wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
WIP_CONSTANTS.PO_MOVE)
AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
OR
(wmti.entity_type = WIP_CONSTANTS.REPETITIVE AND
wor.repetitive_schedule_id IN
(SELECT wrs.repetitive_schedule_id
FROM wip_repetitive_schedules wrs
WHERE wrs.organization_id = wmti.organization_id
AND wrs.wip_entity_id = wmti.wip_entity_id
AND wrs.line_id = wmti.line_id
AND wrs.status_type IN (WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG)))))
AND NOT EXISTS
(SELECT 'Current user is an employee'
FROM fnd_user fu,
per_people_f ppf
WHERE fu.user_id = wmti.created_by
AND fu.employee_id = ppf.person_id);
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND EXISTS -- serialized job
(SELECT 'X'
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND wdj.serialization_start_op IS NOT NULL
AND (-- Forward move
(wmti.fm_operation_seq_num < wdj.serialization_start_op AND
(wmti.to_operation_seq_num > wdj.serialization_start_op
OR
(wmti.to_operation_seq_num = wdj.serialization_start_op AND
wmti.to_intraoperation_step_type <> WIP_CONSTANTS.QUEUE)))
OR
-- Backward move
(wmti.to_operation_seq_num < wdj.serialization_start_op AND
(wmti.fm_operation_seq_num > wdj.serialization_start_op
OR
(wmti.fm_operation_seq_num = wdj.serialization_start_op AND
wmti.fm_intraoperation_step_type <> WIP_CONSTANTS.QUEUE)))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND EXISTS -- serialized discrete job and serialized transaction
(SELECT 'X'
FROM wip_discrete_jobs wdj,
wip_entities we
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND wdj.wip_entity_id = we.wip_entity_id
AND we.entity_type = WIP_CONSTANTS.DISCRETE
AND wdj.serialization_start_op IS NOT NULL
AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
AND wmti.to_operation_seq_num >= wdj.serialization_start_op
AND wmti.primary_quantity <> 1)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND EXISTS -- regular job
(SELECT 'X'
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND (wdj.serialization_start_op IS NULL -- non-serialized job
OR -- serialized job with non-serialized move
(wdj.serialization_start_op IS NOT NULL
AND
(wmti.fm_operation_seq_num < wdj.serialization_start_op OR
(wmti.fm_operation_seq_num = wdj.serialization_start_op AND
wmti.fm_intraoperation_step_type = WIP_CONSTANTS.QUEUE))
AND
(wmti.to_operation_seq_num < wdj.serialization_start_op OR
(wmti.to_operation_seq_num = wdj.serialization_start_op AND
wmti.to_intraoperation_step_type = WIP_CONSTANTS.QUEUE))))
)
AND EXISTS
(SELECT 'X'
FROM wip_serial_move_interface wsmi
WHERE wsmi.transaction_id = wmti.transaction_id)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND EXISTS -- serialized job and serialized transaction
(SELECT 'X'
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND wdj.serialization_start_op IS NOT NULL
AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
AND wmti.to_operation_seq_num >= wdj.serialization_start_op)
AND wmti.primary_quantity <>
(SELECT COUNT(*)
FROM wip_serial_move_interface wsmi,
mtl_serial_numbers msn
WHERE wsmi.transaction_id = wmti.transaction_id
AND wsmi.assembly_serial_number = msn.serial_number
AND wmti.organization_id = msn.current_organization_id
AND wmti.primary_item_id = msn.inventory_item_id
AND msn.wip_entity_id IS NOT NULL
AND msn.wip_entity_id = wmti.wip_entity_id)
AND wmti.primary_quantity <>
(SELECT COUNT(*)
FROM wip_serial_move_interface wsmi,
wip_entities we,
mtl_serial_numbers msn,
mtl_object_genealogy mog
WHERE wsmi.transaction_id = wmti.transaction_id
AND wsmi.assembly_serial_number = msn.serial_number
AND wmti.organization_id = msn.current_organization_id
AND wmti.primary_item_id = msn.inventory_item_id
AND msn.current_status = WIP_CONSTANTS.IN_STORES
AND wmti.wip_entity_id = we.wip_entity_id
AND ((mog.genealogy_origin = 1 AND
mog.parent_object_id = we.gen_object_id AND
mog.object_id = msn.gen_object_id)
OR
(mog.genealogy_origin = 2 AND
mog.parent_object_id = msn.gen_object_id AND
mog.object_id = we.gen_object_id))
AND mog.end_date_active IS NULL)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND EXISTS -- serialized job and serialized transaction
(SELECT 'X'
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wmti.wip_entity_id
AND wdj.serialization_start_op IS NOT NULL
AND wmti.fm_operation_seq_num >= wdj.serialization_start_op
AND wmti.to_operation_seq_num >= wdj.serialization_start_op)
AND NOT EXISTS
(SELECT 'X'
FROM wip_serial_move_interface wsmi,
mtl_serial_numbers msn
WHERE wsmi.transaction_id = wmti.transaction_id
AND wsmi.assembly_serial_number = msn.serial_number
AND wmti.organization_id = msn.current_organization_id
AND wmti.primary_item_id = msn.inventory_item_id
AND msn.line_mark_id IS NULL
AND ((wmti.transaction_type = WIP_CONSTANTS.RET_TXN AND
msn.group_mark_id IS NULL AND
msn.wip_entity_id IS NULL AND
msn.current_status = WIP_CONSTANTS.IN_STORES)
OR
(wmti.transaction_type IN (WIP_CONSTANTS.MOVE_TXN,
WIP_CONSTANTS.COMP_TXN) AND
msn.group_mark_id IS NOT NULL AND
msn.wip_entity_id IS NOT NULL AND
wmti.wip_entity_id = msn.wip_entity_id AND
-- Define but not use or Issue out of store.
msn.current_status IN (WIP_CONSTANTS.DEF_NOT_USED,
WIP_CONSTANTS.OUT_OF_STORES))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
PROCEDURE update_interface_tbl IS
BEGIN
-- there are some errors occur, so set the process_status to error so that
-- move processor will not pick up this record
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND EXISTS
(SELECT 'X'
FROM wip_txn_interface_errors wtie
WHERE wtie.transaction_id = wmti.transaction_id);
END update_interface_tbl;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type IN (WIP_CONSTANTS.RET_TXN,
WIP_CONSTANTS.COMP_TXN)
AND EXISTS -- Item flag was not set properly.
(SELECT 'X'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = wmti.primary_item_id
AND msi.organization_id = wmti.organization_id
AND (msi.inventory_item_flag = 'N' OR
msi.mtl_transactions_enabled_flag = 'N'))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
UPDATE wip_move_txn_interface wmti
SET (wmti.fm_operation_code,
wmti.fm_department_id,
wmti.fm_department_code,
wmti.to_operation_code,
wmti.to_department_id,
wmti.to_department_code,
wmti.process_phase) =
(SELECT bso1.operation_code,
wo1.department_id,
bd1.department_code,
bso2.operation_code,
wo2.department_id,
bd2.department_code,
WIP_CONSTANTS.MOVE_PROC
FROM bom_standard_operations bso1,
bom_standard_operations bso2,
bom_departments bd1,
bom_departments bd2,
wip_operations wo1,
wip_operations wo2
WHERE wo1.organization_id = wmti.organization_id
AND wo1.wip_entity_id = wmti.wip_entity_id
AND wo1.operation_seq_num = wmti.fm_operation_seq_num
AND wo2.organization_id = wmti.organization_id
AND wo2.wip_entity_id = wmti.wip_entity_id
AND wo2.operation_seq_num = wmti.to_operation_seq_num
/* Standard operation ID is optional, so we should use outer join */
AND bso1.standard_operation_id(+) = wo1.standard_operation_id
AND bso2.standard_operation_id(+) = wo2.standard_operation_id
AND wo1.department_id = bd1.department_id
AND wo2.department_id = bd2.department_id
AND (wmti.entity_type IN (WIP_CONSTANTS.DISCRETE,
WIP_CONSTANTS.LOTBASED)
OR
(wmti.entity_type = WIP_CONSTANTS.REPETITIVE AND
wo1.repetitive_schedule_id = wmti.repetitive_schedule_id AND
wo2.repetitive_schedule_id = wmti.repetitive_schedule_id)))
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING;
enums.delete;
last_updated_by;
update_interface_tbl;
select organization_id,
wip_entity_id
into l_org_id,
l_wip_entity_id
from wip_move_transactions
where transaction_id = p_move_id;
select wdj.primary_item_id
into l_assm_item_id
from wip_discrete_jobs wdj
where wdj.organization_id = l_org_id
and wdj.wip_entity_id = l_wip_entity_id;
select distinct lu.meaning
into p_txn_type
from mfg_lookups lu,
mtl_material_transactions mmt
where mmt.move_transaction_id = p_move_id
and mmt.organization_id = l_org_id
and mmt.transaction_source_id = l_wip_entity_id
and mmt.inventory_item_id = l_assm_item_id
and mmt.transaction_type_id in (wip_constants.CPLASSY_TYPE, wip_constants.RETASSY_TYPE)
and lu.lookup_type = 'WIP_MOVE_TRANSACTION_TYPE'
and lu.lookup_code = decode(mmt.transaction_type_id, wip_constants.CPLASSY_TYPE, wip_constants.comp_txn, wip_constants.RETASSY_TYPE, wip_constants.ret_txn);
select meaning
into p_txn_type
from mfg_lookups
where lookup_type = 'WIP_MOVE_TRANSACTION_TYPE'
and lookup_code = wip_constants.move_txn;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.acct_period_id =
(SELECT oap.acct_period_id
FROM org_acct_periods oap
WHERE oap.organization_id = wmti.organization_id
-- modified the statement below for timezone project in J
AND TRUNC(inv_le_timezone_pub.get_le_day_for_inv_org(
wmti.transaction_date, -- p_trxn_date
wmti.organization_id -- p_inv_org_id
)) BETWEEN
oap.period_start_date AND oap.schedule_close_date)
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND (wmti.acct_period_id IS NULL
OR
NOT EXISTS
(SELECT 'X'
FROM wip_period_balances wpb
WHERE wpb.acct_period_id = wmti.acct_period_id
AND wpb.wip_entity_id = wmti.wip_entity_id
AND wpb.organization_id = wmti.organization_id))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND EXISTS
(SELECT 'X'
FROM wip_shop_floor_status_codes wsc,
wip_shop_floor_statuses ws
WHERE wsc.organization_id = wmti.organization_id
AND ws.organization_id = wmti.organization_id
AND ws.wip_entity_id = wmti.wip_entity_id
AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
AND ws.operation_seq_num = wmti.fm_operation_seq_num
AND ws.intraoperation_step_type = wmti.fm_intraoperation_step_type
AND ws.shop_floor_status_code = wsc.shop_floor_status_code
AND wsc.status_move_flag = WIP_CONSTANTS.NO
AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE
AND (wmti.source_code IS NULL OR
wmti.source_code <> 'RCV' OR
(wmti.source_code = 'RCV' AND
NOT EXISTS
(SELECT 'X'
FROM wip_parameters wp
WHERE wp.organization_id = wmti.organization_id
AND wp.osp_shop_floor_status =
wsc.shop_floor_status_code))))
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wmti.transaction_type = WIP_CONSTANTS.COMP_TXN
AND EXISTS
(SELECT 'X'
FROM wip_shop_floor_status_codes wsc,
wip_shop_floor_statuses ws
WHERE wsc.organization_id = wmti.organization_id
AND ws.organization_id = wmti.organization_id
AND ws.wip_entity_id = wmti.wip_entity_id
AND (wmti.line_id IS NULL OR ws.line_id = wmti.line_id)
AND ws.operation_seq_num = wmti.to_operation_seq_num
AND ws.intraoperation_step_type = WIP_CONSTANTS.TOMOVE
AND ws.shop_floor_status_code = wsc.shop_floor_status_code
AND wsc.status_move_flag = WIP_CONSTANTS.NO
AND NVL(wsc.disable_date, SYSDATE + 1) > SYSDATE)
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
enums.delete;
UPDATE wip_move_txn_interface wmti
SET wmti.process_status = WIP_CONSTANTS.ERROR
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING
AND wip_sf_status.count_no_move_statuses(
wmti.organization_id, -- p_org_id
wmti.wip_entity_id, -- p_wip_id
wmti.line_id, -- p_line_id
wmti.repetitive_schedule_id, -- p_sched_id
wmti.fm_operation_seq_num, -- p_fm_op
wmti.fm_intraoperation_step_type, -- p_fm_step
wmti.to_operation_seq_num, -- p_to_op
wmti.to_intraoperation_step_type, -- p_to_step
-- Fixed bug 2121222
wmti.source_code) > 0 -- p_source_code
RETURNING wmti.transaction_id BULK COLLECT INTO enums;
update_interface_tbl;
UPDATE wip_move_txn_interface wmti
SET process_phase = WIP_CONSTANTS.MOVE_PROC
WHERE wmti.group_id = g_group_id
AND wmti.process_phase = WIP_CONSTANTS.MOVE_VAL
AND wmti.process_status = WIP_CONSTANTS.RUNNING;