View: WIP_ICX_OSP_WORKBENCH_V

Product:	WIP - Work in Process
Description:	WIP outside processing purchase orders information
Implementation/DBA Data:	APPS.WIP_ICX_OSP_WORKBENCH_V

View Text

SELECT DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PH.SEGMENT1
, 'BLANKET'
, PH.SEGMENT1||'-'||PR.RELEASE_NUM)
, MSI2.CONCATENATED_SEGMENTS
, PL.VENDOR_PRODUCT_NUM
, PL.ITEM_DESCRIPTION
, PLL.NEED_BY_DATE
, PLL.PROMISED_DATE
, PD.QUANTITY_ORDERED
, PD.QUANTITY_DELIVERED
, PL.UNIT_MEAS_LOOKUP_CODE
, WE.WIP_ENTITY_NAME
, WE.ENTITY_TYPE
, WL.LINE_CODE
, PD.WIP_REPETITIVE_SCHEDULE_ID
, PD.WIP_OPERATION_SEQ_NUM
, DECODE(WIP_WORK_ORDER_PVT.COMPONENT_EXIST( PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.WIP_REPETITIVE_SCHEDULE_ID
, PD.DESTINATION_ORGANIZATION_ID)
, 'Y'
, FND_MESSAGE.GET_STRING('WIP'
, 'AVAILABLE')
, NULL)
, DECODE(DECODE(WE.ENTITY_TYPE
, 1
, FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS( 'WIP_DISCRETE_OPERATIONS'
, PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, NULL
, NULL
, 'WIP_WIPOPMDF'
, 'F')
, 2
, FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS( 'WIP_REPETITIVE_OPERATIONS'
, PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, PD.WIP_REPETITIVE_SCHEDULE_ID
, NULL
, 'WIP_WIPOPMDF'
, 'F')
, 6
, FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS( 'EAM_DISCRETE_OPERATIONS'
, PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, NULL
, NULL
, 'EAMOPMDF'
, 'O'))
, 'Y'
, FND_MESSAGE.GET_STRING('WIP'
, 'AVAILABLE')
, NULL)
, PD.PO_HEADER_ID
, DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, TO_NUMBER(NULL)
, 'BLANKET'
, PR.PO_RELEASE_ID)
, PD.PO_LINE_ID
, PD.LINE_LOCATION_ID
, PD.PO_DISTRIBUTION_ID
, PD.DESTINATION_ORGANIZATION_ID
, PL.ITEM_ID
, PD.WIP_ENTITY_ID
, PD.WIP_LINE_ID
, PH.VENDOR_CONTACT_ID
, PH.VENDOR_ID
, PH.VENDOR_SITE_ID
, /* ADDED FOR R11I.1 */ PD.ORG_ID
, PDT.TYPE_NAME
, PH.AGENT_ID
, PH.SEGMENT1
, DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, TO_NUMBER(NULL)
, 'BLANKET'
, PR.RELEASE_NUM)
, PLL.SHIP_TO_LOCATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.SHIP_VIA_LOOKUP_CODE
, PD.WIP_RESOURCE_SEQ_NUM
, QA_SS_OSP.ARE_OSP_PLANS_APPLICABLE( MSI.CONCATENATED_SEGMENTS
, PV.VENDOR_NAME
, WE.WIP_ENTITY_NAME
, PH.SEGMENT1
, PL.VENDOR_PRODUCT_NUM
, PD.WIP_OPERATION_SEQ_NUM
, MSI.PRIMARY_UNIT_OF_MEASURE
, /* ASSEMBLY PRIMARY UOM */ WL.LINE_CODE
, WIP_OSP.CONVERTTOPRIMARYMOVEQTY( PL.ITEM_ID
, PD.DESTINATION_ORGANIZATION_ID
, PD.QUANTITY_ORDERED
, PL.UNIT_MEAS_LOOKUP_CODE
, /* OSP ITEM UOM-LONG */ MSI.PRIMARY_UNIT_OF_MEASURE
, ( SELECT WOR.USAGE_RATE_OR_AMOUNT
FROM WIP_OPERATION_RESOURCES WOR
WHERE WOR.WIP_ENTITY_ID = PD.WIP_ENTITY_ID
AND NVL(WOR.REPETITIVE_SCHEDULE_ID
, -1) = NVL(PD.WIP_REPETITIVE_SCHEDULE_ID
, -1)
AND WOR.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
AND WOR.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
AND WOR.RESOURCE_SEQ_NUM = PD.WIP_RESOURCE_SEQ_NUM ) )
, DECODE (PL.ITEM_ID
, WE.PRIMARY_ITEM_ID
, DECODE(WE.ENTITY_TYPE
, 2
, WRS.BOM_REVISION
, WDJ.BOM_REVISION)
, BOM_REVISIONS.GET_ITEM_REVISION_FN ( 'EXCLUDE_OPEN_HOLD'
, /* ECO_STATUS */ 'ALL'
, /* EXAMINE_TYPE*/ PD.DESTINATION_ORGANIZATION_ID
, PL.ITEM_ID
, DECODE(WE.ENTITY_TYPE
, 2
, WRS.FIRST_UNIT_START_DATE
, WDJ.SCHEDULED_START_DATE)))
, DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, NULL
, 'BLANKET'
, PR.RELEASE_NUM)
, PD.DESTINATION_ORGANIZATION_ID
, WE.ENTITY_TYPE)
, PV.VENDOR_NAME
, WE.PRIMARY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, DECODE (PL.ITEM_ID
, WE.PRIMARY_ITEM_ID
, DECODE(WE.ENTITY_TYPE
, 2
, WRS.BOM_REVISION
, WDJ.BOM_REVISION)
, BOM_REVISIONS.GET_ITEM_REVISION_FN( 'EXCLUDE_OPEN_HOLD'
, 'ALL'
, PD.DESTINATION_ORGANIZATION_ID
, PL.ITEM_ID
, DECODE(WE.ENTITY_TYPE
, 2
, WRS.FIRST_UNIT_START_DATE
, WDJ.SCHEDULED_START_DATE)))
, WIP_OSP.CONVERTTOPRIMARYMOVEQTY( PL.ITEM_ID
, PD.DESTINATION_ORGANIZATION_ID
, PD.QUANTITY_ORDERED
, PL.UNIT_MEAS_LOOKUP_CODE
, MSI.PRIMARY_UNIT_OF_MEASURE
, ( SELECT WOR.USAGE_RATE_OR_AMOUNT
FROM WIP_OPERATION_RESOURCES WOR
WHERE WOR.WIP_ENTITY_ID = PD.WIP_ENTITY_ID
AND NVL(WOR.REPETITIVE_SCHEDULE_ID
, -1) = NVL(PD.WIP_REPETITIVE_SCHEDULE_ID
, -1)
AND WOR.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
AND WOR.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
AND WOR.RESOURCE_SEQ_NUM = PD.WIP_RESOURCE_SEQ_NUM ) )
, MSI.PRIMARY_UNIT_OF_MEASURE
, MSI.DESCRIPTION
, PD.CREATION_DATE
, PD.CREATED_BY
, PD.LAST_UPDATE_DATE
, PD.LAST_UPDATED_BY
, WDJ.SCHEDULED_START_DATE
, PL.CLOSED_CODE
, DECODE(WE.ENTITY_TYPE
, 2
, WRS.BOM_REVISION
, WDJ.BOM_REVISION)
, HRO.NAME OPERATING_UNIT_CODE
, DECODE(WE.ENTITY_TYPE
, 6
, DECODE(WO.OPERATION_COMPLETED
, 'Y'
, 'DISABLEOPSTATUSLINK'
, 'ENABLEOPSTATUSLINK')
, 'DISABLEOPSTATUSLINK') AS OPERATION_STATUS_LINK
, ML.MEANING AS OPERATIONSTATUS
, NVL(WO.OPERATION_COMPLETED
, 'N') AS OPERATIONCOMPLETED
, PD.WIP_OPERATION_SEQ_NUM AS OPSEQNUM
, WE.WIP_ENTITY_NAME || ':' || PD.WIP_OPERATION_SEQ_NUM AS WORK_ORDER_NAME
FROM PO_HEADERS_ALL PH
, PO_DISTRIBUTIONS_ALL PD
, PO_DOCUMENT_TYPES_ALL PDT
, PO_LINES_ALL PL
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL PR
, PO_VENDORS PV
, WIP_ENTITIES WE
, WIP_DISCRETE_JOBS WDJ
, WIP_LINES WL
, WIP_REPETITIVE_SCHEDULES WRS
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_SYSTEM_ITEMS_KFV MSI2
, HR_ALL_ORGANIZATION_UNITS_TL HRO
, WIP_OPERATIONS WO
, MFG_LOOKUPS ML
WHERE PH.PO_HEADER_ID = PD.PO_HEADER_ID
AND PD.PO_LINE_ID = PL.PO_LINE_ID
AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PDT.ORG_ID = PH.ORG_ID
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'RELEASE')
AND PH.APPROVED_FLAG = 'Y'
AND ( PR.APPROVED_FLAG IS NULL OR PR.APPROVED_FLAG IN ('Y'
, 'R') )
AND PD.WIP_LINE_ID = WL.LINE_ID(+)
AND PD.DESTINATION_ORGANIZATION_ID = WL.ORGANIZATION_ID(+)
AND PL.ITEM_ID = MSI2.INVENTORY_ITEM_ID (+)
AND PLL.SHIP_TO_ORGANIZATION_ID = NVL(MSI2.ORGANIZATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID)
AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID (+)
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PD.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND PD.DESTINATION_ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WO.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WO.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
AND NVL(WO.REPETITIVE_SCHEDULE_ID
, -1) = NVL(PD.WIP_REPETITIVE_SCHEDULE_ID
, -1)
AND ML.LOOKUP_TYPE = 'EAM_OP_STATUS'
AND ML.LOOKUP_CODE = DECODE(WO.OPERATION_COMPLETED
, 'Y'
, 3
, 2)
AND WE.ORGANIZATION_ID= MSI.ORGANIZATION_ID(+)
AND WE.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND PD.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID(+)
AND PD.DESTINATION_ORGANIZATION_ID = WDJ.ORGANIZATION_ID(+)
AND PD.WIP_REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID(+)
AND PD.DESTINATION_ORGANIZATION_ID = WRS.ORGANIZATION_ID(+)
AND ( PLL.CLOSED_CODE IS NULL OR PLL.CLOSED_CODE NOT IN ('FINALLY CLOSED'
, 'CLOSED') )
AND ( PL.CLOSED_CODE IS NULL OR PL.CLOSED_CODE NOT IN ('FINALLY CLOSED'
, 'CLOSED') )
AND ( PH.CANCEL_FLAG IS NULL OR PH.CANCEL_FLAG ='N' )
AND ( PH.FROZEN_FLAG IS NULL OR PH.FROZEN_FLAG = 'N' )
AND ( PH.USER_HOLD_FLAG IS NULL OR PH.USER_HOLD_FLAG = 'N' )
AND ( PL.EXPIRATION_DATE IS NULL OR PL.EXPIRATION_DATE > SYSDATE )
AND ( PL.CANCEL_FLAG IS NULL OR PL.CANCEL_FLAG = 'N' )
AND HRO.ORGANIZATION_ID = PD.ORG_ID
AND HRO.LANGUAGE = USERENV('LANG')

Columns