INV Physical Inventory Tag Count Upload

➜ Back to Library
Description

INV Physical Inventory Tag Count Upload enters counted quantities against the tags of an Oracle Inventory physical inventory, from Excel – updating counts on existing tags, generating new tags where allowed, and voiding tags on supported releases. It records counts; it does not post the inventory adjustments.

When to use it

  • Enter physical count results in bulk from count sheets instead of keying each tag in the form.
  • Update counted quantities against many existing tags at once.
  • Add new count tags for combinations not pre-tagged, where dynamic tag entry is allowed.
  • Record dual-unit-of-measure counts, or counts for lot- and serial-controlled items.
  • Void tags that should be excluded from adjustment (on supported releases).

Before you start

  • Blitz Report is installed and you are signed in to an Inventory responsibility with access to the organization.
  • The physical inventory exists, its snapshot has been taken, and its adjustments are not yet posted.
  • Tags have been generated, unless dynamic tag entry is enabled.

Step 1 – Choose a mode, set the parameters and download

In Blitz Report, open INV Physical Inventory Tag Count Upload, choose an Upload Mode, and set the parameters:

ParameterPurpose
Upload ModeUpdate existing tags (default), Create new tags, or both.
Organization CodeThe inventory organization.
Physical InventoryThe physical inventory whose tags are downloaded.
Counted ByThe default employee recorded as having counted.
Subinventory / Item / Tag Number filtersRestrict which tags are downloaded.

Run the upload to download and open the Excel file. Each row is automatically handled as an update to an existing tag or a new tag.

Step 2 – Enter the counts

On each tag row enter the UOM and counted Quantity (and the Secondary UOM and Secondary Quantity for dual-unit items). For a new tag, enter the Item, Subinventory, Locator and any Lot Number or Serial Number – leave the Tag Number blank to have it generated. On an existing tag you can change only the count, not the item or location.

Step 3 – Void tags (optional)

On supported releases, set Void to Yes on an existing tag to exclude it from adjustment.

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and runs the upload’s validation, then saves the file. Correct anything it flags before continuing.

Step 5 – Upload and view the result

Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which records the counts against the tags. When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Counted quantities recorded against the physical inventory’s tags, and any new tags created.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Does this post the inventory adjustments?
No. It only records counts and tags. Approving and posting adjustments is done separately after counting.

How do I create a new tag?
Use an Upload Mode that allows creation, enter the item, subinventory, locator and so on, and leave the Tag Number blank to have it generated. Dynamic tag entry must be allowed and tag numbers must already have been generated.

Can I change which item or location an existing tag points to?
No. On an existing tag only the count, units of measure and void flag can change.

Who is recorded as having counted the tag?
The Counted By employee, defaulting to your own employee record.

Why can’t I see a particular physical inventory?
Only physical inventories in the organization whose adjustments are not yet posted are listed.

Troubleshooting

MessageCauseWhat to do
New Tags cannot be created in Update Mode / Existing Tags cannot be updated in Create ModeThe row’s action conflicts with the chosen Upload Mode.Set the Upload Mode that matches your rows, or the mode that allows both.
Dynamic Tag Number creation is not enabled for this Physical InventoryTrying to create a tag where dynamic tag entry is off.Enable dynamic tag entry for the physical inventory, or only update existing tags.
Matching Tag exists alreadyA tag with the same item, location, lot and serial already exists.Update that existing tag instead of creating a duplicate.
Tag details cannot be changedAn identifying field was changed on an existing tag.Restore the original item and location values; change only the count.
Tag UOM and Quantity are requiredA non-void tag has no count quantity or unit of measure.Enter the UOM and Quantity, unless the tag is being voided.
with mpit_qry as
(
select /*+ INLINE */
 -- Physical Inventory
 mp.organization_code,
 mpiv.physical_inventory_name,
 mpiv.description,
 mpiv.physical_inventory_date,
 mpiv.snapshot_complete,
 mpiv.freeze_date snapshot_date,
 xxen_util.meaning(mpiv.adjustments_posted,'INV_YES_NO',3) adjustments_posted,
 mpiv.last_adjustment_date,
 mpiv.total_adjustment_value,
 xxen_util.meaning(mpiv.approval_required,'INV_CC_APPROVAL',700) approval_required,
 mpiv.approval_tolerance_pos quantity_tolerance_plus,
 mpiv.approval_tolerance_neg quantity_tolerance_minus,
 mpiv.cost_variance_pos value_tolerance_plus,
 mpiv.cost_variance_neg value_tolerance_minus,
 xxen_util.meaning(mpiv.all_subinventories_flag,'INV_YES_NO',3) all_subinventories,
 (select listagg(mps.subinventory,',') within group (order by mps.subinventory)
  from mtl_physical_subinventories mps
  where mps.physical_inventory_id = mpiv.physical_inventory_id
  and   mps.organization_Id = mpiv.organization_id
 ) subinventories,
 xxen_util.meaning(mpiv.exclude_zero_balance,'INV_YES_NO',3) exclude_zero_balance,
 xxen_util.meaning(mpiv.exclude_negative_balance,'INV_YES_NO',3) exclude_negative_balance,
 xxen_util.meaning(mpiv.dynamic_tag_entry_flag,'INV_YES_NO',3) dynamic_tag_entry_flag,
 mpiv.next_tag_number,
 mpiv.tag_number_increments,
 mpiv.number_of_skus,
 (select gcck.concatenated_segments
  from gl_code_combinations_kfv gcck
  where gcck.code_combination_id = mpiv.default_gl_adjust_account
 ) default_adjustment_account,
 -- Tags
 mpit.tag_number,
 msiv.concatenated_segments item,
 mpit.revision,
 msiv.description item_description,
 xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type,
 msiv.item_type item_type_code,
 msiv.primary_uom_code item_primary_uom,
 msiv.secondary_uom_code item_secondary_uom,
 mck.concatenated_segments item_category,
 mpit.subinventory,
 milk.concatenated_segments locator,
 mpit.lot_number,
 mpit.lot_expiration_date,
 mpit.serial_num serial_number,
 inv_invarptp_xmlp_pkg.cf_parent_lpnformula(mpit.parent_lpn_id) parent_lpn,
 inv_invarptp_xmlp_pkg.cf_outermost_lpnformula(mpit.outermost_lpn_id,mpit.parent_lpn_id) outermost_lpn,
 --
 mpit.tag_uom uom,
 mpit.tag_quantity quantity,
 mpit.tag_secondary_uom secondary_uom,
 mpit.tag_secondary_quantity secondary_quantity,
 (select papf.full_name
  from   per_all_people_f papf
  where  papf.person_id = mpit.counted_by_employee_id
  and    sysdate between papf.effective_start_date and papf.effective_end_date
 ) last_counted_by,
 xxen_util.meaning(decode(mpit.void_flag,1,mpit.void_flag,null),'INV_YES_NO',3) void,
 --
 mpa.system_quantity,
 mpa.secondary_system_qty secondary_system_quantity,
 --
 inv_invarptp_xmlp_pkg.cf_cost_groupformula(mpit.cost_group_id)cost_group_name,
 --
 mpit.attribute_category,
 mpit.attribute1,
 mpit.attribute2,
 mpit.attribute3,
 mpit.attribute4,
 mpit.attribute5,
 mpit.attribute6,
 mpit.attribute7,
 mpit.attribute8,
 mpit.attribute9,
 mpit.attribute10,
 mpit.attribute11,
 mpit.attribute12,
 mpit.attribute13,
 mpit.attribute14,
 mpit.attribute15,
 mpiv.physical_inventory_id,
 mpit.tag_id,
 mpit.last_update_date,
 mpit.last_updated_by
from
 mtl_physical_inventories_v mpiv,
 mtl_physical_inventory_tags mpit,
 mtl_physical_adjustments mpa,
 mtl_parameters mp,
 mtl_item_locations_kfv milk,
 mtl_system_items_vl msiv,
 mtl_item_categories mic,
 mtl_categories_kfv mck
where
 mpiv.physical_inventory_id = mpit.physical_inventory_id and
 mpiv.organization_id = mpit.organization_id and
 mpit.adjustment_id = mpa.adjustment_id (+) and
 mpiv.organization_id = mp.organization_id and
 mpit.organization_id = milk.organization_id (+) and
 mpit.locator_id = milk.inventory_location_id (+) and
 mpit.organization_id = msiv.organization_id (+) and
 mpit.inventory_item_id = msiv.inventory_item_id (+) and
 mpit.organization_id = mic.organization_id (+) and
 mpit.inventory_item_id = mic.inventory_item_id (+) and
 :p_category_set_id = mic.category_set_id (+) and
 mic.category_id = mck.category_id (+) and
 -- from INVADPPI (Physical Inventory Tag Counts) form
 inv_material_status_grp.is_status_applicable( null, null,8,null,null,mpit.organization_id,mpit.inventory_item_id,mpit.subinventory,mpit.locator_id,mpit.lot_number,mpit.serial_num,'A') = 'Y' and
 mpiv.adjustments_posted = 2 and
 -- Exclude Serial Controlled Items in Physical Inventories defined for Multiple Serial Numbers Requests per Tag.
 -- We are not handling this scenario as yet
 &lp_multiple_sn_clause
 --
 not exists
 (select
   null
  from
   mtl_physical_adjustments mpa
  where
   mpit.organization_id = mpa.organization_id and
   mpit.physical_inventory_id= mpa.physical_inventory_id and
   mpit.adjustment_id = mpa.adjustment_id and
   mpit.inventory_item_id = mpa.inventory_item_id and
   nvl(mpa.approval_status,0) = 3 -- processed
 )
)
--
-- Main Query Starts Here
--
select /*+ push_pred(mpit) */
null action_,
null status_,
null message_,
null request_id_,
null modified_columns_,
to_date(null) timestamp_,
null row_id,
:p_upload_mode upload_mode,
mpit.organization_code,
mpit.physical_inventory_name,
-- Tags
mpit.tag_number,
mpit.item,
mpit.revision,
mpit.item_description,
mpit.item_type,
mpit.item_category,
mpit.item_primary_uom,
mpit.item_secondary_uom,
mpit.subinventory,
mpit.locator,
mpit.lot_number,
mpit.lot_expiration_date,
mpit.serial_number,
mpit.parent_lpn,
mpit.outermost_lpn,
--
nvl(mpit.uom,mpit.item_primary_uom) uom,
mpit.quantity,
mpit.secondary_uom,
mpit.secondary_quantity,
mpit.last_counted_by,
:p_counter_emp_name counted_by,
mpit.void,
--
mpit.system_quantity,
mpit.secondary_system_quantity,
--
mpit.cost_group_name,
--
mpit.attribute_category,
mpit.attribute1,
mpit.attribute2,
mpit.attribute3,
mpit.attribute4,
mpit.attribute5,
mpit.attribute6,
mpit.attribute7,
mpit.attribute8,
mpit.attribute9,
mpit.attribute10,
mpit.attribute11,
mpit.attribute12,
mpit.attribute13,
mpit.attribute14,
mpit.attribute15,
-- ids
mpit.tag_id
from
mpit_qry mpit
where
mpit.organization_code = :p_organization_code  and
mpit.physical_inventory_name = :p_physical_inv_name and
1=1
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode like '%' || xxen_upload.action_update
LOV
Organization Code
 
LOV
Physical Inventory
 
LOV
Counted By
 
LOV
Subinventory
mpit.subinventory=:p_subinventory
LOV
Category Set
:p_category_set is not null
LOV
Category From
mpit.item_category >= :p_category_from
LOV
Category To
mpit.item_category <= :p_category_to
LOV
Item
mpit.item = :p_item
LOV
Item From
mpit.item >= :p_item_from
LOV
Item To
mpit.item <= :p_item_to
LOV
Item Type
mpit.item_type_code=xxen_util.lookup_code(:p_item_type,'ITEM_TYPE',3)
LOV
Locator
mpit.locator = :p_locator
LOV
Locator From
mpit.locator >= :p_locator_from
LOV
Locator To
mpit.locator <= :p_locator_to
LOV
Tag Number From
mpit.tag_number >= :p_tag_num_from
LOV
Tag Number To
mpit.tag_number <= :p_tag_num_to
LOV
Void Status
mpit.void is not null
LOV Oracle
Download
Related Blitz Reports
GL Account Distribution Analysis TB GL Account Analysis (Drilldown)v2 SAM: GL Account Analysis (Drilldown) (with inventory and WIP) GL Account Analysis (Drilldown) (with inventory and WIP) COPY OF: TB GL Account Analysis (Drilldown)v3 TB GL Account Analysis (Drilldown)v3 GL Account Analysis INV Item Upload INV Items
Blitz Report™