PJM Project Schedule Exception Report - Sales Orders

➜ Back to Library
Description
Categories: BI Publisher
Application: Project Manufacturing
Source: Project Schedule Exception Report - Sales Orders (XML)
Short Name: PJMRPSES_XML
DB package: PJM_PJMRPSES_XMLP_PKG
Run PJM Project Schedule Exception Report - Sales Orders and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Order By 
 
 LOV Oracle
Tolerance Days 
 
 Number
Item From 
 
 Char
Item To 
 
 Char
Project Number From 
 
 LOV Oracle
Project Number To 
 
 LOV Oracle
Date From 
 
 Date
Date To 
 
 Date
Download
Related Blitz Reports
ECC Procurement, Requisitions PPF_WP3_OM_DETAILS Yanmar - Discount on order lines check ONT DIFOT ECC Project Manufacturing Genealogy & Trace, Sales Orders ECC Project Manufacturing, Exceptions and Overdues, SQL4 ECC Project Manufacturing, Exceptions and Overdues, SQL3 GL Account Analysis ECC Project Manufacturing, Sales Orders
Blitz Report™

Blitz Report™ provides multiple benefits: