WIP JOB Extraction Query

SELECT  WIP_ENTITY_NAME
,PRIMARY_ITEM_NAME
,ITEM_DESCRIPTION 
,SOURCE_CODE
,FIRM_PLANNED_FLAG
,STATUS_TYPE
,START_QUANTITY
,NET_QUANTITY
,CLASS_CODE
,ORGANIZATION_CODE
,ALTERNATE_BOM_DESIGNATOR
,BOM_REVISION
,SUPPLY_TYPE
,BOM_REVISION_DATE
,ALTERNATE_ROUTING_DESIGNATOR
,ROUTING_REVISION
,ROUTING_REVISION_DATE
,SERIALIZATION_START_OP
,COMPLETION_SUBINVENTORY
,COMPLETION_LOCATOR
,DATE_RELEASED
,SCHEDULE_GROUP_NAME
,LINE_CODE
,PROJECT_NUMBER
,TASK_NUMBER
,DUE_DATE
,PRIORITY
,DUE_DATE_PENALTY
,DUE_DATE_TOLERANCE
,DEMAND_CLASS
,LOT_NUMBER
,OVERCOMPLETION_TOLERANCE_TYPE
,OVERCOMPLETION_TOLERANCE_VALUE
,FIRST_UNIT_START_DATE
,FIRST_UNIT_COMPLETION_DATE
,LAST_UNIT_START_DATE
,LAST_UNIT_COMPLETION_DATE
,SHUTDOWN_TYPE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,DEPARTMENT_CODE
,PROCESSING_WORK_DAYS
,DAILY_PRODUCTION_RATE
,SCHEDULED_COMPLETION_DATE
,SCHEDULED_START_DATE
,BUILD_SEQUENCE
FROM
(SELECT distinct WE.WIP_ENTITY_NAME
      ,(SELECT CONCATENATED_SEGMENTS
        FROM mtl_system_items_kfv
        WHERE inventory_item_id = wdj.primary_item_id
        AND ORGANIZATION_ID = WDJ.ORGANIZATION_ID) PRIMARY_ITEM_NAME
      ,(SELECT DESCRIPTION
        FROM MTL_SYSTEM_ITEMS_B
        WHERE INVENTORY_ITEM_ID = WDJ.PRIMARY_ITEM_ID
        AND ORGANIZATION_ID = WDJ.ORGANIZATION_ID) ITEM_DESCRIPTION 
      ,WDJ.SOURCE_CODE
      ,WDJ.FIRM_PLANNED_FLAG
      ,WDJ.STATUS_TYPE
      ,DECODE (  wdj.start_quantity
                 - wdj.quantity_completed
                 - wdj.quantity_scrapped, 0, NULL,
                   wdj.start_quantity
                 - wdj.quantity_completed
                 - wdj.quantity_scrapped
                ) START_QUANTITY
      ,WDJ.NET_QUANTITY
      ,WDJ.SCHEDULED_START_DATE
      ,WDJ.SCHEDULED_COMPLETION_DATE
      ,WDJ.CLASS_CODE
      ,OOD.ORGANIZATION_CODE
      --BOM---------------
      ,WDJ.ALTERNATE_BOM_DESIGNATOR
      ,WDJ.BOM_REVISION
      ,WDJ.WIP_SUPPLY_TYPE SUPPLY_TYPE
      ,WDJ.BOM_REVISION_DATE
      --ROUTING
      ,WDJ.ALTERNATE_ROUTING_DESIGNATOR
      ,WDJ.ROUTING_REVISION
      ,WDJ.ROUTING_REVISION_DATE
      ,WDJ.SERIALIZATION_START_OP
      ,WDJ.COMPLETION_SUBINVENTORY
      ,(SELECT CONCATENATED_SEGMENTS
        FROM MTL_ITEM_LOCATIONS_KFV  IL
        WHERE IL.INVENTORY_LOCATION_ID = WDJ.COMPLETION_LOCATOR_ID) COMPLETION_LOCATOR
      --JOB HISTORY-----
      ,WDJ.DATE_RELEASED
      --SCHEDULE GROUP-----
      ,WSG.SCHEDULE_GROUP_NAME
      ,WL.LINE_CODE
  ,BUILD_SEQUENCE
      ,APPS.PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID) PROJECT_NUMBER
      ,APPS.PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID)TASK_NUMBER
      --SCHEDULING-------------
      ,WDJ.DUE_DATE
      ,WDJ.PRIORITY
      ,WDJ.DUE_DATE_PENALTY
      ,WDJ.DUE_DATE_TOLERANCE
      --MORE---------------
      ,WDJ.DEMAND_CLASS
      ,WDJ.LOT_NUMBER
      ,WDJ.OVERCOMPLETION_TOLERANCE_TYPE
      ,WDJ.OVERCOMPLETION_TOLERANCE_VALUE
      --OPERATIONS------
        --MAIN
        --QUANTITY
        --DATES
      ,WO.FIRST_UNIT_START_DATE
      ,WO.FIRST_UNIT_COMPLETION_DATE
      ,WO.LAST_UNIT_START_DATE
      ,WO.LAST_UNIT_COMPLETION_DATE
      --DESCRIPTION
      ,MFG3.MEANING SHUTDOWN_TYPE
      ,WDJ.ATTRIBUTE_CATEGORY
      ,WDJ.ATTRIBUTE1
      ,WDJ.ATTRIBUTE2
      ,WDJ.ATTRIBUTE3
      ,WDJ.ATTRIBUTE4
      ,WDJ.ATTRIBUTE5
      ,WDJ.ATTRIBUTE6
      ,WDJ.ATTRIBUTE7
      ,WDJ.ATTRIBUTE8
      ,WDJ.ATTRIBUTE9
      ,WDJ.ATTRIBUTE10
      ,WDJ.ATTRIBUTE11
      ,WDJ.ATTRIBUTE12
      ,WDJ.ATTRIBUTE13
      ,WDJ.ATTRIBUTE14
      ,WDJ.ATTRIBUTE15
      ---LINE LEVEL DETAILS
      ,BD.DEPARTMENT_CODE
      --SUPPLY
      ,WRS.PROCESSING_WORK_DAYS
      ,WRS.DAILY_PRODUCTION_RATE
FROM APPS.WIP_DISCRETE_JOBS  WDJ,
     APPS.WIP_ENTITIES  WE,
     APPS.HR_ALL_ORGANIZATION_UNITS  HAOU,
     APPS.ORG_ORGANIZATION_DEFINITIONS  OOD,
     APPS.WIP_REQUIREMENT_OPERATIONS  WRO,
     APPS.WIP_SCHEDULE_GROUPS  WSG,
     APPS.MFG_LOOKUPS  MFG,
     APPS.MFG_LOOKUPS  MFG1,
     APPS.MFG_LOOKUPS  MFG2,
     APPS.BOM_DEPARTMENTS  BD,
     APPS.BOM_STANDARD_OPERATIONS  BSO,
     APPS.MFG_LOOKUPS  ML1,
     APPS.WIP_LINES  WL,
     APPS.MTL_KANBAN_CARDS  KC,
     APPS.WIP_OPERATIONS  WO,
     APPS.MFG_LOOKUPS  MFG3,
     APPS.WIP_OPERATION_RESOURCES  WOR,
     APPS.WIP_REPETITIVE_SCpHEDULES  WRS,
     APPS.CST_ACTIVITIES  CA,
     APPS.BOM_RESOURCES  BR
WHERE 1=1
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
--AND WE.WIP_ENTITY_NAME =
AND OOD.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND OOD.OPERATING_UNIT = HAOU.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID
AND BSO.STANDARD_OPERATION_ID(+) = WO.STANDARD_OPERATION_ID
AND NVL(BSO.OPERATION_TYPE,1)    = 1
AND BSO.LINE_ID IS NULL
AND MFG.LOOKUP_TYPE = 'WIP_DISCRETE_JOB'
AND MFG.LOOKUP_CODE = WDJ.JOB_TYPE
AND MFG1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND MFG1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND MFG2.LOOKUP_TYPE = 'WIP_SUPPLY'
AND MFG2.LOOKUP_CODE = WDJ.WIP_SUPPLY_TYPE
AND MFG3.LOOKUP_TYPE(+)='BOM_EAM_SHUTDOWN_TYPE'
AND MFG3.LOOKUP_CODE(+)= WO.SHUTDOWN_TYPE
--
AND BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID
AND KC.KANBAN_CARD_ID (+)= WDJ.KANBAN_CARD_ID
AND WL.LINE_ID (+)= WDJ.LINE_ID
--
AND WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND ML1.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
AND ML1.LOOKUP_TYPE = 'WIP_SUPPLY'
--AND OOD.ORGANIZATION_CODE in ('NA')
AND WDJ.STATUS_TYPE in (1,3)
--RESOURCE CONDITIONS
AND CA.ACTIVITY_ID(+) = WOR.ACTIVITY_ID
AND BR.RESOURCE_ID  = WOR.RESOURCE_ID
AND WO.WIP_ENTITY_ID (+) = WOR.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM (+) = WOR.OPERATION_SEQ_NUM
AND NVL(WO.REPETITIVE_SCHEDULE_ID,-1) = NVL(WOR.REPETITIVE_SCHEDULE_ID,-1)
AND WRS.ORGANIZATION_ID(+) = WOR.ORGANIZATION_ID
AND WRS.WIP_ENTITY_ID(+) = WOR.WIP_ENTITY_ID
AND WRS.REPETITIVE_SCHEDULE_ID(+) = WOR.REPETITIVE_SCHEDULE_ID);