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 |
