select decode(:P_order_by,
7, ph.segment1,
5, rpad(to_char(100000000 -
abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( pll.need_by_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
,ph.segment1||','||to_char(pr.release_num) blanket_release
,PO_HEADERS_SV3.GET_PO_STATUS(ph.po_header_id) status
,mif.item_number item
,hou.name ship_to_location
,pd.quantity_ordered ordered
,pd.quantity_delivered delivered
,fnd_date.date_to_displaydate(pll.promised_date, 2) promised_date
,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
(pll.promised_date
,'BETWEEN'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
)) exception_days1
,fnd_date.date_to_displaydate(pll.need_by_date, 2) need_by_date
,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
(pll.need_by_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 po_distributions pd
,po_releases pr
,po_line_locations pll
,po_lines pl
,po_headers ph
,mtl_item_flexfields mif
,pa_projects pp
,pa_tasks pt
,hr_organization_units hou
,financials_system_parameters fsp
where pd.project_id is not null
and pd.po_release_id is not null
and pr.po_release_id =pd.po_release_id
and pll.line_location_id =pd.line_location_id
and pl.po_line_id =pll.po_line_id
and ph.po_header_id =pl.po_header_id
and nvl(ph.closed_code,'OPEN') not like '%CLOSED%'
and nvl(ph.authorization_status,'N') not in ('CANCELLED','REJECTED')
and nvl( pr.closed_code , 'OPEN' ) not like '%CLOSED%'
and nvl( pr.authorization_status , 'N' ) not in ( 'CANCELLED' , 'REJECTED' )
and nvl( pr.cancel_flag , 'N' ) <> 'Y'
and mif.organization_id =fsp.inventory_organization_id
and mif.inventory_item_id =pl.item_id
and &P_item_where
and pp.project_id =pd.project_id
and &P_project_where
and pt.task_id (+) =pd.task_id
and hou.organization_id =pd.destination_organization_id
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.promised_date
,'BETWEEN'
,:P_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) > 0
and pll.promised_date &P_date_where
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.need_by_date
,'BETWEEN'
,:P_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) > 0
and pll.need_by_date &P_date_where
))
union all
select decode(:P_order_by,
7, ph.segment1,
5, rpad(to_char(100000000 -
abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( pll.need_by_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
,ph.segment1||','||to_char(pr.release_num) blanket_release
,PO_HEADERS_SV3.GET_PO_STATUS(ph.po_header_id) status
,mif.item_number item
,hou.name ship_to_location
,pd.quantity_ordered ordered
,pd.quantity_delivered delivered
,fnd_date.date_to_displaydate(pll.promised_date, 2) promised_date
,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
(pll.promised_date
,'BETWEEN'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
)) exception_days1
,fnd_date.date_to_displaydate(pll.need_by_date, 2) need_by_date
,abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
(pll.need_by_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 po_distributions_all pd
,po_releases_all pr
,po_line_locations_all pll
,po_lines_all pl
,po_headers_all ph
,mtl_item_flexfields mif
,pa_projects_all pp
,pa_tasks pt
,hr_organization_units hou
,financials_system_params_all fsp
where pd.project_id is not null
and pd.po_release_id is not null
and pr.po_release_id =pd.po_release_id
and pll.line_location_id =pd.line_location_id
and pl.po_line_id =pll.po_line_id
and ph.po_header_id =pl.po_header_id
and nvl(ph.closed_code,'OPEN') not like '%CLOSED%'
and nvl(ph.authorization_status,'N') not in ('CANCELLED','REJECTED')
and nvl( pr.closed_code , 'OPEN' ) not like '%CLOSED%'
and nvl( pr.authorization_status , 'N' ) not in ( 'CANCELLED' , 'REJECTED' )
and nvl( pr.cancel_flag , 'N' ) <> 'Y'
and mif.organization_id =fsp.inventory_organization_id
and fsp.org_id = ph.org_id
and mif.inventory_item_id =pl.item_id
and &P_item_where
and pp.project_id =pd.project_id
and &P_project_where
and pt.task_id (+) =pd.task_id
and hou.organization_id =pd.destination_organization_id
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.promised_date
,'BETWEEN'
,:P_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) < 0
and pll.promised_date &P_date_where
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.need_by_date
,'BETWEEN'
,:P_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) < 0
and pll.need_by_date &P_date_where
))
order by 1 |
