PA List Organization Forecast Exceptions
Description
- Categories
- BI Publisher
- Application
- Projects
- Source
- PRC: List Organization Forecast Exceptions (XML)
- Short Name
- PAFPEXRP_XML
- DB package
- PA_PAFPEXRP_XMLP_PKG
Imported from BI Publisher
Organization Forecast Exceptions Report
Organization Forecast Exceptions Report
SQL 537 lines
select to_number(:org_id) context_org_id, to_number(:child_organization_id) context_organization_id, pou.name project_ou_name, porg.name project_organization_name, eou.name exp_ou_name, eorg.name exp_organization_name, ppa.segment1 project_number, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name) name, fi.other_rejection_Code rejection_code, min(fi.item_date), max(fi.item_date), PA_PAFPEXRP_XMLP_PKG.cf_rejection_descriptionformul(fi.other_rejection_Code) CF_REJECTION_DESCRIPTION from pa_forecast_items fi, pa_projects_all ppa, hr_all_organization_units_tl pou, hr_all_organization_units_tl porg, hr_all_organization_units_tl eou, hr_all_organization_units_tl eorg, per_all_people_f p, pa_project_role_types rt, pa_project_assignments asg where fi.project_organization_id = to_number(:child_organization_id) and nvl(fi.project_org_id,-99) = to_number(:org_id) and fi.forecast_item_type in ('A','R','U') and fi.delete_flag = 'N' and fi.other_rejection_code is not null and fi.person_id = p.person_id(+) and ppa.project_id = fi.project_id and asg.assignment_id = fi.assignment_id and asg.project_role_id = rt.project_role_id and pou.organization_id = fi.project_org_id AND pou.language = USERENV('LANG') and porg.organization_id = fi.project_organization_id AND porg.language = USERENV('LANG') and eou.organization_id = fi.expenditure_org_id AND eou.language = USERENV('LANG') and eorg.organization_id = fi.expenditure_organization_id AND eorg.language = USERENV('LANG') &p_project_id_param &p_assignment_id_param group by pou.name, porg.name, eou.name, eorg.name, ppa.segment1, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name), to_number(:org_id), to_number(:child_organization_id), fi.other_rejection_Code UNION select to_number(:org_id) context_org_id, to_number(:child_organization_id) context_organization_id, pou.name project_ou_name, porg.name project_organization_name, eou.name exp_ou_name, eorg.name exp_organization_name, ppa.segment1 project_number, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name) name, fi.other_rejection_Code rejection_code, min(fi.item_date), max(fi.item_date), PA_PAFPEXRP_XMLP_PKG.cf_rejection_descriptionformul(fi.other_rejection_Code) CF_REJECTION_DESCRIPTION from pa_forecast_items fi, pa_projects_all ppa, hr_all_organization_units_tl pou, hr_all_organization_units_tl porg, hr_all_organization_units_tl eou, hr_all_organization_units_tl eorg, per_all_people_f p, pa_project_role_types rt, pa_project_assignments asg where fi.expenditure_organization_id = to_number(:child_organization_id) and nvl(fi.expenditure_org_id,-99) = to_number(:org_id) and fi.forecast_item_type in ('A','R','U') and fi.delete_flag = 'N' and fi.other_rejection_code is not null and fi.person_id = p.person_id(+) and ppa.project_id = fi.project_id and asg.assignment_id = fi.assignment_id and asg.project_role_id = rt.project_role_id and pou.organization_id = fi.project_org_id AND pou.language = USERENV('LANG') and porg.organization_id = fi.project_organization_id AND porg.language = USERENV('LANG') and eou.organization_id = fi.expenditure_org_id AND eou.language = USERENV('LANG') and eorg.organization_id = fi.expenditure_organization_id AND eorg.language = USERENV('LANG') &p_project_id_param &p_assignment_id_param group by pou.name, porg.name, eou.name, eorg.name, ppa.segment1, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name), to_number(:org_id), to_number(:child_organization_id), fi.other_rejection_Code UNION select to_number(:org_id) context_org_id, to_number(:child_organization_id) context_organization_id, pou.name project_ou_name, porg.name project_organization_name, eou.name exp_ou_name, eorg.name exp_organization_name, ppa.segment1 project_number, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name) name, fi.burden_rejection_Code rejection_code, min(fi.item_date), max(fi.item_date), PA_PAFPEXRP_XMLP_PKG.cf_rejection_descriptionformul(fi.other_rejection_Code) CF_REJECTION_DESCRIPTION from pa_forecast_items fi, pa_projects_all ppa, hr_all_organization_units_tl pou, hr_all_organization_units_tl porg, hr_all_organization_units_tl eou, hr_all_organization_units_tl eorg, per_all_people_f p, pa_project_role_types rt, pa_project_assignments asg where fi.project_organization_id = to_number(:child_organization_id) and nvl(fi.project_org_id,-99) = to_number(:org_id) and fi.forecast_item_type in ('A','R','U') and fi.delete_flag = 'N' and fi.burden_rejection_code is not null and fi.person_id = p.person_id(+) and ppa.project_id = fi.project_id and asg.assignment_id = fi.assignment_id and asg.project_role_id = rt.project_role_id and pou.organization_id = fi.project_org_id AND pou.language = USERENV('LANG') and porg.organization_id = fi.project_organization_id AND porg.language = USERENV('LANG') and eou.organization_id = fi.expenditure_org_id AND eou.language = USERENV('LANG') and eorg.organization_id = fi.expenditure_organization_id AND eorg.language = USERENV('LANG') &p_project_id_param &p_assignment_id_param group by pou.name, porg.name, eou.name, eorg.name, ppa.segment1, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name), to_number(:org_id), to_number(:child_organization_id), fi.burden_rejection_Code, fi.other_rejection_Code UNION select to_number(:org_id) context_org_id, to_number(:child_organization_id) context_organization_id, pou.name project_ou_name, porg.name project_organization_name, eou.name exp_ou_name, eorg.name exp_organization_name, ppa.segment1 project_number, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name) name, fi.cost_rejection_Code rejection_code, min(fi.item_date), max(fi.item_date), PA_PAFPEXRP_XMLP_PKG.cf_rejection_descriptionformul(fi.other_rejection_Code) CF_REJECTION_DESCRIPTION from pa_forecast_items fi, pa_projects_all ppa, hr_all_organization_units_tl pou, hr_all_organization_units_tl porg, hr_all_organization_units_tl eou, hr_all_organization_units_tl eorg, per_all_people_f p, pa_project_role_types rt, pa_project_assignments asg where fi.expenditure_organization_id = to_number(:child_organization_id) and nvl(fi.expenditure_org_id,-99) = to_number(:org_id) and fi.forecast_item_type in ('A','R','U') and fi.delete_flag = 'N' and fi.burden_rejection_code is not null and fi.person_id = p.person_id(+) and ppa.project_id = fi.project_id and asg.assignment_id = fi.assignment_id and asg.project_role_id = rt.project_role_id and pou.organization_id = fi.project_org_id AND pou.language = USERENV('LANG') and porg.organization_id = fi.project_organization_id AND porg.language = USERENV('LANG') and eou.organization_id = fi.expenditure_org_id AND eou.language = USERENV('LANG') and eorg.organization_id = fi.expenditure_organization_id AND eorg.language = USERENV('LANG') &p_project_id_param &p_assignment_id_param group by pou.name, porg.name, eou.name, eorg.name, ppa.segment1, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name), to_number(:org_id), to_number(:child_organization_id), fi.cost_rejection_Code , fi.other_rejection_Code UNION select to_number(:org_id) context_org_id, to_number(:child_organization_id) context_organization_id, pou.name project_ou_name, porg.name project_organization_name, eou.name exp_ou_name, eorg.name exp_organization_name, ppa.segment1 project_number, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name) name, fi.cost_rejection_Code rejection_code, min(fi.item_date), max(fi.item_date), null from pa_forecast_items fi, pa_projects_all ppa, hr_all_organization_units_tl pou, hr_all_organization_units_tl porg, hr_all_organization_units_tl eou, hr_all_organization_units_tl eorg, per_all_people_f p, pa_project_role_types rt, pa_project_assignments asg where fi.project_organization_id = to_number(:child_organization_id) and nvl(fi.project_org_id,-99) = to_number(:org_id) and fi.forecast_item_type in ('A','R','U') and fi.delete_flag = 'N' and fi.cost_rejection_code is not null and fi.person_id = p.person_id(+) and ppa.project_id = fi.project_id and asg.assignment_id = fi.assignment_id and asg.project_role_id = rt.project_role_id and pou.organization_id = fi.project_org_id AND pou.language = USERENV('LANG') and porg.organization_id = fi.project_organization_id AND porg.language = USERENV('LANG') and eou.organization_id = fi.expenditure_org_id AND eou.language = USERENV('LANG') and eorg.organization_id = fi.expenditure_organization_id AND eorg.language = USERENV('LANG') &p_project_id_param &p_assignment_id_param group by pou.name, porg.name, eou.name, eorg.name, ppa.segment1, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name), to_number(:org_id), to_number(:child_organization_id), fi.cost_rejection_Code UNION select to_number(:org_id) context_org_id, to_number(:child_organization_id) context_organization_id, pou.name project_ou_name, porg.name project_organization_name, eou.name exp_ou_name, eorg.name exp_organization_name, ppa.segment1 project_number, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name) name, fi.cost_rejection_Code rejection_code, min(fi.item_date), max(fi.item_date), PA_PAFPEXRP_XMLP_PKG.cf_rejection_descriptionformul(fi.other_rejection_Code) CF_REJECTION_DESCRIPTION from pa_forecast_items fi, pa_projects_all ppa, hr_all_organization_units_tl pou, hr_all_organization_units_tl porg, hr_all_organization_units_tl eou, hr_all_organization_units_tl eorg, per_all_people_f p, pa_project_role_types rt, pa_project_assignments asg where fi.expenditure_organization_id = to_number(:child_organization_id) and nvl(fi.expenditure_org_id,-99) = to_number(:org_id) and fi.forecast_item_type in ('A','R','U') and fi.delete_flag = 'N' and fi.cost_rejection_code is not null and fi.person_id = p.person_id(+) and ppa.project_id = fi.project_id and asg.assignment_id = fi.assignment_id and asg.project_role_id = rt.project_role_id and pou.organization_id = fi.project_org_id AND pou.language = USERENV('LANG') and porg.organization_id = fi.project_organization_id AND porg.language = USERENV('LANG') and eou.organization_id = fi.expenditure_org_id AND eou.language = USERENV('LANG') and eorg.organization_id = fi.expenditure_organization_id AND eorg.language = USERENV('LANG') &p_project_id_param &p_assignment_id_param group by pou.name, porg.name, eou.name, eorg.name, ppa.segment1, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name), to_number(:org_id), to_number(:child_organization_id), fi.cost_rejection_Code, fi.other_rejection_Code UNION select to_number(:org_id) context_org_id, to_number(:child_organization_id) context_organization_id, pou.name project_ou_name, porg.name project_organization_name, eou.name exp_ou_name, eorg.name exp_organization_name, ppa.segment1 project_number, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name) name, fi.rev_rejection_Code rejection_code, min(fi.item_date), max(fi.item_date), PA_PAFPEXRP_XMLP_PKG.cf_rejection_descriptionformul(fi.other_rejection_Code) CF_REJECTION_DESCRIPTION from pa_forecast_items fi, pa_projects_all ppa, hr_all_organization_units_tl pou, hr_all_organization_units_tl porg, hr_all_organization_units_tl eou, hr_all_organization_units_tl eorg, per_all_people_f p, pa_project_role_types rt, pa_project_assignments asg where fi.project_organization_id = to_number(:child_organization_id) and nvl(fi.project_org_id,-99) = to_number(:org_id) and fi.forecast_item_type in ('A','R','U') and fi.delete_flag = 'N' and fi.rev_rejection_code is not null and fi.person_id = p.person_id(+) and ppa.project_id = fi.project_id and asg.assignment_id = fi.assignment_id and asg.project_role_id = rt.project_role_id and pou.organization_id = fi.project_org_id AND pou.language = USERENV('LANG') and porg.organization_id = fi.project_organization_id AND porg.language = USERENV('LANG') and eou.organization_id = fi.expenditure_org_id AND eou.language = USERENV('LANG') and eorg.organization_id = fi.expenditure_organization_id AND eorg.language = USERENV('LANG') &p_project_id_param &p_assignment_id_param group by pou.name, porg.name, eou.name, eorg.name, ppa.segment1, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name), to_number(:org_id), to_number(:child_organization_id), fi.rev_rejection_Code, fi.other_rejection_Code UNION select to_number(:org_id) context_org_id, to_number(:child_organization_id) context_organization_id, pou.name project_ou_name, porg.name project_organization_name, eou.name exp_ou_name, eorg.name exp_organization_name, ppa.segment1 project_number, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name) name, fi.rev_rejection_Code rejection_code, min(fi.item_date), max(fi.item_date), PA_PAFPEXRP_XMLP_PKG.cf_rejection_descriptionformul(fi.other_rejection_Code) CF_REJECTION_DESCRIPTION from pa_forecast_items fi, pa_projects_all ppa, hr_all_organization_units_tl pou, hr_all_organization_units_tl porg, hr_all_organization_units_tl eou, hr_all_organization_units_tl eorg, per_all_people_f p, pa_project_role_types rt, pa_project_assignments asg where fi.expenditure_organization_id = to_number(:child_organization_id) and nvl(fi.expenditure_org_id,-99) = to_number(:org_id) and fi.forecast_item_type in ('A','R','U') and fi.delete_flag = 'N' and fi.rev_rejection_code is not null and fi.person_id = p.person_id(+) and ppa.project_id = fi.project_id and asg.assignment_id = fi.assignment_id and asg.project_role_id = rt.project_role_id and pou.organization_id = fi.project_org_id AND pou.language = USERENV('LANG') and porg.organization_id = fi.project_organization_id AND porg.language = USERENV('LANG') and eou.organization_id = fi.expenditure_org_id AND eou.language = USERENV('LANG') and eorg.organization_id = fi.expenditure_organization_id AND eorg.language = USERENV('LANG') &p_project_id_param &p_assignment_id_param group by pou.name, porg.name, eou.name, eorg.name, ppa.segment1, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name), to_number(:org_id), to_number(:child_organization_id), fi.rev_rejection_Code, fi.other_rejection_Code UNION select to_number(:org_id) context_org_id, to_number(:child_organization_id) context_organization_id, pou.name project_ou_name, porg.name project_organization_name, eou.name exp_ou_name, eorg.name exp_organization_name, ppa.segment1 project_number, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name) name, fi.tp_rejection_Code rejection_code, min(fi.item_date), max(fi.item_date), PA_PAFPEXRP_XMLP_PKG.cf_rejection_descriptionformul(fi.other_rejection_Code) CF_REJECTION_DESCRIPTION from pa_forecast_items fi, pa_projects_all ppa, hr_all_organization_units_tl pou, hr_all_organization_units_tl porg, hr_all_organization_units_tl eou, hr_all_organization_units_tl eorg, per_all_people_f p, pa_project_role_types rt, pa_project_assignments asg where fi.project_organization_id = to_number(:child_organization_id) and nvl(fi.project_org_id,-99) = to_number(:org_id) and fi.forecast_item_type in ('A','R','U') and fi.delete_flag = 'N' and fi.tp_rejection_code is not null and fi.person_id = p.person_id(+) and ppa.project_id = fi.project_id and asg.assignment_id = fi.assignment_id and asg.project_role_id = rt.project_role_id and pou.organization_id = fi.project_org_id AND pou.language = USERENV('LANG') and porg.organization_id = fi.project_organization_id AND porg.language = USERENV('LANG') and eou.organization_id = fi.expenditure_org_id AND eou.language = USERENV('LANG') and eorg.organization_id = fi.expenditure_organization_id AND eorg.language = USERENV('LANG') &p_project_id_param &p_assignment_id_param group by pou.name, porg.name, eou.name, eorg.name, ppa.segment1, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name), to_number(:org_id), to_number(:child_organization_id), fi.tp_rejection_Code, fi.other_rejection_Code UNION select to_number(:org_id) context_org_id, to_number(:child_organization_id) context_organization_id, pou.name project_ou_name, porg.name project_organization_name, eou.name exp_ou_name, eorg.name exp_organization_name, ppa.segment1 project_number, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name) name, fi.tp_rejection_Code rejection_code, min(fi.item_date), max(fi.item_date), PA_PAFPEXRP_XMLP_PKG.cf_rejection_descriptionformul(fi.other_rejection_Code) CF_REJECTION_DESCRIPTION from pa_forecast_items fi, pa_projects_all ppa, hr_all_organization_units_tl pou, hr_all_organization_units_tl porg, hr_all_organization_units_tl eou, hr_all_organization_units_tl eorg, per_all_people_f p, pa_project_role_types rt, pa_project_assignments asg where fi.expenditure_organization_id = to_number(:child_organization_id) and nvl(fi.expenditure_org_id,-99) = to_number(:org_id) and fi.forecast_item_type in ('A','R','U') and fi.delete_flag = 'N' and fi.tp_rejection_code is not null and fi.person_id = p.person_id(+) and ppa.project_id = fi.project_id and asg.assignment_id = fi.assignment_id and asg.project_role_id = rt.project_role_id and pou.organization_id = fi.project_org_id AND pou.language = USERENV('LANG') and porg.organization_id = fi.project_organization_id AND porg.language = USERENV('LANG') and eou.organization_id = fi.expenditure_org_id AND eou.language = USERENV('LANG') and eorg.organization_id = fi.expenditure_organization_id AND eorg.language = USERENV('LANG') &p_project_id_param &p_assignment_id_param --and to_number(:org_id)=to_number(:org_id) --and to_number(:child_organization_id)=to_number(:child_organization_id) group by pou.name, porg.name, eou.name, eorg.name, ppa.segment1, asg.assignment_name, decode(fi.forecast_item_type,'R',rt.meaning,p.full_name), to_number(:org_id), to_number(:child_organization_id), fi.tp_rejection_Code, fi.other_rejection_Code order by 3,4,5,6,7,8,9 |
Parameters 5
| Parameter | Type | List of values SQL |
|---|---|---|
| Selection Criteria | LOV Oracle | |
| Project Name | LOV Oracle | |
| Assignment Name | LOV Oracle | |
| Organization | LOV Oracle | |
| Start Organization | LOV Oracle |