PJM Project Schedule Exception Report - WIP

Description
Categories: BI Publisher, Manufacturing
Application: Project Manufacturing
Source: Project Schedule Exception Report - WIP (XML)
Short Name: PJMRPSEW_XML
DB package: PJM_PJMRPSEW_XMLP_PKG
select decode(:P_order_by,
                    1, rpad(mp.organization_code,3,' ') ||
                       we.wip_entity_name,
                    2, rpad(mp.organization_code,3,' ') ||
                       rpad(to_char(100000000 -
                         abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                                ( wdj.scheduled_start_date
                                , 'BETWEEN'
                                , 0
                                , pp.start_date
                                , pp.completion_date
                                , pt.start_date
                                , pt.completion_date
                                ) )
                                   ),100,'0'),
                    3, rpad(mp.organization_code,3,' ') ||
                       rpad(pp.segment1, 30, ' ') ||
                       pt.task_number,
                       NULL)                order_by
            ,1                              report_type
            ,mp.organization_code           org
            ,we.wip_entity_name             job_name
            ,mif.item_number                assembly
            ,ml1.meaning                    status 
            ,ml2.meaning                    job_type 
            ,wdj.start_quantity             start_qty
            ,wdj.quantity_completed         compl_qty   
            ,fnd_date.date_to_displaydate(wdj.scheduled_start_date, 2)       start_date
            ,fnd_date.date_to_displaydate(wdj.scheduled_completion_date, 2)  completion_date
            ,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                     ( wdj.scheduled_start_date
                     , 'BETWEEN'
                     , 0
                     , pp.start_date
                     , pp.completion_date
                     , pt.start_date
                     , pt.completion_date)) exception_days
            ,pp.segment1                    project_number
            ,pt.task_number                 task_number
            ,fnd_date.date_to_displaydate(nvl(pt.start_date,pp.start_date), 2) proj_task_start_date
            ,fnd_date.date_to_displaydate(nvl(pt.completion_date,pp.completion_date), 2) proj_task_end_date
from wip_discrete_jobs              wdj
            ,wip_entities                   we
            ,mfg_lookups                    ml1
            ,mfg_lookups                    ml2
            ,pa_projects                    pp
            ,pa_tasks                       pt 
            ,mtl_parameters                 mp
            ,mtl_item_flexfields            mif
       where wdj.project_id is not null 
         and wdj.status_type not in (4,5,7,12) 
         and we.wip_entity_id               =wdj.wip_entity_id
         and ml1.lookup_type                ='WIP_JOB_STATUS'
         and ml1.lookup_code                =wdj.job_type 
         and ml2.lookup_type                ='WIP_DISCRETE_JOB'
         and ml2.lookup_code                =wdj.job_type 
         and pp.project_id                  =wdj.project_id
         and &P_project_where
         and pt.task_id (+)                 =wdj.task_id
         and mp.organization_id             =wdj.organization_id
         and mif.organization_id            =wdj.organization_id
         and mif.inventory_item_id          =wdj.primary_item_id
         and &P_item_where
         and PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                     ( wdj.scheduled_start_date
                     , 'BETWEEN'
                     , :P_tolerance_days
                     , pp.start_date
                     , pp.completion_date
                     , pt.start_date
                     , pt.completion_date) > 0
         and wdj.scheduled_start_date &P_date_where
union all
select decode(:P_order_by,
                    1, rpad(mp.organization_code,3,' ') ||
                       we.wip_entity_name,
                    2, rpad(mp.organization_code,3,' ') ||
                       rpad(to_char(100000000 -
                         abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                                (wdj.scheduled_completion_date
                                ,'BETWEEN'
                                ,0
                                ,pp.start_date
                                ,pp.completion_date
                                ,pt.start_date
                                ,pt.completion_date
                                ) )
                                   ),100,'0'),
                    3, rpad(mp.organization_code,3,' ') ||
                       rpad(pp.segment1, 30, ' ') ||
                       pt.task_number,
                       NULL)                order_by
            ,2                              report_type
            ,mp.organization_code           org
            ,we.wip_entity_name             job_name
            ,mif.item_number                assembly
            ,ml1.meaning                    status 
            ,ml2.meaning                    job_type 
            ,wdj.start_quantity             start_qty
            ,wdj.quantity_completed         compl_qty   
            ,fnd_date.date_to_displaydate(wdj.scheduled_start_date, 2)       start_date
            ,fnd_date.date_to_displaydate(wdj.scheduled_completion_date, 2)  completion_date
            ,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                     (wdj.scheduled_completion_date
                     ,'BETWEEN'
                     ,0
                     ,pp.start_date
                     ,pp.completion_date
                     ,pt.start_date
                     ,pt.completion_date
                     ))                     exception_days
            ,pp.segment1                    project_number
            ,pt.task_number                 task_number
            ,fnd_date.date_to_displaydate(nvl(pt.start_date,pp.start_date), 2) proj_task_start_date
            ,fnd_date.date_to_displaydate(nvl(pt.completion_date,pp.completion_date), 2) proj_task_end_date
from wip_discrete_jobs              wdj
            ,wip_entities                   we
            ,mfg_lookups                    ml1
            ,mfg_lookups                    ml2
            ,pa_projects                    pp
            ,pa_tasks                       pt 
            ,mtl_parameters                 mp
            ,mtl_item_flexfields            mif
       where wdj.project_id is not null 
         and wdj.status_type not in (4,5,7,12) 
         and we.wip_entity_id               =wdj.wip_entity_id
         and ml1.lookup_type                ='WIP_JOB_STATUS'
         and ml1.lookup_code                =wdj.job_type 
         and ml2.lookup_type                ='WIP_DISCRETE_JOB'
         and ml2.lookup_code                =wdj.job_type 
         and pp.project_id                  =wdj.project_id
         and &P_project_where
         and pt.task_id (+)                 =wdj.task_id
         and mp.organization_id             =wdj.organization_id
         and mif.organization_id            =wdj.organization_id
         and mif.inventory_item_id          =wdj.primary_item_id
         and &P_item_where
         and PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                 (wdj.scheduled_completion_date
                 ,'BETWEEN'
                 ,:P_tolerance_days
                 ,pp.start_date
                 ,pp.completion_date
                 ,pt.start_date
                 ,pt.completion_date
                 ) > 0
         and wdj.scheduled_completion_date &P_date_where
union all
select decode(:P_order_by,
                    1, rpad(mp.organization_code,3,' ') ||
                       we.wip_entity_name,
                    2, rpad(mp.organization_code,3,' ') ||
                       rpad(to_char(100000000 -
                         abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                                ( wdj.scheduled_start_date
                                , 'BETWEEN'
                                , 0
                                , pp.start_date
                                , pp.completion_date
                                , pt.start_date
                                , pt.completion_date
                                ) ) ),100,'0'),
                    3, rpad(mp.organization_code,3,' ') ||
                       rpad(pp.segment1, 30, ' ') ||
                       pt.task_number,
                       NULL)                order_by
            ,3                              report_type
            ,mp.organization_code           org
            ,we.wip_entity_name             job_name
            ,mif.item_number                assembly
            ,ml1.meaning                    status 
            ,ml2.meaning                    job_type 
            ,wdj.start_quantity             start_qty
            ,wdj.quantity_completed         compl_qty   
            ,fnd_date.date_to_displaydate(wdj.scheduled_start_date, 2)       start_date
            ,fnd_date.date_to_displaydate(wdj.scheduled_completion_date, 2)  completion_date
            ,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                     (wdj.scheduled_start_date
                     ,'BETWEEN'
                     ,0
                     ,pp.start_date
                     ,pp.completion_date
                     ,pt.start_date
                     ,pt.completion_date
                     ))                     exception_days
            ,pp.segment1                    project_number
            ,pt.task_number                 task_number
            ,fnd_date.date_to_displaydate(nvl(pt.start_date,pp.start_date), 2) proj_task_start_date
            ,fnd_date.date_to_displaydate(nvl(pt.completion_date,pp.completion_date), 2) proj_task_end_date
from wip_discrete_jobs              wdj
            ,wip_entities                   we
            ,mfg_lookups                    ml1
            ,mfg_lookups                    ml2
            ,pa_projects                    pp
            ,pa_tasks                       pt 
            ,mtl_parameters                 mp
            ,mtl_item_flexfields            mif
       where wdj.project_id is not null 
         and wdj.status_type not in (4,5,7,12) 
         and we.wip_entity_id               =wdj.wip_entity_id
         and ml1.lookup_type                ='WIP_JOB_STATUS'
         and ml1.lookup_code                =wdj.job_type 
         and ml2.lookup_type                ='WIP_DISCRETE_JOB'
         and ml2.lookup_code                =wdj.job_type 
         and pp.project_id                  =wdj.project_id
         and &P_project_where
         and pt.task_id (+)                 =wdj.task_id
         and mp.organization_id             =wdj.organization_id
         and mif.organization_id            =wdj.organization_id
         and mif.inventory_item_id          =wdj.primary_item_id
         and &P_item_where
         and PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                 (wdj.scheduled_start_date
                 ,'BETWEEN'
                 ,:P_tolerance_days
                 ,pp.start_date,pp.completion_date
                 ,pt.start_date
                 ,pt.completion_date
                 ) < 0
         and wdj.scheduled_start_date &P_date_where
union all
select decode(:P_order_by,
                    1, rpad(mp.organization_code,3,' ') ||
                       we.wip_entity_name,
                    2, rpad(mp.organization_code,3,' ') ||
                       rpad(to_char(100000000 -
                         abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                                ( wdj.scheduled_completion_date
                                , 'BETWEEN'
                                , 0
                                , pp.start_date
                                , pp.completion_date
                                , pt.start_date
                                , pt.completion_date
                                ) ) ),100,'0'),
                    3, rpad(mp.organization_code,3,' ') ||
                       rpad(pp.segment1, 30, ' ') ||
                       pt.task_number,
                       NULL)                order_by
            ,4                              report_type
            ,mp.organization_code           org
            ,we.wip_entity_name             job_name
            ,mif.item_number                assembly
            ,ml1.meaning                    status 
            ,ml2.meaning                    job_type 
            ,wdj.start_quantity             start_qty
            ,wdj.quantity_completed         compl_qty   
            ,fnd_date.date_to_displaydate(wdj.scheduled_start_date, 2)       start_date
            ,fnd_date.date_to_displaydate(wdj.scheduled_completion_date, 2)  completion_date
            ,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                     (wdj.scheduled_completion_date
                     ,'BETWEEN'
                     ,0
                     ,pp.start_date,pp.completion_date
                     ,pt.start_date,pt.completion_date
                     ))                     exception_days
            ,pp.segment1                    project_number
            ,pt.task_number                 task_number
            ,fnd_date.date_to_displaydate(nvl(pt.start_date,pp.start_date), 2) proj_task_start_date
            ,fnd_date.date_to_displaydate(nvl(pt.completion_date,pp.completion_date), 2) proj_task_end_date
from wip_discrete_jobs              wdj
            ,wip_entities                   we
            ,mfg_lookups                    ml1
            ,mfg_lookups                    ml2
            ,pa_projects_all                    pp
            ,pa_tasks                       pt 
            ,mtl_parameters                 mp
            ,mtl_item_flexfields            mif
       where wdj.project_id is not null 
         and wdj.status_type not in (4,5,7,12) 
         and we.wip_entity_id               =wdj.wip_entity_id
         and ml1.lookup_type                ='WIP_JOB_STATUS'
         and ml1.lookup_code                =wdj.job_type 
         and ml2.lookup_type                ='WIP_DISCRETE_JOB'
         and ml2.lookup_code                =wdj.job_type 
         and pp.project_id                  =wdj.project_id
         and &P_project_where
         and pt.task_id (+)                 =wdj.task_id
         and mp.organization_id             =wdj.organization_id
         and mif.organization_id            =wdj.organization_id
         and mif.inventory_item_id          =wdj.primary_item_id
         and &P_item_where
         and PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
                 (wdj.scheduled_completion_date
                 ,'BETWEEN'
                 ,:P_tolerance_days
                 ,pp.start_date
                 ,pp.completion_date
                 ,pt.start_date
                 ,pt.completion_date
                 ) < 0
         and wdj.scheduled_completion_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
Assembly To
 
Assembly From
 
Tolerance Days
 
Number
Order By
 
LOV Oracle