Oracle EBS - 用于查询ATO流程中SO与WIP Job的SQL

SELECT WIP_SO.SALE_ORDER_NUMBER SO_NUM,
WIP_SO.LINE_NUMBER SO_LINE_NUM,
------------------------------------------
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 JOB_CREATION_DATE,
WDJ.CREATED_BY JOB_CREATED_BY,
WDJ.LAST_UPDATE_DATE JOB_LAST_UPDATE_DATE,
WDJ.LAST_UPDATED_BY JOB_LAST_UPDATED_BY,
WDJ.DESCRIPTION JOB_DESC,
WDJ.STATUS_TYPE,
WDJ.JOB_TYPE,
LU3.MEANING JOB_TYPE_NAME,
WDJ.WIP_SUPPLY_TYPE COMP_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,
-----------------------------------------------
WRO.ROWID COMP_ROW_ID,
WRO.INVENTORY_ITEM_ID COM_ITEM_ID,
MSIK1.SEGMENT1 COMP_ITEM_CODE,
MSIK1.DESCRIPTION COMP_ITEM_DESC,
MSIK1.ITEM_TYPE COMP_ITEM_TYPE,
MSIK1.PRIMARY_UOM_CODE COMP_ITEM_UOM,
MSIK1.ATTRIBUTE9 SUPPLIER_NAME,
MSIK1.BUYER_ID,
WRO.OPERATION_SEQ_NUM,
WRO.REPETITIVE_SCHEDULE_ID,
WRO.LAST_UPDATE_DATE COMP_LAST_UPDATE_DATE,
WRO.LAST_UPDATED_BY COMP_LAST_UPDATED_BY,
WRO.CREATION_DATE COMP_CREATION_DATE,
WRO.CREATED_BY COMP_CREATED_BY,
WRO.DEPARTMENT_ID,
BD.DEPARTMENT_CODE,
WRO.WIP_SUPPLY_TYPE,
ML1.MEANING WIP_SUPPLY_MEANING,
WRO.DATE_REQUIRED,
WRO.REQUIRED_QUANTITY,
DECODE(WRO.QUANTITY_ISSUED, 0, NULL, WRO.QUANTITY_ISSUED) QUANTITY_ISSUED,
DECODE((WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED),
0,
NULL,
DECODE(SIGN(WRO.REQUIRED_QUANTITY),
-1 * SIGN(WRO.QUANTITY_ISSUED),
(WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED),
DECODE(SIGN(ABS(WRO.REQUIRED_QUANTITY) -
ABS(WRO.QUANTITY_ISSUED)),
-1,
NULL,
(WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED)))) QUANTITY_OPEN,
WRO.QUANTITY_PER_ASSEMBLY,
WRO.SUPPLY_SUBINVENTORY,
WRO.SUPPLY_LOCATOR_ID,
T.LONG_DESCRIPTION,
MSIK1.DIMENSION_UOM_CODE,
MSIK1.UNIT_LENGTH,
MSIK1.UNIT_WIDTH,
MSIK1.UNIT_HEIGHT,
DECODE(MSIK1.UNIT_LENGTH,NULL,NULL,'D')||''||MSIK1.UNIT_LENGTH||
DECODE(MSIK1.UNIT_LENGTH,NULL,NULL,DECODE(MSIK1.UNIT_WIDTH,NULL,NULL,' x '))||DECODE(MSIK1.UNIT_WIDTH,NULL,NULL,'W')||MSIK1.UNIT_WIDTH||
DECODE(MSIK1.UNIT_LENGTH||MSIK1.UNIT_WIDTH,NULL,NULL,DECODE(MSIK1.UNIT_HEIGHT,NULL,NULL,' x '))||DECODE(MSIK1.UNIT_HEIGHT,NULL,NULL,'H')||MSIK1.UNIT_HEIGHT COMP_ITEM_DIMENSION
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,
BOM_DEPARTMENTS BD,
WIP_LINES WL,
WIP_SCHEDULE_GROUPS WSG,
MTL_SYSTEM_ITEMS_B MSIK,
MTL_SYSTEM_ITEMS_B MSIK1,
MFG_LOOKUPS ML1,
MTL_SYSTEM_ITEMS_TL T,
(
SELECT MR.RESERVATION_ID,
MR.ORGANIZATION_ID ORG_ID,
MR.DEMAND_SOURCE_TYPE_ID,
MR.DEMAND_SOURCE_NAME,
MR.DEMAND_SOURCE_HEADER_ID,
MR.DEMAND_SOURCE_LINE_ID,
OOL.LINE_NUMBER,
MSO.SEGMENT1 SALE_ORDER_NUMBER,
MSO.SEGMENT2, /* 10 */
MSO.SEGMENT3
FROM MTL_RESERVATIONS MR,
OE_ORDER_LINES_ALL OOL,
MTL_SALES_ORDERS MSO
WHERE MSO.SALES_ORDER_ID = MR.DEMAND_SOURCE_HEADER_ID
AND MR.DEMAND_SOURCE_LINE_ID = OOL.LINE_ID
AND MR.DEMAND_SOURCE_TYPE_ID IN (2, 8) /* INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE */
AND MR.SUPPLY_SOURCE_TYPE_ID = 5
) WIP_SO--,
--MTL_ITEM_CATEGORIES MIC,
--MTL_CATEGORIES_B MCB
WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WDJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE = 3 --Job Status:Released
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 BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID
AND ML1.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
AND ML1.LOOKUP_TYPE = 'WIP_SUPPLY'
AND MSIK1.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND MSIK1.ORGANIZATION_ID = T.ORGANIZATION_ID
AND T.LANGUAGE = userenv('LANG')
AND WDJ.SOURCE_LINE_ID = WIP_SO.DEMAND_SOURCE_LINE_ID(+)
--AND MSIK.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
--AND MSIK.ORGANIZATION_ID = MIC.ORGANIZATION_ID
--AND MIC.CATEGORY_SET_ID = 63 --CATEGORY_SET_NAME ='MEW_WIP_CLASS'
--AND MIC.CATEGORY_ID = MCB.CATEGORY_ID
--AND MCB.SEGMENT1 = '886'
--AND MCB.SEGMENT2 = 'S'
--AND MCB.SEGMENT3 = '2200'
--AND WE.WIP_ENTITY_ID = 910797
--AND WE.WIP_ENTITY_NAME = '1009469'
AND WDJ.ORGANIZATION_ID =128[@more@]

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

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