PJM Project Schedule Exception Report - Procurement Activities

Description
Categories: BI Publisher
Application: Project Manufacturing
Source: Project Schedule Exception Report - Procurement Activities (XML)
Short Name: PJMRPSEP_XML
DB package: PJM_PJMRPSEP_XMLP_PKG
select decode(:P_order_by,
              7, ph.segment1,
              5, rpad(to_char(100000000 -
                   abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                          ( pll.need_by_date
                          , 'BETWEEN'
                          , 0
                          , pp.start_date
                          , pp.completion_date
                          , pt.start_date
                          , pt.completion_date
                          ) )
                             ),100,'0'),
              6, rpad(pp.segment1, 30, ' ') ||
                 pt.task_number,
                 NULL)                order_by
      ,1                              report_type
      ,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
      ,fnd_date.date_to_displaydate(pll.promised_date, 2)              promised_date
      ,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                                                  (pll.promised_date
                                                  ,'BETWEEN'
                                                  ,0
                                                  ,pp.start_date
                                                  ,pp.completion_date
                                                  ,pt.start_date
                                                  ,pt.completion_date
                                                  )) exception_days1
      ,fnd_date.date_to_displaydate(pll.need_by_date, 2) need_by_date
      ,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                                                 (pll.need_by_date
                                                 ,'BETWEEN'
                                                 ,0
                                                 ,pp.start_date
                                                 ,pp.completion_date
                                                 ,pt.start_date
                                                 ,pt.completion_date
                                                 )) exception_days2
      ,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               pd
      ,po_releases                    pr
      ,po_line_locations              pll
      ,po_lines                       pl
      ,po_headers                     ph
      ,mtl_item_flexfields            mif
      ,pa_projects                    pp
      ,pa_tasks                       pt 
      ,hr_organization_units          hou
      ,financials_system_parameters fsp
 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            =fsp.inventory_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 ((
       PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.promised_date
                                                  ,'BETWEEN'
                                                  ,:P_tolerance_days
                                                  ,pp.start_date
                                                  ,pp.completion_date
                                                  ,pt.start_date
                                                  ,pt.completion_date
                                                  ) > 0
       and pll.promised_date &P_date_where
       ) or (
       PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.need_by_date
                                                  ,'BETWEEN'
                                                  ,:P_tolerance_days
                                                  ,pp.start_date
                                                  ,pp.completion_date
                                                  ,pt.start_date
                                                  ,pt.completion_date
                                                  ) > 0
       and pll.need_by_date &P_date_where
       ))
union all
select decode(:P_order_by,
              7, ph.segment1,
              5, rpad(to_char(100000000 -
                   abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                          ( pll.need_by_date
                          , 'BETWEEN'
                          , 0
                          , pp.start_date
                          , pp.completion_date
                          , pt.start_date
                          , pt.completion_date
                          ) )
                             ),100,'0'),
              6, rpad(pp.segment1, 30, ' ') ||
                 pt.task_number,
                 NULL)                order_by
      ,2                              report_type
      ,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
      ,fnd_date.date_to_displaydate(pll.promised_date, 2)              promised_date
      ,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                                                  (pll.promised_date
                                                  ,'BETWEEN'
                                                  ,0
                                                  ,pp.start_date
                                                  ,pp.completion_date
                                                  ,pt.start_date
                                                  ,pt.completion_date
                                                  )) exception_days1
      ,fnd_date.date_to_displaydate(pll.need_by_date, 2) need_by_date
      ,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                                                 (pll.need_by_date
                                                 ,'BETWEEN'
                                                 ,0
                                                 ,pp.start_date
                                                 ,pp.completion_date
                                                 ,pt.start_date
                                                 ,pt.completion_date
                                                 )) exception_days2
      ,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_organization_units          hou
      ,financials_system_params_all fsp
 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            =fsp.inventory_organization_id
   and fsp.org_id = ph.org_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 ((
       PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.promised_date
                                                  ,'BETWEEN'
                                                  ,:P_tolerance_days
                                                  ,pp.start_date
                                                  ,pp.completion_date
                                                  ,pt.start_date
                                                  ,pt.completion_date
                                                  ) < 0
       and pll.promised_date &P_date_where
       ) or (
       PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.need_by_date
                                                  ,'BETWEEN'
                                                  ,:P_tolerance_days
                                                  ,pp.start_date
                                                  ,pp.completion_date
                                                  ,pt.start_date
                                                  ,pt.completion_date
                                                  ) < 0
       and pll.need_by_date &P_date_where
       ))
 order by 1
Parameter Name SQL text Validation
Order By
 
LOV Oracle
Tolerance Days
 
Number
Include Purchase Orders
 
LOV Oracle
Include Blanket Releases
 
LOV Oracle
Include Purchase Requisitions
 
LOV Oracle
Include Request For Quotations
 
LOV Oracle
Include Quotations
 
LOV Oracle
Item From
 
Item To
 
Project Number From
 
LOV Oracle
Project Number To
 
LOV Oracle
Date From
 
Date
Date To
 
Date
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: