select decode( :P_order_by ,
7 , ph.segment1 || ',' || to_char( pr.release_num ) ,
6 , rpad( pp.segment1 , 30 , ' ' ) || pt.task_number ,
NULL ) order_by
, ph.segment1 || ',' || to_char( pr.release_num ) blanket_release
, PO_HEADERS_SV3.GET_PO_STATUS( ph.po_header_id ) status
, mif.item_number item
, hou.name ship_to_location
, pd.quantity_ordered ordered
, pd.quantity_delivered delivered
, pd.quantity_cancelled cancelled
, pl.unit_meas_lookup_code uom
, fnd_date.date_to_displaydate( pll.promised_date , 2 ) promised_date
, fnd_date.date_to_displaydate( pll.need_by_date , 2 ) need_by_date
, pp.segment1 project_number
, pt.task_number task_number
, fnd_date.date_to_displaydate( nvl( pt.start_date , pp.start_date ) , 2 ) start_date
, fnd_date.date_to_displaydate( nvl( pt.completion_date , pp.completion_date ) , 2 ) end_date
from po_distributions_all pd
, po_releases_all pr
, po_line_locations_all pll
, po_lines_all pl
, po_headers_all ph
, mtl_item_flexfields mif
, pa_projects_all pp
, pa_tasks pt
, hr_all_organization_units_tl hou
where pd.project_id is not null
and pd.po_release_id is not null
and pr.po_release_id = pd.po_release_id
and pll.line_location_id = pd.line_location_id
and pl.po_line_id = pll.po_line_id
and ph.po_header_id = pl.po_header_id
and nvl( ph.closed_code , 'OPEN' ) not like '%CLOSED%'
and nvl( ph.authorization_status , 'N' ) not in ( 'CANCELLED' , 'REJECTED' )
and nvl( pr.closed_code , 'OPEN' ) not like '%CLOSED%'
and nvl( pr.authorization_status , 'N' ) not in ( 'CANCELLED' , 'REJECTED' )
and nvl( pr.cancel_flag , 'N' ) < > 'Y'
and mif.organization_id = pd.destination_organization_id
and mif.inventory_item_id = pl.item_id
and &P_item_where
and pp.project_id = pd.project_id
and &P_project_where
and pt.task_id (+) = pd.task_id
and hou.organization_id = pd.destination_organization_id
and hou.language = userenv('LANG')
and nvl( pd.quantity_delivered , 0 )
+ nvl( pd.quantity_cancelled , 0 ) < nvl( pd.quantity_ordered , 0 )
and sysdate > nvl( pt.completion_date , nvl( pp.completion_date , sysdate ) )
and nvl( pt.completion_date , pp.completion_date ) &P_date_where
order by 1 |