VIEW: APPS.WIP_ICX_OSP_WORKBENCH_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')