VIEW: APPS.MTL_WORK_ORDER_HEADER_V

Source

View Text - Preformatted

SELECT dsj.wip_entity_id , dsj.wip_entity_name , dsj.organization_id , org.organization_code organization_code , hou.name organization_name , null batch_status , dsj.status_type_disp status_type , msi.concatenated_segments item_number , msi.description item_description , msi.inventory_item_id , dsj.firm_planned_flag , dsj.class_code , dsj.scheduled_start_date , dsj.date_released date_released , dsj.scheduled_completion_date , dsj.date_completed date_completed , dsj.date_closed , dsj.start_quantity , dsj.quantity_remaining , dsj.quantity_completed , dsj.quantity_scrapped , dsj.net_quantity , dsj.completion_subinventory , dsj.job_type , 'JOBS' work_order_type , 'Product' line_type, mln.lot_number, mln.disable_flag, mln.expiration_date, mln.vendor_name, mln.supplier_lot_number, mln.grade_code, mln.origination_date, mln.date_code, mln.status_id, mln.change_date, mln.age, mln.retest_date, mln.maturity_date, mln.item_size, mln.color, mln.volume, mln.volume_uom, mln.place_of_origin, mln.best_by_date, mln.LENGTH, mln.length_uom, mln.recycled_content, mln.thickness, mln.thickness_uom, mln.width, mln.width_uom, mln.curl_wrinkle_fold, mln.lot_attribute_category, mln.c_attribute1, mln.c_attribute2, mln.c_attribute3, mln.c_attribute4, mln.c_attribute5, mln.c_attribute6, mln.c_attribute7, mln.c_attribute8, mln.c_attribute9, mln.c_attribute10, mln.c_attribute11, mln.c_attribute12, mln.c_attribute13, mln.c_attribute14, mln.c_attribute15, mln.c_attribute16, mln.c_attribute17, mln.c_attribute18, mln.c_attribute19, mln.c_attribute20, mln.d_attribute1, mln.d_attribute2, mln.d_attribute3, mln.d_attribute4, mln.d_attribute5, mln.d_attribute6, mln.d_attribute7, mln.d_attribute8, mln.d_attribute9, mln.d_attribute10, mln.n_attribute1, mln.n_attribute2, mln.n_attribute3, mln.n_attribute4, mln.n_attribute5, mln.n_attribute6, mln.n_attribute7, mln.n_attribute8, mln.n_attribute9, mln.n_attribute10, mln.vendor_id, mln.territory_code, mln.attribute_category, mln.attribute1, mln.attribute2, mln.attribute3, mln.attribute4, mln.attribute5, mln.attribute6, mln.attribute7, mln.attribute8, mln.attribute9, mln.attribute10, mln.attribute11, mln.attribute12, mln.attribute13, mln.attribute14, mln.attribute15 FROM mtl_parameters org , hr_organization_units hou , wip_discrete_jobs_v dsj , mtl_system_items_kfv msi , mtl_lot_numbers mln WHERE dsj.primary_item_id = msi.inventory_item_id (+) and dsj.organization_id = msi.organization_id (+) and dsj.organization_id = org.organization_id and org.organization_id = hou.organization_id AND dsj.lot_number = mln.lot_number(+) AND dsj.primary_item_id = mln.inventory_item_id(+) AND dsj.organization_id = mln.organization_id(+) UNION ALL SELECT we.wip_entity_id , we.wip_entity_name , we.organization_id , org.organization_code , hou.name organization_name , gmehdr.batch_status batch_status , gemlu.meaning status_type , msi.concatenated_segments item_number , msi.description item_description , msi.inventory_item_id , null firm_planned_flag , null class_code , gmehdr.plan_start_date scheduled_start_date , gmehdr.actual_start_date date_released , gmehdr.plan_cmplt_date scheduled_completion_date , gmehdr.actual_cmplt_date date_completed , gmehdr.batch_close_date date_closed , gmedtl.plan_qty start_quantity , null quantity_remaining , to_char(gmedtl.actual_qty) quantity_completed , null quantity_scrapped , null net_quantity , null completion_subinventory , null job_type , 'BATCH' work_order_type , 'Product' line_type, NULL lot_number, NULL disable_flag, NULL expiration_date, NULL vendor_name, NULL supplier_lot_number, NULL grade_code, NULL origination_date, NULL date_code, NULL status_id, NULL change_date, NULL age, NULL retest_date, NULL maturity_date, NULL item_size, NULL color, NULL volume, NULL volume_uom, NULL place_of_origin, NULL best_by_date, NULL LENGTH, NULL length_uom, NULL recycled_content, NULL thickness, NULL thickness_uom, NULL width, NULL width_uom, NULL curl_wrinkle_fold, NULL lot_attribute_category, NULL c_attribute1, NULL c_attribute2, NULL c_attribute3, NULL c_attribute4, NULL c_attribute5, NULL c_attribute6, NULL c_attribute7, NULL c_attribute8, NULL c_attribute9, NULL c_attribute10, NULL c_attribute11, NULL c_attribute12, NULL c_attribute13, NULL c_attribute14, NULL c_attribute15, NULL c_attribute16, NULL c_attribute17, NULL c_attribute18, NULL c_attribute19, NULL c_attribute20, NULL d_attribute1, NULL d_attribute2, NULL d_attribute3, NULL d_attribute4, NULL d_attribute5, NULL d_attribute6, NULL d_attribute7, NULL d_attribute8, NULL d_attribute9, NULL d_attribute10, NULL n_attribute1, NULL n_attribute2, NULL n_attribute3, NULL n_attribute4, NULL n_attribute5, NULL n_attribute6, NULL n_attribute7, NULL n_attribute8, NULL n_attribute9, NULL n_attribute10, NULL vendor_id, NULL territory_code, NULL attribute_category, NULL attribute1, NULL attribute2, NULL attribute3, NULL attribute4, NULL attribute5, NULL attribute6, NULL attribute7, NULL attribute8, NULL attribute9, NULL attribute10, NULL attribute11, NULL attribute12, NULL attribute13, NULL attribute14, NULL attribute15 FROM mtl_parameters org , hr_organization_units hou , gme_batch_header gmehdr , gme_material_details gmedtl , wip_entities we , mtl_system_items_kfv msi , gem_lookups gemlu WHERE gmehdr.batch_id = we.wip_entity_id and gmehdr.organization_id = we.organization_id and gmehdr.batch_id = gmedtl.batch_id AND gemlu.LOOKUP_TYPE = 'BATCH_STATUS' AND gemlu.LOOKUP_CODE = gmehdr.batch_status and gmedtl.inventory_item_id = msi.inventory_item_id and gmedtl.line_type = 1 and gmehdr.organization_id = msi.organization_id and gmehdr.organization_id = org.organization_id and org.organization_id = hou.organization_id

View Text - HTML Formatted

SELECT DSJ.WIP_ENTITY_ID
, DSJ.WIP_ENTITY_NAME
, DSJ.ORGANIZATION_ID
, ORG.ORGANIZATION_CODE ORGANIZATION_CODE
, HOU.NAME ORGANIZATION_NAME
, NULL BATCH_STATUS
, DSJ.STATUS_TYPE_DISP STATUS_TYPE
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.INVENTORY_ITEM_ID
, DSJ.FIRM_PLANNED_FLAG
, DSJ.CLASS_CODE
, DSJ.SCHEDULED_START_DATE
, DSJ.DATE_RELEASED DATE_RELEASED
, DSJ.SCHEDULED_COMPLETION_DATE
, DSJ.DATE_COMPLETED DATE_COMPLETED
, DSJ.DATE_CLOSED
, DSJ.START_QUANTITY
, DSJ.QUANTITY_REMAINING
, DSJ.QUANTITY_COMPLETED
, DSJ.QUANTITY_SCRAPPED
, DSJ.NET_QUANTITY
, DSJ.COMPLETION_SUBINVENTORY
, DSJ.JOB_TYPE
, 'JOBS' WORK_ORDER_TYPE
, 'PRODUCT' LINE_TYPE
, MLN.LOT_NUMBER
, MLN.DISABLE_FLAG
, MLN.EXPIRATION_DATE
, MLN.VENDOR_NAME
, MLN.SUPPLIER_LOT_NUMBER
, MLN.GRADE_CODE
, MLN.ORIGINATION_DATE
, MLN.DATE_CODE
, MLN.STATUS_ID
, MLN.CHANGE_DATE
, MLN.AGE
, MLN.RETEST_DATE
, MLN.MATURITY_DATE
, MLN.ITEM_SIZE
, MLN.COLOR
, MLN.VOLUME
, MLN.VOLUME_UOM
, MLN.PLACE_OF_ORIGIN
, MLN.BEST_BY_DATE
, MLN.LENGTH
, MLN.LENGTH_UOM
, MLN.RECYCLED_CONTENT
, MLN.THICKNESS
, MLN.THICKNESS_UOM
, MLN.WIDTH
, MLN.WIDTH_UOM
, MLN.CURL_WRINKLE_FOLD
, MLN.LOT_ATTRIBUTE_CATEGORY
, MLN.C_ATTRIBUTE1
, MLN.C_ATTRIBUTE2
, MLN.C_ATTRIBUTE3
, MLN.C_ATTRIBUTE4
, MLN.C_ATTRIBUTE5
, MLN.C_ATTRIBUTE6
, MLN.C_ATTRIBUTE7
, MLN.C_ATTRIBUTE8
, MLN.C_ATTRIBUTE9
, MLN.C_ATTRIBUTE10
, MLN.C_ATTRIBUTE11
, MLN.C_ATTRIBUTE12
, MLN.C_ATTRIBUTE13
, MLN.C_ATTRIBUTE14
, MLN.C_ATTRIBUTE15
, MLN.C_ATTRIBUTE16
, MLN.C_ATTRIBUTE17
, MLN.C_ATTRIBUTE18
, MLN.C_ATTRIBUTE19
, MLN.C_ATTRIBUTE20
, MLN.D_ATTRIBUTE1
, MLN.D_ATTRIBUTE2
, MLN.D_ATTRIBUTE3
, MLN.D_ATTRIBUTE4
, MLN.D_ATTRIBUTE5
, MLN.D_ATTRIBUTE6
, MLN.D_ATTRIBUTE7
, MLN.D_ATTRIBUTE8
, MLN.D_ATTRIBUTE9
, MLN.D_ATTRIBUTE10
, MLN.N_ATTRIBUTE1
, MLN.N_ATTRIBUTE2
, MLN.N_ATTRIBUTE3
, MLN.N_ATTRIBUTE4
, MLN.N_ATTRIBUTE5
, MLN.N_ATTRIBUTE6
, MLN.N_ATTRIBUTE7
, MLN.N_ATTRIBUTE8
, MLN.N_ATTRIBUTE9
, MLN.N_ATTRIBUTE10
, MLN.VENDOR_ID
, MLN.TERRITORY_CODE
, MLN.ATTRIBUTE_CATEGORY
, MLN.ATTRIBUTE1
, MLN.ATTRIBUTE2
, MLN.ATTRIBUTE3
, MLN.ATTRIBUTE4
, MLN.ATTRIBUTE5
, MLN.ATTRIBUTE6
, MLN.ATTRIBUTE7
, MLN.ATTRIBUTE8
, MLN.ATTRIBUTE9
, MLN.ATTRIBUTE10
, MLN.ATTRIBUTE11
, MLN.ATTRIBUTE12
, MLN.ATTRIBUTE13
, MLN.ATTRIBUTE14
, MLN.ATTRIBUTE15
FROM MTL_PARAMETERS ORG
, HR_ORGANIZATION_UNITS HOU
, WIP_DISCRETE_JOBS_V DSJ
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_LOT_NUMBERS MLN
WHERE DSJ.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND DSJ.ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
AND DSJ.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND DSJ.LOT_NUMBER = MLN.LOT_NUMBER(+)
AND DSJ.PRIMARY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+)
AND DSJ.ORGANIZATION_ID = MLN.ORGANIZATION_ID(+) UNION ALL SELECT WE.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ORGANIZATION_ID
, ORG.ORGANIZATION_CODE
, HOU.NAME ORGANIZATION_NAME
, GMEHDR.BATCH_STATUS BATCH_STATUS
, GEMLU.MEANING STATUS_TYPE
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.INVENTORY_ITEM_ID
, NULL FIRM_PLANNED_FLAG
, NULL CLASS_CODE
, GMEHDR.PLAN_START_DATE SCHEDULED_START_DATE
, GMEHDR.ACTUAL_START_DATE DATE_RELEASED
, GMEHDR.PLAN_CMPLT_DATE SCHEDULED_COMPLETION_DATE
, GMEHDR.ACTUAL_CMPLT_DATE DATE_COMPLETED
, GMEHDR.BATCH_CLOSE_DATE DATE_CLOSED
, GMEDTL.PLAN_QTY START_QUANTITY
, NULL QUANTITY_REMAINING
, TO_CHAR(GMEDTL.ACTUAL_QTY) QUANTITY_COMPLETED
, NULL QUANTITY_SCRAPPED
, NULL NET_QUANTITY
, NULL COMPLETION_SUBINVENTORY
, NULL JOB_TYPE
, 'BATCH' WORK_ORDER_TYPE
, 'PRODUCT' LINE_TYPE
, NULL LOT_NUMBER
, NULL DISABLE_FLAG
, NULL EXPIRATION_DATE
, NULL VENDOR_NAME
, NULL SUPPLIER_LOT_NUMBER
, NULL GRADE_CODE
, NULL ORIGINATION_DATE
, NULL DATE_CODE
, NULL STATUS_ID
, NULL CHANGE_DATE
, NULL AGE
, NULL RETEST_DATE
, NULL MATURITY_DATE
, NULL ITEM_SIZE
, NULL COLOR
, NULL VOLUME
, NULL VOLUME_UOM
, NULL PLACE_OF_ORIGIN
, NULL BEST_BY_DATE
, NULL LENGTH
, NULL LENGTH_UOM
, NULL RECYCLED_CONTENT
, NULL THICKNESS
, NULL THICKNESS_UOM
, NULL WIDTH
, NULL WIDTH_UOM
, NULL CURL_WRINKLE_FOLD
, NULL LOT_ATTRIBUTE_CATEGORY
, NULL C_ATTRIBUTE1
, NULL C_ATTRIBUTE2
, NULL C_ATTRIBUTE3
, NULL C_ATTRIBUTE4
, NULL C_ATTRIBUTE5
, NULL C_ATTRIBUTE6
, NULL C_ATTRIBUTE7
, NULL C_ATTRIBUTE8
, NULL C_ATTRIBUTE9
, NULL C_ATTRIBUTE10
, NULL C_ATTRIBUTE11
, NULL C_ATTRIBUTE12
, NULL C_ATTRIBUTE13
, NULL C_ATTRIBUTE14
, NULL C_ATTRIBUTE15
, NULL C_ATTRIBUTE16
, NULL C_ATTRIBUTE17
, NULL C_ATTRIBUTE18
, NULL C_ATTRIBUTE19
, NULL C_ATTRIBUTE20
, NULL D_ATTRIBUTE1
, NULL D_ATTRIBUTE2
, NULL D_ATTRIBUTE3
, NULL D_ATTRIBUTE4
, NULL D_ATTRIBUTE5
, NULL D_ATTRIBUTE6
, NULL D_ATTRIBUTE7
, NULL D_ATTRIBUTE8
, NULL D_ATTRIBUTE9
, NULL D_ATTRIBUTE10
, NULL N_ATTRIBUTE1
, NULL N_ATTRIBUTE2
, NULL N_ATTRIBUTE3
, NULL N_ATTRIBUTE4
, NULL N_ATTRIBUTE5
, NULL N_ATTRIBUTE6
, NULL N_ATTRIBUTE7
, NULL N_ATTRIBUTE8
, NULL N_ATTRIBUTE9
, NULL N_ATTRIBUTE10
, NULL VENDOR_ID
, NULL TERRITORY_CODE
, NULL ATTRIBUTE_CATEGORY
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL ATTRIBUTE11
, NULL ATTRIBUTE12
, NULL ATTRIBUTE13
, NULL ATTRIBUTE14
, NULL ATTRIBUTE15
FROM MTL_PARAMETERS ORG
, HR_ORGANIZATION_UNITS HOU
, GME_BATCH_HEADER GMEHDR
, GME_MATERIAL_DETAILS GMEDTL
, WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS_KFV MSI
, GEM_LOOKUPS GEMLU
WHERE GMEHDR.BATCH_ID = WE.WIP_ENTITY_ID
AND GMEHDR.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND GMEHDR.BATCH_ID = GMEDTL.BATCH_ID
AND GEMLU.LOOKUP_TYPE = 'BATCH_STATUS'
AND GEMLU.LOOKUP_CODE = GMEHDR.BATCH_STATUS
AND GMEDTL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND GMEDTL.LINE_TYPE = 1
AND GMEHDR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND GMEHDR.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = HOU.ORGANIZATION_ID