PJM Overdue Project WIP Jobs

Description
Categories: BI Publisher, Manufacturing
Application: Project Manufacturing
Source: Overdue Project WIP Jobs Report (XML)
Short Name: PJMRPWSE_XML
DB package: PJM_PJMRPWSE_XMLP_PKG
select decode(:P_order_by,
                    1, rpad(mp.organization_code,3,' ') ||
                       we.wip_entity_name,
                    3, rpad(mp.organization_code,3,' ') ||
                       rpad(pp.segment1, 30, ' ') ||
                       pt.task_number,
                       NULL)                order_by
            ,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)       job_start_date
            ,fnd_date.date_to_displaydate(wdj.scheduled_completion_date, 2)  job_completion_date
            ,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 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 nvl(wdj.start_quantity,0) > nvl(to_number(wdj.quantity_completed),0)
         and we.wip_entity_id               =wdj.wip_entity_id
         and ml1.lookup_type                ='WIP_JOB_STATUS'
         and ml1.lookup_code                =wdj.status_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 sysdate > nvl(pt.completion_date,nvl(pp.completion_date,sysdate))
         and nvl(pt.completion_date,pp.completion_date) &P_date_where
order by 1
Parameter NameSQL textValidation
Date To
 
Date
Date From
 
Date
Project Number To
 
LOV Oracle
Project Number From
 
LOV Oracle
Assembly To
 
Assembly From
 
Order By
 
LOV Oracle