INV Lot Upload

➜ Back to Library
Description

INV Lot Upload creates and updates Oracle Inventory lots from Excel, with the fields of the Lot Numbers form: status, grade, the Disabled flag, the parent lot of a new lot, the origination, expiration, expiration action, retest, maturity and hold dates, and the Lot Attributes and lot descriptive flexfields. A row updates the lot when Organization Code, Item and Lot Number exist, and creates it otherwise.

When to use it

  • Correct the expiration dates of many lots at once, for example after a shelf life change.
  • Put lots on hold or release them by changing their material status or grade.
  • Create lots ahead of receipt or production.
  • Maintain lot attributes and descriptive flexfield values in bulk.

Before you start

  • You are signed in to an Inventory responsibility with access to the organization.
  • The items are lot controlled. Expiration, expiration action and retest dates apply only to items under shelf life control, Lot Status only to lot status enabled items, Grade only to grade controlled items, and Parent Lot only to child lot enabled items.

Choose a template

TemplateUse it for
Default (default)Creating and updating lots with the fields of the Lot Numbers form, without the flexfields.
Expiration Date CorrectionCorrecting the expiration, expiration action and retest dates of existing lots.
Lot Status and GradeChanging the material status, grade and Disabled flag of existing lots, for example to place lots on hold or release them.
Create LotsAn empty sheet to create new lots ahead of receipt or production. Upload Mode is fixed to Create.
FlexfieldsMaintaining the Lot Attributes and the lot descriptive flexfield of existing lots.

Step 1 – Set the parameters

Open INV Lot Upload in Blitz Report, choose the template and set the parameters:

ParameterMeaning
Upload ModeCreate opens an empty sheet; Create, Update (default) downloads existing lots for editing and lets you add new ones.
Organization CodeThe inventory organization of the lots.
ItemRestricts the download to the lots of one item.
Lot NumberRestricts the download to one lot.
Expiration Date From / ToRestricts the download to lots expiring in this date range. Expiration Date To defaults to the From date.
Blitz Report run screen for INV Lot Upload with the Default template, Upload Mode Create, Update, Organization Code M2 and Item AS62444

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with one row per existing lot, or an empty sheet in Create mode.

Excel file with the existing lot B00248 of item AS62444 in organization M2, status GOOD

Step 3 – Enter or change the lots

Organization Code, Item and Lot Number identify a lot and cannot be changed on a downloaded row. Item Description, Shelf Life Control and Origination Type are for information. Change the Lot Status, Grade, Disabled flag or dates of a downloaded row, or add rows with the Organization Code, Item and Lot Number of a new lot plus the values to set. Lot Status and Grade are picked from the values valid for the item.

For a new lot, the status, grade, origination type and dates left blank take the item defaults, a blank Origination Date defaults to today, and a child lot takes the parent lot attributes where the item or organization is set up to copy them. Parent Lot can only be entered for a new lot. Clearing the Expiration Action Date also clears the Expiration Action. Rows can also be pasted from another source: only the pasted columns are updated.

Excel file with the Lot Status of lot B00248 changed to QC Hold, marked Update, and a new lot SP-261005 added, marked Create

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and saves the file. Correct any rows it flags before uploading.

Both edited rows show status Valid after Validate and Save

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which creates or updates each lot.

File Upload page with the saved Excel file selected for upload

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status and message, and the values stored for each lot.

Result report showing lot B00248 updated to QC Hold and lot SP-261005 created with the item default status GOOD and today's origination date, both with status Success

What’s produced

  • New and updated lots, with the same defaulting, validation and status and grade history as the Lot Numbers form.
  • With the profile INV: Global Lot Attributes set to Yes, changed dates and grades are applied to the same lot in all organizations, as in the form.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Can I clear an expiration date?
Not for an item under shelf life control: its lots need an expiration date, as in the form. For other items the expiration date cannot be changed at all.

Does changing the Origination Date recalculate the other dates?
No. As in the form, the other dates are derived from the origination date only when a lot is created.

Can I change the parent lot of an existing lot?
No. A parent lot can only be entered when the lot is created.

Can I delete lots?
No, the Lot Numbers form does not delete lots either. Use the Disabled flag or a material status instead.

Can I have the lot number generated?
No, enter the lot number for each new lot.

Troubleshooting

MessageCauseWhat to do
Expiration Date (or Expiration Action Date, Retest Date) cannot be changed because the item is not under shelf life controlThe item has no shelf life control.Leave these dates unchanged, or change the item’s shelf life control.
Expiration Date is required for items under shelf life controlThe expiration date was cleared, or is missing on a new lot of an item with a user-defined expiration date.Enter an expiration date.
Expiration Date must be on or after the Origination Date (similar for the other dates)A date lies before the lot’s origination date. Retest, Maturity and Hold Date must be after it.Correct the date or the origination date.
Origination Date cannot be clearedThe origination date of an existing lot was deleted.Enter an origination date.
Expiration Action requires an Expiration Action DateAn expiration action was entered without a date.Enter the expiration action date.
Lot Status is invalidThe value is not a lot status valid for the item. An item that is not lot status enabled has no valid statuses, so a downloaded row of such an item fails with the Default template.Pick the status from the list. For items that are not lot status enabled, use a template without the Lot Status column, such as Expiration Date Correction.
Status / Grade / Parent Lot cannot be changed because the item is not …The item is not lot status enabled, grade controlled or child lot enabled.Leave the column blank for this item.
The Material Status cannot be modified. Pending Transactions exist for this LotThe new status disallows transactions that are still pending for the lot.Process or cancel the pending transactions, then upload again.
Cannot change the status if reservations are associated with this entityThe lot is reserved and the new status does not allow reservations.Remove the reservations or choose another status.
Duplicate lot. This lot has already been processed in this uploadThe same lot appears twice in the file.Keep one row per lot.
select
null action_,
null status_,
null message_,
to_number(null) request_id_,
null modified_columns_,
mp.organization_code,
msiv.concatenated_segments item,
msiv.description item_description,
xxen_util.meaning(msiv.shelf_life_code,'MTL_SHELF_LIFE',700) shelf_life_control,
mln.parent_lot_number parent_lot,
mln.lot_number,
xxen_util.yes(decode(mln.disable_flag,1,'Y')) disabled,
(select mmsv.status_code from mtl_material_statuses_vl mmsv where mln.status_id=mmsv.status_id) lot_status,
mln.grade_code grade,
xxen_util.meaning(mln.origination_type,'MTL_LOT_ORIGINATION_TYPE',700) origination_type,
mln.origination_date,
mln.maturity_date,
mln.hold_date,
mln.expiration_date,
mln.expiration_action_date,
mln.expiration_action_code expiration_action,
mln.retest_date,
xxen_util.display_flexfield_context(401,'Lot Attributes',mln.lot_attribute_category) lot_attributes_context,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'DESCRIPTION',mln.rowid,mln.description) lot_attr_description,
mln.vendor_id lot_attr_vendor_id,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'TERRITORY_CODE',mln.rowid,mln.territory_code) lot_attr_territory_code,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'DATE_CODE',mln.rowid,mln.date_code) lot_attr_date_code,
mln.change_date lot_attr_change_date,
mln.age lot_attr_age,
mln.item_size lot_attr_item_size,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'COLOR',mln.rowid,mln.color) lot_attr_color,
mln.volume lot_attr_volume,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'VOLUME_UOM',mln.rowid,mln.volume_uom) lot_attr_volume_uom,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'PLACE_OF_ORIGIN',mln.rowid,mln.place_of_origin) lot_attr_place_of_origin,
mln.best_by_date lot_attr_best_by_date,
mln.length lot_attr_length,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'LENGTH_UOM',mln.rowid,mln.length_uom) lot_attr_length_uom,
mln.recycled_content lot_attr_recycled_content,
mln.thickness lot_attr_thickness,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'THICKNESS_UOM',mln.rowid,mln.thickness_uom) lot_attr_thickness_uom,
mln.width lot_attr_width,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'WIDTH_UOM',mln.rowid,mln.width_uom) lot_attr_width_uom,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'CURL_WRINKLE_FOLD',mln.rowid,mln.curl_wrinkle_fold) lot_attr_curl_wrinkle_fold,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE1',mln.rowid,mln.c_attribute1) lot_attr_c_attribute1,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE2',mln.rowid,mln.c_attribute2) lot_attr_c_attribute2,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE3',mln.rowid,mln.c_attribute3) lot_attr_c_attribute3,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE4',mln.rowid,mln.c_attribute4) lot_attr_c_attribute4,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE5',mln.rowid,mln.c_attribute5) lot_attr_c_attribute5,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE6',mln.rowid,mln.c_attribute6) lot_attr_c_attribute6,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE7',mln.rowid,mln.c_attribute7) lot_attr_c_attribute7,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE8',mln.rowid,mln.c_attribute8) lot_attr_c_attribute8,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE9',mln.rowid,mln.c_attribute9) lot_attr_c_attribute9,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE10',mln.rowid,mln.c_attribute10) lot_attr_c_attribute10,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE11',mln.rowid,mln.c_attribute11) lot_attr_c_attribute11,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE12',mln.rowid,mln.c_attribute12) lot_attr_c_attribute12,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE13',mln.rowid,mln.c_attribute13) lot_attr_c_attribute13,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE14',mln.rowid,mln.c_attribute14) lot_attr_c_attribute14,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE15',mln.rowid,mln.c_attribute15) lot_attr_c_attribute15,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE16',mln.rowid,mln.c_attribute16) lot_attr_c_attribute16,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE17',mln.rowid,mln.c_attribute17) lot_attr_c_attribute17,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE18',mln.rowid,mln.c_attribute18) lot_attr_c_attribute18,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE19',mln.rowid,mln.c_attribute19) lot_attr_c_attribute19,
xxen_util.display_flexfield_value(401,'Lot Attributes',mln.lot_attribute_category,'C_ATTRIBUTE20',mln.rowid,mln.c_attribute20) lot_attr_c_attribute20,
mln.d_attribute1 lot_attr_d_attribute1,
mln.d_attribute2 lot_attr_d_attribute2,
mln.d_attribute3 lot_attr_d_attribute3,
mln.d_attribute4 lot_attr_d_attribute4,
mln.d_attribute5 lot_attr_d_attribute5,
mln.d_attribute6 lot_attr_d_attribute6,
mln.d_attribute7 lot_attr_d_attribute7,
mln.d_attribute8 lot_attr_d_attribute8,
mln.d_attribute9 lot_attr_d_attribute9,
mln.d_attribute10 lot_attr_d_attribute10,
mln.n_attribute1 lot_attr_n_attribute1,
mln.n_attribute2 lot_attr_n_attribute2,
mln.n_attribute3 lot_attr_n_attribute3,
mln.n_attribute4 lot_attr_n_attribute4,
mln.n_attribute5 lot_attr_n_attribute5,
mln.n_attribute6 lot_attr_n_attribute6,
mln.n_attribute7 lot_attr_n_attribute7,
mln.n_attribute8 lot_attr_n_attribute8,
mln.n_attribute9 lot_attr_n_attribute9,
mln.n_attribute10 lot_attr_n_attribute10,
xxen_util.display_flexfield_context(401,'MTL_LOT_NUMBERS',mln.attribute_category) lot_attribute_category,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE1',mln.rowid,mln.attribute1) lot_attribute1,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE2',mln.rowid,mln.attribute2) lot_attribute2,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE3',mln.rowid,mln.attribute3) lot_attribute3,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE4',mln.rowid,mln.attribute4) lot_attribute4,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE5',mln.rowid,mln.attribute5) lot_attribute5,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE6',mln.rowid,mln.attribute6) lot_attribute6,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE7',mln.rowid,mln.attribute7) lot_attribute7,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE8',mln.rowid,mln.attribute8) lot_attribute8,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE9',mln.rowid,mln.attribute9) lot_attribute9,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE10',mln.rowid,mln.attribute10) lot_attribute10,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE11',mln.rowid,mln.attribute11) lot_attribute11,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE12',mln.rowid,mln.attribute12) lot_attribute12,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE13',mln.rowid,mln.attribute13) lot_attribute13,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE14',mln.rowid,mln.attribute14) lot_attribute14,
xxen_util.display_flexfield_value(401,'MTL_LOT_NUMBERS',mln.attribute_category,'ATTRIBUTE15',mln.rowid,mln.attribute15) lot_attribute15,
to_number(null) upload_row
from
mtl_parameters mp,
mtl_system_items_vl msiv,
mtl_lot_numbers mln
where
mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
mp.organization_id=msiv.organization_id and
msiv.organization_id=mln.organization_id and
msiv.inventory_item_id=mln.inventory_item_id and
:p_upload_mode like '%'||xxen_upload.action_update and
1=1
Parameter NameSQL textValidation
Upload Mode
 
LOV
Organization Code
mp.organization_code=:organization_code
LOV
Item
msiv.concatenated_segments=:item
LOV
Lot Number
mln.lot_number=:lot_number
LOV
Expiration Date From
mln.expiration_date>=:expiration_date_from
Date
Expiration Date To
mln.expiration_date<:expiration_date_to+1
Date
Download
Related Blitz Reports
CAC Inventory Lot and Locator OPM Value (Period-End) CAC Inventory and Intransit Value (Period-End) - Discrete/OPM COPY OF: CAC OPM Costed Formula INV Material Status Where-Used INV Physical Inventory Locator Count Sheet ECC Process Genealogy and Trace, Lot Numbers INV Stock Locator Upload ECC Process Genealogy and Trace, Serial Numbers ECC Genealogy and Trace, Serial Numbers
Blitz Report™