VIEW: APPS.CSP_EOO_CANCEL_RECOMM_V

Source

View Text - Preformatted

SELECT sup.expected_delivery_date expected_delivery, nvl(PRICING_QUANTITY, 0) quantity, sup.to_org_primary_uom uom, sup.item_id item_id, msik.concatenated_segments item_number, sup.to_organization_id organization_id, sup.to_subinventory dest_subinv, sup.from_organization_id source_name_id, hou.name source_name, fl3.meaning order_Type, decode(req.transferred_to_oe_flag, 'Y', to_char(ord.order_number), req.segment1) order_number, decode(supply_type_code, 'RECEIVING', fl1.meaning, 'SHIPMENT',decode(req.transferred_to_oe_flag, 'Y', ol.meaning, plc.displayed_field), 'REQ', decode(req.transferred_to_oe_flag, 'Y', ol.meaning, plc.displayed_field)) status, plt.line_type line_Type FROM mtl_supply sup, po_requisition_headers_all req, po_requisition_lines_all prl, oe_order_headers_All ord, oe_order_lines_all oel, oe_lookups ol, po_lookup_codes plc, po_line_types plt, fnd_lookups fl1, fnd_lookups fl2, fnd_lookups fl3, mtl_system_items_kfv msik, hr_all_organization_units hou WHERE hou.organization_id(+) = sup.from_organization_id AND msik.organization_id = sup.to_organization_id AND msik.inventory_item_id = sup.item_id AND sup.supply_type_code IN ('REQ','SHIPMENT','RECEIVING') AND sup.destination_type_code = 'INVENTORY' AND req.requisition_header_id = sup.req_header_id AND prl.requisition_line_id = sup.req_line_id AND prl.requisition_header_id = sup.req_header_id AND ord.source_document_id(+) = req.requisition_header_id and ord.order_source_id(+) = 10 and oel.order_source_id(+) = 10 and oel.source_document_id(+) = prl.requisition_header_id and oel.source_document_line_id(+) = prl.requisition_line_id and oel.flow_status_code = ol.lookup_code(+) and ol.lookup_type(+) = 'LINE_FLOW_STATUS' AND plc.lookup_code = req.authorization_status AND plc.lookup_type = 'AUTHORIZATION STATUS' AND plt.line_type_id = prl.line_type_id AND fl1.lookup_code = 'RECEIVING' AND fl1.lookup_type = 'CSP_SUPPLY_TYPE' AND fl2.lookup_code = 'SHIPMENT' AND fl2.lookup_Type = 'CSP_SUPPLY_TYPE' AND decode(req.transferred_to_oe_flag, 'Y', 'IO', decode(req.type_lookup_code, 'PURCHASE', 'PURREQ', 'REQ')) = fl3.lookup_code AND fl3.lookup_type = 'CSP_NOTIF_DTL_SRC_TYPE' UNION SELECT sup.expected_delivery_date expected_delivery, nvl(sup.to_org_primary_quantity, 0) quantity, sup.to_org_primary_uom uom, sup.item_id item_id, msik.concatenated_segments item_number, sup.to_organization_id organization_id, sup.to_subinventory dest_subinv, po.vendor_id source_name_id, pv.vendor_name source_name, fl3.meaning order_Type, po.segment1 order_number, decode(sup.supply_Type_code, 'RECEIVING', fl1.meaning, 'SHIPMENT', fl2.meaning, 'PO', nvl(po_headers_sv3.get_po_status(sup.po_header_id), plc.displayed_field)) status, plt.line_type line_type FROM mtl_supply sup, po_headers_all po, po_lines_all pol, po_lookup_codes plc, po_line_types plt, fnd_lookups fl1, fnd_lookups fl2, mtl_system_items_kfv msik, po_Vendors pv, fnd_lookups fl3 WHERE pv.vendor_id = po.vendor_id AND po.po_header_id = sup.po_header_id AND sup.supply_type_code in ('PO', 'SHIPMENT', 'RECEIVING') AND msik.organization_id = sup.to_organization_id AND msik.inventory_item_id = sup.item_id AND sup.destination_type_code = 'INVENTORY' AND plc.lookup_code = po.authorization_status AND plc.lookup_type = 'AUTHORIZATION STATUS' AND pol.po_line_id = sup.po_line_id AND pol.po_header_id = po.po_header_id AND plt.line_Type_id = pol.line_Type_id AND fl1.lookup_code = 'RECEIVING' AND fl1.lookup_type = 'CSP_SUPPLY_TYPE' AND fl2.lookup_code = 'SHIPMENT' AND fl2.lookup_Type = 'CSP_SUPPLY_TYPE' AND sup.supply_type_code = fl3.lookup_code AND fl3.lookup_type = 'CSP_NOTIF_DTL_SRC_TYPE' UNION SELECT to_date(null) expected_delivery, pol.quantity quantity, msik.primary_unit_of_measure uom, pol.item_id item_id, msik.concatenated_segments item_number, pol.destination_organization_id organization_id, pol.destination_subinventory dest_subinv, pol.source_organization_id source_name_id, hou.name source_name, fl3.meaning order_Type, poh.segment1 order_number, plc.displayed_field status, plt.line_type line_type FROM po_requisition_headers_all poh, po_requisition_lines_all pol, po_lookup_codes plc, po_line_types plt, mtl_system_items_kfv msik, hr_all_organization_units hou, fnd_lookups fl3 WHERE hou.organization_id = pol.source_organization_id AND pol.source_type_code = 'INVENTORY' AND msik.organization_id = pol.destination_organization_id AND msik.inventory_item_id = pol.item_id AND poh.authorization_status = 'INCOMPLETE' AND pol.requisition_header_id = poh.requisition_header_id AND pol.destination_type_code = 'INVENTORY' AND plc.lookup_code = poh.authorization_status AND plc.lookup_type = 'AUTHORIZATION STATUS' AND plt.line_type_id = pol.line_type_id AND fl3.lookup_code = 'REQ' AND fl3.lookup_type = 'CSP_NOTIF_DTL_SRC_TYPE' UNION SELECT to_date(null) expected_delivery, pol.quantity quantity, msik.primary_unit_of_measure uom, pol.item_id item_id, msik.concatenated_segments item_number, pol.destination_organization_id organization_id, pol.destination_subinventory dest_subinv, pol.vendor_id source_name_id, pv.vendor_name source_name, fl3.meaning order_Type, poh.segment1 order_number, plc.displayed_field status, plt.line_type line_type FROM po_requisition_headers_all poh, po_requisition_lines_all pol, po_lookup_codes plc, po_line_types plt, mtl_system_items_kfv msik, po_Vendors pv, fnd_lookups fl3 WHERE pv.vendor_id(+) = pol.vendor_id AND pol.source_type_code = 'VENDOR' AND msik.organization_id = pol.destination_organization_id AND msik.inventory_item_id = pol.item_id AND poh.authorization_status = 'INCOMPLETE' AND pol.requisition_header_id = poh.requisition_header_id AND pol.destination_type_code = 'INVENTORY' AND plc.lookup_code = poh.authorization_status AND plc.lookup_type = 'AUTHORIZATION STATUS' AND plt.line_type_id = pol.line_type_id AND fl3.lookup_code = 'PURREQ' AND fl3.lookup_type = 'CSP_NOTIF_DTL_SRC_TYPE' UNION SELECT wdj.scheduled_completion_date expected_Delivery, (NVL(start_quantity,0) - NVL(quantity_completed,0) - NVL(quantity_scrapped,0)) quantity, msik.primary_unit_of_measure uom, wdj.primary_item_id item_id, msik.concatenated_Segments item_number, wdj.organization_id organization_id, wdj.completion_subinventory dest_subinv, wdj.organization_id source_name_id, hou.name source_name, fl3.meaning order_type, we.wip_entity_name order_number, ml.meaning status, wdj.class_code line_type FROM wip_discrete_jobs wdj, wip_entities we, mfg_lookups ml, mtl_system_items_kfv msik, hr_All_organization_units hou, fnd_lookups fl3 WHERE hou.organization_id = wdj.organization_id AND msik.organization_id = wdj.organization_id AND msik.inventory_item_id = wdj.primary_item_id AND job_type in (1,3) AND status_type IN (1,3) AND (NVL(start_quantity,0) - NVL(quantity_completed,0) - NVL(quantity_scrapped,0)) > 0 AND ml.lookup_type = 'WIP_JOB_STATUS' AND ml.lookup_code = status_type AND we.wip_entity_id = wdj.wip_entity_id AND fl3.lookup_code = 'WIP' AND fl3.lookup_Type = 'CSP_NOTIF_DTL_SRC_TYPE' UNION SELECT wrs.last_unit_completion_Date expected_delivery, (daily_production_rate * LEAST(0, processing_work_days) - quantity_completed) quantity, msik.primary_unit_of_measure uom, wri.primary_item_id item_id, msik.concatenated_Segments item_number, wri.organization_id organization_id, wri.completion_subinventory dest_subinv, wri.organization_id source_name_id, hou.name source_name, fl3.meaning order_type, we.wip_entity_name order_numberr, ml.meaning status, wri.class_code line_type FROM wip_repetitive_items wri, wip_repetitive_schedules wrs, wip_entities we, mfg_lookups ml, mtl_system_items_kfv msik, hr_All_organization_units hou, fnd_lookups fl3 WHERE hou.organization_id = wri.organization_id AND msik.organization_id = wri.organization_id AND msik.inventory_item_id = wri.primary_item_id AND wrs.status_type IN (1,3) AND wrs.organization_id = wri.organization_id AND wrs.wip_entity_id = wri.wip_entity_id AND wrs.line_id = wri.line_id AND (daily_production_rate * LEAST(0, processing_work_days)- quantity_completed) > 0 AND ml.lookup_code = wrs.status_type AND ml.lookup_type = 'WIP_JOB_STATUS' AND we.wip_entity_id = wrs.wip_entity_id AND fl3.lookup_code = 'WIP' AND fl3.lookup_Type = 'CSP_NOTIF_DTL_SRC_TYPE'

View Text - HTML Formatted

SELECT SUP.EXPECTED_DELIVERY_DATE EXPECTED_DELIVERY
, NVL(PRICING_QUANTITY
, 0) QUANTITY
, SUP.TO_ORG_PRIMARY_UOM UOM
, SUP.ITEM_ID ITEM_ID
, MSIK.CONCATENATED_SEGMENTS ITEM_NUMBER
, SUP.TO_ORGANIZATION_ID ORGANIZATION_ID
, SUP.TO_SUBINVENTORY DEST_SUBINV
, SUP.FROM_ORGANIZATION_ID SOURCE_NAME_ID
, HOU.NAME SOURCE_NAME
, FL3.MEANING ORDER_TYPE
, DECODE(REQ.TRANSFERRED_TO_OE_FLAG
, 'Y'
, TO_CHAR(ORD.ORDER_NUMBER)
, REQ.SEGMENT1) ORDER_NUMBER
, DECODE(SUPPLY_TYPE_CODE
, 'RECEIVING'
, FL1.MEANING
, 'SHIPMENT'
, DECODE(REQ.TRANSFERRED_TO_OE_FLAG
, 'Y'
, OL.MEANING
, PLC.DISPLAYED_FIELD)
, 'REQ'
, DECODE(REQ.TRANSFERRED_TO_OE_FLAG
, 'Y'
, OL.MEANING
, PLC.DISPLAYED_FIELD)) STATUS
, PLT.LINE_TYPE LINE_TYPE
FROM MTL_SUPPLY SUP
, PO_REQUISITION_HEADERS_ALL REQ
, PO_REQUISITION_LINES_ALL PRL
, OE_ORDER_HEADERS_ALL ORD
, OE_ORDER_LINES_ALL OEL
, OE_LOOKUPS OL
, PO_LOOKUP_CODES PLC
, PO_LINE_TYPES PLT
, FND_LOOKUPS FL1
, FND_LOOKUPS FL2
, FND_LOOKUPS FL3
, MTL_SYSTEM_ITEMS_KFV MSIK
, HR_ALL_ORGANIZATION_UNITS HOU
WHERE HOU.ORGANIZATION_ID(+) = SUP.FROM_ORGANIZATION_ID
AND MSIK.ORGANIZATION_ID = SUP.TO_ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = SUP.ITEM_ID
AND SUP.SUPPLY_TYPE_CODE IN ('REQ'
, 'SHIPMENT'
, 'RECEIVING')
AND SUP.DESTINATION_TYPE_CODE = 'INVENTORY'
AND REQ.REQUISITION_HEADER_ID = SUP.REQ_HEADER_ID
AND PRL.REQUISITION_LINE_ID = SUP.REQ_LINE_ID
AND PRL.REQUISITION_HEADER_ID = SUP.REQ_HEADER_ID
AND ORD.SOURCE_DOCUMENT_ID(+) = REQ.REQUISITION_HEADER_ID
AND ORD.ORDER_SOURCE_ID(+) = 10
AND OEL.ORDER_SOURCE_ID(+) = 10
AND OEL.SOURCE_DOCUMENT_ID(+) = PRL.REQUISITION_HEADER_ID
AND OEL.SOURCE_DOCUMENT_LINE_ID(+) = PRL.REQUISITION_LINE_ID
AND OEL.FLOW_STATUS_CODE = OL.LOOKUP_CODE(+)
AND OL.LOOKUP_TYPE(+) = 'LINE_FLOW_STATUS'
AND PLC.LOOKUP_CODE = REQ.AUTHORIZATION_STATUS
AND PLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PLT.LINE_TYPE_ID = PRL.LINE_TYPE_ID
AND FL1.LOOKUP_CODE = 'RECEIVING'
AND FL1.LOOKUP_TYPE = 'CSP_SUPPLY_TYPE'
AND FL2.LOOKUP_CODE = 'SHIPMENT'
AND FL2.LOOKUP_TYPE = 'CSP_SUPPLY_TYPE'
AND DECODE(REQ.TRANSFERRED_TO_OE_FLAG
, 'Y'
, 'IO'
, DECODE(REQ.TYPE_LOOKUP_CODE
, 'PURCHASE'
, 'PURREQ'
, 'REQ')) = FL3.LOOKUP_CODE
AND FL3.LOOKUP_TYPE = 'CSP_NOTIF_DTL_SRC_TYPE' UNION SELECT SUP.EXPECTED_DELIVERY_DATE EXPECTED_DELIVERY
, NVL(SUP.TO_ORG_PRIMARY_QUANTITY
, 0) QUANTITY
, SUP.TO_ORG_PRIMARY_UOM UOM
, SUP.ITEM_ID ITEM_ID
, MSIK.CONCATENATED_SEGMENTS ITEM_NUMBER
, SUP.TO_ORGANIZATION_ID ORGANIZATION_ID
, SUP.TO_SUBINVENTORY DEST_SUBINV
, PO.VENDOR_ID SOURCE_NAME_ID
, PV.VENDOR_NAME SOURCE_NAME
, FL3.MEANING ORDER_TYPE
, PO.SEGMENT1 ORDER_NUMBER
, DECODE(SUP.SUPPLY_TYPE_CODE
, 'RECEIVING'
, FL1.MEANING
, 'SHIPMENT'
, FL2.MEANING
, 'PO'
, NVL(PO_HEADERS_SV3.GET_PO_STATUS(SUP.PO_HEADER_ID)
, PLC.DISPLAYED_FIELD)) STATUS
, PLT.LINE_TYPE LINE_TYPE
FROM MTL_SUPPLY SUP
, PO_HEADERS_ALL PO
, PO_LINES_ALL POL
, PO_LOOKUP_CODES PLC
, PO_LINE_TYPES PLT
, FND_LOOKUPS FL1
, FND_LOOKUPS FL2
, MTL_SYSTEM_ITEMS_KFV MSIK
, PO_VENDORS PV
, FND_LOOKUPS FL3
WHERE PV.VENDOR_ID = PO.VENDOR_ID
AND PO.PO_HEADER_ID = SUP.PO_HEADER_ID
AND SUP.SUPPLY_TYPE_CODE IN ('PO'
, 'SHIPMENT'
, 'RECEIVING')
AND MSIK.ORGANIZATION_ID = SUP.TO_ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = SUP.ITEM_ID
AND SUP.DESTINATION_TYPE_CODE = 'INVENTORY'
AND PLC.LOOKUP_CODE = PO.AUTHORIZATION_STATUS
AND PLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND POL.PO_LINE_ID = SUP.PO_LINE_ID
AND POL.PO_HEADER_ID = PO.PO_HEADER_ID
AND PLT.LINE_TYPE_ID = POL.LINE_TYPE_ID
AND FL1.LOOKUP_CODE = 'RECEIVING'
AND FL1.LOOKUP_TYPE = 'CSP_SUPPLY_TYPE'
AND FL2.LOOKUP_CODE = 'SHIPMENT'
AND FL2.LOOKUP_TYPE = 'CSP_SUPPLY_TYPE'
AND SUP.SUPPLY_TYPE_CODE = FL3.LOOKUP_CODE
AND FL3.LOOKUP_TYPE = 'CSP_NOTIF_DTL_SRC_TYPE' UNION SELECT TO_DATE(NULL) EXPECTED_DELIVERY
, POL.QUANTITY QUANTITY
, MSIK.PRIMARY_UNIT_OF_MEASURE UOM
, POL.ITEM_ID ITEM_ID
, MSIK.CONCATENATED_SEGMENTS ITEM_NUMBER
, POL.DESTINATION_ORGANIZATION_ID ORGANIZATION_ID
, POL.DESTINATION_SUBINVENTORY DEST_SUBINV
, POL.SOURCE_ORGANIZATION_ID SOURCE_NAME_ID
, HOU.NAME SOURCE_NAME
, FL3.MEANING ORDER_TYPE
, POH.SEGMENT1 ORDER_NUMBER
, PLC.DISPLAYED_FIELD STATUS
, PLT.LINE_TYPE LINE_TYPE
FROM PO_REQUISITION_HEADERS_ALL POH
, PO_REQUISITION_LINES_ALL POL
, PO_LOOKUP_CODES PLC
, PO_LINE_TYPES PLT
, MTL_SYSTEM_ITEMS_KFV MSIK
, HR_ALL_ORGANIZATION_UNITS HOU
, FND_LOOKUPS FL3
WHERE HOU.ORGANIZATION_ID = POL.SOURCE_ORGANIZATION_ID
AND POL.SOURCE_TYPE_CODE = 'INVENTORY'
AND MSIK.ORGANIZATION_ID = POL.DESTINATION_ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = POL.ITEM_ID
AND POH.AUTHORIZATION_STATUS = 'INCOMPLETE'
AND POL.REQUISITION_HEADER_ID = POH.REQUISITION_HEADER_ID
AND POL.DESTINATION_TYPE_CODE = 'INVENTORY'
AND PLC.LOOKUP_CODE = POH.AUTHORIZATION_STATUS
AND PLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PLT.LINE_TYPE_ID = POL.LINE_TYPE_ID
AND FL3.LOOKUP_CODE = 'REQ'
AND FL3.LOOKUP_TYPE = 'CSP_NOTIF_DTL_SRC_TYPE' UNION SELECT TO_DATE(NULL) EXPECTED_DELIVERY
, POL.QUANTITY QUANTITY
, MSIK.PRIMARY_UNIT_OF_MEASURE UOM
, POL.ITEM_ID ITEM_ID
, MSIK.CONCATENATED_SEGMENTS ITEM_NUMBER
, POL.DESTINATION_ORGANIZATION_ID ORGANIZATION_ID
, POL.DESTINATION_SUBINVENTORY DEST_SUBINV
, POL.VENDOR_ID SOURCE_NAME_ID
, PV.VENDOR_NAME SOURCE_NAME
, FL3.MEANING ORDER_TYPE
, POH.SEGMENT1 ORDER_NUMBER
, PLC.DISPLAYED_FIELD STATUS
, PLT.LINE_TYPE LINE_TYPE
FROM PO_REQUISITION_HEADERS_ALL POH
, PO_REQUISITION_LINES_ALL POL
, PO_LOOKUP_CODES PLC
, PO_LINE_TYPES PLT
, MTL_SYSTEM_ITEMS_KFV MSIK
, PO_VENDORS PV
, FND_LOOKUPS FL3
WHERE PV.VENDOR_ID(+) = POL.VENDOR_ID
AND POL.SOURCE_TYPE_CODE = 'VENDOR'
AND MSIK.ORGANIZATION_ID = POL.DESTINATION_ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = POL.ITEM_ID
AND POH.AUTHORIZATION_STATUS = 'INCOMPLETE'
AND POL.REQUISITION_HEADER_ID = POH.REQUISITION_HEADER_ID
AND POL.DESTINATION_TYPE_CODE = 'INVENTORY'
AND PLC.LOOKUP_CODE = POH.AUTHORIZATION_STATUS
AND PLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PLT.LINE_TYPE_ID = POL.LINE_TYPE_ID
AND FL3.LOOKUP_CODE = 'PURREQ'
AND FL3.LOOKUP_TYPE = 'CSP_NOTIF_DTL_SRC_TYPE' UNION SELECT WDJ.SCHEDULED_COMPLETION_DATE EXPECTED_DELIVERY
, (NVL(START_QUANTITY
, 0) - NVL(QUANTITY_COMPLETED
, 0) - NVL(QUANTITY_SCRAPPED
, 0)) QUANTITY
, MSIK.PRIMARY_UNIT_OF_MEASURE UOM
, WDJ.PRIMARY_ITEM_ID ITEM_ID
, MSIK.CONCATENATED_SEGMENTS ITEM_NUMBER
, WDJ.ORGANIZATION_ID ORGANIZATION_ID
, WDJ.COMPLETION_SUBINVENTORY DEST_SUBINV
, WDJ.ORGANIZATION_ID SOURCE_NAME_ID
, HOU.NAME SOURCE_NAME
, FL3.MEANING ORDER_TYPE
, WE.WIP_ENTITY_NAME ORDER_NUMBER
, ML.MEANING STATUS
, WDJ.CLASS_CODE LINE_TYPE
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, MFG_LOOKUPS ML
, MTL_SYSTEM_ITEMS_KFV MSIK
, HR_ALL_ORGANIZATION_UNITS HOU
, FND_LOOKUPS FL3
WHERE HOU.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MSIK.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = WDJ.PRIMARY_ITEM_ID
AND JOB_TYPE IN (1
, 3)
AND STATUS_TYPE IN (1
, 3)
AND (NVL(START_QUANTITY
, 0) - NVL(QUANTITY_COMPLETED
, 0) - NVL(QUANTITY_SCRAPPED
, 0)) > 0
AND ML.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND ML.LOOKUP_CODE = STATUS_TYPE
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND FL3.LOOKUP_CODE = 'WIP'
AND FL3.LOOKUP_TYPE = 'CSP_NOTIF_DTL_SRC_TYPE' UNION SELECT WRS.LAST_UNIT_COMPLETION_DATE EXPECTED_DELIVERY
, (DAILY_PRODUCTION_RATE * LEAST(0
, PROCESSING_WORK_DAYS) - QUANTITY_COMPLETED) QUANTITY
, MSIK.PRIMARY_UNIT_OF_MEASURE UOM
, WRI.PRIMARY_ITEM_ID ITEM_ID
, MSIK.CONCATENATED_SEGMENTS ITEM_NUMBER
, WRI.ORGANIZATION_ID ORGANIZATION_ID
, WRI.COMPLETION_SUBINVENTORY DEST_SUBINV
, WRI.ORGANIZATION_ID SOURCE_NAME_ID
, HOU.NAME SOURCE_NAME
, FL3.MEANING ORDER_TYPE
, WE.WIP_ENTITY_NAME ORDER_NUMBERR
, ML.MEANING STATUS
, WRI.CLASS_CODE LINE_TYPE
FROM WIP_REPETITIVE_ITEMS WRI
, WIP_REPETITIVE_SCHEDULES WRS
, WIP_ENTITIES WE
, MFG_LOOKUPS ML
, MTL_SYSTEM_ITEMS_KFV MSIK
, HR_ALL_ORGANIZATION_UNITS HOU
, FND_LOOKUPS FL3
WHERE HOU.ORGANIZATION_ID = WRI.ORGANIZATION_ID
AND MSIK.ORGANIZATION_ID = WRI.ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = WRI.PRIMARY_ITEM_ID
AND WRS.STATUS_TYPE IN (1
, 3)
AND WRS.ORGANIZATION_ID = WRI.ORGANIZATION_ID
AND WRS.WIP_ENTITY_ID = WRI.WIP_ENTITY_ID
AND WRS.LINE_ID = WRI.LINE_ID
AND (DAILY_PRODUCTION_RATE * LEAST(0
, PROCESSING_WORK_DAYS)- QUANTITY_COMPLETED) > 0
AND ML.LOOKUP_CODE = WRS.STATUS_TYPE
AND ML.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND WE.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
AND FL3.LOOKUP_CODE = 'WIP'
AND FL3.LOOKUP_TYPE = 'CSP_NOTIF_DTL_SRC_TYPE'