PJM Project Schedule Exception Report - Sales Orders

Description
Categories: BI Publisher, Manufacturing
Application: Project Manufacturing
Source: Project Schedule Exception Report - Sales Orders (XML)
Short Name: PJMRPSES_XML
DB package: PJM_PJMRPSES_XMLP_PKG
select decode(:P_order_by,
               4, to_char(ooh.order_number),
               5, rpad(to_char(100000000 -
                   abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                          ( ool.promise_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
,      ooh.order_number               so_number
,      mif.item_number                item
,      hou.name                       warehouse
,      nvl(ool.ordered_quantity,0)    ordered_qty   
,      fnd_date.date_to_displaydate(ool.request_date, 2)      requested_date
,      abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
               ( ool.request_date
               , 'BETWEEN'
               , 0
               , pp.start_date
               , pp.completion_date
               , pt.start_date
               , pt.completion_date
               ))                     exception_days1
,      fnd_date.date_to_displaydate(ool.promise_date, 2)                promise_date
,      abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
               ( ool.promise_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   oe_order_lines                 ool
,      hr_organization_units          hou
,      oe_order_headers               ooh
,      mtl_item_flexfields            mif
,      pa_projects                    pp
,      pa_tasks                       pt 
where  ool.project_id is not null 
and    nvl(ool.cancelled_flag,'N')   <> 'Y'
and    nvl(ool.open_flag,'Y')        <> 'N'
and    ooh.header_id                  = ool.header_id
and    hou.organization_id            = ool.ship_from_org_id
and    mif.organization_id            = ool.ship_from_org_id
and    mif.inventory_item_id          = ool.inventory_item_id
and    &P_item_where
and    pp.project_id                  = ool.project_id
and    &P_project_where
and    pt.task_id (+)                 = ool.task_id
and    ((
       PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
           ( ool.request_date
           , 'BETWEEN'
           , :P_tolerance_days
           , pp.start_date
           , pp.completion_date
           , pt.start_date
           , pt.completion_date
           ) > 0
       and ool.request_date &P_date_where
       ) or (
       PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
              ( ool.promise_date
              , 'BETWEEN'
              , :P_tolerance_days
              , pp.start_date
              , pp.completion_date
              , pt.start_date
              , pt.completion_date
              ) > 0
       and ool.promise_date &P_date_where
       ))     
union all
select decode(:P_order_by,
              4, to_char(ooh.order_number),
              5, rpad(to_char(100000000 -
                   abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                          ( ool.promise_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
,      ooh.order_number               so_number
,      mif.item_number                item
,      hou.name                       warehouse
,      nvl(ool.ordered_quantity,0)    ordered_qty   
,      fnd_date.date_to_displaydate(ool.request_date, 2)      requested_date
,      abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
               ( ool.request_date
               , 'BETWEEN'
               , 0
               , pp.start_date
               , pp.completion_date
               , pt.start_date
               , pt.completion_date
               ))                     exception_days1
,      fnd_date.date_to_displaydate(ool.promise_date, 2)                promise_date
,      abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
               ( ool.promise_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   oe_order_lines_all                 ool
,      hr_organization_units          hou
,      oe_order_headers_all               ooh
,      mtl_item_flexfields            mif
,      pa_projects_all                    pp
,      pa_tasks                       pt 
where  ool.project_id is not null 
and    nvl(ool.cancelled_flag,'N')   <> 'Y'
and    nvl(ool.open_flag,'Y')        <> 'N'
and    ooh.header_id                  = ool.header_id
and    hou.organization_id            = ool.ship_from_org_id
and    mif.organization_id            = ool.ship_from_org_id
and    mif.inventory_item_id          = ool.inventory_item_id
and    &P_item_where
and    pp.project_id                  = ool.project_id
and    &P_project_where
and    pt.task_id (+)                 = ool.task_id
and    ((
       PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
           ( ool.request_date
           , 'BETWEEN'
           , :P_tolerance_days
           , pp.start_date
           , pp.completion_date
           , pt.start_date
           , pt.completion_date
           ) < 0
       and ool.request_date &P_date_where
       ) or (
       PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
              ( ool.promise_date
              , 'BETWEEN'
              , :P_tolerance_days
              , pp.start_date
              , pp.completion_date
              , pt.start_date
              , pt.completion_date
              ) < 0
       and ool.promise_date &P_date_where
       ))     
order by 2,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
 
Tolerance Days
 
Number
Order By
 
LOV Oracle