APPS.PQH_REFRESH_DATA SQL Statements

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

Line: 69

     select legislation_code
     from per_business_groups
     where business_group_id = p_business_group_id;

Line: 236

     l_select_stmt           t_where_clause_typ;

Line: 262

      SELECT DISTINCT tca.transaction_table_route_id,
                      tr.shadow_table_route_id,
                      att.master_table_route_id
      FROM  pqh_attributes att,   pqh_table_route tr,
		pqh_txn_category_attributes tca
      WHERE  tca.transaction_table_route_id  = tr.table_route_id
	AND  att.attribute_id = tca.attribute_id
        AND  tca.transaction_category_id = p_txn_category_id
        AND  tr.shadow_table_route_id IS NOT NULL
        AND  tr.table_alias  = DECODE(p_refresh_criteria, 'A', tr.table_alias,
                                                              p_refresh_criteria);

Line: 278

      SELECT att.refresh_col_name, nvl(tca.refresh_flag,'N') ,
	     att.column_type,
	     nvl(at2.attribute_name, att.attribute_name) attribute_name
      FROM   pqh_attributes_vl att, pqh_txn_category_attributes tca,
             pqh_attributes_vl at2
      WHERE   att.attribute_id = tca.attribute_id
        AND  att.column_name = at2.column_name(+)
	AND  att.master_table_route_id = at2.master_table_route_id(+)
	AND  at2.legislation_code(+) = l_legislation_code
        AND  tca.transaction_category_id     = p_txn_category_id
        AND  tca.transaction_table_route_id  = p_txn_tab_id
        AND  att.master_table_route_id       = p_mas_tab_id
       ORDER BY tca.refresh_flag DESC, att.attribute_name;

Line: 294

        SELECT tr.from_clause, tr.where_clause
        FROM pqh_table_route tr
        WHERE tr.table_route_id = p_tab_id ;

Line: 321

     l_select_stmt           := '';

Line: 331

     l_all_txn_rows_array.DELETE;

Line: 332

     l_ordered_txn_row.DELETE;

Line: 333

     l_all_shd_rows_array.DELETE;

Line: 334

     l_all_main_rows_array.DELETE;

Line: 335

     g_refresh_tab.DELETE;

Line: 336

     g_refresh_tab_all.DELETE;

Line: 374

   build_dynamic_select
  ( p_flag          => 'A',
    p_select_stmt   =>  l_select_stmt,
    p_tot_columns   =>  l_tot_txn_columns );

Line: 380

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,1,50), 16);

Line: 381

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,51,50), 16);

Line: 382

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,101,50), 16);

Line: 383

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,151,50), 16);

Line: 384

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,201,50), 16);

Line: 385

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,251,50), 16);

Line: 386

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,301,50), 16);

Line: 387

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,351,50), 16);

Line: 388

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,401,50), 16);

Line: 389

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,451,50), 16);

Line: 390

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,501,50), 16);

Line: 391

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,551,50), 16);

Line: 392

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,601,50), 16);

Line: 393

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,651,50), 16);

Line: 394

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,701,50), 16);

Line: 395

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,751,50), 16);

Line: 396

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,801,50), 16);

Line: 397

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,851,50), 16);

Line: 398

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,901,50), 16);

Line: 399

  hr_utility.set_location('Txn Sel : '||substr(l_select_stmt,951,50), 16);

Line: 429

(p_select_stmt      => l_select_stmt,
 p_from_clause      => l_from_clause_txn,
 p_where_clause     => l_rep_where_clause_txn,
 p_total_columns    => l_tot_txn_columns,
 p_total_rows       => l_tot_txn_rows,
 p_all_txn_rows     => l_all_txn_rows_array );

Line: 478

   build_dynamic_select
  ( p_flag          => 'R',
    p_select_stmt   =>  l_select_stmt,
    p_tot_columns   =>  l_tot_shd_columns );

Line: 484

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,1,50), 16);

Line: 485

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,51,50), 16);

Line: 486

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,101,50), 16);

Line: 487

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,151,50), 16);

Line: 488

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,201,50), 16);

Line: 489

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,251,50), 16);

Line: 490

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,301,50), 16);

Line: 491

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,351,50), 16);

Line: 492

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,401,50), 16);

Line: 493

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,451,50), 16);

Line: 494

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,501,50), 16);

Line: 495

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,551,50), 16);

Line: 496

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,601,50), 16);

Line: 497

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,651,50), 16);

Line: 498

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,701,50), 16);

Line: 499

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,751,50), 16);

Line: 500

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,801,50), 16);

Line: 501

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,851,50), 16);

Line: 502

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,901,50), 16);

Line: 503

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,951,50), 16);

Line: 504

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,1001,50), 16);

Line: 505

  hr_utility.set_location('Shd Sel : '||substr(l_select_stmt,1051,50), 16);

Line: 535

(p_select_stmt      => l_select_stmt,
 p_from_clause      => l_from_clause_shd,
 p_where_clause     => l_rep_where_clause_shd,
 p_total_columns    => l_tot_shd_columns,
 p_total_rows       => l_tot_shd_rows,
 p_all_txn_rows     => l_all_shd_rows_array );

Line: 561

   build_dynamic_select
  ( p_flag          => 'R',
    p_select_stmt   =>  l_select_stmt,
    p_tot_columns   =>  l_tot_main_columns );

Line: 568

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,1,50), 16);

Line: 569

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,51,50), 16);

Line: 570

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,101,50), 16);

Line: 571

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,151,50), 16);

Line: 572

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,201,50), 16);

Line: 573

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,251,50), 16);

Line: 574

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,301,50), 16);

Line: 575

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,351,50), 16);

Line: 576

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,401,50), 16);

Line: 577

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,451,50), 16);

Line: 578

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,501,50), 16);

Line: 579

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,551,50), 16);

Line: 580

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,601,50), 16);

Line: 581

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,651,50), 16);

Line: 582

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,701,50), 16);

Line: 583

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,751,50), 16);

Line: 584

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,801,50), 16);

Line: 585

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,851,50), 16);

Line: 586

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,901,50), 16);

Line: 587

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,951,50), 16);

Line: 588

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,1001,50), 16);

Line: 589

  hr_utility.set_location('Main Sel : '||substr(l_select_stmt,1051,50), 16);

Line: 626

(p_select_stmt      => l_select_stmt,
 p_from_clause      => l_from_clause_main,
 p_where_clause     => l_rep_where_clause_main,
 p_total_columns    => l_tot_main_columns,
 p_total_rows       => l_tot_main_rows,
 p_all_txn_rows     => l_all_main_rows_array );

Line: 652

     LOOP thru the g_refresh_tab and update the necessary columns with new values
 */

     FOR k IN NVL(g_refresh_tab.FIRST,0)..NVL(g_refresh_tab.LAST,-1)
     LOOP
        IF g_refresh_tab(k).updt_flag = 'Y' THEN
          -- call the update_table procedure to updt tables
           update_tables
           (p_column_name           => g_refresh_tab(k).column_name,
            p_column_type           => g_refresh_tab(k).column_type,
            p_column_val            => g_refresh_tab(k).main_val,
            p_from_clause_txn       => l_from_clause_txn,
            p_from_clause_shd       => l_from_clause_shd,
            p_rep_where_clause_shd  => l_rep_where_clause_shd);

Line: 729

    || PROCEDURE : build_dynamic_select
    ||
    ------------------------------------------------------------------*/

PROCEDURE build_dynamic_select
  ( p_flag           IN  VARCHAR2,
    p_select_stmt    OUT NOCOPY t_where_clause_typ,
    p_tot_columns    OUT NOCOPY NUMBER )  IS

/*
   p_flag has 2 values
   'A' means select all columns from the array , this is for txn table select
   'R' means select ONLY those columns where refresh_flag i.e column 2 in array
       is 'Y' , this is for shadow and master table select
    Depending on the column_type we will format the front and back packing string
*/

--
-- local variables
--
 l_proc          varchar2(72) := g_package||'build_dynamic_select';

Line: 759

   p_select_stmt := 'SELECT ';

Line: 779

           p_select_stmt := p_select_stmt||
                            l_front||g_refresh_tab(i).column_name||l_back||' ';

Line: 785

               p_select_stmt := p_select_stmt||' ,';

Line: 790

               p_select_stmt := p_select_stmt||
                                l_front||g_refresh_tab(i).column_name||l_back||' ';

Line: 796

                   p_select_stmt := p_select_stmt||' ,';

Line: 803

  p_select_stmt := rtrim(p_select_stmt,',');

Line: 809

    p_select_stmt    := null;

Line: 815

END build_dynamic_select;

Line: 1194

(p_select_stmt      IN   t_where_clause_typ,
 p_from_clause      IN   pqh_table_route.from_clause%TYPE,
 p_where_clause     IN   pqh_table_route.where_clause%TYPE,
 p_total_columns    IN   NUMBER,
 p_total_rows       OUT NOCOPY  NUMBER,
 p_all_txn_rows     OUT NOCOPY  DBMS_SQL.VARCHAR2_TABLE )
 IS
/*
  This procedure will get all rows of the table and populate the array
  The OUT array p_all_txn_rows will have the following data .
  eg:  table has  3 rows and 3 columns then array has following value
  r1.c1, r2.c1, r3.c1, r2.c1, r2.c2, r2.c3, r3.c1, r3.c2, r3.c3

*/

--
-- local variables
--
 l_proc            varchar2(72) := g_package||'get_all_rows';

Line: 1226

  l_qry_string := p_select_stmt||' FROM '||
                  p_from_clause||' WHERE '||
                  p_where_clause ;

Line: 1374

    Y =>  update column
    N => don't update column as NO change
    C => don't update column as the USER HAS CHANGED THE COLUMN WHICH CAN BE REFRESHED
    we will use 'C' to set visual attribute of item in the TXN form
    as we plan to give provision to user to refresh a refreshable column with right mouse
    click. This new visual attribute will wrn the user that he or someone who routed this txn to him
    has intentionally changed this value, so be cautious before you refresh this column
*/

--
-- local variables
--
 l_proc            varchar2(72) := g_package||'compute_updt_flag';

Line: 1445

    || PROCEDURE : update_tables
    ||
    ------------------------------------------------------------------*/


PROCEDURE update_tables
(p_column_name           IN pqh_attributes.column_name%TYPE,
 p_column_type           IN pqh_attributes.column_type%TYPE,
 p_column_val            IN VARCHAR2,
 p_from_clause_txn       IN pqh_table_route.from_clause%TYPE,
 p_from_clause_shd       IN pqh_table_route.from_clause%TYPE,
 p_rep_where_clause_shd  IN pqh_table_route.where_clause%TYPE )
IS
/*
  This procedure will update the txn and shadow tables with the new value.
  As the shadow and txn tables are identical, we use the wwhere clause of shadow
  which uniquely identifies only ONE row
*/
--
-- local variables
--
 l_proc            varchar2(72) := g_package||'update_tables';

Line: 1500

         update TRANSACTION TABLE
 */
  -- construct the updt stmt
  l_stmt_str  := ''; -- initialize string

Line: 1504

  l_stmt_str := 'UPDATE '||p_from_clause_txn||l_where_clause ;

Line: 1506

  hr_utility.set_location('Update Statement ',10);

Line: 1557

         update SHADOW TABLE
 */
  -- construct the updt stmt
  l_stmt_str  := ''; -- initialize string

Line: 1561

  l_stmt_str := 'UPDATE '||p_from_clause_shd||l_where_clause ;

Line: 1576

END update_tables;

Line: 1603

  SELECT tca.form_column_name
  FROM   pqh_attributes att, pqh_txn_category_attributes tca
  WHERE  att.attribute_id = tca.attribute_id
    AND  tca.transaction_category_id = g_txn_category_id
    AND  att.refresh_col_name = p_column_name;