View: WIP_OPSM_WORK_ORDER_EXPORT_V

Product:	WIP - Work in Process
Description:
Implementation/DBA Data:	APPS.WIP_OPSM_WORK_ORDER_EXPORT_V

View Text

SELECT WE.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, WE.WIP_ENTITY_NAME
, 'DISCRETE'
, WDJ.START_QUANTITY
, DECODE (WDJ.OVERCOMPLETION_TOLERANCE_TYPE
, 1
, WDJ.START_QUANTITY * (WDJ.OVERCOMPLETION_TOLERANCE_VALUE / 100)
, WDJ.OVERCOMPLETION_TOLERANCE_VALUE)
, WDJ.QUANTITY_COMPLETED
, WDJ.START_QUANTITY
, WDJ.QUANTITY_COMPLETED
, UPPER(MLK.MEANING)
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.DATE_RELEASED
, WDJ.DATE_COMPLETED
, WDJ.DATE_CLOSED
, WDJ.FIRM_PLANNED_FLAG
, WDJ.CREATION_DATE
, WDJ.CREATED_BY
, WDJ.LAST_UPDATE_DATE
, WDJ.LAST_UPDATED_BY
, WDJ.LAST_UPDATE_LOGIN
, WDJ.LOT_NUMBER
, MLN.LOT_NUMBER
, MLN.PARENT_LOT_NUMBER
, MLN.EXPIRATION_DATE
, MLN.ORIGINATION_DATE
, MLN.RETEST_DATE
, MLN.BEST_BY_DATE
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, MSIK.PRIMARY_UOM_CODE FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, MFG_LOOKUPS MLK
, MTL_SYSTEM_ITEMS_B_KFV MSIK
, MTL_PARAMETERS MP
, MTL_CROSS_REFERENCES MCR
, MTL_LOT_NUMBERS MLN WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MLK.LOOKUP_TYPE(+) = 'WIP_JOB_STATUS'
AND MLK.LOOKUP_CODE(+) = WDJ.STATUS_TYPE
AND MSIK.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND MSIK.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND MLN.LOT_NUMBER(+) = WDJ.LOT_NUMBER
AND MLN.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND MLN.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND MSIK.LOT_CONTROL_CODE = 2
AND MP.OPSM_ENABLED_FLAG = 'Y'
AND MP.MASTER_ORGANIZATION_ID = MCR.ORGANIZATION_ID
AND MCR.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND MCR.CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
AND MCR.CROSS_REFERENCE = 'YES'
AND NOT EXISTS (SELECT 'Y'
FROM MTL_CROSS_REFERENCES
WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
AND ORGANIZATION_ID = WE.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND UPPER(CROSS_REFERENCE) ='NO')