DBA Data[Home] [Help]

APPS.PSB_WS_DISTRIBUTION_RULES_PVT SQL Statements

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

Line: 16

 |                       PROCEDURE Insert_Row                            |
 +=======================================================================*/

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_Row_Id                    IN OUT  NOCOPY   VARCHAR2,
  --
  p_Distribution_Rule_Line_Id IN       NUMBER,
  p_Distribution_Rule_Id      IN       NUMBER,
  p_Budget_Group_Id           IN       NUMBER,
  p_distribute_flag           IN       VARCHAR2,
  p_distribute_all_level_flag IN       VARCHAR2,
  p_download_flag             IN       VARCHAR2,
  p_download_all_level_flag   IN       VARCHAR2,
  p_year_category_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

)
IS

  CURSOR C IS
    SELECT rowid
    FROM   psb_ws_distribution_rule_lines
    WHERE  distribution_rule_line_id = p_distribution_rule_line_id ;
Line: 60

    P_LAST_UPDATE_DATE DATE;
Line: 61

    P_LAST_UPDATED_BY NUMBER;
Line: 62

    P_LAST_UPDATE_LOGIN NUMBER;
Line: 64

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

  SAVEPOINT Insert_Row_Pvt ;
Line: 87

  P_LAST_UPDATE_DATE := SYSDATE;
Line: 89

    P_LAST_UPDATED_BY := 1;
Line: 90

    P_LAST_UPDATE_LOGIN := 0;
Line: 92

    P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
Line: 93

    if P_LAST_UPDATED_BY is NULL then
      P_LAST_UPDATED_BY := -1;
Line: 96

    P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
Line: 97

    if P_LAST_UPDATE_LOGIN is NULL then
      P_LAST_UPDATE_LOGIN := -1;
Line: 107

  INSERT INTO psb_ws_distribution_rule_lines
	 (    distribution_rule_line_id,
	      distribution_rule_id,
	      budget_group_id,
	      distribute_flag,
	      distribute_all_level_flag,
	      download_flag ,
	      download_all_level_flag ,
	      year_category_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_distribution_rule_line_id,
	      p_Distribution_rule_id,
	      p_budget_group_id,
	      p_distribute_flag,
	      p_distribute_all_level_flag,
	      p_download_flag ,
	      p_download_all_level_flag ,
	      p_year_category_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: 182

    ROLLBACK TO Insert_Row_Pvt ;
Line: 189

    ROLLBACK TO Insert_Row_Pvt ;
Line: 196

    ROLLBACK TO Insert_Row_Pvt ;
Line: 207

END Insert_Row;
Line: 257

       SELECT distribution_rule_line_id,
	      distribution_rule_id,
	      budget_group_id,
	      distribute_flag,
	      distribute_all_level_flag,
	      download_flag,
	      download_all_level_flag ,
	      year_category_type,
      attribute1,
      attribute2,
      attribute3,
      attribute4,
      attribute5,
      attribute6,
      attribute7,
      attribute8,
      attribute9,
      attribute10,
      context
       FROM   psb_ws_distribution_rule_lines
       WHERE  distribution_rule_line_id = p_distribution_rule_line_id
       FOR UPDATE of distribution_rule_id NOWAIT;
Line: 306

    FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
Line: 424

 |                       PROCEDURE Update_Row                               |
 +==========================================================================*/

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_Distribution_Rule_Line_Id IN       NUMBER,
  p_Distribution_Rule_Id      IN       NUMBER,
  p_Budget_Group_Id           IN       NUMBER,
  p_distribute_flag           IN       VARCHAR2,
  p_distribute_all_level_flag IN       VARCHAR2,
  p_download_flag             IN       VARCHAR2,
  p_download_all_level_flag   IN       VARCHAR2,
  p_year_category_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

)
IS
    P_LAST_UPDATE_DATE DATE;
Line: 461

    P_LAST_UPDATED_BY NUMBER;
Line: 462

    P_LAST_UPDATE_LOGIN NUMBER;
Line: 464

  l_api_name            CONSTANT VARCHAR2(30)   := 'Update_Row';
Line: 470

  SAVEPOINT Update_Row_Pvt ;
Line: 488

  P_LAST_UPDATE_DATE := SYSDATE;
Line: 490

    P_LAST_UPDATED_BY := 1;
Line: 491

    P_LAST_UPDATE_LOGIN := 0;
Line: 493

    P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
Line: 494

    if P_LAST_UPDATED_BY is NULL then
      P_LAST_UPDATED_BY := -1;
Line: 497

    P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
Line: 498

    if P_LAST_UPDATE_LOGIN is NULL then
      P_LAST_UPDATE_LOGIN := -1;
Line: 507

  UPDATE psb_ws_distribution_rule_lines
  SET
	distribution_rule_id   =  p_distribution_rule_id,
	Budget_Group_Id        =  p_Budget_Group_Id      ,
	distribute_flag        = p_distribute_flag            ,
	distribute_all_level_flag = p_distribute_all_level_flag  ,
	download_flag          = p_download_flag              ,
	download_all_level_flag   = p_download_all_level_flag ,
	year_category_type    =   p_year_category_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 distribution_rule_line_id = p_distribution_rule_line_id;
Line: 552

    ROLLBACK TO Update_Row_Pvt ;
Line: 559

    ROLLBACK TO Update_Row_Pvt ;
Line: 566

    ROLLBACK TO Update_Row_Pvt ;
Line: 577

END Update_Row;
Line: 584

 |                       PROCEDURE Delete_Row                               |
 +==========================================================================*/

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_Distribution_Rule_Line_Id IN       NUMBER
)
IS
  --
  l_api_name            CONSTANT VARCHAR2(30)   := 'Delete_Row';
Line: 606

  SAVEPOINT Delete_Row_Pvt ;
Line: 627

  DELETE psb_ws_distribution_rule_lines
  WHERE  distribution_rule_line_id  = p_distribution_rule_line_id;
Line: 646

    ROLLBACK TO Delete_Row_Pvt ;
Line: 653

    ROLLBACK TO Delete_Row_Pvt ;
Line: 660

    ROLLBACK TO Delete_Row_Pvt ;
Line: 671

END Delete_Row;
Line: 704

    SELECT '1'
    FROM   psb_ws_distribution_rules
    WHERE  name = p_name
    AND    ( (p_Row_Id IS NULL)
	      OR (RowId <> p_Row_Id) );
Line: 784

PROCEDURE Distribution_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_Row_Id                    IN OUT  NOCOPY   VARCHAR2,
  --
  p_Distribution_Id           IN       NUMBER,
  p_Distribution_Rule_Id      IN       NUMBER,
  p_Worksheet_Id              IN       NUMBER,
  p_distribution_date         IN       DATE,
  p_distributed_flag          IN       VARCHAR2,
  p_distribution_instructions IN       VARCHAR2,
  p_distribution_option_flag  IN       VARCHAR2,
  p_revision_option_flag      IN       VARCHAR2,
  p_mode                      IN       VARCHAR2
)
IS

  CURSOR C IS
    SELECT rowid
    FROM   psb_ws_distributions
    WHERE  distribution_id = p_distribution_id ;
Line: 813

    P_LAST_UPDATE_DATE DATE;
Line: 814

    P_LAST_UPDATED_BY NUMBER;
Line: 815

    P_LAST_UPDATE_LOGIN NUMBER;
Line: 817

  l_api_name            CONSTANT VARCHAR2(30)   := 'Distribution_Insert_Row';
Line: 823

  SAVEPOINT Distribution_Insert_Row_Pvt ;
Line: 840

  P_LAST_UPDATE_DATE := SYSDATE;
Line: 842

    P_LAST_UPDATED_BY := 1;
Line: 843

    P_LAST_UPDATE_LOGIN := 0;
Line: 845

    P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
Line: 846

    if P_LAST_UPDATED_BY is NULL then
      P_LAST_UPDATED_BY := -1;
Line: 849

    P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
Line: 850

    if P_LAST_UPDATE_LOGIN is NULL then
      P_LAST_UPDATE_LOGIN := -1;
Line: 860

  INSERT INTO psb_ws_distributions
	 (    distribution_id,
	      distribution_rule_id,
	      worksheet_id,
	      distribution_date,
	      distributed_flag,
	      distribution_instructions,
	      distribution_option_flag,
	      revision_option_flag,
	      creation_date,
	      created_by,
	      last_update_date,
	      last_updated_by,
	      last_update_login
	 )
	 VALUES
	 (    p_distribution_id,
	      p_Distribution_rule_id,
	      p_worksheet_id,
	      p_distribution_date,
	      p_distributed_flag,
	      p_distribution_instructions,
	      p_distribution_option_flag,
	      p_revision_option_flag,
	      p_last_update_date,
	      p_last_updated_by,
	      p_last_update_date,
	      p_last_updated_by,
	      p_last_update_login

	 );
Line: 912

    ROLLBACK TO Distribution_Insert_Row_Pvt ;
Line: 919

    ROLLBACK TO Distribution_Insert_Row_Pvt ;
Line: 926

    ROLLBACK TO Distribution_Insert_Row_Pvt ;
Line: 937

END Distribution_Insert_Row;
Line: 940

PROCEDURE Rules_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_Row_Id                    IN OUT  NOCOPY   VARCHAR2,
  --
  p_Distribution_Rule_Id      IN        NUMBER,
  p_Budget_Group_Id           IN        NUMBER,
  p_Name                      IN       VARCHAR2,
  p_mode                      in varchar2

)
IS

  CURSOR C IS
    SELECT rowid
    FROM   psb_ws_distribution_rules
    WHERE  distribution_rule_id = p_distribution_rule_id ;
Line: 965

    P_LAST_UPDATE_DATE DATE;
Line: 966

    P_LAST_UPDATED_BY NUMBER;
Line: 967

    P_LAST_UPDATE_LOGIN NUMBER;
Line: 969

  l_api_name            CONSTANT VARCHAR2(30)   := 'Rules_Insert_Row';
Line: 975

  SAVEPOINT Rules_Insert_Row_Pvt ;
Line: 992

  P_LAST_UPDATE_DATE := SYSDATE;
Line: 994

    P_LAST_UPDATED_BY := 1;
Line: 995

    P_LAST_UPDATE_LOGIN := 0;
Line: 997

    P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
Line: 998

    if P_LAST_UPDATED_BY is NULL then
      P_LAST_UPDATED_BY := -1;
Line: 1001

    P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
Line: 1002

    if P_LAST_UPDATE_LOGIN is NULL then
      P_LAST_UPDATE_LOGIN := -1;
Line: 1012

  INSERT INTO psb_ws_distribution_rules
	 (    distribution_rule_id,
	      name,
	      budget_group_id,
	      creation_date,
	      created_by,
	      last_update_date,
	      last_updated_by,
	      last_update_login
	 )
	 VALUES
	 (    p_distribution_rule_id,
	      p_Name,
	      p_budget_group_id,
	      p_last_update_date,
	      p_last_updated_by,
	      p_last_update_date,
	      p_last_updated_by,
	      p_last_update_login

	 );
Line: 1054

    ROLLBACK TO Rules_Insert_Row_Pvt ;
Line: 1061

    ROLLBACK TO Rules_Insert_Row_Pvt ;
Line: 1068

    ROLLBACK TO Rules_Insert_Row_Pvt ;
Line: 1079

END Rules_Insert_Row;
Line: 1081

 |                       PROCEDURE Delete_Row                               |
 +==========================================================================*/

PROCEDURE Rules_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_Distribution_Rule_Id      IN       NUMBER
)
IS
  --
  l_api_name            CONSTANT VARCHAR2(30)   := 'Delete_Row';
Line: 1104

  SAVEPOINT Rules_Delete_Row_Pvt ;
Line: 1122

  SELECT count(*) INTO l_ws_count
    FROM psb_ws_distributions
   WHERE distribution_rule_id = p_distribution_rule_id;
Line: 1134

  DELETE psb_ws_distribution_rules
    WHERE  distribution_rule_id  = p_distribution_rule_id;
Line: 1137

  DELETE psb_ws_distribution_rule_lines
    WHERE distribution_rule_id  = p_distribution_rule_id;
Line: 1156

    ROLLBACK TO Rules_Delete_Row_Pvt ;
Line: 1163

    ROLLBACK TO Rules_Delete_Row_Pvt ;
Line: 1170

    ROLLBACK TO Rules_Delete_Row_Pvt ;
Line: 1181

END Rules_Delete_Row;
Line: 1208

    SELECT budget_group_id ,
	   distribute_flag ,
	   distribute_all_level_flag ,
	   download_flag ,
	   download_all_level_flag ,
	   year_category_type ,
	   attribute1 ,
	   attribute2 ,
	   attribute3 ,
	   attribute4 ,
	   attribute5 ,
	   attribute6 ,
	   attribute7 ,
	   attribute8 ,
	   attribute9 ,
	   attribute10 ,
	   context
    FROM psb_ws_distribution_rule_lines
   WHERE distribution_rule_id = p_Source_Distribution_Rule_Id ;
Line: 1254

  SELECT psb_ws_distribution_rules_s.NEXTVAL
    INTO l_dist_rule_id FROM dual;
Line: 1258

  RULES_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_row_id                    => l_rowid,
     p_distribution_rule_id     => l_dist_rule_id,
     p_name                     => p_target_rule_name,
     p_budget_group_id          => p_Source_Budget_Group ,
     p_mode                      => 'R'
    );
Line: 1279

     SELECT psb_ws_distribute_rule_lines_s.NEXTVAL
       INTO l_dist_rule_line_id FROM dual;
Line: 1282

     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_row_id                    => l_rowid,
      p_distribution_rule_line_id => l_dist_rule_line_id,
      p_distribution_rule_id      => l_dist_rule_id,
      p_budget_group_id           =>  lines_rec.budget_group_id,
      p_distribute_flag           => lines_rec.distribute_flag,
      p_distribute_all_level_flag => lines_rec.distribute_all_level_flag,
      p_download_flag             => lines_rec.download_flag  ,
      p_download_all_level_flag   => lines_rec.download_all_level_flag  ,
      p_year_category_type        => lines_rec.year_category_type ,
      p_attribute1                => lines_rec.attribute1,
      p_attribute2                => lines_rec.attribute2,
      p_attribute3                => lines_rec.attribute3,
      p_attribute4                => lines_rec.attribute4,
      p_attribute5                => lines_rec.attribute5,
      p_attribute6                => lines_rec.attribute6,
      p_attribute7                => lines_rec.attribute7,
      p_attribute8                => lines_rec.attribute8,
      p_attribute9                => lines_rec.attribute9,
      p_attribute10               => lines_rec.attribute10,
      p_context                   => lines_rec.context,
      p_mode                      => 'R'
    );