PA List Organization Forecast Exceptions

Description
Categories: BI Publisher
Columns: Context Org Id, Context Organization Id, Project Ou Name, Project Organization Name, Exp Ou Name, Exp Organization Name, Project Number, Assignment Name, Name, Rejection Code ...
Application: Projects
Source: PRC: List Organization Forecast Exceptions (XML)
Short Name: PAFPEXRP_XML
DB package: PA_PAFPEXRP_XMLP_PKG
            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
Parameter Name SQL text Validation
Selection Criteria
 
LOV Oracle
Project Name
 
LOV Oracle
Assignment Name
 
LOV Oracle
Organization
 
LOV Oracle
Start Organization
 
LOV Oracle
Operating Unit Id
 
PA_SRS_FP_CHAR_1