Query Oracle datamart based on flex fields

Create query scripts to query and extract the required information from the Oracle datamart. Make sure these scripts are available to any user (including users with read-only permission) who wants to execute these scripts. This topic lists the functions and sample queries for a few specific use cases.

Important:

Log into TeamForge as a Reporting user and run these queries. Note that this is a one-time process.
Use the following flex field functions by joining the flex_field_dimension, artifact_flex_field and artifact_transaction_fact (or) artifact_daily_snapshot_fact tables as it depends on the XML data and flex field key generated by the platform.

Function for Date flex fields

Input arguments

Flex field XML (derived automatically by joining the artifact_transaction_fact (or) artifact_daily_snapshot_fact and artifact_flex_field tables)
Flex field key (derived automatically from flex_field_dimension table and other tables)

Output arguments

Date flex field values

create or replace FUNCTION get_artifact_date_value( artifact_xml IN XMLTYPE, field_key IN INTEGER )
   RETURN varchar2
   IS
 flex varchar2(4000);
   BEGIN
      SELECT x.val INTO flex FROM dual ,
      XMLTABLE ('/fields/field[@key=$keyalias]'
          PASSING artifact_xml,field_key as "keyalias"
          COLUMNS val VARCHAR2(4000) PATH '@val') x;
   RETURN(flex);
   END;

Function for Text flex fields

Input arguments

Flex field XML (derived automatically by joining the artifact_transaction_fact (or) artifact_daily_snapshot_fact and artifact_flex_field tables)
Flex field key (derived automatically from flex_field_dimension table and other tables)

Output arguments

Text flex field values

create or replace FUNCTION replacen( artifact_xml1 IN XMLTYPE)
   RETURN varchar2
   IS 
   replaceval  varchar2(3000);
   BEGIN
   SELECT REPLACE((REPLACE(cast(artifact_xml1 as varchar2(3000)),'','')),'','') into replaceval  from dual; 
   RETURN(replaceval);
   END;
create or replace FUNCTION get_artifact_text_value( artifact_xml IN XMLTYPE, field_key IN integer )
   RETURN varchar2
   IS flex varchar2(3200);
   fields_xml XMLTYPE;
   BEGIN
   SELECT XMLTYPE(replacen(artifact_xml)) INTO fields_xml FROM dual;
      SELECT x.val INTO flex FROM dual ,
      XMLTABLE ('/fields/field[@key=$keyalias]'
          PASSING fields_xml,field_key as "keyalias"
          COLUMNS val VARCHAR2(400) PATH '@val' ) x;
   RETURN(flex);
   END;

Function for Single-select flex fields

Input arguments

Flex field XML (derived automatically by joining the artifact_transaction_fact (or) artifact_daily_snapshot_fact and artifact_flex_field tables)
Flex field key (derived automatically from flex_field_dimension table and other tables)

Output arguments

Values selected or stored in single-select flex field

create or replace FUNCTION get_artifact_select_value( artifact_xml IN XMLTYPE, field_key IN INTEGER)
   RETURN VARCHAR2
   IS
    singleSelectValue VARCHAR2(100);
    flex INTEGER;
   BEGIN
      SELECT x.val INTO flex FROM dual ,
      XMLTABLE ('/fields/field[@key=$keyalias]'
          PASSING artifact_xml,field_key as "keyalias"
          COLUMNS val VARCHAR2(30) PATH '@val') x;
   SELECT  z.value INTO singleSelectValue from  FLEX_FIELD_VALUE_DIMENSION z  where z.FLEX_FIELD_VALUE_KEY=flex;
   RETURN(singleSelectValue);
   END;

Function for Multi-select flex fields

Input arguments

Flex field XML (derived automatically by joining the artifact_transaction_fact (or) artifact_daily_snapshot_fact and artifact_flex_field tables).
Flex field key (derived automatically from flex_field_dimension table and other tables).
Specific value(s) stored in the field or the keyword 'ALL' for retrieving all values stored in multi-select flex field.
Logical conditional operator. Possible values are 'ALL' or 'ANY'. The argument value of 'ALL' performs a search equivalent to the 'AND' condition and the value of 'ANY' performs a search equivalent to the 'OR' condition.

Output arguments

Values selected or stored in multi-select flex field

-----Inner Function----
		create or replace FUNCTION get_artifact_multiselect_any(artifact_xml IN XMLTYPE, field_key IN INTEGER,selectedfields IN varchar2)
   RETURN varchar2
   IS 
   flexs varchar2(300);
   arraylength integer default 1;
   loop1 integer default 1;
   loop2 integer default 1;
    i integer default 1;
    j integer default 1;
     v_array1 apex_application_global.vc_arr2; 
     v_array2 apex_application_global.vc_arr2; 
     v_array3 apex_application_global.vc_arr2; 
     results varchar2(200) ; 
      
   BEGIN
       SELECT x.val  INTO flexs FROM dual ,
        XMLTABLE ('/fields/field[@key=$keyalias]'
          PASSING artifact_xml,field_key as "keyalias"
          COLUMNS val VARCHAR2(4000) PATH '@val') x;
   v_array1 := apex_util.string_to_table(flexs,',');
   
   FOR  loop1 in 1..v_array1.count LOOP
     select value into v_array2(loop1) from  flex_field_value_dimension where flex_field_value_key=v_array1(loop1);
   END LOOP;
 
 v_array3 := apex_util.string_to_table(selectedfields,',');
 
 
 FOR   loop2 in 1..1 LOOP
                IF (v_array3(loop2)='ALL') THEN
		                             FOR i in 1..v_array2.count LOOP
					                             IF LENGTH(results)!=0 THEN 
                                                     results:=results ||','||v_array2(i);
                                                 ELSE
                                                      results:=v_array2(i);
                                                  END IF;
                                                 
					                   END LOOP;
									   EXIT;
		 
		        ELSE
		 
		 
                                     FOR  i in 1..v_array2.count LOOP
                                          FOR  j in 1..v_array3.count LOOP
                                               IF (v_array3(j)=v_array2(i)) THEN
                                                    IF LENGTH(results)!=0 THEN 
                                                        results:=results ||','||v_array3(j);
                                                        ELSE
                                                        results:=v_array3(j);
                                                    END IF;
                                                    EXIT;
                                                END IF;
     
                                          END LOOP;
                                     END LOOP;
                END IF;
  END LOOP;

 RETURN(results);
           END;
/
		
-----End Inner function-----
   
   create or replace FUNCTION get_artifact_multiselect_value(artifact_xml IN XMLTYPE, field_key IN INTEGER,selectedfields IN varchar2,condition IN varchar2)
   RETURN varchar2
   IS 
   flexs varchar2(300);
   loop1 integer default 1;
   loop2 integer default 1;
    i integer default 1;
    j integer default 1;
     v_array1 apex_application_global.vc_arr2; 
     v_array2 apex_application_global.vc_arr2; 
     v_array3 apex_application_global.vc_arr2; 
     results varchar2(200) ; 
     filteredField varchar2(200) ; 
	 valcount integer default 0;
      
   BEGIN
       SELECT x.val  INTO flexs FROM dual ,
        XMLTABLE ('/fields/field[@key=$keyalias]'
          PASSING artifact_xml,field_key as "keyalias"
          COLUMNS val VARCHAR2(4000) PATH '@val') x;
   v_array1 := apex_util.string_to_table(flexs,',');
   
   FOR  loop1 in 1..v_array1.count LOOP
     select value into v_array2(loop1) from  flex_field_value_dimension where flex_field_value_key=v_array1(loop1);
   END LOOP;
 
 v_array3 := apex_util.string_to_table(selectedfields,',');
 
 IF (UPPER(condition)=UPPER('ALL')) THEN
										  FOR   loop2 in 1..1 LOOP
													IF (v_array3(loop2)='ALL') THEN
																	 FOR i in 1..v_array2.count LOOP
																				 IF LENGTH(results)!=0 THEN 
																					 results:=results ||','||v_array2(i);
																				 ELSE
																					  results:=v_array2(i);
																				 END IF;
																				 
																	   END LOOP;
																	   filteredField:=results;
																	   EXIT;
										 
													ELSE
										 
										 
																	 FOR  i in 1..v_array3.count LOOP
																		  FOR  j in 1..v_array2.count LOOP
																			   IF (v_array3(i)=v_array2(j)) THEN
																					IF LENGTH(results)!=0 THEN 
																						results:=results ||','||v_array3(j);
																						valcount:=valcount+1;
																						ELSE
																						results:=v_array3(j);
																						valcount:=valcount+1;
																					END IF;
																					EXIT;
																				END IF;
									 
																		  END LOOP;
																	 END LOOP;
																	 IF (valcount=v_array3.count) THEN
																	 filteredField:=results;
																	 ELSE 
																	 filteredField:='';
																	 END IF;
													END IF;
										END LOOP;
  
     
	 
	 
	 ELSE
			select get_artifact_multiselect_any(artifact_xml,field_key,selectedfields) into results from dual;
				IF results IS NOT NULL THEN
					filteredField:=results;
				ELSE 
					filteredField:='';
				END IF;
   END IF;
filteredField:=TRIM(TRAILING ',' FROM filteredField);
         
RETURN(filteredField);
           END;

Function for Multi-user flex fields

Input arguments

Flex field XML (derived automatically by joining the artifact_transaction_fact (or) artifact_daily_snapshot_fact and artifact_flex_field tables).
Flex field key (derived automatically from flex_field_dimension table and other tables).
Specific value(s) stored in the field or the keyword 'ALL' for retrieving all values stored in multi-select flex field.
Logical conditional operator. Possible values are 'ALL' or 'ANY'. The argument value of 'ALL' performs a search equivalent to the 'AND' condition and the value of 'ANY' performs a search equivalent to the 'OR' condition.

Output arguments

Values selected or stored in multi-user flex field

------Inner Function-----
		create or replace FUNCTION get_artifact_user_any(artifact_xml IN XMLTYPE, field_key IN integer,selectedfields IN varchar2)
   RETURN varchar2
   IS 
   flexs varchar2(300);
   arraylength integer default 1;
   loop1 integer default 1;
   loop2 integer default 1;
    i integer default 1;
    j integer default 1;
     v_array1 apex_application_global.vc_arr2; 
     v_array2 apex_application_global.vc_arr2; 
     v_array3 apex_application_global.vc_arr2; 
     results varchar2(200) ; 
      
   BEGIN
       SELECT x.val  INTO flexs FROM dual ,
        XMLTABLE ('/fields/field[@key=$keyalias]'
          PASSING artifact_xml,field_key as "keyalias"
          COLUMNS val VARCHAR2(4000) PATH '@val') x;
   v_array1 := apex_util.string_to_table(flexs,',');
   
   FOR  loop1 in 1..v_array1.count LOOP
     select full_name into v_array2(loop1) from  user_dimension where user_key=v_array1(loop1);
   END LOOP;
 
 v_array3 := apex_util.string_to_table(selectedfields,',');
 
 
 FOR   loop2 in 1..v_array3.count LOOP
                IF (v_array3(loop2)='ALL') THEN
		                             FOR i in 1..v_array2.count LOOP
					                             IF LENGTH(results)!=0 THEN 
                                                     results:=results ||','||v_array2(i);
                                                 ELSE
                                                      results:=v_array2(i);
                                                  END IF;
                                                 
					                   END LOOP;
									   EXIT;
		 
		        ELSE
		 
		 
                                     FOR  i in 1..v_array2.count LOOP
                                          FOR  j in 1..v_array3.count LOOP
                                               IF (v_array3(j)=v_array2(i)) THEN
                                                    IF LENGTH(results)!=0 THEN 
                                                        results:=results ||','||v_array3(j);
                                                        ELSE
                                                        results:=v_array3(j);
                                                    END IF;
                                                    EXIT;
                                                END IF;
     
                                          END LOOP;
                                     END LOOP;
                END IF;
  END LOOP;

 RETURN(results);
           END;

------End Inner Function----

create or replace FUNCTION get_artifact_user_value(artifact_xml IN XMLTYPE, field_key IN INTEGER,selectedfields IN varchar2,condition IN varchar2)
   RETURN varchar2
   IS 
   flexs varchar2(300);
   loop1 integer default 1;
   loop2 integer default 1;
    i integer default 1;
    j integer default 1;
     v_array1 apex_application_global.vc_arr2; 
     v_array2 apex_application_global.vc_arr2; 
     v_array3 apex_application_global.vc_arr2; 
     results varchar2(200) ; 
     filteredField varchar2(200) ; 
	 valcount integer default 0;
      
   BEGIN
       SELECT x.val  INTO flexs FROM dual ,
        XMLTABLE ('/fields/field[@key=$keyalias]'
          PASSING artifact_xml,field_key as "keyalias"
          COLUMNS val VARCHAR2(4000) PATH '@val') x;
   v_array1 := apex_util.string_to_table(flexs,',');
   
   FOR  loop1 in 1..v_array1.count LOOP
     select full_name into v_array2(loop1) from  user_dimension where user_key=v_array1(loop1);
   END LOOP;
 
 v_array3 := apex_util.string_to_table(selectedfields,',');
 
 IF (UPPER(condition)=UPPER('ALL')) THEN
										  FOR   loop2 in 1..1 LOOP
													IF (v_array3(loop2)='ALL') THEN
																	 FOR i in 1..v_array2.count LOOP
																				 IF LENGTH(results)!=0 THEN 
																					 results:=results ||','||v_array2(i);
																				 ELSE
																					  results:=v_array2(i);
																				 END IF;
																				 
																	   END LOOP;
																	   filteredField:=results;
																	   EXIT;
										 
													ELSE
										 
										 
																	 FOR  i in 1..v_array3.count LOOP
																		  FOR  j in 1..v_array2.count LOOP
																			   IF (v_array3(i)=v_array2(j)) THEN
																					IF LENGTH(results)!=0 THEN 
																						results:=results ||','||v_array3(j);
																						valcount:=valcount+1;
																						ELSE
																						results:=v_array3(j);
																						valcount:=valcount+1;
																					END IF;
																					EXIT;
																				END IF;
									 
																		  END LOOP;
																	 END LOOP;
																	 IF (valcount=v_array3.count) THEN
																	 filteredField:=results;
																	 ELSE 
																	 filteredField:='';
																	 END IF;
													END IF;
										END LOOP;
  
     
	 
	 
	 ELSE
			select get_artifact_user_any(artifact_xml,field_key,selectedfields) into results from dual;
				IF results IS NOT NULL THEN
					filteredField:=results;
				ELSE 
					filteredField:='';
				END IF;
   END IF;
filteredField:=TRIM(TRAILING ',' FROM filteredField);
         
RETURN(filteredField);
           END;

Sample use cases and queries

Note:

Flex field Name, Value, Tracker title and so on that are used in the filtering conditions are case-sensitive.
See Case 7 below for building a case-insensitive search using upper() or lower() functions.

Use case 1: Filter Date and Text flex fields

Suppose you want to retrieve data based on the following assumptions:

Date flex field named 'CreatedDate' that has a value of '2015-07-07 00:00:00'
Text flex field named 'TEXT' with part of its value containing the pattern 'cre'
Tracker title is 'TestDateTracker'

Note:

You can change the field name, tracker title and values based on the data in your system
The date passed as input should be of the format “YYYY-MM-DD HH24:MI:SS”

Sample query

SELECT t.date_of_trans  "Date",
       t.Project  ,
       t.Tracker  ,
       t.PlaningFolder  ,
       t.Priority,
       count(distinct t.artifact_key) "TotalArtifacts"  FROM (SELECT          b.date_of_trans,
                        e.title  AS Project,
		 c.title AS Tracker,
                        d.title AS PlaningFolder,
                        'P'||a.priority AS Priority,
                        a.artifact_key
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
                      inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
                      inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
	          inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
	          inner join  project_dimension e on  (a.project_key=e.project_key) 
                  inner join flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
                  and ffd.name='Date'
                  and c.title='TestDateTracker'
	          and get_artifact_date_value(ff.flex_fields,ffd.flex_field_key)='2015-08-12 00:00:00'

UNION


 SELECT            b.date_of_trans,
                           e.title  AS Project,
		    c.title AS Tracker,
                           d.title AS PlaningFolder,
                           'P'||a.priority,
                            a.artifact_key
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
                       inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
                       inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
	           inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
               	   inner join  project_dimension e on  (a.project_key=e.project_key)
                   inner join  flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
                   and ffd.name='TEXT' 
 and c.title='TrackerTest'
	          and get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) LIKE 'cre%'
	          and d.id='plan1004' ) t


GROUP BY  t.date_of_trans,t.Project,t.Tracker,t.PlaningFolder,t.Priority
ORDER BY  t.date_of_trans,t.Project,t.Tracker,t.PlaningFolder,t.Priority

Use case 2: Filter Single-select flex fields

Suppose you want to retrieve data based on the following assumptions:

Flex field name: 'FSS1'
Value selected or stored: 'S1'
Tracker title: 'FTracker'

Note:

You can change the field name, tracker title and values based on the data in your system

Sample query

SELECT    b.date_of_trans  "Date",
                   e.title "Project",
                   c.title  "Tracker",
                   d.title  "Planingfolder",
                   'P'||a.priority  "Priority",
                  count( distinct a.artifact_key) "TotalArtifacts"
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
                             inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
                             inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
		     inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
		    inner join  project_dimension e on  (a.project_key=e.project_key)  
                    inner join flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
                    and ffd.name='FSS1' 
                    and c.title='FTracker' and get_artifact_select_value(ff.flex_fields,ffd.flex_field_key)='S1'
--where a.effective_from <= sysdate and a.effective_till > to_date('2012-04-01', 'YYYY-MM-DD')
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority

Use case 3: Filter Text flex fields

Suppose you want to retrieve data based on the following assumptions:

Flex field name: 'TEXT'
Value contains: 'cre
Tracker name: 'TrackerTest'

Note:

You can change the field name, tracker title and values based on the data in your system

Sample query

SELECT            b.date_of_trans  "Date",
                           e.title "Project",
		    c.title  "Tracker",
                           d.title  "Planingfolder",
                           'P'||a.priority  "Priority",
                           count( distinct a.artifact_key) "TotalArtifacts"
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
                       inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
                       inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
	           inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
               	   inner join  project_dimension e on  (a.project_key=e.project_key)
                   inner join  flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
                   and ffd.name='TEXT' 
 and c.title='TrackerTest'
	          and get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) LIKE 'cre%'
	          and d.id='plan1004'  
--where a.effective_from <= sysdate and a.effective_till > to_date('2012-04-01', 'YYYY-MM-DD')
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority

Use case 4: Filter Date flex fields

Suppose you want to retrieve data based on the following assumptions:

Flex field name: 'Date'
Value: '2015-08-12 00:00:00'
Tracker name: 'TestDateTracker'

Note:

You can change the field name, tracker title and values based on the data in your system

Sample query

SELECT          b.date_of_trans  "Date",
                        e.title "Project",
		 c.title  "Tracker",
                        d.title  "Planingfolder",
                        'P'||a.priority  "Priority",
                        count( distinct a.artifact_key) "TotalArtifacts"
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
                      inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
                      inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
	          inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
	          inner join  project_dimension e on  (a.project_key=e.project_key) 
                  inner join flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
                  and ffd.name='Date'
                  and c.title='TestDateTracker'
	          and get_artifact_date_value(ff.flex_fields,ffd.flex_field_key)='2015-08-12 00:00:00'
--where a.effective_from <= sysdate and a.effective_till > to_date('2012-04-01', 'YYYY-MM-DD')
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority

Use case 5: Multi-select flex fields

Suppose you want to retrieve data based on the following assumptions:

Multi-select flex field name: 'Multiselect'
Value: 'M12,M11'
Tracker name: 'TrackerTest'
Conditional parameter: 'ALL'

Note:

You can change the field name, tracker title and values based on the data in your system
Flex field name, value and tracker title that are used in the SQL filter conditions are case sensitive
If you want to select all the values in User flex field, then pass 'ALL' as the conditional parameter
If you want to select any value, then pass 'ANY' as the conditional parameter

Sample query

SELECT          b.date_of_trans  "Date",
                              e.title "Project",
		                      c.title  "Tracker",
                              d.title  "Planingfolder",
                         'P'||a.priority  "Priority",
	             count(distinct a.artifact_key)
 FROM       artifact_transaction_fact a   inner join date_dimension b on (a.trans_date_key=b.date_key) 
               inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
               inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
		       inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
		       inner join  project_dimension e on  (a.project_key=e.project_key) 
               inner join flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
               and ffd.name='Multiselect'
               and c.title='TrackerTest'
               and
get_artifact_multiselect_value(ff.flex_fields,ffd.flex_field_key,'M11,M12','ALL') IS NOT NULL  
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority

Use case 6: Filter User flex fields

Suppose you want to retrieve data based on the following assumptions:

User flex field name: 'Select User'
Value: 'user1,user2'
Tracker name: 'TrackerTestUser'
Conditional parameter: 'ALL'

Note:

You can change the field name, tracker title and values based on the data in your system
Flex field name, value and tracker title that are used in the SQL filter conditions are case sensitive
If you want to select all the values in User flex field, then pass 'ALL' as the conditional parameter
If you want to select any value, then pass 'ANY' as the conditional parameter

Sample query

  SELECT
          b.date_of_trans  "Date",
          e.title "Project",
		  c.title  "Tracker",
          d.title  "Planingfolder",
         'P'||a.priority  "Priority",
         count( distinct a.artifact_key) "TotalArtifacts"
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
        inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
        inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
		inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
		inner join  project_dimension e on  (a.project_key=e.project_key) 
        inner join flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
        and ffd.name='Select User'
        and c.title='TrackerTestUser'
        and get_artifact_user_value(ff.flex_fields,ffd.flex_field_key,'user1,user2','ALL') IS NOT NULL  
--where a.effective_from <= sysdate and a.effective_till > to_date('2012-04-01', 'YYYY-MM-DD')
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority

Use case 7: Filter Text flex fields, case-insensitive search

Suppose you want to retrieve data based on the following assumptions:

Flex field name: 'TEXT'
Value contains: 'cre'
Tracker name: 'TestDateTracker'

Note:

You can change the field name, tracker title and values based on the data in your system
The upper() function can be replaced with the lower() function appropriately for case-insensitive search

Sample query

 SELECT
            b.date_of_trans  "Date",
            e.title "Project",
 		   c.title  "Tracker",
            d.title  "Planingfolder",
           'P'||a.priority  "Priority",
            count( distinct a.artifact_key) "TotalArtifacts"
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
            inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
            inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
		    inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
		    inner join  project_dimension e on  (a.project_key=e.project_key)  
            inner join  flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
            and UPPER(ffd.name)=UPPER('TEXT')
            and upper(c.title)=upper('TestDateTracker')
		    and upper(get_artifact_text_value(ff.flex_fields,ffd.flex_field_key)) like upper('cre%')   
--where a.effective_from <= sysdate and a.effective_till > to_date('2012-04-01', 'YYYY-MM-DD')
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority