Bills of Material Details Report

Bills of Material Details Report :

SELECT UNIQUE

MSI.SEGMENT1 " BILL NAME #",

MSI.DESCRIPTION " BILL DESC #",

MSI.PRIMARY_UNIT_OF_MEASURE "PRIMARY UOM #",

MSI.ITEM_TYPE "BILL TYPE #",

TO_CHAR(BBOM.CREATION_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "BILL CREATION DATE #",

FU1.USER_NAME "BILL CREATED BY #",

TO_CHAR(BBOM.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "BILL UPDATED DATE #",

FU2.USER_NAME "BILL LAST UPDATED BY #",

BIC.ITEM_NUM "ITEM NUMBER #",
BIC.OPERATION_SEQ_NUM "OPERATION SEQ NUMBER #",

MSI1.SEGMENT1 "COMPONENT ITEM NAME #",

MSI1.ITEM_TYPE "COMP TYPE #",

MSI1.DESCRIPTION "COMPONENT ITEM DESC #",

TO_CHAR(BIC.CREATION_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "COMPONENT LINE CREATION DATE #",

FU3.USER_NAME "COMPONENT LINE CREATED BY #",

TO_CHAR(BIC.LAST_UPDATE_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "COMPONENT LINE UPDATED DATE #",

FU4.USER_NAME "COMPON LINE LAST UPDATED BY #",

BIC.COMPONENT_QUANTITY "COMPONENT QUANTITY#",

TO_CHAR(BIC.DISABLE_DATE,'DY DD-MON-YYYY HH:MI:SS AM') "DISABLE DATE#"

FROM

APPS.BOM_BILL_OF_MATERIALS BBOM,

APPS.MTL_SYSTEM_ITEMS_B MSI,

APPS.BOM_INVENTORY_COMPONENTS BIC,

APPS.MTL_SYSTEM_ITEMS_B MSI1,

APPS.FND_USER FU1,

APPS.FND_USER FU2,

APPS.FND_USER FU3,

APPS.FND_USER FU4

WHERE

MSI.INVENTORY_ITEM_ID = BBOM.ASSEMBLY_ITEM_ID

AND BBOM.CREATED_BY=FU1.USER_ID

AND BBOM.LAST_UPDATED_BY=FU2.USER_ID

AND BIC.CREATED_BY=FU3.USER_ID

AND BIC.LAST_UPDATED_BY=FU4.USER_ID

AND MSI.ORGANIZATION_ID = BBOM.ORGANIZATION_ID

AND MSI.ORGANIZATION_ID = 121

AND MSI1.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID

AND MSI1.ORGANIZATION_ID = 121

AND BBOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID

AND BIC.DISABLE_DATE IS NULL

AND MSI.SEGMENT1 ='SRVS' -- Bill Name

ORDER BY

MSI.SEGMENT1,

BIC.ITEM_NUM,

BIC.OPERATION_SEQ_NUM

Inventory Part Number Report with Catalog Group Name

Inventory Part Number Report with Catalog Group Name :- 

 

SELECT  UNIQUE OOD.ORGANIZATION_NAME "ORGANIZATION NAME #", 
MSI.INVENTORY_ITEM_ID "INVENTORY_ITEM_ID #",

MSI.SEGMENT1 "PART NUMBER#",

MSI.DESCRIPTION "DESCRIPTION #",

TL.LONG_DESCRIPTION  "LONG DESCRIPTION #",

MSI.INVENTORY_ITEM_STATUS_CODE "INVENTORY ITEM STATUS CODE #",

MSI.PRIMARY_UNIT_OF_MEASURE "PRIMARY_UOM #",

MSI.ITEM_CATALOG_GROUP_ID "CATALOG GROUP ID #",

MICGB.SEGMENT1 "CATALOG GROUP NAME #",              

TO_CHAR(FU.USER_NAME) "ITEM CREATED BY #",       

TO_CHAR(MSI.CREATION_DATE,'DD-MON-YYYY') "ITEM CREATION DATE#" ,

TO_CHAR(FU1.USER_NAME) "ITEM LAST UPDATED BY #",

TO_CHAR(MSI.LAST_UPDATE_DATE,'DD-MON-YYYY ') "ITEM LAST UPDATED DATE #"

FROM
INV.MTL_SYSTEM_ITEMS_B MSI,

APPS.MTL_SYSTEM_ITEMS_TL TL,

APPS.ORG_ORGANIZATION_DEFINITIONS OOD,

APPS.FND_USER FU,

APPS.FND_USER FU1,

APPS.MTL_ITEM_CATALOG_GROUPS_B MICGB

WHERE
               OOD.ORGANIZATION_ID=MSI.ORGANIZATION_ID

AND       MSI.CREATED_BY=FU.USER_ID

AND       MSI.LAST_UPDATED_BY=FU1.USER_ID

AND       OOD.ORGANIZATION_ID = 122

AND       MSI.ITEM_CATALOG_GROUP_ID =  MICGB.ITEM_CATALOG_GROUP_ID (+)

AND       MSI.INVENTORY_ITEM_ID=TL.INVENTORY_ITEM_ID

AND       MSI.ORGANIZATION_ID=TL.ORGANIZATION_ID

AND       MSI.ORGANIZATION_ID=122

AND       MSI.SEGMENT1 = ‘PART NUMBER’

ORDER BY MSI.ITEM_CATALOG_GROUP_ID ,MSI.INVENTORY_ITEM_ID

The SQL Query to Find the On Hand Quantity

The SQL Query to Find the On Hand Quantity :-

SELECT   MOQ.ORGANIZATION_ID,

                  MSI.SEGMENT1 "ITEM NAME #",    

                  MSI.DESCRIPTION "ITEM DESC #",

                  SUM (MOQ.TRANSACTION_QUANTITY)

FROM      APPS.MTL_ONHAND_QUANTITIES MOQ,

                  INV.MTL_SYSTEM_ITEMS_B MSI

WHERE  MSI.INVENTORY_ITEM_ID   = MOQ.INVENTORY_ITEM_ID (+)

AND        MSI.ORGANIZATION_ID     = MOQ.ORGANIZATION_ID (+)

AND        MOQ.ORGANIZATION_ID    = 122

AND        MSI.SEGMENT1 IN (

'AIRSET-P',

'AIRSETMTGKIT-P')

GROUP BY MOQ.ORGANIZATION_ID, MSI.SEGMENT1 ,  MSI.DESCRIPTION

UNION

SELECT  MMT.ORGANIZATION_ID,

                 MSI.SEGMENT1 "ITEM NAME #",    

                 MSI.DESCRIPTION "ITEM DESC #",

                 SUM (MMT.TRANSACTION_QUANTITY)

FROM    APPS.MTL_MATERIAL_TRANSACTIONS MMT,

                INV.MTL_SYSTEM_ITEMS_B MSI

WHERE  MSI.INVENTORY_ITEM_ID   = MMT.INVENTORY_ITEM_ID (+)

AND       MSI.ORGANIZATION_ID     = MMT.ORGANIZATION_ID (+)

AND       MMT.ORGANIZATION_ID    = 122

AND       MSI.SEGMENT1 IN (

'AIRSET-P',

'AIRSETMTGKIT-P'

)

GROUP BY MMT.ORGANIZATION_ID, MSI.SEGMENT1 ,  MSI.DESCRIPTION

Item Import Error Problem Solution - The following user ID does not have a person ID assigned to it

Item Import Error Problem Solution - The following user ID does not have a person ID assigned to it

while loading data into MTL_SYSTEM_ITEMS_INTERFACE and submit item import; while running Item import program, I got error as below.

Errors:-

************************************

TRANSACTION ID : 14483609

ORGANIZATION ID :           ----This is master organization

TABLE NAME : MTL_SYTEM_ITEMS_INTERFACE

COLUMN NAME : LOGIN_USER_ID

MESSAGE NAME : INV_IOI_ERR

ERROR MESSAGE : The following user ID does not have a person ID assigned to it:56236

 ----USER

************************************

TRANSACTION ID : 14483609

ORGANIZATION ID :           ----This is master organization

TABLE NAME : MTL_ITEM_REVISIONS_INTERFACE

COLUMN NAME : ITEM_ID

MESSAGE NAME : INV_IOI_REV_NO_ITEM

ERROR MESSAGE : The revision entered is for an item that does not exist in MTL_SYSTEM_ITEMS nor MTL_SYSTEM_ITEMS_INTERFACE.

---------------------------------------------------------------------------

End of log messages from FND_FILE

--------------------------------------------------------------------------- 

Problem solution:-

User that we are log in for running the items import program. Must be required Employee person but we are not assigned to that in the person field; any HRMS person should be assigned. For example I have attached here SCOTT.TIGER employee name. And now I add Employee person accordingly; we are not got same type of error.  

Please, assign concerned HRMS Employee Person to respective User.

Thanks,

Swapnil.