Pending Transaction Clear in 11.5.10

11.5.10 RECEIVING OPEN INTERFACE를 통해 RETURN처리하는 방법
==================================================



PURPOSE
-------
11.5.10에서 Receiving Open Interface를 통해 Return처리하는 방법을 기술함.

Explanation
-----------
11.5.10 is ROI supports for Standard Purchase Order
. RETURN to RECEIVING transactions
. RETURN to VENDOR transactions

1. Receipt routing = Standard Receipt일 경우

A) perform a "RETURN to RECEIVING" for a standard Purchase Order
through ROI
TRANSACTION_TYPE = RETURN TO RECEIVING
PARENT_TRANSACTION_ID = (Transaction Id for DELIVER transaction)

B) perform a "RETURN to VENDOR" for a standard Purchase Order
through ROI
TRANSACTION_TYPE = RETURN TO VENDOR
PARENT_TRANSACTION_ID = (Transaction Id for RECEIVE transaction)


2. Receipt routing = Direct Receipt일 경우,
==> only 1 step return is needed,
TRANSACTION_TYPE = RETURN TO VENDOR
TRANSACTION_ID = (Transaction Id for DELIVER transaction)

3. a Lot and Serial Controlled Item의 경우,
다음 table들에 data를 Insert해 주어야 합니다.

RCV_TRANSACTIONS_INTERFACE,
MTL_TRANSACTIONS_LOTS_INTERFACE,
MTL_SERIAL_NUMBERS_INTERFACE.


TEST
------

step1> PO등록
1) Standard Purchase Order number: # 8415
Inventory Item = Lot-Serial-Controlled-1
Receipt Routing = Standard Receipt
Destination Type Code = Inventory

step2) Receipt처리
Receipt number :# 7970
The deliver transaction's quantity=1
item=Lot-Serial-Controlled-1
Lot Number=S336
Serial Number = SN0021

step3> Return처리

A) "RETURN to RECEIVING" for PURCHASE ORDER Example

SQL>
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
EMPLOYEE_ID,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
FROM_ORGANIZATION_ID,
FROM_SUBINVENTORY,
FROM_LOCATOR_ID,
SOURCE_DOCUMENT_CODE,
PARENT_TRANSACTION_ID,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
DESTINATION_TYPE_CODE,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
VALIDATION_FLAG
)
VALUES
(rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO RECEIVING', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
1, --QUANTITY
'Each', --UNIT_OF_MEASURE
169845, --ITEM_ID
13706, --EMPLOYEE_ID
477550, --SHIPMENT_HEADER_ID
484510, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
7927, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
524018, --PARENT_TRANSACTION_ID
40924, --PO_HEADER_ID
46915, --PO_LINE_ID
81963, --PO_LINE_LOCATION_ID
84349, --PO_DISTRIBUTION_ID
'INVENTORY', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --VALIDATION_FLAG
);


INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
( TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
VALUES
( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--
TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'S366', --LOT_NUMBER
1, --TRANSACTION_QUANTITY
1, --PRIMARY_QUANTITY
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --
SERIAL_TRANSACTION_TEMP_ID
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --
PRODUCT_TRANSACTION_ID
);

INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
( TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,--
TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'SN0021', --FM_SERIAL_NUMBER
'SN0021', --TO_SERIAL_NUMBER
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --
PRODUCT_TRANSACTION_ID
);
Commit;

B) "RETURN to VENDOR" Transaction for PURCHASE ORDER

INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
EMPLOYEE_ID,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
FROM_ORGANIZATION_ID,
FROM_SUBINVENTORY,
FROM_LOCATOR_ID,
SOURCE_DOCUMENT_CODE,
PARENT_TRANSACTION_ID,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
DESTINATION_TYPE_CODE,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
VALIDATION_FLAG
)
VALUES
(rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO VENDOR', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
1, --QUANTITY
'Each', --UNIT_OF_MEASURE
169845, --ITEM_ID
13706, --EMPLOYEE_ID
477550, --SHIPMENT_HEADER_ID
484510, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
7927, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
524017, --PARENT_TRANSACTION_ID
40924, --PO_HEADER_ID
46915, --PO_LINE_ID
81963, --PO_LINE_LOCATION_ID
84349, --PO_DISTRIBUTION_ID
'RECEIVING', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --VALIDATION_FLAG
);


INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
( TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID
)
VALUES
( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--
TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'S366', --LOT_NUMBER
1, --TRANSACTION_QUANTITY
1, --PRIMARY_QUANTITY
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --
SERIAL_TRANSACTION_TEMP_ID
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --
PRODUCT_TRANSACTION_ID
);

INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
( TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,--
TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'SN0021', --FM_SERIAL_NUMBER
'SN0021', --TO_SERIAL_NUMBER
'RCV', --PRODUCT_CODE
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --
PRODUCT_TRANSACTION_ID
);
Commit;


step4) RUN the Receiving Transaction Processor with (GROUP_ID=30053)


step5) Check a data
Navigate to Receiving / Receiving Transactions Summary form

Interview Preparation - Oracle - Inventory Module

Inventory interview question

 

1. What is item import? How is it done?

 The process of converting inventory items from another inventory system, migrating assembly and component items from a legacy manufacturing system, converting purchase items from a custom purchasing system and importing new items from a product data management package into Oracle Inventory. This import mechanism is achieved through a concurrent program called Open Item Interface.

 

Custom programs are executed prior to item interface and this gets data from the external systems into the interface tables such as MTL_SYSTEM_ITEMS _INTERFACE and MTL_ITEM_REVISIONS_INTERFACE. Item Interface program is then run which actually imports the items and revision information from the above mentioned interface face tables into the base tables such as MTL_SYSTEM_ITEMS_B, MTL_ITEM_REVISIONS. Item Interface assigns defaults and validates the data to ensure data integrity before feeding data into base tables.

 

2. What are Interface and Base Tables?

 Interface Tables            : MTL_SYSTEM_ITEMS_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE (Oracle Defaults)
MTL_ITEM_CATEGORIES_INTERFACE (Oracle has Provided certain default categories, if not specified)
MTL_INTERFACE_ERRORS
Base Tables : MTL_SYSTEM_ITEMS_B (Segment 1 stores Model # of Item)
MTL_ITEM_ATTRIBUTES, MTL_ITEM_CATEGORIES,  MTL_ITEM_LOCATIONS,    MTL_ITEM_REVISIONS
MTL_ITEM_STATUS (Status Active/Engineer )
MTL_ITEM_SUB_INVENTORIES

3.  What are Item Attributes?

 Attributes are the specific characteristics associated to every item, namely order cost, item status, revision control, COGS account etc.

 

4.       What are Templates?

 Templates are the defined set of attributes that can be used over and over to create similar items. Templates initial definition of items easier. Oracle has provided certain predefined templates such as (ATO MODEL, ATO OPTION CLASS, and FINISHED GOOD etc). Templates can also be User defined .

 

5)   What are Status Codes?

 Statuses are used to provide default values to certain item attributes to control the functionality of an item. Statuses typically default 8 item attributes namely, BOM allowed, build in WIP, Customer orders enabled, internal orders enabled, invoice enabled, transactable, purchasable, stockable.

Different status types include Active, Inactive, Engineer, obsolete, Phase-out, Prototype, OPM.

 

6.  What are Categories and Category Sets?

  Category is a code used to group items with similar characteristics such as plastics, metals or glass items etc.

A subset of categories grouped together is termed as a Category set. Typical category sets include purchasing, materials, costing and planning.

 8.   What are Lot Numbers and Serial Numbers?

 Lot number is a number that identifies a specific batch of items.
Serial Number is a number assigned to each unit of an item and used to track the item.

 9.  What are Locators?

 A locator is a physical area with in the sub inventory where you store material such as a row, aisle, shelf, or a bin etc.

10.   What is a Sub Inventory?
It is a subdivision of an organization representing a physical area or a logical  grouping of items such as store room or a receiving dock.

 11.     What are the flexfields in Inventory module?

 Item Key Flexfield, Category Key Flexfield.

 12. While importing items from the legacy system through items interface what profile options do u set.

There are two profile options that we need to check, before running the Item Import. They are

 i) PRIMARY_UNIT_OF_MEASURE from INV: Define Primary Unit of Measure

ii) INVENTORY_ITEM_STATUS_CODE from INV: Define Item Status

Oracle Back to Back Order in Order Management

Back Orders

·         The Oracle "term" backorder is a "status" on the order line or delivery line indicating that you have tried to release an order for picking in your warehouse, but that the pick release was UNSUCCESSFUL because there was no available inventory.(Backorder can be partial or complete). The Oracle term backorder does NOT mean that you have open purchase orders for the out-of-stock item from your vendors.

·         The term backorder is also used in business a little differently than in Oracle. The term "An item is on backorder" usually means that the item is not in stock, but the shipping company has already placed purchase orders from their suppliers to restock the item.

·         Back Order is when you do not fulfill the Sales Order, or if the inventory is out of stock for delivery to customer.

Back to Back Orders (B2B)

In Drop-ship items are directly shipped to customer from the supplier and only logical receiving is performed in Oracle. In B2B orders items are physically received to Oracle from supplier and later they are shipped to customers.

Ex: When an order for Laptop is placed, you cannot send laptop and charger differently to the customer. If the company is not interested in maintaining the inventory of chargers, B2B is perfect solution as laptop charger order will go out when ever an order is created for laptop.And the charger is received to oracle and can be shipped with the Laptop.

Flow status code of the order line --FSC
Item reservation type ….IRT

1. Enter sales order …source code Internal
2. Book the order, at this time FSC – Supply Eligible
3. Perform progress order … and FSC –PO Req. Requested & IRT inventory
4. Req. Import –FSC –PO Req. Created & IRT external requisition
5. Auto Create PO –FSC – PO created & IRT PO order
6. Perform receiving transaction--- FSC Awaiting shipping& IRT Inventory
After this complete the order as normal sales order.

Important Notes:
Items used in Back to back order should be ATO enabled, Build in WIP flag checked and in general planning set the Buy flag.
In B2B order at some point we will physically receive goods before shipping them out, where as in Drop ship goods are directly shipped to Customer
Drop ship order may connect to more than one PO but B2B is connected to single PO.

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