VIEW: APPS.MRP_WIP_JOBS_VIEW

Source

View Text - Preformatted

SELECT JOBS.PRIMARY_ITEM_ID , ITEMS.ORGANIZATION_ID , ITEMS.COMPILE_DESIGNATOR , JOBS.WIP_ENTITY_ID , WIP.WIP_ENTITY_NAME , JOBS.MPS_NET_QUANTITY , GREATEST(0, (JOBS.MPS_NET_QUANTITY - JOBS.QUANTITY_COMPLETED - JOBS.QUANTITY_SCRAPPED)) , JOBS.NET_QUANTITY , GREATEST(0, (JOBS.NET_QUANTITY - JOBS.QUANTITY_COMPLETED - JOBS.QUANTITY_SCRAPPED)) , TRUNC(JOBS.MPS_SCHEDULED_COMPLETION_DATE) , TRUNC(JOBS.SCHEDULED_COMPLETION_DATE) , JOBS.QUANTITY_SCRAPPED , JOBS.QUANTITY_COMPLETED , GREATEST(0, DECODE(JOBS.JOB_TYPE, 1, (JOBS.MPS_NET_QUANTITY - JOBS.QUANTITY_COMPLETED - JOBS.QUANTITY_SCRAPPED) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE), -1, 0, NVL(ITEMS.SHRINKAGE_RATE, 0)), 0)) , GREATEST(0, DECODE(JOBS.JOB_TYPE, 1, (JOBS.NET_QUANTITY - JOBS.QUANTITY_COMPLETED - JOBS.QUANTITY_SCRAPPED) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE), -1, 0, NVL(ITEMS.SHRINKAGE_RATE, 0)), 0)) , JOBS.FIRM_PLANNED_FLAG , TRUNC(JOBS.SCHEDULED_START_DATE) , JOBS.BOM_REVISION , JOBS.STATUS_TYPE , JOBS.JOB_TYPE , JOBS.DEMAND_CLASS , items.full_pegging , jobs.project_id ,jobs.task_id ,jobs.schedule_group_id ,jobs.build_sequence ,jobs.line_id ,jobs.alternate_routing_designator ,jobs.alternate_bom_designator ,1 , jobs.end_item_unit_number FROM WIP_ENTITIES WIP, MRP_SYSTEM_ITEMS ITEMS, WIP_DISCRETE_JOBS JOBS WHERE WIP.ORGANIZATION_ID = JOBS.ORGANIZATION_ID AND WIP.WIP_ENTITY_ID = JOBS.WIP_ENTITY_ID AND ITEMS.ORGANIZATION_ID = JOBS.ORGANIZATION_ID AND ITEMS.INVENTORY_ITEM_ID = JOBS.PRIMARY_ITEM_ID UNION ALL SELECT scheds.primary_item_id , items.organization_id , items.compile_designator , scheds.wip_entity_id , wip.wip_entity_name , scheds.mps_net_quantity , GREATEST(0, (scheds.planned_quantity - scheds.quantity_completed)) , GREATEST(0, (scheds.planned_quantity -scheds.quantity_completed)) , GREATEST(0, (scheds.planned_quantity - scheds.quantity_completed)) , trunc(scheds.mps_scheduled_completion_date) , trunc(scheds.scheduled_completion_date) , 0 , scheds.quantity_completed , GREATEST(0, ((scheds.mps_net_quantity - scheds.quantity_completed) * decode(sign(items.shrinkage_rate), -1, 0, nvl(items.shrinkage_rate, 0)))) , GREATEST(0, ((scheds.planned_quantity - scheds.quantity_completed) * decode(sign(items.shrinkage_rate), -1, 0, nvl(items.shrinkage_rate, 0)))) , 2 , trunc(scheds.scheduled_start_date) , scheds.bom_revision , scheds.status , to_number(null) , scheds.demand_class , items.full_pegging , scheds.project_id , scheds.task_id , scheds.schedule_group_id , scheds.build_sequence , scheds.line_id , scheds.alternate_routing_designator , scheds.alternate_bom_designator , 4 , scheds.end_item_unit_number FROM wip_entities wip, mrp_system_items items, wip_flow_schedules scheds WHERE wip.organization_id = scheds.organization_id AND wip.wip_entity_id = scheds.wip_entity_id AND items.organization_id = scheds.organization_id AND items.inventory_item_id = scheds.primary_item_id AND scheds.scheduled_flag = 1

View Text - HTML Formatted

SELECT JOBS.PRIMARY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.COMPILE_DESIGNATOR
, JOBS.WIP_ENTITY_ID
, WIP.WIP_ENTITY_NAME
, JOBS.MPS_NET_QUANTITY
, GREATEST(0
, (JOBS.MPS_NET_QUANTITY - JOBS.QUANTITY_COMPLETED - JOBS.QUANTITY_SCRAPPED))
, JOBS.NET_QUANTITY
, GREATEST(0
, (JOBS.NET_QUANTITY - JOBS.QUANTITY_COMPLETED - JOBS.QUANTITY_SCRAPPED))
, TRUNC(JOBS.MPS_SCHEDULED_COMPLETION_DATE)
, TRUNC(JOBS.SCHEDULED_COMPLETION_DATE)
, JOBS.QUANTITY_SCRAPPED
, JOBS.QUANTITY_COMPLETED
, GREATEST(0
, DECODE(JOBS.JOB_TYPE
, 1
, (JOBS.MPS_NET_QUANTITY - JOBS.QUANTITY_COMPLETED - JOBS.QUANTITY_SCRAPPED) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, NVL(ITEMS.SHRINKAGE_RATE
, 0))
, 0))
, GREATEST(0
, DECODE(JOBS.JOB_TYPE
, 1
, (JOBS.NET_QUANTITY - JOBS.QUANTITY_COMPLETED - JOBS.QUANTITY_SCRAPPED) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, NVL(ITEMS.SHRINKAGE_RATE
, 0))
, 0))
, JOBS.FIRM_PLANNED_FLAG
, TRUNC(JOBS.SCHEDULED_START_DATE)
, JOBS.BOM_REVISION
, JOBS.STATUS_TYPE
, JOBS.JOB_TYPE
, JOBS.DEMAND_CLASS
, ITEMS.FULL_PEGGING
, JOBS.PROJECT_ID
, JOBS.TASK_ID
, JOBS.SCHEDULE_GROUP_ID
, JOBS.BUILD_SEQUENCE
, JOBS.LINE_ID
, JOBS.ALTERNATE_ROUTING_DESIGNATOR
, JOBS.ALTERNATE_BOM_DESIGNATOR
, 1
, JOBS.END_ITEM_UNIT_NUMBER
FROM WIP_ENTITIES WIP
, MRP_SYSTEM_ITEMS ITEMS
, WIP_DISCRETE_JOBS JOBS
WHERE WIP.ORGANIZATION_ID = JOBS.ORGANIZATION_ID
AND WIP.WIP_ENTITY_ID = JOBS.WIP_ENTITY_ID
AND ITEMS.ORGANIZATION_ID = JOBS.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = JOBS.PRIMARY_ITEM_ID UNION ALL SELECT SCHEDS.PRIMARY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ITEMS.COMPILE_DESIGNATOR
, SCHEDS.WIP_ENTITY_ID
, WIP.WIP_ENTITY_NAME
, SCHEDS.MPS_NET_QUANTITY
, GREATEST(0
, (SCHEDS.PLANNED_QUANTITY - SCHEDS.QUANTITY_COMPLETED))
, GREATEST(0
, (SCHEDS.PLANNED_QUANTITY -SCHEDS.QUANTITY_COMPLETED))
, GREATEST(0
, (SCHEDS.PLANNED_QUANTITY - SCHEDS.QUANTITY_COMPLETED))
, TRUNC(SCHEDS.MPS_SCHEDULED_COMPLETION_DATE)
, TRUNC(SCHEDS.SCHEDULED_COMPLETION_DATE)
, 0
, SCHEDS.QUANTITY_COMPLETED
, GREATEST(0
, ((SCHEDS.MPS_NET_QUANTITY - SCHEDS.QUANTITY_COMPLETED) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, NVL(ITEMS.SHRINKAGE_RATE
, 0))))
, GREATEST(0
, ((SCHEDS.PLANNED_QUANTITY - SCHEDS.QUANTITY_COMPLETED) * DECODE(SIGN(ITEMS.SHRINKAGE_RATE)
, -1
, 0
, NVL(ITEMS.SHRINKAGE_RATE
, 0))))
, 2
, TRUNC(SCHEDS.SCHEDULED_START_DATE)
, SCHEDS.BOM_REVISION
, SCHEDS.STATUS
, TO_NUMBER(NULL)
, SCHEDS.DEMAND_CLASS
, ITEMS.FULL_PEGGING
, SCHEDS.PROJECT_ID
, SCHEDS.TASK_ID
, SCHEDS.SCHEDULE_GROUP_ID
, SCHEDS.BUILD_SEQUENCE
, SCHEDS.LINE_ID
, SCHEDS.ALTERNATE_ROUTING_DESIGNATOR
, SCHEDS.ALTERNATE_BOM_DESIGNATOR
, 4
, SCHEDS.END_ITEM_UNIT_NUMBER
FROM WIP_ENTITIES WIP
, MRP_SYSTEM_ITEMS ITEMS
, WIP_FLOW_SCHEDULES SCHEDS
WHERE WIP.ORGANIZATION_ID = SCHEDS.ORGANIZATION_ID
AND WIP.WIP_ENTITY_ID = SCHEDS.WIP_ENTITY_ID
AND ITEMS.ORGANIZATION_ID = SCHEDS.ORGANIZATION_ID
AND ITEMS.INVENTORY_ITEM_ID = SCHEDS.PRIMARY_ITEM_ID
AND SCHEDS.SCHEDULED_FLAG = 1