[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'WIP' document_type
, hou.name organization_name
, mif.item_number item_number
, mif.description description
, we.wip_entity_name wip_job_name
, wdj.wip_entity_id job_id
, ml1.meaning job_type
, ml2.meaning status
, wdj.scheduled_start_date job_start_date
, wdj.scheduled_completion_date job_end_date
, wdj.start_quantity start_quantity
, wdj.quantity_completed quantity_completed
, pp.segment1 project_number
, pp.name project_name
, pp.start_date project_start_date
, pp.completion_date project_end_date
, pt.task_number task_number
, pt.task_name task_name
, pt.start_date task_start_date
, pt.completion_date task_end_date
, wdj.project_id project_id
, wdj.task_id task_id
, 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_days1
, 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_days2
from wip_discrete_jobs wdj
, wip_entities we
, fnd_lookup_values ml1
, fnd_lookup_values ml2
, pa_projects_all pp
, pa_tasks pt
, hr_all_organization_units_tl hou
, mtl_item_flexfields mif
where wdj.project_id > 0
and wdj.status_type not in (4,5,7,12)
and we.wip_entity_id = wdj.wip_entity_id
and ml1.view_application_id = 700
and ml1.language = userenv('LANG')
and ml1.lookup_type = 'WIP_DISCRETE_JOB'
and ml1.lookup_code = wdj.job_type
and ml2.view_application_id = 700
and ml2.language = userenv('LANG')
and ml2.lookup_type = 'WIP_JOB_STATUS'
and ml2.lookup_code = wdj.status_type
and pp.project_id = wdj.project_id
and ( c_project_from is null or pp.project_id >= c_project_from )
and ( c_project_to is null or pp.project_id <= c_project_to )
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(wdj.scheduled_start_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <> 0
and wdj.scheduled_start_date
between nvl( fnd_date.canonical_to_date(d_date_from), wdj.scheduled_start_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , wdj.scheduled_start_date + 1)
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(wdj.scheduled_completion_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and wdj.scheduled_completion_date
between nvl( fnd_date.canonical_to_date(d_date_from), wdj.scheduled_completion_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , wdj.scheduled_completion_date + 1)
))
and pt.task_id (+) = wdj.task_id
and hou.organization_id = wdj.organization_id
and hou.language = userenv('LANG')
and mif.organization_id = wdj.organization_id
and mif.inventory_item_id = wdj.primary_item_id
and ( c_item_from is null or mif.item_number >= c_item_from )
and ( c_item_to is null or mif.item_number <= c_item_to )
order by hou.name , we.wip_entity_name
;
select 'SO' document_type
, ooh.order_number so_number
, ool.line_id so_line_id
, ool.line_number line_number
, hou.name warehouse
, mif.item_number item_number
, mif.description description
, nvl(ool.ordered_quantity,0) quantity
, ool.request_date requested_date
, ool.promise_date promised_date
, pp.segment1 project_number
, pp.name project_name
, pp.start_date project_start_date
, pp.completion_date project_end_date
, pt.task_number task_number
, pt.task_name task_name
, pt.start_date task_start_date
, pt.completion_date task_end_date
, 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
, 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
, ool.project_id project_id
, ool.task_id task_id
from oe_order_lines_all ool
, hr_all_organization_units_tl hou
, oe_order_headers_all ooh
, mtl_item_flexfields mif
, pa_projects_all pp
, pa_tasks pt
where ool.project_id > 0
and nvl(ool.cancelled_flag,'N') <> 'Y'
and nvl(ool.open_flag,'Y') <> 'N'
and nvl(ool.shipped_quantity,0)+nvl(ool.cancelled_quantity,0) < ool.ordered_quantity
and ooh.header_id = ool.header_id
and hou.organization_id = ool.ship_from_org_id
and hou.language = userenv('LANG')
and mif.organization_id = ool.ship_from_org_id
and mif.inventory_item_id = ool.inventory_item_id
and ( c_item_from is null or mif.item_number >= c_item_from )
and ( c_item_to is null or mif.item_number <= c_item_to )
and pp.project_id = ool.project_id
and ( c_project_from is null or pp.project_id >= c_project_from )
and ( c_project_to is null or pp.project_id <= c_project_to )
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ool.request_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and ool.request_date
between nvl( fnd_date.canonical_to_date(d_date_from), ool.request_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , ool.request_date + 1)
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ool.promise_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and ool.promise_date
between nvl( fnd_date.canonical_to_date(d_date_from), ool.promise_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , ool.promise_date + 1)
))
and pt.task_id (+) = ool.task_id
order by 2
;
select 'FORECAST' document_type
, mfdes.forecast_set forecast_set
, mfd.forecast_designator forecast_name
, hou.name organization_name
, mif.item_number item_number
, mif.description description
, mfd.forecast_date forecast_start_date
, mfd.rate_end_date forecast_end_date
, mfd.current_forecast_quantity quantity
, pp.segment1 project_number
, pp.name project_name
, pp.start_date project_start_date
, pp.completion_date project_end_date
, pt.task_number task_number
, pt.task_name task_name
, pt.start_date task_start_date
, pt.completion_date task_end_date
, mfd.transaction_id transaction_id
, PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(mfd.forecast_date
,'BETWEEN'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) exception_days1
, PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(mfd.rate_end_date
,'BETWEEN'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) exception_days2
, mfd.project_id project_id
, mfd.task_id task_id
from mrp_forecast_dates mfd
, hr_all_organization_units_tl hou
, mtl_item_flexfields mif
, pa_projects_all pp
, pa_tasks pt
, mrp_forecast_designators mfdes
where mfd.project_id > 0
and nvl(mfd.current_forecast_quantity,0) <>0
and mif.organization_id = mfd.organization_id
and mif.inventory_item_id = mfd.inventory_item_id
and ( c_item_from is null or mif.item_number >= c_item_from )
and ( c_item_to is null or mif.item_number <= c_item_to )
and pp.project_id = mfd.project_id
and ( c_project_from is null or pp.project_id >= c_project_from )
and ( c_project_to is null or pp.project_id <= c_project_to )
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(mfd.forecast_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and mfd.forecast_date
between nvl( fnd_date.canonical_to_date(d_date_from), mfd.forecast_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , mfd.forecast_date + 1)
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(mfd.rate_end_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and mfd.rate_end_date
between nvl( fnd_date.canonical_to_date(d_date_from), mfd.rate_end_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , mfd.rate_end_date + 1)
))
and pt.task_id (+) = mfd.task_id
and hou.organization_id = mfd.organization_id
and hou.language = userenv('LANG')
and mfdes.forecast_designator = mfd.forecast_designator
and mfdes.organization_Id = mfd.organization_id
order by mfd.forecast_designator
;
select 'PR' document_type
, prh.segment1 pr_number
, hou.name ship_to_location
, nvl(prh.closed_code,prh.authorization_status) status
, mif.item_number item_number
, mif.description description
, prd.req_line_quantity quantity
, prl.need_by_date need_by_date
, prd.distribution_id distribution_id
, pp.segment1 project_number
, pp.name project_name
, pp.start_date project_start_date
, pp.completion_date project_end_date
, pt.task_number task_number
, pt.task_name task_name
, pt.start_date task_start_date
, pt.completion_date task_end_date
, PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(prl.need_by_date
,'BETWEEN'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) exception_days1
, prd.project_id project_id
, prd.task_id task_id
from po_requisition_lines_all prl
, po_req_distributions_all prd
, po_requisition_headers_all prh
, mtl_item_flexfields mif
, pa_projects_all pp
, pa_tasks pt
, hr_all_organization_units_tl hou
, financials_system_params_all fsp
where prd.project_id > 0
and prd.requisition_line_id = prl.requisition_line_id
and prh.requisition_header_id = prl.requisition_header_id
and nvl(prh.closed_code,'OPEN') not like '%CLOSED%'
and nvl(prh.authorization_status,'NOT') not in ('CANCELLED','REJECTED','RETURNED')
and mif.organization_id = fsp.inventory_organization_id
and mif.inventory_item_id = prl.item_id
and ( c_item_from is null or mif.item_number >= c_item_from )
and ( c_item_to is null or mif.item_number <= c_item_to )
and pp.project_id = prd.project_id
and ( c_project_from is null or pp.project_id >= c_project_from )
and ( c_project_to is null or pp.project_id <= c_project_to )
and PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(prl.need_by_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and prl.need_by_date
between nvl( fnd_date.canonical_to_date(d_date_from), prl.need_by_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , prl.need_by_date + 1)
and pt.task_id (+) = prd.task_id
and hou.organization_id (+) = prl.destination_organization_id
and hou.language (+) = userenv('LANG')
and fsp.org_id = prh.org_id
order by prh.segment1
;
select 'RFQ' document_type
, ph.segment1 rfq_number
, hl.location_code ship_to_location
, mif.item_number item_number
, mif.description description
, ph.reply_date due_date
, pl.po_line_id po_line_id
, plc.displayed_field status
, pp.segment1 project_number
, pp.name project_name
, pp.start_date project_start_date
, pp.completion_date project_end_date
, pt.task_number task_number
, pt.task_name task_name
, pt.start_date task_start_date
, pt.completion_date task_end_date
, PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.reply_date
,'BEFORE_END'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) exception_days1
, pl.project_id project_id
, pl.task_id task_id
from po_lines_all pl
, po_headers_all ph
, po_lookup_codes plc
, hr_locations hl
, mtl_item_flexfields mif
, pa_projects_all pp
, pa_tasks pt
, financials_system_params_all fsp
where pl.project_id > 0
and ph.po_header_id = pl.po_header_id
and ph.type_lookup_code = 'RFQ'
and ph.status_lookup_code <> 'C'
and plc.lookup_type = 'RFQ/QUOTE STATUS'
and plc.lookup_code = ph.status_lookup_code
and hl.location_id (+) = ph.ship_to_location_id
and mif.organization_id = fsp.inventory_organization_id
and mif.inventory_item_id = pl.item_id
and ( c_item_from is null or mif.item_number >= c_item_from )
and ( c_item_to is null or mif.item_number <= c_item_to )
and pp.project_id = pl.project_id
and ( c_project_from is null or pp.project_id >= c_project_from )
and ( c_project_to is null or pp.project_id <= c_project_to )
and PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.reply_date
,'BEFORE_END'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and ph.reply_date
between nvl( fnd_date.canonical_to_date(d_date_from), ph.reply_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , ph.reply_date + 1)
and pt.task_id (+) = pl.task_id
and pl.org_id = ph.org_id
and fsp.org_id = pl.org_id
order by ph.segment1
;
select 'QUOTATION' document_type
, ph.segment1 quotation_number
, hl.location_code ship_to_location
, mif.item_number item_number
, mif.description description
, ph.start_date eff_start_date
, ph.end_date eff_end_date
, pl.po_line_id po_line_id
, plc.displayed_field status
, pp.segment1 project_number
, pp.name project_name
, pp.start_date project_start_date
, pp.completion_date project_end_date
, pt.task_number task_number
, pt.task_name task_name
, pt.start_date task_start_date
, pt.completion_date task_end_date
, PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.start_date
,'BEFORE_END'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) exception_days1
, PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.end_date
,'AFTER_END'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) exception_days2
, pl.project_id project_id
, pl.task_id task_id
from po_lines_all pl
, po_headers_all ph
, po_lookup_codes plc
, hr_locations hl
, mtl_item_flexfields mif
, pa_projects_all pp
, pa_tasks pt
, financials_system_params_all fsp
where pl.project_id > 0
and ph.po_header_id = pl.po_header_id
and ph.type_lookup_code = 'QUOTATION'
and ph.status_lookup_code <> 'C'
and plc.lookup_type = 'RFQ/QUOTE STATUS'
and plc.lookup_code = ph.status_lookup_code
and hl.location_id (+) = ph.ship_to_location_id
and mif.organization_id = fsp.inventory_organization_id
and mif.inventory_item_id = pl.item_id
and ( c_item_from is null or mif.item_number >= c_item_from )
and ( c_item_to is null or mif.item_number <= c_item_to )
and pp.project_id = pl.project_id
and ( c_project_from is null or pp.project_id >= c_project_from )
and ( c_project_to is null or pp.project_id <= c_project_to )
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.start_date
,'BEFORE_END'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and ph.start_date
between nvl( fnd_date.canonical_to_date(d_date_from), ph.start_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , ph.start_date + 1)
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(ph.end_date
,'AFTER_END'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and ph.end_date
between nvl( fnd_date.canonical_to_date(d_date_from), ph.end_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , ph.end_date + 1)
))
and pt.task_id (+) = pl.task_id
and pl.org_id = ph.org_id
and fsp.org_id = pl.org_id
order by ph.segment1
;
select 'MDS' document_type
, msd.schedule_designator mds_name
, msd.mps_transaction_id transaction_id
, msd.schedule_level schedule_level
, mif.item_number item_number
, mif.description description
, msd.schedule_date schedule_date
, msd.rate_end_date schedule_end_date
, msd.original_schedule_quantity quantity
, pp.segment1 project_number
, pp.name project_name
, pp.start_date project_start_date
, pp.completion_date project_end_date
, pt.task_number task_number
, pt.task_name task_name
, pt.start_date task_start_date
, pt.completion_date task_end_date
, hou.name organization_name
, PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.schedule_date
,'BETWEEN'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) exception_days1
, PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.rate_end_date
,'BETWEEN'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) exception_days2
, msd.project_id project_id
, msd.task_id task_id
from mrp_schedule_dates msd
, hr_all_organization_units_tl hou
, mtl_item_flexfields mif
, pa_projects_all pp
, pa_tasks pt
where msd.project_id > 0
and nvl(decode(mif.repetitive_planning_flag,
'Y',msd.repetitive_daily_rate
,msd.schedule_quantity),0) <>0
and msd.supply_demand_type = 1
and msd.schedule_level = 2
and mif.organization_id = msd.organization_id
and mif.inventory_item_id = msd.inventory_item_id
and ( c_item_from is null or mif.item_number >= c_item_from )
and ( c_item_to is null or mif.item_number <= c_item_to )
and hou.organization_id = msd.organization_id
and hou.language = userenv('LANG')
and pp.project_id = msd.project_id
and ( c_project_from is null or pp.project_id >= c_project_from )
and ( c_project_to is null or pp.project_id <= c_project_to )
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.schedule_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and msd.schedule_date
between nvl( fnd_date.canonical_to_date(d_date_from), msd.schedule_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , msd.schedule_date + 1)
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.rate_end_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and msd.rate_end_date
between nvl( fnd_date.canonical_to_date(d_date_from), msd.rate_end_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , msd.rate_end_date + 1)
))
and pt.task_id (+) = msd.task_id
order by msd.schedule_designator
;
select 'MPS' document_type
, msd.schedule_designator mps_name
, msd.mps_transaction_id transaction_id
, msd.schedule_level schedule_level
, mif.item_number item_number
, mif.description description
, msd.schedule_date schedule_date
, msd.rate_end_date schedule_end_date
, msd.original_schedule_quantity quantity
, pp.segment1 project_number
, pp.name project_name
, pp.start_date project_start_date
, pp.completion_date project_end_date
, pt.task_number task_number
, pt.task_name task_name
, pt.start_date task_start_date
, pt.completion_date task_end_date
, hou.name organization_name
, PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.schedule_date
,'BETWEEN'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) exception_days1
, PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.rate_end_date
,'BETWEEN'
,0
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) exception_days2
, msd.project_id project_id
, msd.task_id task_id
from mrp_schedule_dates msd
, hr_all_organization_units_tl hou
, mtl_item_flexfields mif
, pa_projects_all pp
, pa_tasks pt
where msd.project_id > 0
and nvl(decode(mif.repetitive_planning_flag,
'Y',msd.repetitive_daily_rate
,msd.schedule_quantity),0) <>0
and msd.supply_demand_type = 2
and msd.schedule_level = 2
and mif.organization_id = msd.organization_id
and mif.inventory_item_id = msd.inventory_item_id
and ( c_item_from is null or mif.item_number >= c_item_from )
and ( c_item_to is null or mif.item_number <= c_item_to )
and hou.organization_id = msd.organization_id
and hou.language = userenv('LANG')
and pp.project_id = msd.project_id
and ( c_project_from is null or pp.project_id >= c_project_from )
and ( c_project_to is null or pp.project_id <= c_project_to )
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.schedule_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and msd.schedule_date
between nvl( fnd_date.canonical_to_date(d_date_from), msd.schedule_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , msd.schedule_date + 1)
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(msd.rate_end_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and msd.rate_end_date
between nvl( fnd_date.canonical_to_date(d_date_from), msd.rate_end_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , msd.rate_end_date + 1)
))
and pt.task_id (+) = msd.task_id
order by msd.schedule_designator
;
select 'PO' document_type
, ph.segment1 po_number
, PO_HEADERS_SV3.GET_PO_STATUS(ph.po_header_id) status
, hou.name ship_to_location
, mif.item_number item_number
, mif.description description
, pd.quantity_ordered ordered_quantity
, pd.quantity_delivered delivered_quantity
, pd.po_distribution_id po_distribution_id
, pll.promised_date promised_date
, pll.need_by_date need_by_date
, pp.segment1 project_number
, pp.name project_name
, pp.start_date project_start_date
, pp.completion_date project_end_date
, pt.task_number task_number
, pt.task_name task_name
, pt.start_date task_start_date
, pt.completion_date task_end_date
, 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
, 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
, pd.project_id project_id
, pd.task_id task_id
from po_distributions_all pd
, 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_all_organization_units_tl hou
, financials_system_params_all fsp
where pd.project_id > 0
and pd.po_release_id is null
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(pl.cancel_flag, 'N') <> 'Y' /* Bug 6262080; base bug 5757447 */
select 'BLANKET_RELEASE' document_type
, ph.segment1 po_number
, PO_HEADERS_SV3.GET_PO_STATUS(ph.po_header_id) status
, hou.name ship_to_location
, pr.release_num release_number
, mif.item_number item_number
, mif.description description
, pd.quantity_ordered ordered_quantity
, pd.quantity_delivered delivered_quantity
, pd.po_distribution_id po_distribution_id
, pll.promised_date promised_date
, pll.need_by_date need_by_date
, pp.segment1 project_number
, pp.name project_name
, pp.start_date project_start_date
, pp.completion_date project_end_date
, pt.task_number task_number
, pt.task_name task_name
, pt.start_date task_start_date
, pt.completion_date task_end_date
, 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
, 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
, pd.project_id project_id
, pd.task_id task_id
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_all_organization_units_tl hou
, financials_system_params_all fsp
where pd.project_id > 0
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 ( c_item_from is null or mif.item_number >= c_item_from )
and ( c_item_to is null or mif.item_number <= c_item_to )
and pp.project_id = pd.project_id
and ( c_project_from is null or pp.project_id >= c_project_from )
and ( c_project_to is null or pp.project_id <= c_project_to )
and ((
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.promised_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and pll.promised_date
between nvl( fnd_date.canonical_to_date(d_date_from), pll.promised_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , pll.promised_date + 1)
) or (
PJM_INTEGRATION_PROJ_MFG.PJM_EXCEPTION_DAYS(pll.need_by_date
,'BETWEEN'
,n_tolerance_days
,pp.start_date
,pp.completion_date
,pt.start_date
,pt.completion_date
) <>0
and pll.need_by_date
between nvl( fnd_date.canonical_to_date(d_date_from), pll.need_by_date - 1)
and nvl( fnd_date.canonical_to_date(d_date_to) , pll.need_by_date + 1)
))
and pt.task_id (+) = pd.task_id
and hou.organization_id (+) = pd.destination_organization_id
and hou.language (+) = userenv('LANG')
and ph.org_id = pl.org_id
and pl.org_id = pd.org_id
and pl.org_id = fsp.org_id
and pl.org_id = pll.org_id
and pl.org_id = pr.org_id
order by ph.segment1
;