VIEW: APPS.JAI_OE_MATCH_LINES_V

Source

View Text - Preformatted

SELECT wdd.organization_id , wdd.customer_id , wdd.inventory_item_id , wdd.delivery_detail_id , wdd.subinventory , wdd.source_header_number , wdd.source_header_id , wdd.source_line_id , wdd.requested_quantity_uom , nvl(wdd.picked_quantity,wdd.requested_quantity) , wnd.delivery_id , wdd.source_header_type_id , wdd.org_id , wdd.ship_from_location_id FROM wsh_delivery_details wdd , wsh_new_deliveries wnd , wsh_delivery_assignments wda , JAI_INV_SUBINV_DTLS jsubinv , JAI_INV_ITM_SETUPS jamsi WHERE wda.delivery_id = wnd.delivery_id AND wda.delivery_detail_id = wdd.delivery_detail_id AND wnd.organization_id = wdd.organization_id AND wdd.source_code = 'OE' AND wnd.status_code NOT IN ( 'CO', 'CL', 'IT' ) AND jsubinv.sub_inventory_name = wdd.subinventory AND jsubinv.location_id = wdd.ship_from_location_id AND jsubinv.trading = 'Y' AND jamsi.organization_id = wdd.organization_id AND jamsi.inventory_item_id = wdd.inventory_item_id AND jamsi.item_trading_flag = 'Y' AND jamsi.excise_flag = 'Y' AND /* cbabu for BUG#2504343 */ NOT EXISTS ( SELECT 1 FROM JAI_CMN_MATCH_RECEIPTS WHERE ref_line_id = wdd.delivery_detail_id AND order_invoice ='O' AND ship_status IS NULL )

View Text - HTML Formatted

SELECT WDD.ORGANIZATION_ID
, WDD.CUSTOMER_ID
, WDD.INVENTORY_ITEM_ID
, WDD.DELIVERY_DETAIL_ID
, WDD.SUBINVENTORY
, WDD.SOURCE_HEADER_NUMBER
, WDD.SOURCE_HEADER_ID
, WDD.SOURCE_LINE_ID
, WDD.REQUESTED_QUANTITY_UOM
, NVL(WDD.PICKED_QUANTITY
, WDD.REQUESTED_QUANTITY)
, WND.DELIVERY_ID
, WDD.SOURCE_HEADER_TYPE_ID
, WDD.ORG_ID
, WDD.SHIP_FROM_LOCATION_ID
FROM WSH_DELIVERY_DETAILS WDD
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_ASSIGNMENTS WDA
, JAI_INV_SUBINV_DTLS JSUBINV
, JAI_INV_ITM_SETUPS JAMSI
WHERE WDA.DELIVERY_ID = WND.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WND.ORGANIZATION_ID = WDD.ORGANIZATION_ID
AND WDD.SOURCE_CODE = 'OE'
AND WND.STATUS_CODE NOT IN ( 'CO'
, 'CL'
, 'IT' )
AND JSUBINV.SUB_INVENTORY_NAME = WDD.SUBINVENTORY
AND JSUBINV.LOCATION_ID = WDD.SHIP_FROM_LOCATION_ID
AND JSUBINV.TRADING = 'Y'
AND JAMSI.ORGANIZATION_ID = WDD.ORGANIZATION_ID
AND JAMSI.INVENTORY_ITEM_ID = WDD.INVENTORY_ITEM_ID
AND JAMSI.ITEM_TRADING_FLAG = 'Y'
AND JAMSI.EXCISE_FLAG = 'Y'
AND /* CBABU FOR BUG#2504343 */ NOT EXISTS ( SELECT 1
FROM JAI_CMN_MATCH_RECEIPTS
WHERE REF_LINE_ID = WDD.DELIVERY_DETAIL_ID
AND ORDER_INVOICE ='O'
AND SHIP_STATUS IS NULL )