select iop.organization_code,
esi.item_number,
esi.DESCRIPTION,
esi.PRIMARY_UOM_CODE,
UOMT.UNIT_OF_MEASURE,
esi.ITEM_TYPE,
esi.INVENTORY_ITEM_STATUS_CODE,
decode(esi.LOT_CONTROL_CODE,1,'No',2,'Yes') Lot_Controlled,
esi.ENABLED_FLAG ,
ECB.SEGMENT1||','||ECB.SEGMENT2 Category,
ECSB.CATALOG_CODE
from EGP_SYSTEM_ITEMS esi,
INV_ORG_PARAMETERS IOP,
INV_UNITS_OF_MEASURE_TL UOMT,
INV_UNITS_OF_MEASURE_B UOMB,
EGP_ITEM_CATEGORIES EIC,
EGP_CATEGORIES_B ECB,
EGP_CATEGORY_SETS_B ECSB
where 1 = 1
and esi.organization_id = iop.organization_id
and UOMB.UOM_CODE = esi.PRIMARY_UOM_CODE
and UOMT.UNIT_OF_MEASURE_ID = UOMB.UNIT_OF_MEASURE_ID
and esi.INVENTORY_ITEM_ID = EIC.INVENTORY_ITEM_ID(+)
and esi.organization_id = EIC.organization_id(+)
and EIC.CATEGORY_ID = ECB.CATEGORY_ID(+)
and EIC.CATEGORY_SET_ID = ECSB.CATEGORY_SET_ID(+)
esi.item_number,
esi.DESCRIPTION,
esi.PRIMARY_UOM_CODE,
UOMT.UNIT_OF_MEASURE,
esi.ITEM_TYPE,
esi.INVENTORY_ITEM_STATUS_CODE,
decode(esi.LOT_CONTROL_CODE,1,'No',2,'Yes') Lot_Controlled,
esi.ENABLED_FLAG ,
ECB.SEGMENT1||','||ECB.SEGMENT2 Category,
ECSB.CATALOG_CODE
from EGP_SYSTEM_ITEMS esi,
INV_ORG_PARAMETERS IOP,
INV_UNITS_OF_MEASURE_TL UOMT,
INV_UNITS_OF_MEASURE_B UOMB,
EGP_ITEM_CATEGORIES EIC,
EGP_CATEGORIES_B ECB,
EGP_CATEGORY_SETS_B ECSB
where 1 = 1
and esi.organization_id = iop.organization_id
and UOMB.UOM_CODE = esi.PRIMARY_UOM_CODE
and UOMT.UNIT_OF_MEASURE_ID = UOMB.UNIT_OF_MEASURE_ID
and esi.INVENTORY_ITEM_ID = EIC.INVENTORY_ITEM_ID(+)
and esi.organization_id = EIC.organization_id(+)
and EIC.CATEGORY_ID = ECB.CATEGORY_ID(+)
and EIC.CATEGORY_SET_ID = ECSB.CATEGORY_SET_ID(+)
Thank you Priyanka. It is useful. Can you post Items related ER for fusion tables if possible.
ReplyDeletewhat are equivalent or mapping tables in fusion for these EBS tables MTL_TRANSACTION_ACCOUNTS, MTL_ITEM_LOCATIONS_KFV, MTL_TRANSACTION_TYPES, MTL_INTERORG_SHIP_METHODS
ReplyDeleteThank you, very much for shared it, it is very useful.
ReplyDeleteThank you so much.
ReplyDeleteReally Helpful
ReplyDeleteMy spouse and I love your blog and find almost all of your posts to be just what I’m looking for. Appreciating the persistence you put into your blog and the detailed information you provide. I found another one blog like you OA Framework.Actually I was looking for the same information on internet for Oracle OAF and came across your blog. I am impressed by the information that you have on this blog. Thanks once more for all the details.
ReplyDeleteSpot on with the query. Thanks so much
ReplyDeleteHow do we find the parent item class description for an item
ReplyDelete