PJM Overdue Project Procurement Activities

Description
Categories: BI Publisher, Manufacturing
Application: Project Manufacturing
Source: Overdue Project Procurement Activities Report (XML)
Short Name: PJMRPPSE_XML
DB package: PJM_PJMRPPSE_XMLP_PKG
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
Parameter Name SQL text Validation
Date To
 
Date
Date From
 
Date
Project Number To
 
LOV Oracle
Project Number From
 
LOV Oracle
Item To
 
Item From
 
Include Quotations
 
LOV Oracle
Include Request For Quotations
 
LOV Oracle
Include Purchase Requisitions
 
LOV Oracle
Include Blanket Releases
 
LOV Oracle
Include Purchase Orders
 
LOV Oracle
Order By
 
LOV Oracle