PA List Organization Forecast Exceptions

âžœ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Organization Forecast Exceptions Report
Application: Projects
Source: PRC: List Organization Forecast Exceptions (XML)
Short Name: PAFPEXRP_XML
DB package: PA_PAFPEXRP_XMLP_PKG
Run PA List Organization Forecast Exceptions and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
            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,</