eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIL_DO_L1_LD_OPPTY_DLY_PKG SQL Statements

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

Line: 24


PROCEDURE Delete_Data (
       ERRBUF            IN OUT VARCHAR2
      ,RETCODE           IN OUT VARCHAR2
      ,p_date IN DATE
    ) ;

Line: 32


 PROCEDURE Insert_Data (
       ERRBUF               IN OUT VARCHAR2
      ,RETCODE              IN OUT VARCHAR2
      ,p_date               IN DATE
      ,p_degree             IN NUMBER
    ) ;

Line: 44


      , p_delete_flag IN  VARCHAR2 DEFAULT 'Y'
      , p_degree      IN  VARCHAR2   DEFAULT '4'
      , p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
      , p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
    ) ;

Line: 56


      /*******Main procedure : initializes global variables, deletes and then inserts previous day's data
    ERRBUFF:    error message returned by the proc
    RETCODE:    completion status of the procedure
    p_degree:   parallel degree
    p_debug:    debug mode (yes or no)
    p_trace:    trace mode (yes or no)
  *******/
  PROCEDURE Refresh_Data
    (
        ERRBUF        OUT VARCHAR2
      , RETCODE       OUT VARCHAR2
      , p_degree      IN  VARCHAR2   DEFAULT '4'
      , p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
      , p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
    ) IS

    -- Local variables
    l_collect_for_date DATE     := TRUNC(SYSDATE-1); -- date for which data is collected; one day prior to collection date

Line: 101


    SELECT MAX(collection_date)+1
    INTO l_collection_date_start
    FROM bil_do_l1_ld_oppty_dly;

Line: 112


        , p_delete_flag => 'Y'
        , p_degree      => p_degree
        , p_debug_mode  => p_debug_mode
        , p_trace_mode  => p_trace_mode
       );

Line: 127


        , p_delete_flag => 'Y'
        , p_degree      => p_degree
        , p_debug_mode  => p_debug_mode
        , p_trace_mode  => p_trace_mode
       );

Line: 219


              , p_delete_flag => 'N'
              , p_degree      => p_degree
              , p_debug_mode  => p_debug_mode
             , p_trace_mode  => p_trace_mode
            );

Line: 234


              , p_delete_flag => 'Y'
              , p_degree      => p_degree
              , p_debug_mode  => p_debug_mode
             , p_trace_mode  => p_trace_mode
            );

Line: 273


  Delete data for the specified date
  ERRBUFF:            error message returned by the proc
  RETCODE:            completion status of the procedure
  p_date:             date for which the data will be deleted
  *******/
  PROCEDURE Delete_Data (
              ERRBUF  IN OUT VARCHAR2
             ,RETCODE IN OUT VARCHAR2
             ,p_date  IN DATE
            ) IS

  BEGIN

    DELETE FROM bil_do_l1_ld_oppty_dly
      WHERE collection_date = p_date;

Line: 295


        ERRBUF := ERRBUF ||' Delete_Data:'||sqlcode||' '|| sqlerrm;

Line: 297


  END Delete_Data;

Line: 313


/******* Insert data for the specified day
    ERRBUFF:    error message returned by the proc
    RETCODE:    completion status of the procedure
    p_date:     date for which data will be inserted
    p_degree:   parallel degree
*******/
PROCEDURE Insert_Data (
              ERRBUF            IN OUT VARCHAR2
             ,RETCODE           IN OUT VARCHAR2
             ,p_date            IN DATE
             ,p_degree          IN NUMBER
            ) IS

 l_sysdate DATE := SYSDATE; -- to be used in insert

Line: 327


 l_stime   DATE := SYSDATE; -- time when insert started, to be used by Write_log

Line: 328


 l_insert_stmnt  VARCHAR2(20000);

Line: 329


 l_select_stmnt1 VARCHAR2(20000);

Line: 330


 l_select_stmnt2 VARCHAR2(20000);

Line: 337


  l_insert_stmnt := 'INSERT INTO /*+ APPEND PARALLEL(bld, ' || p_degree || ') */ bil_do_l1_ld_oppty_dly bld';

Line: 338


  l_insert_stmnt := l_insert_stmnt || '(';

Line: 339


  l_insert_stmnt := l_insert_stmnt || ' collection_date';

Line: 340


  l_insert_stmnt := l_insert_stmnt || ', sales_group_id';

Line: 341


  l_insert_stmnt := l_insert_stmnt || ', total_leads_all';

Line: 342


  l_insert_stmnt := l_insert_stmnt || ', open_leads_all';

Line: 343


  l_insert_stmnt := l_insert_stmnt || ', open_leads_day';

Line: 344


  l_insert_stmnt := l_insert_stmnt || ', touched_leads_all';

Line: 345


  l_insert_stmnt := l_insert_stmnt || ', touched_leads_day';

Line: 346


  l_insert_stmnt := l_insert_stmnt || ', converted_leads_all';

Line: 347


  l_insert_stmnt := l_insert_stmnt || ', converted_leads_day';

Line: 348


  l_insert_stmnt := l_insert_stmnt || ', creation_date';

Line: 349


  l_insert_stmnt := l_insert_stmnt || ', created_by';

Line: 350


  l_insert_stmnt := l_insert_stmnt || ', last_update_date';

Line: 351


  l_insert_stmnt := l_insert_stmnt || ', last_updated_by';

Line: 352


  l_insert_stmnt := l_insert_stmnt || ', last_update_login';

Line: 353


  l_insert_stmnt := l_insert_stmnt || ', request_id';

Line: 354


  l_insert_stmnt := l_insert_stmnt || ', program_application_id';

Line: 355


  l_insert_stmnt := l_insert_stmnt || ', program_id';

Line: 356


  l_insert_stmnt := l_insert_stmnt || ', program_update_date';

Line: 357


  l_insert_stmnt := l_insert_stmnt || ')';

Line: 358


  l_select_stmnt1  := 'SELECT /*+ PARALLEL(v, ' || p_degree || ') */';

Line: 359


  l_select_stmnt1  := l_select_stmnt1 || ':p_date';

Line: 360


  l_select_stmnt1  := l_select_stmnt1 ||  '     , nvl(v.sales_group_id, -999) sales_group_id';

Line: 361


  l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.total_leads_all) total_leads_all';

Line: 362


  l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.open_leads_all) open_leads_all';

Line: 363


  l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.open_leads_day) open_leads_day';

Line: 364


  l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.touched_leads_all) touched_leads_all';

Line: 365


  l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.touched_leads_day) touched_leads_day';

Line: 366


  l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.converted_leads_all) converted_leads_all';

Line: 367


  l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.converted_leads_day) converted_leads_day';

Line: 368


  l_select_stmnt1  := l_select_stmnt1 ||  '     , SYSDATE';

Line: 369


  l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_user_id;

Line: 370


  l_select_stmnt1  := l_select_stmnt1 ||  '     , SYSDATE';

Line: 371


  l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_user_id;

Line: 372


  l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_login_id;

Line: 373


  l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_request_id;

Line: 374


  l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_appl_id;

Line: 375


  l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_program_id;

Line: 376


  l_select_stmnt1  := l_select_stmnt1 ||  '     , SYSDATE';

Line: 377


  l_select_stmnt2  := '   FROM ';

Line: 378


  l_select_stmnt2  := l_select_stmnt2 ||  '   (SELECT /*+ PARALLEL(sl, ' || p_degree ||') */';

Line: 379


  l_select_stmnt2  := l_select_stmnt2 ||  '     grp.sales_group_id sales_group_id ';

Line: 380


  l_select_stmnt2  := l_select_stmnt2 ||  '     , COUNT(sl.sales_lead_id) total_leads_all ';

Line: 381


  l_select_stmnt2  := l_select_stmnt2 ||  '    , DECODE(st.opp_open_status_flag, ' || l_quote ||'Y' || l_quote || ', COUNT(sl.sales_lead_id), 0) open_leads_all ';

Line: 382


  l_select_stmnt2  := l_select_stmnt2 ||  '   , DECODE(' || ':p_date' || ', TRUNC(sl.creation_date) , DECODE(st.opp_open_status_flag, ';

Line: 383


  l_select_stmnt2  := l_select_stmnt2 || l_quote || 'Y' || l_quote || ' , COUNT(sl.sales_lead_id), 0) ,0) open_leads_day ';

Line: 384


  l_select_stmnt2  := l_select_stmnt2 || '    , DECODE(st.opp_open_status_flag, '|| l_quote ||'Y' || l_quote ;

Line: 385


  l_select_stmnt2  := l_select_stmnt2 || '    , DECODE(sl.creation_date, sl.last_update_date, 0, COUNT(sl.sales_lead_id))';

Line: 386


  l_select_stmnt2  := l_select_stmnt2 || '       , 0) touched_leads_all ';

Line: 387


  l_select_stmnt2  := l_select_stmnt2 || '     , DECODE(' || ':p_date' || ', TRUNC(sl.last_update_date)';

Line: 388


  l_select_stmnt2  := l_select_stmnt2 || '       , DECODE(st.opp_open_status_flag, ' || l_quote ||'Y' || l_quote;

Line: 389


  l_select_stmnt2  := l_select_stmnt2 || '         , DECODE(sl.creation_date, sl.last_update_date, 0, COUNT(sl.sales_lead_id))';

Line: 390


  l_select_stmnt2  := l_select_stmnt2 || '       , 0) ,0) touched_leads_day ';

Line: 391


  l_select_stmnt2  := l_select_stmnt2 || '     , 0 converted_leads_all ';

Line: 392


  l_select_stmnt2  := l_select_stmnt2 || '     , 0 converted_leads_day ';

Line: 393


  l_select_stmnt2  := l_select_stmnt2 || '   FROM ';

Line: 394


  l_select_stmnt2  := l_select_stmnt2 || '       as_sales_leads sl ';

Line: 395


  l_select_stmnt2  := l_select_stmnt2 || '     , as_statuses_b  st ';

Line: 396


  l_select_stmnt2  := l_select_stmnt2 || '     , bil_do_l1_base_grp_temp grp ';

Line: 397


  l_select_stmnt2  := l_select_stmnt2 || '   WHERE ';

Line: 398


  l_select_stmnt2  := l_select_stmnt2 || '        sl.status_code = st.status_code ';

Line: 399


  l_select_stmnt2  := l_select_stmnt2 || '    AND st.lead_flag = ' || l_quote ||'Y' || l_quote;

Line: 400


  l_select_stmnt2  := l_select_stmnt2 || '    AND st.enabled_flag = ' || l_quote ||'Y' || l_quote;

Line: 401


  l_select_stmnt2  := l_select_stmnt2 || '    AND NVL(sl.deleted_flag, '|| l_quote || 'N' || l_quote || ') <> ' || l_quote ||'Y' || l_quote;

Line: 402


  l_select_stmnt2  := l_select_stmnt2 || '    AND grp.child_sales_group_id = sl.assign_sales_group_id ';

Line: 403


  l_select_stmnt2  := l_select_stmnt2 || '   GROUP BY ';

Line: 404


  l_select_stmnt2  := l_select_stmnt2 || '      grp.sales_group_id ';

Line: 405


  l_select_stmnt2  := l_select_stmnt2 || '    , st.opp_open_status_flag ';

Line: 406


  l_select_stmnt2  := l_select_stmnt2 || '    , sl.creation_date ';

Line: 407


  l_select_stmnt2  := l_select_stmnt2 || '    , sl.last_update_date ';

Line: 408


  l_select_stmnt2  := l_select_stmnt2 || ' UNION ALL ';

Line: 409


  l_select_stmnt2  := l_select_stmnt2 || '      SELECT /*+ PARALLEL(sl, ' || p_degree || ') */';

Line: 410


  l_select_stmnt2  := l_select_stmnt2 || '         grp.sales_group_id sales_group_id ';

Line: 411


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 total_leads_all ';

Line: 412


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 open_leads_all ';

Line: 413


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 open_leads_day ';

Line: 414


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 touched_leads_all ';

Line: 415


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 touched_leads_day ';

Line: 416


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 converted_leads_all ';

Line: 417


  l_select_stmnt2  := l_select_stmnt2 || '       , DECODE(' || ':p_date' || ', TRUNC(slop.creation_date) ';

Line: 418


  l_select_stmnt2  := l_select_stmnt2 || '          , COUNT(DISTINCT sl.sales_lead_id), 0) converted_leads_day ';

Line: 419


  l_select_stmnt2  := l_select_stmnt2 || '     FROM ';

Line: 420


  l_select_stmnt2  := l_select_stmnt2 || '        as_sales_leads sl ';

Line: 421


  l_select_stmnt2  := l_select_stmnt2 || '      , as_sales_lead_opportunity slop ';

Line: 422


  l_select_stmnt2  := l_select_stmnt2 || '     , bil_do_l1_base_grp_temp grp ';

Line: 423


  l_select_stmnt2  := l_select_stmnt2 || '     WHERE ';

Line: 424


  l_select_stmnt2  := l_select_stmnt2 || '        NVL(sl.deleted_flag, ' || l_quote ||'N' || l_quote ||') <> ' || l_quote ||'Y' || l_quote;

Line: 425


  l_select_stmnt2  := l_select_stmnt2 || '    AND sl.sales_lead_id = slop.sales_lead_id ';

Line: 426


  l_select_stmnt2  := l_select_stmnt2 || '    AND grp.child_sales_group_id = sl.assign_sales_group_id';

Line: 427


  l_select_stmnt2  := l_select_stmnt2 || '   AND NOT EXISTS (SELECT sales_lead_id FROM as_sales_lead_opportunity slo';

Line: 428


  l_select_stmnt2  := l_select_stmnt2 || '                     WHERE slo.sales_lead_id = slop.sales_lead_id';

Line: 429


  l_select_stmnt2  := l_select_stmnt2 || '                     AND slo.creation_date < :p_date)';

Line: 430


  l_select_stmnt2  := l_select_stmnt2 || '   GROUP BY grp.sales_group_id';

Line: 431


  l_select_stmnt2  := l_select_stmnt2 || '            , TRUNC(slop.creation_date)';

Line: 433


  l_select_stmnt2  := l_select_stmnt2 || ' UNION ALL ';

Line: 434


  l_select_stmnt2  := l_select_stmnt2 || '      SELECT /*+ PARALLEL(sl, ' || p_degree || ') */';

Line: 435


  l_select_stmnt2  := l_select_stmnt2 || '         grp.sales_group_id sales_group_id ';

Line: 436


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 total_leads_all ';

Line: 437


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 open_leads_all ';

Line: 438


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 open_leads_day ';

Line: 439


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 touched_leads_all ';

Line: 440


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 touched_leads_day ';

Line: 441


  l_select_stmnt2  := l_select_stmnt2 || '       , COUNT(DISTINCT sl.sales_lead_id) converted_leads_all ';

Line: 442


  l_select_stmnt2  := l_select_stmnt2 || '       , 0 converted_leads_day ';

Line: 443


  l_select_stmnt2  := l_select_stmnt2 || '     FROM ';

Line: 444


  l_select_stmnt2  := l_select_stmnt2 || '        as_sales_leads sl ';

Line: 445


  l_select_stmnt2  := l_select_stmnt2 || '      , as_sales_lead_opportunity slop ';

Line: 446


  l_select_stmnt2  := l_select_stmnt2 || '     , bil_do_l1_base_grp_temp grp ';

Line: 447


  l_select_stmnt2  := l_select_stmnt2 || '     WHERE ';

Line: 448


  l_select_stmnt2  := l_select_stmnt2 || '        NVL(sl.deleted_flag, ' || l_quote ||'N' || l_quote ||') <> ' || l_quote ||'Y' || l_quote;

Line: 449


  l_select_stmnt2  := l_select_stmnt2 || '    AND sl.sales_lead_id = slop.sales_lead_id ';

Line: 450


  l_select_stmnt2  := l_select_stmnt2 || '    AND grp.child_sales_group_id = sl.assign_sales_group_id';

Line: 451


  l_select_stmnt2  := l_select_stmnt2 || '   GROUP BY grp.sales_group_id';

Line: 452


  l_select_stmnt2  := l_select_stmnt2 || '            , TRUNC(slop.creation_date)';

Line: 453


  l_select_stmnt2  := l_select_stmnt2 || '   ) V ';

Line: 454


  l_select_stmnt2  := l_select_stmnt2 || '   GROUP BY v.sales_group_id ';

Line: 456


/*dbms_output.put_line(substr(l_insert_stmnt,1,150));

Line: 457


dbms_output.put_line(substr(l_insert_stmnt,151,150));

Line: 458


dbms_output.put_line(substr(l_insert_stmnt,301,150));

Line: 459


dbms_output.put_line(substr(l_insert_stmnt,451,150));

Line: 460


dbms_output.put_line(substr(l_insert_stmnt,601,150));

Line: 461


dbms_output.put_line(substr(l_insert_stmnt,751,150));

Line: 462


dbms_output.put_line(substr(l_insert_stmnt,901,150));*/

Line: 465


/*dbms_output.put_line(substr(l_select_stmnt1,1,150));

Line: 466


dbms_output.put_line(substr(l_select_stmnt1,151,150));

Line: 467


dbms_output.put_line(substr(l_select_stmnt1,301,150));

Line: 468


dbms_output.put_line(substr(l_select_stmnt1,451,150));

Line: 469


dbms_output.put_line(substr(l_select_stmnt1,601,150));

Line: 470


dbms_output.put_line(substr(l_select_stmnt1,751,150));

Line: 471


dbms_output.put_line(substr(l_select_stmnt1,901,150)); */

Line: 473


/* dbms_output.put_line(substr(l_select_stmnt2,1,150));

Line: 474


dbms_output.put_line(substr(l_select_stmnt2,151,150));

Line: 475


dbms_output.put_line(substr(l_select_stmnt2,301,150));

Line: 476


dbms_output.put_line(substr(l_select_stmnt2,451,150));

Line: 477


dbms_output.put_line(substr(l_select_stmnt2,601,150));

Line: 478


dbms_output.put_line(substr(l_select_stmnt2,751,150));

Line: 479


dbms_output.put_line(substr(l_select_stmnt2,901,150));

Line: 480


dbms_output.put_line(substr(l_select_stmnt2,1051,150));

Line: 481


dbms_output.put_line(substr(l_select_stmnt2,1201,150));

Line: 482


dbms_output.put_line(substr(l_select_stmnt2,1351,150));

Line: 483


dbms_output.put_line(substr(l_select_stmnt2,1501,150));

Line: 484


dbms_output.put_line(substr(l_select_stmnt2,1651,150));

Line: 485


dbms_output.put_line(substr(l_select_stmnt2,1801,150));

Line: 486


dbms_output.put_line(substr(l_select_stmnt2,1951,150));

Line: 487


dbms_output.put_line(substr(l_select_stmnt2,2111,150));

Line: 488


dbms_output.put_line(substr(l_select_stmnt2,2261,150));

Line: 489


dbms_output.put_line(substr(l_select_stmnt2,2311,150));

Line: 490


dbms_output.put_line(substr(l_select_stmnt2,2461,150)); */

Line: 492


  EXECUTE IMMEDIATE l_insert_stmnt || l_select_stmnt1 || l_select_stmnt2
  USING
    p_date
  , p_date
  , p_date
  , p_date
  , p_date;

Line: 505


 BIL_DO_UTIL_PKG.Write_Log(p_msg=>'     Rows Inserted: '|| l_row_count,p_stime=>l_stime,p_etime=>SYSDATE, p_debug=>G_Debug);

Line: 508


        BIL_DO_UTIL_PKG.Write_Log(p_msg=>'     No rows Inserted. ', p_force=>'Y');

Line: 515


        ERRBUF := ERRBUF ||' Insert_Data: '||sqlcode||' '|| sqlerrm;

Line: 517


        BIL_DO_UTIL_PKG.Write_Log(p_msg=>' Insert_Data: '||sqlcode||' '|| sqlerrm
           , p_force=> 'Y');

Line: 522


END Insert_Data;

Line: 525


 /******* deletes and then inserts specified day's data
    ERRBUFF:    error message returned by the proc
    RETCODE:    completion status of the procedure
    p_degree:   parallel degree
    p_date  :   date for which data is collected
    p_debug_mode:    debug mode (yes or no)
    p_trace_mode:    trace mode (yes or no)
  *******/

  PROCEDURE Refresh_Data_Day
    (
        ERRBUF        IN OUT VARCHAR2
      , RETCODE       IN OUT VARCHAR2
	  , p_date        IN  DATE
      , p_delete_flag IN  VARCHAR2 DEFAULT 'Y'
      , p_degree      IN  VARCHAR2 DEFAULT '4'
      , p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
      , p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
    ) IS

    -- Local variables
    l_collect_for_date DATE     := p_date; -- date for which data is collected

Line: 552


     IF p_delete_flag = 'Y' THEN
     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Delete Data for collection date: ' || TO_CHAR(l_collect_for_date, 'DD-MON-YYYY'), p_debug=>p_debug_mode);

Line: 554


        Delete_Data (
           ERRBUF       => ERRBUF
          ,RETCODE      => RETCODE
          ,p_date => l_collect_for_date
         );

Line: 562


     BIL_DO_UTIL_PKG.Write_Log('Insert Data for collection date: ' || TO_CHAR(l_collect_for_date, 'DD-MON-YYYY'), p_debug=>p_debug_mode);

Line: 564


     Insert_Data (
           ERRBUF           => ERRBUF
          ,RETCODE          => RETCODE
          ,p_date           => l_collect_for_date
          ,p_degree         => l_degree
         );