APPS.AMW_RISK_PVT SQL Statements

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

Line: 45

      SELECT risk_id
      FROM amw_risks_all_vl
      WHERE name = l_risk_name;

Line: 51

      SELECT count(*)
      FROM amw_risks_b
      GROUP BY risk_id
	  HAVING risk_id=l_risk_id;

Line: 57

      SELECT risk_rev_id,
			 approval_status
      FROM amw_risks_b
	  WHERE risk_id=l_risk_id AND
	  		latest_revision_flag='Y';

Line: 136

			   -- this record is Pending Approval, cannot do G_OP_UPDATE or G_OP_REVISE
			   AMW_UTILITY_PVT.Error_Message(p_message_name => 'AMW_PENDING_CHANGE_ERROR',
                                             p_token_name   => 'OBJ_TYPE',
                                             p_token_value  =>  G_OBJ_TYPE);

Line: 143

		   	   		p_operate_mode 			=> G_OP_UPDATE,
					p_api_version_number    => p_api_version_number,
					p_init_msg_list     	=> p_init_msg_list,
					p_commit     			=> p_commit,
		    		p_validation_level     	=> p_validation_level,
		    		x_return_status     	=> x_return_status,
		    		x_msg_count     		=> x_msg_count,
		    		x_msg_data     			=> x_msg_data,
		    		p_risk_rec     			=> l_dummy_risk_rec,
		    		x_risk_rev_id     		=> x_risk_rev_id,
		    		x_risk_id     			=> x_risk_id);

Line: 285

      SELECT risk_rev_id
      FROM amw_risks_b
      WHERE risk_id = l_risk_id AND approval_status='D' AND latest_revision_flag='Y';

Line: 333

	 ELSIF p_operate_mode = G_OP_UPDATE THEN
 	 	l_dummy_risk_rec := p_risk_rec;

Line: 348

		Update_Risk(
		    p_operate_mode 			=> p_operate_mode,
		    p_api_version_number    => p_api_version_number,
		    p_init_msg_list     	=> p_init_msg_list,
		    p_commit     			=> p_commit,
		    p_validation_level     	=> p_validation_level,
		    x_return_status     	=> x_return_status,
		    x_msg_count     		=> x_msg_count,
		    x_msg_data     			=> x_msg_data,
		    p_risk_rec     			=> l_dummy_risk_rec,
		    x_risk_rev_id     		=> x_risk_rev_id,
		    x_risk_id     			=> x_risk_id);

Line: 389

		   	  Update_Risk(
			      p_operate_mode 		=> p_operate_mode,
				  p_api_version_number 	=> p_api_version_number,
				  p_init_msg_list 		=> p_init_msg_list,
				  p_commit 				=> p_commit,
				  p_validation_level 	=> p_validation_level,
				  x_return_status 		=> x_return_status,
				  x_msg_count 			=> x_msg_count,
				  x_msg_data 			=> x_msg_data,
				  p_risk_rec 			=> l_dummy_risk_rec,
				  x_risk_rev_id 		=> x_risk_rev_id,
				  x_risk_id 			=> x_risk_id);

Line: 434

	 ELSIF p_operate_mode = G_OP_DELETE THEN
		Delete_Risk(
		    p_operate_mode 			=> p_operate_mode,
		    p_api_version_number    => p_api_version_number,
		    p_init_msg_list     	=> p_init_msg_list,
		    p_commit     			=> p_commit,
		    p_validation_level     	=> p_validation_level,
		    x_return_status     	=> x_return_status,
		    x_msg_count     		=> x_msg_count,
		    x_msg_data     			=> x_msg_data,
		    p_risk_rev_id     		=> p_risk_rec.risk_rev_id,
			x_risk_id     			=> x_risk_id);

Line: 540

      SELECT AMW_RISK_REV_ID_S.NEXTVAL
      FROM dual;

Line: 544

      SELECT 1
      FROM AMW_RISKS_B
      WHERE RISK_REV_ID = l_rev_id;

Line: 549

      SELECT AMW_RISK_ID_S.NEXTVAL
      FROM dual;

Line: 553

      SELECT 1
      FROM AMW_RISKS_B
      WHERE RISK_ID = l_id;

Line: 653

	  -- Invoke table handler(AMW_RISKS_PKG.Insert_Row)
	  AMW_UTILITY_PVT.debug_message( 'Private API: Calling AMW_RISKS_PKG.Insert_Row');

Line: 655

      AMW_RISKS_PKG.Insert_Row(
					  x_rowid		 	   			=> l_row_id,
			          x_name 		 	   			=> l_dummy_risk_rec.risk_name,
					  x_description 	   			=> l_dummy_risk_rec.risk_description,
          			  x_risk_id  		   			=> l_dummy_risk_rec.risk_id,
          			  x_last_update_date   			=> SYSDATE,
          			  x_last_update_login  			=> G_LOGIN_ID,
          			  x_created_by  	   			=> G_USER_ID,
					  x_last_updated_by    			=> G_USER_ID,
          			  x_risk_impact  	   			=> l_dummy_risk_rec.risk_impact,
          			  x_likelihood  	   			=> l_dummy_risk_rec.likelihood,
          			  x_attribute_category 			=> l_dummy_risk_rec.attribute_category,
          			  x_attribute1  	   			=> l_dummy_risk_rec.attribute1,
          			  x_attribute2  	   			=> l_dummy_risk_rec.attribute2,
          			  x_attribute3  	   			=> l_dummy_risk_rec.attribute3,
          			  x_attribute4  	   			=> l_dummy_risk_rec.attribute4,
          			  x_attribute5  	   			=> l_dummy_risk_rec.attribute5,
          			  x_attribute6  	   			=> l_dummy_risk_rec.attribute6,
          			  x_attribute7  	   			=> l_dummy_risk_rec.attribute7,
          			  x_attribute8  	  			=> l_dummy_risk_rec.attribute8,
          			  x_attribute9  	   			=> l_dummy_risk_rec.attribute9,
          			  x_attribute10  	   			=> l_dummy_risk_rec.attribute10,
          			  x_attribute11  	   			=> l_dummy_risk_rec.attribute11,
          			  x_attribute12  	   			=> l_dummy_risk_rec.attribute12,
          			  x_attribute13  	   			=> l_dummy_risk_rec.attribute13,
          			  x_attribute14  	   			=> l_dummy_risk_rec.attribute14,
          			  x_attribute15  	   			=> l_dummy_risk_rec.attribute15,
          			  x_security_group_id  			=> l_dummy_risk_rec.security_group_id,
          			  x_risk_type  		   			=> l_dummy_risk_rec.risk_type,
          			  x_approval_status    			=> l_dummy_risk_rec.approval_status,
          			  x_object_version_number  		=> l_object_version_number,
          			  x_approval_date  				=> l_dummy_risk_rec.approval_date,
          			  x_creation_date  				=> SYSDATE,
          			  x_risk_rev_num  				=> l_dummy_risk_rec.risk_rev_num,
          			  x_risk_rev_id  				=> l_dummy_risk_rec.risk_rev_id,
          			  x_requestor_id  				=> l_dummy_risk_rec.requestor_id,
          			  x_orig_system_reference  		=> l_dummy_risk_rec.orig_system_reference,
          			  x_latest_revision_flag  		=> l_dummy_risk_rec.latest_revision_flag,
          			  x_end_date  					=> l_dummy_risk_rec.end_date,
          			  x_curr_approved_flag  		=> l_dummy_risk_rec.curr_approved_flag,
					  X_MATERIAL   					=> l_dummy_risk_rec.material,
                      X_CLASSIFICATION				=> l_dummy_risk_rec.classification);

Line: 771

PROCEDURE Update_Risk(
    p_operate_mode	   			 IN	  VARCHAR2,
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2     := G_FALSE,
    p_commit                     IN   VARCHAR2     := G_FALSE,
    p_validation_level           IN   NUMBER       := G_VALID_LEVEL_FULL,

    x_return_status              OUT  NOCOPY VARCHAR2,
    x_msg_count                  OUT  NOCOPY NUMBER,
    x_msg_data                   OUT  NOCOPY VARCHAR2,

    p_risk_rec               	 IN   risk_rec_type,
    x_risk_rev_id      		 OUT  NOCOPY NUMBER,
    x_risk_id      		 OUT  NOCOPY NUMBER
    )
IS
l_api_name                  CONSTANT VARCHAR2(30) := 'Update_Risk';

Line: 794

      SELECT risk_rev_id
      FROM amw_risks_b
      WHERE risk_id = l_risk_id AND approval_status='D' AND latest_revision_flag='Y';

Line: 800

      SAVEPOINT UPDATE_Risk_PVT;

Line: 823

      AMW_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');

Line: 833

			 AMW_UTILITY_PVT.debug_message('l_risk_rev_id in Update_Risk is NULL');

Line: 872

	  -- Invoke table handler(AMW_RISKS_PKG.Update_Row)
	  AMW_RISKS_PKG.Update_Row(
	  	 	  x_name 			  			 => l_dummy_risk_rec.risk_name,
  	  	  	  x_description 				 => l_dummy_risk_rec.risk_description,
          	  x_risk_id  					 => l_dummy_risk_rec.risk_id,
          	  x_last_update_date  			 => SYSDATE,
          	  x_last_update_login  			 => G_LOGIN_ID,
          	  x_last_updated_by  			 => G_USER_ID,
          	  x_risk_impact  				 => l_dummy_risk_rec.risk_impact,
          	  x_likelihood  				 => l_dummy_risk_rec.likelihood,
          	  x_attribute_category  		 => l_dummy_risk_rec.attribute_category,
          	  x_attribute1  				 => l_dummy_risk_rec.attribute1,
          	  x_attribute2  				 => l_dummy_risk_rec.attribute2,
          	  x_attribute3  				 => l_dummy_risk_rec.attribute3,
          	  x_attribute4  				 => l_dummy_risk_rec.attribute4,
          	  x_attribute5  				 => l_dummy_risk_rec.attribute5,
          	  x_attribute6  				 => l_dummy_risk_rec.attribute6,
          	  x_attribute7  				 => l_dummy_risk_rec.attribute7,
          	  x_attribute8  				 => l_dummy_risk_rec.attribute8,
          	  x_attribute9  				 => l_dummy_risk_rec.attribute9,
          	  x_attribute10  				 => l_dummy_risk_rec.attribute10,
          	  x_attribute11  				 => l_dummy_risk_rec.attribute11,
          	  x_attribute12  				 => l_dummy_risk_rec.attribute12,
          	  x_attribute13  				 => l_dummy_risk_rec.attribute13,
          	  x_attribute14  				 => l_dummy_risk_rec.attribute14,
          	  x_attribute15  				 => l_dummy_risk_rec.attribute15,
          	  x_security_group_id  			 => l_dummy_risk_rec.security_group_id,
          	  x_risk_type  					 => l_dummy_risk_rec.risk_type,
          	  x_approval_status  			 => l_dummy_risk_rec.approval_status,
          	  x_object_version_number  		 => l_dummy_risk_rec.object_version_number,
          	  x_approval_date  				 => l_dummy_risk_rec.approval_date,
          	  x_risk_rev_num  				 => l_dummy_risk_rec.risk_rev_num,
          	  x_risk_rev_id  				 => l_dummy_risk_rec.risk_rev_id,
          	  x_requestor_id  				 => l_dummy_risk_rec.requestor_id,
          	  x_orig_system_reference  		 => l_dummy_risk_rec.orig_system_reference,
          	  x_latest_revision_flag  		 => l_dummy_risk_rec.latest_revision_flag,
          	  x_end_date  					 => l_dummy_risk_rec.end_date,
          	  x_curr_approved_flag  		 => l_dummy_risk_rec.curr_approved_flag,
			  X_MATERIAL					 => l_dummy_risk_rec.material,
              X_CLASSIFICATION  			 => l_dummy_risk_rec.classification);

Line: 934

     ROLLBACK TO UPDATE_Risk_PVT;

Line: 943

     ROLLBACK TO UPDATE_Risk_PVT;

Line: 952

     ROLLBACK TO UPDATE_Risk_PVT;

Line: 964

End Update_Risk;

Line: 975

PROCEDURE Delete_Risk(
    p_operate_mode	   			 IN	  VARCHAR2,
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2     := G_FALSE,
    p_commit                     IN   VARCHAR2     := G_FALSE,
    p_validation_level           IN   NUMBER       := G_VALID_LEVEL_FULL,
    x_return_status              OUT  NOCOPY VARCHAR2,
    x_msg_count                  OUT  NOCOPY NUMBER,
    x_msg_data                   OUT  NOCOPY VARCHAR2,
    p_risk_rev_id                IN   NUMBER,
    x_risk_id      		 OUT  NOCOPY NUMBER
    )
IS
L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_Risk';

Line: 993

      SAVEPOINT DELETE_Risk_PVT;

Line: 1016

      AMW_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');

Line: 1019

      AMW_RISKS_PKG.Delete_Row(
          x_RISK_REV_ID  => p_RISK_REV_ID);

Line: 1044

     ROLLBACK TO DELETE_Risk_PVT;

Line: 1053

     ROLLBACK TO DELETE_Risk_PVT;

Line: 1062

     ROLLBACK TO DELETE_Risk_PVT;

Line: 1074

End Delete_Risk;

Line: 1117

      SELECT risk_rev_id,
	  		 risk_rev_num,
			 object_version_number
      FROM amw_risks_b
      WHERE risk_id = l_risk_id AND ( approval_status='A' OR approval_status='R') AND latest_revision_flag='Y';

Line: 1164

	Update_Risk(
    	p_operate_mode 	  		=> p_operate_mode,
	    p_api_version_number    => p_api_version_number,
	    p_init_msg_list     	=> p_init_msg_list,
	    p_commit     			=> p_commit,
	    p_validation_level     	=> p_validation_level,
	    x_return_status     	=> x_return_status,
	    x_msg_count     		=> x_msg_count,
	    x_msg_data     			=> x_msg_data,
	    p_risk_rec     			=> l_dummy_risk_rec,
	    x_risk_rev_id     		=> x_risk_rev_id,
	    x_risk_id     			=> x_risk_id);

Line: 1574

	  SELECT *
      FROM amw_risks_b
      WHERE risk_rev_id = p_risk_rec.risk_rev_id;

Line: 1581

	  SELECT name,
	  		 description
      FROM amw_risks_all_vl
      WHERE risk_rev_id = p_risk_rec.risk_rev_id;

Line: 1624

   IF p_risk_rec.last_update_date IS NULL THEN
      x_complete_rec.last_update_date := l_risk_rec.last_update_date;

Line: 1629

   IF p_risk_rec.last_update_login IS NULL THEN
      x_complete_rec.last_update_login := l_risk_rec.last_update_login;

Line: 1639

   IF p_risk_rec.last_updated_by IS NULL THEN
      x_complete_rec.last_updated_by := l_risk_rec.last_updated_by;

Line: 1860

	  -- 07.21.2003 tsho, only update and revise need complete_risk_rec
	  IF p_operate_mode = G_OP_UPDATE OR p_operate_mode = G_OP_REVISE THEN
	     Complete_risk_Rec(
      	    p_risk_rec 	   => p_risk_rec,
			x_complete_rec => l_risk_rec);

Line: 1997

      ELSIF p_operate_mode = G_OP_UPDATE THEN
	  	 Validate_update_risk_rec(
		 	x_return_status 	  => x_return_status,
		 	x_msg_count 		  => x_msg_count,
		 	x_msg_data 			  => x_msg_data,
			p_risk_rec 			  => p_risk_rec);

Line: 2023

      ELSIF p_operate_mode = G_OP_DELETE THEN
	  	 Validate_delete_risk_rec(
		 	x_return_status 	  => x_return_status,
		 	x_msg_count 		  => x_msg_count,
		 	x_msg_data 			  => x_msg_data,
			p_risk_rec 			  => p_risk_rec);

Line: 2075

      SELECT 1
      FROM amw_risks_all_vl
      WHERE name = l_risk_name;

Line: 2149

PROCEDURE Validate_update_risk_rec(
    x_return_status              OUT  NOCOPY VARCHAR2,
    x_msg_count                  OUT  NOCOPY NUMBER,
    x_msg_data                   OUT  NOCOPY VARCHAR2,
    p_risk_rec               	 IN   risk_rec_type
    )
IS
l_api_name 						 CONSTANT VARCHAR2(30) := 'Validate_Update_Risk_Rec';

Line: 2161

      SELECT approval_status
      FROM amw_risks_b
      WHERE risk_rev_id = l_risk_rev_id;

Line: 2167

      SELECT 1
      FROM amw_risks_all_vl
      WHERE name = l_risk_name AND risk_id <> l_risk_id;

Line: 2176

	  -- only approval_status='D' can be updated
	  OPEN c_target_risk(p_risk_rec.risk_rev_id);

Line: 2239

END Validate_update_risk_Rec;

Line: 2263

      SELECT approval_status
      FROM amw_risks_b
      WHERE risk_rev_id = l_risk_rev_id;

Line: 2269

      SELECT name
      FROM amw_risks_all_vl
      WHERE risk_rev_id = l_risk_rev_id;

Line: 2344

PROCEDURE Validate_delete_risk_rec(
    x_return_status              OUT  NOCOPY VARCHAR2,
    x_msg_count                  OUT  NOCOPY NUMBER,
    x_msg_data                   OUT  NOCOPY VARCHAR2,
    p_risk_rec               	 IN   risk_rec_type
    )
IS
l_api_name 						 CONSTANT VARCHAR2(30) := 'Validate_Delete_Risk_Rec';

Line: 2355

      SELECT 1
      FROM amw_risks_b
      WHERE risk_rev_id = l_risk_rev_id;

Line: 2364

	  -- can only delete a risk which exists and has APPROVAL_STATUS='''
      l_dummy := NULL;

Line: 2417

END Validate_delete_risk_Rec;

Line: 2443

      SELECT risk_rev_id
        FROM amw_risks_b
       WHERE risk_id = (
                 SELECT r.risk_id
                   FROM amw_risks_b r
                  WHERE r.risk_rev_id = l_risk_rev_id
             )
         AND curr_approved_flag='Y';

Line: 2474

      update amw_risks_b
         set curr_approved_flag='N'
            ,latest_revision_flag ='N'
	        ,last_update_date=l_date
		    ,last_updated_by=G_USER_ID
		    ,last_update_login=G_LOGIN_ID
            ,end_date=l_date
       where risk_rev_id = l_target_risk_rev_id;

Line: 2485

   update amw_risks_b
      set approval_status='A'
         ,curr_approved_flag='Y'
         ,latest_revision_flag ='Y'
         ,approval_date=l_date
	     ,last_update_date=l_date
		 ,last_updated_by=G_USER_ID
		 ,last_update_login=G_LOGIN_ID
    where risk_rev_id=p_risk_rev_id;