How Physical Inventory 11i Works

An Oracle Technical White Paper

June 2006
Revision 1

1
 How Physical Inventory 11i Works

INTRODUCTION

Oracle Inventory provides several tools to aid users in controlling and improving the
accuracy of inventory records.

a. ABC Analysis
b. Cycle Counting
c. Physical Inventory

This white paper will give an overview of Physical Inventory, various steps involved,
associated tables, forms, packages, concurrent programs and report.

EXECUTIVE SUMMARY

Physical Inventory is a periodic reconcilation of system on-hand balances with physical

counts in inventory. Performed to verify the accuracy of system on-hand quantities, for
the entire organization or for particular subinventories within an organization. Physical
Inventory is a process designed to count all the inventory of all items at one time.
Adjustments are then reviewed and posted to the general ledger. Performed to ensure
accuracy for tax, audit, and effective planning purposes. Primary purpose is to verify the
financial value of the inventory for financial auditors, and to verify the item detail for
planners.

Physical Inventory – performed periodically (also known as Periodic (annual) Inventory)

Cycle Counting – performed every period (ABC Analysis is a method of Cycle Counting)

INITIAL PREPARARION FOR PHYSICAL INVENTORY STEPS

1. Housekeeping – Keep inventory presorted into homogenous groups for counting ease
2. Identification – Parts are clearly identified and tagged with Part Numbers
3. Training – Instruct floor staff

2
PHYSICAL INVENTORY PROCESS

1. Define physical inventory – assign a name(s) to specific subinventory(ies) portions

(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-3)

2. Take snapshots of on-hand quantities – how much does Oracle say that you have ?
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-5)

3a. Generate tags – slips of paper to put with specific subinventory groups
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-7)

Tags are alphanumericially-labeled slips of paper posted at an item’s location to record

on-hand quantity at that location. Tags are created by either by Oracle auto-generated or
manually entered. Oracle Physical Inventory is used to reconcile system-maintained
items on-hand balances with actual counts of inventory.
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-7)

3b. Physically Printing the Tags

4. Count items – Count items, record count on the tag, verify by recounting or sampling

5. Enter counts – input total number of items from each tag for each group into system
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-11)

6. Void tags – complete for all groups and check off as completed
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-11)

7. Approve counts – compare and reconcile tag counts against system totals
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-15)

8. Run adjustments – investigate major discrepencies before authorizing adjustments

(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-18)

9. Purge physical inventory – new, accurate totals reflected in system, posted to GL

(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-20)

3
RESOURCES USED

• ARU (Automated Release Updates) Patches derived from Specific searches by

each module (form, concurrent process, report, java file) by each 11i version
(11.5.1 through 11.5.10; but including non-versions 11.5.0, and 11.5 for
completeness)

• MetaLink Doc IDs derived from: 1) Specific searches by each module (form,
concurrent process, report, java file) by each 11i version (11.5.1 through 11.5.10;
but including non-versions 11.5.0, and 11.5 for completeness); and also 2)
General searches on ‘physical inventory’.

• Web IV Bugs, Notes, and Technical Bullitens derived from: 1) Specific searches
by each module (form, concurrent process, report, java file) by each 11i version
(11.5.1 through 11.5.10; but including non-versions 11.5.0, and 11.5 for
completeness); and also 2) General searches on ‘physical inventory’

• TAR Database Find Page APPS / CRM TARs derived from: 1) Specific searches
by each module (form, concurrent process, report, java file) by each 11i version
(11.5.1 through 11.5.10; but including non-versions 11.5.0, and 11.5 for
completeness); and also 2) General searches on ‘physical inventory’

• TAR Database Find Page Mailing List Archives email (including Development
Meeting Minutes emails and attachments) and their corresponding attachments
derived from: 1) Specific searches by each module (form, concurrent process,
report, java file) by each 11i version (11.5.1 through 11.5.10; but including non-
versions 11.5.0, and 11.5 for completeness); and also 2) General searches on
‘physical inventory’

• Secondary search of all current and archived email and their corresponding
attachments derived from General searches on ‘physical inventory’

• Secondary search on personal file system of all Phyical Inventory TARs

personally worked and captured in personal file system archive.

• AOL Instant Messenger logs of all dialog captured pertaining to Physical

Inventory.

• Reproduction of all Physical Inventory functionality on internal Oracle 11i

application via test cases and free form in conjunction with “Oracle Inventory
User’s Guide” and “Understanding Physical Inventory”, (May 2003), An Oracle
White Paper.

• “Oracle Inventory User’s Guide Release 11i”, (September 2002), Oracle

Corporation, Chapter 13.

4
 • “Oracle Inventory User’s Guide Release 11”, (March 1998), Oracle Corporation,
Chapter 13.

• “Oracle Inventory Technical Reference Manual”, (December 1999), Oracle

Corporation.

• “R11i Implement and Use Oracle Inventory student guide”, (2002), Oracle
University, pages 1-80, 3-106; 11-1 through 11-82

• “Understanding Physical Inventory”, (May 2003), An Oracle White Paper

• Arnold, J.R. Tony, and Stephen N. Chapman, “Introduction to Materials

Management”, (2004), Pearson Prentice Hall, page 324.

SCOPE

• This Technical White Paper pertains to Oracle Inventory (Product 508 within the
Manufacturing suite of products) in Oracle Applications 11i only; previous
versions of Oracle Applications, including 11.0.3, 10.7, 10.7SC are out of scope
of this document.

• The following report modules pertain to Oracle Assets (Product 503 within Oracle
Financials suite of products) and are out of scope of this document:

FARXPIC - RXi: Physical Inventory Comparison Report

FARXPIM - RXi: Physical Inventory Missing Assets Report
RXFAPIC - RX-only: Physical Inventory Comparison Report
RXFAPIM - RX-only: Physical Inventory Missing Assets Report

• This Technical White paper pertains to Discrete Inventory; OPM (Product 733
within Oracle Process Manufacturing) is out of scope of this document. The
following modules pertain to OPM (Product 733 within Oracle Process
Manufacturing) and and are out of scope of this document:

PIR041 - Physical Count Entries

PIR051 - Physical Count Forms
PIDEFED - Physical Inventory
PIPCNTED - Physical Count Entry
FAXPIDEF - Enter Physical Inventory

5
FORMS USED BY PHYSICAL INVENTORY

INVADPAP - Approve Physical Inventory Adjustments

INVADPPI - Define Physical Inventory
INVADPTE - Enter Physical Inventory Counts
INVADPVT - Void Physical Inventory Tags
INVARPGT - Generate Physical Inventory Tags

CONCURRENT PROCESSES USED BY PHYSICAL INVENTORY

INCAPA - Perform physical inventory Adjustments

INCAPF - Freeze physical inventory
INCAPP - Purge physical inventory information
INCAPT - Generate physical inventory tags

REPORTS USED BY PHYSICAL INVENTORY

INVARPAR - Physical inventory adjustments report

INVARPIA - Physical inventory accuracy analysis
INVARPMT - Physical inventory missing tag listing
INVARPSL - Physical inventory counts report
INVARPTG - Physical inventory tag listing
INVARPTP - Physical Inventory Tags
INVARPTS - Physical inventory trend and summary analysis

JAVA FILES USED BY PHYSICAL INVENTORY

PhyInvButton.java
PhyInvCreateLPNFListener.java
PhyInvCreateLPNPage.java
PhyInvFListener.java
PhyInvFunction.java
PhyInvPage.java
(all java files reside at java/count/server/ )

6
ERROR MESSAGES GENERATED BY PHYSICAL INVENTORY

APP-INV-05003: You cannot use this form to delete a physical inventory

APP-INV-05109: You must select a physical inventory before continuing
APP-INV-05135: Prior physical inventory snapshot failed or incomplete
APP-INV-05168: You cannot dynamically create tags for this physical inventory
APP-INV-05189: You cannot delete a tag -- void it instead Action: Go to Void Physical
Inventory Tags form and void tags.
APP-INV-05288: You must select a physical inventory before continuing
APP-INV-05289: You must choose a physical inventory name
APP-INV-05433: Dynamic tag entry is not enabled for this physical inventory
APP-INV-05473: The physical inventory you selected does not require approvals
APP-INV-05499: This physical inventory generated adjustments and cannot be updated
APP-INV-05503: Purge tags only (alternatively, purge the entire physical inventory)?
APP-INV-05565: You cannot generate tags for this physical inventory
APP-BOM-20441: Physical Inventory Adjustments exist for this item
(MTL_PHYSICAL_ADJUSTMENTS)
APP-BOM-20444: Physical Inventory Tags exist for this item
(MTL_PHYSICAL_INVENTORY_TAGS)

DATABASE TABLES DIRECTLY AFFECTED BY PHYSICAL INVENTORY

MTL_PHYSICAL_INVENTORIES
MTL_PHYSICAL_SUBINVENTORIES
MTL_PHYSICAL_INVENTORY_TAGS
MTL_PHYSICAL_ADJUSTMENTS
MTL_MATERIAL_TRANSACTIONS

PHYSICAL INVENTORY DATABASE PACKAGE MODULES

INVPINLS.pls
INVPINLB.pls
INVITPSS.pls
INVITPSB.pls
INVADPTB.pls
INVADPTS.pls
INVCGUGB.pls
INVCGUGS.pls
INVITMLS.pls
INVITMLB.pls
INVINVLS.pls
INVINVLB.pls
INVITATS.pls
INVITATB.pls

7
PHYSICAL INVENTORY DATABASE PACKAGES

INV_PHY_INV_LOVS
INV_UI_ITEM_SUB_LOC_LOVS
INVADPT1
INV_CG_UPGRADE
INV_UI_ITEM_LOVS
INV_INV_LOVS
INV_UI_ITEM_ATT_LOVS

8
PHYSICAL INVENTORY PROCESS DETAILS

1. Define physical inventory – assign a name(s) to specific subinventory(ies) portions

(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-3)

Business:
The context, scope of specific subinventory, business rules, of the Physical Inventory are
defined is named.

Functional:
Inventory > Counting > Physical Inventory

Inventory > Counting > Physical Inventory > Physical Inventories

/u01/applmgr/apsappl/inv/11.5.0/forms/US/INVADPPI.fmx

Click on the New button

9
Inventory > Counting > Physical Inventory > Physical Inventories > New
/u01/applmgr/apsappl/inv/11.5.0/forms/US/INVADPPI.fmx

Enter a unique physical inventory name in the name field.

Enter a description in the description field (optional).

Functional and Technical:

Name field in the GUI corresponds to
MTL_PHYSICAL_INVENTORIES.PHYSICAL_INVENTORY_NAME

Description field in the GUI corresponds to

MTL_PHYSICAL_INVENTORIES.DESCRIPTION

Date filed in the GUI corresponds to

MTL_PHYSICAL_INVENTORIES.PHYSICAL_INVENTORY_DATE

Primary Key MTL_PHYSICAL_INVENTORIES.PHYSICAL_INVENTORY_ID is

automatically generated set based on SEQuence built into the source code which returns
NEXTVAL based on sequence.

10
Primary Key MTL_PHYSICAL_INVENTORIES.ORGANIZATION_ID was determined
when selecting the organization in the application:

11
Functional:
Select approval requirements for the adjustments:

Available options in the List Of Values are:

Always:
Business: Requires approval for all Physical Inventory adjustments. Adjustment program
canot be run until all the adjustments are approved.

Functional: This functionality will cause the Qty% + / - and Value + / - fields to be
greyed out.

Technical:
MTL_PHYSICAL_INVENTORIES.APPROVAL_REQUIRED is set to 1

If out of tolerance:
Business: Quantity and / or Value adjustment tolerances can be entered. Adjustments
exceeding tolerances must be approved before running the adjustment program.
Adjustment value = (Actual count – System count) x Item cost

Functional: This functionality allows the Qty% + / - and Value + / - fields to be enterable.

Technical:
MTL_PHYSICAL_INVENTORIES.APPROVAL_REQUIRED is set to 3

Never:
Business: Allow all adjustments to post without approval

Functional: This functionality will cause the Qty% + / - and Value + / - fields to be
greyed out.

Technical:
MTL_PHYSICAL_INVENTORIES.APPROVAL_REQUIRED is set to 2

12
Functional:
If selecting If out of tolerance approval option, then enter positive and negative approval
tolerances.

Tolerances are set by:

Quantity:
Business: Denotes an acceptable +/ - percentage limit between actual counted quantity
versus system on-hand quantity stored in the application before approval is required.

Functional: Expressed in numeric quantities. Application will force + and – to match.

Technical: (example)
MTL_PHYSICAL_INVENTORIES.APPROVAL_TOLERANCE_POS is set to 2
MTL_PHYSICAL_INVENTORIES.APPROVAL_TOLERANCE_NEG is set to 2

Value:
Business: Denotes an acceptable + / - total value difference between actual count an
system count.

Funcational: Expressed in currency amounts. Application will force + and – to match.

Technical: (example)
MTL_PHYSICAL_INVENTORIES.COST_VARIANCE_POS is set to 10000
MTL_PHYSICAL_INVENTORIES.COST_VARIANCE_POS is set to 10000

13
Functional:
Select the scope of the Physical Inventory

Available options between the radio buttons are:

All:
Business: Used to perform Physical Inventory on all Subinventories for the Organization.

Functional: This functionality will cause the Specific fields to be greyed out.

Technical:
MTL_PHYSICAL_INVENTORIES.ALL_SUBINVENTORIES_FLAG is set to 1

Specific:
Business: Used to denote specific Subinventories within the Organization for performing
Physical Inventory.

Functional: This functionality allows the Specific fields to be selected from the List Of
Values.

Technical:
MTL_PHYSICAL_INVENTORIES.ALL_SUBINVENTORIES_FLAG is set to 2

Primary Key MTL_PHYSICAL_SUBINVENTORIES.SUBINVENTORY is set to the

varchar2 value determined when specifying the subinventory in the application:

14
Technical:
Primary Key MTL_PHYSICAL_SUBINVENTORIES.PHYSICAL_INVENTORY_ID is
set to match existing value in
Primary Key MTL_PHYSICAL_INVENTORIES.PHYSICAL_INVENTORY_ID to
form future equijoin.

Primary Key MTL_PHYSICAL_SUBINVENTORIES.ORGANIZATION_ID is set to

match existing value in
Primary Key MTL_PHYSICAL_INVENTORIES.ORGANIZATION_ID to form future
equijoin.

15
Functional:
Indicate whether to allow dynamic entry of tags.

Business: Tags are alphanumericially-labeled slips of paper posted at an item’s location

to record on-hand quantity at that location. Tags are created by either by Oracle auto-
generated or manually entered. Oracle Physical Inventory is used to reconcile system-
maintained items on-hand balances with actual counts of inventory. A tag is a tool for
recording the on-hand quantity of a specific item in a particular location. A tag is usually
a piece of pater posted at the location of the item.
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-7)

Business and Functional: Checked - Used for third-party pre-numbered Tag generation.

Technical:
MTL_PHYSICAL_INVENTORIES.DYNAMIC_TAG_ENTRY_FLAG is set to 1

Business and Functional: Unchecked – All tags must be Oracle automatically generated
or manually entered before use.

Technical:
MTL_PHYSICAL_INVENTORIES.DYNAMIC_TAG_ENTRY_FLAG is set to 2

Functional:
Select Snapshot button

or select Tools > Perform snapshot

16
2. Take snapshots of on-hand quantities – how much does Oracle say that you have ?
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-5)

Business:
Snapshot must be completed before generating Tags. After taking the snapshot,
application will no longer be able to update header information of the physical inventory.

The snapshot saves all item on-hand quantities and costs and uses this information as the
basis for all physical inventory adjustments rather than the current on-hand quantity.
System allow to resume transaction activities even before running the adjustments
program for the physical inventory. Therefore need to procedurally coordinate snapshot
of physical inventory with actual counting and ensure that no transaction activity occurs
in a particular location until adjustments are launched and posted. Once snapshot
concurrent process INCAPF is finished, the snapshot complex box is check, the snapshot
date is updated and the Tags button is enabled in the define physical inventory window.

Functional:
Inventory > Counting > Physical Inventory > Physical Inventories > New > Snapshot
/u01/applmgr/apsappl/inv/11.5.0/forms/US/INVADPPI.fmx

or select Tools > Perform snapshot

Functional result:

17
View > Requests

18
Ensure that concurrent request has completed successfully

Technical:
Concurrent program INCAPF [Freeze Physical Inventory] is launched and performs the
following:

MTL_PHYSICAL_INVENTORIES.FREEZE_DATE is set to SYSDATE

INCAPF calculates the number of SKU and this is set in

MTL_PHYSICAL_INVENTORIES.NUMBER_OF_SKUS.

INCAPF inserts rows into MTL_PHYSICAL_ADJUSTMENTS equal to the calculation

from MTL_PHYSICAL_INVENTORIES.NUMBER_OF_SKUS (above) each with a
unique value.

Primary Key MTL_PHYSICAL_ADJUSTMENTS.ADJUSTMENT_ID based on

SEQuence built into the source code which returns NEXTVAL.

INCAPF derives On-Hand Quantity from

MTL_ONHAND_QUANTITIES.TRANSACTION_QUANTITY and populates
MTL_PHYSICAL_ADJUSTMENTS.SYSTEM_QUANTITY

19
Technical Example:
MTL_PHYSICAL_INVENTORIES.NUMBER_OF_SKUS is system calculated and set
at 14.

INCAPF inserts Fourteen (14) rows into

MTL_PHYSICAL_ADJUSTMENTS.ADJUSTMENT_ID

20
3a. Generate tags – slips of paper to put with specific subinventory groups
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-7)

Business: Tags are alphanumericially-labeled slips of paper posted at an item’s location

to record on-hand quantity at that location. Tags are either created by Oracle auto-
generated or manually entered. Oracle Physical Inventory is used to reconcile system-
maintained items on-hand balances with actual counts of inventory. A tag is a tool for
recording the on-hand quantity of a specific item in a particular location. A tag is usually
a piece of pater posted at the location of the item. Physical Inventory Tags represent
actual hard copy counts for a group of a given item.
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-7)

There are three options when Generating Tags:

1. Dynamic Tag Entry
2. Default Tags
3. Blank Tags

Dynamic Tag Entry:

Business: Used for third-party pre-numbered Tag generation or when Tags are are
generated outside the system.

Functional: Checked - You do not have to run the Tag Generation program. Use your
own tag numbers when you enter the counts.

Technical:
MTL_PHYSICAL_INVENTORIES.DYNAMIC_TAG_FLAG is set to 1

21
Default Tags and Blank Tags:
Business: Used to have Tags automatically generated by Oracle.

Functional: Unchecked - All tags must be Oracle automatically generated or manually

entered before use.

Technical:
MTL_PHYSICAL_INVENTORIES.DYNAMIC_TAG_FLAG is set to 2

Functional:
Inventory > Counting > Physical Inventory > Tag Generation

Inventory > Counting > Physical Inventory > Tag Generation

/u01/applmgr/apsappl/inv/11.5.0/forms/US/INVARPGT.fmx

Select the name of the Physical Inventory from the List of Values.
Select Tag Type to create from the radio buttons

22
Technical:
Query is performed for all available physical inventory names which returns values from
MTL_PHYSICAL_INVENTORIES.PHYSICAL_INVENTORY_NAME which is
populated in the List of Values which the user selects in the GUI Application.

When user selects from List of Values, then corresponding

Primary Key MTL_PHYSICAL_INVENTORIES.PHYSICAL_INVENTORY_ID
populates
Foreign Key MTL_PHYSICAL_INVENTORY_TAGS.PHYSICAL_INVENTORY_ID
thus forming an equijoin between
MTL_PHYSICAL_INVENTORIES and
MTL_PHYSICAL_INVENTORY_TAGS
and populating the GUI Description field per value residing in
MTL_PHYSICAL_INVENTORIES.DESCRIPTION
and also populates the GUI Snapshot Date field per value residing in
MTL.PHYSICAL_INVENTORIES.FREEZE_DATE

23
Blank Tag Entry:
Business: Used to enter counts for those items whose locations are not currently recorded
in the system. Used to retain empty Tags to record counts for stock-keeping units for
which Inventory has no on-hand quantity. These Tags do not contain any information
about the items or location in physical inventory definition. This information can be
specified when entering the Tag counts. Allows user to specify a starting Tag number, the
increment for each digit in the Tag numbers and an ending Tag number or the total
number of Tags.

Functional:

Allow Dynamic Tags - Unchecked - All tags must be Oracle automatically generated or
manually entered before use.
Tag Type – Blank – Used to enter counts for those items whose locations are not
currently recorded in the system. Floor staff needs to fill out the item and location
information, in addition to the count quantity for each Tag or this information can be
specified when entering the Tag counts.

Show Serial on Tags – Checked – This option will display Serial Number on Tags

Show Serial on Tags – Unchecked – This option will not display Serial Number on Tags
User will have to match the serial numbers to the items manually.

Enter Starting Tag number in the Starting Tag field using zero (0) padding to the left of
the start number if necessary to accommidate all possible digits.

Tag numbers may be alphanumeric, however only the numeric portion will be
incremented. Enter the amount by which each digit can increase:

Technical:
The incremental value will be stored in
MTL_PHYSICAL_INVENTORIES.TAG_NUMBER_INCREMENTS and
MTL_PHYSICAL_INVENTORY_TAGS_S sequence

24
Functional Example 1:
In this example, each numeric digit can increase by one.

Functional Example 2:
In this example, each numeric digit can increase by one, alpha digit remain constant.

Functional Example 3:
In this example, each numeric digit can increase by one as long as they remain ending in
an even number (in which the last digit increments by two).

Enter the Ending Tag number:

The Number of Tags is automatically populated.

Functional Example 4:
In this example, the user selected a Starting Tag of 000000, only even tags, with an
Ending Tag of 200000. The Application calculated that 100001 Tags will be generated.

Functional Example 5:
In this example, the user selected a Starting Tag of 000000, only even tags, with a request
to only generate 100 Tags. The Application calculated Ending Tag number of 000198.

Blank Tag Entry allows the user to specify either the Ending Tag or the Number of Tags.

25
Default Tag Entry:
Business: Used to generate one tag per SKU, or unique combination of item,
subinventory, locator, revision, lot number and serial number that exists in the
subinventories of physical inventory. Allows user to specific a starting number and the
increment for each digit of the Tags.

Functional:

Allow Dynamic Tags - Unchecked - All tags must be Oracle automatically generated or
manually entered before use.
Tag Type – Default – Tags contain information on the items in the physical inventory
definition. Floor staff only needs to enter the quantity counted for each Tag.

Show Serial on Tags – Checked – This option will display Serial Number on Tags

Show Serial on Tags – Unchecked – This option will not display Serial Number on Tags
User will have to match the serial numbers to the items manually.

Enter Starting Tag number in the Starting Tag field

Tag numbers may be alphanumeric, however only the numeric portion will be
incremented.

Enter the amount by which each digit can increase:

Technical:
The incremental value will be stored in
MTL_PHYSICAL_INVENTORIES.TAG_NUMBER_INCREMENTS and
MTL_PHYSICAL_INVENTORY_TAGS_S sequence

26
Functional Example 1:
In this example, each numeric digit can increase by one.

Functional Example 2:
In this example, each numeric digit can increase by one, alpha digit remain constant.

Functional Example 3:
In this example, each numeric digit can increase by one as long as they remain ending in
an even number (in which the last digit increments by two).

Functional Example 4:
In this example, the user selected a Starting Tag of 000001. This Physical Inventory
Name (selected from the List of Values in the Name field above) restricts the Number of
Tags generated to 77; the application calculated Ending Tag number of 000077.

Default Tag Entry does not allow the user to specify either the Ending Tag or the Number
of Tags.

27
Business and Functional:
Choose the Generate button to create the Tag numbers

Pressing Generate does not physically print the tags.

Use the Physical Inventory Tags report to print tags that have been generated by
Inventory > Reports > ABC and Counting > Run Physical Inventory Tags report

Also see “Oracle Inventory User’s Guide Release 11i”, (September 2002), Oracle
Corporation, Physical Inventory Tags, page 14-91.

Functional result:

View > Requests

28
Ensure that concurrent request has completed successfully

29
Technical:
Concurrent process INCAPT [Generate Physical Inventory Tags] is launched and
performs the following:

Query performed to select ORGANIZATION_ID per equijoin between

Primary Key MTL_PHYSICAL_INVENTORIES.PHYSICAL_INVENTORY_ID and
Foreign Key MTL_PHYSICAL_INVENTORY_TAGS.PHYSICAL_INVENTORY_ID

MTL_PHYSICAL_INVENTORIES.NEXT_TAG_NUMBER and
MTL_PHYSICAL_INVENTORIES.TAG_NUMBER_INCREMENTS
are calculated per GUI selections above and updated.

Tag numbers are generated using the starting Tag Number

and the incremented using SEQuence MTL_PHYSICAL_INVENTORY_TAGS_S

Records corresponding to the Tag Numbers are inserted into

MTL_PHYSICAL_INVENTORY_TAGS with
Primary Key MTL_PHYSICAL_INVENTORY_TAGS.TAG_ID

MTL_PHYSICAL_INVENTORY_TAGS table is further populated by rows from

retrieved from equijoin
MTL_PHYSICAL_ADJUSTMENTS.PHYSICAL_INVENTORY_ID joined with
MTL_PHYSICAL_INVENTORY_TAGS. PHYSICAL_INVENTORY_ID

The same number of records are inserted into MTL_PHYSICAL_INVENTORIES

by equijoin
Primary Key MTL_PHYSICAL_INVENTORIES.PHYSICAL_INVENTORY_ID and
Primary Key MTL_PHYSICAL_INVENTORIES.ORGANIZATION_ID joined with
Foreign Key MTL_PHYSICAL_INVENTORY_TAGS. PHYSICAL_INVENTORY_ID
Foreign Key MTL_PHYSICAL_INVENTORY_TAGS. ORGANIZATION_ID

30
3b. Physically Printing the Tags:

Business and Functional:

Tags MUST be first generated before they can be printed.

Use the Physical Inventory Tags report to print tags that have been generated by
Inventory > Reports > ABC and Counting > Run Physical Inventory Tags report

Also see “Oracle Inventory User’s Guide Release 11i”, (September 2002), Oracle
Corporation, Physical Inventory Tags, page 14-91.

Functional:
Inventory > Reports > ABC and Counting

Select a Single Request

31
Inventory > Reports > ABC and Counting > Single Request
/u01/applmgr/apsappl/fnd/11.5.0/forms/US/FNDRSRUN.fmx

In the Name field, select the Physical inventory counts report from the List of Values.

32
Choose a Sort By parameter from the List of Values

Choose a Physical Inventory from the List of Values

33
Submit the request to physically print the Tags

Functional Result:

View > Requests

34
Ensure that concurrent request has completed successfully

Confirm that Physical Inventory Tags have successfully printed.

35
4. Count items – Count items, record count on the tag, verify by recounting or sampling

Business:
Floor staff uses Tags generated and printed to record physical counts.

Tags can be queried based on Tag Number, Item, Revision, Subinventory, Locator, Lot,
and Serial Number.

If Default Tags are used, then the Tags can be queried and counts entered. If Blank Tags
are used, then the Tag can be queried and the necessary Item, Revision, Subinventory,
Locator, Lot, Serial Number, and actual count quantity is entered. If Dynamic Tags are
used, then counts can be entered for any item without a pre-generated Tag number.

After Physical Inventory is counted and count quanties are recorded on each Tag (as well
as any other required information), then counts are entered into the Application.

36
5. Enter counts – input total number of items from each tag for each group into system
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-11)

Business:
After recording the physical counts on the Tags, the quanties on the Tags are entered into
the Application.

Functional:

Entering Tag Counts for Default Tags:

Business:
System will display the tags with relevant item information. User need to enter only the
quantity counted for each tag. User can also query a subset of tags by any combination of
tag number, item, revision, subinventory, locator, lot and serial number.

Functional:
Inventory > Counting > Physical Inventory > Tag Counts
/u01/applmgr/apsappl/inv/11.5.0/forms/US/INVADPTE.fmx

Select the Physical Inventory Name from the List of Values.

37
Press the Find Button

Determine whether to query all existing Tags or a subset

Functional Result:

38
Select the Default Counter from the List of Values.

Enter count information for each Tag

39
Detail tab displays additional information for reference which corresponds to each Tag:

Save your work

Entering Tag Counts for Blank or Dynamic Tags:

Business:
System will display the tags but no item information. User needs to enter the count
quantity as well as the item information (item name, subinventory, locator, revision, lot
and serial numbers). User can query by Tag number for the Blank Tags. User can enter
Tag numbers to query Dynamic Tags generated outside of the System.

40
6. Void tags – complete for all groups and check off as completed
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-11)

Business:
It is important for auditing purposes to track the status of each physical inventory tag.
Therefore if one or more Tags are not used, then they should be voided in the Physical
Inventory Counts window, therefore a voided Tag is not reported as a missing Tag in the
Physical Inventory Missing Tag Listing. If blank Tags were generated at the beginning of
Physical Inventory, but not all were used, then the unused portion should be voided. This
accounts for all the Tags that have been generated in the process. Tags that are lost,
damaged, or discards should be voided. All voided Tag will be adjusted by system to a
count quantity of zero.

Void tags that were deliberately disguarded during physical inventory.

Save your work

Technical:
Value entered into Qty field in INVADPTE form populates
MTL_PHYSICAL_INVENTORY_TAGS.TAG_QUANTITY

Value entered into UOM field in INVADPTE form populates

MTL_PHYSICAL_INVENTORY_TAGS.TAG_UOM

Value entered into Counted By field in INVADPTE form populates

MTL_PHYSICAL_INVENTORY_TAGS. COUNTED_BY_EMPLOYEE_ID

When Tag is voided MTL_PHYSICAL_INVENTORY_TAGS.TAG_QUANTITY is set

to NULL and MTL_PHYSICAL_INVENTORY_TAGS.VOID_FLAG is set to 1.

Value entered into Qty field in INVADPTE form also populates

MTL_PHYSICAL_ADJUSTMENTS.COUNT_QUANTITY

Value derived from calculuation of

MTL_PHYSICAL_ADJUSTMENTS.COUNT_QUANTITY
minus
MTL_PHYSICAL_ADJUSTMENTS. SYSTEM_QUANTITY
is used to populate
MTL_PHYSICAL_ADJUSTMENTS.ADJUSTMENT_QUANTITY

41
7. Approve counts – compare and reconcile tag counts against system totals
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-15)

Business:
User must either approve or reject all adjustments of the physical innentory before
running adjustment program INCAPA. Physical Inventory adjustments pending approval
can be viewed, rejected, or approved. The Process Physical Inventory Adjustments
program INCAPA in Step 8 adjusts inventory balances by the new quantity entered in the
Tag Counts form in Step 5. If the adjustment is rejected, then Oracle Physical Inventory
does not change the system on-hand quantity. When counts are approved or rejected and
work is saved, flags are set for processing.

Functional:
Inventory > Counting > Physical Inventory > Approve Adjustments
/u01/applmgr/apsappl/inv/11.5.0/forms/US/ INVADPAP.fmx

Select the physical inventory and choose approve

42
Enter the name of the employee approving the adjustments

Select an adjustment or reject all adjustments of the physical inventory before running
adjustment program

Save your work

43
Technical:
MTL_PHYSICAL_ADJUSTMENTS

If Physical Adjustments are approved, then

MTL_PHYSICAL_ADJUSTMENTS.APPROVAL_STATUS is set to 1
and value entered into Name field in INADPAP form populates
MTL_PHYSICAL_ADJUSTMENTS.APPROVED_BY_EMPLOYEE_ID

If Physical Adjustments are rejected, then

MTL_PHYSICAL_ADJUSTMENTS.APPROVAL_STATUS is set to 2
and value entered into Name field in INADPAP form populates
MTL_PHYSICAL_ADJUSTMENTS.APPROVED_BY_EMPLOYEE_ID

If Physical Adjustments posted but not yet approved or rejected, then

MTL_PHYSICAL_ADJUSTMENTS.APPROVAL_STATUS is set to 3
and value entered into Name field in INADPAP form populates
MTL_PHYSICAL_ADJUSTMENTS.APPROVED_BY_EMPLOYEE_ID

44
8. Run adjustments – investigate major discrepencies before authorizing adjustments
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-18)

Business:
Run adjustments automatically creats a material transaction in which adjustments are
posted which change on-hand quantity balances and adjustment account specified. If the
count of an item matches the system quantity, then no adjustment transaction is posted.

Once the adjustment program is completed it does not allow new tag generation and users
wlll no longer will be able to make changes to the physical inventory. No adjustment will
be posted if any adjustment is still pending approval. All adjustments must be approved
or rejected before adjustment can be processed.

Functional:
Inventory > Counting > Physical Inventory >

Inventory > Counting > Physical Inventory

/u01/applmgr/apsappl/inv/11.5.0/forms/US/INVADPPI.fmx

45
The status of this Physical Inventory is shown as the Snapshot is Complete, but the
Adjustments have not yet been ran and posted.

To Launch Adjustments, from the menu select Tools > Launch adjustments

46
Select Adjustment Account and Adjustment Date

Click Launch Adjustments button

Concurrent request number is returned

Go to Inventory > Requests

Confirm that request completed successfully.

Technical:
INCAPA processes records which are designed by the flags that were set in the
proceeding step.

Concurrent program INCAPA [Perform Physical Inventory Adjustments] is launched

and performs the following:

47
INCAPA initially checks for any adjustment records in either pending approval or
rejection status. Existance of any of these types or records will prevent adjustment from
posting.

INCAPA gathers data derived in fields from INVADPAP form,which is used to populate
MTL_PHYSICAL_INVENTORIES.LAST_ADJUSTMENT_DATE
MTL_PHYSICAL_INVENTORIES.TOTAL_ADJUSTMENT_VALUE

INCAPA gathers data derived in fields from INVADPAP form,which is used to populate
MTL_PHYSICAL_ADJUSTMENTS.GL_ADJUST_ACCOUNT
MTL_PHYSICAL_ADJUSTMENTS.APPROVAL_STATUS is set to 3

Primary Key MTL_PHYSICAL_ADJUSTMENTS.ADJUSTMENT_ID

is used to populate corresponding values in
Foreign Key MTL_MATERIAL_TRANSACTIONS.PHYSICAL_ADJUSTMENT_ID

MTL_PHYSICAL_ADJUSTMENTS.GL_ADJUST_ACCOUNT
is used to populate corresponding values in
MTL_MATERIAL_TRANSACTIONS.DISTRIBUTION_ACCOUNT_ID

INCAPA gathers value derived from calculuation of

[MTL_PHYSICAL_ADJUSTMENTS.COUNT_QUANTITY
minus
MTL_PHYSICAL_ADJUSTMENTS. SYSTEM_QUANTITY
which is used to populate
MTL_PHYSICAL_ADJUSTMENTS.ADJUSTMENT_QUANTITY]
and using this value, inserts corresponding rows into
MTL_MATERIAL_TRANSACTIONS_TEMP

Primary Key
MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_HEADER_ID is
based on
SEQuence built into the source code which returns NEXTVAL.

Adjustment transaction records are inserted into

MTL_MATERIAL_TRANSACTIONS_TEMP

INCAPA gathers all approved records from MTL_PHYSICAL_ADJUSTMENTS

WHERE ADJUSTMENT_QUANTITY != 0

INCAPA gathers MTL_PHYSICAL_INVENTORIES.DESCRIPTION

which is used to populate
MTL_MATERIAL_TRANSACTIONS_TEMP

48
INCAPA gathers
MTL_PHYSICAL_INVENTORIES.APPROVAL_TOLERANCE_POS
MTL_PHYSICAL_INVENTORIES.APPROVAL_TOLERANCE_NEG
which is used to populate
MTL_MATERIAL_TRANSACTIONS_TEMP

INCAPA gathers lot-controlled and serial-controlled items which are used to populate
MTL_TRANSACTION_LOT_NUMBERS
MTL_SERIAL_NUMBERS_TEMP

INCAPA gathers period_close_id for the adjustment date and confirms that the period is
open.

49
9. Purge physical inventory – new, accurate totals reflected in system, posted to GL
(Refer to “Oracle Inventory User’s Guide Release 11i”, page 13-20)

Business:
Purging physical inventory deletes the defination from the database, but does not affect
and physical inventory adjustmetns or transactions that have already been performed.

Functional:
Inventory > Counting > Physical Inventory >

Inventory > Counting > Physical Inventory

/u01/applmgr/apsappl/inv/11.5.0/forms/US/INVADPPI.fmx

The status of this Physical Inventory is shown as the Snapshot is Complete and
Adjustments have been ran and posted.

50
To Perform Purge, from the menu select Tools > Perform purge

Select the scope of the purge and click the Purge button

/u01/applmgr/apsappl/inv/11.5.0/forms/US/INCAPP

51
Technical:
Concurrent program INCAPP [ Purge Physical Inventory Information ] is launched and
performs the following:

INCAPP gathers and validates org_id

Where MTL_PHYSICAL_ADJUSTMENTS.APPROVAL_STATUS is set to 3

then these records are posted for purge.

• If purge type is ‘All’, then records from these tables will be purged in the
following order:

o MTL_PHYSICAL_ADJUSTMENTS
o MTL_PHYSICAL_SUBINVENTORIES
o MTL_PHYSICAL_INVENTORY_TAGS
o MTL_PHYSICAL_INVENTORIES (these records will not be deleted if
adjustment transaction rows of that physical invetnory are found in
MTL_MATERIAL_TRANSACTIONS table).

• If purge type is ‘Tag Only’, then:

o Records from MTL_PHYSICAL_INVENTORY_TAGS table will be
purged
o Updates will be made to MTL_PHYSICAL_ADJUSTMENTS with count
and adjustment quantities.
o Tags will be reset in MTL_PHYSICAL_INVENTORIES table

52