APPS.PA_IND_RATE_SCH_REVISIONS_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 16

    select 1
    into   x_dummy
    from   gl_period_statuses gp,
	   pa_implementations imp
    where  gp.start_date = x_start_date_active
      and  gp.application_id = Pa_Period_Process_Pkg.Application_Id
      and  gp.set_of_books_id = imp.set_of_books_id
      and  gp.adjustment_period_flag = 'N';

Line: 50

    select 1
    into   x_dummy
    from   gl_period_statuses gp,
	   pa_implementations imp
    where  gp.end_date = x_end_date_active
      and  gp.application_id = Pa_Period_Process_Pkg.Application_Id
      and  gp.set_of_books_id = imp.set_of_books_id
      and  gp.adjustment_period_flag = 'N';

Line: 90

    select ind_rate_sch_revision_id,
           start_date_active,
           end_date_active
    into   x_max_revision_id,
           x_max_start_date_active,
           x_max_end_date_active
    from   pa_ind_rate_sch_revisions
    where  ind_rate_sch_id = x_ind_rate_sch_id
    and    start_date_active in
			(select max(start_date_active)
                         from   pa_ind_rate_sch_revisions
			 where ind_rate_sch_id = x_ind_rate_sch_id);

Line: 200

select ics.ind_compiled_set_id
  from pa_ind_compiled_sets ics
  where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id ;

Line: 205

  select 1
  from dual
  where exists (select null
/*                  from pa_expenditure_items ei */       /* for bug 6334295 */
                  from pa_expenditure_items_all ei
                  where tp_ind_compiled_set_id = l_ind_compile_set_id  );

Line: 213

  select 1
  from dual
  where exists (select /*+ index(ei PA_EXPENDITURE_ITEMS_N11) */ null  --added hint for bug 5845101
/*                  from pa_expenditure_items ei */       /* for bug 6334295 */
                  from pa_expenditure_items_all ei
                  where cost_ind_compiled_set_id = l_ind_compile_set_id);

Line: 221

   select 1
  from dual
  where exists (select null
/*                  from pa_expenditure_items */       /* for bug 6334295 */
                  from pa_expenditure_items_all
                  where rev_ind_compiled_set_id = l_ind_compile_set_id);

Line: 229

  select 1
  from dual
  where exists  (select null
/*                  from pa_expenditure_items */       /* for bug 6334295 */
                  from pa_expenditure_items_all
                  where inv_ind_compiled_set_id = l_ind_compile_set_id);

Line: 241

   select 1
     from   sys.dual
    where exists (select 1
                  from pa_alloc_txn_details
                  where ind_rate_sch_revision_id = x_ind_rate_sch_revision_id);

Line: 250

  select 1 from dual where exists (
                     select 1 from pa_ind_compiled_sets ICS
                     WHERE ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id and
		     (exists
                       (SELECT NULL FROM pa_cost_distribution_lines_all CDL
                         WHERE ICS.ind_compiled_set_id = CDL.ind_compiled_set_id
                       )
                     OR (l_gms_installed = 'Y' and EXISTS
                          (SELECT NULL FROM gms_award_distributions adl
                           WHERE ICS.ind_compiled_set_id = adL.ind_compiled_set_id
                          )
		         )
                     OR (l_gms_installed = 'Y' and EXISTS
                          (SELECT NULL FROM gms_encumbrance_items gei
                           WHERE ICS.ind_compiled_set_id = gei.ind_compiled_set_id
                          )
		         ))
		      ); */

Line: 271

  select 1 from dual where exists

                       (SELECT NULL FROM pa_cost_distribution_lines_all CDL
                         WHERE  CDL.ind_compiled_set_id = l_ind_compile_set_id
                       )
/*                     OR (l_gms_installed = 'Y' and EXISTS*/        /* for bug 6334295 */
                       OR EXISTS
                          (SELECT NULL FROM gms_award_distributions adl
                           WHERE adL.ind_compiled_set_id = l_ind_compile_set_id)

		       OR EXISTS
/*                     OR (l_gms_installed = 'Y' and EXISTS*/       /* for bug 6334295 */
/*                          (SELECT NULL FROM gms_encumbrance_items gei */       /* for bug 6334295 */
                          (SELECT NULL FROM gms_encumbrance_items_all gei
                           WHERE  gei.ind_compiled_set_id = l_ind_compile_set_id
		                );

Line: 301

/* This select does a full table scan on pa_expenditure_item
    select 1
    into   x_dummy
    from   sys.dual
    where  not exists
           (select 1
           from   pa_expenditure_items ei,
		  pa_ind_compiled_sets ics
           where  ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
	   and    (   (ei.cost_ind_compiled_set_id = ics.ind_compiled_set_id)
	           or (ei.rev_ind_compiled_set_id = ics.ind_compiled_set_id)
	           or (ei.inv_ind_compiled_set_id = ics.ind_compiled_set_id)));

Line: 471

  select 1
  from pa_ind_compiled_sets ics
  where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
  and exists (select null
                  from pa_expenditure_items ei
                  where tp_ind_compiled_set_id = ics.ind_compiled_set_id and expenditure_item_date > x_end_date_active);

Line: 480

  select 1
  from pa_ind_compiled_sets ics
  where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
  /*and not exists (select null -- Changed the not exists to exists */
  and exists (select null
                  from pa_expenditure_items_all ei
                  where cost_ind_compiled_set_id = ics.ind_compiled_set_id and expenditure_item_date > x_end_date_active);

Line: 489

  select 1
  from pa_ind_compiled_sets ics
  where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
  /*and not exists (select null -- Changed the not exists to exists */
  and exists (select null
                  from pa_expenditure_items_all
                  where rev_ind_compiled_set_id = ics.ind_compiled_set_id and expenditure_item_date > x_end_date_active);

Line: 498

  select 1
  from pa_ind_compiled_sets ics
  where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
  /*and not exists (select null -- Changed the not exists to exists */
  and exists (select null
                  from pa_expenditure_items_all
                  where inv_ind_compiled_set_id = ics.ind_compiled_set_id and expenditure_item_date > x_end_date_active);

Line: 508

  select 1 from dual where exists (select 1 from pa_ind_compiled_sets ICS
                     WHERE ics.ind_rate_sch_revision_id =x_ind_rate_sch_revision_id and
			exists (SELECT NULL FROM gms_encumbrance_items_all gei
                           WHERE ICS.ind_compiled_set_id = gei.ind_compiled_set_id
                             and ENCUMBRANCE_ITEM_DATE > x_end_date_active
/*                             and l_gms_installed = 'Y'*/      /* commented for bug 6334295 */
                          ));

Line: 602

    select end_date_active
    into   x_end_date_active
    from   pa_ind_rate_sch_revisions
    where  ind_rate_sch_revision_id = x_ind_rate_sch_revision_id;

Line: 609

        select end_date_active,
               start_date_active,
               ind_rate_sch_revision_id
        into   x_prev_end_date_active,
               x_prev_start_date_active,
               x_prev_revision_id
        from   pa_ind_rate_sch_revisions
        where  ind_rate_sch_id = x_ind_rate_sch_id
        and    end_date_active in
                              (select max(end_date_active)
                               from   pa_ind_rate_sch_revisions
                               where ind_rate_sch_id = x_ind_rate_sch_id);

Line: 624

        select end_date_active,
               start_date_active,
               ind_rate_sch_revision_id
        into   x_prev_end_date_active,
               x_prev_start_date_active,
               x_prev_revision_id
        from   pa_ind_rate_sch_revisions
        where  ind_rate_sch_id = x_ind_rate_sch_id
        and    end_date_active < x_end_date_active
        and    end_date_active in
                              (select max(end_date_active)
                               from   pa_ind_rate_sch_revisions
                               where ind_rate_sch_id = x_ind_rate_sch_id
                               and    end_date_active < x_end_date_active);

Line: 700

    select start_date_active
    into   x_start_date_active
    from   pa_ind_rate_sch_revisions
    where  ind_rate_sch_revision_id = x_ind_rate_sch_revision_id;

Line: 705

    select start_date_active,
           end_date_active,
           ind_rate_sch_revision_id
    into   x_next_start_date_active,
           x_next_end_date_active,
           x_next_revision_id
    from   pa_ind_rate_sch_revisions
    where  ind_rate_sch_id = x_ind_rate_sch_id
    and    start_date_active > x_start_date_active
    and    start_date_active in
                            (select min(start_date_active)
                             from   pa_ind_rate_sch_revisions
                             where  ind_rate_sch_id = x_ind_rate_sch_id
                             and    start_date_active > x_start_date_active);

Line: 783

   SELECT 1 INTO dummy
   FROM sys.dual
   WHERE EXISTS
     (SELECT 1
      FROM   pa_ind_cost_multipliers
      WHERE  ind_rate_sch_revision_id = x_ind_rate_sch_revision_id);

Line: 797

/***2933915:Added parameter x_ready_to_compile and another select in check_ready_compile()
to check if the multiplier has changed in pa_ind_cost_multipliers.This will form the
additional criteria for deciding if a revision can be compiled or not ***/

procedure check_ready_compile(x_ind_rate_sch_revision_id IN     number,
			      x_ready_compile_flag	 IN OUT NOCOPY varchar2,
                              x_ready_for_compile        IN OUT NOCOPY varchar2,      /*2933915*/
			      x_compiled_flag	         IN OUT NOCOPY varchar2,
                              x_return_status            IN OUT NOCOPY number,
                              x_stage                    IN OUT NOCOPY number)
is
begin

   x_stage := 100;

Line: 813

    SELECT ready_to_compile_flag, compiled_flag
     INTO   x_ready_compile_flag, x_compiled_flag
    FROM   pa_ind_rate_sch_revisions
    WHERE  ind_rate_sch_revision_id = x_ind_rate_sch_revision_id;

Line: 820

   SELECT 'Y'
   INTO   x_ready_for_compile
   FROM   dual
   WHERE  exists (select 1
                   from pa_ind_cost_multipliers
                   WHERE  ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
		   AND     nvl(ready_to_compile_flag,'N') in ('Y','X'));