Oracle EBS-有关Manufacture中Discrete Job的SQL

SELECT WDJ.ROWID ROW_ID,
WDJ.ORGANIZATION_ID,
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WDJ.PRIMARY_ITEM_ID,
MSIK.SEGMENT1 FG_ITEM_NUMBER,
MSIK.DESCRIPTION FG_ITEM_DESC,
MSIK.PRIMARY_UOM_CODE FG_UOM_CODE,
WDJ.COMPLETION_SUBINVENTORY,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE,
WDJ.CREATION_DATE,
WDJ.CREATED_BY,
WDJ.LAST_UPDATE_DATE,
WDJ.LAST_UPDATED_BY,
WDJ.DESCRIPTION JOB_DESC,
WDJ.STATUS_TYPE,
WDJ.JOB_TYPE,
LU3.MEANING JOB_TYPE_NAME,
WDJ.WIP_SUPPLY_TYPE,
WDJ.CLASS_CODE,
WDJ.DATE_RELEASED,
WDJ.DATE_COMPLETED,
WDJ.DATE_CLOSED,
WDJ.START_QUANTITY,
DECODE(WDJ.QUANTITY_COMPLETED, 0, NULL, WDJ.QUANTITY_COMPLETED) "QUANTITY_COMPLETED",
DECODE(WDJ.START_QUANTITY - WDJ.QUANTITY_COMPLETED -
WDJ.QUANTITY_SCRAPPED,
0,
NULL,
WDJ.START_QUANTITY - WDJ.QUANTITY_COMPLETED -
WDJ.QUANTITY_SCRAPPED) "QUANTITY_REMAINING",
DECODE(WDJ.QUANTITY_SCRAPPED, 0, NULL, WDJ.QUANTITY_SCRAPPED) "QUANTITY_SCRAPPED",
WDJ.NET_QUANTITY,
WDJ.COMMON_BOM_SEQUENCE_ID,
WDJ.COMMON_ROUTING_SEQUENCE_ID,
WDJ.BOM_REVISION,
WDJ.ROUTING_REVISION,
WDJ.BOM_REVISION_DATE,
WDJ.ROUTING_REVISION_DATE,
WDJ.COMPLETION_LOCATOR_ID,
DECODE(WDJ.COMPLETION_SUBINVENTORY, NULL, 1, MSUB.LOCATOR_TYPE) SUB_LOCATOR_CONTROL,
WDJ.DEMAND_CLASS,
WSG.SCHEDULE_GROUP_NAME,
WDJ.SCHEDULE_GROUP_ID,
WDJ.BUILD_SEQUENCE,
WL.LINE_CODE,
WDJ.LINE_ID,
WDJ.ATTRIBUTE1,
WDJ.ATTRIBUTE2,
WDJ.ATTRIBUTE3,
LU1.MEANING STATUS_TYPE_DISP,
LU2.MEANING WIP_SUPPLY_TYPE_DISP,
WDJ.OVERCOMPLETION_TOLERANCE_TYPE,
WDJ.OVERCOMPLETION_TOLERANCE_VALUE,
WDJ.PRIORITY,
WE.ENTITY_TYPE
FROM MTL_SECONDARY_INVENTORIES MSUB,
MFG_LOOKUPS LU1,
MFG_LOOKUPS LU2,
MFG_LOOKUPS LU3,
WIP_ENTITIES WE,
WIP_DISCRETE_JOBS WDJ,
WIP_REQUIREMENT_OPERATIONS WRO,
WIP_LINES WL,
WIP_SCHEDULE_GROUPS WSG,
MTL_SYSTEM_ITEMS_VL MSIK,
MTL_SYSTEM_ITEMS_VL MSIK1
WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WDJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND MSUB.SECONDARY_INVENTORY_NAME(+) = WDJ.COMPLETION_SUBINVENTORY
AND MSUB.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID
AND WL.LINE_ID(+) = WDJ.LINE_ID
AND WL.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND LU2.LOOKUP_TYPE = 'WIP_SUPPLY'
AND LU1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU3.LOOKUP_TYPE = 'WIP_DISCRETE_JOB'
AND LU1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND LU2.LOOKUP_CODE = WDJ.WIP_SUPPLY_TYPE
AND LU3.LOOKUP_CODE = WDJ.JOB_TYPE
AND MSIK.INVENTORY_ITEM_ID = WDJ.PRIMARY_ITEM_ID
AND MSIK.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MSIK1.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSIK1.ORGANIZATION_ID = WRO.ORGANIZATION_ID
--AND WE.WIP_ENTITY_ID = 910797
--AND WE.WIP_ENTITY_NAME = '1076454'[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45641/viewspace-927455/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45641/viewspace-927455/