PJM Overdue Project Procurement Activities

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