[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_wlpni
(p_api_version IN NUMBER
, p_init_msg_lst IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_ORGANIZATION_ID IN NUMBER
, p_LPN_ID IN NUMBER
, p_license_plate_number IN VARCHAR2
, p_LPN_GROUP_ID IN NUMBER
, p_PARENT_LPN_ID IN NUMBER
, p_PARENT_LICENSE_PLATE_NUMBER IN VARCHAR2
, p_REQUEST_ID IN NUMBER
, p_INVENTORY_ITEM_ID IN NUMBER
, p_REVISION IN VARCHAR2
, p_LOT_NUMBER IN VARCHAR2
, p_SERIAL_NUMBER IN VARCHAR2
, p_SUBINVENTORY_CODE IN VARCHAR2
, p_LOCATOR_ID IN NUMBER
, p_GROSS_WEIGHT_UOM_CODE IN VARCHAR2
, p_GROSS_WEIGHT IN NUMBER
, p_CONTENT_VOLUME_UOM_CODE IN VARCHAR2
, p_CONTENT_VOLUME IN NUMBER
, p_TARE_WEIGHT_UOM_CODE IN VARCHAR2
, p_TARE_WEIGHT IN NUMBER
, p_STATUS_ID IN NUMBER
, p_SEALED_STATUS IN NUMBER
, p_ATTRIBUTE_CATEGORY IN VARCHAR2
, p_ATTRIBUTE1 IN VARCHAR2
, p_ATTRIBUTE2 IN VARCHAR2
, p_ATTRIBUTE3 IN VARCHAR2
, p_ATTRIBUTE4 IN VARCHAR2
, p_ATTRIBUTE5 IN VARCHAR2
, p_ATTRIBUTE6 IN VARCHAR2
, p_ATTRIBUTE7 IN VARCHAR2
, p_ATTRIBUTE8 IN VARCHAR2
, p_ATTRIBUTE9 IN VARCHAR2
, p_ATTRIBUTE10 IN VARCHAR2
, p_ATTRIBUTE11 IN VARCHAR2
, p_ATTRIBUTE12 IN VARCHAR2
, p_ATTRIBUTE13 IN VARCHAR2
, p_ATTRIBUTE14 IN VARCHAR2
, p_ATTRIBUTE15 IN VARCHAR2
, p_COST_GROUP_ID IN NUMBER
, p_LPN_CONTEXT IN NUMBER
, p_LPN_REUSABILITY IN NUMBER
, p_OUTERMOST_LPN_ID IN NUMBER
, p_outermost_lpn IN VARCHAR2
, p_HOMOGENEOUS_CONTAINER IN NUMBER
, p_SOURCE_TYPE_ID IN NUMBER
, p_SOURCE_HEADER_ID IN NUMBER
, p_SOURCE_LINE_ID IN NUMBER
, p_SOURCE_LINE_DETAIL_ID IN NUMBER
, p_SOURCE_NAME IN VARCHAR2
) IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
inv_rcv_integration_pvt.insert_wlpni
(x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_LPN_ID => p_LPN_ID
, p_license_plate_number => p_license_plate_number
, p_PARENT_LPN_ID => p_PARENT_LPN_ID
, p_PARENT_LICENSE_PLATE_NUMBER => p_PARENT_LICENSE_PLATE_NUMBER
, p_REQUEST_ID => p_REQUEST_ID
, p_INVENTORY_ITEM_ID => p_INVENTORY_ITEM_ID
, p_REVISION => p_REVISION
, p_LOT_NUMBER => p_LOT_NUMBER
, p_SERIAL_NUMBER => p_SERIAL_NUMBER
, p_ORGANIZATION_ID => p_ORGANIZATION_ID
, p_SUBINVENTORY_CODE => p_SUBINVENTORY_CODE
, p_LOCATOR_ID => p_LOCATOR_ID
, p_GROSS_WEIGHT_UOM_CODE => p_GROSS_WEIGHT_UOM_CODE
, p_GROSS_WEIGHT => p_GROSS_WEIGHT
, p_CONTENT_VOLUME_UOM_CODE => p_CONTENT_VOLUME_UOM_CODE
, p_CONTENT_VOLUME => p_CONTENT_VOLUME
, p_TARE_WEIGHT_UOM_CODE => p_TARE_WEIGHT_UOM_CODE
, p_TARE_WEIGHT => p_TARE_WEIGHT
, p_STATUS_ID => p_STATUS_ID
, p_SEALED_STATUS => p_SEALED_STATUS
, p_ATTRIBUTE_CATEGORY => p_ATTRIBUTE_CATEGORY
, p_ATTRIBUTE1 => p_ATTRIBUTE1
, p_ATTRIBUTE2 => p_ATTRIBUTE2
, p_ATTRIBUTE3 => p_ATTRIBUTE3
, p_ATTRIBUTE4 => p_ATTRIBUTE4
, p_ATTRIBUTE5 => p_ATTRIBUTE5
, p_ATTRIBUTE6 => p_ATTRIBUTE6
, p_ATTRIBUTE7 => p_ATTRIBUTE7
, p_ATTRIBUTE8 => p_ATTRIBUTE8
, p_ATTRIBUTE9 => p_ATTRIBUTE9
, p_ATTRIBUTE10 => p_ATTRIBUTE10
, p_ATTRIBUTE11 => p_ATTRIBUTE11
, p_ATTRIBUTE12 => p_ATTRIBUTE12
, p_ATTRIBUTE13 => p_ATTRIBUTE13
, p_ATTRIBUTE14 => p_ATTRIBUTE14
, p_ATTRIBUTE15 => p_ATTRIBUTE15
, p_COST_GROUP_ID => p_COST_GROUP_ID
, p_LPN_CONTEXT => p_LPN_CONTEXT
, p_LPN_REUSABILITY => p_LPN_REUSABILITY
, p_OUTERMOST_LPN_ID => p_OUTERMOST_LPN_ID
--, p_outermost_lpn => p_outermost_lpn
, p_HOMOGENEOUS_CONTAINER => p_HOMOGENEOUS_CONTAINER
, p_SOURCE_TYPE_ID => p_SOURCE_TYPE_ID
, p_SOURCE_HEADER_ID => p_SOURCE_HEADER_ID
, p_SOURCE_LINE_ID => p_SOURCE_LINE_ID
, p_SOURCE_LINE_DETAIL_ID => p_SOURCE_LINE_DETAIL_ID
, p_SOURCE_NAME => p_SOURCE_NAME
, p_LPN_GROUP_ID => p_LPN_GROUP_ID
);
print_debug('INSERT_WLPNI - WLPNI not inserted as both LPN and LPNID are NULL',1);
END insert_wlpni;
PROCEDURE insert_mtli (
p_api_version IN NUMBER
, p_init_msg_lst IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_transaction_interface_id IN OUT NOCOPY NUMBER
, p_lot_number IN VARCHAR2
, p_transaction_quantity IN NUMBER
, p_primary_quantity IN NUMBER
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_expiration_date IN DATE
, p_status_id IN NUMBER
, x_serial_transaction_temp_id OUT NOCOPY NUMBER
, p_product_transaction_id IN OUT NOCOPY NUMBER
, p_product_code IN VARCHAR2
, p_att_exist IN VARCHAR2
, p_update_mln IN VARCHAR2
, p_description IN VARCHAR2
, p_vendor_name IN VARCHAR2
, p_supplier_lot_number IN VARCHAR2
, p_origination_date IN DATE
, p_date_code IN VARCHAR2
, p_grade_code IN VARCHAR2
, p_change_date IN DATE
, p_maturity_date IN DATE
, p_retest_date IN DATE
, p_age IN NUMBER
, p_item_size IN NUMBER
, p_color IN VARCHAR2
, p_volume IN NUMBER
, p_volume_uom IN VARCHAR2
, p_place_of_origin IN VARCHAR2
, p_best_by_date IN DATE
, p_length IN NUMBER
, p_length_uom IN VARCHAR2
, p_recycled_content IN NUMBER
, p_thickness IN NUMBER
, p_thickness_uom IN VARCHAR2
, p_width IN NUMBER
, p_width_uom IN VARCHAR2
, p_curl_wrinkle_fold IN VARCHAR2
, p_vendor_id IN NUMBER
, p_territory_code IN VARCHAR2
, p_lot_attribute_category IN VARCHAR2
, p_c_attribute1 IN VARCHAR2
, p_c_attribute2 IN VARCHAR2
, p_c_attribute3 IN VARCHAR2
, p_c_attribute4 IN VARCHAR2
, p_c_attribute5 IN VARCHAR2
, p_c_attribute6 IN VARCHAR2
, p_c_attribute7 IN VARCHAR2
, p_c_attribute8 IN VARCHAR2
, p_c_attribute9 IN VARCHAR2
, p_c_attribute10 IN VARCHAR2
, p_c_attribute11 IN VARCHAR2
, p_c_attribute12 IN VARCHAR2
, p_c_attribute13 IN VARCHAR2
, p_c_attribute14 IN VARCHAR2
, p_c_attribute15 IN VARCHAR2
, p_c_attribute16 IN VARCHAR2
, p_c_attribute17 IN VARCHAR2
, p_c_attribute18 IN VARCHAR2
, p_c_attribute19 IN VARCHAR2
, p_c_attribute20 IN VARCHAR2
, p_d_attribute1 IN DATE
, p_d_attribute2 IN DATE
, p_d_attribute3 IN DATE
, p_d_attribute4 IN DATE
, p_d_attribute5 IN DATE
, p_d_attribute6 IN DATE
, p_d_attribute7 IN DATE
, p_d_attribute8 IN DATE
, p_d_attribute9 IN DATE
, p_d_attribute10 IN DATE
, p_n_attribute1 IN NUMBER
, p_n_attribute2 IN NUMBER
, p_n_attribute3 IN NUMBER
, p_n_attribute4 IN NUMBER
, p_n_attribute5 IN NUMBER
, p_n_attribute6 IN NUMBER
, p_n_attribute7 IN NUMBER
, p_n_attribute8 IN NUMBER
, p_n_attribute9 IN NUMBER
, p_n_attribute10 IN NUMBER
, p_attribute_category IN VARCHAR2
, p_attribute1 IN VARCHAR2
, p_attribute2 IN VARCHAR2
, p_attribute3 IN VARCHAR2
, p_attribute4 IN VARCHAR2
, p_attribute5 IN VARCHAR2
, p_attribute6 IN VARCHAR2
, p_attribute7 IN VARCHAR2
, p_attribute8 IN VARCHAR2
, p_attribute9 IN VARCHAR2
, p_attribute10 IN VARCHAR2
, p_attribute11 IN VARCHAR2
, p_attribute12 IN VARCHAR2
, p_attribute13 IN VARCHAR2
, p_attribute14 IN VARCHAR2
, p_attribute15 IN VARCHAR2
, p_from_org_id IN NUMBER
, p_secondary_quantity IN NUMBER --OPM Convergence
, p_origination_type IN NUMBER--OPM Convergence
, p_expiration_action_code IN VARCHAR2--OPM Convergence
, p_expiration_action_date IN DATE-- OPM Convergence
, p_hold_date IN DATE--OPM Convergence
, p_parent_lot_number IN VARCHAR2 --OPM Convergence
, p_reasond_id IN NUMBER --OPM convergence
) IS
CURSOR c_mln_attributes( v_lot_number VARCHAR2
, v_inventory_item_id NUMBER
, v_organization_id NUMBER) IS
SELECT lot_number
, expiration_date
, description
, vendor_name
, supplier_lot_number
, grade_code
, origination_date
, date_code
, status_id
, change_date
, age
, retest_date
, maturity_date
, 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
, vendor_id
, territory_code
, 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
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, origination_type --OPM Convergence
, availability_type --OPM Convergence
, expiration_action_code --OPM Convergence
, expiration_action_date -- OPM Convergence
, hold_date --OPM Convergence
FROM mtl_lot_numbers
WHERE lot_number = Ltrim(Rtrim(v_lot_number))
AND inventory_item_id = v_inventory_item_id
AND organization_id = v_organization_id;
l_api_name CONSTANT VARCHAR2(30) := 'insert_mtli';
print_debug('FND_API not compatible inv_rcv_integration_apis.insert_mtli', 4);
SELECT 1
, serial_transaction_temp_id
INTO l_lot_count
, x_serial_transaction_temp_id
FROM mtl_transaction_lots_interface MTLI
WHERE transaction_interface_id = p_transaction_interface_id
AND product_transaction_id = p_product_transaction_id
AND Ltrim(Rtrim(lot_number)) = Ltrim(Rtrim(p_lot_number))
AND ROWNUM = 1
AND EXISTS (
SELECT 1
FROM rcv_transactions_interface RTI
WHERE RTI.INTERFACE_TRANSACTION_ID = MTLI.product_transaction_id
AND RTI.item_id = p_inventory_item_id
AND RTI.to_organization_id = p_organization_id
);
UPDATE mtl_transaction_lots_interface
SET transaction_quantity = transaction_quantity + p_transaction_quantity
, primary_quantity = primary_quantity + p_primary_quantity
, secondary_transaction_quantity = secondary_transaction_quantity + p_secondary_quantity
WHERE transaction_interface_id = p_transaction_interface_id
AND Ltrim(Rtrim(lot_number)) = Ltrim(Rtrim(p_lot_number));
print_debug('Updated ' || SQL%ROWCOUNT || ' lot record(s)', 4);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_interface_id
FROM sys.dual;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_serial_transaction_temp_id
FROM sys.DUAL;
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_product_transaction_id
FROM sys.dual;
/* Logic to insert lot attributes.
* Check the value of the parameter p_att_exist
* If this value is "N" then use the input parameters to insert the attributes
* If this value is "Y", then open the cursor passing the lot, item and org
* Use the values fetched from the cursor to insert the attributes
*/
IF (NVL(p_att_exist, 'Y') = 'Y') THEN
IF p_from_org_id IS NOT NULL THEN
l_att_org_id := p_from_org_id;
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
transaction_interface_id
, source_code
, source_line_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, lot_number
, lot_expiration_date
, transaction_quantity
, primary_quantity
, 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
, product_transaction_id
, product_code
/* , attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15 */
, origination_type --OPM Convergence
, expiration_action_code --OPM Convergence
, expiration_action_date --OPM Convergence
, hold_date --OPM Convergence
, secondary_transaction_quantity --OPM Convergence
, parent_lot_number --OPM Convergence
)
VALUES (
l_transaction_interface_id
, l_source_code
, l_source_line_id
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, Ltrim(Rtrim(p_lot_number))
, l_expiration_date
, p_transaction_quantity
, p_primary_quantity
, l_serial_transaction_temp_id
, 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_status_id
, 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
, l_product_transaction_id
, p_product_code
/* , l_attribute_category
, l_attribute1
, l_attribute2
, l_attribute3
, l_attribute4
, l_attribute5
, l_attribute6
, l_attribute7
, l_attribute8
, l_attribute9
, l_attribute10
, l_attribute11
, l_attribute12
, l_attribute13
, l_attribute14
, l_attribute15 */
, l_origination_type --OPM Convergence
, l_expiration_action_code --OPM Convergence
, l_expiration_action_date --OPM Convergence
, l_hold_date --OPM Convergence
, p_secondary_quantity --OPM Convergence
, l_parent_lot_number --OPM Convergence
);
IF (NVL(p_update_mln, 'N') = 'Y') THEN
BEGIN
SELECT count(1)
INTO l_lot_exists
FROM mtl_lot_numbers
WHERE lot_number = Ltrim(Rtrim(p_lot_number))
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
UPDATE mtl_lot_numbers
SET description = l_description
, vendor_name = l_vendor_name
, supplier_lot_number = l_supplier_lot_number
, origination_date = l_origination_date
, date_code = l_date_code
, grade_code = l_grade_code
, change_date = l_change_date
, maturity_date = l_maturity_date
, retest_date = l_retest_date
, age = l_age
, item_size = l_item_size
, color = l_color
, volume = l_volume
, volume_uom = l_volume_uom
, place_of_origin = l_place_of_origin
, best_by_date = l_best_by_date
, length = l_length
, length_uom = l_length_uom
, recycled_content = l_recycled_content
, thickness = l_thickness
, thickness_uom = l_thickness_uom
, width = l_width
, width_uom = l_width_uom
, curl_wrinkle_fold = l_curl_wrinkle_fold
, vendor_id = l_vendor_id
, territory_code = l_territory_code
, lot_attribute_category = l_lot_attribute_category
, c_attribute1 = l_c_attribute1
, c_attribute2 = l_c_attribute2
, c_attribute3 = l_c_attribute3
, c_attribute4 = l_c_attribute4
, c_attribute5 = l_c_attribute5
, c_attribute6 = l_c_attribute6
, c_attribute7 = l_c_attribute7
, c_attribute8 = l_c_attribute8
, c_attribute9 = l_c_attribute9
, c_attribute10 = l_c_attribute10
, c_attribute11 = l_c_attribute11
, c_attribute12 = l_c_attribute12
, c_attribute13 = l_c_attribute13
, c_attribute14 = l_c_attribute14
, c_attribute15 = l_c_attribute15
, c_attribute16 = l_c_attribute16
, c_attribute17 = l_c_attribute17
, c_attribute18 = l_c_attribute18
, c_attribute19 = l_c_attribute19
, c_attribute20 = l_c_attribute20
, d_attribute1 = l_d_attribute1
, d_attribute2 = l_d_attribute2
, d_attribute3 = l_d_attribute3
, d_attribute4 = l_d_attribute4
, d_attribute5 = l_d_attribute5
, d_attribute6 = l_d_attribute6
, d_attribute7 = l_d_attribute7
, d_attribute8 = l_d_attribute8
, d_attribute9 = l_d_attribute9
, d_attribute10 = l_d_attribute10
, n_attribute1 = l_n_attribute1
, n_attribute2 = l_n_attribute2
, n_attribute3 = l_n_attribute3
, n_attribute4 = l_n_attribute4
, n_attribute5 = l_n_attribute5
, n_attribute6 = l_n_attribute6
, n_attribute7 = l_n_attribute7
, n_attribute8 = l_n_attribute8
, n_attribute9 = l_n_attribute9
, n_attribute10 = l_n_attribute10
, attribute_category = l_attribute_category
, attribute1 = l_attribute1
, attribute2 = l_attribute2
, attribute3 = l_attribute3
, attribute4 = l_attribute4
, attribute5 = l_attribute5
, attribute6 = l_attribute6
, attribute7 = l_attribute7
, attribute8 = l_attribute8
, attribute9 = l_attribute9
, attribute10 = l_attribute10
, attribute11 = l_attribute11
, attribute12 = l_attribute12
, attribute13 = l_attribute13
, attribute14 = l_attribute14
, attribute15 = l_attribute15
, origination_type = l_origination_type --OPM Convergence
, availability_type = l_availability_type --OPM Convergence
, expiration_action_code = l_expiration_action_code--OPM Convergence
, expiration_action_date = l_expiration_action_date --OPM Convergence
, hold_date = l_hold_date --OPM Convergence
, parent_lot_number = l_parent_lot_number --OPM Convergence
WHERE lot_number = Ltrim(Rtrim(p_lot_number))
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
END IF; --END IF p_update_mln = 'Y'
'INV_RCV_INTEGRATION_APIS.INSERT_MTLI',
l_progress,
SQLCODE);
END insert_mtli;
* PROCEDURE: insert_msni
* Description:
* This procedure inserts a record into MTL_SERIAL_NUMBERS_INTERFACE
* Generate transaction_interface_id if the parameter is NULL
* Generate product_transaction_id if the parameter is NULL
* The insert logic is based on the parameter p_att_exist.
* If p_att_exist is "N" Then (attributes are not available in table)
* Read the input parameters (including attributes) into a PL/SQL table
* Insert one record into MSNI with the from and to serial numbers passed
* Else
* Loop through each serial number between the from and to serial number
* Fetch the attributes into one row of the PL/SQL table and
* For each row in the PL/SQL table, insert one MSNI record
* End If
*
* @param p_api_version - Version of the API
* @param p_init_msg_lst - Flag to initialize message list
* @param x_return_status
* Return status indicating Success (S), Error (E), Unexpected Error (U)
* @param x_msg_count
* Number of messages in message list
* @param x_msg_data
* Stacked messages text
* @param p_transaction_interface_id - MTLI.Interface Transaction ID
* @param p_fm_serial_number - From Serial Number
* @param p_to_serial_number - To Serial Number
* @param p_organization_id - Organization ID
* @param p_inventory_item_id - Inventory Item ID
* @param p_status_id - Material Status for the lot
* @param p_product_transaction_id - Product Transaction Id. This parameter
* is stamped with the transaction identifier with
* @param p_product_code - Code of the product creating this record
* @param p_att_exist - Flag to indicate if attributes exist
* @param p_update_msn - Flag to update MSN with attributes
* @param named attributes - Named attributes
* @param C Attributes - Character atributes (1 - 20)
* @param D Attributes - Date atributes (1 - 10)
* @param N Attributes - Number atributes (1 - 10)
* @param p_attribute_cateogry - Attribute Category
* @param Attribute1-15 - Serial Attributes
*
* @ return: NONE
*---------------------------------------------------------------------------*/
PROCEDURE insert_msni(
p_api_version IN NUMBER
, p_init_msg_lst IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_transaction_interface_id IN OUT NOCOPY NUMBER
, p_fm_serial_number IN VARCHAR2
, p_to_serial_number IN VARCHAR2
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_status_id IN NUMBER
, p_product_transaction_id IN OUT NOCOPY NUMBER
, p_product_code IN VARCHAR2
, p_att_exist IN VARCHAR2
, p_update_msn IN VARCHAR2
, p_vendor_serial_number IN VARCHAR2
, p_vendor_lot_number IN VARCHAR2
, p_parent_serial_number IN VARCHAR2
, p_origination_date IN DATE
, p_territory_code IN VARCHAR2
, p_time_since_new IN NUMBER
, p_cycles_since_new IN NUMBER
, p_time_since_overhaul IN NUMBER
, p_cycles_since_overhaul IN NUMBER
, p_time_since_repair IN NUMBER
, p_cycles_since_repair IN NUMBER
, p_time_since_visit IN NUMBER
, p_cycles_since_visit IN NUMBER
, p_time_since_mark IN NUMBER
, p_cycles_since_mark IN NUMBER
, p_number_of_repairs IN NUMBER
, p_serial_attribute_category IN VARCHAR2
, p_c_attribute1 IN VARCHAR2
, p_c_attribute2 IN VARCHAR2
, p_c_attribute3 IN VARCHAR2
, p_c_attribute4 IN VARCHAR2
, p_c_attribute5 IN VARCHAR2
, p_c_attribute6 IN VARCHAR2
, p_c_attribute7 IN VARCHAR2
, p_c_attribute8 IN VARCHAR2
, p_c_attribute9 IN VARCHAR2
, p_c_attribute10 IN VARCHAR2
, p_c_attribute11 IN VARCHAR2
, p_c_attribute12 IN VARCHAR2
, p_c_attribute13 IN VARCHAR2
, p_c_attribute14 IN VARCHAR2
, p_c_attribute15 IN VARCHAR2
, p_c_attribute16 IN VARCHAR2
, p_c_attribute17 IN VARCHAR2
, p_c_attribute18 IN VARCHAR2
, p_c_attribute19 IN VARCHAR2
, p_c_attribute20 IN VARCHAR2
, p_d_attribute1 IN DATE
, p_d_attribute2 IN DATE
, p_d_attribute3 IN DATE
, p_d_attribute4 IN DATE
, p_d_attribute5 IN DATE
, p_d_attribute6 IN DATE
, p_d_attribute7 IN DATE
, p_d_attribute8 IN DATE
, p_d_attribute9 IN DATE
, p_d_attribute10 IN DATE
, p_n_attribute1 IN NUMBER
, p_n_attribute2 IN NUMBER
, p_n_attribute3 IN NUMBER
, p_n_attribute4 IN NUMBER
, p_n_attribute5 IN NUMBER
, p_n_attribute6 IN NUMBER
, p_n_attribute7 IN NUMBER
, p_n_attribute8 IN NUMBER
, p_n_attribute9 IN NUMBER
, p_n_attribute10 IN NUMBER
, p_attribute_category IN VARCHAR2
, p_attribute1 IN VARCHAR2
, p_attribute2 IN VARCHAR2
, p_attribute3 IN VARCHAR2
, p_attribute4 IN VARCHAR2
, p_attribute5 IN VARCHAR2
, p_attribute6 IN VARCHAR2
, p_attribute7 IN VARCHAR2
, p_attribute8 IN VARCHAR2
, p_attribute9 IN VARCHAR2
, p_attribute10 IN VARCHAR2
, p_attribute11 IN VARCHAR2
, p_attribute12 IN VARCHAR2
, p_attribute13 IN VARCHAR2
, p_attribute14 IN VARCHAR2
, p_attribute15 IN VARCHAR2
) IS
CURSOR c_msn_attributes ( v_serial_number VARCHAR2,
v_inventory_item_id NUMBER) IS
SELECT serial_number fm_serial_number
, serial_number to_serial_number
, to_number(NULL) transaction_interface_id
, status_id
, to_number(NULL) product_transaction_id
, to_char(NULL) product_code
, vendor_serial_number
, vendor_lot_number
, parent_serial_number
, origination_date
, 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
, serial_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
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM mtl_serial_numbers
WHERE serial_number = v_serial_number
AND inventory_item_id = v_inventory_item_id;
l_msni_rec_tbl msni_rec_tbl_tp; --Table to hold each MSNI record inserted
l_msni_tbl_count NUMBER; --Count of records to be inserted
l_api_name CONSTANT VARCHAR2(30) := 'insert_mtli';
print_debug('FND_API not compatible','inv_rcv_integration_apis.insert_msni');
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_interface_id
FROM sys.dual;
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_product_transaction_id
FROM sys.dual;
/* To insert the attributes, check the value of p_att_exist.
* If the parameter p_att_exist is 'N' then
* Read the input parameters and store them in the table
* Create one MSNI record with from and to serial number from parameters
* Else
* Loop through each serial number between from and to serial number
* Fetch the attributes into the table for each serial number
* Create on MSNI record with from and to serial number as current serial
*/
IF (NVL(p_att_exist, 'Y') = 'N') THEN
l_msni_rec_tbl(1).fm_serial_number := p_fm_serial_number;
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
transaction_interface_id
, source_code
, source_line_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, fm_serial_number
, to_serial_number
, status_id
, product_transaction_id
, product_code
, vendor_serial_number
, vendor_lot_number
, parent_serial_number
, origination_date
, 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
, serial_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
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
)
VALUES (
l_msni_rec_tbl(i).transaction_interface_id
, l_source_code
, l_source_line_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, l_msni_rec_tbl(i).fm_serial_number
, l_msni_rec_tbl(i).to_serial_number
, l_msni_rec_tbl(i).status_id
, l_msni_rec_tbl(i).product_transaction_id
, l_msni_rec_tbl(i).product_code
, l_msni_rec_tbl(i).vendor_serial_number
, l_msni_rec_tbl(i).vendor_lot_number
, l_msni_rec_tbl(i).parent_serial_number
, l_msni_rec_tbl(i).origination_date
, l_msni_rec_tbl(i).territory_code
, l_msni_rec_tbl(i).time_since_new
, l_msni_rec_tbl(i).cycles_since_new
, l_msni_rec_tbl(i).time_since_overhaul
, l_msni_rec_tbl(i).cycles_since_overhaul
, l_msni_rec_tbl(i).time_since_repair
, l_msni_rec_tbl(i).cycles_since_repair
, l_msni_rec_tbl(i).time_since_visit
, l_msni_rec_tbl(i).cycles_since_visit
, l_msni_rec_tbl(i).time_since_mark
, l_msni_rec_tbl(i).cycles_since_mark
, l_msni_rec_tbl(i).number_of_repairs
, l_msni_rec_tbl(i).serial_attribute_category
, l_msni_rec_tbl(i).c_attribute1
, l_msni_rec_tbl(i).c_attribute2
, l_msni_rec_tbl(i).c_attribute3
, l_msni_rec_tbl(i).c_attribute4
, l_msni_rec_tbl(i).c_attribute5
, l_msni_rec_tbl(i).c_attribute6
, l_msni_rec_tbl(i).c_attribute7
, l_msni_rec_tbl(i).c_attribute8
, l_msni_rec_tbl(i).c_attribute9
, l_msni_rec_tbl(i).c_attribute10
, l_msni_rec_tbl(i).c_attribute11
, l_msni_rec_tbl(i).c_attribute12
, l_msni_rec_tbl(i).c_attribute13
, l_msni_rec_tbl(i).c_attribute14
, l_msni_rec_tbl(i).c_attribute15
, l_msni_rec_tbl(i).c_attribute16
, l_msni_rec_tbl(i).c_attribute17
, l_msni_rec_tbl(i).c_attribute18
, l_msni_rec_tbl(i).c_attribute19
, l_msni_rec_tbl(i).c_attribute20
, l_msni_rec_tbl(i).d_attribute1
, l_msni_rec_tbl(i).d_attribute2
, l_msni_rec_tbl(i).d_attribute3
, l_msni_rec_tbl(i).d_attribute4
, l_msni_rec_tbl(i).d_attribute5
, l_msni_rec_tbl(i).d_attribute6
, l_msni_rec_tbl(i).d_attribute7
, l_msni_rec_tbl(i).d_attribute8
, l_msni_rec_tbl(i).d_attribute9
, l_msni_rec_tbl(i).d_attribute10
, l_msni_rec_tbl(i).n_attribute1
, l_msni_rec_tbl(i).n_attribute2
, l_msni_rec_tbl(i).n_attribute3
, l_msni_rec_tbl(i).n_attribute4
, l_msni_rec_tbl(i).n_attribute5
, l_msni_rec_tbl(i).n_attribute6
, l_msni_rec_tbl(i).n_attribute7
, l_msni_rec_tbl(i).n_attribute8
, l_msni_rec_tbl(i).n_attribute9
, l_msni_rec_tbl(i).n_attribute10
, l_msni_rec_tbl(i).attribute_category
, l_msni_rec_tbl(i).attribute1
, l_msni_rec_tbl(i).attribute2
, l_msni_rec_tbl(i).attribute3
, l_msni_rec_tbl(i).attribute4
, l_msni_rec_tbl(i).attribute5
, l_msni_rec_tbl(i).attribute6
, l_msni_rec_tbl(i).attribute7
, l_msni_rec_tbl(i).attribute8
, l_msni_rec_tbl(i).attribute9
, l_msni_rec_tbl(i).attribute10
, l_msni_rec_tbl(i).attribute11
, l_msni_rec_tbl(i).attribute12
, l_msni_rec_tbl(i).attribute13
, l_msni_rec_tbl(i).attribute14
, l_msni_rec_tbl(i).attribute15
);
IF (NVL(p_update_msn, 'N') = 'Y') THEN
UPDATE mtl_serial_numbers
SET vendor_serial_number = l_msni_rec_tbl(i).vendor_serial_number
, vendor_lot_number = l_msni_rec_tbl(i).vendor_lot_number
, parent_serial_number = l_msni_rec_tbl(i).parent_serial_number
, origination_date = l_msni_rec_tbl(i).origination_date
, territory_code = l_msni_rec_tbl(i).territory_code
, time_since_new = l_msni_rec_tbl(i).time_since_new
, cycles_since_new = l_msni_rec_tbl(i).cycles_since_new
, time_since_overhaul = l_msni_rec_tbl(i).time_since_overhaul
, cycles_since_overhaul = l_msni_rec_tbl(i).cycles_since_overhaul
, time_since_repair = l_msni_rec_tbl(i).time_since_repair
, cycles_since_repair = l_msni_rec_tbl(i).cycles_since_repair
, time_since_visit = l_msni_rec_tbl(i).time_since_visit
, cycles_since_visit = l_msni_rec_tbl(i).cycles_since_visit
, time_since_mark = l_msni_rec_tbl(i).time_since_mark
, cycles_since_mark = l_msni_rec_tbl(i).cycles_since_mark
, number_of_repairs = l_msni_rec_tbl(i).number_of_repairs
, serial_attribute_category = l_msni_rec_tbl(i).serial_attribute_category
, c_attribute1 = l_msni_rec_tbl(i).c_attribute1
, c_attribute2 = l_msni_rec_tbl(i).c_attribute2
, c_attribute3 = l_msni_rec_tbl(i).c_attribute3
, c_attribute4 = l_msni_rec_tbl(i).c_attribute4
, c_attribute5 = l_msni_rec_tbl(i).c_attribute5
, c_attribute6 = l_msni_rec_tbl(i).c_attribute6
, c_attribute7 = l_msni_rec_tbl(i).c_attribute7
, c_attribute8 = l_msni_rec_tbl(i).c_attribute8
, c_attribute9 = l_msni_rec_tbl(i).c_attribute9
, c_attribute10 = l_msni_rec_tbl(i).c_attribute10
, c_attribute11 = l_msni_rec_tbl(i).c_attribute11
, c_attribute12 = l_msni_rec_tbl(i).c_attribute12
, c_attribute13 = l_msni_rec_tbl(i).c_attribute13
, c_attribute14 = l_msni_rec_tbl(i).c_attribute14
, c_attribute15 = l_msni_rec_tbl(i).c_attribute15
, c_attribute16 = l_msni_rec_tbl(i).c_attribute16
, c_attribute17 = l_msni_rec_tbl(i).c_attribute17
, c_attribute18 = l_msni_rec_tbl(i).c_attribute18
, c_attribute19 = l_msni_rec_tbl(i).c_attribute19
, c_attribute20 = l_msni_rec_tbl(i).c_attribute20
, d_attribute1 = l_msni_rec_tbl(i).d_attribute1
, d_attribute2 = l_msni_rec_tbl(i).d_attribute2
, d_attribute3 = l_msni_rec_tbl(i).d_attribute3
, d_attribute4 = l_msni_rec_tbl(i).d_attribute4
, d_attribute5 = l_msni_rec_tbl(i).d_attribute5
, d_attribute6 = l_msni_rec_tbl(i).d_attribute6
, d_attribute7 = l_msni_rec_tbl(i).d_attribute7
, d_attribute8 = l_msni_rec_tbl(i).d_attribute8
, d_attribute9 = l_msni_rec_tbl(i).d_attribute9
, d_attribute10 = l_msni_rec_tbl(i).d_attribute10
, n_attribute1 = l_msni_rec_tbl(i).n_attribute1
, n_attribute2 = l_msni_rec_tbl(i).n_attribute2
, n_attribute3 = l_msni_rec_tbl(i).n_attribute3
, n_attribute4 = l_msni_rec_tbl(i).n_attribute4
, n_attribute5 = l_msni_rec_tbl(i).n_attribute5
, n_attribute6 = l_msni_rec_tbl(i).n_attribute6
, n_attribute7 = l_msni_rec_tbl(i).n_attribute7
, n_attribute8 = l_msni_rec_tbl(i).n_attribute8
, n_attribute9 = l_msni_rec_tbl(i).n_attribute9
, n_attribute10 = l_msni_rec_tbl(i).n_attribute10
, attribute_category = l_msni_rec_tbl(i).attribute_category
, attribute1 = l_msni_rec_tbl(i).attribute1
, attribute2 = l_msni_rec_tbl(i).attribute2
, attribute3 = l_msni_rec_tbl(i).attribute3
, attribute4 = l_msni_rec_tbl(i).attribute4
, attribute5 = l_msni_rec_tbl(i).attribute5
, attribute6 = l_msni_rec_tbl(i).attribute6
, attribute7 = l_msni_rec_tbl(i).attribute7
, attribute8 = l_msni_rec_tbl(i).attribute8
, attribute9 = l_msni_rec_tbl(i).attribute9
, attribute10 = l_msni_rec_tbl(i).attribute10
, attribute11 = l_msni_rec_tbl(i).attribute11
, attribute12 = l_msni_rec_tbl(i).attribute12
, attribute13 = l_msni_rec_tbl(i).attribute13
, attribute14 = l_msni_rec_tbl(i).attribute14
, attribute15 = l_msni_rec_tbl(i).attribute15
WHERE inventory_item_id = p_inventory_item_id
AND serial_number between
l_msni_rec_tbl(i).fm_serial_number and
l_msni_rec_tbl(i).to_serial_number
AND LENGTH(serial_number) = LENGTH(p_fm_serial_number);
END IF; --END If p_update_msn = 'Y'
inv_mobile_helper_functions.sql_error('INV_RCV_INTEGRATION_APIS.INSERT_MSNI', l_progress, SQLCODE);
END insert_msni;
SELECT 'Y'
INTO l_lot_exists
FROM mtl_lot_numbers
WHERE lot_number = LTRIM(RTRIM(p_lot_number))
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT shelf_life_days
, shelf_life_code
INTO l_shelf_life_days
, l_shelf_life_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id;
SELECT SYSDATE + l_shelf_life_days
INTO l_expiration_date
FROM DUAL;
SELECT 'Y'
INTO l_serial_exists
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = l_cur_serial_number
AND current_organization_id = p_org_id
AND current_status IN (1,6);
select 1, lpn_id
into l_exists, l_lpn_id
from wms_license_plate_numbers wlpn
where wlpn.organization_id = nvl(p_org_id, wlpn.organization_id )
and wlpn.license_plate_number = p_lpn
and wlpn.lpn_id = nvl(p_lpn_id, wlpn.lpn_id)
and ( (p_parent_lpn_id is null ) or ( wlpn.parent_lpn_id = p_parent_lpn_id and p_parent_lpn_id is not null ))
and rownum = 1;
SELECT DISTINCT(to_organization_id)
INTO l_org_id
FROM rcv_transactions_interface
WHERE lpn_group_id = p_lpn_group_id;
SELECT 1
INTO l_exist
FROM dual
WHERE exists (SELECT 1
FROM rcv_transactions_interface
WHERE group_id = p_group_id
AND item_id is null
AND item_description is null
AND (lpn_id is not null
OR license_plate_number is not null
OR transfer_lpn_id is not null
OR transfer_license_plate_number is not null)
);
* in which case we use a select statement directly
*/
PROCEDURE validate_sub_loc(
p_api_version IN NUMBER
, p_init_msg_lst IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_group_id IN NUMBER
, p_request_id IN NUMBER
, p_rti_id IN NUMBER
, p_validation_mode IN NUMBER
) IS
l_exist NUMBER;
SELECT subinventory
, locator_id
, transaction_type
, to_organization_id
, NVL(auto_transact_code, '@@@') auto_transact_code
, transfer_lpn_id
, transfer_license_plate_number
, location_id
FROM rcv_transactions_interface rti
WHERE GROUP_ID = p_group_id
AND interface_transaction_id = p_rti_id;
SELECT subinventory
, locator_id
, transaction_type
, to_organization_id
, NVL(auto_transact_code, '@@@') auto_transact_code
, transfer_lpn_id
, transfer_license_plate_number
, location_id
FROM rcv_transactions_interface rti
WHERE GROUP_ID = p_group_id;
SELECT subinventory
, locator_id
, transaction_type
, to_organization_id
, NVL(auto_transact_code, '@@@') auto_transact_code
, transfer_lpn_id
, transfer_license_plate_number
, location_id
FROM rcv_transactions_interface rti
WHERE interface_transaction_id = p_rti_id;
SELECT NVL(subinventory_type, 1), Nvl(lpn_controlled_flag, 2)
INTO l_sub_type, l_lpn_cont_flag
FROM mtl_secondary_inventories msi
WHERE secondary_inventory_name = l_sub
AND organization_id = l_org_id;
SELECT NVL(l_loc_type, -1)
INTO l_loc_type
FROM mtl_item_locations mil
WHERE inventory_location_id = l_locator_id
AND organization_id = l_org_id;
SELECT 1
INTO l_exist
FROM dual
WHERE exists (SELECT '1'
FROM rcv_supply rs, wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = l_lpn_id
AND wlpn.lpn_id = rs.lpn_id
AND rs.location_id <> l_location_id);
SELECT 1
INTO l_exist
FROM dual
WHERE exists (SELECT '1'
FROM rcv_supply rs, wms_license_plate_numbers wlpn
WHERE wlpn.license_plate_number = l_lpn_num
AND wlpn.lpn_id = rs.lpn_id
AND rs.location_id <> l_location_id);
SELECT to_organization_id
INTO l_org_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_rti_id;
SELECT DISTINCT DECODE (rt.transaction_type,'ACCEPT','INSPECT',
'REJECT','INSPECT',
'DELIVER', DECODE (mp.wms_enabled_flag,'Y','PUTAWAY','DELIVER'),
transaction_type) transaction_type
FROM rcv_transactions rt,
mtl_parameters mp
WHERE rt.group_id = p_group_id
AND rt.organization_id = mp.organization_id;
SELECT DISTINCT rt.transfer_lpn_id
, rt.organization_id
FROM rcv_transactions rt
, mtl_parameters mp
, mtl_system_items_kfv msi
, rcv_supply rs
WHERE mp.wms_enabled_flag = 'Y'
AND mp.organization_id = rt.organization_id
AND rt.transaction_type IN ('RECEIVE','ACCEPT','REJECT')
AND rs.rcv_transaction_id = rt.transaction_id
AND rs.supply_type_code = 'RECEIVING'
AND rs.item_id = msi.inventory_item_id
AND msi.organization_id = rt.organization_id
AND rt.group_id = p_group_id
AND rt.lpn_group_id = p_lpn_group_id
AND ((msi.lot_control_code = 2
AND exists (SELECT 1
FROM rcv_lots_supply rsl
WHERE rsl.transaction_id = rs.rcv_transaction_id)
)
OR
(msi.lot_control_code = 1))
AND ((msi.serial_number_control_code IN (2,5)
AND exists (SELECT 1
FROM rcv_serials_supply rss
WHERE rss.transaction_id = rs.rcv_transaction_id)
)
OR
(msi.serial_number_control_code IN (1, 6)))
ORDER BY rt.transfer_lpn_id;
SELECT 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
,Ltrim(Rtrim(lot_number)) 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
,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
,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
,org_cost_group_id
,cost_type_id
,transaction_status
,standard_operation_id
,task_priority
,wms_task_type
,parent_line_id
,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
,move_order_header_id
,serial_allocated_flag
FROM mtl_material_transactions_temp
WHERE
-- For call from putaway: p_operation_type will be null
-- For call from item load putaway, type will be 'LOAD' or 'DROP'
(((p_operation_type IS NULL OR p_operation_type IN ('LOAD','DROP'))
AND p_remaining_mmtt_splt = 'N'
AND Nvl(transaction_header_id, -2) <> Nvl(p_txn_header_id, -1))
OR
-- For call from putaway when it is splitting the remaining header id
(p_operation_type IS NULL
AND p_remaining_mmtt_splt = 'Y'
AND transaction_header_id = p_txn_header_id)
OR
-- For call from deliver
(p_operation_type = 'DELIVER'
AND transaction_temp_id = p_txn_temp_id))
AND move_order_line_id = x_orig_mol_rec.line_id
AND ((transaction_source_type_id = 1 AND
transaction_action_id = 27) OR
( transaction_source_type_id = 7 AND
transaction_action_id = 12) OR
( transaction_source_type_id = 12 AND
transaction_action_id = 27) OR
( transaction_source_type_id = 4 AND
transaction_action_id = 2) OR
( transaction_source_type_id = 5 AND
transaction_action_id IN (27,31) ) OR
( transaction_source_type_id = 4 AND
transaction_action_id = 27)OR
( transaction_source_type_id = 13 AND
transaction_action_id = 12) ) --bugfix 5263798
order by transaction_temp_id asc; --bugfix 6189438
-- Update original MOL
x_orig_mol_rec.primary_quantity := x_orig_mol_rec.primary_quantity - l_prim_qty_to_splt;
-- Update new MOL
x_new_mol_rec.primary_quantity := x_new_mol_rec.primary_quantity + l_prim_qty_to_splt;
--create a new MMTT record, set the new quantity, insert into MMTT table
l_new_mmtt_rec := l_orig_mmtt_rec;
INSERT INTO mtl_material_transactions_temp
( 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
,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
,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
,org_cost_group_id
,cost_type_id
,transaction_status
,standard_operation_id
,task_priority
,wms_task_type
,parent_line_id
,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
,move_order_header_id
,serial_allocated_flag )
VALUES
( mtl_material_transactions_s.NEXTVAL --use different header
,mtl_material_transactions_s.NEXTVAL
,l_new_mmtt_rec.SOURCE_CODE
,l_new_mmtt_rec.SOURCE_LINE_ID
,l_new_mmtt_rec.TRANSACTION_MODE
,l_new_mmtt_rec.LOCK_FLAG
,l_sysdate
,l_new_mmtt_rec.LAST_UPDATED_BY
,l_sysdate
,l_new_mmtt_rec.CREATED_BY
,l_new_mmtt_rec.LAST_UPDATE_LOGIN
,l_new_mmtt_rec.REQUEST_ID
,l_new_mmtt_rec.PROGRAM_APPLICATION_ID
,l_new_mmtt_rec.PROGRAM_ID
,l_new_mmtt_rec.PROGRAM_UPDATE_DATE
,l_new_mmtt_rec.INVENTORY_ITEM_ID
,l_new_mmtt_rec.REVISION
,l_new_mmtt_rec.ORGANIZATION_ID
,l_new_mmtt_rec.SUBINVENTORY_CODE
,l_new_mmtt_rec.LOCATOR_ID
,l_new_mmtt_rec.TRANSACTION_QUANTITY
,l_new_mmtt_rec.PRIMARY_QUANTITY
,l_new_mmtt_rec.TRANSACTION_UOM
,l_new_mmtt_rec.TRANSACTION_COST
,l_new_mmtt_rec.TRANSACTION_TYPE_ID
,l_new_mmtt_rec.TRANSACTION_ACTION_ID
,l_new_mmtt_rec.TRANSACTION_SOURCE_TYPE_ID
,l_new_mmtt_rec.TRANSACTION_SOURCE_ID
,l_new_mmtt_rec.TRANSACTION_SOURCE_NAME
,l_new_mmtt_rec.TRANSACTION_DATE
,l_new_mmtt_rec.ACCT_PERIOD_ID
,l_new_mmtt_rec.DISTRIBUTION_ACCOUNT_ID
,l_new_mmtt_rec.TRANSACTION_REFERENCE
,l_new_mmtt_rec.REQUISITION_LINE_ID
,l_new_mmtt_rec.REQUISITION_DISTRIBUTION_ID
,l_new_mmtt_rec.REASON_ID
,Ltrim(Rtrim(l_new_mmtt_rec.lot_number))
,l_new_mmtt_rec.LOT_EXPIRATION_DATE
,l_new_mmtt_rec.SERIAL_NUMBER
,l_new_mmtt_rec.RECEIVING_DOCUMENT
,l_new_mmtt_rec.DEMAND_ID
,l_new_mmtt_rec.RCV_TRANSACTION_ID
,l_new_mmtt_rec.MOVE_TRANSACTION_ID
,l_new_mmtt_rec.COMPLETION_TRANSACTION_ID
,l_new_mmtt_rec.WIP_ENTITY_TYPE
,l_new_mmtt_rec.SCHEDULE_ID
,l_new_mmtt_rec.REPETITIVE_LINE_ID
,l_new_mmtt_rec.employee_code
,l_new_mmtt_rec.PRIMARY_SWITCH
,l_new_mmtt_rec.SCHEDULE_UPDATE_CODE
,l_new_mmtt_rec.SETUP_TEARDOWN_CODE
,l_new_mmtt_rec.ITEM_ORDERING
,l_new_mmtt_rec.NEGATIVE_REQ_FLAG
,l_new_mmtt_rec.OPERATION_SEQ_NUM
,l_new_mmtt_rec.PICKING_LINE_ID
,l_new_mmtt_rec.TRX_SOURCE_LINE_ID
,l_new_mmtt_rec.TRX_SOURCE_DELIVERY_ID
,l_new_mmtt_rec.PHYSICAL_ADJUSTMENT_ID
,l_new_mmtt_rec.CYCLE_COUNT_ID
,l_new_mmtt_rec.RMA_LINE_ID
,l_new_mmtt_rec.CUSTOMER_SHIP_ID
,l_new_mmtt_rec.CURRENCY_CODE
,l_new_mmtt_rec.CURRENCY_CONVERSION_RATE
,l_new_mmtt_rec.CURRENCY_CONVERSION_TYPE
,l_new_mmtt_rec.CURRENCY_CONVERSION_DATE
,l_new_mmtt_rec.USSGL_TRANSACTION_CODE
,l_new_mmtt_rec.VENDOR_LOT_NUMBER
,l_new_mmtt_rec.ENCUMBRANCE_ACCOUNT
,l_new_mmtt_rec.ENCUMBRANCE_AMOUNT
,l_new_mmtt_rec.SHIP_TO_LOCATION
,l_new_mmtt_rec.SHIPMENT_NUMBER
,l_new_mmtt_rec.TRANSFER_COST
,l_new_mmtt_rec.TRANSPORTATION_COST
,l_new_mmtt_rec.TRANSPORTATION_ACCOUNT
,l_new_mmtt_rec.FREIGHT_CODE
,l_new_mmtt_rec.CONTAINERS
,l_new_mmtt_rec.WAYBILL_AIRBILL
,l_new_mmtt_rec.EXPECTED_ARRIVAL_DATE
,l_new_mmtt_rec.TRANSFER_SUBINVENTORY
,l_new_mmtt_rec.TRANSFER_ORGANIZATION
,l_new_mmtt_rec.TRANSFER_TO_LOCATION
,l_new_mmtt_rec.NEW_AVERAGE_COST
,l_new_mmtt_rec.VALUE_CHANGE
,l_new_mmtt_rec.PERCENTAGE_CHANGE
,l_new_mmtt_rec.MATERIAL_ALLOCATION_TEMP_ID
,l_new_mmtt_rec.DEMAND_SOURCE_HEADER_ID
,l_new_mmtt_rec.DEMAND_SOURCE_LINE
,l_new_mmtt_rec.DEMAND_SOURCE_DELIVERY
,l_new_mmtt_rec.ITEM_SEGMENTS
,l_new_mmtt_rec.ITEM_DESCRIPTION
,l_new_mmtt_rec.ITEM_TRX_ENABLED_FLAG
,l_new_mmtt_rec.ITEM_LOCATION_CONTROL_CODE
,l_new_mmtt_rec.ITEM_RESTRICT_SUBINV_CODE
,l_new_mmtt_rec.ITEM_RESTRICT_LOCATORS_CODE
,l_new_mmtt_rec.ITEM_REVISION_QTY_CONTROL_CODE
,l_new_mmtt_rec.ITEM_PRIMARY_UOM_CODE
,l_new_mmtt_rec.ITEM_UOM_CLASS
,l_new_mmtt_rec.ITEM_SHELF_LIFE_CODE
,l_new_mmtt_rec.ITEM_SHELF_LIFE_DAYS
,l_new_mmtt_rec.ITEM_LOT_CONTROL_CODE
,l_new_mmtt_rec.ITEM_SERIAL_CONTROL_CODE
,l_new_mmtt_rec.ITEM_INVENTORY_ASSET_FLAG
,l_new_mmtt_rec.ALLOWED_UNITS_LOOKUP_CODE
,l_new_mmtt_rec.DEPARTMENT_ID
,l_new_mmtt_rec.DEPARTMENT_CODE
,l_new_mmtt_rec.WIP_SUPPLY_TYPE
,l_new_mmtt_rec.SUPPLY_SUBINVENTORY
,l_new_mmtt_rec.SUPPLY_LOCATOR_ID
,l_new_mmtt_rec.VALID_SUBINVENTORY_FLAG
,l_new_mmtt_rec.VALID_LOCATOR_FLAG
,l_new_mmtt_rec.LOCATOR_SEGMENTS
,l_new_mmtt_rec.CURRENT_LOCATOR_CONTROL_CODE
,l_new_mmtt_rec.NUMBER_OF_LOTS_ENTERED
,l_new_mmtt_rec.WIP_COMMIT_FLAG
,l_new_mmtt_rec.NEXT_LOT_NUMBER
,l_new_mmtt_rec.LOT_ALPHA_PREFIX
,l_new_mmtt_rec.NEXT_SERIAL_NUMBER
,l_new_mmtt_rec.SERIAL_ALPHA_PREFIX
,l_new_mmtt_rec.SHIPPABLE_FLAG
,l_new_mmtt_rec.POSTING_FLAG
,l_new_mmtt_rec.REQUIRED_FLAG
,l_new_mmtt_rec.PROCESS_FLAG
,l_new_mmtt_rec.ERROR_CODE
,l_new_mmtt_rec.ERROR_EXPLANATION
,l_new_mmtt_rec.ATTRIBUTE_CATEGORY
,l_new_mmtt_rec.ATTRIBUTE1
,l_new_mmtt_rec.ATTRIBUTE2
,l_new_mmtt_rec.ATTRIBUTE3
,l_new_mmtt_rec.ATTRIBUTE4
,l_new_mmtt_rec.ATTRIBUTE5
,l_new_mmtt_rec.ATTRIBUTE6
,l_new_mmtt_rec.ATTRIBUTE7
,l_new_mmtt_rec.ATTRIBUTE8
,l_new_mmtt_rec.ATTRIBUTE9
,l_new_mmtt_rec.ATTRIBUTE10
,l_new_mmtt_rec.ATTRIBUTE11
,l_new_mmtt_rec.ATTRIBUTE12
,l_new_mmtt_rec.ATTRIBUTE13
,l_new_mmtt_rec.ATTRIBUTE14
,l_new_mmtt_rec.ATTRIBUTE15
,l_new_mmtt_rec.MOVEMENT_ID
,l_new_mmtt_rec.RESERVATION_QUANTITY
,l_new_mmtt_rec.SHIPPED_QUANTITY
,l_new_mmtt_rec.TRANSACTION_LINE_NUMBER
,l_new_mmtt_rec.TASK_ID
,l_new_mmtt_rec.TO_TASK_ID
,l_new_mmtt_rec.SOURCE_TASK_ID
,l_new_mmtt_rec.PROJECT_ID
,l_new_mmtt_rec.SOURCE_PROJECT_ID
,l_new_mmtt_rec.PA_EXPENDITURE_ORG_ID
,l_new_mmtt_rec.TO_PROJECT_ID
,l_new_mmtt_rec.EXPENDITURE_TYPE
,l_new_mmtt_rec.FINAL_COMPLETION_FLAG
,l_new_mmtt_rec.TRANSFER_PERCENTAGE
,l_new_mmtt_rec.TRANSACTION_SEQUENCE_ID
,l_new_mmtt_rec.MATERIAL_ACCOUNT
,l_new_mmtt_rec.MATERIAL_OVERHEAD_ACCOUNT
,l_new_mmtt_rec.RESOURCE_ACCOUNT
,l_new_mmtt_rec.OUTSIDE_PROCESSING_ACCOUNT
,l_new_mmtt_rec.OVERHEAD_ACCOUNT
,l_new_mmtt_rec.FLOW_SCHEDULE
,l_new_mmtt_rec.COST_GROUP_ID
,l_new_mmtt_rec.DEMAND_CLASS
,l_new_mmtt_rec.QA_COLLECTION_ID
,l_new_mmtt_rec.KANBAN_CARD_ID
,l_new_mmtt_rec.OVERCOMPLETION_TRANSACTION_QTY
,l_new_mmtt_rec.OVERCOMPLETION_PRIMARY_QTY
,l_new_mmtt_rec.OVERCOMPLETION_TRANSACTION_ID
,l_new_mmtt_rec.END_ITEM_UNIT_NUMBER
,l_new_mmtt_rec.SCHEDULED_PAYBACK_DATE
,l_new_mmtt_rec.LINE_TYPE_CODE
,l_new_mmtt_rec.PARENT_TRANSACTION_TEMP_ID
,l_new_mmtt_rec.PUT_AWAY_STRATEGY_ID
,l_new_mmtt_rec.PUT_AWAY_RULE_ID
,l_new_mmtt_rec.PICK_STRATEGY_ID
,l_new_mmtt_rec.PICK_RULE_ID
,l_new_mmtt_rec.MOVE_ORDER_LINE_ID
,l_new_mmtt_rec.TASK_GROUP_ID
,l_new_mmtt_rec.PICK_SLIP_NUMBER
,l_new_mmtt_rec.RESERVATION_ID
,l_new_mmtt_rec.COMMON_BOM_SEQ_ID
,l_new_mmtt_rec.COMMON_ROUTING_SEQ_ID
,l_new_mmtt_rec.ORG_COST_GROUP_ID
,l_new_mmtt_rec.COST_TYPE_ID
,l_new_mmtt_rec.TRANSACTION_STATUS
,l_new_mmtt_rec.STANDARD_OPERATION_ID
,l_new_mmtt_rec.TASK_PRIORITY
,l_new_mmtt_rec.WMS_TASK_TYPE
,l_new_mmtt_rec.PARENT_LINE_ID
,l_new_mmtt_rec.TRANSFER_COST_GROUP_ID
,l_new_mmtt_rec.LPN_ID
,l_new_mmtt_rec.TRANSFER_LPN_ID
,l_new_mmtt_rec.WMS_TASK_STATUS
,l_new_mmtt_rec.CONTENT_LPN_ID
,l_new_mmtt_rec.CONTAINER_ITEM_ID
,l_new_mmtt_rec.CARTONIZATION_ID
,l_new_mmtt_rec.PICK_SLIP_DATE
,l_new_mmtt_rec.REBUILD_ITEM_ID
,l_new_mmtt_rec.REBUILD_SERIAL_NUMBER
,l_new_mmtt_rec.REBUILD_ACTIVITY_ID
,l_new_mmtt_rec.REBUILD_JOB_NAME
,l_new_mmtt_rec.ORGANIZATION_TYPE
,l_new_mmtt_rec.TRANSFER_ORGANIZATION_TYPE
,l_new_mmtt_rec.OWNING_ORGANIZATION_ID
,l_new_mmtt_rec.OWNING_TP_TYPE
,l_new_mmtt_rec.XFR_OWNING_ORGANIZATION_ID
,l_new_mmtt_rec.TRANSFER_OWNING_TP_TYPE
,l_new_mmtt_rec.PLANNING_ORGANIZATION_ID
,l_new_mmtt_rec.PLANNING_TP_TYPE
,l_new_mmtt_rec.XFR_PLANNING_ORGANIZATION_ID
,l_new_mmtt_rec.TRANSFER_PLANNING_TP_TYPE
,l_new_mmtt_rec.SECONDARY_UOM_CODE
,l_new_mmtt_rec.SECONDARY_TRANSACTION_QUANTITY
,l_new_mmtt_rec.ALLOCATED_LPN_ID
,l_new_mmtt_rec.SCHEDULE_NUMBER
,l_new_mmtt_rec.SCHEDULED_FLAG
,l_new_mmtt_rec.CLASS_CODE
,l_new_mmtt_rec.SCHEDULE_GROUP
,l_new_mmtt_rec.BUILD_SEQUENCE
,l_new_mmtt_rec.BOM_REVISION
,l_new_mmtt_rec.ROUTING_REVISION
,l_new_mmtt_rec.BOM_REVISION_DATE
,l_new_mmtt_rec.ROUTING_REVISION_DATE
,l_new_mmtt_rec.ALTERNATE_BOM_DESIGNATOR
,l_new_mmtt_rec.ALTERNATE_ROUTING_DESIGNATOR
,l_new_mmtt_rec.TRANSACTION_BATCH_ID
,l_new_mmtt_rec.TRANSACTION_BATCH_SEQ
,l_new_mmtt_rec.operation_plan_id
,l_new_mmtt_rec.move_order_header_id
,l_new_mmtt_rec.serial_allocated_flag)
returning transaction_temp_id INTO l_new_mmtt_id;
print_debug('SPLIT_MMTT: Error inserting mmtt', 4);
print_debug('SPLIT_MMTT: MMTT successfully inserted: ' || l_new_mmtt_id,4);
/* Update original mmtt */
l_orig_mmtt_rec.primary_quantity := l_orig_mmtt_rec.primary_quantity - l_prim_qty_to_splt;
UPDATE
mtl_material_transactions_temp
SET
primary_quantity = l_orig_mmtt_rec.primary_quantity
,transaction_quantity = l_orig_mmtt_rec.transaction_quantity
, secondary_transaction_quantity = decode (l_orig_mmtt_rec.secondary_uom_code, NULL, NULL, l_orig_mmtt_rec.secondary_transaction_quantity)
WHERE
transaction_temp_id = l_orig_mmtt_rec.transaction_temp_id;
-- Update putaway_temp_table if necessary
IF (p_updt_putaway_temp_tbl = fnd_api.g_true) THEN
BEGIN
UPDATE
wms_putaway_group_tasks_gtmp
SET
primary_quantity = l_orig_mmtt_rec.primary_quantity
,transaction_quantity = Decode(primary_quantity,
transaction_quantity,
l_orig_mmtt_rec.transaction_quantity,
inv_rcv_cache.convert_qty
(x_orig_mol_rec.inventory_item_id
,l_orig_mmtt_rec.primary_quantity
,p_prim_uom_code
,l_orig_mmtt_rec.transaction_uom
,NULL
))
,secondary_quantity = decode (l_orig_mmtt_rec.secondary_uom_code, NULL, NULL, l_orig_mmtt_rec.secondary_transaction_quantity)
WHERE
row_type = 'All Task'
AND move_order_line_id = x_orig_mol_rec.line_id
AND transaction_temp_id = l_orig_mmtt_rec.transaction_temp_id;
-- only update qty in the first loop
-- Update new MOL with the increased quantity
x_new_mol_rec.primary_quantity := x_new_mol_rec.primary_quantity + l_prim_qty_to_splt;
-- Update old MOL with the reduced quantity
x_orig_mol_rec.primary_quantity := x_orig_mol_rec.primary_quantity - l_prim_qty_to_splt;
/* If the MMTT has the exact amount to be split, then simply update the original mmtt mol pointer */
IF (l_debug = 1) THEN
print_debug('SPLIT_MMTT: splitting MMTT:'||
l_orig_mmtt_rec.transaction_temp_id||
' with QTY:' ||
l_orig_mmtt_rec.primary_quantity||
' into QTY:' || l_prim_qty_to_splt ||
' with SEC QTY:' ||
l_orig_mmtt_rec.secondary_transaction_quantity||
' into SEC QTY:' || l_sec_qty_to_splt ||
'... Exact amount',4);
UPDATE mtl_material_transactions_temp
SET move_order_line_id = x_new_mol_rec.line_id
WHERE transaction_temp_id = l_orig_mmtt_rec.transaction_temp_id;
-- Update MOL of putaway_temp_table if necessary
IF (p_updt_putaway_temp_tbl = fnd_api.g_true) THEN
BEGIN
UPDATE
wms_putaway_group_tasks_gtmp
SET move_order_line_id = x_new_mol_rec.line_id
WHERE transaction_temp_id = l_orig_mmtt_rec.transaction_temp_id
AND move_order_line_id = x_orig_mol_rec.line_id
AND row_type = 'All Task';
-- Don't update qty in second loop, since it is updated during
-- the first one
-- Update new MOL with the increased quantity
x_new_mol_rec.primary_quantity := x_new_mol_rec.primary_quantity + l_prim_qty_to_splt;
-- Update original MOL with the reduced quantity
x_orig_mol_rec.primary_quantity := x_orig_mol_rec.primary_quantity - l_prim_qty_to_splt;
' Successfully update MMTT to new MOL', 4);
UPDATE mtl_material_transactions_temp
SET move_order_line_id = x_new_mol_rec.line_id
WHERE transaction_temp_id =
l_orig_mmtt_rec.transaction_temp_id;
-- Delete from putawau_temp_table if necessary
IF (p_updt_putaway_temp_tbl = fnd_api.g_true) THEN
BEGIN
UPDATE
wms_putaway_group_tasks_gtmp
SET
move_order_line_id = x_new_mol_rec.line_id
WHERE transaction_temp_id = l_orig_mmtt_rec.transaction_temp_id
AND move_order_line_id = x_orig_mol_rec.line_id
AND row_type = 'All Task';
-- don't update qty, since it is updated in first loop
-- Update new MOL with the increased quantity
x_new_mol_rec.primary_quantity := x_new_mol_rec.primary_quantity + l_orig_mmtt_rec.primary_quantity;
-- Update original MOL with the reduced quantity
x_orig_mol_rec.primary_quantity := x_orig_mol_rec.primary_quantity - l_orig_mmtt_rec.primary_quantity;
-- Update p_mo_splt_tb and l_qty_to_splt so that
-- the new qty will be looked at in the next iteration
l_prim_qty_to_splt := l_prim_qty_to_splt-l_orig_mmtt_rec.primary_quantity;
l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
SELECT
line_id
,header_id
,quantity
,primary_quantity
,Nvl(quantity_delivered,0)
,Nvl(quantity_detailed,0)
,uom_code
,inventory_item_id
,organization_id
, SECONDARY_UOM_CODE --OPM Convergence
, NVL(SECONDARY_QUANTITY,0) --OPM Convergence
, NVL(SECONDARY_QUANTITY_DELIVERED,0) --OPM Convergence
, NVL(SECONDARY_QUANTITY_DETAILED,0) --OPM Convergence
, NVL(SECONDARY_REQUIRED_QUANTITY,0) --OPM Convergence
, backorder_delivery_detail_id --R12: XDOCK EXE
, crossdock_type --R12: XDOCK EXE
INTO
l_orig_mol_rec.line_id
,l_orig_mol_rec.header_id
,l_orig_mol_rec.quantity
,l_orig_mol_rec.primary_quantity
,l_orig_mol_rec.quantity_delivered
,l_orig_mol_rec.quantity_detailed
,l_orig_mol_rec.uom_code
,l_orig_mol_rec.inventory_item_id
,l_orig_mol_rec.organization_id
,l_orig_mol_rec.SECONDARY_UOM --OPM Convergence
,l_orig_mol_rec.SECONDARY_QUANTITY --OPM Convergence
,l_orig_mol_rec.SECONDARY_QUANTITY_DELIVERED --OPM Convergence
,l_orig_mol_rec.SECONDARY_QUANTITY_DETAILED --OPM Convergence
,l_orig_mol_rec.SECONDARY_REQUIRED_QUANTITY --OPM Convergence
,l_orig_mol_rec.backorder_delivery_detail_id
,l_orig_mol_rec.crossdock_type
FROM
mtl_txn_request_lines
WHERE
line_id = p_orig_mol_id;
SELECT MAX(line_number)
INTO l_line_num
FROM mtl_txn_request_lines
WHERE header_id = l_orig_mol_rec.header_id;
SELECT MTL_TXN_REQUEST_LINES_S.NEXTVAL
INTO l_new_mol_rec.line_id
FROM dual;
INSERT INTO mtl_txn_request_lines
(
LINE_ID
,HEADER_ID
,LINE_NUMBER
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,REVISION
,FROM_SUBINVENTORY_ID
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
,TO_SUBINVENTORY_CODE
,TO_SUBINVENTORY_ID
,TO_LOCATOR_ID
,TO_ACCOUNT_ID
,SHIP_TO_LOCATION_ID
,LOT_NUMBER
,SERIAL_NUMBER_START
,SERIAL_NUMBER_END
,UOM_CODE
,QUANTITY
,QUANTITY_DELIVERED
,QUANTITY_DETAILED
,DATE_REQUIRED
,REASON_ID
,REFERENCE
,REFERENCE_TYPE_CODE
,REFERENCE_ID
,REFERENCE_DETAIL_ID
,ASSIGNMENT_ID
,PROJECT_ID
,TASK_ID
,TRANSACTION_HEADER_ID
,LINE_STATUS
,STATUS_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,CREATED_BY
,CREATION_DATE
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE_CATEGORY
,TXN_SOURCE_ID
,TXN_SOURCE_LINE_ID
,TXN_SOURCE_LINE_DETAIL_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_SOURCE_TYPE_ID
,PRIMARY_QUANTITY
,TO_ORGANIZATION_ID
,PUT_AWAY_STRATEGY_ID
,PICK_STRATEGY_ID
,UNIT_NUMBER
,FROM_COST_GROUP_ID
,TO_COST_GROUP_ID
,LPN_ID
,TO_LPN_ID
,INSPECTION_STATUS
,PICK_METHODOLOGY_ID
,CONTAINER_ITEM_ID
,CARTON_GROUPING_ID
,BACKORDER_DELIVERY_DETAIL_ID
,WMS_PROCESS_FLAG
,PICK_SLIP_NUMBER
,PICK_SLIP_DATE
,SHIP_SET_ID
,SHIP_MODEL_ID
,MODEL_QUANTITY
,CROSSDOCK_TYPE
,REQUIRED_QUANTITY
,SECONDARY_QUANTITY --OPM Convergence
,SECONDARY_QUANTITY_DELIVERED --OPM Convergence
,SECONDARY_QUANTITY_DETAILED --OPM Convergence
,WIP_ENTITY_ID --Bug 5934992
,OPERATION_SEQ_NUM --Bug 5948720
,WIP_SUPPLY_TYPE --Bug 5948720
)
SELECT
l_new_mol_rec.line_id --LINE_ID
,HEADER_ID
,l_new_mol_rec.line_number --LINE_NUMBER
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,REVISION
,FROM_SUBINVENTORY_ID
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
,TO_SUBINVENTORY_CODE
,TO_SUBINVENTORY_ID
,TO_LOCATOR_ID
,TO_ACCOUNT_ID
,SHIP_TO_LOCATION_ID
,LOT_NUMBER
,SERIAL_NUMBER_START
,SERIAL_NUMBER_END
,UOM_CODE
,0 --QUANTITY
,0 --QUANTITY_DELIVERED
,0 --QUANTITY_DETAILED
,DATE_REQUIRED
,REASON_ID
,REFERENCE
,REFERENCE_TYPE_CODE
,REFERENCE_ID
,REFERENCE_DETAIL_ID
,ASSIGNMENT_ID
,PROJECT_ID
,TASK_ID
,TRANSACTION_HEADER_ID
,LINE_STATUS
,STATUS_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,Sysdate --LAST_UPDATE_DATE
,CREATED_BY
,Sysdate --CREATION_DATE
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE_CATEGORY
,TXN_SOURCE_ID
,TXN_SOURCE_LINE_ID
,TXN_SOURCE_LINE_DETAIL_ID
,TRANSACTION_TYPE_ID
,TRANSACTION_SOURCE_TYPE_ID
,0 --PRIMARY_QUANTITY
,TO_ORGANIZATION_ID
,PUT_AWAY_STRATEGY_ID
,PICK_STRATEGY_ID
,UNIT_NUMBER
,FROM_COST_GROUP_ID
,TO_COST_GROUP_ID
,LPN_ID
,TO_LPN_ID
,INSPECTION_STATUS
,PICK_METHODOLOGY_ID
,CONTAINER_ITEM_ID
,CARTON_GROUPING_ID
,BACKORDER_DELIVERY_DETAIL_ID
,WMS_PROCESS_FLAG
,PICK_SLIP_NUMBER
,PICK_SLIP_DATE
,SHIP_SET_ID
,SHIP_MODEL_ID
,MODEL_QUANTITY
,CROSSDOCK_TYPE
,REQUIRED_QUANTITY
,0--SECONDARY_QUANTITY --OPM Convergence
,0--SECONDARY_QUANTITY_DELIVERED --OPM Convergence
,0--SECONDARY_QUANTITY_DETAILED --OPM Convergence
,WIP_ENTITY_ID --Bug 5934992
,OPERATION_SEQ_NUM --Bug 5948720
,WIP_SUPPLY_TYPE --Bug 5948720
FROM mtl_txn_request_lines
WHERE line_id = l_orig_mol_rec.line_id;
SELECT Nvl(source_document_type_id, -1)
INTO l_doc_type
FROM wsh_delivery_details
WHERE delivery_detail_id = l_orig_mol_rec.backorder_delivery_detail_id;
l_rsv_update_rec := l_rsv_results_tbl(1);
l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
l_rsv_update_rec.primary_reservation_quantity := l_new_mol_rec.primary_quantity;
l_rsv_update_rec.reservation_quantity :=
inv_rcv_cache.convert_qty
(p_inventory_item_id => l_new_mol_rec.inventory_item_id
,p_from_qty => l_new_mol_rec.primary_quantity
,p_from_uom_code => l_rsv_results_tbl(1).primary_uom_code
,p_to_uom_code => l_rsv_results_tbl(1).reservation_uom_code
);
,p_to_rsv_rec => l_rsv_update_rec
,p_original_serial_number => l_dummy_serial
,p_to_serial_number => l_dummy_serial
,p_validation_flag => fnd_api.g_false --??
,x_to_reservation_id => l_new_reservation_id);
print_debug('SPLIT_MO - Update new MOL:'
||l_new_mol_rec.line_id||' with quantity = ' ||
l_new_mol_rec.quantity || ' qty_dlvd = ' ||
l_new_mol_rec.quantity_delivered || ' qty_dtld = ' ||
l_new_mol_rec.quantity_detailed, 4);
UPDATE
mtl_txn_request_lines
SET
primary_quantity = l_new_mol_rec.primary_quantity
,quantity = l_new_mol_rec.quantity
,quantity_delivered = l_new_mol_rec.quantity_delivered
,quantity_detailed = l_new_mol_rec.quantity_detailed
-- OPM COnvergence
,secondary_quantity = l_new_mol_rec.secondary_quantity
,secondary_quantity_delivered = l_new_mol_rec.secondary_quantity_delivered
,secondary_quantity_detailed = l_new_mol_rec.secondary_quantity_detailed
,last_update_date = Sysdate
,backorder_delivery_detail_id = l_new_mol_rec.backorder_delivery_detail_id
WHERE
line_id = l_new_mol_rec.line_id;
UPDATE
mtl_txn_request_lines
SET
primary_quantity = l_new_mol_rec.primary_quantity
,quantity = l_new_mol_rec.quantity
,quantity_delivered = l_new_mol_rec.quantity_delivered
,quantity_detailed = l_new_mol_rec.quantity_detailed
,last_update_date = Sysdate
,backorder_delivery_detail_id = l_new_mol_rec.backorder_delivery_detail_id
WHERE
line_id = l_new_mol_rec.line_id;
print_debug('SPLIT_MO: Error update new mol record',
4);
-- Update the p_mo_splt_tb with the new MOL line_id
p_mo_splt_tb(l_indx).line_id := l_new_mol_rec.line_id;
print_debug('SPLIT_MO - Update original MOL:'
||l_orig_mol_rec.line_id ||' with quantity = ' ||
l_orig_mol_rec.quantity || ' qty_dlvd = ' ||
l_orig_mol_rec.quantity_delivered || ' qty_dtld = ' ||
l_orig_mol_rec.quantity_detailed||' with sec quantity = ' ||
l_orig_mol_rec.secondary_quantity || ' sec_qty_dlvd = ' ||
l_orig_mol_rec.secondary_quantity_delivered || ' sec_qty_dtld = ' ||
l_orig_mol_rec.secondary_quantity_detailed,4);
UPDATE
mtl_txn_request_lines
SET
primary_quantity = l_orig_mol_rec.primary_quantity
,quantity = l_orig_mol_rec.quantity
,quantity_delivered = l_orig_mol_rec.quantity_delivered
,quantity_detailed = l_orig_mol_rec.quantity_detailed
-- OPM COnvergence
,secondary_quantity = l_orig_mol_rec.secondary_quantity
,secondary_quantity_delivered = l_orig_mol_rec.secondary_quantity_delivered
,secondary_quantity_detailed = l_orig_mol_rec.secondary_quantity_detailed
,last_update_date = Sysdate
WHERE
line_id = l_orig_mol_rec.line_id;
UPDATE
mtl_txn_request_lines
SET
primary_quantity = l_orig_mol_rec.primary_quantity
,quantity = l_orig_mol_rec.quantity
,quantity_delivered = l_orig_mol_rec.quantity_delivered
,quantity_detailed = l_orig_mol_rec.quantity_detailed
,last_update_date = Sysdate
WHERE
line_id = l_orig_mol_rec.line_id;
print_debug('SPLIT_MO ERROR: error update original MOL',4);
SELECT 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
,Ltrim(Rtrim(lot_number)) 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
,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
,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
,org_cost_group_id
,cost_type_id
,transaction_status
,standard_operation_id
,task_priority
,wms_task_type
,parent_line_id
,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
,move_order_header_id
,serial_allocated_flag
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_orig_mmtt_id;
INSERT INTO mtl_material_transactions_temp
( 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
,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
,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
,org_cost_group_id
,cost_type_id
,transaction_status
,standard_operation_id
,task_priority
,wms_task_type
,parent_line_id
,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
,move_order_header_id
,serial_allocated_flag )
VALUES
( mtl_material_transactions_s.NEXTVAL --use different header
,mtl_material_transactions_s.NEXTVAL
,l_mmtt_rec.SOURCE_CODE
,l_mmtt_rec.SOURCE_LINE_ID
,l_mmtt_rec.TRANSACTION_MODE
,l_mmtt_rec.LOCK_FLAG
,l_sysdate
,l_mmtt_rec.LAST_UPDATED_BY
,l_sysdate
,l_mmtt_rec.CREATED_BY
,l_mmtt_rec.LAST_UPDATE_LOGIN
,l_mmtt_rec.REQUEST_ID
,l_mmtt_rec.PROGRAM_APPLICATION_ID
,l_mmtt_rec.PROGRAM_ID
,l_mmtt_rec.PROGRAM_UPDATE_DATE
,l_mmtt_rec.INVENTORY_ITEM_ID
,l_mmtt_rec.REVISION
,l_mmtt_rec.ORGANIZATION_ID
,l_mmtt_rec.SUBINVENTORY_CODE
,l_mmtt_rec.LOCATOR_ID
,l_mmtt_rec.TRANSACTION_QUANTITY
,l_mmtt_rec.PRIMARY_QUANTITY
,l_mmtt_rec.TRANSACTION_UOM
,l_mmtt_rec.TRANSACTION_COST
,l_mmtt_rec.TRANSACTION_TYPE_ID
,l_mmtt_rec.TRANSACTION_ACTION_ID
,l_mmtt_rec.TRANSACTION_SOURCE_TYPE_ID
,l_mmtt_rec.TRANSACTION_SOURCE_ID
,l_mmtt_rec.TRANSACTION_SOURCE_NAME
,l_mmtt_rec.TRANSACTION_DATE
,l_mmtt_rec.ACCT_PERIOD_ID
,l_mmtt_rec.DISTRIBUTION_ACCOUNT_ID
,l_mmtt_rec.TRANSACTION_REFERENCE
,l_mmtt_rec.REQUISITION_LINE_ID
,l_mmtt_rec.REQUISITION_DISTRIBUTION_ID
,l_mmtt_rec.REASON_ID
,Ltrim(Rtrim(l_mmtt_rec.lot_number))
,l_mmtt_rec.LOT_EXPIRATION_DATE
,l_mmtt_rec.SERIAL_NUMBER
,l_mmtt_rec.RECEIVING_DOCUMENT
,l_mmtt_rec.DEMAND_ID
,l_mmtt_rec.RCV_TRANSACTION_ID
,l_mmtt_rec.MOVE_TRANSACTION_ID
,l_mmtt_rec.COMPLETION_TRANSACTION_ID
,l_mmtt_rec.WIP_ENTITY_TYPE
,l_mmtt_rec.SCHEDULE_ID
,l_mmtt_rec.REPETITIVE_LINE_ID
,l_mmtt_rec.employee_code
,l_mmtt_rec.PRIMARY_SWITCH
,l_mmtt_rec.SCHEDULE_UPDATE_CODE
,l_mmtt_rec.SETUP_TEARDOWN_CODE
,l_mmtt_rec.ITEM_ORDERING
,l_mmtt_rec.NEGATIVE_REQ_FLAG
,l_mmtt_rec.OPERATION_SEQ_NUM
,l_mmtt_rec.PICKING_LINE_ID
,l_mmtt_rec.TRX_SOURCE_LINE_ID
,l_mmtt_rec.TRX_SOURCE_DELIVERY_ID
,l_mmtt_rec.PHYSICAL_ADJUSTMENT_ID
,l_mmtt_rec.CYCLE_COUNT_ID
,l_mmtt_rec.RMA_LINE_ID
,l_mmtt_rec.CUSTOMER_SHIP_ID
,l_mmtt_rec.CURRENCY_CODE
,l_mmtt_rec.CURRENCY_CONVERSION_RATE
,l_mmtt_rec.CURRENCY_CONVERSION_TYPE
,l_mmtt_rec.CURRENCY_CONVERSION_DATE
,l_mmtt_rec.USSGL_TRANSACTION_CODE
,l_mmtt_rec.VENDOR_LOT_NUMBER
,l_mmtt_rec.ENCUMBRANCE_ACCOUNT
,l_mmtt_rec.ENCUMBRANCE_AMOUNT
,l_mmtt_rec.SHIP_TO_LOCATION
,l_mmtt_rec.SHIPMENT_NUMBER
,l_mmtt_rec.TRANSFER_COST
,l_mmtt_rec.TRANSPORTATION_COST
,l_mmtt_rec.TRANSPORTATION_ACCOUNT
,l_mmtt_rec.FREIGHT_CODE
,l_mmtt_rec.CONTAINERS
,l_mmtt_rec.WAYBILL_AIRBILL
,l_mmtt_rec.EXPECTED_ARRIVAL_DATE
,l_mmtt_rec.TRANSFER_SUBINVENTORY
,l_mmtt_rec.TRANSFER_ORGANIZATION
,l_mmtt_rec.TRANSFER_TO_LOCATION
,l_mmtt_rec.NEW_AVERAGE_COST
,l_mmtt_rec.VALUE_CHANGE
,l_mmtt_rec.PERCENTAGE_CHANGE
,l_mmtt_rec.MATERIAL_ALLOCATION_TEMP_ID
,l_mmtt_rec.DEMAND_SOURCE_HEADER_ID
,l_mmtt_rec.DEMAND_SOURCE_LINE
,l_mmtt_rec.DEMAND_SOURCE_DELIVERY
,l_mmtt_rec.ITEM_SEGMENTS
,l_mmtt_rec.ITEM_DESCRIPTION
,l_mmtt_rec.ITEM_TRX_ENABLED_FLAG
,l_mmtt_rec.ITEM_LOCATION_CONTROL_CODE
,l_mmtt_rec.ITEM_RESTRICT_SUBINV_CODE
,l_mmtt_rec.ITEM_RESTRICT_LOCATORS_CODE
,l_mmtt_rec.ITEM_REVISION_QTY_CONTROL_CODE
,l_mmtt_rec.ITEM_PRIMARY_UOM_CODE
,l_mmtt_rec.ITEM_UOM_CLASS
,l_mmtt_rec.ITEM_SHELF_LIFE_CODE
,l_mmtt_rec.ITEM_SHELF_LIFE_DAYS
,l_mmtt_rec.ITEM_LOT_CONTROL_CODE
,l_mmtt_rec.ITEM_SERIAL_CONTROL_CODE
,l_mmtt_rec.ITEM_INVENTORY_ASSET_FLAG
,l_mmtt_rec.ALLOWED_UNITS_LOOKUP_CODE
,l_mmtt_rec.DEPARTMENT_ID
,l_mmtt_rec.DEPARTMENT_CODE
,l_mmtt_rec.WIP_SUPPLY_TYPE
,l_mmtt_rec.SUPPLY_SUBINVENTORY
,l_mmtt_rec.SUPPLY_LOCATOR_ID
,l_mmtt_rec.VALID_SUBINVENTORY_FLAG
,l_mmtt_rec.VALID_LOCATOR_FLAG
,l_mmtt_rec.LOCATOR_SEGMENTS
,l_mmtt_rec.CURRENT_LOCATOR_CONTROL_CODE
,l_mmtt_rec.NUMBER_OF_LOTS_ENTERED
,l_mmtt_rec.WIP_COMMIT_FLAG
,l_mmtt_rec.NEXT_LOT_NUMBER
,l_mmtt_rec.LOT_ALPHA_PREFIX
,l_mmtt_rec.NEXT_SERIAL_NUMBER
,l_mmtt_rec.SERIAL_ALPHA_PREFIX
,l_mmtt_rec.SHIPPABLE_FLAG
,l_mmtt_rec.POSTING_FLAG
,l_mmtt_rec.REQUIRED_FLAG
,l_mmtt_rec.PROCESS_FLAG
,l_mmtt_rec.ERROR_CODE
,l_mmtt_rec.ERROR_EXPLANATION
,l_mmtt_rec.ATTRIBUTE_CATEGORY
,l_mmtt_rec.ATTRIBUTE1
,l_mmtt_rec.ATTRIBUTE2
,l_mmtt_rec.ATTRIBUTE3
,l_mmtt_rec.ATTRIBUTE4
,l_mmtt_rec.ATTRIBUTE5
,l_mmtt_rec.ATTRIBUTE6
,l_mmtt_rec.ATTRIBUTE7
,l_mmtt_rec.ATTRIBUTE8
,l_mmtt_rec.ATTRIBUTE9
,l_mmtt_rec.ATTRIBUTE10
,l_mmtt_rec.ATTRIBUTE11
,l_mmtt_rec.ATTRIBUTE12
,l_mmtt_rec.ATTRIBUTE13
,l_mmtt_rec.ATTRIBUTE14
,l_mmtt_rec.ATTRIBUTE15
,l_mmtt_rec.MOVEMENT_ID
,l_mmtt_rec.RESERVATION_QUANTITY
,l_mmtt_rec.SHIPPED_QUANTITY
,l_mmtt_rec.TRANSACTION_LINE_NUMBER
,l_mmtt_rec.TASK_ID
,l_mmtt_rec.TO_TASK_ID
,l_mmtt_rec.SOURCE_TASK_ID
,l_mmtt_rec.PROJECT_ID
,l_mmtt_rec.SOURCE_PROJECT_ID
,l_mmtt_rec.PA_EXPENDITURE_ORG_ID
,l_mmtt_rec.TO_PROJECT_ID
,l_mmtt_rec.EXPENDITURE_TYPE
,l_mmtt_rec.FINAL_COMPLETION_FLAG
,l_mmtt_rec.TRANSFER_PERCENTAGE
,l_mmtt_rec.TRANSACTION_SEQUENCE_ID
,l_mmtt_rec.MATERIAL_ACCOUNT
,l_mmtt_rec.MATERIAL_OVERHEAD_ACCOUNT
,l_mmtt_rec.RESOURCE_ACCOUNT
,l_mmtt_rec.OUTSIDE_PROCESSING_ACCOUNT
,l_mmtt_rec.OVERHEAD_ACCOUNT
,l_mmtt_rec.FLOW_SCHEDULE
,l_mmtt_rec.COST_GROUP_ID
,l_mmtt_rec.DEMAND_CLASS
,l_mmtt_rec.QA_COLLECTION_ID
,l_mmtt_rec.KANBAN_CARD_ID
,l_mmtt_rec.OVERCOMPLETION_TRANSACTION_QTY
,l_mmtt_rec.OVERCOMPLETION_PRIMARY_QTY
,l_mmtt_rec.OVERCOMPLETION_TRANSACTION_ID
,l_mmtt_rec.END_ITEM_UNIT_NUMBER
,l_mmtt_rec.SCHEDULED_PAYBACK_DATE
,l_mmtt_rec.LINE_TYPE_CODE
,l_mmtt_rec.PARENT_TRANSACTION_TEMP_ID
,l_mmtt_rec.PUT_AWAY_STRATEGY_ID
,l_mmtt_rec.PUT_AWAY_RULE_ID
,l_mmtt_rec.PICK_STRATEGY_ID
,l_mmtt_rec.PICK_RULE_ID
,l_mmtt_rec.MOVE_ORDER_LINE_ID
,l_mmtt_rec.TASK_GROUP_ID
,l_mmtt_rec.PICK_SLIP_NUMBER
,l_mmtt_rec.RESERVATION_ID
,l_mmtt_rec.COMMON_BOM_SEQ_ID
,l_mmtt_rec.COMMON_ROUTING_SEQ_ID
,l_mmtt_rec.ORG_COST_GROUP_ID
,l_mmtt_rec.COST_TYPE_ID
,l_mmtt_rec.TRANSACTION_STATUS
,l_mmtt_rec.STANDARD_OPERATION_ID
,l_mmtt_rec.TASK_PRIORITY
,l_mmtt_rec.WMS_TASK_TYPE
,l_mmtt_rec.PARENT_LINE_ID
,l_mmtt_rec.TRANSFER_COST_GROUP_ID
,l_mmtt_rec.LPN_ID
,l_mmtt_rec.TRANSFER_LPN_ID
,l_mmtt_rec.WMS_TASK_STATUS
,l_mmtt_rec.CONTENT_LPN_ID
,l_mmtt_rec.CONTAINER_ITEM_ID
,l_mmtt_rec.CARTONIZATION_ID
,l_mmtt_rec.PICK_SLIP_DATE
,l_mmtt_rec.REBUILD_ITEM_ID
,l_mmtt_rec.REBUILD_SERIAL_NUMBER
,l_mmtt_rec.REBUILD_ACTIVITY_ID
,l_mmtt_rec.REBUILD_JOB_NAME
,l_mmtt_rec.ORGANIZATION_TYPE
,l_mmtt_rec.TRANSFER_ORGANIZATION_TYPE
,l_mmtt_rec.OWNING_ORGANIZATION_ID
,l_mmtt_rec.OWNING_TP_TYPE
,l_mmtt_rec.XFR_OWNING_ORGANIZATION_ID
,l_mmtt_rec.TRANSFER_OWNING_TP_TYPE
,l_mmtt_rec.PLANNING_ORGANIZATION_ID
,l_mmtt_rec.PLANNING_TP_TYPE
,l_mmtt_rec.XFR_PLANNING_ORGANIZATION_ID
,l_mmtt_rec.TRANSFER_PLANNING_TP_TYPE
,l_mmtt_rec.SECONDARY_UOM_CODE
,l_mmtt_rec.SECONDARY_TRANSACTION_QUANTITY
,l_mmtt_rec.ALLOCATED_LPN_ID
,l_mmtt_rec.SCHEDULE_NUMBER
,l_mmtt_rec.SCHEDULED_FLAG
,l_mmtt_rec.CLASS_CODE
,l_mmtt_rec.SCHEDULE_GROUP
,l_mmtt_rec.BUILD_SEQUENCE
,l_mmtt_rec.BOM_REVISION
,l_mmtt_rec.ROUTING_REVISION
,l_mmtt_rec.BOM_REVISION_DATE
,l_mmtt_rec.ROUTING_REVISION_DATE
,l_mmtt_rec.ALTERNATE_BOM_DESIGNATOR
,l_mmtt_rec.ALTERNATE_ROUTING_DESIGNATOR
,l_mmtt_rec.TRANSACTION_BATCH_ID
,l_mmtt_rec.TRANSACTION_BATCH_SEQ
,l_mmtt_rec.operation_plan_id
,l_mmtt_rec.move_order_header_id
,l_mmtt_rec.serial_allocated_flag)
returning transaction_temp_id INTO l_new_mmtt_id;
print_debug('SPLIT_MMTT: Error while inserting new MMTT!', 4);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - l_mmtt_rec.transaction_quantity
, primary_quantity = primary_quantity - l_mmtt_rec.primary_quantity
WHERE transaction_temp_id = p_orig_mmtt_id;