[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Code to insert in cst_comp_snap_temp in case of WIP LPN completion
* and complete LPN putaway
*******************************************************************/
procedure create_snapshot(p_temp_id NUMBER, p_org_id NUMBER)
IS
l_errNum NUMBER;
select primary_cost_method
into l_primary_cost_method
from mtl_parameters
where organization_id = p_org_id;
* Insert a row into MTL_MATERIAL_TRANSACTION_TEMP
* If the transaction is an InterOrg transfer type, call the CostGroup
* API to determine cost groups.
*******************************************************************/
PROCEDURE insert_line_trx(
curlpnrec wms_container_pub.wms_container_content_rec_type
, v_trxtempid NUMBER
, v_trxaction NUMBER
, v_orgid NUMBER
, v_subinv VARCHAR2
, v_locatorid NUMBER
, v_trxqty NUMBER
, v_cost_group_id NUMBER := NULL
, v_mmtt_rec IN OUT NOCOPY mtl_material_transactions_temp%ROWTYPE
, x_trxtempid OUT NOCOPY NUMBER
, v_sectrxqty NUMBER := NULL --INVCONV kkillams
) IS
l_cst_grp_id NUMBER;
SELECT mtl_material_transactions_s.NEXTVAL
INTO x_trxtempid
FROM DUAL;
SELECT primary_uom_code
INTO l_temp_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_mmtt_rec.inventory_item_id
AND organization_id = v_mmtt_rec.organization_id;
fnd_message.set_token('module', 'INSERT_LINE_TRX');
INSERT INTO mtl_material_transactions_temp
(
transaction_header_id
, transaction_temp_id
, source_code
, source_line_id
, process_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, inventory_item_id
, organization_id
, subinventory_code
, locator_id
, transfer_to_location
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_date
, acct_period_id
, transfer_organization
, transfer_subinventory
, reason_id
, shipment_number
, distribution_account_id
, waybill_airbill
, expected_arrival_date
, freight_code
, revision
, lpn_id
, content_lpn_id
, transfer_lpn_id
, cost_group_id
, transaction_source_id
, trx_source_line_id
, demand_source_header_id
, demand_source_line
, posting_flag
, pick_rule_id
, pick_strategy_id
, put_away_rule_id
, put_away_strategy_id
, move_order_line_id
, pick_slip_number
, reservation_id
, transaction_status
, standard_operation_id
, task_priority
, wms_task_type
, transfer_cost_group_id
, wip_entity_type
, repetitive_line_id
, operation_seq_num
, department_id
, department_code
, lock_flag
, primary_switch
, wip_supply_type
, negative_req_flag
, required_flag
, completion_transaction_id
, flow_schedule
, transaction_batch_id
, transaction_batch_seq
, transaction_mode
, 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
, fob_point
, intransit_account
, trx_flow_header_id
, logical_trx_type_code
, original_transaction_temp_id
, secondary_uom_code --kkillams
, secondary_transaction_quantity --kkillams
, ship_to_location --eIB Build; Bug# 4348541
, v_mmtt_rec.last_update_date
, v_mmtt_rec.last_updated_by
, v_mmtt_rec.last_update_login
, v_mmtt_rec.inventory_item_id
, v_mmtt_rec.organization_id
, v_mmtt_rec.subinventory_code
, v_mmtt_rec.locator_id
, v_mmtt_rec.transfer_to_location
, v_mmtt_rec.transaction_quantity
, v_mmtt_rec.primary_quantity
, v_mmtt_rec.transaction_uom
, v_mmtt_rec.transaction_type_id
, v_mmtt_rec.transaction_action_id
, v_mmtt_rec.transaction_source_type_id
, v_mmtt_rec.transaction_date
, v_mmtt_rec.acct_period_id
, v_mmtt_rec.transfer_organization
, v_mmtt_rec.transfer_subinventory
, v_mmtt_rec.reason_id
, v_mmtt_rec.shipment_number
, v_mmtt_rec.distribution_account_id
, v_mmtt_rec.waybill_airbill
, v_mmtt_rec.expected_arrival_date
, v_mmtt_rec.freight_code
, v_mmtt_rec.revision
, v_mmtt_rec.lpn_id
, v_mmtt_rec.content_lpn_id
, v_mmtt_rec.transfer_lpn_id
, NVL(v_cost_group_id, v_mmtt_rec.cost_group_id)
, v_mmtt_rec.transaction_source_id
, v_mmtt_rec.trx_source_line_id
, v_mmtt_rec.demand_source_header_id
, v_mmtt_rec.demand_source_line
, v_mmtt_rec.posting_flag
, v_mmtt_rec.pick_rule_id
, v_mmtt_rec.pick_strategy_id
, v_mmtt_rec.put_away_rule_id
, v_mmtt_rec.put_away_strategy_id
, v_mmtt_rec.move_order_line_id
, v_mmtt_rec.pick_slip_number
, v_mmtt_rec.reservation_id
, v_mmtt_rec.transaction_status
, v_mmtt_rec.standard_operation_id
, v_mmtt_rec.task_priority
, v_mmtt_rec.wms_task_type
, l_xfr_cst_grp_id
, v_mmtt_rec.wip_entity_type
, v_mmtt_rec.repetitive_line_id
, v_mmtt_rec.operation_seq_num
, v_mmtt_rec.department_id
, v_mmtt_rec.department_code
, v_mmtt_rec.lock_flag
, v_mmtt_rec.primary_switch
, v_mmtt_rec.wip_supply_type
, v_mmtt_rec.negative_req_flag
, v_mmtt_rec.required_flag
, v_mmtt_rec.completion_transaction_id
, v_mmtt_rec.flow_schedule
, v_mmtt_rec.transaction_batch_id
, v_mmtt_rec.transaction_batch_seq
, v_mmtt_rec.transaction_mode
, v_mmtt_rec.owning_organization_id
, v_mmtt_rec.owning_tp_type
, v_mmtt_rec.xfr_owning_organization_id
, v_mmtt_rec.transfer_owning_tp_type
, v_mmtt_rec.planning_organization_id
, v_mmtt_rec.planning_tp_type
, v_mmtt_rec.xfr_planning_organization_id
, v_mmtt_rec.transfer_planning_tp_type
, v_mmtt_rec.fob_point
, v_mmtt_rec.intransit_account
, v_mmtt_rec.trx_flow_header_id
, v_mmtt_rec.logical_trx_type_code
, v_mmtt_rec.original_transaction_temp_id
, v_mmtt_rec.secondary_uom_code --kkillams
, v_mmtt_rec.secondary_transaction_quantity --kkillams
, v_mmtt_rec.ship_to_location --eIB Build; Bug# 4348541
* Insert a row into MTL_TRANSACTION_LOTS_TEMP
*******************************************************************/
FUNCTION insert_lot_trx(curlpnrec wms_container_pub.wms_container_content_rec_type, trxtmpid NUMBER)
RETURN NUMBER IS
lotobjid NUMBER;
SELECT expiration_date
INTO l_exp_date
FROM mtl_lot_numbers
WHERE organization_id = curlpnrec.organization_id
AND inventory_item_id = curlpnrec.content_item_id
AND lot_number = curlpnrec.lot_number;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE inventory_item_id = curlpnrec.content_item_id
AND organization_id = curlpnrec.organization_id;
fnd_message.set_token('module', 'INSERT_LOT_TRX');
inv_trx_util_pub.insert_lot_trx(
p_trx_tmp_id => trxtmpid
, p_user_id => 1
, p_lot_number => curlpnrec.lot_number
, p_trx_qty => curlpnrec.quantity
, p_pri_qty => l_primary_qty
, p_secondary_qty => curlpnrec.sec_quantity --INVCONV kkillams
, p_secondary_uom => curlpnrec.sec_uom --INVCONV kkillams
, x_ser_trx_id => sertrxid
, x_proc_msg => ret_msgdata
, p_exp_date => l_exp_date
);
inv_log_util.TRACE('**Error from insertLot :' || ret_msgdata, 'INV_LPN_TRX_PUB', 1);
inv_log_util.TRACE('*Inserted Lot :' || curlpnrec.lot_number, 'INV_LPN_TRX_PUB', 9);
* Insert a row into MTL_SERIAL_NUMBERS_TEMP
*******************************************************************/
FUNCTION insert_ser_trx(p_ser_number VARCHAR2, p_sertrxid NUMBER)
RETURN NUMBER IS
retval NUMBER;
inv_trx_util_pub.insert_ser_trx(p_trx_tmp_id => p_sertrxid, p_user_id => 1, p_fm_ser_num => p_ser_number
, p_to_ser_num => p_ser_number, x_proc_msg => ret_msgdata);
inv_log_util.TRACE('**Error from insertSerial :' || ret_msgdata, 'INV_LPN_TRX_PUB', 1);
inv_log_util.TRACE('*Inserted Serial:' || p_ser_number || ',trxid=' || p_sertrxid, 'INV_LPN_TRX_PUB', 9);
* Explode the contents of the lpn and insert into MMTT, MSNT and MTLT
*******************************************************************/
FUNCTION explode_and_insert(p_lpn_id NUMBER, p_hdr_id NUMBER, p_mmtt IN OUT NOCOPY mtl_material_transactions_temp%ROWTYPE)
RETURN NUMBER IS
tb_lpn_cnts wms_container_pub.wms_container_tbl_type;
SELECT subinventory_code
, locator_id
INTO l_lpn_subinv
, l_lpn_locator_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
UPDATE mtl_material_transactions_temp
SET inventory_item_id = -1
, transaction_batch_id = p_mmtt.transaction_batch_id
, transaction_batch_seq = p_mmtt.transaction_batch_seq
, subinventory_code = NVL(subinventory_code, l_lpn_subinv)
, locator_id = NVL(locator_id, l_lpn_locator_id)
WHERE transaction_temp_id = p_mmtt.transaction_temp_id;
UPDATE mtl_transactions_interface
SET transaction_batch_id = p_mmtt.transaction_batch_id
, transaction_batch_seq = p_mmtt.transaction_batch_seq
WHERE transaction_interface_id = p_mmtt.transaction_temp_id;
inv_log_util.TRACE('From MTI, try update MTI batch and seq rowcount='||sql%rowcount, 'INV_LPN_TRX_PUB', 9);
UPDATE mtl_material_transactions_temp
SET inventory_item_id = -1
WHERE transaction_temp_id = p_mmtt.transaction_temp_id;
SELECT NVL(lpn_controlled_flag, 2)
INTO l_skip_comingle_check
FROM mtl_secondary_inventories
WHERE organization_id = NVL(p_mmtt.transfer_organization, p_mmtt.organization_id)
AND secondary_inventory_name = NVL(p_mmtt.transfer_subinventory, p_mmtt.subinventory_code);
SELECT 1
INTO l_skip_comingle_check
FROM mtl_item_locations
WHERE project_id IS NOT NULL
AND inventory_location_id = NVL(p_mmtt.transfer_to_location, p_mmtt.locator_id)
AND subinventory_code = NVL(p_mmtt.transfer_subinventory, p_mmtt.subinventory_code)
AND organization_id = NVL(p_mmtt.transfer_organization, p_mmtt.organization_id);
inv_log_util.TRACE('** Going to update last Lot rec1 qty = ' || lotqty, 'INV_LPN_TRX_PUB', 9);
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = lotqty
, primary_quantity = lotqty * l_conv_fact /*Bug#5486052.*/
, secondary_quantity = lotsecqty --INVCONV kkillams
WHERE transaction_temp_id = v_lasttrxtmpid
AND lot_number = lastlotnum;
inv_log_util.TRACE('** Inserting Lot Number ' || curlpnrec.lot_number, 'INV_LPN_TRX_PUB', 9);
SELECT cost_group
INTO l_cst_grp
FROM cst_cost_groups
WHERE cost_group_id = NVL(curlpnrec.cost_group_id, p_mmtt.cost_group_id);
sertrxid := insert_lot_trx(curlpnrec, v_lasttrxtmpid);
inv_log_util.TRACE('** Going to update last Lot rec2 qty = ' || lotqty, 'INV_LPN_TRX_PUB', 9);
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = lotqty
, primary_quantity = lotqty * l_conv_fact /*Bug#5486052.*/
, secondary_quantity = lotsecqty --INVCONV kkillams
WHERE transaction_temp_id = v_lasttrxtmpid
AND lot_number = lastlotnum;
inv_log_util.TRACE('** Inserting Serial Number ' || curlpnrec.serial_number, 'INV_LPN_TRX_PUB', 9);
retval := insert_ser_trx(curlpnrec.serial_number, sertrxid);
SELECT cost_group
INTO l_cst_grp
FROM cst_cost_groups
WHERE cost_group_id = NVL(curlpnrec.cost_group_id, p_mmtt.cost_group_id);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = v_lastitemqty
, primary_quantity = v_lastitemqty * l_conv_fact /*Bug#5486052.*/
, secondary_transaction_quantity = CASE WHEN v_lastitemsecqty <> 0 THEN v_lastitemsecqty ELSE secondary_transaction_quantity END --INVCONV kkillams
WHERE transaction_temp_id = v_lasttrxtmpid;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = lotqty
, primary_quantity = lotqty * l_conv_fact /*Bug#5486052.*/
, secondary_quantity = lotsecqty --INVCONV kkillams
WHERE transaction_temp_id = v_lasttrxtmpid
AND lot_number = lastlotnum;
'** inserting into MMTT. Mode-Normal. Qty=' || itemqty || ',sub=' || l_lpn_subinv || ',loc=' || l_lpn_locator_id
, 'INV_LPN_TRX_PUB'
, 9
);
insert_line_trx(
curlpnrec
, p_mmtt.transaction_temp_id
, p_mmtt.transaction_action_id
, p_mmtt.organization_id
, l_lpn_subinv
, l_lpn_locator_id
, itemqty
, curlpnrec.cost_group_id
, p_mmtt
, v_lasttrxtmpid
, itemsecqty --INCONV kkillams
);
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items_b
WHERE inventory_item_id = curlpnrec.content_item_id
AND organization_id = curlpnrec.organization_id;
sertrxid := insert_lot_trx(curlpnrec, v_lasttrxtmpid);
retval := insert_ser_trx(curlpnrec.serial_number, sertrxid);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = v_lastitemqty
, primary_quantity = v_lastitemqty * l_conv_fact /*Bug#5486052.*/
-- nsinghi bug#5553546 v_lastitemsecqty is being wrongly assigned as v_lastitemqty in THEN part. change it to v_lastitemsecqty
-- , secondary_transaction_quantity = CASE WHEN v_lastitemsecqty <> 0 THEN v_lastitemqty ELSE secondary_transaction_quantity END
, secondary_transaction_quantity = CASE WHEN v_lastitemsecqty <> 0 THEN v_lastitemsecqty ELSE secondary_transaction_quantity END
WHERE transaction_temp_id = v_lasttrxtmpid;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = lotqty
, primary_quantity = lotqty * l_conv_fact /*Bug#5486052.*/
, secondary_quantity = lotsecqty --INVCONV kkillams
WHERE transaction_temp_id = v_lasttrxtmpid
AND lot_number = lastlotnum;
PROCEDURE update_fob_point(
v_mmtt IN OUT NOCOPY mtl_material_transactions_temp%ROWTYPE
, x_return_status IN OUT NOCOPY VARCHAR2
, x_msg_data IN OUT NOCOPY VARCHAR2
, x_msg_count IN OUT NOCOPY NUMBER
) IS
l_fob_point NUMBER;
SELECT fob_point
, intransit_inv_account
INTO l_fob_point
, l_intransit_inv_account
FROM mtl_interorg_parameters
WHERE from_organization_id = v_mmtt.organization_id
AND to_organization_id = v_mmtt.transfer_organization;
UPDATE mtl_material_transactions_temp
SET fob_point = l_fob_point
, intransit_account = l_intransit_inv_account
WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
SELECT shipment_line_id
INTO l_shipment_line_id
FROM rcv_transactions
WHERE transaction_id =v_mmtt.rcv_transaction_id;
SELECT Nvl(mmt_transaction_id,-1)
INTO l_mmt_transaction_id
FROM rcv_shipment_lines
WHERE shipment_line_id = l_shipment_line_id;
SELECT fob_point
, intransit_inv_account
INTO l_fob_point
, l_intransit_inv_account
FROM mtl_interorg_parameters
WHERE from_organization_id = v_mmtt.transfer_organization
AND to_organization_id = v_mmtt.organization_id;
SELECT fob_point
, intransit_account
INTO l_fob_point
, l_intransit_inv_account
FROM mtl_material_transactions
WHERE transaction_id = l_mmt_transaction_id;
UPDATE mtl_material_transactions_temp
SET fob_point = l_fob_point
, intransit_account = l_intransit_inv_account
WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
END update_fob_point;
SELECT fm_serial_number
, to_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = v_sertrxid;
SELECT lot_number
, primary_quantity
, transaction_quantity
, secondary_quantity
, serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_tempid;
* Update the status of the LPN
*******************************************************************/
PROCEDURE update_lpn_status(v_lpn wms_container_pub.lpn) IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
fnd_message.set_name('INV', 'INV_LPN_UPDATE_FAILURE');
* Insert a row into MTL_SERIAL_NUMBERS_TEMP which is a copy of another msnt row
*******************************************************************/
PROCEDURE copy_msnt(p_source_row_id ROWID, p_new_sertrxid NUMBER, p_new_fm_serial VARCHAR2, p_new_to_serial VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'COPY_MSNT';
SELECT *
INTO l_sertmp_rec
FROM mtl_serial_numbers_temp
WHERE ROWID = p_source_row_id;
INSERT INTO mtl_serial_numbers_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
, vendor_serial_number
, vendor_lot_number
, fm_serial_number
, to_serial_number
, serial_prefix
, ERROR_CODE
, group_header_id
, parent_serial_number
, end_item_unit_number
, serial_attribute_category
, origination_date
, 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
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
)
VALUES (
p_new_sertrxid
, SYSDATE
, l_sertmp_rec.last_updated_by
, SYSDATE
, l_sertmp_rec.created_by
, l_sertmp_rec.last_update_login
, l_sertmp_rec.request_id
, l_sertmp_rec.program_application_id
, l_sertmp_rec.program_id
, l_sertmp_rec.program_update_date
, l_sertmp_rec.vendor_serial_number
, l_sertmp_rec.vendor_lot_number
, p_new_fm_serial
, p_new_to_serial
, l_sertmp_rec.serial_prefix
, l_sertmp_rec.ERROR_CODE
, l_sertmp_rec.group_header_id
, l_sertmp_rec.parent_serial_number
, l_sertmp_rec.end_item_unit_number
, l_sertmp_rec.serial_attribute_category
, l_sertmp_rec.origination_date
, l_sertmp_rec.c_attribute1
, l_sertmp_rec.c_attribute2
, l_sertmp_rec.c_attribute3
, l_sertmp_rec.c_attribute4
, l_sertmp_rec.c_attribute5
, l_sertmp_rec.c_attribute6
, l_sertmp_rec.c_attribute7
, l_sertmp_rec.c_attribute8
, l_sertmp_rec.c_attribute9
, l_sertmp_rec.c_attribute10
, l_sertmp_rec.c_attribute11
, l_sertmp_rec.c_attribute12
, l_sertmp_rec.c_attribute13
, l_sertmp_rec.c_attribute14
, l_sertmp_rec.c_attribute15
, l_sertmp_rec.c_attribute16
, l_sertmp_rec.c_attribute17
, l_sertmp_rec.c_attribute18
, l_sertmp_rec.c_attribute19
, l_sertmp_rec.c_attribute20
, l_sertmp_rec.d_attribute1
, l_sertmp_rec.d_attribute2
, l_sertmp_rec.d_attribute3
, l_sertmp_rec.d_attribute4
, l_sertmp_rec.d_attribute5
, l_sertmp_rec.d_attribute6
, l_sertmp_rec.d_attribute7
, l_sertmp_rec.d_attribute8
, l_sertmp_rec.d_attribute9
, l_sertmp_rec.d_attribute10
, l_sertmp_rec.n_attribute1
, l_sertmp_rec.n_attribute2
, l_sertmp_rec.n_attribute3
, l_sertmp_rec.n_attribute4
, l_sertmp_rec.n_attribute5
, l_sertmp_rec.n_attribute6
, l_sertmp_rec.n_attribute7
, l_sertmp_rec.n_attribute8
, l_sertmp_rec.n_attribute9
, l_sertmp_rec.n_attribute10
, l_sertmp_rec.status_id
, l_sertmp_rec.territory_code
, l_sertmp_rec.time_since_new
, l_sertmp_rec.cycles_since_new
, l_sertmp_rec.time_since_overhaul
, l_sertmp_rec.cycles_since_overhaul
, l_sertmp_rec.time_since_repair
, l_sertmp_rec.cycles_since_repair
, l_sertmp_rec.time_since_visit
, l_sertmp_rec.cycles_since_visit
, l_sertmp_rec.time_since_mark
, l_sertmp_rec.cycles_since_mark
, l_sertmp_rec.number_of_repairs
);
/* SELECT wdd2.delivery_detail_id
, wdd2.src_requested_quantity_uom
, NVL(wdd2.picked_quantity, wdd2.requested_quantity) requested_quantity
, wdd2.requested_quantity_uom
, wdd1.delivery_detail_id lpn_detail_id
, wdd2.picked_quantity2
, wdd2.requested_quantity_uom2
, wdd2.transaction_temp_id
, wdd2.serial_number
, wdd2.source_header_id
, wdd2.source_line_id
FROM wsh_delivery_details wdd1, wsh_delivery_details wdd2, wsh_delivery_assignments_v wda
WHERE wdd1.organization_id = p_organization_id
AND wdd1.lpn_id = p_lpn_id
AND wdd1.released_status = 'X' -- For LPN reuse ER : 6845650
AND wda.parent_delivery_detail_id = wdd1.delivery_detail_id
AND wdd2.delivery_detail_id = wda.delivery_detail_id
AND wdd2.inventory_item_id = p_item_rec.inventory_item_id
AND NVL(wdd2.revision, '@') = NVL(p_revision, '@')
AND NVL(wdd2.lot_number, -999) = NVL(lot, -999)
AND wdd2.source_header_id = NVL(p_transaction_source_id, wdd2.source_header_id)
AND wdd2.source_line_id = NVL(p_trx_source_line_id, wdd2.source_line_id); */
SELECT wdd2.delivery_detail_id
, wdd2.src_requested_quantity_uom
, NVL(wdd2.picked_quantity, wdd2.requested_quantity) requested_quantity
, wdd2.requested_quantity_uom
, wdd1.delivery_detail_id lpn_detail_id
, wdd2.picked_quantity2
, wdd2.requested_quantity_uom2
, wdd2.transaction_temp_id
, wdd2.serial_number
, wdd2.source_header_id
, wdd2.source_line_id
FROM wsh_delivery_details wdd1, wsh_delivery_details wdd2, wsh_delivery_assignments wda
WHERE wdd1.organization_id = p_organization_id
AND wdd1.lpn_id = p_lpn_id
AND wda.parent_delivery_detail_id = wdd1.delivery_detail_id
AND wdd2.delivery_detail_id = wda.delivery_detail_id
AND wdd2.inventory_item_id = p_item_rec.inventory_item_id
AND ((WDD2.REVISION IS NULL AND p_revision IS NULL ) OR WDD2.REVISION = p_revision )
--AND NVL(wdd2.revision, '@') = NVL(p_revision, '@')
AND ((WDD2.LOT_NUMBER IS NULL AND lot IS null) OR WDD2.LOT_NUMBER =lot )
--AND NVL(wdd2.lot_number, -999) = NVL(lot, -999)
AND ((p_transaction_source_id IS NULL AND WDD2.SOURCE_HEADER_ID=wdd2.source_header_id) OR WDD2.SOURCE_HEADER_ID=p_transaction_source_id)
--AND wdd2.source_header_id = NVL(p_transaction_source_id, wdd2.source_header_id)
AND ((p_trx_source_line_id IS NULL AND WDD2.SOURCE_LINE_ID =wdd2.source_line_id) OR WDD2.SOURCE_LINE_ID=p_trx_source_line_id );
SELECT DISTINCT msn.serial_number
FROM mtl_serial_numbers msn, mtl_serial_numbers_temp wddmsnt, mtl_serial_numbers_temp trxmsnt
WHERE msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_item_rec.inventory_item_id
AND wddmsnt.transaction_temp_id = p_wdd_trx_tmp_id
AND LENGTH(msn.serial_number) = LENGTH(wddmsnt.fm_serial_number)
AND msn.serial_number BETWEEN wddmsnt.fm_serial_number AND NVL(wddmsnt.to_serial_number, wddmsnt.fm_serial_number)
AND trxmsnt.transaction_temp_id = p_serial_trx_temp_id
AND LENGTH(msn.serial_number) = LENGTH(trxmsnt.fm_serial_number)
AND msn.serial_number BETWEEN trxmsnt.fm_serial_number AND NVL(trxmsnt.to_serial_number, trxmsnt.fm_serial_number)
ORDER BY serial_number;
SELECT DISTINCT ROWID
, fm_serial_number
, to_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE transaction_temp_id = p_wdd_trx_tmp_id
AND fm_serial_number >= p_start_serial
AND LENGTH(p_start_serial) = LENGTH(fm_serial_number)
ORDER BY fm_serial_number;
SELECT 1
INTO l_split_quantity
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_serial_trx_temp_id
AND dd_rec.serial_number BETWEEN fm_serial_number AND NVL(to_serial_number, fm_serial_number)
AND LENGTH(fm_serial_number) = LENGTH(dd_rec.serial_number)
AND ROWNUM < 2;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_invpcinrectype.transaction_temp_id
FROM DUAL;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_invpcinrectype.transaction_temp_id
WHERE ROWID = wdd_ser_rec.ROWID;
DELETE FROM mtl_serial_numbers_temp
WHERE ROWID = wdd_ser_rec.ROWID;
UPDATE mtl_serial_numbers_temp
SET to_serial_number = wdd_ser_rec.to_serial_number
WHERE ROWID = wdd_ser_rec.ROWID;
UPDATE mtl_serial_numbers_temp
SET fm_serial_number = wdd_ser_rec.fm_serial_number
WHERE ROWID = wdd_ser_rec.ROWID;
inv_log_util.TRACE('Done with call to WSH Create_Update_Containers', l_api_name, 4);
wsh_interface.update_shipping_attributes(p_source_code => 'INV', p_changed_attributes => l_shipping_attr
, x_return_status => ret_status);
inv_log_util.TRACE('***Error in update shipping attribures for split trx', l_api_name, 9);
l_del_det_in_rec.action_code := 'UPDATE';
inv_log_util.TRACE('Calling Create_Update_Delivery_Detail count=' || l_del_det_attr.COUNT, l_api_name, 9);
wsh_interface_ext_grp.create_update_delivery_detail(
p_api_version_number => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, x_return_status => ret_status
, x_msg_count => ret_msgcnt
, x_msg_data => ret_msgdata
, p_detail_info_tab => l_del_det_attr
, p_in_rec => l_del_det_in_rec
, x_out_rec => l_del_det_out_rec
);
inv_log_util.TRACE('Error calling Create_Update_Delivery_Detail: ' || ret_msgdata, l_api_name, 1);
SELECT lot_number
, primary_quantity
, serial_transaction_temp_id
, secondary_quantity
, secondary_unit_of_measure
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_tempid
ORDER BY lot_number;
wms_catch_weight_pvt.update_lpn_secondary_quantity(
p_api_version => 1.0
, x_return_status => ret_status
, x_msg_count => ret_msgcnt
, x_msg_data => ret_msgdata
, p_record_source => 'WDD'
, p_organization_id => p_org_id
, p_lpn_id => p_lpn_id
, p_inventory_item_id => p_item_rec.inventory_item_id
, p_revision => p_revision
, p_lot_number => lottmp_rec.lot_number
, p_quantity => NULL
, p_uom_code => NULL
, p_secondary_quantity => NULL
, p_secondary_uom_code => NULL
);
inv_log_util.TRACE('Error calling Update_LPN_Secondary_Quantity: ' || ret_msgdata, l_api_name, 1);
wms_catch_weight_pvt.update_lpn_secondary_quantity(
p_api_version => 1.0
, x_return_status => ret_status
, x_msg_count => ret_msgcnt
, x_msg_data => ret_msgdata
, p_record_source => 'WDD'
, p_organization_id => p_org_id
, p_lpn_id => p_xfr_lpn_id
, p_inventory_item_id => p_item_rec.inventory_item_id
, p_revision => p_revision
, p_lot_number => lottmp_rec.lot_number
, p_quantity => NULL
, p_uom_code => NULL
, p_secondary_quantity => NULL
, p_secondary_uom_code => NULL
);
inv_log_util.TRACE('Error calling Update_LPN_Secondary_Quantity: ' || ret_msgdata, l_api_name, 1);
wms_catch_weight_pvt.update_lpn_secondary_quantity(
p_api_version => 1.0
, x_return_status => ret_status
, x_msg_count => ret_msgcnt
, x_msg_data => ret_msgdata
, p_record_source => 'WDD'
, p_organization_id => p_org_id
, p_lpn_id => p_lpn_id
, p_inventory_item_id => p_item_rec.inventory_item_id
, p_revision => p_revision
, p_quantity => NULL
, p_uom_code => NULL
, p_secondary_quantity => NULL
, p_secondary_uom_code => NULL
);
inv_log_util.TRACE('Error calling Update_LPN_Secondary_Quantity: ' || ret_msgdata, l_api_name, 1);
wms_catch_weight_pvt.update_lpn_secondary_quantity(
p_api_version => 1.0
, x_return_status => ret_status
, x_msg_count => ret_msgcnt
, x_msg_data => ret_msgdata
, p_record_source => 'WDD'
, p_organization_id => p_org_id
, p_lpn_id => p_xfr_lpn_id
, p_inventory_item_id => p_item_rec.inventory_item_id
, p_revision => p_revision
, p_quantity => NULL
, p_uom_code => NULL
, p_secondary_quantity => NULL
, p_secondary_uom_code => NULL
);
inv_log_util.TRACE('Error calling Update_LPN_Secondary_Quantity: ' || ret_msgdata, l_api_name, 1);
SELECT *
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id
AND NVL(transaction_status, 1) <> 2 -- don't consider suggestions
AND process_flag = 'Y'
ORDER BY transaction_batch_id,transaction_batch_seq;
SELECT lot_number
,primary_quantity
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id;
Cursor to select transactions
for which OPM-QM workflow event will be raised */
CURSOR cur_get_txn_for_opm_qm IS
SELECT mmt.transaction_id
,mmt.transaction_action_id
,mmt.transaction_source_type_id
FROM mtl_material_transactions mmt
WHERE mmt.transaction_set_id = p_trx_hdr_id
AND mmt.transaction_quantity > 0
AND exists (select 1
from mtl_parameters mp
where mp.organization_id = mmt.organization_id
and process_enabled_flag = 'Y')
AND exists (select 1
from mtl_system_items_b msib
where msib.inventory_item_id = mmt.inventory_item_id
and msib.organization_id = mmt.organization_id
and msib.process_quality_enabled_flag = 'Y')
AND ( ( ( ( (mmt.transaction_action_id IN ( inv_globals.g_action_issue
,inv_globals.g_action_receipt
,inv_globals.g_action_subxfr
,inv_globals.g_action_orgxfr
,inv_globals.g_action_intransitshipment
,inv_globals.g_action_intransitreceipt))
--Bug#6509707.Included Account Alias Receipt.
AND (mmt.transaction_source_type_id IN (inv_globals.g_sourcetype_inventory,inv_globals.g_sourcetype_accountalias)
) )
-- Pawan Kumar added bug 5533472
OR (
(mmt.transaction_action_id = inv_globals.g_action_assycomplete)
AND (mmt.transaction_source_type_id = inv_globals.g_sourcetype_wip)
)
OR (
(mmt.transaction_action_id = inv_globals.g_action_cyclecountadj)
AND (mmt.transaction_source_type_id = inv_globals.g_sourcetype_cyclecount)
)
OR (
(mmt.transaction_action_id = inv_globals.g_action_physicalcountadj)
AND (mmt.transaction_source_type_id = inv_globals.g_sourcetype_physicalcount)
)
)
AND (exists (select gisv.spec_id
from gmd_inventory_spec_vrs gisv, gmd_specifications_b gsb
where gsb.inventory_item_id = mmt.inventory_item_id
and gsb.spec_status in (400,700)
and gsb.delete_mark = 0
and gisv.spec_id = gsb.spec_id
and gisv.delete_mark = 0
and (gisv.organization_id is null OR gisv.organization_id = mmt.organization_id)))
)
OR ( (mmt.transaction_action_id = inv_globals.g_action_receipt)
AND (mmt.transaction_source_type_id in ( inv_globals.g_sourcetype_purchaseorder
,inv_globals.g_sourcetype_rma
,inv_globals.g_sourcetype_intreq))
AND ( (exists (select gssv.spec_id
from gmd_supplier_spec_vrs gssv, gmd_specifications_b gsb
where gsb.inventory_item_id = mmt.inventory_item_id
and gsb.spec_status in (400,700)
and gsb.delete_mark = 0
and gssv.spec_id = gsb.spec_id
and gssv.delete_mark = 0
and (gssv.organization_id is null OR gssv.organization_id = mmt.organization_id)))
OR (exists (select gisv.spec_id
from gmd_inventory_spec_vrs gisv, gmd_specifications_b gsb
where gsb.inventory_item_id = mmt.inventory_item_id
and gsb.spec_status in (400,700)
and gsb.delete_mark = 0
and gisv.spec_id = gsb.spec_id
and gisv.delete_mark = 0
and (gisv.organization_id is null OR gisv.organization_id = mmt.organization_id)))
)
)
);
select distinct mtln.product_transaction_id,mln.gen_object_id
from mtl_transaction_lot_numbers mtln,mtl_lot_numbers mln
where transaction_id = l_txn_id
and mln.lot_number = mtln.lot_number
and mln.inventory_item_id = mtln.inventory_item_id
and mln.organization_id = mtln.organization_id;
v_deleterow BOOLEAN := FALSE; -- Should the original row in MMTT be deleted ?
SELECT 1
INTO l_is_cartonization
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_material_transactions_temp
WHERE transaction_action_id = inv_globals.g_action_containerpack
AND transfer_lpn_id IS NULL
AND transaction_header_id = p_trx_hdr_id);
SELECT organization_id
, MAX(containers)
INTO l_org
, l_containers
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id
AND NVL(transaction_status, 1) <> 2
AND process_flag = 'Y'
GROUP BY organization_id;
UPDATE mtl_material_transactions_temp
SET transfer_lpn_id = cartonization_id
WHERE transaction_header_id = p_trx_hdr_id;
quantity tree built/updated in forms session was not getting
picked up in pl/sql layer which would
have enabled to catch the error earlier than this point */
--Jalaj Srivastava Bug 5515181
--pass primary_quantity instead of txn qty
/* Jalaj Srivastava Bug 5446542
Lot indivisibility check will not be done
here. it will be done through the forms
or by the txn group layer */
/* ********************************************************************
IF (v_mmtt.lot_number IS NOT NULL) THEN
l_lot_indiv_trx_valid := INV_LOT_API_PUB.VALIDATE_LOT_INDIVISIBLE
( p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_transaction_type_id => v_mmtt.transaction_type_id
, p_organization_id => v_mmtt.organization_id
, p_inventory_item_id => v_mmtt.inventory_item_id
, p_revision => v_mmtt.revision
, p_subinventory_code => v_mmtt.subinventory_code
, p_locator_id => v_mmtt.locator_id
, p_lot_number => v_mmtt.lot_number
, p_primary_quantity => v_mmtt.primary_quantity
, p_qoh => NULL
, p_atr => NULL
, x_return_status => ret_status
, x_msg_count => ret_msgcnt
, x_msg_data => ret_msgdata);
inv_globals.g_action_retropriceupdate)
)
OR(
v_mmtt.transaction_source_type_id = inv_globals.g_sourcetype_rma
AND v_mmtt.transaction_action_id = inv_globals.g_action_logicalreceipt
)
OR(
v_mmtt.transaction_source_type_id = inv_globals.g_sourcetype_intreq
AND v_mmtt.transaction_action_id = inv_globals.g_action_logicalexpreqreceipt
)
OR(
v_mmtt.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
AND v_mmtt.transaction_action_id = inv_globals.g_action_logicalissue
)
) THEN
fnd_message.set_name('INV', 'INV_INT_TRXACTCODE');
update_fob_point(v_mmtt => v_mmtt, x_return_status => l_fob_ret_sts, x_msg_data => l_fob_ret_msg
, x_msg_count => l_fob_msg_count);
inv_log_util.TRACE(' Error from update_fob_point:' || l_fob_ret_msg, 'INV_LPN_TRX_PUB', 1);
inv_log_util.TRACE('Going to update mmtt with material_allocation_temp_id', 'INV_LPN_TRX_PUB', 1);
UPDATE mtl_material_transactions_temp
SET material_allocation_temp_id = mtl_material_transactions_s.NEXTVAL
WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_mode = inv_txn_manager_pub.proc_mode_mmtt_async
WHERE transaction_header_id = p_trx_hdr_id;
UPDATE mtl_material_transactions_temp
SET transaction_mode = inv_txn_manager_pub.proc_mode_mmtt_bgrnd
WHERE transaction_header_id = p_trx_hdr_id;
v_deleterow := FALSE;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET PRIMARY_QUANTITY = v_mmtt.primary_quantity,
TRANSACTION_QUANTITY = v_mmtt.transaction_quantity,
SECONDARY_TRANSACTION_QUANTITY = CASE WHEN v_mmtt.secondary_uom_code IS NOT NULL THEN v_mmtt.secondary_transaction_quantity
ELSE SECONDARY_TRANSACTION_QUANTITY
END --INVCONV kkillams
WHERE TRANSACTION_TEMP_ID = v_mmtt.transaction_temp_id
AND PRIMARY_QUANTITY > 0;
SELECT 'Y'
INTO l_putway_explode_req
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE v_mmtt.move_order_line_id IS NOT NULL
AND mtrl.line_id = v_mmtt.move_order_line_id
AND mtrh.header_id = mtrl.header_id
AND v_mmtt.transaction_source_type_id IN(4, 5)
AND mtrh.move_order_type = 6
AND ROWNUM < 2;
expldrowcnt := explode_and_insert(v_mmtt.content_lpn_id, p_trx_hdr_id, v_mmtt);
inv_log_util.TRACE(' Failed in EXPLODE_AND_INSERT!!', 'INV_LPN_TRX_PUB', 1);
UPDATE mtl_material_transactions_temp
SET ERROR_CODE = l_error_code
, error_explanation = x_proc_msg
, process_flag = 'E'
, lock_flag = 'N'
WHERE transaction_header_id = p_trx_hdr_id;
UPDATE mtl_transactions_interface
SET ERROR_CODE = substrb(l_error_code,1,240)/*added substrb for 3632722*/
, error_explanation = substrb(x_proc_msg,1,240)/*added substrb for 3632722*/
, process_flag = 3
, lock_flag = 2
WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
and transaction_batch_id = v_mmtt.transaction_batch_id;
inv_log_util.TRACE('Updated error msg for ' || l_num_ret_rows || ' rows form MTI with txnbatchid='
|| v_mmtt.transaction_batch_id, 1);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
AND transaction_batch_id = v_mmtt.transaction_batch_id);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
AND transaction_batch_id = v_mmtt.transaction_batch_id));
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
AND transaction_batch_id = v_mmtt.transaction_batch_id);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
AND transaction_batch_id = v_mmtt.transaction_batch_id;
UPDATE mtl_transactions_interface
SET ERROR_CODE = substrb(l_error_code,1,240)/*added substrb for 3632722*/
, error_explanation = substrb(x_proc_msg,1,240)/*added substrb for 3632722*/
, process_flag = 3
, lock_flag = 2
WHERE transaction_interface_id = v_mmtt.transaction_temp_id;
inv_log_util.TRACE('Updated error msg for ' || l_num_ret_rows || ' rows form MTI with txntempid='
|| v_mmtt.transaction_temp_id, 1);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = v_mmtt.transaction_temp_id);
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET ERROR_CODE = l_error_code
, error_explanation = x_proc_msg
, process_flag = 'E'
, lock_flag = 'N'
WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET ERROR_CODE = l_error_code
, process_flag = 'E'
, lock_flag = 'N'
WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
AND transaction_batch_id = v_mmtt.transaction_batch_id;
UPDATE mtl_material_transactions_temp
SET ERROR_CODE = l_error_code
, error_explanation = x_proc_msg
, process_flag = 'E'
, lock_flag = 'N'
WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
update mtl_material_transactions_temp
set secondary_transaction_quantity = l_secondary_txn_quantity
where transaction_temp_id = v_mmtt.transaction_temp_id;
SELECT COUNT(1)
INTO l_process
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id
AND process_flag = 'Y'
AND ROWNUM < 2;
SELECT 1
INTO l_is_from_mti
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id
AND transaction_mode = inv_txn_manager_pub.proc_mode_mti);
UPDATE mtl_transactions_interface
SET ERROR_CODE = substrb(l_error_code,1,240)/*added substrb for 3632722*/
, error_explanation = substrb(x_proc_msg,1,240)/*added substrb for 3632722*/
, process_flag = 3
, lock_flag = 2
WHERE transaction_header_id = p_trx_hdr_id;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id));
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_trx_hdr_id;
UPDATE mtl_material_transactions_temp
SET ERROR_CODE = l_error_code
, error_explanation = x_proc_msg
, process_flag = 'E'
, lock_flag = 'N'
WHERE transaction_header_id = p_trx_hdr_id;
SELECT interface_transaction_id
, transaction_type
INTO l_rcv_interface_txn_id
, l_rcv_txn_type
FROM rcv_transactions
WHERE transaction_id = p_rcv_transaction_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_rcv
WHERE lpn_id = p_lpn_id;
SELECT lpn_context
INTO v_cnt_lpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_content_lpn_id;
update_lpn_status(v_lpn);
SELECT interface_transaction_id
, transaction_type
INTO l_rcv_interface_txn_id
, l_rcv_txn_type
FROM (SELECT interface_transaction_id
, transaction_type
, creation_date
FROM rcv_transactions
WHERE interface_transaction_id = (SELECT interface_transaction_id
FROM rcv_transactions
WHERE transaction_id = p_rcv_transaction_id)
ORDER BY transaction_id DESC)
WHERE ROWNUM < 2;
select subinventory,locator_id
into l_sub,l_loc_id
from rcv_transactions
where transaction_id = p_rcv_transaction_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_rcv
, subinventory_code = nvl(l_sub,subinventory_code) --6374764
, locator_id = nvl(l_loc_id,locator_id) --6374764
WHERE lpn_id = p_transfer_lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_rcv
, subinventory_code = nvl(l_sub,subinventory_code) --6374764
, locator_id = nvl(l_loc_id,locator_id) --6374764
WHERE lpn_id = p_transfer_lpn_id;
UPDATE wms_lpn_contents
SET source_name = NULL
, source_header_id = NULL
, cost_group_id = NULL
WHERE parent_lpn_id = p_lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_rcv
, subinventory_code = nvl(l_sub,subinventory_code) --6374764
, locator_id = nvl(l_loc_id,locator_id) --6374764
WHERE lpn_id = p_lpn_id;
UPDATE mtl_serial_numbers
SET cost_group_id = NULL
WHERE lpn_id = p_lpn_id;
UPDATE wms_lpn_contents
SET cost_group_id = NULL
WHERE parent_lpn_id = p_lpn_id;
UPDATE mtl_serial_numbers
SET cost_group_id = NULL
WHERE lpn_id = p_lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_rcv
WHERE lpn_id = p_lpn_id;
SELECT transaction_type
INTO l_rcv_txn_type
FROM rcv_transactions
WHERE transaction_id = p_rcv_transaction_id;
UPDATE wms_lpn_contents
SET cost_group_id = p_cost_group_id
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND parent_lpn_id = p_lpn_id
AND SERIAL_SUMMARY_ENTRY=2 ;
/* Bug 3910656- Added the last condition to the query to update only those
records with the serial_summary_entry as 2. */
-- If this item is Serial controlled, set cost_group_id for Serials
UPDATE mtl_serial_numbers
SET cost_group_id = p_cost_group_id
WHERE inventory_item_id = p_inventory_item_id
AND lpn_id = p_lpn_id;
inv_log_util.TRACE('**Updated WMS_Contents cost_grp_id for lpn=' || p_lpn_id || ' and cg=' || p_cost_group_id
, 'INV_LPN_TRX_PUB', 9);
SELECT lpn_controlled_flag
INTO v_autounpack
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT lpn_context
INTO v_lpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = v_lpn.lpn_id;
update_lpn_status(v_lpn);
SELECT lpn_context
INTO v_lpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT lpn_context
INTO v_lpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = v_lpn_ctx
, subinventory_code = p_subinventory_code
, locator_id = p_locator_id
WHERE lpn_id = p_lpn_id
AND lpn_context = wms_container_pub.lpn_context_pregenerated;
SELECT lpn_controlled_flag
INTO v_autounpack
FROM mtl_secondary_inventories
WHERE organization_id = v_xfr_org
AND secondary_inventory_name = p_transfer_subinventory;
SELECT wms_task_type
INTO l_system_task_type
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT lpn_context
INTO l_lpn_ctx_tmp
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT lpn_context
INTO v_cnt_lpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_content_lpn_id;
/*if the transaction is cyclecount use outer LPN to update reservations*/
IF(p_transaction_source_type_id=inv_globals.G_SOURCETYPE_CYCLECOUNT) THEN
IF (l_debug = 1) THEN
inv_log_util.TRACE('entered here cyclecount.. Bug#6043776','INV_LPN_TRX_PUB', 9);
SELECT OUTERMOST_LPN_ID into l_cyclpn_id
from wms_license_plate_numbers
where lpn_id=p_content_lpn_id;
/** moved update here to fix bug3299521, this way we would avoid updating
the parent_lpn context for unpacked inner lpns**/
update_lpn_status(v_lpn);
SELECT lpn_context
INTO v_xfrlpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_transfer_lpn_id;
update_lpn_status(v_lpn);
update_lpn_status(v_lpn);
SELECT lpn_context
INTO v_xfrlpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_transfer_lpn_id;
update_lpn_status(v_lpn);
SELECT lpn_context
INTO v_cnt_lpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_content_lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_inv
WHERE lpn_id = p_transfer_lpn_id
AND lpn_context <> wms_container_pub.lpn_context_picked;
SELECT lpn_context
INTO v_lpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT lpn_context
INTO v_xfrlpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_transfer_lpn_id;
update_lpn_status(v_lpn);
UPDATE mtl_material_transactions_temp
SET content_lpn_id = (SELECT outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_content_lpn_id
AND rownum < 2)
WHERE transaction_temp_id = p_transaction_temp_id;
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = (SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND inventory_item_id = -1);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN(
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id =
(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND inventory_item_id = -1));
inv_log_util.TRACE('* No of MSNT records deleted =' || SQL%ROWCOUNT, 'INV_LPN_TRX_PUB', 1);
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = (SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND inventory_item_id = -1);
inv_log_util.TRACE('* No of MTLT records deleted =' || SQL%ROWCOUNT, 'INV_LPN_TRX_PUB', 1);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND inventory_item_id = -1;
inv_log_util.TRACE('* No. of MMTT record deleted ' || SQL%ROWCOUNT, 'INV_LPN_TRX_PUB', 1);