PJM Project Schedule Exception Report - WIP
Description
Categories: BI Publisher
Application: Project Manufacturing
Source: Project Schedule Exception Report - WIP (XML)
Short Name: PJMRPSEW_XML
DB package: PJM_PJMRPSEW_XMLP_PKG
Source: Project Schedule Exception Report - WIP (XML)
Short Name: PJMRPSEW_XML
DB package: PJM_PJMRPSEW_XMLP_PKG
Run
PJM Project Schedule Exception Report - WIP and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
|---|---|---|---|
| Order By |
|
LOV Oracle | |
| Tolerance Days |
|
Number | |
| Assembly From |
|
Char | |
| Assembly To |
|
Char | |
| Project Number From |
|
LOV Oracle | |
| Project Number To |
|
LOV Oracle | |
| Date From |
|
Date | |
| Date To |
|
Date |
