Project Agreements

*##############################################################################

#     PROJECT HEADER DETAILS - AGREEMENTS

/*############################################################################*/

  SELECT DISTINCT ppa.segment1 pnum

       , ppa.creation_date date_

       , ppa.name

       , pps.project_status_name status

       , ppta.project_type project_type

       , ppa.distribution_rule type_

       , ppa.start_date

       , ppa.completion_date end_date

       , ppta.burden_cost_flag burden

       , ppa.attribute2 fec

       , pirsa.ind_rate_sch_name proj_burden_flag

       , '--- funding_agreement_details ---'

       , pt.task_number

       , paa.agreement_num

       , ppf.allocated_amount

       , paa.description

       , paa.amount

       , paa.invoice_limit_flag

       , paa.revenue_limit_flag

       , ppf.funding_category

    FROM pa.pa_projects_all ppa

       , pa.pa_tasks pt

       , pa.pa_projects_all ppa_template

       , pa.pa_project_statuses pps

       , pa.pa_project_types_all ppta

       , pa.pa_ind_rate_schedules_all_bg pirsa

       , pa.pa_project_fundings ppf

       , pa.pa_agreements_all paa

   WHERE ppa.project_id = pt.project_id

     AND ppa.project_status_code = pps.project_status_code

     AND ppa.project_type = ppta.project_type

     AND ppa.created_from_project_id = ppa_template.project_id(+)

     AND ppa.cost_ind_rate_sch_id = pirsa.ind_rate_sch_id(+)

     AND ppa.project_id = ppf.project_id

     AND ppf.agreement_id = paa.agreement_id

     AND ppf.task_id = pt.task_id

     AND ppa.creation_date BETWEEN '01-NOV-2012' AND '01-DEC-2012'

ORDER BY ppa.creation_date DESC;

/*##############################################################################

#      BASIC AGREEMENTS WITH HARD INVOICE LIMIT

/*############################################################################*/

  SELECT DISTINCT ppa.segment1 pnum

                , ppa.creation_date date_

                , pps.project_status_name status

                , ppa.name proj_name

                , paa.agreement_num

                , paa.invoice_limit_flag

                , paa.revenue_limit_flag

    FROM pa.pa_projects_all ppa

       , pa.pa_project_fundings ppf

       , pa.pa_agreements_all paa

       , pa.pa_project_statuses pps

   WHERE ppa.project_id = ppf.project_id

     AND ppf.agreement_id = paa.agreement_id

     AND ppa.project_status_code = pps.project_status_code

     AND paa.invoice_limit_flag = 'Y'

ORDER BY ppa.creation_date DESC;

/* Basic Agreements

   Can have an agreement without a Project linked to it, since the link to

   the Project is via the Funding */

  SELECT fu.description

       , paa.creation_date cr_date

       , PAA.ADVANCE_REQUIRED

       , paa.*

    FROM pa.pa_agreements_all paa

       , applsys.fnd_user fu

   WHERE paa.created_by = fu.user_id

ORDER BY agreement_id DESC;

/* Basic Fundings */

  SELECT *

    FROM pa.pa_project_fundings ppf

ORDER BY 2 DESC;