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.WIP_BIS_UTZ_ALERT SQL Statements

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

Line: 70


  /* SELECT clause */
  p_select_time            VARCHAR2(1000) := 'to_char(NULL) ';

Line: 72


  p_select_period_setname  VARCHAR2(1000) := 'to_char(NULL) ';

Line: 73


  p_select_org             VARCHAR2(1000) := 'to_char(NULL) ';

Line: 74


  p_select_geo             VARCHAR2(1000) := 'to_char(NULL) ';

Line: 75


  p_select_product         VARCHAR2(1000) := 'to_char(NULL) ';

Line: 90


  p_select_statement VARCHAR2(32767);

Line: 97


    p_select_time  := 'TEMP.period_year ';

Line: 98


    p_select_period_setname := 'TEMP.period_set_name';

Line: 100


    p_select_time  := 'TEMP.period_quarter ';

Line: 101


    p_select_period_setname := 'TEMP.period_set_name';

Line: 103


    p_select_time  := 'TEMP.period_month ';

Line: 104


    p_select_period_setname := 'TEMP.period_set_name';

Line: 106


  p_groupby_time := p_select_time || ', ' || p_select_period_setname;

Line: 113


    p_select_org      := 'TEMP.set_of_books_id ';

Line: 115


    p_select_org      := 'TEMP.legal_entity_id ';

Line: 117


    p_select_org      := 'TEMP.operating_unit_id ';

Line: 119


    p_select_org      := 'TEMP.organization_id ';

Line: 121


  p_groupby_org := p_select_org;

Line: 127


    p_select_geo := 'TEMP.area_code ';

Line: 128


    p_groupby_geo := p_select_geo;

Line: 130


    p_select_geo := 'TEMP.area_code || ''+'' ' ||
                    '|| TEMP.country_code ';

Line: 135


    p_select_geo := 'TEMP.area_code || ''+'' ' ||
                    '|| TEMP.country_code || ''+'' || TEMP.region_code ';

Line: 145


    p_select_product       := 'TEMP.category_id ';

Line: 148


    p_select_product       := 'TEMP.inventory_item_id ';

Line: 151


  p_groupby_product      := p_select_product;

Line: 157


  p_select_statement :=
    'select ' ||
       p_select_time            || ', ' ||
       p_select_period_setname  || ', ' ||
       p_select_org             || ', ' ||
       p_select_geo             || ', ' ||
       p_select_product         || ', ' ||
    '  (decode(sum(TEMP.available_hours), 0, 0, ' ||
    '         sum(TEMP.actual_hours)/sum(TEMP.available_hours))*100) ' ||
    'from ' ||
       p_from || ' TEMP ' ||
    'group by ' ||
       p_groupby_time      || ', ' ||
       p_groupby_org       || ', ' ||
       p_groupby_geo       || ', ' ||
       p_groupby_product;

Line: 174


  DBMS_SQL.PARSE( cursor_id, p_select_statement, DBMS_SQL.V7 );

Line: 311


       SELECT MAX(set_of_books_id) INTO l_sob_id
     FROM gl_sets_of_books
     WHERE name = sob;

Line: 319


       SELECT MAX(organization_id) INTO l_le_id
     FROM hr_legal_entities
     WHERE name = le;

Line: 327


       SELECT MAX(organization_id) INTO l_ou_id
     FROM hr_operating_units
     WHERE name = ou;

Line: 333


       SELECT MAX(organization_id) INTO l_org_id
     FROM org_organization_definitions
     WHERE organization_name = org;

Line: 345


       SELECT MAX(id) INTO l_area_id
     FROM bis_areas_v
     WHERE name = area;

Line: 359


       SELECT MAX(region_code) INTO l_region_id
     FROM bis_regions_v
     WHERE area_code = l_area_id AND country_code = l_country_id
     AND name = region;

Line: 378


       SELECT MAX(inventory_item_id) INTO l_item_id
     FROM wip_bis_utz_cat_v
     WHERE category_name = prod AND inventory_item_name = item;

Line: 487


  /* SELECT clause */
  p_select_time            VARCHAR2(1000) := '-1 ';

Line: 489


  p_select_start_date      VARCHAR2(1000) := 'to_date(NULL) ';

Line: 490


  p_select_end_date        VARCHAR2(1000) := 'to_date(NULL) ';

Line: 491


  p_select_org             VARCHAR2(1000) := '-1 ';

Line: 492


  p_select_geo             VARCHAR2(1000) := '-1 ';

Line: 493


  p_select_product         VARCHAR2(1000) := '-1 ';

Line: 494


  p_select_prod_id         VARCHAR2(1000) := 'to_number(NULL) ';

Line: 495


  p_select_sob_name        VARCHAR2(1000) ;

Line: 496


  p_select_le_name         VARCHAR2(1000) ;

Line: 497


  p_select_ou_name         VARCHAR2(1000) ;

Line: 498


  p_select_org_name        VARCHAR2(1000) ;

Line: 499


  p_select_area_name       VARCHAR2(1000) ;

Line: 500


  p_select_country_name    VARCHAR2(1000) ;

Line: 501


  p_select_country_id      VARCHAR2(1000) := 'to_char(NULL) ';

Line: 502


  p_select_prod_name       VARCHAR2(1000) ;

Line: 503


  p_select_item_name       VARCHAR2(1000) ;

Line: 505


  p_select_region_name     VARCHAR2(1000) ;

Line: 533


  p_select_statement VARCHAR2(32767);

Line: 542


  p_select_sob_name := all_text;

Line: 543


  p_select_le_name := all_text;

Line: 544


  p_select_ou_name  := all_text;

Line: 545


  p_select_org_name  := all_text;

Line: 546


  p_select_area_name := all_text;

Line: 547


  p_select_country_name := all_text;

Line: 548


  p_select_prod_name := all_text;

Line: 549


  p_select_item_name := all_text;

Line: 550


  p_select_region_name := all_text;

Line: 560


    p_select_start_date := 'gl_p.start_date '; -- Bug 3554853

Line: 561


    p_select_end_date := 'gl_p.end_date '; -- Bug 3554853

Line: 562


    p_select_time  := 'TEMP.period_year ';

Line: 566


          'and gl_p.start_date = (select max(gl_p.start_date) from gl_periods ' || -- Bug 3554853
                            'where period_set_name = TEMP.period_set_name ' ||
                            '  and period_type = ''Year'') ';

Line: 571


    p_select_start_date := 'gl_p.start_date '; -- Bug 3554853

Line: 572


    p_select_end_date := 'gl_p.end_date '; -- Bug 3554853

Line: 573


    p_select_time  := 'TEMP.period_quarter ';

Line: 577


        'and gl_p.start_date = (select max(gl_p.start_date) from gl_periods ' || -- Bug 3554853
                              'where period_set_name = TEMP.period_set_name ' ||
                              '  and period_type = ''Quarter'') ';

Line: 582


    p_select_start_date := 'gl_p.start_date '; -- Bug 3554853

Line: 583


    p_select_end_date := 'gl_p.end_date '; -- Bug 3554853

Line: 584


    p_select_time  := 'TEMP.period_month ';

Line: 589


       ' (select max(gl_p.start_date) ' ||  -- Bug 3554853
       '  from gl_periods p, gl_sets_of_books sob ' ||
       ' where p.period_set_name = TEMP.period_set_name ' ||
       '   and sob.SET_OF_BOOKS_ID = TEMP.set_of_books_id ' ||
       '   and p.period_type = sob.ACCOUNTED_PERIOD_TYPE) ';

Line: 595


  p_groupby_time := p_select_time || ', ' || p_select_start_date ||
                    ', ' ||  p_select_end_date;

Line: 603


    p_select_org  := 'TEMP.set_of_books_id ';

Line: 604


    p_select_sob_name := 'TEMP.set_of_books_name';

Line: 608


    p_select_org  := 'TEMP.legal_entity_id ';

Line: 609


    p_select_sob_name := 'TEMP.set_of_books_name';

Line: 610


    p_select_le_name := 'TEMP.legal_entity_name';

Line: 614


    p_select_org  := 'TEMP.operating_unit_id ';

Line: 615


    p_select_sob_name := 'TEMP.set_of_books_name';

Line: 616


    p_select_le_name := 'TEMP.legal_entity_name';

Line: 617


    p_select_ou_name := 'TEMP.operating_unit_name';

Line: 621


    p_select_org  := 'TEMP.organization_id ';

Line: 622


    p_select_sob_name := 'TEMP.set_of_books_name';

Line: 623


    p_select_le_name := 'TEMP.legal_entity_name';

Line: 624


    p_select_ou_name := 'TEMP.operating_unit_name';

Line: 625


    p_select_org_name := 'TEMP.organization_name';

Line: 633


      p_select_geo := 'TEMP.area_code ';

Line: 634


      p_select_area_name := 'TEMP.area_name ';

Line: 638


      p_groupby_geo := p_select_geo || ', ' || p_select_area_name;

Line: 640


      p_select_geo := 'TEMP.country_code ' ;

Line: 641


      p_select_area_name := 'TEMP.area_name ';

Line: 642


      p_select_country_name := 'TEMP.country_name ';

Line: 646


      p_groupby_geo := p_select_area_name || ', ' ||
                       p_select_geo || ', ' ||
                   p_select_country_name;

Line: 651


     p_select_geo := 'TEMP.region_code ';

Line: 652


      p_select_area_name := 'TEMP.area_name ';

Line: 653


      p_select_country_name := 'TEMP.country_name ';

Line: 654


      p_select_country_id := 'TEMP.country_code ';

Line: 655


      p_select_region_name := 'TEMP.region_name ';

Line: 661


      p_groupby_geo := p_select_area_name || ', ' ||
                       p_select_geo || ', ' ||
                   p_select_country_name || ', ' ||
                   p_select_country_id || ', ' || -- Added for bug 3570060
                   p_select_region_name;

Line: 674


    p_select_product := 'TEMP.category_id ';

Line: 675


    p_select_prod_name := 'TEMP.category_name ';

Line: 681


    p_select_product  := 'TEMP.inventory_item_id ';

Line: 682


    p_select_prod_id := 'TEMP.category_id ';

Line: 683


    p_select_prod_name := 'TEMP.category_name ';

Line: 684


    p_select_item_name := 'TEMP.inventory_item_name ';

Line: 696


  p_select_statement :=
    'select ' ||
       p_select_time            || ', ' ||
       p_select_org             || ', ' ||
       p_select_geo             || ', ' ||
       p_select_product         || ', ' ||
       p_select_prod_id         || ', ' ||
       p_select_sob_name        || ', ' ||
       p_select_le_name         || ', ' ||
       p_select_ou_name         || ', ' ||
       p_select_org_name        || ', ' ||
       p_select_area_name       || ', ' ||
       p_select_country_name    || ', ' ||
       p_select_prod_name       || ', ' ||
       p_select_item_name       || ', ' ||
    '  (decode(sum(TEMP.available_hours), 0, 0, ' ||
    '        sum(TEMP.actual_hours)/sum(TEMP.available_hours))*100), ' ||
    '  trgt.target, ' ||
    '  bbp.plan_id, ' ||
    '  bbp.name, ' ||
    '  btl.workflow_process_short_name, ' ||
    '  min(trgt.range1_low), ' ||
    '  min(trgt.range1_high), ' ||
    '  min(trgt.range2_low), ' ||
    '  min(trgt.range2_high), ' ||
    '  min(trgt.range3_low), ' ||
    '  min(trgt.range3_high), ' ||
    '  min(trgt.notify_resp1_id), ' ||
    '  min(trgt.notify_resp2_id), ' ||
    '  min(trgt.notify_resp3_id), ' ||
    '  min(trgt.notify_resp1_short_name), ' ||
    '  min(trgt.notify_resp2_short_name), ' ||
    '  min(trgt.notify_resp3_short_name), ' ||
       p_select_start_date       || ', ' ||
       p_select_end_date         || ', ' ||
       p_select_region_name      || ', ' || /* James */
       p_select_country_id       ||  /* James */

    'from ' ||
    '    bisbv_business_plans bbp, ' ||
    '    bisbv_targets trgt, ' ||
    '    bisbv_target_levels btl, ' ||
         p_from_time || ' gl_p, ' || -- Bug 3554853
         p_from || ' TEMP ' ||
    'where btl.target_level_id = ' || ':target_level_id' ||
    '  and trgt.target_level_id = btl.target_level_id ' ||
    '  and bbp.plan_id = trgt.plan_id ' ||
       p_where_trgt_time || ' ' ||
       p_where_trgt_org  || ' ' ||
       p_where_trgt_geo  || ' ' ||
       p_where_trgt_prod || ' ' ||
    'group by ' ||
       p_groupby_time      || ', ' ||
       p_select_org        || ', ' ||
       p_select_sob_name   || ', ' ||
       p_select_le_name    || ', ' ||
       p_select_ou_name    || ', ' ||
       p_select_org_name   || ', ' ||
       p_groupby_org       || ', ' ||
       p_groupby_geo       || ', ' ||
       p_groupby_product   || ', ' ||
       'trgt.target, bbp.plan_id, bbp.name, ' ||
       'btl.workflow_process_short_name ';

Line: 760


  DBMS_SQL.PARSE( cursor_id, p_select_statement, DBMS_SQL.V7 );

Line: 839


      /*INSERT INTO my_alert_test
    VALUES(v_actual, v_target, v_range1_low, v_range1_high, v_range2_low,
           v_range2_high, v_range3_low, v_range3_high, v_time, v_org,
           v_geography, v_product);*/

Line: 931


   select name from wf_roles
   where name = p_role;

Line: 951


   select bis_excpt_wf_s.nextval
   into l_wf_item_key
   from dual;

Line: 1056


   select btl.target_level_id target_level_id,
          decode(tltime.dimension_level_short_name,
                 'TOTAL_TIME', 0,
                 'YEAR', 1,
                 'QUARTER', 2,
                 'MONTH', 3) time_value,
          decode(tlorg.dimension_level_short_name,
                 'SET OF BOOKS', 1,
                 'LEGAL ENTITY', 2,
                 'OPERATING UNIT', 3,
                 'ORGANIZATION', 4) org_value,
          decode(tlgeo.dimension_level_short_name,
                 'TOTAL GEOGRAPHY', 1,
                 'AREA', 2,
                 'COUNTRY', 3,
         'REGION', 4) geo_value,  /* James 7/8/99 */
          decode(tlcat.dimension_level_short_name,
                 'TOTAL PRODUCTS', 1,
                 'PRODUCT GROUP', 2,
                 'ITEM', 3) prod_value
     from bisbv_performance_measures bpm,
          bisbv_target_levels btl,
          bisbv_dimension_levels tltime,
          bisbv_dimension_levels tlorg,
          bisbv_dimension_levels tlgeo,
          bisbv_dimension_levels tlcat
    where bpm.measure_short_name = 'WIPBIUZIND'
      and btl.measure_id = bpm.measure_id
      and btl.time_level_id = tltime.dimension_level_id
      and btl.org_level_id = tlorg.dimension_level_id
      and btl.dimension1_level_id = tlgeo.dimension_level_id
      and btl.dimension2_level_id = tlcat.dimension_level_id;