DBA Data[Home] [Help]

APPS.BIS_VG_LOOKUP SQL Statements

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

Line: 106

     bis_vg_log.update_failure_log( x_error_tbl
				    , x_return_status
				    , x_error_Tbl
				    );
Line: 125

    x_column_table.DELETE;
Line: 146

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 155

FUNCTION : get_select_statement
  PARAMETERS:
  1. p_lookup_table       lookup table name
  2. p_lookup_type        lookup type;
Line: 163

  COMMENT  : Call this function to get the select statement for lookup table
             information to a view.
EXCEPTION : None
  ===========================================================================*/
FUNCTION  get_select_statement
    ( p_lookup_table  IN  VARCHAR2
    , p_lookup_type   IN  VARCHAR2
    , p_lookup_column IN  VARCHAR2
    , x_return_status       OUT VARCHAR2
    , x_error_Tbl           OUT BIS_VG_UTIL.Error_Tbl_Type
    )
RETURN VARCHAR2
IS
l_select       VARCHAR2(1000);
Line: 178

  BIS_DEBUG_PUB.Add('> get_select_statement');
Line: 181

  l_select := 'select lookup_code, '||p_lookup_column;
Line: 182

  l_select := l_select || ' from '||p_lookup_table;
Line: 183

  l_select := l_select || ' where lookup_type = '''||p_lookup_type||'''';
Line: 185

  BIS_DEBUG_PUB.Add(' l_select = '||l_select);
Line: 187

  BIS_DEBUG_PUB.Add('< get_select_statement');
Line: 188

  RETURN l_select;
Line: 203

	  , p_error_proc_name   => G_PKG_NAME||'.get_select_statement'
	  , p_error_table       => x_error_tbl
	  , x_error_table       => x_error_tbl
	  );
Line: 207

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 213

END get_select_statement;
Line: 219

  2. p_select             select statement for the lookup table
  3. x_Select_Table       table of varchars to hold additional select
                          (select table)
  4. x_return_status    error or normal
  5. x_error_Tbl        table of error messages
--
  COMMENT   : Call this procedure to add a particular decode lookup
              information to a view.
EXCEPTION : None
  ===========================================================================*/
PROCEDURE write_decode_statement
    ( p_expr          IN  VARCHAR2
    , p_select        IN  VARCHAR2
    , x_Select_Table  OUT BIS_VG_TYPES.View_Text_Table_Type
    , x_return_status       OUT VARCHAR2
    , x_error_Tbl           OUT BIS_VG_UTIL.Error_Tbl_Type
    )
IS
l_cursor_id         INTEGER;
Line: 241

l_select_table      BIS_VG_TYPES.View_Text_Table_Type;
Line: 256

  dbms_sql.parse(l_cursor_id, p_select, dbms_sql.V7);
Line: 288

           x_select_table(x_select_table.COUNT + 1) := ', ';
Line: 291

      x_select_table(x_select_table.COUNT + 1) := 'DECODE';
Line: 294

                                   , l_select_table
				   , x_return_status
				   , x_error_Tbl
                                   );
Line: 298

      bis_vg_util.concatenate_Tables( x_select_table
                                    , l_select_table
                                    , x_select_table
				    , x_return_status
				    , x_error_Tbl
                                    );
Line: 313

    x_select_table(x_select_table.COUNT + 1) := ', '|| ''''
                                                    || l_lookup_code
                                                    || '''';
Line: 319

    x_select_table(x_select_table.COUNT + 1) := ', '|| ''''
                                                    || l_meaning
                                                    || '''';
Line: 327

    x_select_table(x_select_table.COUNT + 1) := 'NULL';
Line: 330

    x_select_table(x_select_table.COUNT + 1) := ', NULL )';
Line: 334

      x_select_table(x_select_table.COUNT + 1) := ')';
Line: 361

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 377

  5. x_Select_Table       table of varchars to hold additional select
                          (select table)
  6. x_return_status    error or normal
  7. x_error_Tbl        table of error messages
--
  COMMENT   : Call this procedure to add a particular decode lookup
              information to a view.
EXCEPTION : None
  ===========================================================================*/
PROCEDURE put_decode_statement
    ( p_expr          IN  VARCHAR2
    , p_lookup_table  IN  VARCHAR2
    , p_lookup_type   IN  VARCHAR2
    , p_lookup_column IN  VARCHAR2
    , x_Select_Table  OUT BIS_VG_TYPES.View_Text_Table_Type
    , x_return_status       OUT VARCHAR2
    , x_error_Tbl           OUT BIS_VG_UTIL.Error_Tbl_Type
    )
IS
l_select       VARCHAR2(1000);
Line: 405

  l_select := get_select_statement( p_lookup_table
                                     , p_lookup_type
                                     , p_lookup_column
				     , x_return_status
				     , x_error_Tbl
                                     );
Line: 413

  BIS_DEBUG_PUB.Add(' l_select = '||l_select);
Line: 417

                         , l_select
			 , x_select_table
			 , x_return_status
			 , x_error_Tbl
			   );
Line: 432

	 , p_value2 => l_select
	 , p_error_table       => x_error_tbl
	 , x_error_table       => x_error_tbl
	 );
Line: 436

     bis_vg_log.update_failure_log( x_error_tbl
				    , x_return_status
				    , x_error_Tbl
				    );
Line: 463

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 480

  6. x_Select_Table       table of varchars to hold additional select
  7. x_return_status    error or normal
  8. x_error_Tbl        table of error messages
                          (select table)
--
  COMMENT   : Call this procedure to add a particular decode lookup
              information to a view for the given language.
EXCEPTION : None
  ===========================================================================*/
PROCEDURE put_decode_statement_lang
    ( p_expr          IN  VARCHAR2
    , p_lookup_table  IN  VARCHAR2
    , p_lookup_type   IN  VARCHAR2
    , p_lookup_column IN  VARCHAR2
    , p_language      IN  VARCHAR2
    , x_Select_Table  OUT BIS_VG_TYPES.View_Text_Table_Type
    , x_return_status       OUT VARCHAR2
    , x_error_Tbl           OUT BIS_VG_UTIL.Error_Tbl_Type
    )
IS
l_select      VARCHAR2(1000);
Line: 517

  l_select := get_select_statement( p_lookup_table
                                     , p_lookup_type
                                     , p_lookup_column
				     , x_return_status
				     , x_error_Tbl
                                       );
Line: 524

  l_select := l_select || ' and language = '''|| p_language ||'''';
Line: 526

  BIS_DEBUG_PUB.Add(' l_select = '||l_select);
Line: 530

                         , l_select
			 , x_select_table
			 , x_return_status
			 , x_error_Tbl
			 );
Line: 555

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 570

  5. x_Select_Table       table of varchars to hold additional select
                          (select table)
  6. x_return_status    error or normal
  7. x_error_Tbl        table of error messages
--
  COMMENT   : Call this procedure to add a particular decode lookup
              information to a view.
EXCEPTION : None
  ===========================================================================*/
PROCEDURE put_decode_statement_languages
    ( p_expr          IN  VARCHAR2
    , p_lookup_table  IN  VARCHAR2
    , p_lookup_type   IN  VARCHAR2
    , p_lookup_column IN  VARCHAR2
    , x_Select_Table  OUT BIS_VG_TYPES.View_Text_Table_Type
    , x_return_status       OUT VARCHAR2
    , x_error_Tbl           OUT BIS_VG_UTIL.Error_Tbl_Type
    )
IS
l_select      VARCHAR2(1000);
Line: 599

  l_select := 'select language from :table_name' ||
                  'where lookup_type = :type';
Line: 603

  dbms_sql.parse(l_cursor_id, l_select, dbms_sql.NATIVE);
Line: 616

  x_select_table(x_select_table.COUNT + 1) := 'DECODE';
Line: 617

  x_select_table(x_select_table.COUNT + 1) := '( USERENV(''LANG'')';
Line: 624

     x_select_table(x_select_table.COUNT + 1) := ', '||l_language;
Line: 632

			       , x_Select_Table
			       , x_return_status
			       , x_error_Tbl
			       );
Line: 640

  x_select_table(x_select_table.COUNT + 1) := ', NULL )';
Line: 667

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 695

   SELECT 1
   FROM user_tab_columns
   WHERE table_name=p_lookup_table
        AND column_name='LANGUAGE';
Line: 737

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 747

PROCEDURE : add_select_info
  PARAMETERS:
  1. p_expr               pl/sql expression for decode
  2. p_lookup_table       lookup table name
  3. p_lookup_type        lookup type;
Line: 753

  5. x_Select_Table       table of varchars to hold additional select
                          (select table)
  6. x_return_status    error or normal
  7. x_error_Tbl        table of error messages
--
  COMMENT   : Call this procedure to add a particular lookup select
              information to a view.
EXCEPTION : None
  ===========================================================================*/
PROCEDURE add_select_info
   ( p_expr          IN  VARCHAR2
   , p_lookup_table  IN  VARCHAR2
   , p_lookup_type   IN  VARCHAR2
   , p_lookup_column IN  VARCHAR2
   , x_Select_Table  OUT BIS_VG_TYPES.View_Text_Table_Type
   , x_return_status       OUT VARCHAR2
   , x_error_Tbl           OUT BIS_VG_UTIL.Error_Tbl_Type
   )
IS
l_lang BOOLEAN := FALSE;
Line: 774

  BIS_DEBUG_PUB.Add('> add_select_info');
Line: 787

                                      , x_select_table
				      , x_return_status
				      , x_error_Tbl
                                      );
Line: 800

                            , x_select_table
			    , x_return_status
			    , x_error_Tbl
                            );
Line: 805

  BIS_DEBUG_PUB.Add('< add_select_info');
Line: 820

	  , p_error_proc_name   => G_PKG_NAME||'.add_select_info'
	  , p_error_table       => x_error_tbl
	  , x_error_table       => x_error_tbl
	  );
Line: 824

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 830

END add_select_info;
Line: 850

PROCEDURE parse_LA_select
( p_View_Select_Table 	IN  BIS_VG_TYPES.View_Text_Table_Type
, p_Select_Pointer    	IN  BIS_VG_TYPES.View_Character_Pointer_Type
, x_expr          	OUT VARCHAR2
, x_lookup_table  	OUT VARCHAR2
, x_lookup_type   	OUT VARCHAR2
, x_lookup_column 	OUT VARCHAR2
, x_Select_Pointer    	OUT BIS_VG_TYPES.View_Character_Pointer_Type
, x_return_status       OUT VARCHAR2
, x_error_Tbl           OUT BIS_VG_UTIL.Error_Tbl_Type
)
IS
l_str           VARCHAR2(2000);
Line: 866

   BIS_DEBUG_PUB.Add('> parse_LA_select');
Line: 868

   l_str := bis_vg_parser.Skip_Tag( p_View_Select_Table
                                   , p_select_pointer
                                   , x_select_pointer
				   , x_return_status
				   , x_error_Tbl
                                   );
Line: 874

  l_str := bis_vg_parser.get_expression( p_View_Select_Table
                                                 , p_Select_Pointer
                                                 , l_tmp_pointer
						 , x_return_status
						 , x_error_Tbl
					       );
Line: 882

				 , x_select_pointer
				 , x_return_status
				 , x_error_Tbl
				 )
      ) THEN
     BIS_VG_UTIL.Add_Error_message
       ( p_error_msg_name => bis_vg_lookup.LAT_COL_TAG_EXP_NO_EXP_MSG
	 , p_error_proc_name   => G_PKG_NAME||'.parse_LA_select'
	 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
	 , p_token1          => 'tag'
	 , p_value1         =>  l_str
	 , p_error_table       => x_error_tbl
	 , x_error_table       => x_error_tbl
	 );
Line: 896

     bis_vg_log.update_failure_log( x_error_tbl
				    , x_return_status
				    , x_error_Tbl
				    );
Line: 904

      ( p_View_Select_Table
	, l_tmp_pointer
	, x_return_status
	, x_error_Tbl
	);
Line: 911

  x_expr := bis_vg_parser.get_token_increment_pointer( p_View_Select_Table
                                                        , x_select_pointer
                                                        , ':'
                                                        , x_select_pointer
							, x_return_status
							, x_error_Tbl
                                                        );
Line: 923

				 , x_select_pointer
				 , x_return_status
				 , x_error_Tbl
				 )
      ) THEN
     BIS_VG_UTIL.Add_Error_message
       ( p_error_msg_name => bis_vg_lookup.LAT_COL_TAG_EXP_NO_EXP_MSG
	 , p_error_proc_name   => G_PKG_NAME||'.parse_LA_select'
	 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
	 , p_token1          => 'tag'
	 , p_value1         =>  l_str
	 , p_error_table       => x_error_tbl
	 , x_error_table       => x_error_tbl
	 );
Line: 937

     bis_vg_log.update_failure_log( x_error_tbl
				    , x_return_status
				    , x_error_Tbl
				    );
Line: 950

                                 ( p_View_Select_Table
                                    , x_select_pointer
                                    , ':'''
                                    , x_select_pointer
				    , x_return_status
				    , x_error_Tbl
                                    );
Line: 963

				 , x_select_pointer
				 , x_return_status
				 , x_error_Tbl
				 )
      ) THEN
     BIS_VG_UTIL.Add_Error_message
       ( p_error_msg_name => bis_vg_lookup.LAT_SEL_TAG_EXP_NO_TABLE_MSG
	 , p_error_proc_name   => G_PKG_NAME||'.parse_LA_select'
	 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
	 , p_token1          => 'tag'
	 , p_value1         =>  l_str
	 , p_error_table       => x_error_tbl
	 , x_error_table       => x_error_tbl
	 );
Line: 977

     bis_vg_log.update_failure_log( x_error_tbl
				    , x_return_status
				    , x_error_Tbl
				    );
Line: 987

                                ( p_View_Select_Table
                                   , x_select_pointer
                                   , ':'''
                                   , x_select_pointer
				   , x_return_status
				   , x_error_Tbl
                                   );
Line: 1000

				 , x_select_pointer
				 , x_return_status
				 , x_error_Tbl
				 )
      ) THEN
     BIS_VG_UTIL.Add_Error_message
       ( p_error_msg_name => bis_vg_lookup.LAT_SEL_TAG_EXP_NO_TYPE_MSG
	 , p_error_proc_name   => G_PKG_NAME||'.parse_LA_select'
	 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
	 , p_token1          => 'tag'
	 , p_value1         =>  l_str
	 , p_error_table       => x_error_tbl
	 , x_error_table       => x_error_tbl
	 );
Line: 1014

     bis_vg_log.update_failure_log( x_error_tbl
				    , x_return_status
				    , x_error_Tbl
				    );
Line: 1024

                                  ( p_View_Select_Table
                                     , x_select_pointer
                                     , ''''
                                     , x_select_pointer
				     , x_return_status
				     , x_error_Tbl
                                     );
Line: 1038

	 , p_error_proc_name   => G_PKG_NAME||'.parse_LA_select'
	 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
	 , p_token1          => 'tag'
	 , p_value1         =>  l_str
	 , p_error_table       => x_error_tbl
	 , x_error_table       => x_error_tbl
	 );
Line: 1045

     bis_vg_log.update_failure_log( x_error_tbl
				    , x_return_status
				    , x_error_Tbl
				    );
Line: 1052

    BIS_DEBUG_PUB.Add('Parse_LA_Select  Tag = '|| l_str);
Line: 1055

    BIS_DEBUG_PUB.Add('< parse_LA_select');
Line: 1069

	  , p_error_proc_name   => G_PKG_NAME||'.parse_LA_select'
	  , p_error_table       => x_error_tbl
	  , x_error_table       => x_error_tbl
	  );
Line: 1073

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 1080

END parse_LA_select;
Line: 1103

     select object_type
          from user_objects
          where object_name = UPPER(p_table);
Line: 1108

     select 1
     from   user_synonyms syn, all_tab_columns col
     where  syn.synonym_name = UPPER(p_table)
     and syn.table_name = col.table_name
     and col.column_name = UPPER(p_column)
     and syn.table_owner = col.owner
     and col.owner = user
     UNION
     select 1
     from user_tab_columns col1
     where col1.table_name =UPPER(p_table)
     and col1.column_name = UPPER(p_column);
Line: 1122

     select 1
     from   all_tab_columns a, user_synonyms u
      where  u.synonym_name = UPPER(p_table)
      and    u.table_name = a.table_name
      and    a.owner = u.table_owner
      and    a.column_name = UPPER(p_column)
     UNION
     select 1
     from  user_tab_columns col1
     where col1.table_name =UPPER(p_table)
     and col1.column_name = UPPER(p_column);
Line: 1179

PROCEDURE put_decode_in_select
   ( p_View_Select_Table IN  BIS_VG_TYPES.View_Text_Table_Type
   , p_Select_Pointer    IN  BIS_VG_TYPES.View_Character_Pointer_Type
   , p_Mode              IN  NUMBER
   , x_Select_Table      OUT BIS_VG_TYPES.View_Text_Table_Type
   , x_Select_Pointer    OUT BIS_VG_TYPES.View_Character_Pointer_Type
   , x_return_status       OUT VARCHAR2
   , x_error_Tbl           OUT BIS_VG_UTIL.Error_Tbl_Type
   )
IS
l_expr          VARCHAR2(2000);
Line: 1196

   BIS_DEBUG_PUB.Add('> put_decode_in_select');
Line: 1198

   parse_LA_select ( p_View_Select_Table
		     , p_Select_Pointer
		     , l_expr
		     , l_lookup_table
		     , l_lookup_type
		     , l_lookup_column
		     , x_Select_Pointer
		     , x_return_status
		     , x_error_Tbl

		     );
Line: 1210

   l_tag := bis_vg_util.get_string ( p_View_Select_Table
				     , p_Select_Pointer
				     , x_Select_Pointer
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 1218

    x_select_table(1) := 'TO_CHAR(NULL)';
Line: 1223

	add_select_info( l_expr
			 , l_lookup_table
			 , l_lookup_type
			 , l_lookup_column
			 , x_select_table
			 , x_return_status
			 , x_error_Tbl
			 );
Line: 1236

	    , p_error_proc_name   => G_PKG_NAME||'.put_decode_in_select'
	    , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
	    , p_token1 =>   'tab'
	    , p_value1 =>   l_lookup_table
	    , p_token2  =>   'col'
	    , p_value2  =>   l_lookup_column
	    , p_token3  =>  'tag'
	    , p_value3 =>  l_tag
	    , p_error_table       => x_error_tbl
	    , x_error_table       => x_error_tbl
	    );
Line: 1247

	bis_vg_log.update_failure_log( x_error_tbl
				       , x_return_status
				       , x_error_Tbl
				       );
Line: 1255

  BIS_DEBUG_PUB.Add('> put_decode_in_select');
Line: 1269

	  , p_error_proc_name   => G_PKG_NAME||'.put_decode_in_select'
	  , p_error_table       => x_error_tbl
	  , x_error_table       => x_error_tbl
	  );
Line: 1273

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );
Line: 1279

END put_decode_in_select;
Line: 1285

  2. p_View_Select_Table  table of varchars to hold SELECT clause of view
  3. p_Mode               mode of execution of the program
  4. p_Column_Pointer     pointer to the lookup column in column table
  5. p_Select_Pointer     pointer to the select clause
  6. x_Column_Table       table of varchars to hold additional columns
  7. x_Select_Table       table of varchars to hold additional columns
  8. x_Column_Pointer     pointer to the character after the delimiter
                          (column table)
  9. x_Select_Pointer     pointer to the character after the delimiter
                          (select table)
 10. x_return_status    error or normal
 11. x_error_Tbl        table of error messages
--
COMMENT   : Call this procedure to add a particular lookup information
            to a view.
EXCEPTION : None
  ===========================================================================*/
PROCEDURE add_Lookup_Info
( p_View_Column_Table IN  BIS_VG_TYPES.View_Text_Table_Type
, p_View_Select_Table IN  BIS_VG_TYPES.View_Text_Table_Type
, p_Mode              IN  NUMBER
, p_Column_Pointer    IN  BIS_VG_TYPES.View_Character_Pointer_Type
, p_Select_Pointer    IN  BIS_VG_TYPES.View_Character_Pointer_Type
, x_Column_Table      OUT BIS_VG_TYPES.View_Text_Table_Type
, x_Select_Table      OUT BIS_VG_TYPES.View_Text_Table_Type
, x_Column_Pointer    OUT BIS_VG_TYPES.View_Character_Pointer_Type
, x_Select_Pointer    OUT BIS_VG_TYPES.View_Character_Pointer_Type
, x_return_status       OUT VARCHAR2
, x_error_Tbl           OUT BIS_VG_UTIL.Error_Tbl_Type
)
IS
--
BEGIN
  BIS_DEBUG_PUB.Add('> add_Lookup_Info :-)');
Line: 1329

  put_decode_in_select( p_view_select_table
                      , p_select_pointer
                      , p_Mode
                      , x_select_table
                      , x_select_pointer
		      , x_return_status
		      , x_error_Tbl
                      );
Line: 1361

      bis_vg_log.update_failure_log( x_error_tbl
				     , x_return_status
				     , x_error_Tbl
				     );