I have to include a blob type field in a group by statement. Is there a way
around it? Please find select statement below. The blob type field is
FND_LOBS.FILE_DATA.
I appreciate all the help I can have.
Thanks,
Mark
SELECT
-- :c_q2_delivery_id c_q3_delivery_id
--, :c_q2_print_all c_q3_print_all
&p_item_flex c_item_flex
,det.inventory_item_id c_inv_item_id
, nvl(det.item_description,msi.description) c_item_description
, det.customer_item_id c_customer_item_id
, det.source_header_number c_so_number
,det.source_line_number c_so_line_number
, det.cust_po_number c_po_number
, sum ( round(nvl(det.requested_quantity,0),:p_quantity_precision))
c_req_qty
, det.requested_quantity_uom c_req_qty_uom
, det.requested_quantity_uom2 c_req_qty_uom2
, det.src_requested_quantity_uom c_src_req_uom
, det.src_requested_quantity_uom2 c_src_req_uom2
-- src_requested_quantity remains the same for delivery details from the
same source_line_id
-- so we take the avg. For other columns take the sum.
,avg( round(nvl(det.src_requested_quantity,0),:p_quantity_precision) )c_src_
req_qty
,avg( round(nvl(det.src_requested_quantity2,0),:p_quantity_precision) )
c_src_req_qty2
, sum(round(nvl(det.shipped_quantity,0),:p_quantity_precision) ) c_ship_qty
,sum( round(nvl(det.shipped_quantity2,0),:p_quantity_precision)) c_ship_qty2
,sum( round(nvl(det.delivered_quantity,0),:p_quantity_precision) ) c_del_qty
,sum( round(nvl(det.src_requested_quantity,0)-nvl(det.shipped_quantity,0)-nv
l(det.requested_quantity,0),:p_quantity_precision) )c_back_qty -- this
column no longer being used as it is wrong.
,
sum(round(nvl(det.src_requested_quantity2,0)-nvl(det.shipped_quantity2,0)-nv
l(det.requested_quantity2,0),:p_quantity_precision) )c_back_qty2 -- this
column should also be ot used ,
, det.customer_id c_customer_id
, det.source_code c_src_code
, det.source_header_id c_src_hdr_id
, det.source_line_id c_src_line_id
, det.lot_number c_lot_number
, det.sublot_number c_sublot_number
, det.preferred_grade c_preferred_grade
, det.shipping_instructions c_ship_instructions
,FND_LOBS.FILE_DATA
,FND_DOCUMENTS_TL.MEDIA_ID
,OE_ORDER_LINES_ALL.ORIG_SYS_DOCUMENT_REF
, det.packing_instructions c_pack_instructions
FROM
wsh_delivery_details det
, wsh_delivery_assignments das
,OE_ORDER_LINES_ALL
,FND_ATTACHED_DOCUMENTS
,FND_DOCUMENTS_TL
,FND_LOBS
, mtl_system_items msi
WHERE det.delivery_detail_id=das.delivery_detail_id
AND det.container_flag='N'
--AD nvl(det.released_status, 'N') in ('Y', 'C', 'X')
AND das.delivery_id=:c_q2_delivery_id
AND msi.organization_id(+)=:p_organization_id
AND msi.inventory_item_id(+)=det.inventory_item_id
AND FND_LOBS.FILE_ID=FND_DOCUMENTS_TL.MEDIA_ID
AND FND_DOCUMENTS_TL.DOCUMENT_ID=FND_ATTACHED_DOCUMENTS.DOCUMENT_ID
AND FND_ATTACHED_DOCUMENTS.PK1_VALUE=OE_ORDER_LINES_ALL.LINE_ID
AND OE_ORDER_LINES_ALL.LINE_ID=det.SOURCE_LINE_ID
group by
&p_item_flex
,det.inventory_item_id
, nvl(det.item_description,msi.description)
, det.customer_item_id
, det.source_header_number
,det.source_line_number
, det.cust_po_number
, det.requested_quantity_uom
, det.requested_quantity_uom2
, det.src_requested_quantity_uom
, det.src_requested_quantity_uom2
, det.customer_id
, det.source_code
, det.source_header_id
, det.source_line_id
,FND_DOCUMENTS_TL.MEDIA_ID
,OE_ORDER_LINES_ALL.ORIG_SYS_DOCUMENT_REF
, det.lot_number
, det.sublot_number
, det.preferred_grade
, det.shipping_instructions
, det.packing_instructions
ORDER BY &p_order_by
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Liggayu
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
