Simple Query To Fetch Item Information

SELECT

     substr(inv.segment1,1,7) "Item",

     substr(inv.inventory_item_id,1,4) "Id",

     inv.bom_enabled_flag,

     inv.build_in_wip_flag,

     inv.customer_order_enabled_flag,

     inv.internal_order_enabled_flag,

     inv.invoiceable_item_flag,

     inv.mtl_transactions_enabled_flag,

     inv.purchasing_item_flag,

     inv.stock_enabled_flag,

     substr(inv.receiving_routing_id,1,2) "Rte ID",

     substr(inv.list_price_per_unit,1,2) "Lst Pr"

FROM

     mtl_system_items_B inv

WHERE

     inv.segment1 LIKE 'XX%' AND

     inv.organization_id = "Org_Id" 

Creating Different Type of Items

Finished Good Item

1.    Enter the following information, within the Master Item window:

Field Name	Value
Item Number	XX 1000
Description	FINISHED GOOD

2.    Apply the Finished Good template.

3.    Select the Work In Process tab. Notice that the template applied the following attributes:

-        Build in WIP = Yes

-        Supply Type = Push

4.    Assign the Finished Good Item to the M1 Seattle Manufacturing Organization

Phantom Item

5.    Enter the following data, within the Master Item window:

Field Name	Value
Item Number	XX 2000
Description	PHANTOM

6.    Apply the Phantom Item template.

7.    Select the Work In Process tab. Notice that the template applied the following attributes:

-        Build in WIP = Yes

-        Supply Type = Phantom

8.    Assign the Phantom Item to the M1 Seattle Manufacturing Organization.

Purchased Item (Push)

9.    Enter the following data, within the Master Item window:

Field Name	Value
Item Number	XX 1001
Description	XX BUY 1

10.  Apply the Purchased Item template.

11.  Select the Purchasing tab and enter the following information:

-        List Price = 40

12.  Select the Receiving tab and enter the following information:

-        Receipt Routing = Inspection

13.  Select the Work In Process tab and enter the following information:

-        Build In WIP = Yes

-        Supply Type = Push

14.  Assign the Purchased Item to the M1 Seattle Manufacturing Organization.

Purchased Item (Operation Pull)

15.  Enter the following data, within the Master Item window:

Field Name	Value
Item Number	XX 2002
Description	XX BUY 2

16.  Apply the Purchased Item template.

17.  Select the Purchasing tab and enter the following information:

-        List Price = 80

18.  Select the Work In Process tab and enter the following information:

-        Build In WIP = No

-        Supply Type = Operation Pull

19.  Assign the Purchased Item to the M1 Seattle Manufacturing Organization.

Thanks & Regards,

S.Grace Paul Regan

Simple Query to fetch Subinventory & Stock Locator Information

SELECT

     substr(matsub.secondary_inventory_name,1,10) "SubInv",

     substr(matsub.description,1,20) "Descripton",

     substr(matloc.description,1,20) "Locator",

         substr(matloc.inventory_location_type,1,1) "Locator Type",

     substr(matloc.inventory_location_ID,1,4) "Locator ID"

FROM

     mtl_secondary_inventories matsub,

     mtl_item_locations matloc

WHERE

     matsub.secondary_inventory_name LIKE ‘&XX_SUBINVENTORY%' AND

     matsub.organization_id = 207 AND

     matloc.organization_id = 207 AND

     matsub.secondary_inventory_name = matloc.subinventory_code

Thanks & Regards,

S.Grace Paul Regan

Item Interface

Overview:

Oracle provides flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

Conversion/Interface Strategy:

1. Data Mapping
   
   
   During the data mapping process, list of all the data sets and data elements that will need to be moved into the Oracle tables as part of conversion are identified. Data mapping tables are prepared as part of this activity that show what are the data elements that are needed by the target system to meet the business requirements and from where they will be extracted in the old system.
   
   
2. Download Programs
   
   
   After the conversion data mapping is complete, download programs are developed that are used to extract the identified conversion data elements from the current systems in the form of an ASCII flat file. The structure of the flat file must match the structure of the Oracle standard interface tables. These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.
   
   
   
3. Upload Program
   
   
   Once the data has been extracted to a flat file, it is then moved to the target file system and the data from the file is loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities. Then programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables.
   
   
4. Interface Program
   
   
   Once the interface tables are populated, the respective interface program (each data element interface has a specific interface program to run) is submitted. The interface programs validate the data, derive and assign the default values and ultimately populate the production base tables.

The Item import Interface(IOI) reads data from following tables for importing items and item details. The MTL_SYSTEMS_ITEM_INTERFACE table is used for new item numbers
and all item attributes. This is the main item interface table, and can be
the only table used to import items.


MTL_ITEM_REVISIONS_INTERFACE is used if Item revisions history is also loaded with items. Item categories can be imported using MTL_ITEM_CATEGORIES_INTERFACE.

The import error can be tracked using MTL_INTERFACE_ERRORS table. The transaction_id and request_id populated by the import program can be used to link interface table and error table.

Required Data:

ITEM_NUMBER or SEGMENT Columns

Every row in the item interface table must identify the item and organization. To identify the item when importing it, you may specify either the ITEM_NUMBER or SEGMENTn columns—the Item Interface generates the INVENTORY_ITEM_ID for you.


ORGANIZATION_ID or ORGANIZATION_CODE


You need to specify either the ORGANIZATION_ID or ORGANIZATION_CODE that identifies the organization.


DESCRIPTION


When you import a new item, you are also required to specify the DESCRIPTION.

TRANSACTION_TYPE & PROCESS_FLAG

There are two other columns the Item Interface uses to manage processing. They are TRANSACTION_TYPE, which tells the Item Interface how to handle the row, and PROCESS_FLAG, which indicates the current status of the row.


Always set the TRANSACTION_TYPE column to CREATE, to create an item record (true when both importing a new item and assigning an already existing item to another organization). This is the only value currently supported by the Item Interface. The Item Interface uses the PROCESS_FLAG to indicate whether processing of the row succeeded or failed. When a row is ready to be processed, give the PROCESS_FLAG a value of 1 (Pending), so that the Item Interface can pick up the row and process it into the production tables.

Meaning of PROCESS_FLAG Values:

Code	Code Meaning
1	Pending
2	Assign complete
3	Assign/validation failed
4	Validation succeeded; import failed
5	Import in process
7	Import succeeded

Note:

When more than one of these columns has been entered and they conflict, ITEM_NUMBER overrides SEGMENTn and ORGANIZATION_ID overrides ORGANIZATION_CODE. It is strongly recommended that you use SEGMENT column instead of ITEM_NUMBER.

For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import (IOI) program can be run in parallel if separate set_process_ids are passed while submitting.

Derived Data:

Many columns have defaults that the Item Interface uses when you leave that column null in the item interface table

Validations:

1] Validation for organization code (standard table: 

    ORG_ORGANIZATION_DEFINITIONS)

2] Validation for Item_number (standard table: mtl_system_items_b)

3] Validation for Description

4] Validation for Primary Unit of Measure (standard table: 

    MTL_UNITS_OF_MEASURE)

5] Validation for Template Name (standard table: mtl_item_templates)

6] Validation for Item Type (standard table: FND_COMMON_LOOKUPS)

7] Validation for ENCUMBRANCE_ACCOUNT (standard table: 

    gl_code_combinations)

8] Validation for EXPENSE_ACCOUNT (standard table: gl_code_combinations)

Note: You can add more validation as per your business requirement.

Record Insertion:

Through your custom program you can insert the below columns in the interface table. Again the list is a sample one; you can add additional columns as your business requirement.

Columns inserted:

• ORGANIZATION_ID                        –>Taken from ORG_ORGANIZATION_DEFINITIONS table
• ORGANIZATION_CODE                  –>Taken from Staging table
• LAST_UPDATE_DATE                      –>sysdate
• LAST_UPDATED_BY                        –>fnd_global.user_id
• CREATION_DATE                             –>sysdate
• CREATED_BY                                    –>fnd_global.user_id
• LAST_UPDATE_LOGIN                    –>fnd_global.login_id
• DESCRIPTION                                   –>Taken from Staging table
• SEGMENT1                                         –>Taken from Staging table
• PRIMARY_UOM_CODE                    –>Taken from Staging table
• PRIMARY_UNIT_OF_MEASURE      –>Taken from MTL_UNITS_OF_MEASURE
• ITEM_TYPE                                         –>NULL
• TEMPLATE_NAME                             –>Taken from Staging table
• TEMPLATE_ID                                     –>Taken from mtl_item_templates        
• MIN_MINMAX_QUANTITY              –>Taken from Staging table
• MAX_MINMAX_QUANTITY             –>Taken from Staging table
• LIST_PRICE_PER_UNIT                     –>Taken from Staging table
• ITEM_CATALOG_GROUP_ID            –>Taken from Staging table
• SET_PROCESS_ID                               –>1
• PROCESS_FLAG                                  –>1
• TRANSACTION_TYPE                         –>‘CREATE’

Standard Concurrent Program:

After you insert valid data into Interface table, you can go to Items > Import > Import Items and run the standard concurrent program. Here is the parameter form.

1] All Organizations:

• Yes: Run the interface for all organization codes in the item interface table.
• No: Run the interface only for the organization you are currently in. Item interface rows for organizations other than your current organization are ignored.

2] Validate Items:

• Yes: Validate all items and their data residing in the interface table that have not yet been validated. If items are not validated, they will not be processed into Oracle Inventory.
• No:  Do not validate items in the interface table.

3] Process Items:

• Yes: All qualifying items in the interface table are inserted into Oracle Inventory.
• No: Do not insert items into Oracle Inventory.

4] Delete Processed Rows:

• Yes: Delete successfully processed items from the item interface tables.
• No: Leave all rows in the item interface tables.

5] Process Set:

Enter a number for the set id for the set of rows you want to process. The program picks up the rows marked with that id in the SET_PROCESS_ID column. If you leave this field blank, all rows are picked up for processing regardless of the SET_PROCESS_ID column value.

Working with failed interface rows:

If a row fails validation, the Item Interface sets the PROCESS_FLAG to 3 (Assign/validation failed) and inserts a row in the interface errors table, MTL_INTERFACE_ERRORS. To identify the error message for the failed row, the program automatically populates the TRANSACTION_ID column in this table with the TRANSACTION_ID value from the corresponding item interface table.

The UNIQUE_ID column in MTL_INTERFACE_ERRORS is populated from the sequence MTL_SYSTEM_ITEMS_INTERFACE_S. Thus, for a given row, the sequence of errors can be determined by examining UNIQUE_ID for a given TRANSACTION_ID.

You should resolve errors in the sequence that they were found by the interface, that is, in increasing order of UNIQUE_ID for any TRANSACTION_ID.

Resubmitting an Errored Row:

During Item Interface processing, rows can error out either due to validation (indicated by PROCESS_FLAG = 3 in MTL_SYSTEM_ITEMS_INTERFACE and the corresponding error in MTL_INTERFACE_ERRORS) or due to an Oracle Error.

When an Oracle Error is encountered, the processing is stopped and everything is rolled back to the previous save point. This could be at PROCESS_FLAG = 1, 2, 3, or 4.

When you encounter rows errored out due to validations, you must first fix the row corresponding to the error with the appropriate value. Then reset PROCESS_FLAG = 1, INVENTORY_ITEM_ID = null, and TRANSACTION_ID = null. Then resubmit the row for reprocessing.

Useful Query:

01

Select

02

SEGMENT1,

03	DESCRIPTION,

04	PROCESS_FLAG,

05	SET_PROCESS_ID,

06	INVENTORY_ITEM_ID,

07	ORGANIZATION_ID,

08	ORGANIZATION_CODE,

09	CREATION_DATE,

10	ITEM_TYPE,

11	UNIT_OF_ISSUE,

12	TEMPLATE_ID,

13	TEMPLATE_NAME,

14	EXPENSE_ACCOUNT,

15	ENCUMBRANCE_ACCOUNT,

16	PRIMARY_UOM_CODE,

17	PRIMARY_UNIT_OF_MEASURE,

18	MIN_MINMAX_QUANTITY,

19	MAX_MINMAX_QUANTITY,

20

TAX_CODE,

21	REQUEST_ID

22

from

23	MTL_SYSTEM_ITEMS_INTERFACE

24	order by CREATION_DATE;

25	----------------------------

26

Select

27	ORGANIZATION_ID,

28	UNIQUE_ID,

29	REQUEST_ID ,

30	TABLE_NAME ,

31	COLUMN_NAME,

32	ERROR_MESSAGE

33	CREATION_DATE,

34	MESSAGE_TYPE

35

from

36	MTL_INTERFACE_ERRORS

37	order by CREATION_DATE;

Base tables that would be affected by  MTL_SYSTEM_ITEMS_INTERFACE

MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_CATEGORIES_B
MTL_CATEGORY_SETS_B
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES

Required columns in MTL_ITEM_REVISIONS_INTERFACE table.

The table is only used if Item revision is to be loaded in the same run with IOI. If this table is not used then items are created with the default revision setup for an organization.

PROCESS_FLAG = 1

TRANSACTION_TYPE = 'CREATE'

SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)

ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.

REVISION 

EFFECTIVITY_DATE

IMPLEMENTATION_DATE

ITEM_NUMBER = Same as item_number in mtl_system_items_interface table.
Each row in the mtl_item_revisions_interface table must have the REVISION and 

EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

Required columns for MTL_ITEM_CATEGORIES_INTERFACE table

TRANSACTION_TYPE = 'CREATE'

SET_PROCESS_ID = any numeric value(Should be same for the item in 

MTL_SYSTEM_ITEMS_INTERFACE table)

ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org

ITEM_NUMBER/INVENTORY_ITEM_ID or both

CATEGORY_SET_NAME or CATEGORY_SET_NAME or both

CATEGORY_ID or CATEGORY_NAME or both

For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import(IOI) program can be run in parallel if seperate set_process_ids are passed while submitting. The IOI automatically separates Master records from Child, and  processes Master records first. However, as one IOI process is not aware of  other IOI processes running in parallel, do not split a given item's separate Organization records into two different SET_PROCESS_IDs that are being run in parallel.

Item import program can be run in 2 modes INSERT & UPDATE.
The method to update Item attribute columns to NULL is to use the following values:
· for Numeric fields: insert -999999
· for Character fields: insert '!'

Useful Metalink Note:

1. FAQ for Item Import [ID 109628.1
2. Error in Validating MTL_SYSTEM_ITEMS_INTERFACE [ID 1057175.6]
3. A Guideline to IOI Error Messages and Solutions [ID 52746.1]
4. How to Import Item Costs Using the Item Open Interface (IOI) [ID 191376.1]
5. Item Import Performance Tips [ID 66496.1]
6. INCOIN: Basic Steps for Researching Failed Item Imports [ID 552683.1]
7. Troubleshooting Guide for INV_IOI_MASTER_CHILD Errors in Item Import [ID 429924.1]
8. How To Prepare to Run Parallel Runs of the Item Import Interface INCOIN [ID 842767.1]
9. Item Import Gives Error: “The Default Primary Unit of Measure Is Invalid” [ID 789927.1]
10. Understanding Item Import and Debugging Problems with Item Import [ID 268968.1]
11. How to create a Category Set and Assign Items to Categories [ID 423551.1]
12. Item API Or Concurrent Manager, Which One Is Best For Item Creation? [ID 760498.1]

Composed from various web-sources & Oracle Meta link documents

Thanks & Regards,
S.Grace Paul Regan

Cost Cutoff Date

Navigation: Inventory Super User> Setup> Organization> Parameters>Costing Tab.

What is the Cost Cutoff Date?

When the cost processors are run, users specify the cost cutoff date option and the cutoff date for all cost organization books that are being processed. The cost cutoff date is the last day on which the cost processor will process transactions for an accounting period.

What is the cost cutoff date option?

You can set the cutoff date option to User-Defined or Auto. The User-Defined option requires you to specify the cutoff date; while the Auto option saves you the effort of redefining the cutoff date which is automatically moved forward by the cost processor. Under the Auto option, the cost processor moves the cutoff date forward up to the last date of the earliest open cost period, and then it stops until the period is closed. After the period is closed, the cost processor advances the cutoff date into the next open period, and so on. However if a transaction is successfully preprocessed after the cutoff date, then the cutoff date for that cost organization book moves forward to the date of the last successfully preprocessed transaction; this could happen, for example, if the cutoff date option was originally set to User-Defined and subsequently changed to Auto.

 

What are backdated transactions?

One of the purposes of the cost cutoff date is to allow backdating of transactions. For example, if you set the cost cutoff date to October 31, you can still process October transactions that were entered in November for the period ending October 31 by backdating them to October 31 or earlier. However, when the cost cutoff date advances forward to a date past October 31, the transactions can no longer be backdated to October 31 or earlier, and they are processed with the forward date. If you set a cost cutoff date at October 31, the cost processor will queue up but not process any transactions with a date after October 31. If you subsequently need to backdate transactions to a date before October 31, you can process these transactions before any transactions with dates of November 1 and beyond. You can also backdate transactions to any date after October 31, with the assurance that these transactions will be processed in the correct order when the cost cutoff date moves forward.

What is the costing date of transactions?

The costing date of transactions is normally the same as the transaction date, except for backdated transactions. If there are any previously costed transactions (that is, the cost processor has processed transactions whose date is earlier than the date of the backdated transactions), then the costing date of the backdated transactions is set as the cost processor run date.

Note: The cost cutoff date only affects the accounted date of the transaction. It does not affect the inventory transaction date, or the inventory quantity reported for a given date.

Backdating of Transactions: Examples:

By setting the cost cutoff date for a cost accounting period, you can manage which transactions are processed in that period, including backdated transactions. The following examples illustrate how the cost processor sets the accounted date for backdated transactions. Assume that the current date is November 2, the cost cutoff date is October 31, and the following costed and uncosted transactions are in process:



Below, the inventory transaction is backdated to position A. The transaction will be costed with accounting date B before the transactions 2 and 3 are processed.



Below, the inventory transaction is backdated to position C. The transaction will be costed with accounting date C after the transactions 2 and 3 are processed.



Below, the inventory transaction is backdated to position D. The transaction will be costed with accounting date D after the cost cutoff is moved past October 31.

 

How to use Cost Cutoff Date?

This functionality was developed to allow businesses the option of changing labor rates and overhead rates at the beginning of the accounting period while transactions in the next period wait for these new costs to be completed.     This will allow period close, cost updates, and rate changes to occur without impacting or interrupting business operations.  When using the Cost Cutoff Date, all cost processing for the new accounting period is stopped for that organization.  This allows the accountants the opportunity to close the previous period.  Once the new costs are set, then the costing is started for the new period.  This occurs by changing the cost cutoff date to a date in the future.

If this field is left blank, then the old cost processing will occur.  If the field is populated, then no processing of costs will occur from that date onwards.  The cost cutoff date will use midnight as the start of the period and also inventory quantities are recorded at midnight.  With this new functionality.

This option is much useful with the Organization that a Standard Costing as their costing method.

The Standard Cost Update can now run in one organization while other organizations are still costing transactions.  Standard Cost Workers are launched based on organization, so this improves the speed of costing transactions. To use this Feature: For standard costing organizations if the start of the new costs is to be January 1 2012, that is the date that is entered. New standard costs can then be established prior to January 2012, in December 2011 for example. These costs will not impact the December costing activities as they will not be active until Jan 1 2012. No transactions that occur after the start of the day January 1, 2012 are costed.

This will allow the accountants to complete the costing for the December transactions, close the period, and run the reports for review. A standard cost update can be performed using the new cost type for January 2012.  This will update the cost of the costed items up to the end of the day (midnight) December 31, 2011.  The reports can then be re-run with the same quantity and newly updated costs. Still NO January 2012transactions will be costed.

This is true whether it takes a few days in the next period or a few weeks to finish the costing of the previous period.  The uncosted transactions will remain in the MTL_MATERIAL_TRANSACTIONS with costed_flag = N waiting for a cost worker to process them.  The Cost Manager will spawn no cost worker until the Cutoff Date is changed. To cost the January 2012 transactions once December 2011 is properly closed, the Cost Cutoff Date is changed. This can be changed to the start of the next period or next quarter or next year---whenever the rates need to be changed next.  Once changed the cost processing begins for the transactions that have been waiting.  For standard costs the processing of the transactions is immediate.

For Average, FIFO, and LIFO costing the process takes longer because of the need to process the transactions sequentially to keep the costs accurate.  For Average, FIFO, LIFO cost organizations , Rate Cost Types are created and the old rates are copied to that new cost type.  This rate will not be used for cost processing until the Cost Cutoff Date is changed.  The cost type and the cutoff date need to be changed to allow the new costs to be used and the transactions can be processed for the new period.   For Shipping transactions, the costs also use the cutoff date.  The shipment in the new period will be uncosted. If another organization receives the shipment, the receipt will also be uncosted.  This is due to the fact that the receiving transaction requires the accounting distribution from the sending organization.  In standard costing, once the cutoff date is changed, the shipments are costed immediately.  In Average FIFO LIFO costing, the first uncosted transactions will cause an error. Once the cost cutoff date is changed and the errored transaction reset, the transaction will be costed first for the shipment and then for the receipt.  All subsequent transactions will then be processed as well.
Important Note: If this functionality is not required or desired, the Cost Cutoff Date field must be BLANK. If there is a date entered in that field, no costing will occur on transactions for that organization from that date onwards..

Content: Extract from Metalink Note.
Thanks & Regards,
S.Grace Paul Regan

Over Receipt & Encumbrance Accounting

If you have not set the Over Receipt tolerance with consideration or proper validation in the business where Encumbrance Accounting is enabled you will land with very critical Accrual issue.

What happens if Over Receipt tolerance in the Receiving Options are not set with 0 % Tolerance and the Action=Reject ?

Even though you have three ways matching enabled for your Invoice Matching option this will restrict only for over payment even if you have over received by mistake. But if encumbrance accounting is used in your system here is the complexity.

What will these over received quantities do?

If the items are Inventory Items this will inflate your Inventory Valuation Account and Accrual Account or if the items are expense items this will inflate your Accrual Account.

E.g. Your PO Ordered Quantity: 100 Unit Price: 10 Total PO Value: 10 * 100 = 1000 (Month Of January)

If you have received in January - 150 Quantity instead of 100 Ordered Quantity your Accrual value will be 10*150 = 1500

When you run the Receipt Accrual at the January Period end your Accrual value will be 1500 where as it should be only 1000 and this will open up your next month accrual value with 1500 which is not supposed to be there. Whereas the extra 500 will be an inflated value in your budget and will pass on and will end up with unstable budgeting.

If you will not diagnose this over receipt issue users may use the budget for which they are not eligible and if this continues this will affect the company’s balance sheet severely in couple of years.

Hence make sure if encumbrance accounting is enabled in an Organization, Receiving control should be with zero tolerance or plan accordingly.

Thanks & Regards,
S.Grace Paul Regan

How does one close the Inventory period for a period status of "Closed not Summarized"?

The Period Close Reconciliation Report does not currently have any identified performance issues. In release 11.5.10 we have actually moved the entire period summarization logic to this report to allow users to run the process separately (or not at all, if they do not use our summarization feature).
That is why this report is taking a long time, whereas previously the close process itself was taking this amount of time.

Yes, it is possible to run the program independently. You can run the Period Close Reconciliation Report at any time just like a normal report. As you have stated, if it is run on a "Closed not Summarized" period, it will
summarize and the status will go to "Closed" after the report completes.

If it is run on a "Closed" period, it will simply pick up the summary information previously calculated. If it is run on an open period, it will run through a simulation of the summarization (can detect any potential
errors before closing for real).

Please note that a "Closed not Summarized" period is treated by Inventory in exactly the same way as a "Closed" period (transaction entry not allowed). In addition, the accounting would have already been transferred to GL in both cases. The only thing different has to do with the availability of summary
information. This information can be queried through the Accounting Periods
form or through the Period Close Reconciliation Report.


NAVIGATION

1. Responsibility: Cost Management
2. Goto View > Requests > Submit New Request > Single Program
3. Enter Period Close Reconciliation Report.
4. Enter the parameters and submit.

Thanks & Regards,
S.Grace Paul Regan

INTER ORGANIZATION TRANSFERS

INTER ORGANIZATION TRANSFERS

Scope and Application

This document is intended for users of Oracle Inventory applications. This document does not address the accounting distribution for inter - organization transfers.

Note: Items must be defined in both organizations in order to be able to transfer

INTER ORGANIZATION TRANSFERS

You can define multiple inventories, warehouses, and manufacturing facilities as distinct organizations. With Oracle Inventory you can perform inter–organization transfers as direct or intransit shipments.

You can transfer one or more items in a single transaction. You can also transfer partial quantities of the same item to different subinventories and locators in a single transaction. The items you transfer must exist in both organizations. You can also transfer expense and asset items from one organization to another using intransit inventory.

Direct Inter–organization Transfers

You can use a direct inter–organization transfer to move inventory directly from a shipping organization to a destination organization.

The validity of a transfer transaction depends on the controls you have defined in both the shipping and destination organizations for the items you want to transfer.

For example, you can transfer item A from organization X to organization Y, even though item A is under lot control only in organization X (you can specify the lot numbers for item A in organization X during the transfer transaction). However, you
cannot transfer item B from organization X to organization Y if item B is under lot control only in organization Y (you cannot specify lot numbers for item B in the destination organization because you are performing a direct transfer).

Inter–Organization Transfers via Intransit Inventory

You usually transfer material to intransit inventory when transportation time is significant. When you perform the transfer transaction, you do not need to specify the delivery location. You only need to enter the subinventory you are shipping from, a shipment number, the freight information, and, depending on the inter–organization transfer charge that applies between the organizations, a percentage of the transaction value or a discrete amount that Oracle Inventory uses to compute
transfer charges.

If the FOB point is set to Receipt in the Shipping Networks window, the destination organization owns the shipment when they receive it. If it is set to Shipment, the destination organization owns the shipment when the shipping organization ships it, and while it is intransit.
While your shipment is intransit, you can update shipping information such as the freight carrier or arrival date in the Maintain Shipments window. See: Managing Shipments, Oracle Purchasing User’s Guide.

At the time of shipment, you must define your receiving parameters for the destination organization. You can receive and deliver your shipment in a single transaction or you can receive and store your shipment at the receiving dock. See: Receipts, Oracle Purchasing User’s Guide.

The validity of a transfer transaction depends on the controls you have defined in both the shipping and destination organizations for the items you want to transfer. For example, you can transfer item A from organization X to organization Y, even though item A is under lot control only in organization X (you can specify the lot numbers for item A in organization X during the transfer transaction). You can also
transfer item B from organization X to organization Y if item B is under lot control only in organization Y (you can specify lot numbers for item B in the destination organization when you perform the receiving transaction).

Transferring Between Organizations

You can transfer material from your current organization to another organization, or from your current organization to intransit inventory.

Material in intransit inventory belongs to the organization identified by the FOB point. See: Defining Inter–Organization Shipping Networks.

Prerequisites

Define an inventory item that is common to both organizations. See: Defining Items and Assigning Items to Organizations. Define at least two organizations, one of which is valid to receive material from the other. See: Creating an Organization, Oracleand Defining Human Resource Management Systems User’s Guide

Organization Parameters.

Set up inter–organization relationships and their corresponding accounts. See: Defining Inter–Organization Shipping Networks.

For direct transfers, if the item to transfer is under serial number control, the item must have the same unit of measure in each organization. See: Defining Items.

To enter the information to perform a transfer between organizations:

Navigate to the Inter–organization Transfer window.

Enter the date of entry for the transaction.

The date you can enter is controlled by the INV:Transaction Dateprofile option. See: Oracle Inventory Profile Options.

Validation

Enter an organization to which to transfer the material. You must first define this organization as valid to receive material from your current organization. See: Defining Inter–Organization Shipping

Networks.

In addition, if this organization uses intransit inventory, Oracle Inventory stores the material as intransit inventory when you transfer any material to this organization. You must then move the material from intransit inventory to this organization with an intransit inventory receipt.

Enter a transaction type. This can be either a predefined system type or one you defined. See: Defining and Updating Transaction

Types.

Optionally, you can enter the source of the transaction type. See: Defining and Updating Transaction Source Types.

Indicate if inventory information should be defaulted from the serial number.

Enter any optional Shipment information.

Optionally, you can enter the following Shipment information:

To enter the items to transfer:

Choose Transaction Lines from the Inter–organization Transfer window.

Enter an inventory item to transfer.

You can transfer the same item more than once. For example, you can specify an item more than once to transfer partial quantities to different subinventories or stock locators.

For a direct transfer, if the item is under revision control in either organization, enter a revision that is common to the item in both organizations.

Enter a subinventory from which to transfer the material.

Optionally, enter the subinventory to which to transfer the material.

You must enter a value in this field for direct inter–organization transfers.

If you established locator control for the item, enter from and to locators.

Enter a lot number for the item. If you want to enter multiple lot numbers, complete the remaining steps, then choose the Lot/Serial button to display the Lot Entry window. For receipt transactions, if you enter a lot number, enter the date the lot expires. You can enter a value here only if the Lot Expiration (Shelf Life) Control attribute is set to User–defined Expiration Date.

Enter a unit of measure. This can be the primary unit of measure(the default) or any valid alternate unit of measure. If you enter an alternate unit of measure, Oracle Inventory issues the quantity you specify in this unit of measure. Oracle Inventory also converts the quantity to the primary unit of measure so that it can correctly update the on–hand quantity.

Enter the quantity of the item to transfer.

Optionally enter a reason code for the transaction. For example, you can use reason codes to allow you to mark exceptional charges to support a quality data collection and reporting system. You can also enter up to 240 characters of free text in the Reference field that describe the transaction.

To enter internal transfer charges to assign to the To organization:

_

Enter a value in the Added Value field that represents the transfer

charge. You can enter a value here only if you entered Requested value in the Inter–Organization Transfer Charge field in the Organization Parameters window.

Enter the percent of the transaction value that represents the transfer charge. You can enter a value here only if you entered Requested percent in the Inter–Organization Transfer Charge field in the Organization Parameters window. Defining Inter–Organization Information.

To enter freight information costs to assign to the From (current)

organization:

Enter the transportation cost to physically transfer the material; that is, the freight carrier charges.

Enter the general ledger account to which to charge the value you entered in the Transportation Cost field. Oracle Inventory displays the account you defined for the freight carrier as the default. See: Defining Freight Carriers.

To enter a unit number:

If Oracle Project Manufacturing is installed and if you have enabled its end item model/unit effectivity feature, you can enter a unit number for the item. See: Model/Unit Effectivity, Oracle Project Manufacturing Implementation Manual. Note: The Unit Number field is visible only if you have installed Project Manufacturing.

To enter lot or serial number information.

Choose the Lot/Serial button. See: Assigning Lot Numbers and Assigning Serial Numbers.



To view quantity available and quantity on hand values:

Review the following fields: Available: Displays the quantity available to transfer, based on the unit of measure you specified. The available quantity is the quantity on hand less all reservations for the item. This amount could include the amount you have reserved if you enter a transaction source that has reservations against it. The available quantity includes reservations against current transaction source. The available quantity is specific to the revision level, lot number, From

subinventory, and From locator you specify for the transfer. On hand: Displays the current on–hand quantity for the item, based on the unit of measure you specified. The on–hand quantity is specific to the revision, lot number, From subinventory, and From locator you specify for the transfer. On–hand includes quantities for pending transactions in the MTL_MATERIAL_TRANSACTIONS table.

To process the transaction:



Save your work.

To record movement statistics:

Use either of the following methods to record and maintain information associated with the movement of goods: Navigate to the Movement Statistics window and record information manually. See: Entering Movement Statistics. Automate the collection of this information by setting up parameters in the Movement Statistics Parameters and Economic Zones windows. See: Defining Movement Statistics Parameters and Defini


•A shipment number to uniquely identify an item or group of items to transfer. When the To Org uses intransit inventory, you must enter a value here.
•The freight carrier for the transfer.
•A waybill or airbill number for the transfer.
•The number of containers in which the material is stored for the transfer.
•The date you expect to receive the material at the destination organization. You must enter a date equal to or later than the current date. Oracle Inventory uses this date for reporting purposes only.

Content from Oracle Metalink

Thanks & Regards,
S.Grace Paul Regan

Troubleshooting Average Costing In Oracle R12

When troubleshooting the Actual Cost Manager's failure to start you will discover that it is not fully documented in one source document. There are pieces of the troubleshoot process scattered throughout Metalink's notes and FAQ's that take a considerable amount of time to locate.

This article will help expedite the Actual Cost Manager's troubleshooting process.
When Average Cost Updates are not being calculated the cause can be related to the Cost Manager and the Actual Cost Worker not running.

There are several reasons why this is happening:
The first thing to check is the Inventory > Set Up > Organizations > Parameters > Costing Information [TAB] > Cost Cutoff Date.

 If the cost cutoff date is set to the current date or the date is in the past the Average Cost calculations will not work. The Fix: It will need to be re-set to a valid date or cleared.
Other problems are associated with the Actual Cost Worker - if it does not start or if it completes with a warning or an error the Average Costs will not get updated.

 If there are any errors in the MTL_MATERIAL_TRANSACTIONS table the Cost Manager usually will not spawn an Actual Cost Worker in an Average Costing
environment UNTIL ALL ERRORS ARE CORRECTED in the table.

Problems can also be associated with the transaction rows that have a ‘null’ Distribution_Account_id which can cause the Costed_flag = ‘E’ which the Cost Manager identifies as an Error and then the Average Cost Update will not work since the Actual Cost Worker does not get started. The Fix: Add the correct Distribution_Acct_id and re-run the

Average Cost update.
Errors relating to the MTL_MATERIAL_TRANSACTIONS can be viewed in the forms: Cost Management > View Transactions > Material Transactions > select a date range when costs are not posting or an item that has not had its unit cost updated after an Average Cost Update has been posted. Look in the Transaction ID [TAB] Error Code column and the Error Explanation column. (Example: Error Code = CST_NO_TXN_INVALID_ACCOUNT and the Error Explanation = An invalid account is associated with the item being transacted.)


If an invalid account is associated with the item being transacted it could be because a Cost Variance account is missing from the Cost Group - verify this by: Cost Management > Set Up > Cost Groups > Account field (Cost Variance.) The Fix:Add a new Cost Variance account in the GL module and apply it to the Cost Group’s Cost Variance account.

Thanks & Regards,
S.Grace Paul Regan

What is Inventory?

●Inventory defines the item you use in manufacturing and maintain perpetual inventory balances through transactions that reflect daily activities such as receipts and material movement. Oracle Inventory provides the transaction needed to maintain on-hand quantities. These transactions records most inventory movements like receipts, issues and location changes.
●Inventory Organization is further subdivided into sub inventories A subinventory is a subdivision of an organizations inventory, frequently it represents a physical location. Oracle Inventory provides basic inventory planning like min-max planning and re-order point planning and basic forecasting capabilities.
●Oracle Inventory can be seamlessly integrated since Oracle General Ledger provides a set of books and currency exchange rates and receives transaction accounting summary and details.
●Inventory is the crucial module, connecting all of Oracle Manufacturing Applications together. It shares UOM, items, item information, receipts, delivers, inter-org information ATP/on-hand quantity and basic forecasting info with Cost Management, WIP, ASCP, BOM, OM, Purchasing, as well as engineering items and information with Engineering.

Thanks & Regards,
S.Grace Paul Regan

Oracle Inventory Organizations

Oracle Applications uses multiple types of organizations to build the business execution structure. At the top of the structure is the accounting set of books SOB), defined in the General Ledger. Next, different types of organizations are used to further define the organization structure and relationships. All organizations are defined and updated with the Define Organization form.

Set of Books:

A General Ledger SOB, linked to the inventory organization, controls the financial accounting of inventory transactions. A SOB is made up of a chart of accounts, a financial calendar, and a currency. The general ledger secures transactions (journal entries, balances) by SOB.

Legal Entity:

A legal entity organization defines the tax and fiscal reporting level. The legal entity represents the legal company.

Operating Unit:

An operating unit organization defines the Purchasing, Order Entry, Accounts Payable and Accounts Receivable level of operation. An operating unit may span multiple manufacturing facilities, distribution points and sales offices, or it may be limited to a single site.

Inventory Organization:

Two flavors of inventory organizations are found in Oracle Applications. They are defined the same, and both are assigned a set of books, a legal entity organization, an operating unit organization, and a location. An item master organization is used for item number maintenance and validation, No item transaction will be done in an Item Master Organization.

This master organization serves as a data repository storing items and item attributes, master level categories and category sets, master level cross references, and numerous data defaults. On-hand balances, inventory movements, and other on-going inventory activities are not performed in an item master organization. Generally, the master organization is used as the validation organization for Purchasing and Order Entry. It is recommended that a single item master organization be defined, even in multiple organization, multiple sets of books environments.

In addition to the item master organization there are one or more non-master inventory organizations. Like the item master inventory organization, the non-master organizations are assigned a set of books, a legal entity organization and an operating unit organization. The non-master inventory organization points to a master organization and looks to the master organization for master level item attributes, master level categories, and other master level controlled data.

Note that each organization has its own set of books/legal entity/operating unit relationship, so inventory organizations with differing SOB’s or operating unitsmay share the same master organization.

These non-master inventory organizations are the execution level organizations. They hold on-hand balances and transaction history. Here is where inventory users execute their daily activities, such as receiving and issuing material, performing cycle counts, and viewing material availability and transaction history. A single organization therefore generally represents a single manufacturing site or distribution center.

Locations:

A location code is an address. Each inventory organization must be assigned at least one location code.

Subinventories:

A subinventory is used as a holding point for on-hand
inventory and generally represents a stockroom, stocking area or cage used for storing material. Subinventories are defined within inventory.

organizations:

An inventory organization may have any number of subinventories, and an asset account is assigned to each subinventory. Since the subinventory entity is logical, as there is not an address or physical location description associated with it, clients may define subinventories for any physical or logical grouping of inventory.

Examples of subinventories:

Stores. Used for the primary stockroom for storing raw materials and sub-assemblies

FG. Used for Finished Goods inventory if finished goods are stocked

WIP. Used for raw material and sub-assemblies stored in production areas and issued at operation or job completion (backflush).

MRB. Used for Material Review Board goods. This is generally material that requires engineering review before disposition as scrap, rework, return to supplier or use as is.

Stock Locators:

Stock locators are an optional entity that may be used to represent physical locations within a subinventory. You may choose to use stock locators for selected subinventories or selected items within selected subinventories. If locators are used, subinventory and locator track on-hand balances. Therefore, if locators are defined to represent a shelf within a stockroom, on-hand balances on the system would show the item and quantity down to the physical location within the facility.

Oracle Inventory uses a key flexfield for stock locators. This presents a few limitations for its use. Only one locator flexfield definition is allowed per install. Therefore, if the stockroom (subinventory) wants to track material by row, bin and shelf, it will likely define a three-segment flexfield with segments for row, bin, and shelf. If locators are desired for another subinventory, even in another organization, the structure will again be 3 segments for row, bin and shelf. In addition to this limitation, locators must be unique within an organization; you cannot use the same locator in different subinventories within an organization, but you can use the same locator in subinventories in a different organization.

Thanks & Regards,

S.Grace Paul Regan