join between wip_discrete_jobs and mtl_system_items_b

join between wip_discrete_jobs and mtl_system_items_b

In this post , We will be discuss about the join between wip_discrete_jobs and mtl_system_items_b table wip_discrete_jobs table helps to store the manufacturing job information's. mtl_system_items_b helps to store the inventory item information's. Here in this post , I will share the detail sql query and the tables which helps to explain the complete join between wip_discrete_jobs and mtl_system_items_b.

 

Important Tables for creating join between wip_discrete_jobs and mtl_system_items_b

1.WIP_ENTITIES
2.wip_discrete_jobs   
3.MTL_SYSTEM_ITEMS_B
4.MTL_SYSTEM_ITEMS_B
5.wip_requirement_operations

 

 

 

Detail sql query using join between wip_discrete_jobs and mtl_system_items_b

SELECT WE.WIP_ENTITY_NAME,
  DECODE(wdj.job_type,1,'STANDARD',2,'NON_STANDARD',wdj.job_type)JOB_TYPE,
  MSB.SEGMENT1 ASSEMBLY_ITEM,
  MSB.DESCRIPTION,
  wdj.class_code,
  wdj.scheduled_start_date SCHEDULE_START_DATE,
  wdj.scheduled_completion_date SCHEDULE_END_DATE ,
  wdj.start_quantity,
  wdj.net_quantity,
  wdj.completion_subinventory,
  MSB1.SEGMENT1 COMPONENET_ITEM
 FROM apps.WIP_ENTITIES WE,
 apps.wip_discrete_jobs wdj,        
 apps.MTL_SYSTEM_ITEMS_B MSB ,
apps.MTL_SYSTEM_ITEMS_B MSB1,
apps.wip_requirement_operations wro
 WHERE WE.WIP_ENTITY_NAME='246412'
 AND we.wip_entity_id = wdj.wip_entity_id
 AND WE.PRIMARY_ITEM_ID=msb.INVENTORY_ITEM_ID
 AND WE.WIP_ENTITY_ID=WRO.WIP_ENTITY_ID
 AND WE.ORGANIZATION_ID=WRO.ORGANIZATION_ID
 AND wro.inventory_item_id=msb1.inventory_item_id
 AND wro.organization_id = msb1.organization_id