[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION insert_line_trx(
p_trx_hdr_id IN NUMBER
, p_item_id IN NUMBER
, p_revision IN VARCHAR2 := NULL
, p_org_id IN NUMBER
, p_trx_action_id IN NUMBER
, p_subinv_code IN VARCHAR2
, p_tosubinv_code IN VARCHAR2 := NULL
, p_locator_id IN NUMBER := NULL
, p_tolocator_id IN NUMBER := NULL
, p_xfr_org_id IN NUMBER := NULL
, p_trx_type_id IN NUMBER
, p_trx_src_type_id IN NUMBER
, p_trx_qty IN NUMBER
, p_pri_qty IN NUMBER
, p_uom IN VARCHAR2
, p_date IN DATE := SYSDATE
, p_reason_id IN NUMBER := NULL
, p_user_id IN NUMBER
, p_frt_code IN VARCHAR2 := NULL
, p_ship_num IN VARCHAR2 := NULL
, p_dist_id IN NUMBER := NULL
, p_way_bill IN VARCHAR2 := NULL
, p_exp_arr IN DATE := NULL
, p_cost_group IN NUMBER := NULL
, p_from_lpn_id IN NUMBER := NULL
, p_cnt_lpn_id IN NUMBER := NULL
, p_xfr_lpn_id IN NUMBER := NULL
, p_trx_src_id IN NUMBER := NULL
, x_trx_tmp_id OUT NOCOPY NUMBER
, x_proc_msg OUT NOCOPY VARCHAR2
, p_xfr_cost_group IN NUMBER := NULL
, p_completion_trx_id IN NUMBER := NULL
, p_flow_schedule IN VARCHAR2 := NULL
, p_trx_cost IN NUMBER := NULL
, p_project_id IN NUMBER := NULL
, p_task_id IN NUMBER := NULL
, p_cost_of_transfer IN NUMBER := NULL
, p_cost_of_transportation IN NUMBER := NULL
, p_transfer_percentage IN NUMBER := NULL
, p_transportation_cost_account IN NUMBER := NULL
, p_planning_org_id IN NUMBER
, p_planning_tp_type IN NUMBER
, p_owning_org_id IN NUMBER
, p_owning_tp_type IN NUMBER
, p_trx_src_line_id IN NUMBER := NULL
, p_secondary_trx_qty IN NUMBER := NULL
, p_secondary_uom IN VARCHAR2 := NULL
, p_move_order_line_id IN NUMBER := NULL
, p_posting_flag IN VARCHAR2 := NULL
, p_move_order_header_id IN NUMBER
, p_serial_allocated_flag IN VARCHAR2
, p_transaction_status IN NUMBER
, p_process_flag IN VARCHAR2 := NULL
, p_ship_to_location_id IN NUMBER --eIB Build; Bug# 4348541
/* SELECT mtl_material_transactions_s.NEXTVAL
INTO x_trx_tmp_id
FROM DUAL; */
INSERT INTO mtl_material_transactions_temp
(
transaction_header_id
, transaction_temp_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
, secondary_transaction_quantity
, secondary_uom_code
, 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
, transfer_cost_group_id
, completion_transaction_id
, flow_schedule
, transaction_cost
, project_id
, task_id
, planning_organization_id
, planning_tp_type
, owning_organization_id
, owning_tp_type
, posting_flag
, transfer_cost
, transportation_cost
, transfer_percentage
, transportation_account
, move_order_header_id
, move_order_line_id
, serial_allocated_flag
, transaction_status
, ship_to_location --eIB Build; Bug# 4348541
FUNCTION insert_lot_trx(
p_trx_tmp_id IN NUMBER
, p_user_id IN NUMBER
, p_lot_number IN VARCHAR2
, p_trx_qty IN NUMBER
, p_pri_qty IN NUMBER
, p_exp_date IN DATE := NULL
, p_description IN VARCHAR2 := NULL
, p_vendor_name IN VARCHAR2 := NULL
, p_supplier_lot_number IN VARCHAR2 := NULL
, p_origination_date IN DATE := NULL
, p_date_code IN VARCHAR2 := NULL
, p_grade_code IN VARCHAR2 := NULL
, p_change_date IN DATE := NULL
, p_maturity_date IN DATE := NULL
, p_status_id IN NUMBER := NULL
, p_retest_date IN DATE := NULL
, p_age IN NUMBER := NULL
, p_item_size IN NUMBER := NULL
, p_color IN VARCHAR2 := NULL
, p_volume IN NUMBER := NULL
, p_volume_uom IN VARCHAR2 := NULL
, p_place_of_origin IN VARCHAR2 := NULL
, p_best_by_date IN DATE := NULL
, p_length IN NUMBER := NULL
, p_length_uom IN VARCHAR2 := NULL
, p_recycled_content IN NUMBER := NULL
, p_thickness IN NUMBER := NULL
, p_thickness_uom IN VARCHAR2 := NULL
, p_width IN NUMBER := NULL
, p_width_uom IN VARCHAR2 := NULL
, p_curl_wrinkle_fold IN VARCHAR2 := NULL
, p_lot_attribute_category IN VARCHAR2 := NULL
, p_c_attribute1 IN VARCHAR2 := NULL
, p_c_attribute2 IN VARCHAR2 := NULL
, p_c_attribute3 IN VARCHAR2 := NULL
, p_c_attribute4 IN VARCHAR2 := NULL
, p_c_attribute5 IN VARCHAR2 := NULL
, p_c_attribute6 IN VARCHAR2 := NULL
, p_c_attribute7 IN VARCHAR2 := NULL
, p_c_attribute8 IN VARCHAR2 := NULL
, p_c_attribute9 IN VARCHAR2 := NULL
, p_c_attribute10 IN VARCHAR2 := NULL
, p_c_attribute11 IN VARCHAR2 := NULL
, p_c_attribute12 IN VARCHAR2 := NULL
, p_c_attribute13 IN VARCHAR2 := NULL
, p_c_attribute14 IN VARCHAR2 := NULL
, p_c_attribute15 IN VARCHAR2 := NULL
, p_c_attribute16 IN VARCHAR2 := NULL
, p_c_attribute17 IN VARCHAR2 := NULL
, p_c_attribute18 IN VARCHAR2 := NULL
, p_c_attribute19 IN VARCHAR2 := NULL
, p_c_attribute20 IN VARCHAR2 := NULL
, p_d_attribute1 IN DATE := NULL
, p_d_attribute2 IN DATE := NULL
, p_d_attribute3 IN DATE := NULL
, p_d_attribute4 IN DATE := NULL
, p_d_attribute5 IN DATE := NULL
, p_d_attribute6 IN DATE := NULL
, p_d_attribute7 IN DATE := NULL
, p_d_attribute8 IN DATE := NULL
, p_d_attribute9 IN DATE := NULL
, p_d_attribute10 IN DATE := NULL
, p_n_attribute1 IN NUMBER := NULL
, p_n_attribute2 IN NUMBER := NULL
, p_n_attribute3 IN NUMBER := NULL
, p_n_attribute4 IN NUMBER := NULL
, p_n_attribute5 IN NUMBER := NULL
, p_n_attribute6 IN NUMBER := NULL
, p_n_attribute7 IN NUMBER := NULL
, p_n_attribute8 IN NUMBER := NULL
, p_n_attribute9 IN NUMBER := NULL
, p_n_attribute10 IN NUMBER := NULL
, x_ser_trx_id OUT NOCOPY NUMBER
, x_proc_msg OUT NOCOPY VARCHAR2
, p_territory_code IN VARCHAR2 := NULL
, p_vendor_id IN VARCHAR2 := NULL
, p_secondary_qty IN NUMBER := NULL
, p_secondary_uom IN VARCHAR2 := NULL
--Bug No 3952081
--Add arguments to intake new OPM attributes of the lot
, p_parent_lot_number IN MTL_LOT_NUMBERS.PARENT_LOT_NUMBER%TYPE := NULL
, p_origination_type IN MTL_LOT_NUMBERS.ORIGINATION_TYPE%TYPE := NULL
, p_expriration_action_date IN MTL_LOT_NUMBERS.EXPIRATION_ACTION_DATE%TYPE := NULL
, p_expriration_action_code IN MTL_LOT_NUMBERS.EXPIRATION_ACTION_CODE%TYPE := NULL
, p_hold_date IN MTL_LOT_NUMBERS.HOLD_DATE%TYPE := NULL
)
RETURN NUMBER IS
-- Bug# 2032659 Beginning
l_description VARCHAR(250) := NULL;
SELECT description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, 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
INTO l_description
, l_vendor_name
, l_supplier_lot_number
, l_origination_date
, l_date_code
, l_grade_code
, l_change_date
, l_maturity_date
, l_retest_date
, l_age
, l_item_size
, l_color
, l_volume
, l_volume_uom
, l_place_of_origin
, l_best_by_date
, l_length
, l_length_uom
, l_recycled_content
, l_thickness
, l_thickness_uom
, l_width
, l_width_uom
, l_curl_wrinkle_fold
, l_lot_attribute_category
, l_c_attribute1
, l_c_attribute2
, l_c_attribute3
, l_c_attribute4
, l_c_attribute5
, l_c_attribute6
, l_c_attribute7
, l_c_attribute8
, l_c_attribute9
, l_c_attribute10
, l_c_attribute11
, l_c_attribute12
, l_c_attribute13
, l_c_attribute14
, l_c_attribute15
, l_c_attribute16
, l_c_attribute17
, l_c_attribute18
, l_c_attribute19
, l_c_attribute20
, l_d_attribute1
, l_d_attribute2
, l_d_attribute3
, l_d_attribute4
, l_d_attribute5
, l_d_attribute6
, l_d_attribute7
, l_d_attribute8
, l_d_attribute9
, l_d_attribute10
, l_n_attribute1
, l_n_attribute2
, l_n_attribute3
, l_n_attribute4
, l_n_attribute5
, l_n_attribute6
, l_n_attribute7
, l_n_attribute8
, l_n_attribute9
, l_n_attribute10
, l_vendor_id
, l_territory_code
FROM mtl_lot_numbers mln, mtl_material_transactions_temp mmtt
WHERE mln.lot_number = LTRIM(RTRIM(p_lot_number))
AND mmtt.transaction_temp_id = p_trx_tmp_id
AND mln.organization_id = mmtt.organization_id
AND mln.inventory_item_id = mmtt.inventory_item_id;
INSERT INTO mtl_transaction_lots_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, transaction_quantity
, primary_quantity
, secondary_quantity
, secondary_unit_of_measure
, lot_number
, lot_expiration_date
, serial_transaction_temp_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
--Bug No 3952081
--Insert OPM attributes
, PARENT_LOT_NUMBER
, ORIGINATION_TYPE
, EXPIRATION_ACTION_DATE
, EXPIRATION_ACTION_CODE
, HOLD_DATE
)
VALUES (
p_trx_tmp_id
, SYSDATE
, p_user_id
, SYSDATE
, p_user_id
, p_trx_qty
, p_pri_qty
, p_secondary_qty
, p_secondary_uom
, LTRIM(RTRIM(p_lot_number))
, p_exp_date
-- , x_ser_trx_id
, mtl_material_transactions_s.NEXTVAL
, NVL(p_description, l_description)
, NVL(p_vendor_name, l_vendor_name)
, NVL(p_supplier_lot_number, l_supplier_lot_number)
, NVL(p_origination_date, l_origination_date)
, NVL(p_date_code, l_date_code)
, NVL(p_grade_code, l_grade_code)
, NVL(p_change_date, l_change_date)
, NVL(p_maturity_date, l_maturity_date)
, p_status_id -- This is not attribute column
, NVL(p_retest_date, l_retest_date)
, NVL(p_age, l_age)
, NVL(p_item_size, l_item_size)
, NVL(p_color, l_color)
, NVL(p_volume, l_volume)
, NVL(p_volume_uom, l_volume_uom)
, NVL(p_place_of_origin, l_place_of_origin)
, NVL(p_best_by_date, l_best_by_date)
, NVL(p_length, l_length)
, NVL(p_length_uom, l_length_uom)
, NVL(p_recycled_content, l_recycled_content)
, NVL(p_thickness, l_thickness)
, NVL(p_thickness_uom, l_thickness_uom)
, NVL(p_width, l_width)
, NVL(p_width_uom, l_width_uom)
, NVL(p_curl_wrinkle_fold, l_curl_wrinkle_fold)
, NVL(p_lot_attribute_category, l_lot_attribute_category)
, NVL(p_c_attribute1, l_c_attribute1)
, NVL(p_c_attribute2, l_c_attribute2)
, NVL(p_c_attribute3, l_c_attribute3)
, NVL(p_c_attribute4, l_c_attribute4)
, NVL(p_c_attribute5, l_c_attribute5)
, NVL(p_c_attribute6, l_c_attribute6)
, NVL(p_c_attribute7, l_c_attribute7)
, NVL(p_c_attribute8, l_c_attribute8)
, NVL(p_c_attribute9, l_c_attribute9)
, NVL(p_c_attribute10, l_c_attribute10)
, NVL(p_c_attribute11, l_c_attribute11)
, NVL(p_c_attribute12, l_c_attribute12)
, NVL(p_c_attribute13, l_c_attribute13)
, NVL(p_c_attribute14, l_c_attribute14)
, NVL(p_c_attribute15, l_c_attribute15)
, NVL(p_c_attribute16, l_c_attribute16)
, NVL(p_c_attribute17, l_c_attribute17)
, NVL(p_c_attribute18, l_c_attribute18)
, NVL(p_c_attribute19, l_c_attribute19)
, NVL(p_c_attribute20, l_c_attribute20)
, NVL(p_d_attribute1, l_d_attribute1)
, NVL(p_d_attribute2, l_d_attribute2)
, NVL(p_d_attribute3, l_d_attribute3)
, NVL(p_d_attribute4, l_d_attribute4)
, NVL(p_d_attribute5, l_d_attribute5)
, NVL(p_d_attribute6, l_d_attribute6)
, NVL(p_d_attribute7, l_d_attribute7)
, NVL(p_d_attribute8, l_d_attribute8)
, NVL(p_d_attribute9, l_d_attribute9)
, NVL(p_d_attribute10, l_d_attribute10)
, NVL(p_n_attribute1, l_n_attribute1)
, NVL(p_n_attribute2, l_n_attribute2)
, NVL(p_n_attribute3, l_n_attribute3)
, NVL(p_n_attribute4, l_n_attribute4)
, NVL(p_n_attribute5, l_n_attribute5)
, NVL(p_n_attribute6, l_n_attribute6)
, NVL(p_n_attribute7, l_n_attribute7)
, NVL(p_n_attribute8, l_n_attribute8)
, NVL(p_n_attribute9, l_n_attribute9)
, NVL(p_n_attribute10, l_n_attribute10)
, NVL(p_vendor_id, l_vendor_id)
, NVL(p_territory_code, l_territory_code)
--Bug 3952081
--Use tha passed arguments directly to populate MTLT.
, p_parent_lot_number
, p_origination_type
, p_expriration_action_date
, p_expriration_action_code
, p_hold_date
) RETURNING serial_transaction_temp_id INTO x_ser_trx_id;
FUNCTION insert_ser_trx(
p_trx_tmp_id IN NUMBER
, p_user_id IN NUMBER
, p_fm_ser_num IN VARCHAR2
, p_to_ser_num IN VARCHAR2
, p_ven_ser_num IN VARCHAR2 := NULL
, p_vet_lot_num IN VARCHAR2 := NULL
, p_parent_ser_num IN VARCHAR2 := NULL
, p_end_item_unit_num IN VARCHAR2 := NULL
, p_serial_attribute_category IN VARCHAR2 := NULL
, p_orgination_date IN DATE := NULL
, p_c_attribute1 IN VARCHAR2 := NULL
, p_c_attribute2 IN VARCHAR2 := NULL
, p_c_attribute3 IN VARCHAR2 := NULL
, p_c_attribute4 IN VARCHAR2 := NULL
, p_c_attribute5 IN VARCHAR2 := NULL
, p_c_attribute6 IN VARCHAR2 := NULL
, p_c_attribute7 IN VARCHAR2 := NULL
, p_c_attribute8 IN VARCHAR2 := NULL
, p_c_attribute9 IN VARCHAR2 := NULL
, p_c_attribute10 IN VARCHAR2 := NULL
, p_c_attribute11 IN VARCHAR2 := NULL
, p_c_attribute12 IN VARCHAR2 := NULL
, p_c_attribute13 IN VARCHAR2 := NULL
, p_c_attribute14 IN VARCHAR2 := NULL
, p_c_attribute15 IN VARCHAR2 := NULL
, p_c_attribute16 IN VARCHAR2 := NULL
, p_c_attribute17 IN VARCHAR2 := NULL
, p_c_attribute18 IN VARCHAR2 := NULL
, p_c_attribute19 IN VARCHAR2 := NULL
, p_c_attribute20 IN VARCHAR2 := NULL
, p_d_attribute1 IN DATE := NULL
, p_d_attribute2 IN DATE := NULL
, p_d_attribute3 IN DATE := NULL
, p_d_attribute4 IN DATE := NULL
, p_d_attribute5 IN DATE := NULL
, p_d_attribute6 IN DATE := NULL
, p_d_attribute7 IN DATE := NULL
, p_d_attribute8 IN DATE := NULL
, p_d_attribute9 IN DATE := NULL
, p_d_attribute10 IN DATE := NULL
, p_n_attribute1 IN NUMBER := NULL
, p_n_attribute2 IN NUMBER := NULL
, p_n_attribute3 IN NUMBER := NULL
, p_n_attribute4 IN NUMBER := NULL
, p_n_attribute5 IN NUMBER := NULL
, p_n_attribute6 IN NUMBER := NULL
, p_n_attribute7 IN NUMBER := NULL
, p_n_attribute8 IN NUMBER := NULL
, p_n_attribute9 IN NUMBER := NULL
, p_n_attribute10 IN NUMBER := NULL
, p_status_id IN NUMBER := NULL
, p_territory_code IN VARCHAR2 := NULL
, p_time_since_new IN NUMBER := NULL
, p_cycles_since_new IN NUMBER := NULL
, p_time_since_overhaul IN NUMBER := NULL
, p_cycles_since_overhaul IN NUMBER := NULL
, p_time_since_repair IN NUMBER := NULL
, p_cycles_since_repair IN NUMBER := NULL
, p_time_since_visit IN NUMBER := NULL
, p_cycles_since_visit IN NUMBER := NULL
, p_time_since_mark IN NUMBER := NULL
, p_cycles_since_mark IN NUMBER := NULL
, p_number_of_repairs IN NUMBER := NULL
, p_validation_level IN NUMBER := NULL
, p_wms_installed IN VARCHAR2 := NULL
, p_quantity IN NUMBER := NULL -- Number of Serials between FROM and TO
, x_proc_msg OUT NOCOPY VARCHAR2
, p_attribute_category IN VARCHAR2 := NULL
, p_attribute1 IN VARCHAR2 := NULL
, p_attribute2 IN VARCHAR2 := NULL
, p_attribute3 IN VARCHAR2 := NULL
, p_attribute4 IN VARCHAR2 := NULL
, p_attribute5 IN VARCHAR2 := NULL
, p_attribute6 IN VARCHAR2 := NULL
, p_attribute7 IN VARCHAR2 := NULL
, p_attribute8 IN VARCHAR2 := NULL
, p_attribute9 IN VARCHAR2 := NULL
, p_attribute10 IN VARCHAR2 := NULL
, p_attribute11 IN VARCHAR2 := NULL
, p_attribute12 IN VARCHAR2 := NULL
, p_attribute13 IN VARCHAR2 := NULL
, p_attribute14 IN VARCHAR2 := NULL
, p_attribute15 IN VARCHAR2 := NULL
, p_dffupdatedflag IN VARCHAR2 := NULL
)
RETURN NUMBER IS
l_serial_prefix NUMBER;
SELECT mmtt.transaction_temp_id
, mmtt.transaction_header_id
, mmtt.inventory_item_id
, mmtt.organization_id
, mmtt.transaction_type_id
, mmtt.subinventory_code
, mmtt.locator_id
INTO l_transaction_temp_id
, l_trx_header_id
, l_item_id
, l_org_id
, l_trx_type_id
, l_subinventory_code
, l_locator_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_trx_tmp_id;
SELECT mmtt.transaction_temp_id
, mmtt.transaction_header_id
, mmtt.inventory_item_id
, mmtt.organization_id
, mmtt.transaction_type_id
, mmtt.subinventory_code
, mmtt.locator_id
, mtlt.lot_number
INTO l_transaction_temp_id
, l_trx_header_id
, l_item_id
, l_org_id
, l_trx_type_id
, l_subinventory_code
, l_locator_id
, l_lot_number
FROM mtl_transaction_lots_temp mtlt, mtl_material_transactions_temp mmtt
WHERE mtlt.serial_transaction_temp_id = p_trx_tmp_id
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id;
SELECT COUNT(msn.serial_number)
INTO l_number_of_serial_numbers
FROM mtl_serial_numbers msn
WHERE msn.inventory_item_id = l_item_id
AND msn.serial_number BETWEEN p_fm_ser_num AND p_to_ser_num
AND LENGTH(msn.serial_number) = LENGTH(p_fm_ser_num)
AND current_status = 3
AND msn.current_organization_id = l_org_id
AND(msn.group_mark_id IS NULL OR msn.group_mark_id <= 0)
AND msn.current_subinventory_code = l_subinventory_code
/*Fixed for bug#6758460
Condition modified to handle the null locator id
if item is non locator controlled then this condition
fails and cause group mark id not marked in MSN
*/
/*AND msn.current_locator_id = l_locator_id*/
AND nvl(msn.current_locator_id,-999999) = nvl(l_locator_id,-999999)
AND(l_lot_number IS NULL OR msn.lot_number = l_lot_number)
AND(
inv_material_status_grp.is_status_applicable(
p_wms_installed
, NULL -- p_trx_status_enabled
, l_trx_type_id
, NULL -- p_lot_status_enabled
, NULL -- p_serial_status_enabled
, l_org_id
, l_item_id
, l_subinventory_code
, l_locator_id
, l_lot_number
, msn.serial_number
, 'A'
) = 'Y'
);
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, vendor_serial_number
, vendor_lot_number
, fm_serial_number
, to_serial_number
, serial_prefix -- Bug#2527211
, 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
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, dff_updated_flag
)
VALUES (
p_trx_tmp_id
, SYSDATE
, p_user_id
, SYSDATE
, p_user_id
, p_user_id
, p_ven_ser_num
, p_vet_lot_num
, p_fm_ser_num
, p_to_ser_num
, NVL(l_serial_prefix, 1) -- Bug#2527211
, p_parent_ser_num
, p_end_item_unit_num
, p_serial_attribute_category
, p_orgination_date
, p_c_attribute1
, p_c_attribute2
, p_c_attribute3
, p_c_attribute4
, p_c_attribute5
, p_c_attribute6
, p_c_attribute7
, p_c_attribute8
, p_c_attribute9
, p_c_attribute10
, p_c_attribute11
, p_c_attribute12
, p_c_attribute13
, p_c_attribute14
, p_c_attribute15
, p_c_attribute16
, p_c_attribute17
, p_c_attribute18
, p_c_attribute19
, p_c_attribute20
, p_d_attribute1
, p_d_attribute2
, p_d_attribute3
, p_d_attribute4
, p_d_attribute5
, p_d_attribute6
, p_d_attribute7
, p_d_attribute8
, p_d_attribute9
, p_d_attribute10
, p_n_attribute1
, p_n_attribute2
, p_n_attribute3
, p_n_attribute4
, p_n_attribute5
, p_n_attribute6
, p_n_attribute7
, p_n_attribute8
, p_n_attribute9
, p_n_attribute10
, p_status_id
, p_territory_code
, p_time_since_new
, p_cycles_since_new
, p_time_since_overhaul
, p_cycles_since_overhaul
, p_time_since_repair
, p_cycles_since_repair
, p_time_since_visit
, p_cycles_since_visit
, p_time_since_mark
, p_cycles_since_mark
, p_number_of_repairs
, p_attribute_category
, p_attribute1
, p_attribute2
, p_attribute3
, p_attribute4
, p_attribute5
, p_attribute6
, p_attribute7
, p_attribute8
, p_attribute9
, p_attribute10
, p_attribute11
, p_attribute12
, p_attribute13
, p_attribute14
, p_attribute15
, p_dffupdatedflag
);
PROCEDURE copy_insert_line_trx(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_new_txn_temp_id OUT NOCOPY NUMBER
, p_transaction_temp_id IN NUMBER
, p_transaction_header_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_revision IN VARCHAR2
, p_organization_id IN NUMBER
, p_subinventory_code IN VARCHAR2
, p_locator_id IN NUMBER
, p_cost_group_id IN NUMBER
, p_to_organization_id IN NUMBER
, p_to_subinventory_code IN VARCHAR2
, p_to_locator_id IN NUMBER
, p_to_cost_group_id IN NUMBER
, p_txn_qty IN NUMBER
, p_primary_qty IN NUMBER
, p_sec_txn_qty IN NUMBER --INVCONV KKILLAMS
, p_transaction_uom IN VARCHAR2
, p_lpn_id IN NUMBER
, p_transfer_lpn_id IN NUMBER
, p_content_lpn_id IN NUMBER
, p_txn_type_id IN NUMBER
, p_txn_action_id IN NUMBER
, p_txn_source_type_id IN NUMBER
, p_transaction_date IN DATE
, p_transaction_source_id IN NUMBER
, p_trx_source_line_id IN NUMBER
, p_move_order_line_id IN NUMBER
, p_reservation_id IN NUMBER
, p_parent_line_id IN NUMBER
, p_pick_slip_number IN NUMBER
, p_wms_task_type IN NUMBER
, p_user_id IN NUMBER
, p_move_order_header_id IN NUMBER
, p_serial_allocated_flag IN VARCHAR2
, p_operation_plan_id IN NUMBER --lezhang
, p_transaction_status IN NUMBER
) IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
l_api_name VARCHAR2(30) := 'COPY_INSERT_LINE_TRX';
SELECT mmtt.organization_id, mmtt.inventory_item_id, mmtt.transaction_uom
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_temp_id;
SELECT primary_uom_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_inventory_item_id
AND msi.organization_id = l_organization_id;
SELECT mtl_material_transactions_s.NEXTVAL INTO x_new_txn_temp_id FROM DUAL;
INSERT INTO mtl_material_transactions_temp(
transaction_header_id
, transaction_temp_id
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, cost_group_id
, transfer_organization
, transfer_subinventory
, transfer_to_location
, transfer_cost_group_id
, transaction_quantity
, primary_quantity
, transaction_uom
, move_order_header_id
, move_order_line_id
, serial_allocated_flag
, reservation_id
, lpn_id
, transfer_lpn_id
, content_lpn_id
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_name
, transaction_source_id
, trx_source_line_id
, trx_source_delivery_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, transaction_cost
, transaction_date
, acct_period_id
, distribution_account_id
, parent_line_id
, parent_transaction_temp_id
, pick_slip_number
, container_item_id
, cartonization_id
, standard_operation_id
, operation_plan_id
, wms_task_type
, wms_task_status
, task_priority
, task_group_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
, 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
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, allowed_units_lookup_code
, wip_entity_type
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, wip_commit_flag
, shippable_flag
, posting_flag
, required_flag
, process_flag
, 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
, 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
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, transaction_status
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, secondary_transaction_quantity --INVCONV kkillams
)
SELECT decode(p_transaction_header_id, fnd_api.g_miss_num, NULL, NULL, transaction_header_id, p_transaction_header_id)
, x_new_txn_temp_id
, decode(l_inventory_item_id, NULL, inventory_item_id, l_inventory_item_id)
, decode(p_revision, fnd_api.g_miss_char, NULL, NULL, revision, p_revision)
, decode(l_organization_id, NULL, organization_id, l_organization_id)
, decode(p_subinventory_code, fnd_api.g_miss_char, NULL, NULL, subinventory_code, p_subinventory_code)
, decode(p_locator_id, fnd_api.g_miss_num, NULL, NULL, locator_id, p_locator_id)
, decode(p_cost_group_id, fnd_api.g_miss_num, NULL, NULL, cost_group_id, p_cost_group_id)
, decode(p_to_organization_id, fnd_api.g_miss_num, NULL, NULL, transfer_organization, p_to_organization_id)
, decode(p_to_subinventory_code, fnd_api.g_miss_char, NULL, NULL, transfer_subinventory, p_to_subinventory_code)
, decode(p_to_locator_id, fnd_api.g_miss_num, NULL, NULL, transfer_to_location, p_to_locator_id)
, decode(p_to_cost_group_id, fnd_api.g_miss_num, NULL, NULL, transfer_cost_group_id, p_to_cost_group_id)
, decode(l_txn_qty, NULL, transaction_quantity, l_txn_qty)
, decode(l_primary_qty, NULL, primary_quantity, l_primary_qty)
, decode(l_transaction_uom, NULL, transaction_uom, l_transaction_uom)
, decode(p_move_order_header_id, fnd_api.g_miss_num, NULL, NULL, move_order_header_id, p_move_order_header_id)
, decode(p_move_order_line_id, fnd_api.g_miss_num, NULL, NULL, move_order_line_id, p_move_order_line_id)
, decode(p_serial_allocated_flag, fnd_api.g_miss_char, NULL, NULL, serial_allocated_flag, p_serial_allocated_flag)
, decode(p_reservation_id, fnd_api.g_miss_num, NULL, NULL, reservation_id, p_reservation_id)
, decode(p_lpn_id, fnd_api.g_miss_num, NULL, NULL, lpn_id, p_lpn_id)
, decode(p_transfer_lpn_id, fnd_api.g_miss_num, NULL, NULL, transfer_lpn_id, p_transfer_lpn_id)
, decode(p_content_lpn_id, fnd_api.g_miss_num, NULL, NULL, content_lpn_id, p_content_lpn_id)
, decode(p_txn_type_id, NULL, transaction_type_id, p_txn_type_id)
, decode(p_txn_action_id, NULL, transaction_action_id, p_txn_action_id)
, decode(p_txn_source_type_id, NULL, transaction_source_type_id, p_txn_source_type_id)
, transaction_source_name
, decode(p_transaction_source_id, fnd_api.g_miss_num, NULL, NULL, transaction_source_id, p_transaction_source_id)
, decode(p_trx_source_line_id, fnd_api.g_miss_num, NULL, NULL, trx_source_line_id, p_trx_source_line_id)
, trx_source_delivery_id
, decode(p_transaction_source_id, fnd_api.g_miss_num, NULL, NULL, demand_source_header_id, p_transaction_source_id)
, decode(p_trx_source_line_id, fnd_api.g_miss_num, NULL, NULL, demand_source_line, p_trx_source_line_id)
, demand_source_delivery
, transaction_cost
, l_transaction_date
, l_acct_period_id
, distribution_account_id
, decode(p_parent_line_id, fnd_api.g_miss_num, NULL, NULL, parent_line_id, p_parent_line_id)
, parent_transaction_temp_id
, decode(p_pick_slip_number, fnd_api.g_miss_num, NULL, NULL, pick_slip_number, p_pick_slip_number)
, container_item_id
, cartonization_id
, standard_operation_id
, decode(p_operation_plan_id, fnd_api.g_miss_num, NULL, NULL, operation_plan_id, p_operation_plan_id) --lezhang
, decode(p_wms_task_type, fnd_api.g_miss_num, NULL, NULL, wms_task_type, p_wms_task_type)
, wms_task_status
, task_priority
, task_group_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
, 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
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, allowed_units_lookup_code
, wip_entity_type
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, wip_commit_flag
, shippable_flag
, posting_flag
, required_flag
, process_flag
, 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
, 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
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, NVL(p_transaction_status, transaction_status)
, SYSDATE
, nvl(p_user_id, fnd_global.user_id)
, SYSDATE
, nvl(p_user_id, fnd_global.user_id)
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, decode(p_sec_txn_qty, fnd_api.g_miss_num, NULL, NULL, secondary_transaction_quantity, p_sec_txn_qty) --INVCONV KKILLAMS
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
inv_log_util.trace('Inserted a new record into MMTT with TxnTempID = ' || x_new_txn_temp_id, g_pkg_name || '.' || l_api_name, 5);
END copy_insert_line_trx;
FUNCTION delete_ser_trx(
p_trx_header_id IN NUMBER
, p_trx_tmp_id IN NUMBER
, p_serial_trx_tmp_id IN NUMBER
, p_serial_control_code IN NUMBER
, p_user_id IN NUMBER
, x_proc_msg OUT NOCOPY VARCHAR2
)
RETURN NUMBER IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = NVL(p_serial_trx_tmp_id, p_trx_tmp_id);
* Procedure: DELETE_TRANSACTION
* 1. Deletes a MMTT record given the Transaction Temp ID
* 2. If it is a Lot Controlled Item, cascades the Delete till MTLT
* 3. If it is a Serial Controlled Item , cascades the Delete till MSNT. Unmarks the Serial.
* 4. Cascades the delete till WDT. Care should be taked to call the API if the Task is Loaded.
*/
PROCEDURE delete_transaction(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, p_transaction_temp_id NUMBER
, p_update_parent BOOLEAN
) IS
l_inventory_item_id NUMBER;
SELECT msi.inventory_item_id, msi.lot_control_code, msi.serial_number_control_code, mmtt.parent_line_id
,mmtt.transaction_header_id --Bug#6211912
FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_temp_id
AND msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id;
TRACE('Cleaning up MMTT, MTLT and MSNT for Txn Temp ID = ' || p_transaction_temp_id, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
TRACE('Item ID = ' || l_inventory_item_id, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
TRACE('Lot Control = ' || l_lot_control_code, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
TRACE('Serial Control = ' || l_serial_control_code, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
TRACE('Parent Line ID = ' || l_parent_line_id, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
IF l_parent_line_id IS NOT NULL AND p_update_parent THEN
IF l_debug = 1 THEN
TRACE('Child Record... Updating the Parent: TxnTempID = ' || l_parent_line_id, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
update_parent_mmtt(
x_return_status => x_return_status
, p_parent_line_id => l_parent_line_id
, p_child_line_id => p_transaction_temp_id
, p_lot_control_code => l_lot_control_code
, p_serial_control_code => l_serial_control_code
);
TRACE('Error occurred while updating the Parent Record', 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
UPDATE mtl_serial_numbers
SET group_mark_id = NULL, line_mark_id = NULL, lot_line_mark_id = NULL
WHERE group_mark_id IN (SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id)
OR group_mark_id = l_txn_hdr_id ; --Bug#6211912
DELETE 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);
UPDATE mtl_serial_numbers
SET group_mark_id = NULL, line_mark_id = NULL, lot_line_mark_id = NULL
WHERE group_mark_id = p_transaction_temp_id
OR group_mark_id = l_txn_hdr_id ; --Bug#6211912
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id;
TRACE('Serials unmarked in MSN = ' || l_unmarked_count, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
TRACE('Records deleted in MSNT = ' || SQL%ROWCOUNT, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id;
TRACE('Records deleted in MTLT = ' || SQL%ROWCOUNT, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
DELETE wms_dispatched_tasks
WHERE transaction_temp_id = p_transaction_temp_id;
TRACE('Records deleted in WDT = ' || SQL%ROWCOUNT, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
TRACE('Records deleted in MMTT = ' || SQL%ROWCOUNT, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
TRACE('Exception Occurred = ' || SQLERRM, 'INV_TRX_UTIL_PUB.DELETE_TRANSACTION');
END delete_transaction;
PROCEDURE delete_lot_ser_trx(
p_trx_tmp_id IN NUMBER
, p_org_id IN NUMBER
, p_item_id IN NUMBER
, p_lotctrl IN NUMBER
, p_serctrl IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR c_serial(l_txn_tmp_id IN NUMBER) IS
SELECT fm_serial_number, NVL(to_serial_number, fm_serial_number) to_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_txn_tmp_id;
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_txn_tmp_id;
TRACE('parameters passed to delete_lot_ser_trx', 'INVTRXUB', 9);
DELETE FROM mtl_serial_numbers_temp msnt WHERE msnt.transaction_temp_id = p_trx_tmp_id;
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN(
SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
AND mtlt.transaction_temp_id = p_trx_tmp_id);
DELETE FROM mtl_transaction_lots_temp WHERE transaction_temp_id = p_trx_tmp_id;
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id = p_trx_tmp_id;
TRACE('Records deleted in MTLT = ' || SQL%ROWCOUNT, 'INV_TRX_UTIL_PUB.delete_lot_ser_trx');
END delete_lot_ser_trx;
* Procedure: UPDATE_PARENT_MMTT
* This procedure updates or deletes the parent task when one of the child tasks
* is deleted. Generally this procedure is called before deleting a Child Record.
* 1. Parent MMTT Qty is updated if there will be more than one MMTT even after
* the deletion of the child record.
* 2. Parent MMTT is deleted along with the Task when there will be only one MMTT
* after the deletion of the child record. Child Tasks will not be dispatched
* or Queued.
*/
PROCEDURE update_parent_mmtt(
x_return_status OUT NOCOPY VARCHAR2
, p_parent_line_id IN NUMBER
, p_child_line_id IN NUMBER
, p_lot_control_code IN NUMBER
, p_serial_control_code IN NUMBER
) IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
SELECT c.inventory_item_id, c.primary_quantity, c.transaction_quantity, c.transaction_uom, p.transaction_uom
FROM mtl_material_transactions_temp c, mtl_material_transactions_temp p
WHERE c.transaction_temp_id = p_child_line_id
AND p.transaction_temp_id = p_parent_line_id;
TRACE('Either Parent TxnTempID or Child TxnTempID is invalid', 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id IN (SELECT serial_transaction_temp_id FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_parent_line_id)
AND fm_serial_number IN (SELECT msnt.fm_serial_number
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_child_line_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id)
RETURNING fm_serial_number BULK COLLECT INTO l_serials_tbl;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id IN (SELECT serial_transaction_temp_id FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_parent_line_id)
AND ROWNUM <= l_child_pri_qty
RETURNING fm_serial_number BULK COLLECT INTO l_serials_tbl;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = p_parent_line_id
AND fm_serial_number IN (SELECT msnt.fm_serial_number FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_child_line_id)
RETURNING fm_serial_number BULK COLLECT INTO l_serials_tbl;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = p_parent_line_id
AND ROWNUM <= l_child_pri_qty
RETURNING fm_serial_number BULK COLLECT INTO l_serials_tbl;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL, line_mark_id = NULL, lot_line_mark_id = NULL
WHERE inventory_item_id = l_item_id
AND serial_number = l_serials_tbl(i);
UPDATE mtl_transaction_lots_temp p
SET (p.primary_quantity, p.transaction_quantity) =
(SELECT p.primary_quantity - SUM(c.primary_quantity)
, p.transaction_quantity - inv_convert.inv_um_convert(l_item_id, NULL, SUM(c.transaction_quantity), l_child_uom, l_parent_uom, NULL, NULL)
FROM mtl_transaction_lots_temp c
WHERE c.transaction_temp_id = p_child_line_id
AND c.lot_number = p.lot_number
GROUP BY c.lot_number)
WHERE p.transaction_temp_id = p_parent_line_id;
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id = p_parent_line_id
AND primary_quantity <= 0;
TRACE('Updating the Parent Task with Txn Temp ID = ' || p_parent_line_id, 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - inv_convert.inv_um_convert(inventory_item_id, NULL, l_child_txn_qty, l_child_uom, transaction_uom, NULL, NULL)
, primary_quantity = primary_quantity - l_child_pri_qty
WHERE transaction_temp_id = p_parent_line_id
RETURNING primary_quantity INTO l_parent_pri_qty;
, 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
, 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
TRACE('Deleting the Parent Task with Txn Temp ID = ' || p_parent_line_id, 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
DELETE wms_dispatched_tasks WHERE transaction_temp_id = p_parent_line_id;
DELETE mtl_material_transactions_temp WHERE transaction_temp_id = p_parent_line_id;
TRACE('Unexpected Error occurred - ' || SQLERRM, 'INV_TRX_UTIL_PUB.UPDATE_PARENT_MMTT');
END update_parent_mmtt;