Oracle Enterprise Resource Planning | ERP System: Inventory

Transactions under Resolution Recommended will not prevent the closing of a period but these transactions cannot be resolved after an accounting period is closed.

Below are the queries to check for the pending transactions.

--Unprocessed Material Transactions

SELECT COUNT(*)
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
PERIOD_NAME = 'PERIOD_NAME') AND
NVL(TRANSACTION_STATUS, 0) != 2

--Uncosted Material Transactions

SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
COUNT(*)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
PERIOD_NAME = 'PERIOD_NAME') AND
       COSTED_FLAG IS NOT NULL

--Pending WIP Costing Transactions

SELECT COUNT(*)
  FROM WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
PERIOD_NAME = 'PERIOD_NAME')

--Uncosted WSM Transactions

SELECT COUNT(*)
  FROM WSM_SPLIT_MERGE_TRANSACTIONS
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
PERIOD_NAME = 'PERIOD_NAME')

--Uncosted WSM Interface

SELECT COUNT(*)
  FROM WSM_SPLIT_MERGE_TXN_INTERFACE
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
PERIOD_NAME = 'PERIOD_NAME')

--Pending Receiving Transactions

SELECT COUNT(*)
  FROM RCV_TRANSACTIONS_INTERFACE
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
PERIOD_NAME = 'PERIOD_NAME') AND
       DESTINATION_TYPE_CODE = 'INVENTORY'

--Pending Material Transactions

SELECT COUNT(*)
   FROM MTL_TRANSACTIONS_INTERFACE
  WHERE ORGANIZATION_ID =
        (SELECT ORGANIZATION_ID
           FROM APPS.ORG_ORGANIZATION_DEFINITIONS
          WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
        TRANSACTION_DATE <
        (SELECT SCHEDULE_CLOSE_DATE + 1
           FROM APPS.ORG_ACCT_PERIODS
          WHERE ORGANIZATION_ID =
                (SELECT ORGANIZATION_ID
                   FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                  WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
PERIOD_NAME = 'PERIOD_NAME') AND
PROCESS_FLAG != 9

--Pending Shop Floor Move Transactions

SELECT COUNT(*)
  FROM WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID =
        (SELECT ORGANIZATION_ID
           FROM APPS.ORG_ORGANIZATION_DEFINITIONS
          WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
        TRANSACTION_DATE <
        (SELECT SCHEDULE_CLOSE_DATE + 1
           FROM APPS.ORG_ACCT_PERIODS
          WHERE ORGANIZATION_ID =
                (SELECT ORGANIZATION_ID
                   FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                  WHERE ORGANIZATION_CODE = 'ORGZ_CODE') AND
PERIOD_NAME = 'PERIOD_NAME')
       --Unprocessed Shipping Transactions

SELECT COUNT(*)
  FROM WSH_DELIVERY_DETAILS     WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA,
       WSH_NEW_DELIVERIES       WND,
       WSH_DELIVERY_LEGS        WDL,
       WSH_TRIP_STOPS           WTS
WHERE WDD.SOURCE_CODE = 'OE' AND WDD.RELEASED_STATUS = 'C' AND
       WDD.INV_INTERFACED_FLAG IN ('N', 'P') AND
WDD.ORGANIZATION_ID = ORGZ_ID AND
       WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND
       WND.DELIVERY_ID = WDA.DELIVERY_ID AND
       WND.STATUS_CODE IN ('CL', 'IT') AND
       WDL.DELIVERY_ID = WND.DELIVERY_ID AND
       WTS.PENDING_INTERFACE_FLAG IN ('Y', 'P') AND
       TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN
       TO_DATE('PERIOD_START_DATE 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
       TO_DATE('PERIOD_END_DATE 23:59:59', 'DD-MON-YYYY HH24:MI:SS') AND
       WDL.PICK_UP_STOP_ID = WTS.STOP_ID

0 Comments

Apps Functional

Mass Item Sub Inventory Assignment

By Jag - July 12, 2014

0 Comments

Apps Functional

ABC Analysis and Cycle Count | Oracle Apps R12

By Jag - July 07, 2014

1 Comments

Apps Functional

Oracle Inventory: Inventory Organizations:Oracle apps r12

By Jag - April 21, 2014

0 Comments

Account Receivables

AutoInvoice Program Overview in Oracle Applications – Receivables

By Jag - April 16, 2014

0 Comments

Apps Functional

Adjusting Items in Inventory and Classifying Items-Oracle ERP Apps R12

By Jag - April 14, 2014

0 Comments

Inventory

Oracle Inventory --- Purchasing Category:

By Jag - August 20, 2013

0 Comments

Interview FAQ

Oracle Apps Inventory FAQs

By Jag - August 18, 2013

Where do we set Inventory Organization for a particular responsibility??

Through Organizational Access Form

Transaction Source Type:

Item:

Items is a part or service you:

o Purchase

o Sell

o Plan

o Manufacture

o Stock

o Distribute

o Prototype

The following Modules use items:

Item Statuses and Item Attributes:

Status attributes are item attributes that enable key functionality for each item. An item status is defined by selecting the value check boxes for the status attributes. Both status attributes and item status can be controlled at the item level or organization levels.

Item Statuses are:

Ø BOM Allowed

Ø Build in WIP

Ø Customer Orders Enabled

Ø Internal Orders Enabled

Ø Invoice Enabled

Ø Transactable

Ø Purchasable

Ø Stackable

Item Attributes

Item attributes are the collection of information about an item.

Stackable, Receivable, Financing Allowed, Purchased, Shippable, Returnable, Costing Enabled,

Transact able, BOM Allowed, Build in WIP, Purchasable, Customer Orders Enabled, Internal Orders Enabled, Invoice Enabled, Inventory Item, Revision Control, Lot Control, Locator Control

Receipt Required, List Price.

Categories and Category Sets

Categories are logical groupings of items that have similar characteristics.

A category set is a distinct category grouping scheme and consists of categories.

Some application modules, Inventory, for example require that all items are assigned to a category. The user specifies a default category for this purpose in each of these modules. Categories and category sets are used to group items for various reports and programs.

Item Categories - Setup

Ø Define the Flexfield structures for the item categories Flexfield.

Ø Define categories.

Ø Define category sets and assign the categories to the sets. Each set can use a different Flexfield definition if required.

Ø Assign default category sets to each functional area, like Purchasing, Planning and Inventory

Ø Assign items to categories. An item can be assigned to only one category within a set.

Item Cataloging

Ø Item cataloging is used to add descriptive information to items and to partition the Item Master into groups of items that share common characteristics. The characteristic required to uniquely define an item in each group is configured in advance. While defining items, these characteristics are assigned to an item catalog group.

Ø  While the catalog group Flexfield is a required setup, item cataloging is optional.

Ø To define a catalog, as many distinct item catalog groups as needed can be defined. Each group has unique characteristics (called descriptive elements) that completely describe items belonging to the group.

Ø When assigning an item to an item catalog group, values for the descriptive elements that apply to the item are defined. For example, an item catalog group called Computer could have a descriptive element called Processing Speed. Possible values for Processing Speed might be 100MHZ, 133MHZ, and so on.

Transaction

Ø A transaction is an item movement into, within, or out of inventory. A transaction changes the quantity, location, or cost of an item. Inventory supports a number of predefined and user-defined transaction types.

Ø Every material movement has a corresponding set of accounting transactions that Oracle Inventory automatically generates.

Ø All transactions validate the various controls (revision, locator, lot number, and serial number) enabled for items.

Inventory Organization

Ø An inventory organization can be a physical entity like a warehouse where inventory is stored and transacted.

Ø An inventory organization can be a logical entity like an item master organization which only holds items with no transactions An inventory organization can have its own location with a set of books, a costing method, a workday calendar, and a list of items.

Ø An inventory organization can share one or more of these characteristics with other organizations.

Ø An inventory organization is an inventory location with its own set of books, costing method, workday calendar and list of items. An organization can be a company, subsidiary, or warehouse.

Consider the following when you plan your enterprise structure:

Ø Sets of Books: You can tie one Oracle General Ledger set of books to each inventory organization.

Ø Costing Methods: You set your costing method (Standard or Average) at the organizational level. The item attribute control level determines the costing organization.

Ø Item Costs: Oracle Inventory keeps one cost per item per inventory organization.

Ø Movement between Inventory Sites: You can use in-transit inventory for inter-organization transfers.

Ø Planning Method: You can choose how to plan your items.

Ø Forecasting: You can forecast your items.

Ø Accuracy Analysis: You can perform a cycle count, or a physical inventory

Item validation organization

Ø Item validation organization is a logical entity listing all the items that an enterprise sells to customers. Multiple item validation organizations can be listed that share the same item master organization.

Ø A minimum of one item validation organizations is required per set of books.

Ø A maximum of one item validation organization per operating unit is allowed to determine the items that may be sold in each operating unit.

Item Master Organization

Item Master Organization is usually the first Inventory organization that is set up. Its single purpose is for entering items. It has no sub inventories and is not used for inventory transactions. Items are entered in an item master organization and then assigned to be used in child organizations.

Child Organization

Child Organization is an inventory organization with at least one subinventory that is set up for processing inventory transactions. It is not used to enter items. It gets a list of items from the master.

What is Item Master organization and Child Organization?

Ø Item Master Organization is usually the first Inventory organization that is set up. Its single purpose is for entering items. It has no sub inventories and is not used for inventory transactions. Items are entered in an item master organization and then assigned to use in child organizations.

Ø Child Organization is an inventory organization with at least one sub inventory that is set up for processing inventory transactions. It is not used to enter items. It gets a list of items from the master.

What is the purpose of Organization Assignment?

After defining an Item in the Item master, it has to be assignment to the Organization and is known as Organization Assignment. The purpose of the Organization Assignment is to make the item available for Transactions in particular Organizations.

What are Organization Assignment and Organization Item?

Item can be enabled in all child organizations under master organization or child organizations where the item to be used is chosen. Inventory propagates item to all organizations in which the item is to be defined.

Organizational attributes for item attributes which are enabled in that organization, can be entered or changed which are enabled in that organization. For example, go to an organization to choose reorder point planning for an item, and then go to another organization and choose Min-Max planning for the same item.

Deletion Constraints and Deletion Groups

Ø If you want to enforce specific business rules and add custom checks before you can delete an item, you must define item deletion constraints to supplement the standard predefined item deletion conditions.

Ø You can delete items that have incorrect attribute information. For example, if you make a mistake in entering an item number, use the Deletion Groups window to delete the item.

Ø If you decide to purge the item immediately after incorrectly defining it and before using it anywhere in the system, you will be able to delete it.

Subinventory

Ø A sub inventory is a Subdivision of an organization, representing either a physical area or a logical grouping of items, such as a storeroom or receiving dock

Ø A subinventory is a physical or logical grouping of inventory, such as raw material, finished goods, defective material, or a freezer compartment.

Ø The subinventory is the primary place where items are physically stocked. A subinventory must be specified for every inventory transaction

Ø Sub inventories can be further divided into areas designated as locators.

0 Comments

Table	Description
MTL_PARAMETERS	It maintains a set of default options like general ledger accounts; locator, lot, and serial controls, inter-organization options, costing method, etc. for each organization defined in Oracle Inventory. Each organization’s item master organization (MASTER_ORGANIZATION_ID) and costing organization (COST_ORGANIZATION_ID) are maintained here.
MTL_SYSTEM_ITEMS_B	This is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item Description and Long Description in multiple languages.

Key Tables in Oracle Inventory

List of Profile Options In INV,PO,OM,MS/MRP in Oracle

List of Profile Options In INV,PO,OM,MS/MRP in Oracle

Inventory Period Close:Oracle Apps R12

Mass Item Sub Inventory Assignment

ABC Analysis and Cycle Count | Oracle Apps R12

An ABC analysis determines the relative value of a group of inventory items based on a user–specified valuation criterion.

Oracle Inventory: Inventory Organizations:Oracle apps r12

Inventory Organization Structure

AutoInvoice Program Overview in Oracle Applications – Receivables

Adjusting Items in Inventory and Classifying Items-Oracle ERP Apps R12

Adjusting Items in Inventory

How to do it...

Oracle Inventory --- Purchasing Category:

Oracle Apps Inventory FAQs

SHARE

recent posts

Categories

Followers

Labels