APPS.PSB_CREATE_BR_PVT SQL Statements

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

Line: 27

  PROCEDURE Insert_BR_Lines_Pvt
  (
    p_budget_revision_id            IN  NUMBER,
    p_budget_revision_acct_line_id  IN  NUMBER,
    p_freeze_flag                   IN  VARCHAR2,
    p_view_line_flag                IN  VARCHAR2,
    p_last_update_date              IN  DATE,
    p_last_updated_by               IN  NUMBER,
    p_last_update_login             IN  NUMBER,
    p_created_by                    IN  NUMBER,
    p_creation_date                 IN  DATE,
    p_return_status                 OUT  NOCOPY VARCHAR2
  ) ;

Line: 41

  PROCEDURE Insert_BR_Pos_Lines_Pvt
  (
    p_budget_revision_id            IN  NUMBER,
    p_budget_revision_pos_line_id   IN  NUMBER,
    p_freeze_flag                   IN  VARCHAR2,
    p_view_line_flag                IN  VARCHAR2,
    p_last_update_date              IN  DATE,
    p_last_updated_by               IN  NUMBER,
    p_last_update_login             IN  NUMBER,
    p_created_by                    IN  NUMBER,
    p_creation_date                 IN  DATE,
    p_return_status                 OUT  NOCOPY VARCHAR2
  ) ;

Line: 437

	 SELECT *
	 FROM   psb_budget_revisions
	 WHERE  budget_revision_id = p_budget_revision_id;

Line: 442

	 SELECT count(*)
	 FROM   psb_budget_revision_pos_lines lines,
		psb_budget_revisions rev
	 WHERE  rev.budget_revision_id = p_budget_revision_id
	   AND  rev.budget_revision_id = lines.budget_revision_id;

Line: 476

  SELECT '1' INTO l_tmp_char
  FROM   psb_budget_groups
  WHERE  budget_group_id = p_budget_group_id ;

Line: 502

  SELECT name INTO l_main_budget_group_name
  FROM   psb_budget_groups
  WHERE  budget_group_id = l_main_budget_group_id;

Line: 618

    SELECT lines.*
    FROM   psb_budget_revision_lines     lines,
	   psb_budget_revision_accounts  acct
    WHERE  lines.budget_revision_id    = p_budget_revision_id
    AND    lines.budget_revision_acct_line_id =
				       acct.budget_revision_acct_line_id
    AND      acct.budget_group_id in
	       (  SELECT budget_group_id
		    FROM psb_budget_groups
		   WHERE budget_group_type = 'R'
		     AND effective_start_date <= sysdate
		     AND ((effective_end_date IS NULL)
			   OR
			  (effective_end_date >= sysdate))
		  START WITH budget_group_id = p_budget_group_id
		  CONNECT BY PRIOR budget_group_id = parent_budget_group_id
	       )
  )
  LOOP

    debug('Budget Revision Account line id '||
				      l_lines_rec.budget_revision_acct_line_id);

Line: 644

    Insert_BR_Lines_Pvt
    ( p_budget_revision_id           => l_new_budget_revision_id,
      p_budget_revision_acct_line_id =>
				     l_lines_rec.budget_revision_acct_line_id,
      p_freeze_flag                  => l_lines_rec.freeze_flag,
      p_view_line_flag               => l_lines_rec.view_line_flag,
      p_last_update_date             => g_current_date,
      p_last_updated_by              => g_current_user_id,
      p_last_update_login            => g_current_login_id,
      p_created_by                   => g_current_user_id,
      p_creation_date                => g_current_date,
      p_return_status                => l_return_status
    ) ;

Line: 684

      SELECT lines.*
      FROM   psb_budget_revision_pos_lines   lines ,
	     psb_budget_revision_positions   pos
      WHERE  lines.budget_revision_id      = p_budget_revision_id
      AND    lines.budget_revision_pos_line_id
					   = pos.budget_revision_pos_line_id
      AND    pos.budget_group_id in
		       (
			 SELECT bg.budget_group_id
			   FROM psb_budget_groups bg
			  WHERE budget_group_type = 'R'
			    AND effective_start_date <= sysdate
			    AND ((effective_end_date IS NULL)
				  OR
				 (effective_end_date >= sysdate))
			 START WITH bg.budget_group_id = p_budget_group_id
			 CONNECT BY PRIOR bg.budget_group_id =
						     bg.parent_budget_group_id
			)
    )
    LOOP
      --
      debug('Budget Revision Position line id '||
				      l_lines_rec.budget_revision_pos_line_id);

Line: 713

      Insert_BR_Pos_Lines_Pvt
      ( p_budget_revision_id           => l_new_budget_revision_id,
	p_budget_revision_pos_line_id =>
				     l_lines_rec.budget_revision_pos_line_id,
	p_freeze_flag                  => l_lines_rec.freeze_flag,
	p_view_line_flag               => l_lines_rec.view_line_flag,
	p_last_update_date             => g_current_date,
	p_last_updated_by              => g_current_user_id,
	p_last_update_login            => g_current_login_id,
	p_created_by                   => g_current_user_id,
	p_creation_date                => g_current_date,
	p_return_status                => l_return_status
      );

Line: 870

      SELECT NVL(freeze_flag, 'N') INTO l_parent_freeze_flag
      FROM   psb_budget_revisions
      WHERE  budget_revision_id = l_parent_budget_revision_id;

Line: 935

  UPDATE psb_budget_revision_lines
  SET    freeze_flag  = p_freeze_flag
  WHERE  budget_revision_id = p_budget_revision_id;

Line: 943

    UPDATE psb_budget_revision_pos_lines
    SET    freeze_flag  = p_freeze_flag
    WHERE  budget_revision_id = p_budget_revision_id;

Line: 1046

  SELECT br.budget_group_id,
	 br.global_budget_revision_id,
	 br.global_budget_revision,
	 bg.parent_budget_group_id
      INTO
	 l_budget_group_id,
	 l_global_budget_revision_id,
	 l_global_budget_revision,
	 l_parent_budget_group_id
  FROM   psb_budget_revisions    br,
	 psb_budget_groups       bg
  WHERE  br.budget_revision_id  = p_budget_revision_id
  AND    br.budget_group_id = bg.budget_group_id ;

Line: 1070

  SELECT budget_group_id INTO l_global_budget_group_id
  FROM   psb_budget_revisions
  WHERE  budget_revision_id = l_global_budget_revision_id;

Line: 1093

    SELECT budget_revision_id INTO p_budget_revision_id_OUT
    FROM   psb_budget_revisions
    WHERE  global_budget_revision_id = l_global_budget_revision_id
    AND    budget_group_id           = l_parent_budget_group_id ;

Line: 1099

    SELECT DISTINCT child_worksheet_id INTO p_budget_revision_id_OUT
    FROM   psb_ws_distribution_details details, psb_ws_distributions distr
    WHERE  distr.worksheet_id               = p_budget_revision_id
    AND    distr.distribution_option_flag   = 'R'
    AND    details.global_worksheet_id      = l_global_budget_revision_id
    AND    details.child_budget_group_id    = l_parent_budget_group_id;

Line: 1215

  p_budget_revision_tbl.DELETE;

Line: 1359

  p_budget_revision_tbl.DELETE ;

Line: 1365

  SELECT budget_group_id                       ,
	 global_budget_revision_id                   ,
	 NVL( global_budget_revision ,  'N' )
    INTO
	 l_budget_group_id                     ,
	 l_global_budget_revision_id                 ,
	 l_global_budget_revision
  FROM   psb_budget_revisions
  WHERE  budget_revision_id = p_budget_revision_id ;

Line: 1386

     SELECT budget_group_id
       FROM psb_budget_groups
      WHERE budget_group_type          = 'R'
	AND effective_start_date <= sysdate
	AND ((effective_end_date IS NULL)
	     OR
	     (effective_end_date >= sysdate))
     START WITH budget_group_id       = l_budget_group_id
     CONNECT BY PRIOR budget_group_id = parent_budget_group_id
  )
  LOOP

    --
    -- The hierarchial query will also return the l_budget_group_id.
    -- Do not consider it.
    --
    IF l_budget_group_rec.budget_group_id <> l_budget_group_id THEN

      l_child_budget_revision_id := NULL ;

Line: 1415

        SELECT budget_revision_id INTO l_child_budget_revision_id
        FROM   psb_budget_revisions
        WHERE  global_budget_revision_id = l_global_budget_revision_id
        AND    budget_group_id           = l_budget_group_rec.budget_group_id ;

Line: 1421

	SELECT child_worksheet_id INTO l_child_budget_revision_id
	FROM   psb_ws_distribution_details details, psb_ws_distributions distr
	WHERE  distr.worksheet_id               = p_budget_revision_id
	AND    distr.distribution_option_flag   = 'R'
	AND    details.global_worksheet_id   = l_global_budget_revision_id
	AND    details.child_budget_group_id =
					l_budget_group_rec.budget_group_id
	AND    ROWNUM < 2 ;

Line: 1495

 |                     PROCEDURE Update_Target_Budget_Revision               |
 +===========================================================================*/
--
-- The API takes 2 budget revisions, source and target. It updates target
-- budget revision by adding new account or position lines if they are their
-- in the source budget revision and not in the target budget revision.
-- It also updates the budget revision submission related columns in
-- the source budget revision.
--
PROCEDURE Update_Target_Budget_Revision
(
  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_source_budget_revision_id IN       NUMBER,
  p_revision_option_flag      IN       VARCHAR2,
  p_target_budget_revision_id IN       NUMBER
)
IS
  --
  l_api_name                           CONSTANT VARCHAR2(30)
						:= 'Update_Target_Budget Revision' ;

Line: 1544

	 SELECT count(*)
	 FROM   psb_budget_revision_pos_lines lines,
		psb_budget_revisions rev
	 WHERE  rev.budget_revision_id = p_source_budget_revision_id
	   AND  rev.budget_revision_id = lines.budget_revision_id;

Line: 1552

  SAVEPOINT Update_Target_Revision_Pvt ;

Line: 1602

  SELECT budget_group_id,
	 global_budget_revision_id,
	 NVL( global_budget_revision,  'N' )
       INTO
	 l_source_budget_group_id,
	 l_source_global_budget_rev_id,
	 l_source_global_budget_rev
  FROM   psb_budget_revisions
  WHERE  budget_revision_id = p_source_budget_revision_id ;

Line: 1621

  SELECT budget_group_id,
	 global_budget_revision_id
      INTO
	 l_target_budget_group_id,
	 l_target_global_budget_rev_id
  FROM   psb_budget_revisions
  WHERE  budget_revision_id = p_target_budget_revision_id ;

Line: 1637

    SELECT lines.budget_revision_acct_line_id
    FROM   psb_budget_revision_lines     lines,
	   psb_budget_revision_accounts  acct
    WHERE  lines.budget_revision_id               = p_source_budget_revision_id
    AND    lines.budget_revision_acct_line_id
						  =
					   acct.budget_revision_acct_line_id
    AND    acct.budget_group_id in
	       (  SELECT budget_group_id
		    FROM psb_budget_groups
		   WHERE budget_group_type = 'R'
		     AND effective_start_date <= sysdate
		     AND ((effective_end_date IS NULL)
			   OR
			  (effective_end_date >= sysdate))
		  START WITH budget_group_id       = l_target_budget_group_id
		  CONNECT BY PRIOR budget_group_id = parent_budget_group_id
	       )
     MINUS
     SELECT lines.budget_revision_acct_line_id
     FROM   psb_budget_revision_lines lines
     WHERE  budget_revision_id = p_target_budget_revision_id
  )
  LOOP

    SELECT * INTO l_br_lines_rec
    FROM   psb_budget_revision_lines
    WHERE  budget_revision_id    = p_source_budget_revision_id
    AND    budget_revision_acct_line_id =
			   l_account_line_id_rec.budget_revision_acct_line_id ;

Line: 1673

    Insert_BR_Lines_Pvt
    (
       p_budget_revision_id           =>  p_target_budget_revision_id,
       p_budget_revision_acct_line_id =>
			  l_br_lines_rec.budget_revision_acct_line_id,
       p_freeze_flag                  => l_br_lines_rec.freeze_flag,
       p_view_line_flag               => l_br_lines_rec.view_line_flag,
       p_last_update_date             => g_current_date,
       p_last_updated_by              => g_current_user_id,
       p_last_update_login            => g_current_login_id,
       p_created_by                   => g_current_user_id,
       p_creation_date                => g_current_date,
       p_return_status                => l_return_status
    ) ;

Line: 1710

      SELECT lines.budget_revision_pos_line_id
      FROM   psb_budget_revision_pos_lines   lines ,
	     psb_budget_revision_positions   pos
      WHERE  lines.budget_revision_id = p_source_budget_revision_id
      AND    lines.budget_revision_pos_line_id
					 = pos.budget_revision_pos_line_id
      AND    pos.budget_group_id in
		     (
		       SELECT bg.budget_group_id
			 FROM psb_budget_groups bg
			WHERE bg.budget_group_type = 'R'
			  AND effective_start_date <= sysdate
			  AND ((effective_end_date IS NULL)
				OR
			       (effective_end_date >= sysdate))
		       START WITH bg.budget_group_id = l_target_budget_group_id
		       CONNECT BY PRIOR bg.budget_group_id =
						   bg.parent_budget_group_id
		      )
       MINUS
       SELECT budget_revision_pos_line_id
       FROM   psb_budget_revision_pos_lines
       WHERE  budget_revision_id = p_target_budget_revision_id
    )
    LOOP

      SELECT * INTO l_br_lines_pos_rec
      FROM   psb_budget_revision_pos_lines
      WHERE  budget_revision_id     = p_source_budget_revision_id
      AND    budget_revision_pos_line_id
			       = l_lines_pos_rec.budget_revision_pos_line_id ;

Line: 1746

      Insert_BR_Pos_Lines_Pvt
      ( p_budget_revision_id           => p_target_budget_revision_id,
	p_budget_revision_pos_line_id =>
			       l_br_lines_pos_rec.budget_revision_pos_line_id,
	p_freeze_flag                  => l_br_lines_pos_rec.freeze_flag,
	p_view_line_flag               => l_br_lines_pos_rec.view_line_flag,
	p_last_update_date             => g_current_date,
	p_last_updated_by              => g_current_user_id,
	p_last_update_login            => g_current_login_id,
	p_created_by                   => g_current_user_id,
	p_creation_date                => g_current_date,
	p_return_status                => l_return_status
      );

Line: 1782

    ROLLBACK TO Update_Target_Revision_Pvt ;

Line: 1789

    ROLLBACK TO Update_Target_Revision_Pvt ;

Line: 1796

    ROLLBACK TO Update_Target_Revision_Pvt ;

Line: 1808

END Update_Target_Budget_Revision ;

Line: 1812

 |                 PROCEDURE Insert_BR_Lines_Pvt ( Private )                 |
 +===========================================================================*/
--
-- The private procedure inserts a new record in psb_ws_lines table.
--
PROCEDURE Insert_BR_Lines_Pvt
(
  p_budget_revision_id            IN  NUMBER,
  p_budget_revision_acct_line_id  IN  NUMBER,
  p_freeze_flag                   IN  VARCHAR2,
  p_view_line_flag                IN  VARCHAR2,
  p_last_update_date              IN  DATE,
  p_last_updated_by               IN  NUMBER,
  p_last_update_login             IN  NUMBER,
  p_created_by                    IN  NUMBER,
  p_creation_date                 IN  DATE,
  p_return_status                 OUT  NOCOPY VARCHAR2
)
IS
  --
  l_api_name                  CONSTANT VARCHAR2(30) := 'Insert_BR_Lines_Pvt' ;

Line: 1838

  update psb_budget_revision_lines
     set freeze_flag       = p_freeze_flag,
	 view_line_flag    = p_view_line_flag,
	 last_update_date  = g_current_date,
	 last_updated_by   = g_current_user_id,
	 last_update_login = g_current_login_id
   where budget_revision_acct_line_id   = p_budget_revision_acct_line_id
     and budget_revision_id      = p_budget_revision_id;

Line: 1849

    INSERT INTO psb_budget_revision_lines
	   (
	     budget_revision_id,
	     budget_revision_acct_line_id,
	     freeze_flag,
	     view_line_flag,
	     last_update_date,
	     last_updated_by,
	     last_update_login,
	     created_by,
	     creation_date
	   )
	 VALUES
	   (
	     p_budget_revision_id,
	     p_budget_revision_acct_line_id,
	     p_freeze_flag,
	     p_view_line_flag,
	     g_current_date,
	     g_current_user_id,
	     g_current_login_id,
	     g_current_user_id,
	     g_current_date
	   );

Line: 1888

END Insert_BR_Lines_Pvt ;

Line: 1892

 |                 PROCEDURE Insert_BR_Pos_Lines_Pvt ( Private )             |
 +===========================================================================*/
--
-- The private procedure inserts a new record in
-- psb_budget_revision_pos_lines table.
--
PROCEDURE Insert_BR_Pos_Lines_Pvt
(
  p_budget_revision_id             IN  NUMBER,
  p_budget_revision_pos_line_id    IN  NUMBER,
  p_freeze_flag                    IN  VARCHAR2,
  p_view_line_flag                 IN  VARCHAR2,
  p_last_update_date               IN  DATE,
  p_last_updated_by                IN  NUMBER,
  p_last_update_login              IN  NUMBER,
  p_created_by                     IN  NUMBER,
  p_creation_date                  IN  DATE,
  p_return_status                  OUT  NOCOPY VARCHAR2
)
IS
  --
  l_api_name                 CONSTANT VARCHAR2(30) := 'Insert_BR_Pos_Lines_Pvt' ;

Line: 1919

  update psb_budget_revision_pos_lines
     set freeze_flag       = p_freeze_flag,
	 view_line_flag    = p_view_line_flag,
	 last_update_date  = g_current_date,
	 last_updated_by   = g_current_user_id,
	 last_update_login = g_current_login_id
   where budget_revision_pos_line_id   = p_budget_revision_pos_line_id
     and budget_revision_id      = p_budget_revision_id;

Line: 1930

    INSERT INTO psb_budget_revision_pos_lines
	   (
	     budget_revision_id,
	     budget_revision_pos_line_id,
	     freeze_flag,
	     view_line_flag,
	     last_update_date,
	     last_updated_by,
	     last_update_login,
	     created_by,
	     creation_date
	   )
	 VALUES
	   (
	     p_budget_revision_id,
	     p_budget_revision_pos_line_id,
	     p_freeze_flag,
	     p_view_line_flag,
	     g_current_date,
	     g_current_user_id,
	     g_current_login_id,
	     g_current_user_id,
	     g_current_date
	   );

Line: 1969

END Insert_BR_Pos_Lines_Pvt ;