oracle中ATO流程,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/，如需转载，请注明出处，否则将追究法律责任。