DBA Data[Home] [Help]

APPS.JTF_DECLARATIVE_DIAGNOSTIC SQL Statements

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

Line: 13

  PROCEDURE INSERT_COL_STEP_DATA(
			P_APPID 		IN 	VARCHAR2,
			P_GROUPNAME 		IN 	VARCHAR2,
			P_TESTCLASSNAME 	IN 	VARCHAR2,
			P_TESTSTEPNAME		IN	VARCHAR2,
			P_COLNAMES_ARRAY  	IN	JTF_VARCHAR2_TABLE_4000,
			P_LOGOP_ARRAY  		IN	JTF_VARCHAR2_TABLE_4000,
			P_VAL1_ARRAY  		IN	JTF_VARCHAR2_TABLE_4000,
			P_VAL2_ARRAY  		IN	JTF_VARCHAR2_TABLE_4000,
			ISUPDATE		IN 	VARCHAR2,
                        P_LUBID                 IN      NUMBER) IS

	V_INDEX  NUMBER;
Line: 29

  	if isupdate = 'TRUE' then
  		delete from jtf_diagnostic_decl_step_cols
  		where
	  		appid = P_APPID
  			and groupname = p_groupname
  			and testclassname = p_testclassname
  			and teststepname = p_teststepname;
Line: 44

			insert into jtf_diagnostic_decl_step_cols
			(
				APPID,
				GROUPNAME,
				TESTCLASSNAME,
				TESTSTEPNAME,
				COLUMN_NAME,
				LOGICAL_OPERATOR,
				VALIDATION_VAL1,
				VALIDATION_VAL2,
				OBJECT_VERSION_NUMBER,
				CREATED_BY,
				LAST_UPDATE_DATE,
				LAST_UPDATED_BY,
				LAST_UPDATE_LOGIN,
				CREATION_DATE,
				SECURITY_GROUP_ID
			)
			values
			(
				P_APPID,
				P_GROUPNAME,
				P_TESTCLASSNAME,
				P_TESTSTEPNAME,
				P_COLNAMES_ARRAY(V_INDEX),
				P_LOGOP_ARRAY(V_INDEX),
				P_VAL1_ARRAY(V_INDEX),
				P_VAL2_ARRAY(V_INDEX),
				1,
				P_LUBID,
				SYSDATE,
				P_LUBID,
				NULL,
				SYSDATE,
				NULL
			);
Line: 87

  END INSERT_COL_STEP_DATA;
Line: 349

  v_selectValue JTF_VARCHAR2_TABLE_4000;
Line: 356

  	select COLUMN_NAME, LOGICAL_OPERATOR,
	VALIDATION_VAL1, VALIDATION_VAL2
	from jtf_diagnostic_decl_step_cols
	where APPID = appshtname
	and GROUPNAME = grpname
	and TESTCLASSNAME = testclsname
	and TESTSTEPNAME = teststpname;
Line: 399

	v_sqlstr := 'select ';
Line: 457

	v_selectValue 	:= JTF_VARCHAR2_TABLE_4000();
Line: 459

		v_selectValue.extend;
Line: 460

		DBMS_SQL.DEFINE_COLUMN(v_cursorID, v_count2, v_selectValue(v_count2), 3000);
Line: 513

			dbms_sql.column_value(v_cursorID, v_count2, v_selectValue(v_count2));
Line: 531

		        	if v_selectValue(v_count2) <>  v_validVal1(v_count2) then
					addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
Line: 535

		        				|| v_selectValue(v_count2) || ' (**FAILED**) '
		        				|| '', DETAILS_CLOB);
Line: 543

		        	if v_selectValue(v_count2) =  v_validVal1(v_count2) then
					addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
Line: 547

						|| v_selectValue(v_count2) || ' (**FAILED**) '
						|| '', DETAILS_CLOB);
Line: 555

		        	if to_number(v_selectValue(v_count2)) <  to_number(v_validVal1(v_count2)) then
					addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
Line: 559

						|| v_selectValue(v_count2) || ' (**FAILED**) '
						|| '', DETAILS_CLOB);
Line: 566

		        	if to_number(v_selectValue(v_count2)) >  to_number(v_validVal1(v_count2)) then
					addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
Line: 570

						|| v_selectValue(v_count2) || ' (**FAILED**) '
						|| '', DETAILS_CLOB);
Line: 577

		        	if to_number(v_selectValue(v_count2)) >=  to_number(v_validVal1(v_count2)) then
					addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
Line: 581

						|| v_selectValue(v_count2) || ' (**FAILED**) '
						|| '', DETAILS_CLOB);
Line: 588

		        	if to_number(v_selectValue(v_count2)) <=  to_number(v_validVal1(v_count2)) then
					addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
Line: 592

						|| v_selectValue(v_count2) || ' (**FAILED**) '
						|| '', DETAILS_CLOB);
Line: 599

		        	if to_number(v_validVal1(v_count2)) <= to_number(v_selectValue(v_count2))
		        		AND to_number(v_selectValue(v_count2)) <= to_number(v_validVal2(v_count2)) then

					addStringToClob(v_selectValue(v_count2), DETAILS_CLOB);
Line: 605

						|| v_selectValue(v_count2) || ' (**FAILED**) '
						|| '', DETAILS_CLOB);
Line: 852

		v_sqlstr := 'select count(*) from ' || table_view_name;
Line: 1173

  PROCEDURE UPDATE_STEP_SEQ(
			P_APPID 	IN VARCHAR2,
			P_GROUPNAME 	IN VARCHAR2,
			P_TESTCLASSNAME IN VARCHAR2,
			P_STEPSEQARRAY	IN JTF_VARCHAR2_TABLE_4000,
                        P_LUBID         IN NUMBER) IS

    v_numofrows NUMBER;
Line: 1184

	SELECT COUNT(*)
	INTO v_numofrows
        FROM jtf_diagnostic_decl_test_steps
	WHERE APPID = P_APPID
	and GROUPNAME = P_GROUPNAME
	and TESTCLASSNAME = P_TESTCLASSNAME;
Line: 1192

		RAISE_APPLICATION_ERROR(-20000, 'Cant Update Step Sequences - Mismatch in number of sequences received');
Line: 1198

		UPDATE jtf_diagnostic_decl_test_steps
		SET EXECUTION_SEQUENCE = v_index,
		OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
		LAST_UPDATE_DATE = SYSDATE,
		LAST_UPDATED_BY = P_LUBID
		WHERE appid = P_APPID
		and GROUPNAME = P_GROUPNAME
		and TESTCLASSNAME = P_TESTCLASSNAME
		and TESTSTEPNAME = P_STEPSEQARRAY(v_index);
Line: 1210

		   		'Cant Update Step Sequence, Record Not Found'
		   		|| P_APPID || ' '
		   		|| P_GROUPNAME || ' '
		   		|| P_TESTCLASSNAME || ' ' || 'OLD EXEC SEQ: ' || v_index);
Line: 1223

  END UPDATE_STEP_SEQ;
Line: 1286

  PROCEDURE insert_core_steps(
				qAppID 		IN VARCHAR2,
				newTestName 	IN VARCHAR2,
				addToGroupName 	IN VARCHAR2,
				stepType 	IN VARCHAR2,
				newStepName 	IN VARCHAR2,
				newStepDesc 	IN VARCHAR2,
				errorType 	IN VARCHAR2,
				newStepErrMsg 	IN VARCHAR2,
				newStepFixInfo 	IN VARCHAR2,
				newStepTableName IN VARCHAR2,
				newStepQuery 	 IN VARCHAR2,
				logicalOperator  IN VARCHAR2,
				val1 		IN VARCHAR2,
				val2 		IN VARCHAR2,
				isUpdate	IN VARCHAR2,
                                P_LUBID         IN NUMBER) IS

  v_ordernumber	jtf_diagnostic_decl_test_steps.EXECUTION_SEQUENCE%TYPE;
Line: 1314

  	select count(*) into v_temp
  	from jtf_diagnostic_decl_test_steps
  	where appid = qAppID
  	and groupname = addToGroupName
  	and testclassname = newTestName
  	and TESTSTEPNAME = newStepName;
Line: 1321

  	if v_temp > 0 and not isupdate = 'TRUE' then
  		raise_application_error(-20000, 'Step name already exists in testcase');
Line: 1323

  	elsif isupdate = 'TRUE' and v_temp > 0 then

  		-- first cleanup all information from the
  		-- jtf_diagnostic_arg and jtf_diagnostic_decl_step_cols

  		v_temp_char := newTestName || '/' || newStepName || '{-STEP/CLASS-}%';
Line: 1330

	  	delete from jtf_diagnostic_arg where
	  	APPID = qAppID
	  	and GROUPNAME = addToGroupName
	  	and TESTCLASSNAME like v_temp_char;
Line: 1335

	  	delete from jtf_diagnostic_decl_step_cols
	  	where appid = qAppID
	  	and groupname = addToGroupName
	  	and testclassname = newTestName
	  	and TESTSTEPNAME = newStepName;
Line: 1341

		update jtf_diagnostic_decl_test_steps
		set
			STEP_TYPE = stepType,
			STEP_DESCRIPTION = newStepDesc,
			ERROR_TYPE = errorType,
			ERROR_MESSAGE = newStepErrMsg,
			FIX_INFO = newStepFixInfo,
			TABLE_VIEW_NAME = newStepTableName,
			WHERE_CLAUSE_OR_SQL = newStepQuery,
			LOGICAL_OPERATOR = logicalOperator,
			VALIDATION_VAL1 = val1,
			VALIDATION_VAL2 = val2,
			OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
			LAST_UPDATE_DATE = SYSDATE,
			LAST_UPDATED_BY = P_LUBID
		where
			APPID = qAppID and
			GROUPNAME = addToGroupName and
			TESTCLASSNAME = newTestName and
			TESTSTEPNAME = newStepName;
Line: 1362

	elsif not isupdate = 'TRUE' and v_temp = 0 then
  		-- insert the record to the database
	  	-- getting the next sequence number for
	  	-- inserting into the DB

	    	select count(*) into v_ordernumber
	  	from jtf_diagnostic_decl_test_steps
	  	where appid = qAppID
	  	and groupname = addToGroupName
	  	and testclassname = newTestName;
Line: 1378

		 insert into jtf_diagnostic_decl_test_steps
		 (
			APPID,
			GROUPNAME,
			TESTCLASSNAME,
			TESTSTEPNAME,
			EXECUTION_SEQUENCE,
			STEP_TYPE,
			STEP_DESCRIPTION,
			ERROR_TYPE,
			ERROR_MESSAGE,
			FIX_INFO,
			MULTI_ORG,
			TABLE_VIEW_NAME,
			WHERE_CLAUSE_OR_SQL,
			LOGICAL_OPERATOR,
			VALIDATION_VAL1,
			VALIDATION_VAL2,
			OBJECT_VERSION_NUMBER,
			CREATED_BY,
			LAST_UPDATE_DATE,
			LAST_UPDATED_BY,
			LAST_UPDATE_LOGIN,
			CREATION_DATE
		 )
		 values
		 (
			qAppID,
			addToGroupName,
			newTestName,
			newStepName,
			v_ordernumber,
			stepType,
			newStepDesc,
			errorType,
			newStepErrMsg,
			newStepFixInfo,
			'N',
			newStepTableName,
			newStepQuery,
			logicalOperator,
			val1,
			val2,
			1,
			P_LUBID,
			SYSDATE,
			P_LUBID,
			NULL,
			SYSDATE
		 );
Line: 1430

  END insert_core_steps;
Line: 1443

    	select TESTSTEPNAME, STEP_DESCRIPTION
    	from jtf_diagnostic_decl_test_steps
    	where APPID like p_appid
    	and GROUPNAME = p_GROUPNAME
    	and TESTCLASSNAME = p_TESTCLASSNAME
    	order by EXECUTION_SEQUENCE;
Line: 1470

 PROCEDURE DELETE_STEPS(
			P_APPID 	IN VARCHAR2,
			P_GROUPNAME 	IN VARCHAR2,
			P_TESTCLASSNAME IN VARCHAR2,
			P_DELSTEPARRAY	IN JTF_VARCHAR2_TABLE_4000) IS

    v_index BINARY_INTEGER := 1;
Line: 1485

		select distinct EXECUTION_SEQUENCE into v_execution_sequence
		from jtf_diagnostic_decl_test_steps
		where APPID = P_APPID and groupname = P_GROUPNAME
		and TESTCLASSNAME = P_TESTCLASSNAME and
		TESTSTEPNAME = P_DELSTEPARRAY(v_index);
Line: 1491

		select step_type into v_step_type
		from jtf_diagnostic_decl_test_steps
		where APPID = P_APPID and groupname = P_GROUPNAME
		and TESTCLASSNAME = P_TESTCLASSNAME and
		TESTSTEPNAME = P_DELSTEPARRAY(v_index);
Line: 1503

			select table_view_name into v_diagnostic_testname
			from jtf_diagnostic_decl_test_steps
			where APPID = P_APPID and groupname = P_GROUPNAME
			and TESTCLASSNAME = P_TESTCLASSNAME and
			TESTSTEPNAME = P_DELSTEPARRAY(v_index);
Line: 1513

		  	delete from jtf_diagnostic_arg where
		  	APPID = P_APPID
		  	and GROUPNAME = P_GROUPNAME
		  	and TESTCLASSNAME = v_diagnostic_testname;
Line: 1520

	  	delete from jtf_diagnostic_decl_test_steps where
	  	APPID = P_APPID
	  	and GROUPNAME = P_GROUPNAME
	  	and TESTCLASSNAME = P_TESTCLASSNAME
	  	and TESTSTEPNAME = P_DELSTEPARRAY(v_index);
Line: 1527

		   RAISE_APPLICATION_ERROR(-20000, 'Cant Delete Step, Record Not Found: '
		   		|| P_APPID
		   		|| ' '
		   		|| P_GROUPNAME
		   		|| ' '
		   		|| P_TESTCLASSNAME
		   		|| ' '
		   		|| P_DELSTEPARRAY(v_index));
Line: 1538

	   	update jtf_diagnostic_decl_test_steps
	    	set EXECUTION_SEQUENCE = (EXECUTION_SEQUENCE - 1),
		OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
		LAST_UPDATE_DATE = SYSDATE
		where EXECUTION_SEQUENCE > v_execution_sequence
	  	and APPID = P_APPID
	  	and GROUPNAME = P_GROUPNAME
	  	and TESTCLASSNAME = P_TESTCLASSNAME;
Line: 1553

		  	delete from jtf_diagnostic_decl_step_cols where
		  	APPID = P_APPID
		  	and GROUPNAME = P_GROUPNAME
		  	and TESTCLASSNAME = P_TESTCLASSNAME
		  	and TESTSTEPNAME = P_DELSTEPARRAY(v_index);
Line: 1569

  END DELETE_STEPS;
Line: 1582

      select vsize(tmpReportStr) into strSize from dual;