VIEW: APPS.POS_PO_OSP_V

Source

View Text - Preformatted

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,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'), 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' OR pd.destination_type_code='SHOP FLOOR') 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') AND PH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL') 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,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'), 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') AND PR.AUTHORIZATION_STATUS NOT IN ('IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL') AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND (UPPER(PLT.OUTSIDE_OPERATION_FLAG) = 'Y' OR pd.destination_type_code='SHOP FLOOR') 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(+)

View Text - HTML Formatted

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
, 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')
, 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' OR PD.DESTINATION_TYPE_CODE='SHOP FLOOR')
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')
AND PH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL')
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
, 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')
, 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')
AND PR.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL')
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND (UPPER(PLT.OUTSIDE_OPERATION_FLAG) = 'Y' OR PD.DESTINATION_TYPE_CODE='SHOP FLOOR')
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(+)