APPS.JTS_CONFIG_VERSION_PVT SQL Statements

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

Line: 31

  SELECT count(*)
  INTO	l_exists
  FROM  jts_config_versions_b
  WHERE	configuration_id = p_config_id;

Line: 38

     SELECT max(version_number)
     INTO   l_max_version
     FROM  jts_config_versions_b
     WHERE configuration_id = p_config_id;

Line: 60

   SELECT jts_config_versions_b_s.NEXTVAL
   INTO   l_version_id
   FROM   sys.dual;

Line: 79

  SELECT version_id
  INTO	l_version_id
  FROM  jts_config_versions_b
  WHERE	version_name = p_version_name
  AND   configuration_id = p_config_id;

Line: 103

  SELECT count(*)
  INTO   l_count
  FROM	 jts_config_versions_vl
  WHERE  configuration_id = p_config_id
  AND	 version_name = p_version_name;

Line: 172

PROCEDURE INSERT_ROW(p_configuration_id  IN NUMBER,
     p_init_version IN VARCHAR2,
     x_version_id OUT NUMBER) IS
   l_queue_name		JTS_CONFIG_VERSIONS_B.queue_name%TYPE;

Line: 204

  insert into JTS_CONFIG_VERSIONS_B (
    VERSION_ID,
    VERSION_NAME,
    CONFIGURATION_ID,
    VERSION_NUMBER,
    QUEUE_NAME,
    OBJECT_VERSION_NUMBER,
    ATTRIBUTE_CATEGORY,
    ATTRIBUTE1,
    ATTRIBUTE2,
    ATTRIBUTE3,
    ATTRIBUTE4,
    ATTRIBUTE5,
    ATTRIBUTE6,
    ATTRIBUTE7,
    ATTRIBUTE8,
    ATTRIBUTE9,
    ATTRIBUTE10,
    ATTRIBUTE11,
    ATTRIBUTE12,
    ATTRIBUTE13,
    ATTRIBUTE14,
    ATTRIBUTE15,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    X_VERSION_ID,
    l_version_name,
    P_CONFIGURATION_ID,
    L_VERSION_NUMBER,
    L_QUEUE_NAME,
    1,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    sysdate,
    FND_GLOBAL.user_id,
    sysdate,
    FND_GLOBAL.user_id,
    FND_GLOBAL.user_id
  );

Line: 262

  insert into JTS_CONFIG_VERSIONS_TL (
    VERSION_ID,
    CONFIGURATION_ID,
    DESCRIPTION,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_VERSION_ID,
    P_CONFIGURATION_ID,
    l_description,
    sysdate,
    FND_GLOBAL.user_id,
    sysdate,
    FND_GLOBAL.user_id,
    FND_GLOBAL.user_id,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from JTS_CONFIG_VERSIONS_TL T
    where T.VERSION_ID = X_VERSION_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 295

END INSERT_ROW;

Line: 299

PROCEDURE DELETE_ROWS(p_config_id  	IN NUMBER
) IS
BEGIN
   DELETE FROM jts_config_versions_b
   WHERE  configuration_id = p_config_id;

Line: 305

   DELETE FROM jts_config_versions_tl
   WHERE  configuration_id = p_config_id;

Line: 311

END DELETE_ROWS;

Line: 355

      INSERT_ROW(p_configuration_id,
		 p_init_version,
		 x_version_id);

Line: 405

PROCEDURE UPDATE_VERSION_STAT(p_api_version	IN  NUMBER,
			   p_version_id		IN  NUMBER,
			   p_status		IN  VARCHAR2
) IS
   l_api_version   CONSTANT NUMBER        := 1.0;

Line: 410

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

Line: 421

   UPDATE  jts_config_versions_b
   SET     version_status_code = p_status,
	   last_update_date = sysdate,
   	   last_updated_by = FND_GLOBAL.user_id
   WHERE   version_id = p_version_id;

Line: 430

END UPDATE_VERSION_STAT;

Line: 433

PROCEDURE UPDATE_REPLAY_DATA(p_api_version	IN  NUMBER,
			   p_version_id		IN  NUMBER,
			   p_status		IN  VARCHAR2
) IS
   l_api_version   CONSTANT NUMBER        := 1.0;

Line: 438

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

Line: 449

   UPDATE  jts_config_versions_b
   SET     replay_status_code = p_status,
	   replayed_on = sysdate,
	   replayed_by = FND_GLOBAL.user_id,
	   last_update_date = sysdate,
   	   last_updated_by = FND_GLOBAL.user_id
   WHERE   version_id = p_version_id;

Line: 460

END UPDATE_REPLAY_DATA;

Line: 463

PROCEDURE UPDATE_LAST_MODIFIED(p_api_version	IN  NUMBER,
			   p_version_id		IN  NUMBER
) IS
   l_api_version   CONSTANT NUMBER        := 1.0;

Line: 467

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

Line: 478

   UPDATE  jts_config_versions_b
   SET     last_update_date = sysdate,
   	   last_updated_by = FND_GLOBAL.user_id
   WHERE   version_id = p_version_id;

Line: 486

END UPDATE_LAST_MODIFIED;

Line: 490

PROCEDURE UPDATE_NAME_DESC(p_api_version	IN  NUMBER,
			   p_version_id		IN  NUMBER,
			   p_config_id		IN  NUMBER,
			   p_version_name 	IN  VARCHAR2,
			   p_version_desc 	IN  VARCHAR2,
   			   x_return_status      OUT VARCHAR2,
   			   x_msg_count          OUT NUMBER,
   			   x_msg_data           OUT VARCHAR2
) IS
   l_api_version   CONSTANT NUMBER        := 1.0;

Line: 500

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

Line: 507

   SAVEPOINT UPDATE_NAME_DESC;

Line: 530

	UPDATE  jts_config_versions_b
	SET     version_name = p_version_name,
		last_update_date = sysdate,
		last_updated_by = FND_GLOBAL.user_id,
		last_update_login = FND_GLOBAL.user_id
	WHERE   version_id = p_version_id;

Line: 538

	UPDATE  jts_config_versions_tl
	SET     description = p_version_desc,
		last_update_date = sysdate,
    		last_updated_by = FND_GLOBAL.user_id,
    		last_update_login = FND_GLOBAL.user_id,
    		source_lang = USERENV('LANG')
	WHERE   version_id = p_version_id
	AND	USERENV('LANG') IN (language, source_lang);

Line: 555

      ROLLBACK TO UPDATE_NAME_DESC;

Line: 566

END UPDATE_NAME_DESC;

Line: 570

PROCEDURE DELETE_VERSION(p_api_version		IN  Number,
			 p_commit		IN  VARCHAR2 DEFAULT FND_API.G_FALSE,
			 p_version_id		IN  NUMBER
) IS
   l_api_version   CONSTANT NUMBER        := 1.0;

Line: 575

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

Line: 579

   SAVEPOINT DELETE_VERSION;

Line: 590

   JTS_CONFIG_VER_STATUS_PVT.DELETE_VERSION_STATUSES(p_api_version, p_version_id);

Line: 591

   JTS_CONFIG_VERSION_FLOW_PVT.DELETE_VERSION_FLOWS(p_api_version, p_version_id);

Line: 592

   JTS_CONFIG_VERSIONS_PKG.DELETE_ROW(p_version_id);

Line: 600

        ROLLBACK TO DELETE_VERSION;

Line: 604

END DELETE_VERSION;

Line: 608

PROCEDURE DELETE_SOME_VERSIONS(p_api_version		IN  Number,
   			       p_version_tbl		IN  Version_Id_Tbl_Type
) IS
   l_api_version   CONSTANT NUMBER        := 1.0;

Line: 612

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

Line: 618

   SAVEPOINT DELETE_SOME_VERSIONS;

Line: 629

      DELETE_VERSION(p_api_version => p_api_version,
		     p_commit => FND_API.G_FALSE,
		     p_version_id => p_version_tbl(i));

Line: 637

      ROLLBACK TO DELETE_SOME_VERSIONS;

Line: 638

END DELETE_SOME_VERSIONS;

Line: 642

PROCEDURE DELETE_VERSIONS(p_api_version		IN  NUMBER,
   			  p_config_id		IN  NUMBER
) IS
   l_api_version   CONSTANT NUMBER        := 1.0;

Line: 646

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

Line: 659

   JTS_CONFIG_VER_STATUS_PVT.DELETE_CONFIG_VER_STATUSES(p_api_version, p_config_id);

Line: 660

   JTS_CONFIG_VERSION_FLOW_PVT.DELETE_CONFIG_VERSION_FLOWS(p_api_version, p_config_id);

Line: 661

   DELETE_ROWS(p_config_id);

Line: 666

END DELETE_VERSIONS;

Line: 695

   l_debug_info := 'Version select';

Line: 696

   SELECT  	configuration_id, version_id, version_name, version_number, v.description, queue_name,
		v.attribute_category, v.attribute1, v.attribute2, v.attribute3, v.attribute4, v.attribute5,
		v.attribute6, v.attribute7, v.attribute8, v.attribute9, v.attribute10, v.attribute11,
		v.attribute12, v.attribute13, v.attribute14, v.attribute15,
   		v.creation_date, v.created_by, v.last_update_date, v.last_updated_by, v.last_update_login,
		u1.user_name, u2.user_name, replay_status_code, version_status_code,
		rep.meaning, ver.meaning, replayed_on, u3.user_name
   INTO		x_version_rec.configuration_id,
		x_version_rec.version_id,
		x_version_rec.version_name,
		x_version_rec.version_number,
		x_version_rec.description,
		x_version_rec.queue_name,
		x_version_rec.attribute_category,
		x_version_rec.attribute1,
		x_version_rec.attribute2,
		x_version_rec.attribute3,
		x_version_rec.attribute4,
		x_version_rec.attribute5,
		x_version_rec.attribute6,
		x_version_rec.attribute7,
		x_version_rec.attribute8,
		x_version_rec.attribute9,
		x_version_rec.attribute10,
		x_version_rec.attribute11,
		x_version_rec.attribute12,
		x_version_rec.attribute13,
		x_version_rec.attribute14,
		x_version_rec.attribute15,
		x_version_rec.creation_date,
		x_version_rec.created_by,
		x_version_rec.last_update_date,
		x_version_rec.last_updated_by,
		x_version_rec.last_update_login,
		x_version_rec.created_by_name,
		x_version_rec.last_updated_by_name,
   		x_version_rec.replay_status_code,
   		x_version_rec.version_status_code,
   		x_version_rec.replay_status,
   		x_version_rec.version_status,
   		x_version_rec.replayed_date,
   		x_version_rec.replayed_by_name
   FROM    	jts_config_versions_vl v,
	        fnd_lookup_values rep,
		fnd_lookup_values ver,
	 	fnd_user u1,
		fnd_user u2,
		fnd_user u3
   WHERE   	version_id = p_version_id
   AND		rep.lookup_type (+) = C_STATUS_TYPE
   AND	 	rep.lookup_code (+) = v.replay_status_code
   AND		ver.lookup_type = C_STATUS_TYPE
   AND	 	ver.lookup_code = nvl(v.version_status_code, C_NEW)
   AND		u1.user_id  = v.created_by
   AND		u2.user_id  = v.last_updated_by
   AND		u3.user_id  (+) = v.replayed_by;

Line: 865

   ELSIF (l_order_by = 'LAST_UPDATE_DATE') THEN
       l_order_by := 'v.last_update_date';

Line: 869

   ELSIF (l_order_by = 'LAST_UPDATED_BY') THEN
       l_order_by := 'u2.user_name';

Line: 881

   sqlStmt :=   'SELECT  configuration_id, version_id, version_name, version_number, v.description, queue_name, '
	     ||	' v.attribute_category, v.attribute1, v.attribute2, v.attribute3, v.attribute4, v.attribute5, '
	     || ' v.attribute6, v.attribute7, v.attribute8, v.attribute9, v.attribute10, v.attribute11, '
	     || ' v.attribute12, v.attribute13, v.attribute14, v.attribute15, '
	     || ' v.creation_date, v.created_by, v.last_update_date, v.last_updated_by, v.last_update_login, '
	     || ' u1.user_name, u2.user_name, '
	     || ' replay_status_code, version_status_code, rep.meaning, ver.meaning, '
	     || ' replayed_on, u3.user_name '
   	     || 'FROM    jts_config_versions_vl v, '
	     || '  	 fnd_lookup_values rep, '
	     || '  	 fnd_lookup_values ver, '
	     || '  	 fnd_user u1, '
	     || ' 	 fnd_user u2, '
	     || ' 	 fnd_user u3 '
   	     || 'WHERE   configuration_id = ' || p_config_id ||
   	      '	AND	rep.lookup_type (+) = ''' || C_STATUS_TYPE ||
   	    ''' AND	rep.lookup_code (+)=  v.replay_status_code ' ||
   	      '	AND	ver.lookup_type = ''' || C_STATUS_TYPE ||
   	    '''	AND	ver.lookup_code = nvl(v.version_status_code, ''' || C_NEW || ''') ' ||
   	      '	AND	u1.user_id = v.created_by ' ||
   	      '	AND	u2.user_id = v.last_updated_by ' ||
   	      '	AND	u3.user_id (+) = v.replayed_by ' ||
   	      '	ORDER BY ' || l_order_by || ' ' || l_how_to_order;

Line: 932

		x_version_tbl(i).last_update_date,
		x_version_tbl(i).last_updated_by,
		x_version_tbl(i).last_update_login,
		x_version_tbl(i).created_by_name,
		x_version_tbl(i).last_updated_by_name,
   		x_version_tbl(i).replay_status_code,
   		x_version_tbl(i).version_status_code,
   		x_version_tbl(i).replay_status,
   		x_version_tbl(i).version_status,
   		x_version_tbl(i).replayed_date,
   		x_version_tbl(i).replayed_by_name;