APPS.JTF_PERZ_QUERY_PVT SQL Statements

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

Line: 44

PROCEDURE update_jtf_perz_query(
	p_QUERY_ID    	NUMBER,
     p_PROFILE_ID    	NUMBER,
     p_APPLICATION_ID    	NUMBER,
     p_QUERY_NAME    	VARCHAR2,
	p_QUERY_TYPE    	VARCHAR2,
     p_QUERY_DESCRIPTION   VARCHAR2,
     p_QUERY_DATA_SOURCE   VARCHAR2,
	p_OBJECT_VERSION_NUMBER IN	NUMBER)

 IS
 BEGIN
    UPDATE JTF_PERZ_QUERY
    SET
	QUERY_TYPE =
		DECODE( p_QUERY_TYPE, Fnd_Api.G_MISS_CHAR, QUERY_TYPE, p_QUERY_TYPE),
    QUERY_DESCRIPTION =
    	DECODE( p_QUERY_DESCRIPTION, Fnd_Api.G_MISS_CHAR, QUERY_DESCRIPTION,
			p_QUERY_DESCRIPTION),
    QUERY_DATA_SOURCE =
		DECODE( p_QUERY_DATA_SOURCE, Fnd_Api.G_MISS_CHAR, QUERY_DATA_SOURCE,
			p_QUERY_DATA_SOURCE),
	OBJECT_VERSION_NUMBER =
		DECODE (p_OBJECT_VERSION_NUMBER, Fnd_Api.G_MISS_NUM,
					OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER+1),
	LAST_UPDATE_DATE = SYSDATE,
	LAST_UPDATED_BY  = Fnd_Global.USER_ID,
	LAST_UPDATE_LOGIN = Fnd_Global.CONC_LOGIN_ID
    WHERE QUERY_ID = p_QUERY_ID
	AND OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER;

Line: 79

END update_jtf_perz_query;

Line: 84

PROCEDURE insert_jtf_perz_query(
        x_Rowid                 OUT NOCOPY /* file.sql.39 change */     VARCHAR2,
        X_QUERY_ID           IN OUT NOCOPY /* file.sql.39 change */     NUMBER,
        x_PROFILE_ID                    NUMBER,
        x_APPLICATION_ID                NUMBER,
        x_QUERY_NAME                    VARCHAR2,
	X_QUERY_TYPE                    VARCHAR2,
        X_QUERY_DESCRIPTION             VARCHAR2,
        X_QUERY_DATA_SOURCE             VARCHAR2
 ) IS
   CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY
            WHERE QUERY_ID = x_QUERY_ID;

Line: 97

   CURSOR C2 IS SELECT JTF_PERZ_QUERY_S.NEXTVAL FROM sys.dual;

Line: 106

   INSERT INTO JTF_PERZ_QUERY(
     QUERY_ID,
     PROFILE_ID,
     APPLICATION_ID,
     QUERY_NAME,
	QUERY_TYPE,
     QUERY_DESCRIPTION,
     QUERY_DATA_SOURCE,
	OBJECT_VERSION_NUMBER,
	CREATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN
   ) VALUES
   (
	x_QUERY_ID,
	DECODE( x_PROFILE_ID, Fnd_Api.G_MISS_NUM, NULL ,x_PROFILE_ID ),
	DECODE( x_APPLICATION_ID, Fnd_Api.G_MISS_NUM, NULL ,x_APPLICATION_ID ),
	DECODE( x_QUERY_NAME, Fnd_Api.G_MISS_CHAR, NULL ,x_QUERY_NAME ),
	DECODE( x_QUERY_TYPE, Fnd_Api.G_MISS_CHAR, NULL ,x_QUERY_TYPE ),
	DECODE( x_QUERY_DESCRIPTION, Fnd_Api.G_MISS_CHAR, NULL ,x_QUERY_DESCRIPTION ),
	DECODE( x_QUERY_DATA_SOURCE, Fnd_Api.G_MISS_CHAR, NULL ,x_QUERY_DATA_SOURCE ),
	   1, G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID);

Line: 135

END insert_jtf_perz_query;

Line: 141

PROCEDURE insert_jtf_perz_query_order_by(
                  X_ROWID                 OUT NOCOPY /* file.sql.39 change */     VARCHAR2,
                  X_QUERY_ORDER_BY_ID     IN OUT NOCOPY /* file.sql.39 change */        NUMBER,
                  X_QUERY_ID                      NUMBER,
                  X_PARAMETER_NAME                VARCHAR2,
                  X_ACND_DCND_FLAG                VARCHAR2,
                  X_PARAMETER_SEQUENCE            NUMBER
 ) IS
   CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_ORDER_BY
            WHERE QUERY_ORDER_BY_ID = x_QUERY_ORDER_BY_ID;

Line: 152

   CURSOR C2 IS SELECT JTF_PERZ_QUERY_ORDER_BY_s.NEXTVAL FROM sys.dual;

Line: 160

   INSERT INTO JTF_PERZ_QUERY_ORDER_BY(
	QUERY_ORDER_BY_ID,
	QUERY_ID,
	PARAMETER_NAME,
	ACND_DCND_FLAG,
	PARAMETER_SEQUENCE,
	CREATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN
   ) VALUES (
	x_QUERY_ORDER_BY_ID,
	x_QUERY_ID,
	DECODE( x_PARAMETER_NAME, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_NAME ),
	DECODE( x_ACND_DCND_FLAG, Fnd_Api.G_MISS_CHAR, NULL ,x_ACND_DCND_FLAG ),
	DECODE( x_PARAMETER_SEQUENCE, Fnd_Api.G_MISS_NUM, NULL ,x_PARAMETER_SEQUENCE ),
	G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID
  );

Line: 184

END insert_jtf_perz_query_order_by;

Line: 190

PROCEDURE insert_jtf_perz_query_raw_sql(
          x_Rowid                 OUT NOCOPY /* file.sql.39 change */     VARCHAR2,
          X_Query_Raw_Sql_ID      IN OUT NOCOPY /* file.sql.39 change */        NUMBER,
          x_Query_ID                      NUMBER,
          x_Select_String                 VARCHAR2,
	X_From_String                   VARCHAR2,
        X_Where_String                  VARCHAR2,
        X_Order_by_String               VARCHAR2,
        X_Group_by_String               VARCHAR2,
        X_Having_String                 VARCHAR2
 ) IS
   CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_RAW_SQL
            WHERE Query_Raw_Sql_ID = X_Query_Raw_Sql_ID;

Line: 203

   CURSOR C2 IS SELECT JTF_PERZ_QUERY_RAW_SQL_s.NEXTVAL FROM sys.dual;

Line: 212

   INSERT INTO JTF_PERZ_QUERY_RAW_SQL(
	QUERY_RAW_SQL_ID,
        QUERY_ID,
        SELECT_STRING,
	FROM_STRING,
        WHERE_STRING,
        ORDER_BY_STRING,
        GROUP_BY_STRING,
        HAVING_STRING,
	CREATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN
   ) VALUES (
        x_QUERY_RAW_SQL_ID,
        x_QUERY_ID,
        DECODE( x_SELECT_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_SELECT_STRING ),
        DECODE( x_FROM_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_FROM_STRING ),
        DECODE( x_WHERE_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_WHERE_STRING ),
        DECODE( x_ORDER_BY_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_ORDER_BY_STRING ),
	DECODE( x_GROUP_BY_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_GROUP_BY_STRING ),
        DECODE( x_HAVING_STRING, Fnd_Api.G_MISS_CHAR, NULL ,x_HAVING_STRING ),
	G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID
   );

Line: 242

END insert_jtf_perz_query_raw_sql;

Line: 245

PROCEDURE insert_jtf_perz_query_param(
	x_Rowid                 OUT NOCOPY /* file.sql.39 change */     VARCHAR2,
	X_query_param_id      IN OUT NOCOPY /* file.sql.39 change */        NUMBER,
	x_Query_ID                      NUMBER,
	x_Parameter_Name                 VARCHAR2,
	X_Parameter_Type                   VARCHAR2,
	X_Parameter_Value                  VARCHAR2,
	X_Parameter_condition               VARCHAR2,
	X_Parameter_sequence               VARCHAR2
 ) IS
   CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_PARAM
            WHERE query_param_id = X_query_param_id;

Line: 257

   CURSOR C2 IS SELECT JTF_PERZ_QUERY_PARAM_s.NEXTVAL FROM sys.dual;

Line: 265

   INSERT INTO JTF_PERZ_QUERY_PARAM(
	QUERY_PARAM_ID,
	QUERY_ID,
	PARAMETER_NAME,
	PARAMETER_TYPE,
	PARAMETER_VALUE,
	PARAMETER_CONDITION,
	PARAMETER_SEQUENCE,
	CREATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN
   ) VALUES (
	x_QUERY_PARAM_ID,
	x_QUERY_ID,
	DECODE( x_PARAMETER_NAME, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_NAME ),
	DECODE( x_PARAMETER_TYPE, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_TYPE ),
	DECODE( x_PARAMETER_VALUE, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_VALUE ),
	DECODE( x_PARAMETER_CONDITION, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_CONDITION ),
	DECODE( x_PARAMETER_SEQUENCE, Fnd_Api.G_MISS_CHAR, NULL ,x_PARAMETER_SEQUENCE ),
	G_USER_ID, SYSDATE, G_USER_ID, G_LOGIN_ID
   );

Line: 293

END insert_jtf_perz_query_param;

Line: 333

	SELECT query_id, object_version_number
	 INTO x_query_id , x_object_version_number
	FROM  JTF_PERZ_QUERY
	WHERE query_name = p_query_name AND
	      query_type = p_query_type AND
	      application_id = p_application_id AND
	      profile_id = p_profile_id;

Line: 517

	insert_jtf_perz_query(
		l_rowid,
		l_query_id,
        p_profile_id,
		p_application_id,
		p_query_name,
		p_query_type,
		p_query_desc,
		p_query_data_source
	);

Line: 539

	insert_jtf_perz_query_order_by(
		l_rowid,
	  	l_query_Order_By_ID,
		l_query_id,
		p_query_order_by_tbl(l_curr_row).Parameter_Name,
		p_query_order_by_tbl(l_curr_row).Acnd_Dcnd_Flag,
		p_query_order_by_tbl(l_curr_row).Parameter_sequence
	);

Line: 561

	insert_jtf_perz_query_param(
		l_rowid,
		l_query_param_id,
		l_query_id,
		p_query_param_tbl(l_curr_row).Parameter_Name,
		p_query_param_tbl(l_curr_row).Parameter_Type,
		p_query_param_tbl(l_curr_row).Parameter_Value,
		p_query_param_tbl(l_curr_row).Parameter_condition,
		p_query_param_tbl(l_curr_row).Parameter_sequence
	);

Line: 576

   IF (p_query_raw_sql_rec.Select_String IS NOT NULL) THEN

	l_rowid := NULL;

Line: 581

	insert_jtf_perz_query_raw_sql(
		l_rowid,
		l_Query_Raw_Sql_ID,
		l_query_id,
		p_query_raw_sql_rec.Select_String,
		p_query_raw_sql_rec.From_String,
		p_query_raw_sql_rec.Where_String,
		p_query_raw_sql_rec.Order_by_String,
		p_query_raw_sql_rec.Group_by_String,
		p_query_raw_sql_rec.Having_String
	);

Line: 646

PROCEDURE Update_Perz_Query
(	p_api_version_number	IN	NUMBER,
  	p_init_msg_list		IN	VARCHAR2 	:= Fnd_Api.G_FALSE,
	p_commit		IN VARCHAR2		:= Fnd_Api.G_FALSE,

	p_application_id	IN NUMBER,
	p_profile_id        IN NUMBER,

	p_query_id           IN NUMBER,
	p_query_name         IN VARCHAR2,
	p_query_type         IN VARCHAR2,
	p_query_desc		 IN VARCHAR2,
	p_query_data_source  IN VARCHAR2,

	p_query_param_tbl	 IN Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
				 := Jtf_Perz_Query_Pub.G_MISS_QUERY_PARAMETER_TBL,
    p_query_order_by_tbl IN Jtf_Perz_Query_Pub.QUERY_ORDER_BY_TBL_TYPE
				:= Jtf_Perz_Query_Pub.G_MISS_QUERY_ORDER_BY_TBL,
    p_query_raw_sql_rec	 IN Jtf_Perz_Query_Pub.QUERY_RAW_SQL_REC_TYPE ,

	x_query_id          OUT NOCOPY /* file.sql.39 change */ NUMBER,
	x_return_status	 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
	x_msg_count	 OUT NOCOPY /* file.sql.39 change */ NUMBER,
	x_msg_data	 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)IS

	l_query_param_tbl	  Jtf_Perz_Query_Pub.QUERY_PARAMETER_TBL_TYPE
				:= p_query_param_tbl;

Line: 682

	l_api_name		CONSTANT VARCHAR2(30)	:= 'Update Perz Query';

Line: 709

      SAVEPOINT	UPDATE_PERZ_QUERY_PVT;

Line: 751

	-- 2. Delete all parmeter table entries for this query_id

   	DELETE  FROM JTF_PERZ_QUERY_PARAM WHERE QUERY_ID = l_query_id;

Line: 755

	-- 3. Delete all order by table entries for this query_id
   	DELETE  FROM JTF_PERZ_QUERY_ORDER_BY WHERE QUERY_ID = l_query_id;

Line: 758

	-- 4. Delete all raw sql table entries for this query_id
   	DELETE  FROM JTF_PERZ_QUERY_RAW_SQL WHERE QUERY_ID = l_query_id;

Line: 761

	-- 5. Update query header information
   	-- **** UPDATE CALL HERE
		update_jtf_perz_query(
          		l_QUERY_ID,
          		p_PROFILE_ID,
          		p_APPLICATION_ID,
          		p_QUERY_NAME,
          		p_QUERY_TYPE,
          		p_QUERY_DESC,
          		p_QUERY_DATA_SOURCE,
			l_object_version_number);

Line: 773

	-- 6. Insert new data into the three different tables

   	IF (p_query_order_by_tbl.COUNT > 0) THEN
      		FOR l_curr_row IN 1..p_query_order_by_tbl.COUNT LOOP

			l_rowid := NULL;

Line: 781

	  		insert_jtf_perz_query_order_by(l_rowid,
                		l_Query_Order_By_ID,
                		l_query_id,
                		p_query_order_by_tbl(l_curr_row).Parameter_Name,
                		p_query_order_by_tbl(l_curr_row).Acnd_Dcnd_Flag,
                		p_query_order_by_tbl(l_curr_row).Parameter_sequence );

Line: 796

	  		insert_jtf_perz_query_param(
				l_rowid,
                		l_query_param_id,
                		l_query_id,
                		p_query_param_tbl(l_curr_row).Parameter_Name,
                		p_query_param_tbl(l_curr_row).Parameter_Type,
                		p_query_param_tbl(l_curr_row).Parameter_Value,
                		p_query_param_tbl(l_curr_row).Parameter_condition,
                		p_query_param_tbl(l_curr_row).Parameter_sequence );

Line: 808

	IF (p_query_raw_sql_rec.Select_String IS NOT NULL) THEN
		l_rowid := NULL;

Line: 811

	  	insert_jtf_perz_query_raw_sql(
			l_rowid,
			l_Query_Raw_Sql_ID,
			l_query_id,
			p_query_raw_sql_rec.Select_String,
			p_query_raw_sql_rec.From_String,
			p_query_raw_sql_rec.Where_String,
			p_query_raw_sql_rec.Order_by_String,
			p_query_raw_sql_rec.Group_by_String,
			p_query_raw_sql_rec.Having_String
		);

Line: 835

	  ROLLBACK TO UPDATE_PERZ_QUERY_PVT;

Line: 845

	  ROLLBACK TO UPDATE_PERZ_QUERY_PVT;

Line: 854

	  ROLLBACK TO UPDATE_PERZ_QUERY_PVT;

Line: 867

END update_perz_query;

Line: 869

PROCEDURE Delete_Perz_Query
(	p_api_version_number	IN	NUMBER,
  	p_init_msg_list		IN	VARCHAR2 	:= Fnd_Api.G_FALSE,
	p_commit				IN VARCHAR2		:= Fnd_Api.G_FALSE,

	p_application_id	IN NUMBER,
	p_profile_id        IN NUMBER,
	p_query_id            IN NUMBER,

	x_return_status	 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
	x_msg_count	 OUT NOCOPY /* file.sql.39 change */ NUMBER,
	x_msg_data	 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)IS

  	 l_query_id		NUMBER;

Line: 884

     l_api_name		 	CONSTANT VARCHAR2(30)	:= 'Delete Profile';

Line: 887

      SAVEPOINT	DELETE_PERZ_QUERY_PVT;

Line: 929

   DELETE  FROM JTF_PERZ_QUERY_PARAM WHERE QUERY_ID = l_query_id;

Line: 932

	DELETE  FROM JTF_PERZ_QUERY_ORDER_BY WHERE QUERY_ID = l_query_id;

Line: 935

   DELETE  FROM JTF_PERZ_QUERY_RAW_SQL WHERE QUERY_ID = l_query_id;

Line: 938

   DELETE  FROM JTF_PERZ_QUERY WHERE QUERY_ID = l_query_id;

Line: 945

	  ROLLBACK TO DELETE_PERZ_QUERY_PVT;

Line: 953

	  ROLLBACK TO DELETE_PERZ_QUERY_PVT;

Line: 961

	  ROLLBACK TO DELETE_PERZ_QUERY_PVT;

Line: 973

END Delete_perz_query;

Line: 1034

     SELECT  Query_ID, Profile_ID, Application_id, Query_Name, Query_Type,
		Query_Description, Query_Data_source
     FROM    JTF_PERZ_QUERY
     WHERE   Profile_ID = p_profile_id AND Application_ID = p_application_id;

Line: 1040

     SELECT  Query_ID, Profile_ID, Application_id, Query_Name, Query_Type,
		Query_Description, Query_Data_source
     FROM    JTF_PERZ_QUERY
     WHERE   query_id = p_query_id;

Line: 1047

     SELECT  Query_ID, Profile_ID, Application_id, Query_Name, Query_Type,
		Query_Description, Query_Data_source
     FROM    JTF_PERZ_QUERY
     WHERE   query_type = p_query_type
		AND query_name = p_query_name
		AND Profile_ID = p_profile_id
		AND Application_ID = p_application_id;

Line: 1056

     SELECT  Query_ID, Profile_ID, Application_id, Query_Name, Query_Type,
		Query_Description, Query_Data_source
     FROM    JTF_PERZ_QUERY
     WHERE   query_type = p_query_type AND Profile_ID = p_profile_id AND Application_ID = p_application_id;

Line: 1062

     SELECT  Query_ID, Profile_ID, Application_id, Query_Name, Query_Type,
		Query_Description, Query_Data_source
     FROM    JTF_PERZ_QUERY
     WHERE   query_name = p_query_name AND Profile_ID = p_profile_id AND Application_ID = p_application_id;

Line: 1342

		l_SELECT_STRING_temp		VARCHAR2(200);

Line: 1353

	SELECT QUERY_PARAM_ID, QUERY_ID, PARAMETER_NAME,
		PARAMETER_TYPE, PARAMETER_VALUE, PARAMETER_CONDITION,
		PARAMETER_SEQUENCE
	FROM JTF_PERZ_QUERY_PARAM
	WHERE QUERY_ID = p_query_id
	ORDER BY PARAMETER_SEQUENCE;

Line: 1361

	SELECT QUERY_ORDER_BY_ID, QUERY_ID, PARAMETER_NAME,
		ACND_DCND_FLAG, PARAMETER_SEQUENCE
	FROM JTF_PERZ_QUERY_ORDER_BY
	WHERE QUERY_ID = p_query_id
	ORDER BY PARAMETER_SEQUENCE;

Line: 1498

	l_SELECT_STRING_temp	:= NULL;

Line: 1507

		SELECT QUERY_RAW_SQL_ID, QUERY_ID, SELECT_STRING,
		FROM_STRING, WHERE_STRING, ORDER_BY_STRING,
		GROUP_BY_STRING, HAVING_STRING
		INTO
			x_query_raw_sql_rec.QUERY_RAW_SQL_ID,
			x_query_raw_sql_rec.QUERY_ID,
			x_query_raw_sql_rec.SELECT_STRING,
			x_query_raw_sql_rec.FROM_STRING,
			x_query_raw_sql_rec.WHERE_STRING,
			x_query_raw_sql_rec.ORDER_BY_STRING,
			x_query_raw_sql_rec.GROUP_BY_STRING,
			x_query_raw_sql_rec.HAVING_STRING
		FROM JTF_PERZ_QUERY_RAW_SQL
		WHERE QUERY_ID = p_query_id;

Line: 1706

   Update_Perz_Query
   (	p_api_version_number,
  	p_init_msg_list,
	l_commit,

	p_application_id,
	l_profile_id,

	l_query_id,
	p_query_name ,
	p_query_type,
	p_query_desc,
	p_query_data_source ,

	p_query_param_tbl,

    	p_query_order_by_tbl,
    	p_query_raw_sql_rec	 ,
	x_query_id  ,
	x_return_status	,
	x_msg_count,
	x_msg_data
   );