VIEW: APPS.WSH_BIS_WAREHOUSE_DOCS_V

Source

View Text - Preformatted

SELECT pld.pick_slip_number, max(ph.warehouse_id) warehouse_id, max(ph.creation_date) date_printed, decode( sum(decode(dep.date_closed,null,1,0)), 0, max(dep.date_closed), null) date_closed, sum(1) number_of_lines, SUM( NVL(si.unit_weight, 0) * WSH_WV_PVT.convert_uom(pl.unit_code, si.primary_uom_code, pld.requested_quantity, pl.inventory_item_id) ) weight, SUM( NVL(si.unit_volume, 0) * WSH_WV_PVT.convert_uom(pl.unit_code, si.primary_uom_code, pld.requested_quantity, pl.inventory_item_id) ) volume, sum( decode(pl.included_item_flag, 'Y',0, l.selling_price * pld.requested_quantity)) value from so_picking_batches_all pb, so_picking_headers_all ph, so_picking_lines_all pl, so_picking_line_details pld, so_lines_all l, wsh_departures dep, mtl_system_items si where pb.batch_id = ph.batch_id and ph.picking_header_id = pl.picking_header_id and pl.picking_line_id = pld.picking_line_id and pl.order_line_id = l.line_id and pld.departure_id = dep.departure_id(+) and pld.pick_slip_number is not null and pl.inventory_item_id = si.inventory_item_id and ph.warehouse_id = si.organization_id group by pld.pick_slip_number

View Text - HTML Formatted

SELECT PLD.PICK_SLIP_NUMBER
, MAX(PH.WAREHOUSE_ID) WAREHOUSE_ID
, MAX(PH.CREATION_DATE) DATE_PRINTED
, DECODE( SUM(DECODE(DEP.DATE_CLOSED
, NULL
, 1
, 0))
, 0
, MAX(DEP.DATE_CLOSED)
, NULL) DATE_CLOSED
, SUM(1) NUMBER_OF_LINES
, SUM( NVL(SI.UNIT_WEIGHT
, 0) * WSH_WV_PVT.CONVERT_UOM(PL.UNIT_CODE
, SI.PRIMARY_UOM_CODE
, PLD.REQUESTED_QUANTITY
, PL.INVENTORY_ITEM_ID) ) WEIGHT
, SUM( NVL(SI.UNIT_VOLUME
, 0) * WSH_WV_PVT.CONVERT_UOM(PL.UNIT_CODE
, SI.PRIMARY_UOM_CODE
, PLD.REQUESTED_QUANTITY
, PL.INVENTORY_ITEM_ID) ) VOLUME
, SUM( DECODE(PL.INCLUDED_ITEM_FLAG
, 'Y'
, 0
, L.SELLING_PRICE * PLD.REQUESTED_QUANTITY)) VALUE
FROM SO_PICKING_BATCHES_ALL PB
, SO_PICKING_HEADERS_ALL PH
, SO_PICKING_LINES_ALL PL
, SO_PICKING_LINE_DETAILS PLD
, SO_LINES_ALL L
, WSH_DEPARTURES DEP
, MTL_SYSTEM_ITEMS SI
WHERE PB.BATCH_ID = PH.BATCH_ID
AND PH.PICKING_HEADER_ID = PL.PICKING_HEADER_ID
AND PL.PICKING_LINE_ID = PLD.PICKING_LINE_ID
AND PL.ORDER_LINE_ID = L.LINE_ID
AND PLD.DEPARTURE_ID = DEP.DEPARTURE_ID(+)
AND PLD.PICK_SLIP_NUMBER IS NOT NULL
AND PL.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID
AND PH.WAREHOUSE_ID = SI.ORGANIZATION_ID GROUP BY PLD.PICK_SLIP_NUMBER