APPS.ZPB_TRANSFORM_IO_TABLES SQL Statements

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

Line: 14

  tableinsert  VARCHAR2(5000);

Line: 21

    tableinsert := 'INSERT INTO ' || p_temp_table || '(MEMBER, ';

Line: 22

    tableinsert := tableinsert || ' LEVEL_TYPE,  MEMBER_ORDER,  USER_ID, ';

Line: 30

      tableinsert := tableinsert || p_dimensions(counter);

Line: 39

        tableinsert := tableinsert || ')';

Line: 42

        tableinsert := tableinsert || ',  ';

Line: 48

    EXECUTE IMMEDIATE tableinsert || tablevalues;

Line: 71

    tableSelect  VARCHAR2(5000);

Line: 87

       SELECT DISTINCT(Dimension) bulk collect into dimensions
          FROM zpb_solve_output_levels
          WHERE ANALYSIS_CYCLE_ID = p_ac_id
          ORDER BY Dimension;

Line: 98

       tableSelect := 'DELETE FROM ' || p_temp_table || ' WHERE USER_ID = ' ;

Line: 99

       tableSelect := tableSelect || p_userid || ' AND LEVEL_TYPE = ''' || INPUT_TYPE || '''';

Line: 100

       EXECUTE IMMEDIATE tableSelect;

Line: 104

       tableSelect := 'SELECT DISTINCT def.member, def.member_order, ';

Line: 105

       tableSelect := tableSelect || ' nvl(input.dimension, ';

Line: 106

       tableSelect := tableSelect || '''' || '-'|| '''';

Line: 107

       tableSelect := tableSelect || ') AS dimension,';

Line: 108

       tableSelect := tableSelect || ' levellookup.OBJECT_LONG_LABEL
                                       AS input_level, def.SOURCE_TYPE ';

Line: 110

       tableSelect := tableSelect || ' FROM  ((zpb_solve_member_defs def ';

Line: 111

       tableSelect := tableSelect || ' LEFT OUTER JOIN zpb_solve_input_levels input
        ON def.member = input.member
        AND def.ANALYSIS_CYCLE_ID = input.ANALYSIS_CYCLE_ID)
        LEFT OUTER JOIN ZPB_SESSION_METADATA_LABELS levellookup ';

Line: 115

        tableSelect := tableSelect ||
          'ON levellookup.dimension = input.dimension
          AND levellookup.OBJECT_AW_NAME = input.input_level ';

Line: 118

          tableSelect := tableSelect  || ' AND levellookup.USER_ID = ' ||  p_userid   ;

Line: 119

          tableSelect := tableSelect  || ' AND levellookup.OBJECT_TYPE = ';

Line: 120

          tableSelect := tableSelect  || '''' || 'LEVEL' || '''' || ')';

Line: 121

        tableSelect := tableSelect || ' WHERE def.ANALYSIS_CYCLE_ID = ';

Line: 122

        tableSelect := tableSelect || p_ac_id;

Line: 124

        tableSelect := tableSelect  || ' ORDER BY 1, 3';

Line: 126

       OPEN c4 FOR tableSelect;

Line: 158

   tableSelect := 'SELECT OBJECT_LONG_LABEL' ||
     ' FROM ZPB_SESSION_METADATA_LABELS' ||
     ' WHERE USER_ID = ' || p_userid ||
     ' AND OBJECT_TYPE =  ''' || 'DIMENSION' || '''' ||
     ' AND OBJECT_AW_NAME IN' ||
     ' (SELECT Dimension FROM ZPB_SOLVE_OUTPUT_LEVELS' ||
     ' WHERE ANALYSIS_CYCLE_ID = ' || p_ac_id || ')' ||
     ' ORDER BY Dimension';

Line: 167

   OPEN labelCursor FOR tableSelect;

Line: 169

   tableSelect := 'SELECT solvetemp.MEMBER as member, memberlookup.';

Line: 170

   tableSelect := tableSelect || p_view_long_lbl_column || ' as MemberName, ';

Line: 174

     tableselect := tableselect || 'solvetemp.' || dimensions(indx);

Line: 176

       tableselect := tableselect || ',  ';

Line: 180

   tableselect := tableselect || ' FROM ' ||  p_temp_table || ' solvetemp, ';

Line: 181

   tableselect := tableselect || p_view_dim_name || ' memberlookup ';

Line: 182

   tableselect := tableselect || ' WHERE solvetemp.LEVEL_TYPE = ''' || INPUT_TYPE || '''';

Line: 183

   tableselect := tableselect || ' AND solvetemp.MEMBER = memberlookup.';

Line: 184

   tableselect := tableselect || p_view_member_column;

Line: 185

   tableselect := tableselect || ' ORDER BY solvetemp.MEMBER_ORDER';

Line: 187

   OPEN dataCursor FOR tableSelect;

Line: 211

    tableSelect  VARCHAR2(5000);

Line: 227

      SELECT DISTINCT(Dimension) bulk collect into dimensions
          FROM zpb_solve_output_levels
          WHERE ANALYSIS_CYCLE_ID = p_ac_id
          ORDER BY Dimension;

Line: 238

       tableSelect := 'DELETE FROM ' || p_temp_table || ' WHERE USER_ID = ' ;

Line: 239

       tableSelect := tableSelect || p_userid || ' AND LEVEL_TYPE = ''' || OUTPUT_TYPE || '''';

Line: 240

       EXECUTE IMMEDIATE tableSelect;

Line: 244

    tableSelect := 'SELECT distinct output.member, output.member_order, ';

Line: 245

    tableSelect := tableSelect || ' output.dimension AS dimension,
       output.hierarchy AS hierarchy,
       levellookup.OBJECT_LONG_LABEL AS output_level
     FROM  zpb_solve_output_levels output ';

Line: 249

     tableSelect := tableSelect || ' INNER JOIN ZPB_SESSION_METADATA_LABELS levellookup
      ON levellookup.dimension = output.dimension
      AND levellookup.OBJECT_AW_NAME = output.output_level
     WHERE output.ANALYSIS_CYCLE_ID = ';

Line: 253

     tableSelect := tableSelect  || p_ac_id;

Line: 254

     tableSelect := tableSelect  || ' AND levellookup.USER_ID = ' ||  p_userid   ;

Line: 255

     tableSelect := tableSelect  || ' AND levellookup.OBJECT_TYPE = ';

Line: 256

     tableSelect := tableSelect  || '''' || 'LEVEL' || '''';

Line: 257

     tableSelect := tableSelect  || ' ORDER BY output.member, output.dimension';

Line: 259

      OPEN c4 FOR tableSelect;

Line: 290

   tableSelect := 'SELECT OBJECT_LONG_LABEL' ||
     ' FROM ZPB_SESSION_METADATA_LABELS' ||
     ' WHERE USER_ID = ' || p_userid ||
     ' AND OBJECT_TYPE =  ''' || 'DIMENSION' || '''' ||
     ' AND OBJECT_AW_NAME IN' ||
     ' (SELECT Dimension FROM ZPB_SOLVE_OUTPUT_LEVELS' ||
     ' WHERE ANALYSIS_CYCLE_ID = ' || p_ac_id || ')' ||
     ' ORDER BY Dimension';

Line: 299

   OPEN labelCursor FOR tableSelect;

Line: 301

   tableSelect := 'SELECT solvetemp.MEMBER as member, memberlookup.';

Line: 302

   tableSelect := tableSelect || p_view_long_lbl_column || ' as MemberName, ';

Line: 306

     tableselect := tableselect || 'solvetemp.' || dimensions(indx);

Line: 308

       tableselect := tableselect || ',  ';

Line: 312

   tableselect := tableselect || ' FROM ' ||  p_temp_table || ' solvetemp, ';

Line: 313

   tableselect := tableselect || p_view_dim_name || ' memberlookup ';

Line: 314

   tableselect := tableselect || ' WHERE solvetemp.LEVEL_TYPE = ''' || OUTPUT_TYPE || '''';

Line: 315

   tableselect := tableselect || ' AND solvetemp.MEMBER = memberlookup.';

Line: 316

   tableselect := tableselect || p_view_member_column;

Line: 317

   tableselect := tableselect || ' ORDER BY solvetemp.MEMBER_ORDER';

Line: 319

   OPEN dataCursor FOR tableSelect;