某处看到的SO与WIP的关系(仅限于从标准SO自动产生任务单)

/* Formatted on 2014-9-22 17:30:45 (QP5 v5.115.810.9015) */
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