View: POS_PO_OSP_V

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	APPS.POS_PO_OSP_V

View Text

SELECT PH.SEGMENT1
, PH.VENDOR_ID
, PH.VENDOR_SITE_ID
, PH.VENDOR_CONTACT_ID
, MSI.CONCATENATED_SEGMENTS
, PL.VENDOR_PRODUCT_NUM
, PL.ITEM_DESCRIPTION
, PS.NEED_BY_DATE
, PS.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'))
, 'Y'
, FND_MESSAGE.GET_STRING('WIP'
, 'AVAILABLE')
, NULL)
, PD.PO_HEADER_ID
, TO_NUMBER(NULL)
, 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
, PD.CREATION_DATE
, PD.CREATED_BY
, PD.LAST_UPDATE_DATE
, PD.LAST_UPDATED_BY
, PD.ORG_ID
, PDT.TYPE_NAME
, PH.AGENT_ID
, PH.SEGMENT1
, TO_NUMBER(NULL)
, PS.SHIP_TO_LOCATION_ID
, PS.SHIP_TO_ORGANIZATION_ID
, PS.SHIP_VIA_LOOKUP_CODE
, PD.WIP_RESOURCE_SEQ_NUM
, NVL(PH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, NVL(PS.CLOSED_CODE
, 'OPEN')
, PH.APPROVED_FLAG
, PL.CLOSED_CODE
, PH.CANCEL_FLAG
, PH.FROZEN_FLAG
, PH.USER_HOLD_FLAG
, PL.EXPIRATION_DATE
, PL.CANCEL_FLAG
FROM WIP_ENTITIES WE
, WIP_LINES WL
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_DISTRIBUTIONS_ALL PD
, PO_LINE_LOCATIONS_ALL PS
, PO_LINES_ALL PL
, PO_HEADERS_ALL PH
, PO_DOCUMENT_TYPES_ALL_VL PDT
, PO_LINE_TYPES PLT
WHERE UPPER(PLT.OUTSIDE_OPERATION_FLAG) = 'Y'
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PDT.ORG_ID = FSP.ORG_ID
AND PH.ORG_ID = FSP.ORG_ID
AND PH.TYPE_LOOKUP_CODE = 'STANDARD'
AND PH.APPROVED_FLAG IN ('Y'
, 'R')
AND PH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE')
AND PH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PD.PO_HEADER_ID = PH.PO_HEADER_ID
AND PD.PO_LINE_ID = PL.PO_LINE_ID
AND PD.LINE_LOCATION_ID = PS.LINE_LOCATION_ID
AND PL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID)
AND PD.WIP_ENTITY_ID = WE.WIP_ENTITY_ID(+)
AND PD.DESTINATION_ORGANIZATION_ID = WE.ORGANIZATION_ID(+)
AND PD.WIP_LINE_ID = WL.LINE_ID(+)
AND PD.DESTINATION_ORGANIZATION_ID = WL.ORGANIZATION_ID(+) UNION ALL SELECT PH.SEGMENT1||'-'||PR.RELEASE_NUM
, PH.VENDOR_ID
, PH.VENDOR_SITE_ID
, PH.VENDOR_CONTACT_ID
, MSI.CONCATENATED_SEGMENTS
, PL.VENDOR_PRODUCT_NUM
, PL.ITEM_DESCRIPTION
, PS.NEED_BY_DATE
, PS.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'))
, 'Y'
, FND_MESSAGE.GET_STRING('WIP'
, 'AVAILABLE')
, NULL)
, PD.PO_HEADER_ID
, 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
, PD.CREATION_DATE
, PD.CREATED_BY
, PD.LAST_UPDATE_DATE
, PD.LAST_UPDATED_BY
, PD.ORG_ID
, PDT.TYPE_NAME
, PH.AGENT_ID
, PH.SEGMENT1
, PR.RELEASE_NUM
, PS.SHIP_TO_LOCATION_ID
, PS.SHIP_TO_ORGANIZATION_ID
, PS.SHIP_VIA_LOOKUP_CODE
, PD.WIP_RESOURCE_SEQ_NUM
, NVL(PR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, NVL(PS.CLOSED_CODE
, 'OPEN')
, PH.APPROVED_FLAG
, PL.CLOSED_CODE
, PH.CANCEL_FLAG
, PH.FROZEN_FLAG
, PH.USER_HOLD_FLAG
, PL.EXPIRATION_DATE
, PL.CANCEL_FLAG
FROM WIP_ENTITIES WE
, WIP_LINES WL
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_DISTRIBUTIONS_ALL PD
, PO_LINE_LOCATIONS_ALL PS
, PO_LINES_ALL PL
, PO_LINE_TYPES PLT
, PO_DOCUMENT_TYPES_ALL PDT
, PO_HEADERS_ALL PH
, PO_RELEASES_ALL PR
WHERE PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND PR.APPROVED_FLAG IN ('Y'
, 'R')
AND PH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE')
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND UPPER(PLT.OUTSIDE_OPERATION_FLAG) = 'Y'
AND PH.PO_HEADER_ID = PR.PO_HEADER_ID
AND PS.PO_RELEASE_ID = PR.PO_RELEASE_ID
AND PDT.DOCUMENT_TYPE_CODE(+)='RELEASE'
AND PDT.ORG_ID = FSP.ORG_ID
AND PH.ORG_ID = FSP.ORG_ID
AND PDT.DOCUMENT_SUBTYPE(+) = PR.RELEASE_TYPE
AND PD.PO_HEADER_ID = PH.PO_HEADER_ID
AND PD.PO_LINE_ID = PL.PO_LINE_ID
AND PD.LINE_LOCATION_ID = PS.LINE_LOCATION_ID
AND PL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID)
AND PD.WIP_ENTITY_ID = WE.WIP_ENTITY_ID(+)
AND PD.DESTINATION_ORGANIZATION_ID = WE.ORGANIZATION_ID(+)
AND PD.WIP_LINE_ID = WL.LINE_ID(+)
AND PD.DESTINATION_ORGANIZATION_ID = WL.ORGANIZATION_ID(+)

Columns