Re-Open a Closed Inventory Accounting Period

How to Re-Open a Closed Inventory Accounting Period

Applies to:

Oracle Cost Management - Version: 11.5.10.0 to 12.1.3 - Release: 11.5 to 12.1
Information in this document applies to any platform.
Scripts to Re-open an Inventory Accounting Period that has accidentally been closed.

Goal

If an Inventory Accounting Period has been closed prematurely by accident the following scripts
 can be used to re-open the accounting period if the corresponding GL period is open.  
Re-Opening a closed period will allow transactions to be process for that period.

The re-opening of a closed period should not be used to back date transactions, the system
 allow back dated transactions but this may cause discrepancies between inventory and GL. 
Any discrepancies caused by back dated transactions are not supported by Oracle and would
 have to be resolved with a manual adjustment to the General Ledger.

Solution

-- A script to list all inventory periods for a specific organization
-- A script to reopen closed inventory accounting periods in 11.5.10
-- The script will reopen all inventory periods for the specified
-- Delete scripts to remove the rows created during the period close process
 to prevent duplicate rows
-- organization starting from the specified accounting period.
-- The organization_id can be obtained from the MTL_PARAMETERS table.
-- The acct_period_id can be obtained from the ORG_ACCT_PERIODS table. 

   

   

   

SELECT acct_period_id period, open_flag, period_name name,  
      period_start_date, schedule_close_date, period_close_date  
      FROM  org_acct_periods  
      WHERE organization_id = &org_id  
      order by 1,2;

Update Script :

UPDATE org_acct_periods 
SET open_flag = 'Y', 
period_close_date = NULL, 
summarized_flag = 'N' 
WHERE organization_id = &&org_id 
AND acct_period_id >= &&acct_period_id; 

Delete Script:

DELETE mtl_period_summary 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id;

 
DELETE mtl_period_cg_summary 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id; 

DELETE mtl_per_close_dtls 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id; 

DELETE cst_period_close_summary 
WHERE organization_id = &org_id 
AND acct_period_id >= &acct_period_id;  
commit

Cannot Create/Assign Item in Master Items Error

This document is to help those with the following errors when adding an item / assigning an existing item to a new organization via the Master Items form INVIDITM.fmb or the Item Open Interface INCOIN.

ERROR received in the application (Master Items Form):
APP-INV-05479: Please commit or clear your changes first.

Do you want to save the changes that you have made? (but won't allow save)

ERROR received in Import Items log file (with debug on):
INCOIN fails (ERROR) on CREATE with process_flag = 4 and only error message in log file is:  INVPOPIF.inopinp_OI_process_create: done INVPPROC.inproit_process_item: ret_code=1

The trace file shows that we fail to insert a row into mtl_system_items_b
There are NO errors in the tracefile: No ORA- , APP-, FND- or ROLLBACK.

ERROR received in trace file shows ORA-0001 on the following statement:

INSERT INTO MTL_SYSTEM_ITEMS_TL ( INVENTORY_ITEM_ID, ORGANIZATION_ID, LANGUAGE, SOURCE_LANG,
DESCRIPTION, LONG_DESCRIPTION, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN) SELECT :B2 , :B1 , MSI.LANGUAGE, MSI.SOURCE_LANG,
LTRIM(RTRIM(MSI.DESCRIPTION)), LTRIM(RTRIM(MSI.LONG_DESCRIPTION)), :B7 , :B6 , :B5 , :B4 , :B3
FROM MTL_SYSTEM_ITEMS_TL MSI, MTL_PARAMETERS MP WHERE MSI.INVENTORY_ITEM_ID = :B2 AND
MSI.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID AND MP.ORGANIZATION_ID = :B1

Cause

There are a variety of conditions that can cause this issue.

CONDITIONS SPECIFIC TO IMPORT ITEMS (INCOIN):

1) Space issue on the Item Open Interface tables.

2) Required attribute missing in the MTL_SYSTEM_ITEMS_INTERFACE on CREATE transactions.

CONITIONS COMMON TO THE IMPORT ITEMS process and MASTER ITEMS form:

3) Space issue on the receiving data tables.

4) Existing orphaned rows for deleted items that are being re-added. The root cause for this issue is not known at this time.

5) Unique Custom Indexes with a unique constraint violation.

6) Custom Triggers causing insert into MTL_SYSTEM_ITEMS_B to fail.

Steps to resolve each numbered condition above.

CONDITIONS SPECIFIC TO IMPORT ITEMS (INCOIN):

1) To resolve space issues on the Item Open Interfaces tables, truncate the Interface tables (This should be done every 100k rows processed via Import Items):

TRUNCATE TABLE INV.MTL_SYSTEM_ITEMS_INTERFACE;
TRUNCATE TABLE INV.MTL_INTERFACE_ERRORS;
TRUNCATE TABLE INV.MTL_ITEM_REVISIONS_INTERFACE;
TRUNCATE TABLE INV.MTL_ITEM_CATEGORIES_INTERFACE;

Note: If there is data that needs to retained in the interface tables, back_up the required data, truncate the tables, and reload the data. It is recommended that rows with process_flag = 7 (processed rows) should always be purged.

2) If CREATE transactions are being loaded into the MTL_SYSTEM_ITEMS_INTERFACE, make sure that the 6 required attributes are populated:

PROCESS_FLAG, SET_PROCESS_ID, TRANSACTION_TYPE, ORGANIZATION_ID (or CODE), SEGMENT1 (or INVENTORY_ITEM_ID) and DESCRIPTION.

CONITIONS COMMON TO IMPORT ITEMS process and MASTER ITEMS form:

3) Check and resolve space issues on the following tables (use following script and confir with DBA on results):

select owner,segment_name,segment_type,tablespace_name,blocks,extents
from dba_segments
where segment_name IN ('MTL_SYSTEM_ITEMS_B','MTL_SYSTEM_ITEMS_TL',
'MTL_ITEM_REVISIONS_B','MTL_ITEM_CATEGORIES_B',
'MTL_ITEM_REVISIONS_TL','MTL_ITEM_CATEGORIES','MTL_SYSTEM_ITEMS_INTERFACE', 'MTL_ITEM_REVISIONS_INTERFACE','MTL_ITEM_CATEGORIES_INTERFACE')
order by segment_name;

4) Execute the following scripts to detect orphaned rows for which there is no corresponding item/organizaiton entry in the MTL_SYSTEM_ITEMS_B table.

NOTE: Request a data fix if any of these scripts return a count greater than zero. Supply results of the scripts when creating the service request and mention this note.

SQL-1:

SELECT COUNT(*)
FROM mtl_system_items_tl msitl
WHERE NOT EXISTS (SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = msitl.inventory_item_id
AND item.organization_id = msitl.organization_id );

SQL-2:

SELECT COUNT(*)
FROM mtl_item_revisions_b mirb
WHERE NOT EXISTS ( SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = mirb.inventory_item_id
AND item.organization_id = mirb.organization_id);

SQL-3:

SELECT COUNT(*)
FROM mtl_item_revisions_tl mirtl
WHERE NOT EXISTS (SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = mirtl.inventory_item_id
AND item.organization_id = mirtl.organization_id);

SQL-4:

SELECT COUNT(*)
FROM mtl_item_categories mic
WHERE NOT EXISTS (SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = mic.inventory_item_id
AND item.organization_id = mic.organization_id);

SQL-5:

SELECT COUNT(*)
FROM mtl_pending_item_status mpis
WHERE NOT EXISTS (SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = mpis.inventory_item_id
AND item.organization_id = mpis.organization_id);

SQL-6:

SELECT COUNT(*)
FROM cst_item_costs cic
WHERE NOT EXISTS (SELECT 'X' FROM mtl_system_items_b item
WHERE item.inventory_item_id = cic.inventory_item_id
AND item.organization_id = cic.organization_id);

SQL-7:

SELECT COUNT(*)
FROM eni_oltp_item_star eois
WHERE NOT EXISTS (SELECT NULL FROM mtl_system_items_b item
WHERE item.inventory_item_id = eois.inventory_item_id
AND item.organization_id = eois.organization_id);


5) Run the following script to look for custom indexes.

NOTE: Any unique index could cause this issue.  Drop custom indexes and try to recreate the issue.

SELECT SUBSTR(a.index_name, 1, 30) indname,
SUBSTR(a.column_name, 1, 20) colname,
SUBSTR(a.TABLE_NAME, 1, 20) tblname,
SUBSTR(to_char(a.column_position), 1, 2) colpos,
SUBSTR(a.index_owner, 1, 8) indown,
SUBSTR(b.tablespace_name, 1, 10) tbspace,
b.distinct_keys distkeys,
b.uniqueness uniq
FROM all_ind_columns a,
all_indexes b
WHERE a.TABLE_NAME LIKE UPPER('mtl_%item%')
and a.index_name NOT LIKE 'MTL_%'
AND a.index_name = b.index_name
AND a.TABLE_NAME = b.TABLE_NAME
ORDER BY a.table_name, a.index_name, colpos;

6) Run the following script to look for custom triggers on MTL tables with item information.

NOTE:  Disable any custom triggers and try to recreate the issue.

select TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME,STATUS
from all_triggers where table_name like '%MTL_%ITEM%'
AND SUBSTR(TRIGGER_NAME,1,3) != 'MTL'
AND SUBSTR(TRIGGER_NAME,1,3) != 'EGO'
AND SUBSTR(TRIGGER_NAME,1,3) != 'AX_'
AND SUBSTR(TRIGGER_NAME,1,3) != 'GMF'
AND SUBSTR(TRIGGER_NAME,1,3) != 'JA_'
AND SUBSTR(TRIGGER_NAME,1,3) != 'MRP';

Item Has 2 Category Assignments On Organization Level

Item Has 2 Category Assignments On Organization Level

ACTUAL BEHAVIOR
---------------
Item has 2 category assignments on organization level while it is master controlled.
The assignment has the same category set while "Allow multiple item category assignments" is disabled.
On master level there is only one category. It only exists on 2 organization levels.

EXPECTED BEHAVIOR
-----------------------
Get a single category for the item.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. Inventory > Items > Master Item > Search item (example) AB54888 > Tools > Categories > Here you see only 1 assignement of category (example) "PRODUCT"
2. Inventory > Items > Master Item > Search item (example) AB54888 > Tools > Organization assignement > select organization > click org attributes > Tools > Categories > Here you see only 2 assignement of category (for example) "PRODUCT"

Test the Result 

You can use a query like the following:

• SELECT * from FROM mtl_item_categories WHERE inventory_item_id = &inv_item_id;

APP-FND-01934 ORA-00001: Unique Constraint Error at Master Items

APP-FND-01934 ORA-00001: Unique Constraint Error at Master Items

Resolution:

This error could be solved by recompiling System Items Key Flexfield (KFF).
To recompile Inventory Flexfields, please do the following:
To recompile Inventory Flexfields, please do the following:

1. Go to: Inventory responsibility.

2. Navigate to: Setup > Flexfields > Key > Segments 

3. Query up the Application: Inventory and Flexfield title: "System Items".

4. Click off Freeze Flexfield Definition. Click OK to the caution message. 

5. Click again the Freeze Flexfield Definition radio button. Click ok to the Caution message. 

6. Click on Compile. This will kick off concurrent request Flexfield View Generator. Verify that it completes without error. 

7. Retest the issue.

If the KFF recompile does not solve the error, please go to the alternate solution below: 

1. Take a backup of ENI.ENI_OLTP_ITEM_STAR. 

2. Truncate table ENI.ENI_OLTP_ITEM_STAR. 

3. Go to Business Intelligence Administrator > Daily Processing > Run. 

2. Choose Request Set and click OK. 

4. In the Find screen enter: Load Item Dimension Hierarchy. 

5. Run "Load Item Dimension Hierarchy" concurrent program. 

6. Retest the issue. 

7. Migrate the solution as appropriate to other environment

COGS Recognition and Concurrent Processes

COGS Recognition and Concurrent Processes

The matching and synchronization of the earned and deferred components of sales order revenue and COGS is accomplished by running the following COGS recognition concurrent processes at user-defined intervals:

• Record Order Management Transactions
• Collect Revenue Recognition Information
• Generate COGS Recognition Events

Record Order Management Transactions

The Record Order Management Transactions concurrent process picks up and costs all uncosted sales order issue and RMA return transactions and creates a record for each new order line in the costing COGS recognition matching table. This process is not mandatory. If you don’t run this process, then the cost processor will select and cost the uncosted sales order issues and insert them in the COGS matching table. This process can be used if you need to process the COGS recognition transactions at shorter intervals than the cost processor.

To record order management transactions

1. Navigate to the Record Order Management Transactions window. The Parameters window appears.
2. Select a Ledger from the list of values.
   
3. Click Submit to run the request.

Collect Revenue Recognition Information

The Collect Revenue Recognition Information concurrent process calls an Oracle Receivables API to retrieve the latest revenue recognition percentage of all invoiced sales order lines in Oracle receivables for a specific ledger and with activity dates within a user-specified date range. This process must be run before the Generate COGS recognition Event concurrent process.

To collect revenue recognition information

1. Navigate to the Collect Revenue Recognition Information window. The Parameters window appears.
   
2. Select a Ledger to restrict revenue recognition events within a specific ledger..
3. This concurrent request has two date parameters that allow you to restrict processing of revenue recognition events to a range of dates:
   Start Date: Transactions prior to this date are not selected. The default value is the date of the last successful run of the concurrent request.
   End Date: Transactions after this date are not selected.
4. Choose OK.
5. Choose Submit to run the request.
   

Generate COGS Recognition Events

The Generate COGS Recognition Events concurrent request compares the COGS recognition percentage for each sales order line and accounting period combination to the current earned revenue percentage. When the compared percentages are different, the process raises a COGS recognition event and creates a COGS recognition transaction in Oracle Inventory that adjusts the ratio of earned and deferred COGS to match that of earned and deferred revenue. You must run this process after completion of the Collect Revenue Recognition Information concurrent process.

To generate COGS recognition events

1. Navigate to the Generate COGS Recognition Events window. The Parameters window appears.
2. Select a Ledger from the list of values.
   
3. Click Submit to run the request.

Period Close Considerations

• Ensure that there are open GL period in each ledger for the periods in which you run the concurrent process.
• In perpetual costing organizations, you can create backdated COGS recognition events and transactions in open and closed inventory periods.
• In periodic costing organizations, only events and transactions that are within the current periodic period’s start and end dates will be processed.
• The inventory period close process must be synchronized with Oracle Receivables period close to ensure proper recognition of revenue and COGS in an accounting period.
• In periodic costing organizations, you cannot close the accounting period if Oracle Receivables has not soft closed its accounting period. Attempting to do so generates an error message. This condition ensures that all backdated revenue recognition transactions in Oracle Receivables are processed in costing prior to period close.
• In periodic costing organizations, run the Generate COGS Recognition Events concurrent process after the close of an inventory accounting period to ensure that all COGS recognition events have been processed and costed. Rerun the Periodic Cost Processor and Periodic Distribution Processor. Costing performs a validation to ensure that all organizations in a Periodic Average Costing (PAC) cost group have no mismatched revenue and COGS order lines, and generates an error message if unmatched lines are found.

Cost Update Process

1. Navigate to the Cost Update window. The Start Cost Update Process window displays automatically. This window lets you begin the update for a specified calendar, period, and cost method. Specify the criteria by which OPM will select the costs to be processed.
2. Enter the Legal Entity. Transactions for all organizations linked to this legal entity are selected and included in the cost update process.
3. Enter the code for the Calendar for which the cost update is processed. Costs are updated for the legal entity and the cost type linked to this calendar. Required.
4. Enter the cost Period for which the cost update is effective. Note that closed cost periods are locked from the Cost Update process. Required
5. Period Status displays the status of the calendar period (either Open, Closed, or Frozen). You cannot edit this field.
6. Enter the Cost Type. This should be the same cost type specified on the Fiscal Policy window.
7. If you enter that the update is Final, then the period is marked as frozen at the end of the process. This locks the component costs for the specified period.
   When the costing period is frozen, the following situations apply:
   
   • You cannot update the same period costs again (however, you can update new item costs)
   • You can only inquire on cost component details for the current period (however, you can enter new cost details)
   • Only the costs of new items may be calculated and updated.
   • You can copy costs From a frozen costing period, but not To a period that is frozen.
   • Overhead details may be queried only.
   • Resource cost details may be queried only.
8. Enter the date and time that the Cost Update process will start in Start Date. To start the process immediately, click Now. To start the process at a particular date, click Specific Date. Enter the date you want the cost update to run.
9. Click Accept to run the process.
   A reference number is generated by OPM. Note the reference number.
10. Cost Update Reference Number displays a unique identifier number for each individual cost update process assigned by OPM. You cannot edit the entry