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

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

Line: 13


  SELECT  tv.target_level_short_name
        , tv.target_level_name
        , tv.target_level_id
        , tv.plan_short_name
        , tv.plan_name
        , tv.org_level_value_id
        , tv.time_level_value_id
        , tv.target
        , tv.range1_low
        , tv.range1_high
        , tv.range2_low
        , tv.range2_high
        , tv.range3_low
        , tv.range3_high
        , tv.notify_resp1_id
        , tv.notify_resp1_short_name
        , tv.notify_resp2_id
        , tv.notify_resp2_short_name
        , tv.notify_resp3_id
        , tv.notify_resp3_short_name
  FROM BISFV_TARGETS tv
  WHERE tv.target_level_short_name = p_target_level_short_name
  AND tv.time_level_value_id = p_time_period
  AND tv.org_level_value_id = TO_CHAR(p_org_id)
  AND tv.plan_short_name = p_plan_short_name;

Line: 77


  SELECT  distinct(org_level_value_id) org_id
  FROM BISFV_TARGETS tv
  WHERE tv.target_level_short_name = p_target_level_short_name
  AND tv.plan_short_name = p_plan_short_name;

Line: 109


    SELECT to_number(org_information1) INTO l_sob_id
    FROM hr_organization_information
    WHERE organization_id = p_org_id
    AND org_information_context = 'Accounting Information';

Line: 117


  SELECT period_set_name, currency_code, accounted_period_type
  INTO p_period_set_name, p_currency, p_period_type
  FROM gl_sets_of_books
  WHERE set_of_books_id = l_sob_id;

Line: 172


  SELECT per.period_name, per.start_date, per.end_date
  INTO p_period_name, p_start_date, p_end_date
  FROM gl_periods per
  WHERE per.period_set_name = p_period_set_name
  AND per.period_type = l_period_type
  AND per.adjustment_period_flag = 'N'
  AND per.start_date <= TRUNC(sysdate)
  AND per.end_date >= TRUNC(sysdate);

Line: 203


  SELECT per.period_name, per.start_date, per.end_date
  INTO p_period_name, p_start_date, p_end_date
  FROM gl_periods per
  WHERE per.period_set_name = p_period_set_name
  AND per.period_type = l_period_type
  AND per.adjustment_period_flag = 'N'
  AND per.end_date = (SELECT max(end_date)
                      FROM gl_periods per2
                      WHERE per2.period_set_name = p_period_set_name
                      AND per2.period_type = l_period_type
                      AND per2.adjustment_period_flag = 'N'
                      AND per2.end_date < TRUNC(sysdate));

Line: 271


PROCEDURE insert_row(
p_label IN VARCHAR2,
p_heading IN VARCHAR2,
p_value IN VARCHAR2,
p_value_tbl IN OUT NOCOPY POA_Label_Value_Tbl) IS
l_index NUMBER;

Line: 287


END insert_row;

Line: 302


  SELECT
  TO_CHAR(DECODE(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
  0,0,100*SUM(NVL(poa.pot_contract_amount,0) *NVL(gl.conversion_rate,1))/
  SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1))),
  POA_BIS_ALERTS.g_percent_mask),
  TO_CHAR(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
  l_format_mask),
  TO_CHAR(SUM(NVL(poa.potential_saving,0)*NVL(gl.conversion_rate,1)),
  l_format_mask),
  TO_CHAR(SUM(NVL(poa.pot_contract_amount,0)*NVL(gl.conversion_rate,1)),
  l_format_mask)
  INTO l_leakage_percent, l_total_purchases, l_potential_savings,
  l_leakage_amount
  FROM gl_daily_rates gl,
  poa_bis_savings poa
  WHERE gl.from_currency (+) = poa.currency_code
  AND gl.to_currency (+) = p_currency
  AND gl.conversion_date (+) = poa.rate_date
  AND gl.conversion_type (+) = NVL(poa.rate_type, 'Corporate')
  AND poa.purchase_creation_date < p_end_date + 1
  AND poa.purchase_creation_date >= p_start_date;

Line: 324


  insert_row('L_ACTUAL', fnd_message.get_string('PO', 'POA_ACTUAL'),
    l_leakage_percent, p_value_tbl);

Line: 326


  insert_row('L_CHAR_ATTR1',
    fnd_message.get_string('POA', 'POA_TOTAL_PURCHASES'),
    l_total_purchases, p_value_tbl);

Line: 329


  insert_row('L_CHAR_ATTR2',
    fnd_message.get_string('POA', 'POA_LEAKAGE'),
    l_leakage_amount, p_value_tbl);

Line: 332


  insert_row('L_CHAR_ATTR3',
    fnd_message.get_string('POA', 'POA_TOTAL_SAVINGS'),
    l_potential_savings, p_value_tbl);

Line: 335


  insert_row('L_CURRENCY',
    fnd_message.get_string('POA', 'POA_CURRENCY'), p_currency, p_value_tbl);

Line: 359


  SELECT
  TO_CHAR(DECODE(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
  0,0,100*SUM(NVL(poa.pot_contract_amount,0) *NVL(gl.conversion_rate,1))/
  SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1))),
  POA_BIS_ALERTS.g_percent_mask),
  TO_CHAR(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
  l_format_mask),
  TO_CHAR(SUM(NVL(poa.potential_saving,0)*NVL(gl.conversion_rate,1)),
  l_format_mask),
  TO_CHAR(SUM(NVL(poa.pot_contract_amount,0)*NVL(gl.conversion_rate,1)),
  l_format_mask)
  INTO l_leakage_percent, l_total_purchases, l_potential_savings,
  l_leakage_amount
  FROM gl_daily_rates gl,
  poa_bis_savings poa
  WHERE gl.from_currency (+) = poa.currency_code
  AND gl.to_currency (+) = p_currency
  AND gl.conversion_date (+) = poa.rate_date
  AND gl.conversion_type (+) = NVL(poa.rate_type, 'Corporate')
  AND poa.operating_unit_id = p_org_id
  AND poa.purchase_creation_date < p_end_date + 1
  AND poa.purchase_creation_date >= p_start_date;

Line: 382


  insert_row('L_ACTUAL', fnd_message.get_string('PO', 'POA_ACTUAL'),
    l_leakage_percent, p_value_tbl);

Line: 384


  insert_row('L_CHAR_ATTR1',
    fnd_message.get_string('POA', 'POA_TOTAL_PURCHASES'),
    l_total_purchases, p_value_tbl);

Line: 387


  insert_row('L_CHAR_ATTR2',
    fnd_message.get_string('POA', 'POA_LEAKAGE'),
    l_leakage_amount, p_value_tbl);

Line: 390


  insert_row('L_CHAR_ATTR3',
    fnd_message.get_string('POA', 'POA_TOTAL_SAVINGS'),
    l_potential_savings, p_value_tbl);

Line: 393


  insert_row('L_CURRENCY',
    fnd_message.get_string('POA', 'POA_CURRENCY'), p_currency, p_value_tbl);

Line: 417


  SELECT
  TO_CHAR(DECODE(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
  0,0,100*SUM(NVL(poa.pot_contract_amount,0) *NVL(gl.conversion_rate,1))/
  SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1))),
  POA_BIS_ALERTS.g_percent_mask),
  TO_CHAR(SUM(NVL(poa.purchase_amount,0)*NVL(gl.conversion_rate,1)),
  l_format_mask),
  TO_CHAR(SUM(NVL(poa.potential_saving,0)*NVL(gl.conversion_rate,1)),
  l_format_mask),
  TO_CHAR(SUM(NVL(poa.pot_contract_amount,0)*NVL(gl.conversion_rate,1)),
  l_format_mask)
  INTO l_leakage_percent, l_total_purchases, l_potential_savings,
  l_leakage_amount
  FROM gl_daily_rates gl,
  poa_bis_savings poa
  WHERE gl.from_currency (+) = poa.currency_code
  AND gl.to_currency (+) = p_currency
  AND gl.conversion_date (+) = poa.rate_date
  AND gl.conversion_type (+) = NVL(poa.rate_type, 'Corporate')
  AND poa.ship_to_organization_id = p_org_id
  AND poa.purchase_creation_date < p_end_date + 1
  AND poa.purchase_creation_date >= p_start_date;

Line: 440


  insert_row('L_ACTUAL', fnd_message.get_string('PO', 'POA_ACTUAL'),
    l_leakage_percent, p_value_tbl);

Line: 442


  insert_row('L_CHAR_ATTR1',
    fnd_message.get_string('POA', 'POA_TOTAL_PURCHASES'),
    l_total_purchases, p_value_tbl);

Line: 445


  insert_row('L_CHAR_ATTR2',
    fnd_message.get_string('POA', 'POA_LEAKAGE'),
    l_leakage_amount, p_value_tbl);

Line: 448


  insert_row('L_CHAR_ATTR3',
    fnd_message.get_string('POA', 'POA_TOTAL_SAVINGS'),
    l_potential_savings, p_value_tbl);

Line: 451


  insert_row('L_CURRENCY',
    fnd_message.get_string('POA', 'POA_CURRENCY'), p_currency, p_value_tbl);

Line: 471


  SELECT
  TO_CHAR(decode(sum(poa.sales_amount), NULL, 0,0,0,
  nvl(100*sum((poa.purchase_amount)*nvl(gl.conversion_rate,1))/
  nvl(sum((poa.sales_amount)*nvl(gl.conversion_rate,1)),1),0)),
  POA_BIS_ALERTS.g_percent_mask) Actual
  INTO l_actual
  FROM poa_purchase_sales_v poa,
  gl_daily_rates gl
  WHERE gl.from_currency (+) = poa.currency
  and gl.to_currency (+) = p_currency
  and gl.conversion_date (+) = poa.transaction_date
  and gl.conversion_type (+) = 'Corporate'
  and poa.transaction_date < p_end_date + 1
  and poa.transaction_date >= p_start_date
  and poa.ou_id = p_org_id;

Line: 487


  insert_row('L_ACTUAL', fnd_message.get_string('PO', 'POA_ACTUAL'),
    l_actual, p_value_tbl);

Line: 489


  insert_row('L_CURRENCY',
    fnd_message.get_string('POA', 'POA_CURRENCY'), p_currency, p_value_tbl);

Line: 508


  SELECT
  TO_CHAR(decode(sum(poa.sales_amount), NULL, 0,0,0,
  nvl(100*sum((poa.purchase_amount)*nvl(gl.conversion_rate,1))/
  nvl(sum((poa.sales_amount)*nvl(gl.conversion_rate,1)),1),0)),
  POA_BIS_ALERTS.g_percent_mask) Actual
  INTO l_actual
  FROM poa_purchase_sales_v poa,
  gl_daily_rates gl
  WHERE gl.from_currency (+) = poa.currency
  and gl.to_currency (+) = p_currency
  and gl.conversion_date (+) = poa.transaction_date
  and gl.conversion_type (+) = 'Corporate'
  and poa.transaction_date < p_end_date + 1
  and poa.transaction_date >= p_start_date;

Line: 523


  insert_row('L_ACTUAL', fnd_message.get_string('PO', 'POA_ACTUAL'),
    l_actual, p_value_tbl);

Line: 525


  insert_row('L_CURRENCY',
    fnd_message.get_string('POA', 'POA_CURRENCY'), p_currency, p_value_tbl);

Line: 644


    SELECT displayed_field INTO p_org_name
    FROM po_lookup_codes
    WHERE lookup_code = 'ALL'
    AND lookup_type = 'POA BIS REPORT OPTION';

Line: 649


    SELECT name INTO p_org_name
    FROM hr_organization_units
    WHERE organization_id = p_org_id;

Line: 667


l_user_selection_tbl BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type;

Line: 688


  BIS_ACTUAL_PUB.Retrieve_User_Selections
  (p_api_version => 1.0,
   p_Target_Level_Rec => l_target_level_rec,
   x_Indicator_Region_Tbl => l_user_selection_tbl,
   x_return_status => l_return_status,
   x_msg_count => l_msg_count,
   x_msg_data => l_msg_data,
   x_error_Tbl => l_error_tbl);

Line: 701


  FOR l_index IN 1..l_user_selection_tbl.COUNT LOOP
--  FOR l_index IN 1..1 LOOP
    BEGIN
      POA_LOG.debug_line('Processing ORG_ID: ' ||
        l_user_selection_tbl(l_index).org_level_value_id);

Line: 707


      to_number(l_user_selection_tbl(l_index).org_level_value_id),
      p_for_current_period, l_start_date, l_end_date, l_period_name,
      l_period_set_name, l_currency, l_period_type);

Line: 715


      to_number(l_user_selection_tbl(l_index).org_level_value_id),
      l_start_date, l_end_date, l_currency, l_value_tbl);

Line: 722


        l_user_selection_tbl(l_index).org_level_value_id;

Line: 726


      l_actual_rec.target_level_id := l_user_selection_tbl(l_index).target_level_id;

Line: 751


        l_user_selection_tbl(l_index).org_level_value_id);

Line: 755


        POA_LOG.put_line(l_user_selection_tbl(l_index).org_level_value_id ||
          sqlcode || ': ' || sqlerrm);

Line: 804


  SELECT application_name INTO l_app_name
  FROM fnd_application_vl
  WHERE application_short_name = 'POA';

Line: 809


  SELECT workflow_item_type,
         workflow_process_short_name,
         measure_short_name,
         target_level_name
  INTO   l_workflow_item_type,
         l_workflow_process,
         l_measure_short_name,
         l_target_level_name
  FROM BISFV_TARGET_LEVELS
  WHERE target_level_short_name = p_target_level_short_name;

Line: 846


        l_value_tbl.DELETE;

Line: 866


            insert_row('L_SUBJECT', fnd_message.get_string('POA',
              'POA_' || UPPER(l_measure_short_name) || '_SUBJECT'), '(' ||
              fnd_message.get_string('PO','POA_ACTUAL') || ': ' || l_value ||
              ' ' || fnd_message.get_string('PO','POA_TARGET') || ': ' ||
              to_char(l_target_rec.target,POA_BIS_ALERTS.g_percent_mask) || ')',
              l_value_tbl);

Line: 872


            insert_row('L_TARGET_LEVEL',
              fnd_message.get_string('POA','POA_TARGET_LEVEL'),
              l_target_level_name, l_value_tbl);

Line: 877


            insert_row('L_ORG',
              fnd_message.get_string('PO','POA_ORGANIZATION'), l_org_name,
              l_value_tbl);

Line: 880


            insert_row('L_PERIOD',
              fnd_message.get_string('PO','POA_PERIOD'), l_period_name,
              l_value_tbl);

Line: 883


            insert_row('L_TARGET',
              fnd_message.get_string('PO','POA_TARGET'),
              to_char(l_target_rec.target, POA_BIS_ALERTS.g_percent_mask),
              l_value_tbl);

Line: 887


            insert_row('L_TARGET_RANGE',
              fnd_message.get_string('POA','POA_TARGET_RANGE'),
              to_char(l_range_low, POA_BIS_ALERTS.g_percent_mask) || ' - ' ||
              to_char(l_range_high,POA_BIS_ALERTS.g_percent_mask),l_value_tbl);

Line: 891


            insert_row('L_THANKS',
              fnd_message.get_string('POA','POA_THANKS'), NULL, l_value_tbl);

Line: 893


            insert_row('L_SENDER', l_app_name, NULL, l_value_tbl);

Line: 897


            insert_row('L_URL', fnd_profile.value('ICX_REPORT_LINK') ||
              'OracleOASIS.RunReport?report='|| l_measure_short_name ||
              '&' || 'parameters=' || l_param || '&' ||
              'responsibility_id=' ||
              l_target_rec.notify_resp1_id, NULL, l_value_tbl);

Line: 1026


  SELECT name
  FROM wf_roles
  WHERE name = p_role;

Line: 1049


  SELECT bis_excpt_wf_s.nextval
  INTO l_wf_item_key
  FROM dual;