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