Product: | INV - Inventory |
---|---|
Description: | |
Implementation/DBA Data: | APPS.MTL_WORK_ORDER_HEADER_V |
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