APPS.WIP_BIS_YDSP_ALERT SQL Statements

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

Line: 73

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

Line: 75

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

Line: 76

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

Line: 77

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

Line: 78

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

Line: 97

  p_select_statement VARCHAR2(32767);

Line: 104

    p_select_time  := 'TEMP.period_year ';

Line: 105

    p_select_period_setname := 'TEMP.period_set_name';

Line: 107

    p_select_time  := 'TEMP.period_quarter ';

Line: 108

    p_select_period_setname := 'TEMP.period_set_name';

Line: 110

    p_select_time  := 'TEMP.period_month ';

Line: 111

    p_select_period_setname := 'TEMP.period_set_name';

Line: 113

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

Line: 120

    p_select_org      := 'TEMP.set_of_books_id ';

Line: 122

    p_select_org      := 'TEMP.legal_entity_id ';

Line: 124

    p_select_org      := 'TEMP.operating_unit_id ';

Line: 126

    p_select_org      := 'TEMP.organization_id ';

Line: 128

  p_groupby_org := p_select_org;

Line: 134

    p_select_geo := 'bth.parent_territory_code ';

Line: 139

    p_groupby_geo := p_select_geo;

Line: 141

    p_select_geo := 'bth.parent_territory_code || ''+'' ' ||
                    '|| TEMP.country_code ';

Line: 160

    p_select_product       := 'TEMP.category_id ';

Line: 162

    p_select_product       := 'TEMP.inventory_item_id ';

Line: 165

  p_groupby_product      := p_select_product;

Line: 172

  p_select_statement :=
    'select ' ||
       p_select_time            || ', ' ||
       p_select_period_setname  || ', ' ||
       p_select_org             || ', ' ||
       p_select_geo             || ', ' ||
       p_select_product         || ', ' ||
    '  (decode(sum(TEMP.total_quantity), 0, 0, ' ||
    '    (sum(TEMP.total_quantity) -   ' ||
    '    sum(TEMP.scrap_quantity))/sum(TEMP.total_quantity))*100) ' ||
    'from ' ||
       p_from_geo || ' bth, ' ||
       p_from || ' TEMP ' ||
    'where ' ||
       p_where_geo || ' ' ||
    'group by ' ||
       p_groupby_time      || ', ' ||
       p_groupby_org       || ', ' ||
       p_groupby_geo       || ', ' ||
       p_groupby_product;

Line: 193

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

Line: 396

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

Line: 398

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

Line: 399

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

Line: 400

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

Line: 401

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

Line: 402

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

Line: 403

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

Line: 404

  p_select_sob_name        VARCHAR2(1000) ;

Line: 405

  p_select_le_name         VARCHAR2(1000) ;

Line: 406

  p_select_ou_name         VARCHAR2(1000) ;

Line: 407

  p_select_org_name        VARCHAR2(1000) ;

Line: 408

  p_select_area_name       VARCHAR2(1000) ;

Line: 409

  p_select_country_name    VARCHAR2(1000) ;

Line: 410

  p_select_prod_name       VARCHAR2(1000) ;

Line: 411

  p_select_item_name       VARCHAR2(1000) ;

Line: 439

  p_select_statement VARCHAR2(32767);

Line: 448

  p_select_sob_name := all_text;

Line: 449

  p_select_le_name := all_text;

Line: 450

  p_select_ou_name  := all_text;

Line: 451

  p_select_org_name  := all_text;

Line: 452

  p_select_area_name := all_text;

Line: 453

  p_select_country_name := all_text;

Line: 454

  p_select_prod_name := all_text;

Line: 455

  p_select_item_name := all_text;

Line: 466

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

Line: 467

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

Line: 468

    p_select_time  := 'TEMP.period_year ';

Line: 472

          '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: 477

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

Line: 478

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

Line: 479

    p_select_time  := 'TEMP.period_quarter ';

Line: 483

        '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: 488

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

Line: 489

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

Line: 490

    p_select_time  := 'TEMP.period_month ';

Line: 495

       ' (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: 501

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

Line: 509

    p_select_org  := 'TEMP.set_of_books_id ';

Line: 510

    p_select_sob_name := 'TEMP.set_of_books_name';

Line: 514

    p_select_org  := 'TEMP.legal_entity_id ';

Line: 515

    p_select_sob_name := 'TEMP.set_of_books_name';

Line: 516

    p_select_le_name := 'TEMP.legal_entity_name';

Line: 520

    p_select_org  := 'TEMP.operating_unit_id ';

Line: 521

    p_select_sob_name := 'TEMP.set_of_books_name';

Line: 522

    p_select_le_name := 'TEMP.legal_entity_name';

Line: 523

    p_select_ou_name := 'TEMP.operating_unit_name';

Line: 527

    p_select_org  := 'TEMP.organization_id ';

Line: 528

    p_select_sob_name := 'TEMP.set_of_books_name';

Line: 529

    p_select_le_name := 'TEMP.legal_entity_name';

Line: 530

    p_select_ou_name := 'TEMP.operating_unit_name';

Line: 531

    p_select_org_name := 'TEMP.organization_name';

Line: 539

      p_select_geo := 'bth.parent_territory_code';

Line: 540

      p_select_area_name := 'bth.parent_territory_name ';

Line: 548

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

Line: 550

      p_select_geo := 'TEMP.country_code ' ;

Line: 551

      p_select_area_name := 'bth.parent_territory_name ';

Line: 552

      p_select_country_name := 'bth.child_territory_name ';

Line: 575

    p_select_product := 'TEMP.category_id ';

Line: 576

    p_select_prod_name := 'TEMP.category_name ';

Line: 581

    p_select_product  := 'TEMP.inventory_item_id ';

Line: 582

    p_select_prod_id := 'TEMP.category_id ';

Line: 583

    p_select_prod_name := 'TEMP.category_name ';

Line: 584

    p_select_item_name := 'TEMP.inventory_item_name ';

Line: 595

  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.total_quantity), 0, 0, ' ||
    '    (sum(TEMP.total_quantity) -   ' ||
    '    sum(TEMP.scrap_quantity))/sum(TEMP.total_quantity))*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         ||

    'from ' ||
    '    bisbv_business_plans bbp, ' ||
    '    bisbv_targets trgt, ' ||
    '    bisbv_target_levels btl, ' ||
         p_from_time || ' gl_p, ' ||  --Bug 3554853
         p_from_geo || ' bth, ' ||
         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: 659

/*dbms_output.put_line (substr(p_select_statement,1,100));

Line: 660

dbms_output.put_line (substr(p_select_statement,100,100));

Line: 661

dbms_output.put_line (substr(p_select_statement,200,100));

Line: 662

dbms_output.put_line (substr(p_select_statement,300,100));

Line: 663

dbms_output.put_line (substr(p_select_statement,400,100));

Line: 664

dbms_output.put_line (substr(p_select_statement,500,100));

Line: 665

dbms_output.put_line (substr(p_select_statement,600,100));

Line: 666

dbms_output.put_line (substr(p_select_statement,700,100));

Line: 667

dbms_output.put_line (substr(p_select_statement,800,100));

Line: 668

dbms_output.put_line (substr(p_select_statement,900,100));

Line: 669

dbms_output.put_line (substr(p_select_statement,1000,100));

Line: 670

dbms_output.put_line (substr(p_select_statement,1100,100));

Line: 671

dbms_output.put_line (substr(p_select_statement,1200,100));

Line: 672

dbms_output.put_line (substr(p_select_statement,1300,100));

Line: 673

dbms_output.put_line (substr(p_select_statement,1400,100));

Line: 674

dbms_output.put_line (substr(p_select_statement,1500,100)); */

Line: 676

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

Line: 834

   select name from wf_roles
   where name = p_role;

Line: 852

   select bis_excpt_wf_s.nextval
   into l_wf_item_key
   from dual;

Line: 957

   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,
          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 = 'WIPBIYD'
      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;