Difference between Value sets and Lookups

1. Value sets can be attached to parameters of a concurrent program, whereas Lookups can't.
2. Certain types of Lookups are maintainable by the users too, for example HR Users will maintain "Ethnic Minority" lookups. Value Sets are almost never maintained by end users, with the exception of GL Flexfield codes. Value sets are usually maintained by System Administrators.
3. Value sets can contain values that are a result of an SQL Statement. Hence it is possible to make Value Set list of values dynamic. On the contrary, Lookup Codes are Static list of values.

BOM Tables?

BOM_BILL_OF_MATERIALS
BOM_INVENTORY_COMPONENTS
BOM_OPERATIONAL_ROUTINGS
BOM_OPERATION_SEQUENCES
BOM_OPERATION_RESOURCES

`Query to findout the cancelled WIP Jobs

select
hou.name "OPERATING_UNIT",
ood.ORGANIZATION_CODE,
wdj.WIP_ENTITY_NAME "JOB",
wdj.JOB_TYPE_MEANING "JOB_TYPE", 
msi.segment1 "ASSEMBLY",
msi.DESCRIPTION "ASSEMBLY_DESCRIPTION",
wdj.CLASS_CODE,
wdj.NET_QUANTITY,
wdj.STATUS_TYPE_DISP,
wdj.ATTRIBUTE_CATEGORY "CONTEXT",
wdj.ATTRIBUTE1 "SCHEDULE_GROUP",
wdj.ATTRIBUTE2 "PRODUCTION_LINE",
wdj.ATTRIBUTE3 "Printed",
wdj.ATTRIBUTE10 "SALES_ORDER",
wdj.ATTRIBUTE11 "SALES_ORDER",
wdj.ATTRIBUTE12 "CUSTOMER_NAME",
wdj.ATTRIBUTE15 "ENVS_OPEN_QUANTITY"
 from WIP_DISCRETE_JOBS_V wdj,
 mtl_system_items_b msi,
 org_organization_definitions ood,
 hr_operating_units hou
  where wdj.PRIMARY_ITEM_ID  = msi.INVENTORY_ITEM_ID
  and wdj.ORGANIZATION_ID = msi.ORGANIZATION_ID
  and wdj.ORGANIZATION_ID = ood.ORGANIZATION_ID
  and msi.ORGANIZATION_ID = ood.ORGANIZATION_ID
   and ood.OPERATING_UNIT = hou.ORGANIZATION_ID
 -- and wdj.WIP_ENTITY_NAME ='100476'    
  and wdj.STATUS_TYPE =7

link between mtl_system_items_b and mtl_item_revisions_vl

select
ood.organization_code,
msb.SEGMENT1 ITEM_NUMBER,
msb.DESCRIPTION,
msb.inventory_item_status_code ITEM_STATUS,
mir.REVISION_LABEL,
mir.EFFECTIVITY_DATE,
mir.IMPLEMENTATION_DATE,
mir.DESCRIPTION  REVISION_DESCRIPTION
from mtl_system_items_b msb,
mtl_item_revisions_vl mir,
org_organization_definitions ood
where msb.INVENTORY_ITEM_ID = mir.INVENTORY_ITEM_ID
and msb.organization_id = mir.organization_id
and msb.organization_id = ood.organization_id
and mir.organization_id = ood.organization_id
--and msb.INVENTORY_ITEM_ID =31333
--and  msb.organization_id=143
and mir.IMPLEMENTATION_DATE  = (select max(mirv.IMPLEMENTATION_DATE) from  mtl_item_revisions_vl mirv where mirv.INVENTORY_ITEM_ID = mir.INVENTORY_ITEM_ID
                                and  mir.organization_id =mirv.organization_id )                          
order by ood.organization_code,
msb.SEGMENT1