select decode(:P_order_by,
4, to_char(ooh.order_number),
5, rpad(to_char(100000000 -
abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( ool.promise_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
, ooh.order_number so_number
, mif.item_number item
, hou.name warehouse
, nvl(ool.ordered_quantity,0) ordered_qty
, fnd_date.date_to_displaydate(ool.request_date, 2) requested_date
, abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( ool.request_date
, 'BETWEEN'
, 0
, pp.start_date
, pp.completion_date
, pt.start_date
, pt.completion_date
)) exception_days1
, fnd_date.date_to_displaydate(ool.promise_date, 2) promise_date
, abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( ool.promise_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 oe_order_lines ool
, hr_organization_units hou
, oe_order_headers ooh
, mtl_item_flexfields mif
, pa_projects pp
, pa_tasks pt
where ool.project_id is not null
and nvl(ool.cancelled_flag,'N') <> 'Y'
and nvl(ool.open_flag,'Y') <> 'N'
and ooh.header_id = ool.header_id
and hou.organization_id = ool.ship_from_org_id
and mif.organization_id = ool.ship_from_org_id
and mif.inventory_item_id = ool.inventory_item_id
and &P_item_where
and pp.project_id = ool.project_id
and &P_project_where
and pt.task_id (+) = ool.task_id
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( ool.request_date
, 'BETWEEN'
, :P_tolerance_days
, pp.start_date
, pp.completion_date
, pt.start_date
, pt.completion_date
) > 0
and ool.request_date &P_date_where
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( ool.promise_date
, 'BETWEEN'
, :P_tolerance_days
, pp.start_date
, pp.completion_date
, pt.start_date
, pt.completion_date
) > 0
and ool.promise_date &P_date_where
))
union all
select decode(:P_order_by,
4, to_char(ooh.order_number),
5, rpad(to_char(100000000 -
abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( ool.promise_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
, ooh.order_number so_number
, mif.item_number item
, hou.name warehouse
, nvl(ool.ordered_quantity,0) ordered_qty
, fnd_date.date_to_displaydate(ool.request_date, 2) requested_date
, abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( ool.request_date
, 'BETWEEN'
, 0
, pp.start_date
, pp.completion_date
, pt.start_date
, pt.completion_date
)) exception_days1
, fnd_date.date_to_displaydate(ool.promise_date, 2) promise_date
, abs(PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( ool.promise_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 oe_order_lines_all ool
, hr_organization_units hou
, oe_order_headers_all ooh
, mtl_item_flexfields mif
, pa_projects_all pp
, pa_tasks pt
where ool.project_id is not null
and nvl(ool.cancelled_flag,'N') <> 'Y'
and nvl(ool.open_flag,'Y') <> 'N'
and ooh.header_id = ool.header_id
and hou.organization_id = ool.ship_from_org_id
and mif.organization_id = ool.ship_from_org_id
and mif.inventory_item_id = ool.inventory_item_id
and &P_item_where
and pp.project_id = ool.project_id
and &P_project_where
and pt.task_id (+) = ool.task_id
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( ool.request_date
, 'BETWEEN'
, :P_tolerance_days
, pp.start_date
, pp.completion_date
, pt.start_date
, pt.completion_date
) < 0
and ool.request_date &P_date_where
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS
( ool.promise_date
, 'BETWEEN'
, :P_tolerance_days
, pp.start_date
, pp.completion_date
, pt.start_date
, pt.completion_date
) < 0
and ool.promise_date &P_date_where
))
order by 2,1 |
