[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_cartonization_id (
p_lpn_id IN NUMBER,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2)
IS
l_api_name VARCHAR2(30) := 'insert_cartonization_id';
mydebug('Unknown Exception occurred: ' || SQLERRM,'insert_cartonization_id');
g_cartonization_ids.DELETE;
UPDATE wms_dispatched_tasks
SET status = 9
WHERE transaction_temp_id = p_transaction_temp_id;
select lot_number
from mtl_transaction_lots_temp
where transaction_temp_id = p_temp_id;
select primary_quantity, transaction_quantity, serial_transaction_temp_id
from mtl_transaction_lots_temp
where transaction_temp_id = p_temp_id
and lot_number = p_lot;
SELECT msnt.fm_serial_number,
msnt.to_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_transaction_temp_id;
SELECT msnt.fm_serial_number, msnt.to_serial_number
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND mtlt.transaction_temp_id = p_transaction_temp_id;
SELECT mmtt.transaction_temp_id
, wdt.task_id
, mmtt.organization_id
, mmtt.inventory_item_id
, mmtt.cartonization_id
, mmtt.allocated_lpn_id
, mmtt.transaction_action_id
, DECODE (mmtt.parent_line_id, mmtt.transaction_temp_id, 'Y', 'N')
, mmtt.serial_allocated_flag -- Bug 4434111
INTO l_transaction_temp_id
, l_task_id
, l_org_id
, l_inventory_item_id
, l_cartonization_id
, l_allocated_lpn_id
, l_transaction_action_id
, l_is_bulk_picked_task
, l_serial_alloc_flag -- Bug 4434111
FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
WHERE wdt.person_id = p_sign_on_emp_id
AND wdt.organization_id = p_sign_on_org_id
AND wdt.status <= 3
AND wdt.task_type IN (1, 4, 5, 6)
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.transaction_temp_id = p_transaction_temp_id;
SELECT tt.transaction_temp_id
, tt.task_id
, tt.organization_id
, tt.inventory_item_id
, tt.cartonization_id
, tt.allocated_lpn_id
, tt.serial_allocated_flag -- Bug 4434111
INTO l_transaction_temp_id
, l_task_id
, l_org_id
, l_inventory_item_id
, l_cartonization_id
, l_allocated_lpn_id
, l_serial_alloc_flag -- Bug 4434111
FROM
(
SELECT mmtt.transaction_temp_id transaction_temp_id
, wdt.task_id task_id
, mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.cartonization_id cartonization_id
, mmtt.allocated_lpn_id allocated_lpn_id
, mmtt.serial_allocated_flag serial_allocated_flag
FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
WHERE wdt.person_id = p_sign_on_emp_id
AND wdt.organization_id = p_sign_on_org_id
AND wdt.status <= 3
AND (wdt.task_type IN (1, 4, 5, 6))
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.cartonization_id = p_cartonization_id
AND sub.organization_id = mmtt.organization_id
AND sub.secondary_inventory_name = mmtt.subinventory_code
AND loc.organization_id = mmtt.organization_id
AND loc.inventory_location_id = mmtt.locator_id
ORDER BY wdt.priority, sub.picking_order, -- for task resequencing in the pick load page
loc.picking_order, wdt.task_id
) tt
WHERE ROWNUM < 2;
SELECT tt.transaction_temp_id
, tt.task_id
, tt.organization_id
, tt.inventory_item_id
, tt.cartonization_id
, tt.allocated_lpn_id
, tt.serial_allocated_flag -- Bug 4434111
INTO l_transaction_temp_id
, l_task_id
, l_org_id
, l_inventory_item_id
, l_cartonization_id
, l_allocated_lpn_id
, l_serial_alloc_flag -- Bug 4434111
FROM
(
SELECT mmtt.transaction_temp_id transaction_temp_id
, wdt.task_id task_id
, mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.cartonization_id cartonization_id
, mmtt.allocated_lpn_id allocated_lpn_id
, mmtt.serial_allocated_flag serial_allocated_flag
FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
WHERE wdt.person_id = p_sign_on_emp_id
AND wdt.organization_id = p_sign_on_org_id
AND wdt.status <= 3
AND (wdt.task_type IN (1, 4, 5, 6))
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.device_id = p_device_id
AND wdt.device_invoked = 'Y'
AND sub.organization_id = mmtt.organization_id
AND sub.secondary_inventory_name = mmtt.subinventory_code
AND loc.organization_id = mmtt.organization_id
AND loc.inventory_location_id = mmtt.locator_id
ORDER BY wdt.priority, sub.picking_order, -- for task resequencing in the pick load page
loc.picking_order, wdt.task_id
)tt
WHERE ROWNUM < 2;
SELECT tt.transaction_temp_id
, tt.task_id
, tt.organization_id
, tt.inventory_item_id
, tt.cartonization_id
, tt.allocated_lpn_id
, tt.serial_allocated_flag -- Bug 4434111
INTO l_transaction_temp_id
, l_task_id
, l_org_id
, l_inventory_item_id
, l_cartonization_id
, l_allocated_lpn_id
, l_serial_alloc_flag -- Bug 4434111
FROM
(
SELECT mmtt.transaction_temp_id transaction_temp_id
, wdt.task_id task_id
, mmtt.organization_id organization_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.cartonization_id cartonization_id
, mmtt.allocated_lpn_id allocated_lpn_id
, mmtt.serial_allocated_flag serial_allocated_flag
FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
WHERE wdt.person_id = p_sign_on_emp_id
AND wdt.organization_id = p_sign_on_org_id
AND wdt.status <= 3
AND (wdt.task_type IN (1, 4, 5, 6))
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND sub.organization_id = mmtt.organization_id
AND sub.secondary_inventory_name = mmtt.subinventory_code
AND loc.organization_id = mmtt.organization_id
AND loc.inventory_location_id = mmtt.locator_id
ORDER BY wdt.priority, sub.picking_order, -- for task resequencing in the pick load page
loc.picking_order, wdt.task_id
)tt
WHERE ROWNUM < 2;
UPDATE mtl_material_transactions_temp mmtt
SET posting_flag = 'N'
WHERE parent_line_id = p_transaction_temp_id
AND parent_line_id <> transaction_temp_id;
SELECT serial_number_control_code
, lot_control_code
INTO l_serial_number_control_code
, l_lot_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_org_id;
SELECT msnt.transaction_temp_id
INTO l_serial_temp_id
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_transaction_temp_id
AND ROWNUM < 2;
SELECT msnt.transaction_temp_id
INTO l_serial_temp_id
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, -1)
AND mtlt.transaction_temp_id = l_transaction_temp_id
AND ROWNUM < 2;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL,
last_update_date = Sysdate,
last_updated_by = l_user_id
WHERE serial_number >= l_fm_serial_number
AND serial_number <= l_to_serial_number
AND inventory_item_id = l_inventory_item_id
AND current_organization_id = l_org_id;
mydebug('Rows updated ' || sql%rowcount, l_api_name);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_transaction_temp_id;
mydebug('Rows deleted ' || sql%rowcount, l_api_name);
UPDATE mtl_serial_numbers
SET group_mark_id = NULL,
last_update_date = Sysdate,
last_updated_by = l_user_id
WHERE serial_number >= l_fm_serial_number
AND serial_number <= l_to_serial_number
AND inventory_item_id = l_inventory_item_id
AND current_organization_id = l_org_id;
mydebug('Rows updated ' || sql%rowcount, l_api_name);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(
SELECT serial_transaction_temp_id
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND mtlt.transaction_temp_id = l_transaction_temp_id
);
mydebug('Rows deleted ' || sql%rowcount, l_api_name);
select count(*),sum(primary_quantity),sum(transaction_quantity)
into l_same_lot_rec_cnt, l_sum_same_lot_pri_qty, l_sum_same_lot_tra_qty
from mtl_transaction_lots_temp
where transaction_temp_id = l_transaction_temp_id
and lot_number = l_lot;
select *
into l_mtlt_rec
from mtl_transaction_lots_temp
where transaction_temp_id = l_transaction_temp_id
and lot_number = l_lot
and rownum = 1;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_temp_id
FROM dual;
UPDATE mtl_serial_numbers
SET group_mark_id = l_new_serial_temp_id
WHERE current_organization_id = l_org_id
AND inventory_item_id = l_inventory_item_id
AND serial_number in (select fm_serial_number
from mtl_serial_numbers_temp
where transaction_temp_id = l_serial_temp_id);
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_new_serial_temp_id
WHERE transaction_temp_id = l_serial_temp_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_temp_id
FROM dual;
select sum(primary_quantity), sum(transaction_quantity)
into l_sum_same_lot_pri_qty, l_sum_same_lot_tra_qty
from mtl_transaction_lots_temp
where transaction_temp_id = l_transaction_temp_id
and lot_number = l_lot;
mydebug(' consolidate mtlt 55: Inserting into MTLT', l_api_name);
delete mtl_transaction_lots_temp
where transaction_temp_id = l_transaction_temp_id
and lot_number = l_lot;
inv_rcv_common_apis.insert_mtlt(l_mtlt_rec);
select count(*)
into l_same_lot_rec_cnt
from mtl_transaction_lots_temp
where transaction_temp_id = l_transaction_temp_id
and lot_number = l_lot;
SELECT wlpn1.license_plate_number
, wlpn2.license_plate_number
INTO l_allocated_lpn_name
, l_allocated_outermost_lpn_name
FROM wms_license_plate_numbers wlpn1, wms_license_plate_numbers wlpn2
WHERE wlpn1.lpn_id = l_allocated_lpn_id
AND wlpn1.outermost_lpn_id = wlpn2.lpn_id;
SELECT license_plate_number
INTO l_carton_name
FROM wms_license_plate_numbers
WHERE lpn_id = l_cartonization_id;
SELECT l.license_plate_number lpn
, l.inventory_item_id itemid
, k.concatenated_segments item
INTO l_carton_name
, l_carton_item_id
, l_carton_item_name
FROM wms_license_plate_numbers l, mtl_system_items_vl k /* Bug 5581528 */
WHERE l.lpn_id = l_cartonization_id
AND l.inventory_item_id = k.inventory_item_id
AND l.organization_id = k.organization_id;
SELECT
wda.delivery_id,mtrl.carton_grouping_id
INTO l_delivery_id,l_carton_grouping_id
FROM
mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
, wsh_delivery_details wdd
, wsh_delivery_assignments_v wda
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
AND mmtt.move_order_line_id = mtrl.line_id
AND wdd.move_order_line_id = mtrl.line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.released_status='S';
SELECT
mtrl.carton_grouping_id
INTO l_carton_grouping_id
FROM
mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
AND mmtt.move_order_line_id = mtrl.line_id;
SELECT DECODE (parent_line_id, transaction_temp_id, 'Y', 'N')
INTO l_is_bulk_picked_task
FROM mtl_material_transactions_temp mmtt
WHERE transaction_temp_id = l_transaction_temp_id;
SELECT mtrh.move_order_type
INTO l_move_order_type
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
AND mmtt.transaction_temp_id = l_transaction_temp_id;
SELECT OVPK_TRANSFER_ORDERS_ENABLED
INTO l_temp
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT wip_overpick_enabled
INTO l_temp
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT 'C'
INTO l_item_type
FROM mtl_material_transactions_temp mmtt,
mtl_cross_references mcr
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
AND mmtt.inventory_item_id = mcr.inventory_item_id
AND mcr.cross_reference_type = g_gtin_cross_ref_type
AND (mcr.organization_id = mmtt.organization_id
OR mcr.org_independent_flag = 'Y')
AND rownum = 1;
SELECT wutta.honor_case_pick_flag, pgvl.template_name into l_honor_case_pick_flag, l_template_name
FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta , wms_page_templates_tl pgtl, WMS_PAGE_TEMPLATES_VL pgvl
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
AND mmtt.standard_operation_id = wutta.user_task_type_id
AND mmtt.organization_id = wutta.organization_id
AND pgtl.template_id = wutta.pick_load_page_template_id
AND pgtl.template_id = pgvl.template_id
AND pgtl.language = userenv('LANG');
SELECT mmtt.cartonization_id
, mmtt.container_item_id
, mmtt.inventory_item_id
, mmtt.lot_number
, mmtt.revision
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mmtt.locator_id locator_id
, mmtt.subinventory_code
, inv_project.get_locsegs(mmtt.locator_id, mmtt.organization_id) loc
-- 11
, msik.concatenated_segments item
, mmtt.transaction_temp_id
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
, NVL(msik.lot_control_code, 1) lot_code
, NVL(msik.serial_number_control_code, 1) serial_code
, mmtt.transaction_type_id
, NVL(msik.restrict_subinventories_code, 2) subrest
, NVL(msik.restrict_locators_code, 2) locrest
, NVL(msik.location_control_code, 1) loccode
--21
, msik.primary_uom_code
, NVL(msik.allowed_units_lookup_code, 2) allunits
, NVL(revision_qty_control_code, 1) revcode
, wdt.task_id
, mmtt.cost_group_id
, mmtt.transaction_header_id
, mp.allocate_serial_flag
, mtrl.txn_source_id
, mmtt.wip_entity_type
, wdt.task_type
--31
, mmtt.transaction_source_type_id
, NVL(mmtt.allocated_lpn_id, 0)
, mmtt.pick_slip_number
, inv_project.get_project_id
, inv_project.get_task_id
, inv_project.get_project_number
, inv_project.get_task_number
, mmtt.transaction_action_id
, wdt.device_request_id
, l_sl_alloc_flag
--41
, l_serial_temp_id
, l_allocated_lpn_name
, l_allocated_outermost_lpn_name
, l_carton_name
, l_carton_item_id
, l_carton_item_name
, l_nbr_tasks
, l_cluster_key cluster_key -- patchset J APL changed cluster id to cluster key
, mmtt.parent_line_id
, msi.lpn_controlled_flag
--51
, msik.tracking_quantity_ind
, msik.ont_pricing_qty_source
, msik.secondary_default_ind
, msik.secondary_uom_code
, msik.dual_uom_deviation_high
, msik.dual_uom_deviation_low
, mmtt.trx_source_line_id
, l_check_overpick_passed --OVPK
, Sysdate
, mp.negative_inv_receipt_code
--61
, l_item_type
, msik.description
, inv_ui_item_lovs.get_conversion_rate(mmtt.transaction_uom,
mmtt.organization_id,
mmtt.inventory_item_id)
-- Bug# 4141928
-- For OPM convegence
-- Fetching the sec txn qty and the additional Item attributes
--64
, mmtt.secondary_uom_code
, mmtt.secondary_transaction_quantity
, nvl(msik.lot_divisible_flag,'Y')
/* Added for LMS project */
, wdt.user_task_type
, mmtt.operation_plan_id
/* end for LMS project */
, l_honor_case_pick_flag --69
, l_template_name --70
FROM wms_dispatched_tasks wdt
, mtl_material_transactions_temp mmtt
, mtl_system_items_vl msik /* Bug 5581528 */
, mtl_parameters mp
, mtl_txn_request_lines mtrl
, mtl_secondary_inventories msi
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mp.organization_id = wdt.organization_id
AND mmtt.organization_id = msik.organization_id
AND mmtt.inventory_item_id = msik.inventory_item_id
AND mmtt.move_order_line_id = mtrl.line_id (+)
AND mmtt.subinventory_code = msi.secondary_inventory_name
AND mmtt.organization_id = msi.organization_id;
SELECT mmtt.cartonization_id
, mmtt.container_item_id
, mmtt.inventory_item_id
, mmtt.lot_number
, mmtt.revision
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mmtt.locator_id locator_id
, mmtt.subinventory_code
, inv_project.get_locsegs(mmtt.locator_id, mmtt.organization_id) loc
-- 11
, msik.concatenated_segments item
, mmtt.transaction_temp_id
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
, NVL(msik.lot_control_code, 1) lot_code
, NVL(msik.serial_number_control_code, 1) serial_code
, mmtt.transaction_type_id
, NVL(msik.restrict_subinventories_code, 2) subrest
, NVL(msik.restrict_locators_code, 2) locrest
, NVL(msik.location_control_code, 1) loccode
--21
, msik.primary_uom_code
, NVL(msik.allowed_units_lookup_code, 2) allunits
, NVL(revision_qty_control_code, 1) revcode
, wdt.task_id
, mmtt.cost_group_id
, mmtt.transaction_header_id
, mp.allocate_serial_flag
, mtrl.txn_source_id
, mmtt.wip_entity_type
, wdt.task_type
--31
, mmtt.transaction_source_type_id
, NVL(mmtt.allocated_lpn_id, 0)
, mmtt.pick_slip_number
, inv_project.get_project_id
, inv_project.get_task_id
, inv_project.get_project_number
, inv_project.get_task_number
, mmtt.transaction_action_id
, wdt.device_request_id
, l_sl_alloc_flag
--41
, l_serial_temp_id
, l_allocated_lpn_name
, l_allocated_outermost_lpn_name
, l_carton_name
, l_carton_item_id
, l_carton_item_name
, l_nbr_tasks
, l_cluster_key cluster_key -- patchset J APL changed cluster id to cluster key
, mmtt.parent_line_id
, msi.lpn_controlled_flag
--51
, msik.tracking_quantity_ind
, msik.ont_pricing_qty_source
, msik.secondary_default_ind
, msik.secondary_uom_code
, msik.dual_uom_deviation_high
, msik.dual_uom_deviation_low
, mmtt.trx_source_line_id
, l_check_overpick_passed --OVPK
, Sysdate
, mp.negative_inv_receipt_code
--61
, l_item_type
, msik.description
, inv_ui_item_lovs.get_conversion_rate(mmtt.transaction_uom,
mmtt.organization_id,
mmtt.inventory_item_id)
-- Bug# 4141928
-- For OPM convegence
-- Fetching the sec txn qty and the additional Item attributes
--64
, mmtt.secondary_uom_code
, mmtt.secondary_transaction_quantity
, nvl(msik.lot_divisible_flag,'Y')
/* Added for LMS project */
, wdt.user_task_type
, mmtt.operation_plan_id
/* end for LMS project */
FROM wms_dispatched_tasks wdt
, mtl_material_transactions_temp mmtt
, mtl_system_items_vl msik /* Bug 5581528 */
, mtl_parameters mp
, mtl_txn_request_lines mtrl
, mtl_secondary_inventories msi
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mp.organization_id = wdt.organization_id
AND mmtt.organization_id = msik.organization_id
AND mmtt.inventory_item_id = msik.inventory_item_id
AND mmtt.move_order_line_id = mtrl.line_id (+)
AND mmtt.subinventory_code = msi.secondary_inventory_name
AND mmtt.organization_id = msi.organization_id;
SELECT transaction_temp_id, transaction_header_id, lpn_id, transfer_lpn_id
FROM mtl_material_transactions_temp t1
WHERE transfer_lpn_id = p_lpn_id
AND organization_id = p_org_id
AND parent_line_id IS NULL
AND EXISTS(
SELECT 1
FROM mtl_material_transactions_temp t2
WHERE t2.parent_line_id = t1.transaction_temp_id
AND t2.organization_id = t1.organization_id
);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = v_rec.transaction_header_id
, transfer_lpn_id = v_rec.transfer_lpn_id
, lpn_id = v_rec.lpn_id
, parent_line_id = NULL
WHERE parent_line_id = v_rec.transaction_temp_id
AND organization_id = p_organization_id;
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = v_rec.transaction_temp_id;
SELECT DECODE (mtrh.move_order_type
, inv_globals.g_move_order_mfg_pick, 'MFG'
, 'OTHERS')
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mtrh.header_id = mtrl.header_id
AND rownum = 1;
SELECT nvl(mmtt.transfer_subinventory,0) transfer_subinventory
, nvl(mmtt.transfer_to_location,0) transfer_to_location
, mmtt.transaction_type_id
, mtrl.txn_source_id
, mtrl.txn_source_line_id
, mtrl.reference_id
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mtrl.line_id = mmtt.move_order_line_id;
wms_op_runtime_pub_apis.update_drop_locator_for_task(
x_return_status => l_return_status
, x_message => l_message
, x_drop_lpn_option => x_drop_lpn_option
, p_transfer_lpn_id => p_transfer_lpn_id
);
SELECT mmtt.transaction_action_id
, mmtt.transaction_temp_id
, mmtt.inventory_item_id item_id
, msik.concatenated_segments item
, msik.revision_qty_control_code
, msik.lot_control_code
, msik.serial_number_control_code
, mmtt.revision
, mmtt.primary_quantity
, msik.primary_uom_code
, mmtt.transfer_subinventory transfer_sub
, NVL(msi.dropping_order, 0) sub_dropping_order
, NVL(msi.picking_order, 0) sub_picking_order
, inv_project.get_locsegs(mmtt.transfer_to_location,
mmtt.organization_id) transfer_loc
, NVL(mil.dropping_order, 0) loc_dropping_order
, NVL(mil.picking_order, 0) loc_picking_order
, mmtt.transaction_type_id
, mmtt.wip_entity_type
, wdt.priority
, wdt.task_id taskid
, wdt.task_type
, inv_project.get_project_id
, inv_project.get_project_number
, inv_project.get_task_id
, inv_project.get_task_number
, 0 wip_entity_id
, 0 repetitive_schedule_id
, 0 operation_seq_num
, mmtt.transfer_to_location --Bug#2756609
FROM mtl_material_transactions_temp mmtt
, mtl_secondary_inventories msi
, mtl_item_locations mil
, wms_dispatched_tasks wdt
, mtl_system_items_vl msik /* Bug 5581528 */
WHERE mmtt.organization_id = p_organization_id
AND mmtt.transfer_lpn_id = p_transfer_lpn_id
AND mmtt.transaction_source_type_id = inv_globals.g_sourcetype_inventory
AND mmtt.transaction_action_id = inv_globals.g_action_subxfr
AND msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
AND mmtt.organization_id = mil.organization_id
AND mmtt.transfer_subinventory = mil.subinventory_code
AND mmtt.transfer_to_location = mil.inventory_location_id
AND wdt.organization_id = mmtt.organization_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.status IN (3, 4)
AND wdt.task_type = 1
AND msik.organization_id = mmtt.organization_id
AND msik.inventory_item_id = mmtt.inventory_item_id
UNION ALL
SELECT mmtt.transaction_action_id
, mmtt.transaction_temp_id
, mmtt.inventory_item_id item_id
, msik.concatenated_segments item
, msik.revision_qty_control_code
, msik.lot_control_code
, msik.serial_number_control_code
, mmtt.revision
, mmtt.primary_quantity
, msik.primary_uom_code
, to_char(NULL) transfer_sub
, to_number(NULL) sub_dropping_order
, to_number(NULL) sub_picking_order
, to_char(NULL) transfer_loc
, to_number(NULL) loc_dropping_order
, to_number(NULL) loc_picking_order
, mmtt.transaction_type_id
, mmtt.wip_entity_type
, wdt.priority
, wdt.task_id taskid
, wdt.task_type
, to_char(NULL)
, to_char(NULL)
, to_char(NULL)
, to_char(NULL)
, mtrl.txn_source_id wip_entity_id
, mtrl.reference_id repetitive_schedule_id
, mtrl.txn_source_line_id operation_seq_num
, to_number(NULL) transfer_to_location --Bug#2756609
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
, wms_dispatched_tasks wdt
, mtl_system_items_vl msik /* Bug 5581528 */
WHERE mmtt.organization_id = p_organization_id
AND mmtt.transfer_lpn_id = p_transfer_lpn_id
AND mmtt.transaction_source_type_id = inv_globals.g_sourcetype_wip
AND mmtt.transaction_action_id = inv_globals.g_action_issue
AND mtrl.line_id = mmtt.move_order_line_id
AND wdt.organization_id = mmtt.organization_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.status IN (3, 4)
AND wdt.task_type = 1
AND msik.organization_id = mmtt.organization_id
AND msik.inventory_item_id = mmtt.inventory_item_id
ORDER BY transaction_action_id DESC
, sub_dropping_order
, sub_picking_order
, transfer_sub
, loc_dropping_order
, loc_picking_order
, transfer_loc
, wip_entity_id
, repetitive_schedule_id
, operation_seq_num
, item_id
, revision
, priority
, taskid;
SELECT mmtt.inventory_item_id
, mmtt.lot_number
, mmtt.revision
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mmtt.locator_id
, mmtt.subinventory_code
, nvl(inv_project.get_locsegs(mmtt.transfer_to_location, mmtt.organization_id),'') loc
, msik.concatenated_segments item
, mmtt.transaction_temp_id
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.transaction_type_id
, mmtt.wip_entity_type
, mmtt.transaction_source_type_id
, wdt.priority priority
, wdt.task_id taskid
, wdt.task_type task_type
, inv_project.get_project_id
, inv_project.get_project_number
, inv_project.get_task_id
, inv_project.get_task_number
FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt, mtl_system_items_vl msik /* Bug 5581528 */
WHERE wdt.organization_id = p_organization_id
AND wdt.status IN (3, 4)
AND wdt.task_type IN (1, 4, 5, 7)
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.transfer_lpn_id = p_transfer_lpn_id
AND mmtt.organization_id = msik.organization_id
AND mmtt.inventory_item_id = msik.inventory_item_id
ORDER BY subinventory_code, loc, priority, taskid;
SELECT lot_number, SUM(primary_quantity)
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_txn_temp_id
GROUP BY lot_number;
SELECT DECODE(COUNT(serial_transaction_temp_id), 0, 'N', 'Y')
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_txn_temp_id;
SELECT msnt.fm_serial_number
FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers_temp msnt
WHERE p_lot_num IS NOT NULL
AND mtlt.transaction_temp_id = p_txn_temp_id
AND mtlt.lot_number = p_lot_num
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
UNION ALL
SELECT msnt.fm_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE p_lot_num IS NULL
AND msnt.transaction_temp_id = p_txn_temp_id
ORDER BY 1;
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_transaction_temp_id;
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM
mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_transaction_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
SELECT 1
INTO l_mmtt_rowcnt
FROM dual
WHERE exists (SELECT 1
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND organization_id = p_organization_id
AND (parent_line_id is NULL -- regular task
OR parent_line_id = transaction_temp_id)); -- bulk task
SELECT 1
INTO l_mmtt_rowcnt
FROM dual
WHERE exists (SELECT 1
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND organization_id = p_organization_id
AND wms_task_status <> 8 -- unreleased
AND (
parent_line_id is NULL -- regular task
OR parent_line_id = transaction_temp_id)); -- bulk task
SELECT 1
INTO l_wdt_rowcnt
FROM dual
WHERE exists (SELECT 1
FROM wms_dispatched_tasks t
WHERE t.transaction_temp_id = p_transaction_temp_id
AND person_id <> p_employee_id);
SELECT wdt.status INTO l_status FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = p_transaction_temp_id;
SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
INTO l_txn_hdr_id
FROM DUAL;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_hdr_id ,
last_update_date = Sysdate,
last_updated_by = l_user_id,
creation_date = Sysdate,
created_by = l_user_id
, posting_flag = 'Y' -- Bug4185621: this will change the parent posting flag to 'Y' for bulking picking
-- If not bulking picking, this has not effect
WHERE transaction_temp_id = p_transaction_temp_id
returning wms_task_type INTO x_task_type_id;
SELECT 1
INTO l_wdt_rowcnt
FROM dual
WHERE exists (SELECT 1
FROM wms_dispatched_tasks t
WHERE t.transaction_temp_id = p_transaction_temp_id);
SELECT bremp.resource_id role_id,
t.wms_task_type,
t.standard_operation_id,
t.operation_plan_id,
t.move_order_line_id,
t.inventory_item_id
INTO l_person_resource_id,
x_task_type_id,
l_standard_operation_id,
l_operation_plan_id,
l_move_order_line_id,
l_item_id
FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
WHERE t.transaction_temp_id = p_transaction_temp_id
AND t.standard_operation_id = bsor.standard_operation_id
AND bsor.resource_id = bremp.resource_id
AND bremp.resource_type = 2
AND ROWNUM < 2;
SELECT resource_id
INTO l_machine_resource_id
FROM bom_resource_equipments
WHERE inventory_item_id = p_equipment_id
AND ROWNUM < 2;
SELECT msi.serial_number_control_code
, msi.lot_control_code
INTO l_serial_ctrl_code
, l_lot_ctrl_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_item_id
AND msi.organization_id =p_organization_id ;
UPDATE MTL_SERIAL_NUMBERS msn
SET GROUP_MARK_ID=l_txn_hdr_id
WHERE msn.current_organization_id=p_organization_id
AND msn.inventory_item_id= l_item_id
AND msn.SERIAL_NUMBER BETWEEN l_fm_serial_number AND
l_to_serial_number;
UPDATE mtl_serial_numbers_temp
SET group_header_id= l_txn_hdr_id
WHERE transaction_temp_id in ( SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id= p_transaction_temp_id );
UPDATE MTL_SERIAL_NUMBERS msn
SET GROUP_MARK_ID=l_txn_hdr_id
WHERE msn.current_organization_id=p_organization_id
AND msn.inventory_item_id= l_item_id
AND msn.SERIAL_NUMBER BETWEEN l_fm_serial_number AND
l_to_serial_number;
UPDATE mtl_serial_numbers_temp
SET group_header_id= l_txn_hdr_id
WHERE transaction_temp_id=p_transaction_temp_id ;
mydebug('manual_pick: Updated MSNT', 'MANUAL_PICK');
INSERT INTO wms_dispatched_tasks
(task_id,
transaction_temp_id,
organization_id,
user_task_type,
person_id,
effective_start_date,
effective_end_date,
equipment_id,
equipment_instance,
person_resource_id,
machine_resource_id,
status,
dispatched_time,
last_update_date,
last_updated_by,
creation_date,
created_by,
task_type,
operation_plan_id,
move_order_line_id)
VALUES
(wms_dispatched_tasks_s.NEXTVAL,
p_transaction_temp_id,
p_organization_id,
l_standard_operation_id,
p_employee_id,
p_effective_start_date,
p_effective_end_date,
p_equipment_id,
l_equipment_serial,
l_person_resource_id,
l_machine_resource_id,
3, -- Dispatched
SYSDATE,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
x_task_type_id,
l_operation_plan_id,
l_move_order_line_id);
mydebug('MANUAL_PICK: After Insert into WDT', 'MANUAL_PICK');
SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
INTO x_task_id, x_transaction_temp_id, x_task_type_id
FROM wms_dispatched_tasks wdt,
mtl_material_transactions_temp mmtt
WHERE wdt.person_id = p_employee_id
AND wdt.organization_id = p_organization_id
AND wdt.task_type = 1 -- Picking
AND wdt.status = 3
AND Nvl(wdt.device_id, -1) = Nvl(p_device_id, -1)
AND Decode(wdt.device_id, NULL, 'Y', wdt.device_invoked) = 'Y'
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND Decode(p_subinventory_code, NULL, mmtt.subinventory_code, p_subinventory_code) = mmtt.subinventory_code
AND mmtt.pick_slip_number = p_grouping_document_number
AND wdt.task_group_id = g_group_sequence_number;
SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
INTO x_task_id, x_transaction_temp_id, x_task_type_id
FROM wms_dispatched_tasks wdt,
mtl_material_transactions_temp mmtt
WHERE wdt.person_id = p_employee_id
AND wdt.organization_id = p_organization_id
AND wdt.task_type = 1 -- Picking
AND wdt.status = 3
AND Nvl(wdt.device_id, -1) = Nvl(p_device_id, -1)
AND Decode(wdt.device_id, NULL, 'Y', wdt.device_invoked) = 'Y'
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND Decode(p_subinventory_code, NULL, mmtt.subinventory_code, p_subinventory_code) = mmtt.subinventory_code
AND mmtt.transaction_source_type_id = p_grouping_source_type_id
AND mmtt.transaction_source_id = p_grouping_document_number
AND wdt.task_group_id = g_group_sequence_number;
SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
INTO x_task_id, x_transaction_temp_id, x_task_type_id
FROM wms_dispatched_tasks wdt,
mtl_material_transactions_temp mmtt
WHERE wdt.person_id = p_employee_id
AND wdt.organization_id = p_organization_id
AND wdt.task_type = 1 -- Picking
AND wdt.status = 3
AND Nvl(wdt.device_id, -1) = Nvl(p_device_id, -1)
AND Decode(wdt.device_id, NULL, 'Y', wdt.device_invoked) = 'Y'
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND Decode(p_subinventory_code, NULL, mmtt.subinventory_code, p_subinventory_code) = mmtt.subinventory_code
AND mmtt.cartonization_id = p_grouping_document_number
AND wdt.task_group_id = g_group_sequence_number;
SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
INTO x_task_id, x_transaction_temp_id, x_task_type_id
FROM wms_dispatched_tasks wdt,
mtl_material_transactions_temp mmtt
WHERE wdt.person_id = p_employee_id
AND wdt.organization_id = p_organization_id
AND wdt.task_type = 1 -- Picking
AND wdt.status = 3
AND Nvl(wdt.device_id, -1) = Nvl(p_device_id, -1)
AND Decode(wdt.device_id, NULL, 'Y', wdt.device_invoked) = 'Y'
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND Decode(p_subinventory_code, NULL, mmtt.subinventory_code, p_subinventory_code) = mmtt.subinventory_code
--AND mmtt.cartonization_id = p_grouping_document_number
AND mmtt.cartonization_id IN (SELECT * FROM TABLE(list_cartonization_id))
AND wdt.task_group_id = g_group_sequence_number;
SELECT task_id, transaction_temp_id, task_type
INTO x_task_id, x_transaction_temp_id, x_task_type_id
FROM (select wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
FROM wms_dispatched_tasks wdt,
mtl_material_transactions_temp mmtt,
mtl_secondary_inventories msi,
mtl_item_locations mil
WHERE wdt.person_id = p_employee_id
AND wdt.organization_id = p_organization_id
AND wdt.task_type = 1 -- Picking
AND wdt.status = 3
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND Decode(p_subinventory_code, NULL, mmtt.subinventory_code,
p_subinventory_code) = mmtt.subinventory_code
AND mmtt.subinventory_code = msi.secondary_inventory_name
AND mmtt.organization_id = msi.organization_id
AND mmtt.locator_id = mil.inventory_location_id
AND mmtt.organization_id = mil.organization_id
AND wdt.task_method = 'CLUSTER'
ORDER BY msi.picking_order, mil.picking_order, wdt.priority, wdt.status, wdt.task_id
)
WHERE rownum <2;
SELECT wdat.device_id,
wdat.assignment_temp_id,
wdb.subinventory_code
FROM wms_device_assignment_temp wdat, wms_devices_b wdb
WHERE wdat.assignment_temp_id >= p_current_device_temp_id
AND wdat.employee_id = p_emp_id
AND wdb.device_type_id <> 100
AND wdat.device_id = wdb.device_id
ORDER BY wdat.assignment_temp_id;
SELECT wdat.device_id,
wdat.assignment_temp_id,
wdb.subinventory_code
FROM wms_device_assignment_temp wdat, wms_devices_b wdb
WHERE wdat.assignment_temp_id < p_current_device_temp_id
AND wdat.employee_id = p_emp_id
AND wdb.device_type_id <> 100
AND wdat.device_id = wdb.device_id
ORDER BY wdat.assignment_temp_id;
CURSOR cluster_size_wdt IS SELECT mmtt.transaction_source_id, count (*)
FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
AND wdt.status = 3
AND wdt.person_id = p_employee_id
AND wdt.organization_id = p_organization_id
AND wdt.task_method = 'CLUSTER'
GROUP BY mmtt.transaction_source_id;
SELECT task_filter_source, task_filter_value
FROM wms_task_filter_b wtf, wms_task_filter_dtl wtfd
WHERE task_filter_name = v_filter_name
AND wtf.task_filter_id = wtfd.task_filter_id;
fnd_msg_pub.delete_msg;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = mtl_material_transactions_s.NEXTVAL,
last_update_date = Sysdate,
last_updated_by = l_user_id
WHERE transaction_temp_id = l_next_transaction_temp_id
returning wms_task_type INTO x_task_type_id;
mydebug('Tempid in table to be deleted viks : ', l_next_transaction_temp_id );
g_start_over_tempid.DELETE(g_start_over_tempid.FIRST);
select wdt.task_method, count(*), max(wdt.task_group_id)
into l_task_method_wdt, l_count_wdt ,l_max_seq_number
from wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
where mmtt.transaction_temp_id = wdt.transaction_temp_id
and wdt.status = 3
and wdt.person_id = p_employee_id
and wdt.organization_id = p_organization_id
and Decode(mmtt.transaction_source_type_id, /*Bug5188179. Apply task filter*/
2, l_so_allowed,
4, Decode(mmtt.transaction_action_id, 1, l_moi_allowed, 2, decode(wms_task_type, 4, l_rep_allowed, l_mot_allowed)),
5, Decode(mmtt.transaction_type_id, 35, l_wip_allowed),
8, l_io_allowed,
13, Decode(mmtt.transaction_type_id,
51, l_wip_allowed,
Decode(mmtt.transaction_action_id, 2, decode(wms_task_type, 4, l_rep_allowed)))) = 1
group by wdt.task_method;
-- Change select pending tasks as sub-query with order by first
-- Move rownum clause out of the order by query to ensure the lowerest
-- row is selected from the pending tasks
--
-- bug 5094839
-- Restore task_goup_id in the select statement to
-- (task_group_id - 1) for getting the correct task_group_id
select transaction_source_id, transaction_source_type_id, device_id,
(task_group_id - 1), pick_slip_number, cartonization_id
INTO x_grouping_document_number, x_grouping_source_type_id, l_device_id,
g_group_sequence_number, l_pick_slip_number, l_cartonization_id
From
( select mmtt.transaction_source_id, mmtt.transaction_source_type_id, wdt.device_id,
wdt.task_group_id, mmtt.pick_slip_number, mmtt.cartonization_id
from mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
where mmtt.transaction_temp_id = wdt.transaction_temp_id
and wdt.status = 3
and wdt.person_id = p_employee_id
and wdt.organization_id = p_organization_id
and Decode(mmtt.transaction_source_type_id, /*Bug5188179. Apply task filter*/
2, l_so_allowed,
4, Decode(mmtt.transaction_action_id, 1, l_moi_allowed, 2, decode(wms_task_type, 4, l_rep_allowed, l_mot_allowed)),
5, Decode(mmtt.transaction_type_id, 35, l_wip_allowed),
8, l_io_allowed,
13, Decode(mmtt.transaction_type_id,
51, l_wip_allowed,
Decode(mmtt.transaction_action_id, 2, decode(wms_task_type, 4, l_rep_allowed)))) = 1
order by wdt.task_group_id, wdt.transaction_temp_id )-- 4584860
Where rownum = 1; -- bug 5264450
-- If the above select doesnt return any rows, just fetch it from MMTT as usual
EXCEPTION WHEN NO_DATA_FOUND THEN
mydebug('No Task in WDT, so fetching from MMTT: ','');
SELECT wdat.assignment_temp_id, wd.subinventory_code
INTO l_assignment_temp_id, l_subinventory_code
FROM wms_device_assignment_temp wdat, wms_devices_b wd
WHERE wdat.device_id = l_device_id
AND wdat.device_id = wd.device_id
AND wdat.employee_id = p_employee_id;
SELECT transaction_temp_id, task_type,
device_invoked, device_request_id
INTO l_transaction_temp_id, x_task_type_id,
l_device_invoked, l_request_id
FROM wms_dispatched_tasks
WHERE person_id = p_employee_id
AND organization_id = p_organization_id
AND device_id = l_loop_device_id
AND task_type IN(1, 3, 4, 5, 6)
AND status = 3
AND ROWNUM = 1
ORDER BY 1;
UPDATE wms_dispatched_tasks
SET device_invoked = 'Y',
device_request_id = l_request_id
WHERE transaction_temp_id = l_transaction_temp_id;
DELETE FROM wms_ordered_tasks;
SELECT count(1) INTO l_wdt_count
FROM wms_dispatched_tasks WDT
WHERE WDT.transaction_temp_id = task_record.transaction_temp_id
AND WDT.person_id <> p_employee_id
AND WDT.status in (3,9);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = mtl_material_transactions_s.NEXTVAL
, last_update_date = Sysdate
, last_updated_by = l_user_id
, posting_flag = 'Y' -- Bug4185621: this will change the parent posting flag to 'Y' for bulking picking
-- If not bulking picking, this has not effect
WHERE transaction_temp_id = task_record.transaction_temp_id;
SELECT STATUS
INTO l_task_Status
FROM wms_dispatched_tasks
WHERE transaction_temp_id = task_record.transaction_temp_id;
UPDATE wms_dispatched_tasks
SET status = 3, -- Dispatched
task_group_id = l_group_sequence_number,
device_id = l_loop_device_id,
device_invoked = Decode(l_loop_device_id, NULL, To_char(NULL),
Decode(l_first_task, 'Y', 'Y', 'N')),
device_request_id = l_request_id,
-- Bugfix 4101378
equipment_id = p_equipment_id,
equipment_instance = p_equipment_serial,
machine_resource_id = task_record.machine_resource_id,
-- End of code Bugfix 4101378
last_update_date = Sysdate,
last_updated_by = l_user_id,
task_method = p_task_method
WHERE transaction_temp_id = task_record.transaction_temp_id;
INSERT INTO wms_dispatched_tasks
(task_id,
transaction_temp_id,
organization_id,
user_task_type,
person_id,
effective_start_date,
effective_end_date,
equipment_id,
equipment_instance,
person_resource_id,
machine_resource_id,
status,
dispatched_time,
last_update_date,
last_updated_by,
creation_date,
created_by,
task_type,
priority,
operation_plan_id,
move_order_line_id,
device_id,
device_invoked,
device_request_id,
task_group_id,
task_method) -- add for cluster picking but others can use it too
VALUES
(wms_dispatched_tasks_s.NEXTVAL,
task_record.transaction_temp_id,
p_organization_id,
NVL(task_record.standard_operation_id, 2),
p_employee_id,
sysdate , --task_record.effective_start_date, --bug#6409956
sysdate , --task_record.effective_end_date, --bug#6409956
p_equipment_id,
p_equipment_serial,
task_record.person_resource_id,
task_record.machine_resource_id,
3, -- Dispatched
Sysdate,
Sysdate,
l_user_id,
Sysdate,
l_user_id,
task_record.task_type,
task_record.priority,
task_record.operation_plan_id,
task_record.move_order_line_id,
l_loop_device_id,
Decode(l_loop_device_id, NULL, To_char(NULL), Decode(l_first_task, 'Y', 'Y', 'N')),
l_request_id,
l_group_sequence_number,
p_task_method); -- add for cluster picking
mydebug('Inserted into WDT', l_api_name);
END; -- end the begin for updating and inserting to WDT4310093
UPDATE wms_dispatched_tasks
SET status = 9, -- Active
device_id = x_avail_device_id,
device_invoked = Decode(x_avail_device_id, NULL, To_char(NULL), 'Y'),
device_request_id = x_device_request_id,
last_update_date = Sysdate,
last_updated_by = l_user_id
WHERE transaction_temp_id = l_next_transaction_temp_id;
mydebug('Updated Cycle count task to active: '||l_next_transaction_temp_id, l_api_name);
UPDATE wms_dispatched_tasks
SET status = 9, -- Active
last_update_date = Sysdate,
last_updated_by = l_user_id
WHERE transaction_temp_id = l_next_transaction_temp_id;
DELETE FROM wms_skip_task_exceptions
WHERE task_id = task_record.transaction_temp_id
AND task_id IN (SELECT wste.task_id
FROM wms_skip_task_exceptions wste, mtl_parameters mp
WHERE ABS((SYSDATE - wste.creation_date) * 24 * 60) > mp.skip_task_waiting_minutes
AND wste.task_id = task_record.transaction_temp_id
AND wste.organization_id = mp.organization_id);
update mtl_material_transactions_temp
set transfer_lpn_id = p_lpn_id
where transaction_temp_id = l_resultant_tasks(1).task_id;
SELECT *
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_ttemp_id
ORDER BY lot_number;
new_task_table.delete;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_transaction_header_id
FROM dual;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_transaction_temp_id
FROM dual;
SELECT wms_dispatched_tasks_s.NEXTVAL INTO l_new_task_id FROM dual;
SELECT status
INTO l_mmtt_task_status
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_source_transaction_number;
UPDATE mtl_material_transactions_temp
SET primary_quantity = primary_quantity - l_split_uom_quantities(i).primary_quantity ,
transaction_quantity = transaction_quantity - l_split_uom_quantities(i).transaction_quantity ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE transaction_temp_id = p_source_transaction_number;
mydebug( ' Updated original txn. temp id :'||p_source_transaction_number, 'split_task');
mydebug( 'Inserting Lot/Serial details of the new tasks in X_RESULTANT_TASK_DETAILS', 'split_task');
SELECT MIN(FM_SERIAL_NUMBER) ,
MAX(FM_SERIAL_NUMBER) ,
MAX(status_id)
INTO x_resultant_task_details(l_index).from_serial_number ,
x_resultant_task_details(l_index).to_serial_number ,
x_resultant_task_details(l_index).serial_status_id
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_mtlt_row.serial_transaction_temp_id;
SELECT MIN(FM_SERIAL_NUMBER) ,
MAX(FM_SERIAL_NUMBER) ,
MAX(status_id) ,
COUNT(*)
INTO x_resultant_task_details(l_index).from_serial_number ,
x_resultant_task_details(l_index).to_serial_number ,
x_resultant_task_details(l_index).serial_status_id ,
x_resultant_task_details(l_index).number_of_serials
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = new_task_table(i).transaction_temp_id;
SELECT transaction_uom ,
inventory_item_id ,
primary_quantity ,
transaction_quantity ,
item_primary_uom_code ,
transaction_uom ,
organization_id
INTO l_mmtt_transaction_uom ,
l_mmtt_inventory_item_id ,
l_mmtt_primary_quantity ,
l_mmtt_transaction_quantity ,
l_mmtt_item_primary_uom_code ,
l_mmtt_transaction_uom ,
l_mmtt_organization_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT lot_control_code ,
serial_number_control_code
INTO l_lot_control_code ,
l_serial_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_mmtt_inventory_item_id
AND organization_id = l_mmtt_organization_id;
SELECT sum(transaction_quantity)
INTO l_mtlt_transaction_qty
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT sum(1)
INTO l_msnt_transaction_qty
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
);
SELECT sum(transaction_quantity)
INTO l_mtlt_transaction_qty
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT sum(1)
INTO l_msnt_transaction_qty
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id;
INSERT
INTO mtl_material_transactions_temp
(
currency_conversion_date ,
shipment_number ,
org_cost_group_id ,
cost_type_id ,
transaction_status ,
standard_operation_id ,
task_priority ,
wms_task_type ,
parent_line_id ,
source_lot_number ,
transfer_cost_group_id ,
lpn_id ,
transfer_lpn_id ,
wms_task_status ,
content_lpn_id ,
container_item_id ,
cartonization_id ,
pick_slip_date ,
rebuild_item_id ,
rebuild_serial_number ,
rebuild_activity_id ,
rebuild_job_name ,
organization_type ,
transfer_organization_type ,
owning_organization_id ,
owning_tp_type ,
xfr_owning_organization_id ,
transfer_owning_tp_type ,
planning_organization_id ,
planning_tp_type ,
xfr_planning_organization_id ,
transfer_planning_tp_type ,
secondary_uom_code ,
secondary_transaction_quantity ,
allocated_lpn_id ,
schedule_number ,
scheduled_flag ,
class_code ,
schedule_group ,
build_sequence ,
bom_revision ,
routing_revision ,
bom_revision_date ,
routing_revision_date ,
alternate_bom_designator ,
alternate_routing_designator ,
transaction_batch_id ,
transaction_batch_seq ,
operation_plan_id ,
intransit_account ,
fob_point ,
transaction_header_id ,
transaction_temp_id ,
source_code ,
source_line_id ,
transaction_mode ,
lock_flag ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
inventory_item_id ,
revision ,
organization_id ,
subinventory_code ,
locator_id ,
transaction_quantity ,
primary_quantity ,
transaction_uom ,
transaction_cost ,
transaction_type_id ,
transaction_action_id ,
transaction_source_type_id ,
transaction_source_id ,
transaction_source_name ,
transaction_date ,
acct_period_id ,
distribution_account_id ,
transaction_reference ,
requisition_line_id ,
requisition_distribution_id ,
reason_id ,
lot_number ,
lot_expiration_date ,
serial_number ,
receiving_document ,
demand_id ,
rcv_transaction_id ,
move_transaction_id ,
completion_transaction_id ,
wip_entity_type ,
schedule_id ,
repetitive_line_id ,
employee_code ,
primary_switch ,
schedule_update_code ,
setup_teardown_code ,
item_ordering ,
negative_req_flag ,
operation_seq_num ,
picking_line_id ,
trx_source_line_id ,
trx_source_delivery_id ,
physical_adjustment_id ,
cycle_count_id ,
rma_line_id ,
customer_ship_id ,
currency_code ,
currency_conversion_rate ,
currency_conversion_type ,
ship_to_location ,
move_order_header_id ,
serial_allocated_flag ,
trx_flow_header_id ,
logical_trx_type_code ,
original_transaction_temp_id ,
vendor_lot_number ,
encumbrance_account ,
encumbrance_amount ,
transfer_cost ,
transportation_cost ,
transportation_account ,
freight_code ,
containers ,
waybill_airbill ,
expected_arrival_date ,
transfer_subinventory ,
transfer_organization ,
transfer_to_location ,
new_average_cost ,
value_change ,
percentage_change ,
material_allocation_temp_id ,
demand_source_header_id ,
demand_source_line ,
demand_source_delivery ,
item_segments ,
item_description ,
item_trx_enabled_flag ,
item_location_control_code ,
item_restrict_subinv_code ,
item_restrict_locators_code ,
item_revision_qty_control_code ,
item_primary_uom_code ,
item_uom_class ,
item_shelf_life_code ,
item_shelf_life_days ,
item_lot_control_code ,
item_serial_control_code ,
item_inventory_asset_flag ,
allowed_units_lookup_code ,
department_id ,
department_code ,
wip_supply_type ,
supply_subinventory ,
supply_locator_id ,
valid_subinventory_flag ,
valid_locator_flag ,
locator_segments ,
current_locator_control_code ,
number_of_lots_entered ,
wip_commit_flag ,
next_lot_number ,
lot_alpha_prefix ,
next_serial_number ,
serial_alpha_prefix ,
shippable_flag ,
posting_flag ,
required_flag ,
process_flag ,
ERROR_CODE ,
error_explanation ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
movement_id ,
reservation_quantity ,
shipped_quantity ,
transaction_line_number ,
task_id ,
to_task_id ,
source_task_id ,
project_id ,
source_project_id ,
pa_expenditure_org_id ,
to_project_id ,
expenditure_type ,
final_completion_flag ,
transfer_percentage ,
transaction_sequence_id ,
material_account ,
material_overhead_account ,
resource_account ,
outside_processing_account ,
overhead_account ,
flow_schedule ,
cost_group_id ,
demand_class ,
qa_collection_id ,
kanban_card_id ,
overcompletion_transaction_qty ,
overcompletion_primary_qty ,
overcompletion_transaction_id ,
end_item_unit_number ,
scheduled_payback_date ,
line_type_code ,
parent_transaction_temp_id ,
put_away_strategy_id ,
put_away_rule_id ,
pick_strategy_id ,
pick_rule_id ,
move_order_line_id ,
task_group_id ,
pick_slip_number ,
reservation_id ,
common_bom_seq_id ,
common_routing_seq_id ,
ussgl_transaction_code
)
SELECT currency_conversion_date ,
shipment_number ,
org_cost_group_id ,
cost_type_id ,
transaction_status ,
standard_operation_id ,
task_priority ,
wms_task_type ,
parent_line_id ,
source_lot_number ,
transfer_cost_group_id ,
lpn_id ,
transfer_lpn_id ,
wms_task_status ,
content_lpn_id ,
container_item_id ,
cartonization_id ,
pick_slip_date ,
rebuild_item_id ,
rebuild_serial_number ,
rebuild_activity_id ,
rebuild_job_name ,
organization_type ,
transfer_organization_type ,
owning_organization_id ,
owning_tp_type ,
xfr_owning_organization_id ,
transfer_owning_tp_type ,
planning_organization_id ,
planning_tp_type ,
xfr_planning_organization_id ,
transfer_planning_tp_type ,
secondary_uom_code ,
secondary_transaction_quantity ,
allocated_lpn_id ,
schedule_number ,
scheduled_flag ,
class_code ,
schedule_group ,
build_sequence ,
bom_revision ,
routing_revision ,
bom_revision_date ,
routing_revision_date ,
alternate_bom_designator ,
alternate_routing_designator ,
transaction_batch_id ,
transaction_batch_seq ,
operation_plan_id ,
intransit_account ,
fob_point ,
p_new_transaction_header_id --TRANSACTION_HEADER_ID
,
p_new_transaction_temp_id --TRANSACTION_TEMP_ID
,
source_code ,
source_line_id ,
transaction_mode ,
lock_flag ,
l_sysdate --LAST_UPDATE_DATE
,
FND_GLOBAL.USER_ID ,
l_sysdate --CREATION_DATE
,
FND_GLOBAL.USER_ID ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
inventory_item_id ,
revision ,
organization_id ,
subinventory_code ,
locator_id ,
p_transaction_qty_to_split --TRANSACTION_QUANTITY
,
p_primary_qty_to_split --PRIMARY_QUANTITY
,
transaction_uom ,
transaction_cost ,
transaction_type_id ,
transaction_action_id ,
transaction_source_type_id ,
transaction_source_id ,
transaction_source_name ,
transaction_date ,
acct_period_id ,
distribution_account_id ,
transaction_reference ,
requisition_line_id ,
requisition_distribution_id ,
reason_id ,
lot_number ,
lot_expiration_date ,
serial_number ,
receiving_document ,
demand_id ,
rcv_transaction_id ,
move_transaction_id ,
completion_transaction_id ,
wip_entity_type ,
schedule_id ,
repetitive_line_id ,
employee_code ,
primary_switch ,
schedule_update_code ,
setup_teardown_code ,
item_ordering ,
negative_req_flag ,
operation_seq_num ,
picking_line_id ,
trx_source_line_id ,
trx_source_delivery_id ,
physical_adjustment_id ,
cycle_count_id ,
rma_line_id ,
customer_ship_id ,
currency_code ,
currency_conversion_rate ,
currency_conversion_type ,
ship_to_location ,
move_order_header_id ,
serial_allocated_flag ,
trx_flow_header_id ,
logical_trx_type_code ,
original_transaction_temp_id ,
vendor_lot_number ,
encumbrance_account ,
encumbrance_amount ,
transfer_cost ,
transportation_cost ,
transportation_account ,
freight_code ,
containers ,
waybill_airbill ,
expected_arrival_date ,
transfer_subinventory ,
transfer_organization ,
transfer_to_location ,
new_average_cost ,
value_change ,
percentage_change ,
material_allocation_temp_id ,
demand_source_header_id ,
demand_source_line ,
demand_source_delivery ,
item_segments ,
item_description ,
item_trx_enabled_flag ,
item_location_control_code ,
item_restrict_subinv_code ,
item_restrict_locators_code ,
item_revision_qty_control_code ,
item_primary_uom_code ,
item_uom_class ,
item_shelf_life_code ,
item_shelf_life_days ,
item_lot_control_code ,
item_serial_control_code ,
item_inventory_asset_flag ,
allowed_units_lookup_code ,
department_id ,
department_code ,
wip_supply_type ,
supply_subinventory ,
supply_locator_id ,
valid_subinventory_flag ,
valid_locator_flag ,
locator_segments ,
current_locator_control_code ,
number_of_lots_entered ,
wip_commit_flag ,
next_lot_number ,
lot_alpha_prefix ,
next_serial_number ,
serial_alpha_prefix ,
shippable_flag ,
posting_flag ,
required_flag ,
process_flag ,
ERROR_CODE ,
error_explanation ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
movement_id ,
reservation_quantity ,
shipped_quantity ,
transaction_line_number ,
task_id ,
to_task_id ,
source_task_id ,
project_id ,
source_project_id ,
pa_expenditure_org_id ,
to_project_id ,
expenditure_type ,
final_completion_flag ,
transfer_percentage ,
transaction_sequence_id ,
material_account ,
material_overhead_account ,
resource_account ,
outside_processing_account ,
overhead_account ,
flow_schedule ,
cost_group_id ,
demand_class ,
qa_collection_id ,
kanban_card_id ,
overcompletion_transaction_qty ,
overcompletion_primary_qty ,
overcompletion_transaction_id ,
end_item_unit_number ,
scheduled_payback_date ,
line_type_code ,
parent_transaction_temp_id ,
put_away_strategy_id ,
put_away_rule_id ,
pick_strategy_id ,
pick_rule_id ,
move_order_line_id ,
task_group_id ,
pick_slip_number ,
reservation_id ,
common_bom_seq_id ,
common_routing_seq_id ,
ussgl_transaction_code
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_orig_transaction_temp_id;
INSERT
INTO wms_dispatched_tasks
(
op_plan_instance_id ,
task_method ,
task_id ,
transaction_temp_id ,
organization_id ,
user_task_type ,
person_id ,
effective_start_date ,
effective_end_date ,
equipment_id ,
equipment_instance ,
person_resource_id ,
machine_resource_id ,
status ,
dispatched_time ,
loaded_time ,
drop_off_time ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
task_type ,
priority ,
task_group_id ,
device_id ,
device_invoked ,
device_request_id ,
suggested_dest_subinventory ,
suggested_dest_locator_id ,
operation_plan_id ,
move_order_line_id ,
transfer_lpn_id
)
SELECT op_plan_instance_id ,
task_method ,
p_new_task_id --task_id
,
p_new_transaction_temp_id --transaction_temp_id
,
organization_id ,
user_task_type ,
person_id ,
effective_start_date ,
effective_end_date ,
equipment_id ,
equipment_instance ,
person_resource_id ,
machine_resource_id ,
status ,
dispatched_time ,
loaded_time ,
drop_off_time ,
l_sysdate --last_update_date
,
FND_GLOBAL.USER_ID ,
l_sysdate --creation_date
,
FND_GLOBAL.USER_ID ,
last_update_login ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
task_type ,
priority ,
task_group_id ,
device_id ,
device_invoked ,
device_request_id ,
suggested_dest_subinventory ,
suggested_dest_locator_id ,
operation_plan_id ,
p_new_mol_id ,
transfer_lpn_id
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_orig_transaction_temp_id;
SELECT rowid,
mtlt.*
FROM mtl_transaction_lots_temp mtlt
WHERE transaction_temp_id = p_orig_transaction_temp_id
ORDER BY lot_number;
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = p_new_transaction_temp_id ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE rowid = mtlt.rowid;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_txn_temp_id
FROM dual;
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = l_new_serial_txn_temp_id ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE transaction_temp_id = p_new_transaction_temp_id
AND lot_number = mtlt.lot_number;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_txn_remaining_qty_mtlt ,
primary_quantity = l_prim_remaining_qty_mtlt ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE rowid = mtlt.rowid;
SELECT rowid,
msnt.*
FROM mtl_serial_numbers_temp msnt
WHERE transaction_temp_id = p_orig_transaction_temp_id
ORDER BY fm_serial_number;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = p_new_transaction_temp_id ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE rowid = msnt.rowid;
UPDATE mtl_serial_numbers msn
SET msn.group_mark_id = p_new_transaction_temp_id ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE msn.inventory_item_id = p_inventory_item_id
AND serial_number = msnt.fm_serial_number
AND current_organization_id = p_organization_id;
INSERT
INTO mtl_transaction_lots_temp
(
TRANSACTION_TEMP_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY ,
LOT_NUMBER ,
LOT_EXPIRATION_DATE ,
ERROR_CODE ,
SERIAL_TRANSACTION_TEMP_ID ,
GROUP_HEADER_ID ,
PUT_AWAY_RULE_ID ,
PICK_RULE_ID ,
DESCRIPTION ,
VENDOR_NAME ,
SUPPLIER_LOT_NUMBER ,
ORIGINATION_DATE ,
DATE_CODE ,
GRADE_CODE ,
CHANGE_DATE ,
MATURITY_DATE ,
STATUS_ID ,
RETEST_DATE ,
AGE ,
ITEM_SIZE ,
COLOR ,
VOLUME ,
VOLUME_UOM ,
PLACE_OF_ORIGIN ,
BEST_BY_DATE ,
LENGTH ,
LENGTH_UOM ,
RECYCLED_CONTENT ,
THICKNESS ,
THICKNESS_UOM ,
WIDTH ,
WIDTH_UOM ,
CURL_WRINKLE_FOLD ,
LOT_ATTRIBUTE_CATEGORY ,
C_ATTRIBUTE1 ,
C_ATTRIBUTE2 ,
C_ATTRIBUTE3 ,
C_ATTRIBUTE4 ,
C_ATTRIBUTE5 ,
C_ATTRIBUTE6 ,
C_ATTRIBUTE7 ,
C_ATTRIBUTE8 ,
C_ATTRIBUTE9 ,
C_ATTRIBUTE10 ,
C_ATTRIBUTE11 ,
C_ATTRIBUTE12 ,
C_ATTRIBUTE13 ,
C_ATTRIBUTE14 ,
C_ATTRIBUTE15 ,
C_ATTRIBUTE16 ,
C_ATTRIBUTE17 ,
C_ATTRIBUTE18 ,
C_ATTRIBUTE19 ,
C_ATTRIBUTE20 ,
D_ATTRIBUTE1 ,
D_ATTRIBUTE2 ,
D_ATTRIBUTE3 ,
D_ATTRIBUTE4 ,
D_ATTRIBUTE5 ,
D_ATTRIBUTE6 ,
D_ATTRIBUTE7 ,
D_ATTRIBUTE8 ,
D_ATTRIBUTE9 ,
D_ATTRIBUTE10 ,
N_ATTRIBUTE1 ,
N_ATTRIBUTE2 ,
N_ATTRIBUTE3 ,
N_ATTRIBUTE4 ,
N_ATTRIBUTE5 ,
N_ATTRIBUTE6 ,
N_ATTRIBUTE7 ,
N_ATTRIBUTE8 ,
N_ATTRIBUTE9 ,
N_ATTRIBUTE10 ,
VENDOR_ID ,
TERRITORY_CODE ,
SUBLOT_NUM ,
SECONDARY_QUANTITY ,
SECONDARY_UNIT_OF_MEASURE ,
QC_GRADE ,
REASON_CODE ,
PRODUCT_CODE ,
PRODUCT_TRANSACTION_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
)
SELECT p_new_transaction_temp_id --TRANSACTION_TEMP_ID
,
sysdate --LAST_UPDATE_DATE
,
FND_GLOBAL.USER_ID ,
sysdate --CREATION_DATE
,
FND_GLOBAL.USER_ID ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
p_transaction_qty_to_split --TRANSACTION_QUANTITY
,
p_primary_qty_to_split --PRIMARY_QUANTITY
,
LOT_NUMBER ,
LOT_EXPIRATION_DATE ,
ERROR_CODE ,
SERIAL_TRANSACTION_TEMP_ID ,
GROUP_HEADER_ID ,
PUT_AWAY_RULE_ID ,
PICK_RULE_ID ,
DESCRIPTION ,
VENDOR_NAME ,
SUPPLIER_LOT_NUMBER ,
ORIGINATION_DATE ,
DATE_CODE ,
GRADE_CODE ,
CHANGE_DATE ,
MATURITY_DATE ,
STATUS_ID ,
RETEST_DATE ,
AGE ,
ITEM_SIZE ,
COLOR ,
VOLUME ,
VOLUME_UOM ,
PLACE_OF_ORIGIN ,
BEST_BY_DATE ,
LENGTH ,
LENGTH_UOM ,
RECYCLED_CONTENT ,
THICKNESS ,
THICKNESS_UOM ,
WIDTH ,
WIDTH_UOM ,
CURL_WRINKLE_FOLD ,
LOT_ATTRIBUTE_CATEGORY ,
C_ATTRIBUTE1 ,
C_ATTRIBUTE2 ,
C_ATTRIBUTE3 ,
C_ATTRIBUTE4 ,
C_ATTRIBUTE5 ,
C_ATTRIBUTE6 ,
C_ATTRIBUTE7 ,
C_ATTRIBUTE8 ,
C_ATTRIBUTE9 ,
C_ATTRIBUTE10 ,
C_ATTRIBUTE11 ,
C_ATTRIBUTE12 ,
C_ATTRIBUTE13 ,
C_ATTRIBUTE14 ,
C_ATTRIBUTE15 ,
C_ATTRIBUTE16 ,
C_ATTRIBUTE17 ,
C_ATTRIBUTE18 ,
C_ATTRIBUTE19 ,
C_ATTRIBUTE20 ,
D_ATTRIBUTE1 ,
D_ATTRIBUTE2 ,
D_ATTRIBUTE3 ,
D_ATTRIBUTE4 ,
D_ATTRIBUTE5 ,
D_ATTRIBUTE6 ,
D_ATTRIBUTE7 ,
D_ATTRIBUTE8 ,
D_ATTRIBUTE9 ,
D_ATTRIBUTE10 ,
N_ATTRIBUTE1 ,
N_ATTRIBUTE2 ,
N_ATTRIBUTE3 ,
N_ATTRIBUTE4 ,
N_ATTRIBUTE5 ,
N_ATTRIBUTE6 ,
N_ATTRIBUTE7 ,
N_ATTRIBUTE8 ,
N_ATTRIBUTE9 ,
N_ATTRIBUTE10 ,
VENDOR_ID ,
TERRITORY_CODE ,
SUBLOT_NUM ,
SECONDARY_QUANTITY ,
SECONDARY_UNIT_OF_MEASURE ,
QC_GRADE ,
REASON_CODE ,
PRODUCT_CODE ,
PRODUCT_TRANSACTION_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
FROM mtl_transaction_lots_temp
WHERE rowid = p_row_id;