OM常用表_视图及关系.doc

在在 OM 二次开发常用到的表 视图及关系二次开发常用到的表 视图及关系 表 MTL ONHAND LOCATOR V MOLV WIP DISCRETE JOBS WDJ OE ORDER HEADERS ALL OOHA OE ORDER LINES ALL OOLA MTL CUSTOMER ITEM XREFS V MCIX 关系 MOLV INVENTORY ITEM ID MCIX INVENTORY ITEM ID WDJ PRIMARY ITEM ID MCIX INVENTORY ITEM ID OOHA HEADER ID OOLA HEADER ID AND OOLA INVENTORY ITEM ID MCIX INVENTORY ITEM ID 表 WIP REQUIREMENT OPERATIONS WRO WIP ENTITIES WE MTL SYSTEM ITEMS B MSIB WIP DISCRETE JOBS WDJ 关系 WE WIP ENTITY ID WRO WIP ENTITY ID WRO WIP ENTITY ID WDJ WIP ENTITY ID MSIB INVENTORY ITEM ID WRO INVENTORY ITEM ID 表 OE ORDER HEADERS ALL OOHA OE ORDER LINES ALL OOLA MTL SYSTEM ITEMS B MSIB MTL RESERVATIONS MR 表 MTL SALES ORDERS KFV MSOK MTL RESERVATIONS MR OE ORDER HEADERS ALL OOHA1 OE ORDER LINES ALL OOLA1 关系 OOLA1 HEADER ID OOHA1 HEADER ID OOLA1 INVENTORY ITEM ID MR INVENTORY ITEM ID MSOK SALES ORDER ID MR DEMAND SOURCE HEADER ID TO NUMBER MSOK SEGMENT1 OOHA1 ORDER NUMBER T 关系 OOHA HEADER ID OOLA HEADER ID MSIB INVENTORY ITEM ID OOLA INVENTORY ITEM ID MR DEMAND SOURCE LINE ID OOLA LINE ID T HEADER ID OOLA HEADER ID T LINE ID OOLA LINE ID 表 WIP DISCRETE JOBS WDJ WIP ENTITIES WE MTL SYSTEM ITEMS B MSIB 关系 WE WIP ENTITY ID WDJ WIP ENTITY ID MSIB INVENTORY ITEM ID WDJ PRIMARY ITEM ID 例子 CREATE OR REPLACE VIEW XXOM OF MAIN V AS select mcix customer id MCIX CUSTOMER NAME mcix customer item id MCIX CUSTOMER ITEM NUMBER mcix INVENTORY ITEM ID MCIX CONCATENATED SEGMENTS MCIX ATTRIBUTE15 NVL SELECT MOLV TOTAL QOH FROM MTL ONHAND LOCATOR V MOLV WHERE MOLV INVENTORY ITEM ID MCIX INVENTORY ITEM ID AND MOLV SUBINVENTORY CODE MODULE AND MOLV ORGANIZATION ID FND PROFILE VALUE MFG ORGANIZATION ID AND MOLV LOCATOR ID 1 0 LOCATOR A NVL SELECT MOLV TOTAL QOH FROM MTL ONHAND LOCATOR V MOLV WHERE MOLV INVENTORY ITEM ID MCIX INVENTORY ITEM ID AND MOLV SUBINVENTORY CODE MODULE AND MOLV ORGANIZATION ID FND PROFILE VALUE MFG ORGANIZATION ID AND MOLV LOCATOR ID 2 0 LOCATOR B NVL SELECT MOLV TOTAL QOH FROM MTL ONHAND LOCATOR V MOLV WHERE MOLV INVENTORY ITEM ID MCIX INVENTORY ITEM ID AND MOLV SUBINVENTORY CODE MODULE AND MOLV ORGANIZATION ID FND PROFILE VALUE MFG ORGANIZATION ID AND MOLV LOCATOR ID 3 0 LOCATOR C NVL SELECT SUM MOLV TOTAL QOH FROM MTL ONHAND LOCATOR V MOLV WHERE MOLV INVENTORY ITEM ID MCIX INVENTORY ITEM ID AND MOLV SUBINVENTORY CODE MODULE AND MOLV ORGANIZATION ID FND PROFILE VALUE MFG ORGANIZATION ID 0 INVENTORY NUM NVL SELECT SUM WDJ START QUANTITY WDJ QUANTITY COMPLETED FROM WIP DISCRETE JOBS WDJ WHERE WDJ PRIMARY ITEM ID MCIX INVENTORY ITEM ID AND WDJ STATUS TYPE 7 CANCELLED WIP AND WDJ ORGANIZATION ID FND PROFILE VALUE MFG ORGANIZATION ID 0 ORDER NUM NVL SELECT SUM oola ORDERED QUANTITY NVL oola SHIPPED QUANTITY 0 from oe order headers all ooha oe order lines all oola where ooha header id oola header id AND ooha booked flag Y and oola cancelled flag N AND OOLA INVENTORY ITEM ID MCIX INVENTORY ITEM ID AND OOHA SHIP FROM ORG ID FND PROFILE VALUE MFG ORGANIZATION ID 0 REQUIREMENT NUM FROM MTL CUSTOMER ITEM XREFS V MCIX WHERE MCIX MASTER ORGANIZATION ID SELECT MPV ORGANIZATION ID FROM MTL PARAMETERS VIEW MPV WHERE MPV ORGANIZATION ID FND PROFILE VALUE MFG ORGANIZATION ID CREATE OR REPLACE VIEW XXOM OF DETAIL V AS SELECT WRO ORGANIZATION ID WRO INVENTORY ITEM ID MSIB SEGMENT1 CONCATENATED SEGMENTS WRO DATE REQUIRED SD DATE WIP Components SD TYPE WE WIP ENTITY NAME WS NUMBER 1 NVL WRO REQUIRED QUANTITY 0 NVL WRO QUANTITY ISSUED 0 QUANTITY FROM WIP REQUIREMENT OPERATIONS WRO WIP ENTITIES WE MTL SYSTEM ITEMS B MSIB WIP DISCRETE JOBS WDJ WHERE WE WIP ENTITY ID WRO WIP ENTITY ID AND WRO WIP ENTITY ID WDJ WIP ENTITY ID AND WDJ STATUS TYPE 7 CANCELLED WIP AND WRO ORGANIZATION ID FND PROFILE VALUE MFG ORGANIZATION ID AND NVL WRO REQUIRED QUANTITY 0 NVL WRO QUANTITY ISSUED 0 0 AND MSIB INVENTORY ITEM ID WRO INVENTORY ITEM ID AND MSIB ORGANIZATION ID FND PROFILE VALUE MFG ORGANIZATION ID UNION ALL SELECT OOHA SHIP FROM ORG ID OOLA INVENTORY ITEM ID MSIB SEGMENT1 CONCATENATED SEGMENTS NVL T REQUIREMENT DATE OOLA SCHEDULE SHIP DATE SD DATE NVL MR REQUIREMENT DATE OOLA SCHEDULE SHIP DATE SD DATE Sales Order SD TYPE TO CHAR OOHA ORDER NUMBER WS NUMBER 1 OOLA ORDERED QUANTITY NVL OOLA SHIPPED QUANTITY 0 QUANTITY FROM oe order headers all ooha oe order lines all oola MTL SYSTEM ITEMS B MSIB MTL RESERVATIONS MR SELECT MR REQUIREMENT DATE OOLA1 HEADER ID OOLA1 LINE ID FROM MTL SALES ORDERS KFV MSOK MTL RESERVATIONS MR OE ORDER HEADERS ALL OOHA1 OE ORDER LINES ALL OOLA1 WHERE OOLA1 HEADER ID OOHA1 HEADER ID AND OOLA1 INVENTORY ITEM ID MR INVENTORY ITEM ID AND MSOK SALES ORDER ID MR DEMAND SOURCE HEADER ID AND TO NUMBER MSOK SEGMENT1 OOHA1 ORDER NUMBER T WHERE ooha header id oola header id AND ooha booked flag Y and oola cancelled flag N AND OOHA SHIP FROM ORG ID FND PROFILE VALUE MFG ORGANIZATION ID AND OOLA ORDERED QUANTITY NVL OOLA SHIPPED QUANTITY 0 0 AND MSIB INVENTORY ITEM ID OOLA INVENTORY ITEM ID AND MSIB ORGANIZATION ID FND PROFILE VALUE MFG ORGANIZATION ID and MR DEMAND SOURCE LINE ID OOLA LINE ID AND T HEADER ID OOLA HEADER ID AND T LINE ID OOLA LINE ID UNION ALL SELECT wdj organization id WDJ PRIMARY ITEM ID INVENTORY ITEM ID MSIB SEGMENT1 CONCATENATED SEGMENTS WDJ Scheduled Completion Date SD DATE Discrete Jobs SD TYPE WE wip entity name SD NUMBER NVL wdj start quantity 0 NVL wdj quantity completed 0 QUANTITY FROM wip discrete jobs WDJ wip entities WE MTL SYSTEM ITEMS B MSIB WHERE WE WIP ENTITY ID