APPS.PSB_BUDGET_PERIOD_PVT SQL Statements

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

Line: 51

  p_update_dist         IN      VARCHAR2,
  p_update_calc         IN      VARCHAR2,
  p_return_status       OUT  NOCOPY     VARCHAR2  ,
  p_msg_count           OUT  NOCOPY     number,
  p_msg_data            OUT  NOCOPY     varchar2
);

Line: 61

PROCEDURE INSERT_ROW (
  p_api_version         in number,
  p_init_msg_list       in varchar2 := fnd_api.g_false,
  p_commit              in varchar2 := fnd_api.g_false,
  p_validation_level    in number := fnd_api.g_valid_level_full,
  p_return_status       OUT  NOCOPY varchar2,
  p_msg_count           OUT  NOCOPY number,
  p_msg_data            OUT  NOCOPY varchar2,
  p_rowid               IN  OUT  NOCOPY varchar2,
  p_budget_period_id    in number,
  p_budget_calendar_id  in number,
  p_description         in varchar2,
  p_start_date          in date,
  p_end_date            in date,
  p_name                in varchar2,
  p_budget_year_type_id in number,
  p_parent_budget_period_id in number,
  p_budget_period_type in varchar2,
  p_period_distribution_type in varchar2,
  p_calculation_period_type in varchar2,
  p_attribute1  in varchar2,
  p_attribute2  in varchar2,
  p_attribute3  in varchar2,
  p_attribute4  in varchar2,
  p_attribute5  in varchar2,
  p_attribute6  in varchar2,
  p_attribute7  in varchar2,
  p_attribute8  in varchar2,
  p_attribute9  in varchar2,
  p_attribute10 in varchar2,
  p_context     in varchar2,
  p_mode        in varchar2,
  p_requery    OUT  NOCOPY varchar2
  ) is
    cursor C is select ROWID from PSB_BUDGET_PERIODS
      where BUDGET_PERIOD_ID = P_BUDGET_PERIOD_ID;

Line: 97

    P_LAST_UPDATE_DATE DATE;

Line: 98

    P_LAST_UPDATED_BY NUMBER;

Line: 99

    P_LAST_UPDATE_LOGIN NUMBER;

Line: 101

l_api_name      CONSTANT VARCHAR2(30) := 'Insert_Row' ;

Line: 107

  SAVEPOINT Insert_Row ;

Line: 125

  P_LAST_UPDATE_DATE := SYSDATE;

Line: 127

    P_LAST_UPDATED_BY := 1;

Line: 128

    P_LAST_UPDATE_LOGIN := 0;

Line: 130

    P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;

Line: 131

    if P_LAST_UPDATED_BY is NULL then
      P_LAST_UPDATED_BY := -1;

Line: 134

    P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;

Line: 135

    if P_LAST_UPDATE_LOGIN is NULL then
      P_LAST_UPDATE_LOGIN := -1;

Line: 177

  insert into PSB_BUDGET_PERIODS (
    budget_period_id,
    budget_calendar_id,
    description,
    start_date,
    end_date,
    name,
    budget_year_type_id,
    parent_budget_period_id,
    budget_period_type,
    period_distribution_type,
    calculation_period_type,
    attribute1,
    attribute2,
    attribute3,
    attribute4,
    attribute5,
    attribute6,
    attribute7,
    attribute8,
    attribute9,
    attribute10,
    context,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login
  ) values (
    p_budget_period_id,
    p_budget_calendar_id,
    p_description,
    p_start_date,
    p_end_date,
    p_name,
    p_budget_year_type_id,
    p_parent_budget_period_id,
    p_budget_period_type,
    p_period_distribution_type,
    p_calculation_period_type,
    p_attribute1,
    p_attribute2,
    p_attribute3,
    p_attribute4,
    p_attribute5,
    p_attribute6,
    p_attribute7,
    p_attribute8,
    p_attribute9,
    p_attribute10,
    p_context,
    p_last_update_date,
    p_last_updated_by,
    p_last_update_date,
    p_last_updated_by,
    p_last_update_login
  );

Line: 281

     rollback to INSERT_ROW ;

Line: 288

     rollback to INSERT_ROW ;

Line: 295

     rollback to INSERT_ROW ;

Line: 304

END INSERT_ROW;

Line: 339

  cursor c1 is select
      budget_calendar_id,
      description,
      start_date,
      end_date,
      name,
      budget_year_type_id,
      parent_budget_period_id,
      budget_period_type,
      period_distribution_type,
      calculation_period_type,
      attribute1,
      attribute2,
      attribute3,
      attribute4,
      attribute5,
      attribute6,
      attribute7,
      attribute8,
      attribute9,
      attribute10,
      context
    from PSB_BUDGET_PERIODS
    where BUDGET_PERIOD_ID = P_BUDGET_PERIOD_ID
    for update of BUDGET_PERIOD_ID nowait;

Line: 488

PROCEDURE UPDATE_ROW (
  p_api_version         in number,
  p_init_msg_list       in varchar2 := fnd_api.g_false,
  p_commit              in varchar2 := fnd_api.g_false,
  p_validation_level    in number   := fnd_api.g_valid_level_full,
  p_return_status       OUT  NOCOPY varchar2,
  p_msg_count           OUT  NOCOPY number,
  p_msg_data            OUT  NOCOPY varchar2,
  p_budget_period_id    in number,
  p_budget_calendar_id  in number,
  p_description         in varchar2,
  p_start_date          in date,
  p_end_date            in date,
  p_name                in varchar2,
  p_budget_year_type_id      in number,
  p_parent_budget_period_id  in number,
  p_budget_period_type       in varchar2,
  p_period_distribution_type in varchar2,
  p_calculation_period_type  in varchar2,
  p_attribute1  in varchar2,
  p_attribute2  in varchar2,
  p_attribute3  in varchar2,
  p_attribute4  in varchar2,
  p_attribute5  in varchar2,
  p_attribute6  in varchar2,
  p_attribute7  in varchar2,
  p_attribute8  in varchar2,
  p_attribute9  in varchar2,
  p_attribute10 in varchar2,
  p_context     in varchar2,
  p_mode        in varchar2,
  p_requery    OUT  NOCOPY varchar2
  ) is
    P_LAST_UPDATE_DATE DATE;

Line: 522

    P_LAST_UPDATED_BY NUMBER;

Line: 523

    P_LAST_UPDATE_LOGIN NUMBER;

Line: 525

l_api_name      CONSTANT VARCHAR2(30) := 'Update Row';

Line: 528

l_update_dist   VARCHAR2(1) := 'N';

Line: 529

l_update_calc   VARCHAR2(1) := 'N';

Line: 535

  SAVEPOINT Update_Row ;

Line: 557

  P_LAST_UPDATE_DATE := SYSDATE;

Line: 559

    P_LAST_UPDATED_BY := 1;

Line: 560

    P_LAST_UPDATE_LOGIN := 0;

Line: 562

    P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;

Line: 563

    if P_LAST_UPDATED_BY is NULL then
      P_LAST_UPDATED_BY := -1;

Line: 566

    P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;

Line: 567

    if P_LAST_UPDATE_LOGIN is NULL then
      P_LAST_UPDATE_LOGIN := -1;

Line: 580

  select period_distribution_type,calculation_period_type
    into  l_pd_dist_type, l_pd_calc_type
    from  psb_budget_periods
   where  budget_period_id = p_budget_period_id;

Line: 590

	l_update_dist := 'Y';

Line: 594

	l_update_calc := 'Y';

Line: 599

  update PSB_BUDGET_PERIODS set
    budget_calendar_id = p_budget_calendar_id,
    name = p_name,
    start_date = p_start_date,
    end_date = p_end_date,
    description = p_description,
    budget_year_type_id = p_budget_year_type_id,
    parent_budget_period_id = p_parent_budget_period_id,
    budget_period_type = p_budget_period_type,
    period_distribution_type = p_period_distribution_type,
    calculation_period_type = p_calculation_period_type,
    attribute1 = p_attribute1,
    attribute2 = p_attribute2,
    attribute3 = p_attribute3,
    attribute4 = p_attribute4,
    attribute5 = p_attribute5,
    attribute6 = p_attribute6,
    attribute7 = p_attribute7,
    attribute8 = p_attribute8,
    attribute9 = p_attribute9,
    attribute10 = p_attribute10,
    context = p_context,
    last_update_date = p_last_update_date,
    last_updated_by = p_last_updated_by,
    last_update_login = p_last_update_login
  where BUDGET_PERIOD_ID = P_BUDGET_PERIOD_ID
  ;

Line: 640

	       p_update_dist         => l_update_dist,
	       p_update_calc         => l_update_calc,
	       p_return_status       => l_return_status,
	       p_msg_count           => p_msg_count,
	       p_msg_data            => p_msg_data
	       );

Line: 652

  IF l_update_dist = 'Y' THEN
     p_requery := 'Y' ;

Line: 673

     rollback to Update_Row ;

Line: 680

     rollback to Update_Row ;

Line: 687

     rollback to Update_Row ;

Line: 697

END UPDATE_ROW;

Line: 733

  cursor c1 is select rowid from PSB_BUDGET_PERIODS
     where BUDGET_PERIOD_ID = P_BUDGET_PERIOD_ID
  ;

Line: 757

    INSERT_ROW (
     p_api_version,
     p_init_msg_list,
     p_commit,
     p_validation_level,
     p_return_status,
     p_msg_count,
     p_msg_data,
     p_rowid,
     p_budget_period_id,
     p_budget_calendar_id,
     p_description,
     p_start_date,
     p_end_date,
     p_name,
     p_budget_year_type_id,
     p_parent_budget_period_id,
     p_budget_period_type,
     p_period_distribution_type,
     p_calculation_period_type,
     p_attribute1,
     p_attribute2,
     p_attribute3,
     p_attribute4,
     p_attribute5,
     p_attribute6,
     p_attribute7,
     p_attribute8,
     p_attribute9,
     p_attribute10,
     p_context,
     p_mode,
     p_requery    );

Line: 801

  UPDATE_ROW (
   p_api_version,
   p_init_msg_list,
   p_commit,
   p_validation_level,
   p_return_status,
   p_msg_count,
   p_msg_data,
   p_budget_period_id,
   p_budget_calendar_id,
   p_description,
   p_start_date,
   p_end_date,
   p_name,
   p_budget_year_type_id,
   p_parent_budget_period_id,
   p_budget_period_type,
   p_period_distribution_type,
   p_calculation_period_type,
   p_attribute1,
   p_attribute2,
   p_attribute3,
   p_attribute4,
   p_attribute5,
   p_attribute6,
   p_attribute7,
   p_attribute8,
   p_attribute9,
   p_attribute10,
   p_context,
   p_mode,
   p_requery );

Line: 846

PROCEDURE DELETE_ROW (
  p_api_version         in number,
  p_init_msg_list       in varchar2 := fnd_api.g_false,
  p_commit              in varchar2 := fnd_api.g_false,
  p_validation_level    in number := fnd_api.g_valid_level_full,
  p_return_status       OUT  NOCOPY varchar2,
  p_msg_count           OUT  NOCOPY number,
  p_msg_data            OUT  NOCOPY varchar2,
  p_budget_period_id    in number
) is
--
l_api_name    CONSTANT VARCHAR2(30) := 'Delete Row' ;

Line: 867

  SAVEPOINT Delete_Row ;

Line: 878

  select budget_calendar_id,start_date,end_date,budget_year_type_id
	 into l_budget_calendar_id,l_start_date,l_end_date,l_budget_year_type_id
    FROM psb_budget_periods
   WHERE budget_period_id = p_budget_period_id;

Line: 894

  delete from PSB_BUDGET_PERIODS
  where parent_budget_period_id = p_budget_period_id;

Line: 900

  delete from PSB_BUDGET_PERIODS
  where BUDGET_PERIOD_ID = P_BUDGET_PERIOD_ID;

Line: 921

     rollback to Delete_Row;

Line: 928

     rollback to Delete_Row;

Line: 935

     rollback to Delete_Row ;

Line: 944

END DELETE_ROW;

Line: 958

 SELECT count(*)
   INTO l_type_count
   FROM PSB_BUDGET_PERIODS
  WHERE budget_calendar_id  = p_calendar_id
    AND budget_year_type_id = p_curr_year_type
    AND budget_period_id   <> p_budget_period_id ;

Line: 1011

  SELECT max(b.sequence_number), end_date
    FROM psb_budget_periods a,
	 psb_budget_year_types b,
	 psb_budget_year_types c
   WHERE a.budget_year_type_id = b.budget_year_type_id
     AND b.sequence_number     < c.sequence_number
     AND c.budget_year_type_id = p_curr_year_type
     AND a.budget_calendar_id  = p_calendar_id
   GROUP BY end_date
   ORDER BY end_date DESC;

Line: 1023

  SELECT min(b.sequence_number), start_date
    FROM psb_budget_periods a,
	 psb_budget_year_types b,
	 psb_budget_year_types c
   WHERE a.budget_year_type_id = b.budget_year_type_id
     AND b.sequence_number     > c.sequence_number
     AND c.budget_year_type_id = p_curr_year_type
     AND a.budget_calendar_id  = p_calendar_id
   GROUP BY start_date ;

Line: 1034

  SELECT max(a.sequence_number)
    FROM psb_budget_year_types a,
	 psb_budget_year_types b
   WHERE a.sequence_number < b.sequence_number
     AND b.budget_year_type_id = p_curr_year_type;

Line: 1041

  SELECT min(a.sequence_number)
    FROM psb_budget_year_types a,
	 psb_budget_year_types b
   WHERE a.sequence_number > b.sequence_number
     AND b.budget_year_type_id = p_curr_year_type;

Line: 1103

       FND_MESSAGE.SET_NAME('PSB', 'PSB_CANNOT_DELETE_YEAR');

Line: 1191

    SELECT psb_budget_periods_s.nextval
      INTO l_budget_period_id
      FROM dual;

Line: 1196

    INSERT_ROW (
      p_api_version              => 1.0,
      p_init_msg_list            => fnd_api.g_false,
      p_commit                   => fnd_api.g_false,
      p_validation_level         => fnd_api.g_valid_level_full,
      p_return_status            => l_return_status,
      p_msg_count                => p_msg_count,
      p_msg_data                 => p_msg_data,
      p_rowid                    => l_rowid,
      p_budget_period_id         => l_budget_period_id,
      p_budget_calendar_id       => p_calendar_id,
      p_description              => l_short_name,
      p_start_date               => l_start_date,
      p_end_date                 => l_end_date ,
      p_name                     => l_short_name,
      p_budget_year_type_id      => null,
      p_parent_budget_period_id  => p_parent_id,
      p_budget_period_type       => p_period_type,
      p_period_distribution_type => null,
      p_calculation_period_type  => null,
      p_attribute1               => null,
      p_attribute2               => null,
      p_attribute3               => null,
      p_attribute4               => null,
      p_attribute5               => null,
      p_attribute6               => null,
      p_attribute7               => null,
      p_attribute8               => null,
      p_attribute9               => null,
      p_attribute10              => null,
      p_context                  => null,
      p_mode                     => 'R',
      p_requery                  => l_requery
      );

Line: 1310

  p_update_dist         IN      VARCHAR2,
  p_update_calc         IN      VARCHAR2,
  p_return_status       OUT  NOCOPY     VARCHAR2,
  p_msg_count           OUT  NOCOPY number,
  p_msg_data            OUT  NOCOPY varchar2
) IS
--
  l_api_name            CONSTANT VARCHAR2(30)   := 'Create_Periods';

Line: 1330

  IF (p_update_dist  = 'Y') THEN

  -- delete old distribution lines

       delete from psb_budget_periods where
	  budget_calendar_id = p_calendar_id
	  AND parent_budget_period_id = p_year_id
	  AND budget_period_type = 'P';

Line: 1370

  IF (p_update_calc = 'Y') THEN

       delete from psb_budget_periods where
	  budget_calendar_id = p_calendar_id
	  AND parent_budget_period_id = p_year_id
	  AND budget_period_type = 'C' ;

Line: 1518

	 SELECT a.budget_calendar_id,
		a.description,
		a.start_date,
		a.end_date,
		a.name,
		a.budget_year_type_id,
		a.parent_budget_period_id,
		a.budget_period_type,
		a.period_distribution_type,
		a.calculation_period_type,
		a.attribute1,
		a.attribute2,
		a.attribute3,
		a.attribute4,
		a.attribute5,
		a.attribute6,
		a.attribute7,
		a.attribute8,
		a.attribute9,
		a.attribute10,
		a.context,
		b.year_category_type
	   FROM psb_budget_periods    a,
		psb_budget_year_types b
	  WHERE budget_calendar_id    = p_source_cal_id
	    AND a.budget_year_type_id = b.budget_year_type_id
	  ORDER BY start_date;

Line: 1548

       SELECT a.year_category_type,a.budget_year_type_id
	 FROM psb_budget_year_types a,
	      psb_budget_year_types b
	WHERE a.sequence_number < b.sequence_number
	  AND b.budget_year_type_id =   l_prev_year_type_id
	ORDER BY a.sequence_number DESC;

Line: 1610

      SELECT psb_budget_periods_s.nextval
	INTO l_year_id
	FROM dual;

Line: 1625

      INSERT_ROW (
      p_api_version              => 1.0,
      p_init_msg_list            => fnd_api.g_false,
      p_commit                   => fnd_api.g_false,
      p_validation_level         => fnd_api.g_valid_level_full,
      p_return_status            => l_return_status,
      p_msg_count                => p_msg_count,
      p_msg_data                 => p_msg_data,
      p_rowid                    => l_rowid,
      p_budget_period_id         => l_year_id,
      p_budget_calendar_id       => p_target_cal_id,
      p_description              => cal_years_rec.name,
      p_start_date               => cal_years_rec.start_date,
      p_end_date                 => cal_years_rec.end_date ,
      p_name                     => cal_years_rec.name,
      p_budget_year_type_id      => l_year_type_id,
      p_parent_budget_period_id  => null,
      p_budget_period_type       => 'Y',
      p_period_distribution_type => cal_years_rec.period_distribution_type,
      p_calculation_period_type  => l_calc_type,
      p_attribute1               => cal_years_rec.attribute1,
      p_attribute2               => cal_years_rec.attribute2,
      p_attribute3               => cal_years_rec.attribute3,
      p_attribute4               => cal_years_rec.attribute4,
      p_attribute5               => cal_years_rec.attribute5,
      p_attribute6               => cal_years_rec.attribute6,
      p_attribute7               => cal_years_rec.attribute7,
      p_attribute8               => cal_years_rec.attribute8,
      p_attribute9               => cal_years_rec.attribute9,
      p_attribute10              => cal_years_rec.attribute10,
      p_context                  => cal_years_rec.context,
      p_mode                     => 'R' ,
      p_requery                  => l_requery
      );

Line: 1674

  UPDATE psb_budget_periods a
     SET (name, description) =
			      (SELECT name, description
				 FROM psb_budget_periods b
				WHERE budget_calendar_id = p_source_cal_id
				  AND budget_period_type = 'P'
				  AND b.start_date       = a.start_date)
   WHERE a.budget_calendar_id = p_target_cal_id
     AND a.budget_period_type = 'P' ;

Line: 1727

 SELECT count(*)
   INTO l_type_count
   FROM PSB_WORKSHEETS
  WHERE budget_calendar_id  = p_calendar_id;