APPS.AMS_ACTMETRICS_ENGINE_PVT SQL Statements

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

Line: 114

G_UPDATE_HISTORY CONSTANT VARCHAR2(1) := 'Y';

Line: 438

PROCEDURE UPDATE_VARIABLE
        (x_errbuf       OUT NOCOPY   VARCHAR2,
         x_retcode      OUT NOCOPY   NUMBER,
         p_commit       IN    VARCHAR2 := Fnd_Api.G_TRUE,
         p_object_list  IN object_currency_table := Empty_object_currency_table,
         p_current_date IN date,
         p_func_currency IN varchar2
);

Line: 447

PROCEDURE Update_formulas(
         x_errbuf       OUT NOCOPY   VARCHAR2,
         x_retcode      OUT NOCOPY   NUMBER,
         p_commit       IN VARCHAR2 := Fnd_Api.G_TRUE,
         p_object_list  IN object_currency_table := Empty_object_currency_table,
         p_current_date IN date,
         p_func_currency IN varchar2
);

Line: 549

   G_RCAM_Parents_table.delete;

Line: 550

   G_CSCH_Parents_table.delete;

Line: 551

   G_EVEO_Parents_table.delete;

Line: 552

   G_EVEH_Parents_table.delete;

Line: 553

   G_EONE_Parents_table.delete;

Line: 555

   G_FORECASTED_STACK.delete;

Line: 556

   G_ACTUAL_STACK.delete;

Line: 558

   G_CAMP_status_table.delete;

Line: 559

   G_CSCH_status_table.delete;

Line: 560

   G_EVEO_status_table.delete;

Line: 561

   G_EVEH_status_table.delete;

Line: 609

     SELECT --/*+ first_rows */
            activity_metric_id, a.metric_id, a.object_version_number,
            metric_uom_code, func_forecasted_value, func_actual_value,
            functional_currency_code, summarize_to_metric, rollup_to_metric,
            last_calculated_date, days_since_last_refresh,
            difference_since_last_calc, computed_using_function_value,
            activity_metric_origin_id,
            --variable_value, forecasted_variable_value,
            transaction_currency_code,
            G_LEAF_LEVEL, DECODE(l_calc_type,G_ROLLUP,rollup_to_metric,summarize_to_metric),
            --NULL,
            dirty_flag,
            func_actual_value orig_actual_value,
            func_forecasted_value orig_forecasted_value,
            trans_actual_value, trans_forecasted_value, depend_act_metric,
            arc_act_metric_used_by, act_metric_used_by_id,
           value_type ,
           metric_calculation_type ,
           accrual_type ,
           compute_using_function ,
           default_uom_code, display_type
     FROM ams_act_metrics_all a, ams_metrics_all_b b
     WHERE arc_act_metric_used_by IN
         (select lookup_code from ams_lookups
          where lookup_type in ('AMS_METRIC_OBJECT_TYPE', 'AMS_METRIC_ROLLUP_TYPE'))
         -- Replaced with metadata lookups above.
         --(G_CAMP, G_CSCH, G_DELV, G_EVEO, G_EVEH, G_RCAM, G_EONE)
         --BUG2845365: Removed dialogue components.
         --G_DILG, G_AMS_COMP_START, G_AMS_COMP_SHOW_WEB_PAGE, G_AMS_COMP_END)
     AND dirty_flag = G_IS_DIRTY
     AND a.metric_id = b.metric_id
     AND b.metric_calculation_type = l_calc_type;

Line: 645

     SELECT
            activity_metric_id, a.metric_id, a.object_version_number,
            metric_uom_code, func_forecasted_value, func_actual_value,
            functional_currency_code, summarize_to_metric, rollup_to_metric,
            last_calculated_date, days_since_last_refresh,
            difference_since_last_calc, computed_using_function_value,
            activity_metric_origin_id,
            -- variable_value, forecasted_variable_value,
            transaction_currency_code,
            G_LEAF_LEVEL, DECODE(l_calc_type,G_ROLLUP,rollup_to_metric,summarize_to_metric),
            --NULL,
            dirty_flag,
            func_actual_value orig_actual_value,
            func_forecasted_value orig_forecasted_value,
            trans_actual_value, trans_forecasted_value, depend_act_metric,
            arc_act_metric_used_by, act_metric_used_by_id,
           value_type ,
           metric_calculation_type ,
           accrual_type ,
           compute_using_function ,
           default_uom_code, display_type
     FROM ams_act_metrics_all a, ams_metrics_all_b b
     WHERE dirty_flag = G_IS_DIRTY
     AND a.metric_id = b.metric_id
     AND b.metric_calculation_type = l_calc_type
     AND a.arc_act_metric_used_by = l_object_type
     AND a.act_metric_used_by_id = l_object_id;

Line: 685

    SELECT  activity_metric_id, a.metric_id, a.object_version_number,
            metric_uom_code, func_forecasted_value, func_actual_value,
            functional_currency_code, summarize_to_metric, rollup_to_metric,
            last_calculated_date, days_since_last_refresh,
            difference_since_last_calc, computed_using_function_value,
            activity_metric_origin_id,
            -- variable_value, forecasted_variable_value,
            transaction_currency_code,
            G_LEAF_LEVEL, DECODE(metric_calculation_type,G_ROLLUP,rollup_to_metric,summarize_to_metric),
            --NULL,
            dirty_flag,
            func_actual_value orig_actual_value,
            func_forecasted_value orig_forecasted_value,
            trans_actual_value, trans_forecasted_value, depend_act_metric,
            arc_act_metric_used_by, act_metric_used_by_id,
           value_type ,
           metric_calculation_type ,
           accrual_type ,
           compute_using_function ,
           default_uom_code, display_type
    FROM ams_act_metrics_all a, ams_metrics_all_b b
    WHERE activity_metric_id = l_parent_act_metric_id
    AND a.metric_id = b.metric_id;

Line: 1020

     l_used_actmet_ids.DELETE;

Line: 1074

      SELECT COUNT(1)
      FROM ams_metric_accruals
      WHERE metric_id = l_metric_id;

Line: 1149

         l_act_metric_id_levels.DELETE(l_id_outside);

Line: 1205

                        SELECT activity_metric_id,
                               func_forecasted_value,
                               func_actual_value,
                               functional_currency_code,
                               metric_uom_code,
                               arc_act_metric_used_by,
                               act_metric_used_by_id
                        BULK COLLECT INTO l_activity_metric_ids,
                               l_func_forecasted_values,
                               l_func_actual_values,
                               l_functional_currency_codes,
                               l_metric_uom_codes,
                               l_arc_act_metric_used_bys,
                               l_act_metric_used_by_ids
                        FROM   ams_act_metrics_all
                        WHERE  summarize_to_metric = l_id;

Line: 1223

                        SELECT activity_metric_id,
                               func_forecasted_value,
                               func_actual_value,
                               functional_currency_code,
                               metric_uom_code,
                               arc_act_metric_used_by,
                               act_metric_used_by_id
                        BULK COLLECT INTO l_activity_metric_ids,
                               l_func_forecasted_values,
                               l_func_actual_values,
                               l_functional_currency_codes,
                               l_metric_uom_codes,
                               l_arc_act_metric_used_bys,
                               l_act_metric_used_by_ids
                        FROM   ams_act_metrics_all
                        WHERE  rollup_to_metric = l_id;

Line: 1403

                  l_activity_metric_ids.DELETE;

Line: 1404

                  l_func_forecasted_values.DELETE;

Line: 1405

                  l_func_actual_values.DELETE;

Line: 1406

                  l_functional_currency_codes.DELETE;

Line: 1407

                  l_metric_uom_codes.DELETE;

Line: 1408

                  l_arc_act_metric_used_bys.DELETE;

Line: 1409

                  l_act_metric_used_by_ids.DELETE;

Line: 1486

   l_CAMP_status_table.delete;

Line: 1487

   l_CSCH_status_table.delete;

Line: 1488

   l_EVEO_status_table.delete;

Line: 1489

   l_EVEH_status_table.delete;

Line: 1510

PROCEDURE Bulk_update(
   x_dirty_actmet IN OUT NOCOPY act_met_ref_rec_set_type,
   p_calc_type IN VARCHAR2,
   p_commit IN varchar2,
   x_return_status OUT NOCOPY VARCHAR2
)
IS
   L_API_NAME CONSTANT VARCHAR2(100) := 'BULK_UPDATE';

Line: 1653

            UPDATE ams_act_metrics_all
            SET last_calculated_date = l_cal_date_table (l_count),
                days_since_last_refresh = l_sin_last_ref(l_count),
                func_actual_value = l_func_act_va(l_count),
                trans_actual_value = l_tran_act_va(l_count),
                func_forecasted_value = l_func_for_value(l_count),
                trans_forecasted_value = l_trans_for_value(l_count),
                dirty_flag = l_dirty_flag(l_count),
                difference_since_last_calc = l_diff(l_count),
                functional_currency_code = l_func_curr_code(l_count),
                transaction_currency_code = l_trans_curr_code(l_count),
                last_update_date = l_today,
                object_version_number = object_version_number + 1
            WHERE activity_metric_id = l_act_met_id (l_count)
            AND OBJECT_VERSION_NUMBER = l_obj_version(l_count);

Line: 1670

               UPDATE ams_act_metrics_all
               SET dirty_flag = G_IS_DIRTY
               WHERE activity_metric_id = l_summarize_ids(l_count);

Line: 1676

           /* update ams_act_metrics_all
            set dirty_flag = G_IS_DIRTY
            where activity_metric_id in
            (select a.activity_metric_id
            from ams_act_metrics_all a, ams_metrics_all_b m,
              ams_metric_formulas f, ams_act_metrics_all b, ams_metrics_all_b c
            where a.metric_id = m.metric_id
            and m.metric_id = f.metric_id
            and b.metric_id = c.metric_id
            and a.arc_act_metric_used_by = b.arc_act_metric_used_by
            and a.act_metric_used_by_id = b.act_metric_used_by_id
            and m.metric_calculation_type = G_FORMULA
            and ((b.metric_id = f.source_id and f.source_type = G_METRIC)
               or (c.metric_category = f.source_id and f.source_type = G_CATEGORY))
            and b.activity_metric_id = l_act_met_id (l_count)); */

Line: 1692

	    update ams_act_metrics_all
            set dirty_flag = G_IS_DIRTY
            where activity_metric_id in
            (select a.activity_metric_id
            from ams_act_metrics_all a, ams_metrics_all_b m,
              ams_metric_formulas f, ams_act_metrics_all b, ams_metrics_all_b c
            where a.metric_id = m.metric_id
            and m.metric_id = f.metric_id
            and b.metric_id = c.metric_id
            and a.arc_act_metric_used_by = b.arc_act_metric_used_by
            and a.act_metric_used_by_id = b.act_metric_used_by_id
            and m.metric_calculation_type = G_FORMULA
            and ((b.metric_id in (select decode(c1.summary_metric_id, NULL, f1.source_id,c1.summary_metric_id)
                                 from ams_metric_formulas f1, ams_metrics_all_b c1
                                 where c1.metric_id=f1.source_id)     and f.source_type = G_METRIC)
               or (c.metric_category = f.source_id and f.source_type = G_CATEGORY))
            and b.activity_metric_id = l_act_met_id (l_count));

Line: 1713

               write_msg(L_API_NAME, 'BATCH UPDATE COMMIT: COUNT='||
                  l_act_met_id.COUNT||', Dirty index='||l_index);

Line: 1717

         l_act_met_id.delete;

Line: 1718

         l_cal_date_table.delete;

Line: 1719

         l_sin_last_ref.delete;

Line: 1720

         l_func_act_va.delete;

Line: 1721

         l_tran_act_va.delete;

Line: 1722

         l_func_for_value.delete;

Line: 1723

         l_trans_for_value.delete;

Line: 1724

         l_dirty_flag.delete;

Line: 1725

         l_diff.delete;

Line: 1726

         l_func_curr_code.delete;

Line: 1727

         l_trans_curr_code.delete;

Line: 1728

         l_obj_version.delete;

Line: 1729

         l_summarize_ids.delete;

Line: 1737

END Bulk_update;

Line: 1761

           p_update_history IN    VARCHAR2 := Fnd_Api.G_FALSE
)
IS
   L_API_VERSION        CONSTANT NUMBER := 1.0;

Line: 1817

   UPDATE_VARIABLE(x_errbuf => x_errbuf,
                   x_retcode => x_retcode,
                   p_commit => FND_API.G_FALSE,
                   p_current_date => l_current_date,
                   p_func_currency => l_default_currency);

Line: 1845

   Bulk_Update(
       x_dirty_actmet => l_dirty_actmet,
       p_calc_type => G_ROLLUP,
       p_commit => FND_API.G_FALSE,
       x_return_status => l_return_status
   );

Line: 1852

   l_dirty_actmet.DELETE;

Line: 1871

   Bulk_Update(
      x_dirty_actmet => l_dirty_actmet,
      p_calc_type => G_SUMMARY,
      p_commit => FND_API.G_FALSE,
      x_return_status => l_return_status
   );

Line: 1878

   l_dirty_actmet.DELETE;

Line: 1880

   Update_formulas(x_errbuf => x_errbuf,
                   x_retcode => x_retcode,
                   p_commit => FND_API.G_FALSE,
                   p_current_date => l_current_date,
                   p_func_currency => l_default_currency);

Line: 1888

   IF p_update_history IN (Fnd_Api.G_TRUE, G_UPDATE_HISTORY) THEN
      Update_History(p_commit => FND_API.G_FALSE);

Line: 1966

           p_update_history        IN    VARCHAR2 := Fnd_Api.G_FALSE
)
IS
   L_API_VERSION        CONSTANT NUMBER := 1.0;

Line: 2068

   UPDATE_VARIABLE(x_errbuf => l_errbuf,
                   x_retcode => l_retcode,
                   p_commit => FND_API.G_FALSE,
                   p_object_list => l_object_list,
                   p_current_date => l_current_date,
                   p_func_currency => l_default_currency);

Line: 2101

   Bulk_Update(
       x_dirty_actmet => l_dirty_actmet,
       p_calc_type => G_ROLLUP,
       p_commit => FND_API.G_FALSE,
       x_return_status => l_return_status
   );

Line: 2114

   l_dirty_actmet.DELETE;

Line: 2143

   Bulk_Update(
      x_dirty_actmet => l_dirty_actmet,
      p_calc_type => G_SUMMARY,
      p_commit => FND_API.G_FALSE,
      x_return_status => l_return_status
   );

Line: 2156

   l_dirty_actmet.DELETE;

Line: 2158

   Update_formulas(x_errbuf => l_errbuf,
                   x_retcode => l_retcode,
                   p_commit => FND_API.G_FALSE,
                   p_object_list => l_object_list,
                   p_current_date => l_current_date,
                   p_func_currency => l_default_currency);

Line: 2167

   IF p_update_history IN (Fnd_Api.G_TRUE, G_UPDATE_HISTORY) THEN
      Update_History(p_commit => FND_API.G_FALSE);

Line: 2278

    SELECT --/*+ first_rows */
           a.metric_parent_id, b.activity_metric_id, b.act_metric_used_by_id,
           b.arc_act_metric_used_by
    FROM ams_metrics_all_b a, ams_act_metrics_all b
    WHERE a.metric_parent_id IS NOT NULL
    AND b.rollup_to_metric IS NULL
    AND a.metric_id = b.metric_id;

Line: 2289

    SELECT /*+ first_rows */
           a.metric_parent_id, b.activity_metric_id, b.act_metric_used_by_id,
           b.arc_act_metric_used_by
    FROM ams_metrics_all_b a, ams_act_metrics_all b
    WHERE a.metric_parent_id IS NOT NULL
    AND b.rollup_to_metric IS NULL
    AND a.metric_id = b.metric_id
    AND b.arc_act_metric_used_by = p_object_type
    AND b.act_metric_used_by_id = p_object_id;

Line: 2370

      G_RCAM_Parents_table.DELETE;

Line: 2371

      G_CSCH_Parents_table.DELETE;

Line: 2372

      G_EVEO_Parents_table.DELETE;

Line: 2373

      G_EVEH_Parents_table.DELETE;

Line: 2374

      G_EONE_Parents_table.DELETE;

Line: 2421

     l_metric_parent_ids.delete;

Line: 2423

      G_RCAM_Parents_table.DELETE;

Line: 2424

      G_CSCH_Parents_table.DELETE;

Line: 2425

      G_EVEO_Parents_table.DELETE;

Line: 2426

      G_EVEH_Parents_table.DELETE;

Line: 2427

      G_EONE_Parents_table.DELETE;

Line: 2447

          UPDATE ams_act_metrics_all
          SET rollup_to_metric = l_act_metric_parents (l_row_count)
          WHERE activity_metric_id = l_act_metrics (l_row_count);

Line: 2453

          UPDATE ams_act_metrics_all
               SET dirty_flag = G_IS_DIRTY
          WHERE activity_metric_id = l_act_metric_parents (l_row_count)
               AND dirty_flag = G_NOT_DIRTY;

Line: 2471

     write_msg(L_API_NAME,'END: ROWS UPDATED='||l_act_metrics.count);

Line: 2473

  l_act_metric_parents.DELETE;

Line: 2474

  l_act_metrics.DELETE;

Line: 2584

    SELECT metric_parent_id
    FROM ams_metrics_all_b
    WHERE metric_id = l_metric_id;

Line: 2590

    SELECT parent_campaign_id
    FROM ams_campaigns_all_b
    WHERE campaign_id = l_act_metric_used_by_id
    AND UPPER(show_campaign_flag) = 'Y';

Line: 2597

    SELECT event_header_id
    FROM ams_event_offers_all_b
    WHERE event_offer_id = l_act_metric_used_by_id;

Line: 2604

    SELECT program_id
    FROM ams_event_headers_all_b
    WHERE event_header_id = l_act_metric_used_by_id;

Line: 2612

    SELECT program_id
    FROM ams_event_headers_all_b
    WHERE event_header_id = l_act_metric_used_by_id;

Line: 2617

         SELECT rollup_type
    FROM ams_campaigns_all_b
    WHERE campaign_id = l_camp_id;

Line: 2624

    SELECT campaign_id
    FROM ams_campaign_schedules_b
    WHERE schedule_id = l_act_metric_used_by_id;

Line: 2629

    SELECT parent_id
    FROM ams_event_offers_all_b
    WHERE event_offer_id = l_act_metric_used_by_id
         AND parent_type = G_RCAM;

Line: 2639

    SELECT activity_metric_id
    FROM ams_act_metrics_all
    WHERE metric_id = l_metric_parent_id
    AND act_metric_used_by_id = l_obj_id
    AND arc_act_metric_used_by = l_obj_code;

Line: 2821

         ELSE -- need to update this one
            l_count := x_act_metrics.count +1;

Line: 2975

      SELECT /*+ first_rows */ activity_metric_id,
             function_name,
             func_actual_value,
             last_calculated_date,
             metric_category,
             TRANSACTION_CURRENCY_CODE,
             functional_currency_code
      FROM   ams_metrics_all_b a, ams_act_metrics_all b,
		       ams_lookups lkup
      WHERE  a.metric_id = b.metric_id
		-- BUG4924982: Performance, join to metric definition.
      AND a.arc_metric_used_for_object = lkup.lookup_code
      AND lookup_type = 'AMS_METRIC_OBJECT_TYPE'
      AND a.metric_calculation_type = G_FUNCTION
      AND a.function_type = G_IS_FUNCTION;

Line: 2993

      SELECT /*+ first_rows */ activity_metric_id,
             function_name,
             func_actual_value,
             last_calculated_date,
             metric_category,
             TRANSACTION_CURRENCY_CODE,
             functional_currency_code
      FROM   ams_metrics_all_b a, ams_act_metrics_all b
      WHERE  a.metric_id = b.metric_id
      AND b.arc_act_metric_used_by = l_object_type
      AND b.act_metric_used_by_id = l_object_id
      AND a.metric_calculation_type = G_FUNCTION
      AND a.function_type = G_IS_FUNCTION;

Line: 3008

      SELECT DISTINCT function_name
      FROM   ams_metrics_all_b
      WHERE  arc_metric_used_for_object IN
         (select lookup_code from ams_lookups
          where lookup_type = 'AMS_METRIC_OBJECT_TYPE')
         -- Replaced with metadata lookups above.
      --(G_CAMP, G_CSCH, G_DELV, G_EVEO, G_EVEH, G_RCAM, G_EONE)
      --BUG2845365: Removed dialogue components.
      --G_DILG, G_AMS_COMP_START, G_AMS_COMP_SHOW_WEB_PAGE, G_AMS_COMP_END)
      AND metric_calculation_type = G_FUNCTION
      AND function_type = G_IS_PROCEDURE
      AND enabled_flag = G_IS_ENABLED;

Line: 3023

      SELECT count(1)
      FROM ams_act_metrics_all a, ams_metrics_all_b b
      WHERE a.metric_id = b.metric_id
      AND b.function_name = l_function_name
      AND a.arc_act_metric_used_by = l_obj_type
      AND a.act_metric_used_by_id = l_obj_id
      AND b.metric_calculation_type = G_FUNCTION
      AND b.function_type = G_IS_PROCEDURE
      AND b.enabled_flag = G_IS_ENABLED;

Line: 3112

      l_function_list.DELETE;

Line: 3154

         l_temp_act_metric_ids.delete;

Line: 3155

         l_temp_function_names.delete;

Line: 3156

         l_temp_func_actual_values.delete;

Line: 3157

         l_temp_last_calculated_dates.delete;

Line: 3158

         l_temp_categories.delete;

Line: 3159

         l_temp_trans_currencies.delete;

Line: 3160

         l_temp_func_currencies.delete;

Line: 3218

     write_msg(L_API_NAME,'NEW ACTUALS TO UPDATE='||l_new_act_metric_ids.COUNT);

Line: 3232

              UPDATE ams_act_metrics_all
              SET last_calculated_date = l_current_date,
                  last_update_date = l_current_date,
                  func_actual_value = l_new_func_actuals(l_count),
--                  FUNCTIONAL_CURRENCY_CODE = l_func_currencies(l_count),
                  days_since_last_refresh = l_days_since_last_refreshs(l_count),
                  trans_actual_value = l_new_trans_actuals(l_count)
              WHERE activity_metric_id = l_new_act_metric_ids(l_count);

Line: 3242

              UPDATE ams_act_metrics_all
                 SET dirty_flag = G_IS_DIRTY
               WHERE activity_metric_id IN
                 (SELECT rollup_to_metric FROM ams_act_metrics_all
                  WHERE activity_metric_id = l_new_act_metric_ids(l_count)
                  AND rollup_to_metric IS NOT NULL
                  UNION ALL
                  SELECT summarize_to_metric FROM ams_act_metrics_all
                  WHERE activity_metric_id = l_new_act_metric_ids(l_count)
                  AND summarize_to_metric IS NOT NULL);

Line: 3262

         l_new_func_actuals.delete;

Line: 3264

         l_days_since_last_refreshs.delete;

Line: 3265

         l_new_trans_actuals.delete;

Line: 3266

         l_new_act_metric_ids.delete;

Line: 3268

      l_act_metric_ids.delete;

Line: 3269

      l_function_names.delete;

Line: 3270

      l_func_actual_values.delete;

Line: 3271

      l_last_calculated_dates.delete;

Line: 3272

      l_categories.delete;

Line: 3273

      l_trans_currencies.delete;

Line: 3274

      l_func_currencies.delete;

Line: 3403

      SELECT master_object_type,
             master_object_id,
             using_object_type,
             using_object_id,
             TO_NUMBER(DECODE(usage_type,G_CREATED,100,G_USED_BY,
                       NVL(pct_of_cost_to_charge_used_by,0)))
                     pct_of_cost_to_charge_used_by,
             cost_frozen_flag
      FROM    ams_object_associations
      WHERE  object_association_id = p_obj_association_id ;

Line: 3414

      SELECT func_actual_value
      FROM    ams_act_metrics_all
      WHERE  activity_metric_origin_id = p_obj_association_id  ;

Line: 3489

      SELECT metric_id
        FROM ams_metrics_all_b
       WHERE arc_metric_used_for_object = l_arc_act_metric_used_by
       START WITH metric_id = l_metric_id
     CONNECT BY PRIOR summary_metric_id = metric_id
       ORDER BY LEVEL DESC ;

Line: 3500

      SELECT act.activity_metric_id activity_metric_id,
             met.metric_id metric_id
        FROM ams_act_metrics_all act,ams_metrics_all_b met
       WHERE met.metric_id = act.metric_id
         AND act.arc_act_metric_used_by = l_arc_act_metric_used_by
         AND act.act_metric_used_by_id  = l_act_metric_used_by_id
         AND met.metric_category =  l_metric_category ;

Line: 3511

      SELECT NVL(func_actual_value,0) func_actual_value,
               NVL(func_forecasted_value,0) func_forecasted_value,
             NVL(func_committed_value,0) func_committed_value
        FROM ams_act_metrics_all
       WHERE metric_id = l_met_id
         AND arc_act_metric_used_by = l_arc_act_metric_used_by
         AND act_metric_used_by_id  = l_act_metric_used_by_id ;

Line: 3643

      SELECT Fnd_Api.G_TRUE
      FROM ams_campaigns_all_b
      WHERE campaign_id = id
      AND status_code = G_CANCELLED;

Line: 3649

      SELECT Fnd_Api.G_TRUE
      FROM ams_campaign_schedules_b
      WHERE schedule_id = id
      AND status_code = G_CANCELLED;

Line: 3655

      SELECT Fnd_Api.G_TRUE
      FROM ams_deliverables_all_b
      WHERE deliverable_id = id
      AND status_code = G_CANCELLED;

Line: 3661

      SELECT Fnd_Api.G_TRUE
      FROM ams_event_headers_all_b
      WHERE event_header_id = id
      AND system_status_code = G_CANCELLED;

Line: 3667

      SELECT Fnd_Api.G_TRUE
      FROM ams_event_offers_all_b
      WHERE event_offer_id = id
      AND system_status_code IN (G_CANCELLED);

Line: 3756

PROCEDURE UPDATE_VARIABLE
        (x_errbuf       OUT NOCOPY   VARCHAR2,
         x_retcode      OUT NOCOPY   NUMBER,
         p_commit       IN    VARCHAR2 := Fnd_Api.G_TRUE,
         p_object_list  IN object_currency_table := Empty_object_currency_table,
         p_current_date IN date,
         p_func_currency IN varchar2
)
IS
   L_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_VARIABLE';

Line: 3768

   SELECT activity_metric_id,
          transaction_currency_code,
          functional_currency_code,
          actual_multiplier,
          forecast_multiplier,
          trans_actual_value,
          trans_forecasted_value,
          variable_value,
          forecasted_variable_value,
          LAST_CALCULATED_DATE
   FROM (
      SELECT a.activity_metric_id,
             a.transaction_currency_code,
             a.functional_currency_code,
             a.variable_value,
             a.forecasted_variable_value,
             a.trans_actual_value,
             a.trans_forecasted_value,
             a.last_calculated_date,
             a.dirty_flag,
             sum(c.trans_actual_value) actual_multiplier,
             sum(c.trans_forecasted_value) forecast_multiplier,
             max(c.last_update_date) last_update_date
      FROM ams_act_metrics_all a, ams_metrics_all_b b, ams_act_metrics_all c
      WHERE a.metric_id = b.metric_id
      AND b.accrual_type = G_VARIABLE
      AND a.arc_act_metric_used_by = c.arc_act_metric_used_by(+)
      AND a.act_metric_used_by_id = c.act_metric_used_by_id(+)
      AND c.metric_id = to_number(compute_using_function)
      GROUP BY a.activity_metric_id,
             a.transaction_currency_code,
             a.functional_currency_code,
             a.VARIABLE_VALUE,
             a.forecasted_variable_value,
             a.trans_actual_value,
             a.trans_forecasted_value,
             a.last_calculated_date,
             a.dirty_flag)
   WHERE last_update_date > nvl(last_calculated_date,last_update_date-1)
   OR functional_currency_code <> l_func_currency_code
   OR dirty_flag = G_IS_DIRTY;

Line: 3812

   SELECT activity_metric_id,
          transaction_currency_code,
          functional_currency_code,
          actual_multiplier,
          forecast_multiplier,
          trans_actual_value,
          trans_forecasted_value,
          variable_value,
          forecasted_variable_value,
          LAST_CALCULATED_DATE
   FROM (
      SELECT a.activity_metric_id,
             a.transaction_currency_code,
             a.functional_currency_code,
             a.variable_value,
             a.forecasted_variable_value,
             a.trans_actual_value,
             a.trans_forecasted_value,
             a.last_calculated_date,
             a.dirty_flag,
             sum(c.trans_actual_value) actual_multiplier,
             sum(c.trans_forecasted_value) forecast_multiplier,
             max(c.last_update_date) last_update_date
      FROM ams_act_metrics_all a, ams_metrics_all_b b, ams_act_metrics_all c
      WHERE a.metric_id = b.metric_id
      AND b.accrual_type = G_VARIABLE
      AND a.arc_act_metric_used_by = l_obj_type
      AND a.act_metric_used_by_id = l_obj_id
      AND a.arc_act_metric_used_by = c.arc_act_metric_used_by(+)
      AND a.act_metric_used_by_id = c.act_metric_used_by_id(+)
      AND c.metric_id = to_number(compute_using_function)
      GROUP BY a.activity_metric_id,
             a.transaction_currency_code,
             a.functional_currency_code,
             a.VARIABLE_VALUE,
             a.forecasted_variable_value,
             a.trans_actual_value,
             a.trans_forecasted_value,
             a.last_calculated_date,
             a.dirty_flag)
   WHERE last_update_date > nvl(last_calculated_date,last_update_date-1)
   OR functional_currency_code <> l_func_currency_code
   OR dirty_flag = G_IS_DIRTY;

Line: 3933

            l_temp_activity_metric_ids.delete;

Line: 3934

            l_temp_trans_currencies.delete;

Line: 3935

            l_temp_func_currencies.delete;

Line: 3936

            l_temp_actual_multipliers.delete;

Line: 3937

            l_temp_forecast_multipliers.delete;

Line: 3938

            l_temp_trans_actual_values.delete;

Line: 3939

            l_temp_trans_forecasted_values.delete;

Line: 3940

            l_temp_variable_values.delete;

Line: 3941

            l_temp_forecasted_variable_val.delete;

Line: 3942

            l_temp_LAST_CALCULATED_DATEs.delete;

Line: 4031

            UPDATE ams_act_metrics_all
               SET last_calculated_date = l_current_date,
                   last_update_date = l_current_date,
                   object_version_number = object_version_number + 1,
                   functional_currency_code = l_new_func_currencies(l_index),
                   DAYS_SINCE_LAST_REFRESH = l_new_days_since_last_refreshs(l_index),
                   --trans_actual_value = l_new_trans_actual_values(l_index),
                   func_actual_value = l_new_func_actual_values(l_index),
                   func_forecasted_value = l_new_func_forecasted_values(l_index),
                   trans_forecasted_value = l_new_trans_forecasted_values(l_index),
                   variable_value = l_new_variable_values(l_index),
                   forecasted_variable_value =
                      l_new_forecast_variable_values(l_index),
                   dirty_flag = G_NOT_DIRTY
             WHERE activity_metric_id = l_activity_metric_ids(l_index);

Line: 4050

             UPDATE ams_act_metrics_all
                SET dirty_flag = G_IS_DIRTY
              WHERE activity_metric_id IN
                 (SELECT rollup_to_metric FROM ams_act_metrics_all
                  WHERE activity_metric_id = l_activity_metric_ids(l_index)
                  UNION ALL
                  SELECT summarize_to_metric FROM ams_act_metrics_all
                  WHERE activity_metric_id = l_activity_metric_ids(l_index));

Line: 4067

      l_activity_metric_ids.delete;

Line: 4068

      l_trans_currencies.delete;

Line: 4069

      l_func_currencies.delete;

Line: 4070

      l_actual_multipliers.delete;

Line: 4071

      l_forecast_multipliers.delete;

Line: 4072

      l_trans_actual_values.delete;

Line: 4073

      l_trans_forecasted_values.delete;

Line: 4074

      l_variable_values.delete;

Line: 4075

      l_forecasted_variable_values.delete;

Line: 4076

      l_LAST_CALCULATED_DATEs.delete;

Line: 4077

      l_new_activity_metric_ids.delete;

Line: 4078

      l_new_variable_values.delete;

Line: 4079

      l_new_func_forecasted_values.delete;

Line: 4080

      l_new_trans_forecasted_values.delete;

Line: 4081

      l_new_func_currencies.delete;

Line: 4082

      l_new_days_since_last_refreshs.delete;

Line: 4083

      l_new_last_calculated_dates.delete;

Line: 4084

      l_new_func_actual_values.delete;

Line: 4085

      l_new_forecast_variable_values.delete;

Line: 4100

PROCEDURE Update_Variable (
   p_api_version                IN  NUMBER,
   p_init_msg_list              IN  VARCHAR2 := Fnd_Api.G_FALSE,
   p_commit                     IN  VARCHAR2 := Fnd_Api.G_FALSE,

   x_return_status              OUT NOCOPY VARCHAR2,
   x_msg_count                  OUT NOCOPY NUMBER,
   x_msg_data                   OUT NOCOPY VARCHAR2,

   p_arc_act_metric_used_by     IN VARCHAR2,
   p_act_metric_used_by_id      IN NUMBER
)
is
   L_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_VARIABLE';

Line: 4123

   savepoint sp_Update_Variable;

Line: 4131

   Update_Variable(x_errbuf => l_errbuf,
                 x_retcode => l_retcode,
                 p_commit => FND_API.G_FALSE,
                 p_object_list => l_object_list,
                 p_current_date => l_current_date,
                 p_func_currency => l_default_currency);

Line: 4161

      ROLLBACK TO sp_Update_Variable;

Line: 4169

      ROLLBACK TO sp_Update_Variable;

Line: 4177

      ROLLBACK TO sp_Update_Variable;

Line: 4188

end Update_Variable;

Line: 4208

    SELECT decode(rollup_type,G_RCAM,G_RCAM,G_CAMP), campaign_id, transaction_currency_code, G_RCAM, parent_campaign_id,
       decode(status_code, G_CANCELLED, Fnd_Api.G_TRUE, Fnd_Api.G_FALSE) cancelled_flag,
       upper(show_campaign_flag)
    FROM ams_campaigns_all_b
    WHERE campaign_id = l_act_metric_used_by_id;

Line: 4217

    SELECT G_CSCH, campaign_id, transaction_currency_code, G_CAMP, campaign_id,
       decode(status_code, G_CANCELLED, Fnd_Api.G_TRUE, Fnd_Api.G_FALSE) cancelled_flag,
       G_SHOW
    FROM ams_campaign_schedules_b
    WHERE schedule_id = l_act_metric_used_by_id;

Line: 4227

    SELECT G_EVEH, event_header_id, currency_code_tc, G_RCAM, program_id,
       decode(system_status_code, G_CANCELLED, Fnd_Api.G_TRUE, Fnd_Api.G_FALSE) cancelled_flag,
       G_SHOW
    FROM ams_event_headers_all_b
    WHERE event_header_id = l_act_metric_used_by_id;

Line: 4236

    SELECT event_object_type, event_offer_id, currency_code_tc,
       decode(parent_type,null,G_EVEH,parent_type) parent_type,
       decode(parent_type,null, event_header_id, parent_id) parent_id,
       decode(system_status_code, G_CANCELLED, Fnd_Api.G_TRUE, Fnd_Api.G_FALSE) cancelled_flag,
       G_SHOW
    FROM ams_event_offers_all_b
    WHERE event_offer_id = l_act_metric_used_by_id;

Line: 4246

    SELECT G_DELV, deliverable_id, transaction_currency_code, NULL, NULL,
       decode(status_code, G_CANCELLED, Fnd_Api.G_TRUE, Fnd_Api.G_FALSE) cancelled_flag,
       G_SHOW
    FROM ams_deliverables_all_b
    WHERE deliverable_id = l_act_metric_used_by_id;

Line: 4254

    SELECT G_ALIST, act_list_header_id, Ams_Actmetric_Pvt.DEFAULT_FUNC_CURRENCY,
       NULL, NULL, Fnd_Api.G_FALSE cancelled_flag,
       G_SHOW
    FROM ams_act_lists
    WHERE act_list_header_id = l_act_metric_used_by_id;

Line: 4343

      G_OBJECT_CACHE(l_obj_index).object_table.delete;

Line: 4389

      SELECT campaign_id obj_id, G_RCAM obj_type,
         TRANSACTION_CURRENCY_CODE currency
      FROM ams_campaigns_all_b
      START WITH campaign_id = l_act_metric_used_by_id
      CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
      UNION ALL
      SELECT campaign_id obj_id,
         decode(rollup_type,G_RCAM,G_RCAM,G_CAMP) obj_type,
         TRANSACTION_CURRENCY_CODE currency
      FROM ams_campaigns_all_b
      WHERE parent_campaign_id = l_act_metric_used_by_id
      UNION ALL
      SELECT EVENT_HEADER_ID obj_id, G_EVEH obj_type, CURRENCY_CODE_TC currency
      FROM ams_event_headers_all_b
      WHERE PROGRAM_ID = l_act_metric_used_by_id
      UNION ALL
      SELECT EVENT_OFFER_ID obj_id, G_EONE obj_type, CURRENCY_CODE_TC currency
      FROM ams_event_offers_all_b
      WHERE parent_id = l_act_metric_used_by_id
      ORDER BY obj_type, obj_id;

Line: 4411

      SELECT campaign_id obj_id,
         decode(rollup_type,G_RCAM,G_RCAM,G_CAMP) obj_type,
         TRANSACTION_CURRENCY_CODE currency
      FROM ams_campaigns_all_b
      START WITH campaign_id = l_act_metric_used_by_id
      CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
      UNION ALL
      SELECT SCHEDULE_ID obj_id, G_CSCH obj_type,
         TRANSACTION_CURRENCY_CODE currency
      FROM ams_campaign_schedules_b
      WHERE CAMPAIGN_ID = l_act_metric_used_by_id
      ORDER BY obj_type, obj_id;

Line: 4425

      SELECT campaign_id obj_id, G_RCAM obj_type,
         TRANSACTION_CURRENCY_CODE currency
      FROM ams_campaigns_all_b
      START WITH campaign_id = (SELECT PROGRAM_ID FROM ams_event_headers_all_b
             WHERE event_header_id = l_act_metric_used_by_id)
      CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
      UNION ALL
      SELECT EVENT_HEADER_ID obj_id, G_EVEH obj_type, CURRENCY_CODE_TC currency
      FROM ams_event_headers_all_b
      WHERE EVENT_HEADER_ID = l_act_metric_used_by_id
      UNION ALL
      SELECT EVENT_OFFER_ID obj_id, G_EVEO obj_type, CURRENCY_CODE_TC currency
      FROM ams_event_offers_all_b
      WHERE EVENT_HEADER_ID = l_act_metric_used_by_id
      ORDER BY obj_type, obj_id;

Line: 4442

      SELECT schedule_id obj_id, G_CSCH obj_type,TRANSACTION_CURRENCY_CODE currency
      FROM ams_campaign_schedules_b
      WHERE schedule_id = l_act_metric_used_by_id
      UNION ALL
      SELECT campaign_id obj_id,
          decode(rollup_type,G_RCAM,G_RCAM,G_CAMP) obj_type,
          TRANSACTION_CURRENCY_CODE currency
      FROM ams_campaigns_all_b
      START WITH campaign_id = (SELECT campaign_id FROM ams_campaign_schedules_b
             WHERE schedule_id = l_act_metric_used_by_id)
      CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
      ORDER BY obj_type, obj_id;

Line: 4456

      SELECT EVENT_OFFER_ID obj_id, G_EVEO obj_type, CURRENCY_CODE_TC currency
      FROM ams_event_offers_all_b
      WHERE EVENT_OFFER_ID = l_act_metric_used_by_id
      UNION ALL
      SELECT EVENT_HEADER_ID obj_id, G_EVEH obj_type, CURRENCY_CODE_TC currency
      FROM ams_event_headers_all_b
      WHERE EVENT_HEADER_ID = (SELECT event_header_id FROM ams_event_offers_all_b
            WHERE event_offer_id = l_act_metric_used_by_id)
      UNION ALL
      SELECT campaign_id obj_id, G_RCAM obj_type, TRANSACTION_CURRENCY_CODE currency
      FROM ams_campaigns_all_b
      START WITH campaign_id = (SELECT PROGRAM_ID FROM ams_event_headers_all_b
             WHERE event_header_id = (SELECT  event_header_id FROM ams_event_offers_all_b
             WHERE event_offer_id = l_act_metric_used_by_id))
      CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
      ORDER BY obj_type, obj_id;

Line: 4474

      SELECT campaign_id obj_id, G_RCAM obj_type,
         TRANSACTION_CURRENCY_CODE currency
      FROM ams_campaigns_all_b
      START WITH campaign_id = (SELECT PARENT_ID FROM ams_event_offers_all_b
             WHERE event_offer_id = l_act_metric_used_by_id)
      CONNECT BY campaign_id = PRIOR PARENT_CAMPAIGN_ID
      UNION ALL
      SELECT event_offer_id obj_id, G_EONE obj_type, CURRENCY_CODE_TC currency
      FROM ams_event_offers_all_b
      WHERE event_offer_id = l_act_metric_used_by_id
      ORDER BY obj_type, obj_id;

Line: 4487

      SELECT deliverable_id obj_id, G_DELV obj_type, transaction_currency_code currency
      FROM   ams_deliverables_all_b
      WHERE  deliverable_id = p_deliverable_id;

Line: 4492

      SELECT act_list_header_id obj_id, G_ALIST obj_type, Ams_Actmetric_Pvt.DEFAULT_FUNC_CURRENCY currency
      FROM   ams_act_lists
      WHERE  act_list_header_id = p_act_list_header_id;

Line: 4498

      SELECT dialog_id, G_DILG, NULL
      FROM AMS_DIALOGS_ALL_B
      WHERE dialog_id = p_dialog_id
      UNION ALL
      SELECT flow_component_id obj_id,component_type_code obj_type, NULL
      FROM ams_dlg_flow_comps_b
      WHERE dialog_id= p_dialog_id;

Line: 4638

SELECT campaign_id INTO l_RCAM_id FROM ams_campaigns_all_b
WHERE rollup_type = G_RCAM
AND parent_campaign_id IS NOT NULL
AND ROWNUM = 1;

Line: 4648

l_object_list.delete;

Line: 4650

SELECT campaign_id into l_camp_id FROM ams_campaigns_all_b
WHERE PARENT_CAMPAIGN_ID IS NOT NULL
AND ROWNUM = 1;

Line: 4659

l_object_list.delete;

Line: 4661

SELECT schedule_id INTO l_csch_id FROM ams_campaign_schedules_b
WHERE campaign_id IS NOT NULL
AND ROWNUM = 1;

Line: 4670

l_object_list.delete;

Line: 4672

SELECT event_header_id INTO l_eveh_id FROM ams_event_headers_all_b
WHERE PROGRAM_ID IS NOT NULL
AND ROWNUM = 1;

Line: 4681

l_object_list.delete;

Line: 4683

SELECT event_offer_id INTO l_eveo_id FROM ams_event_offers_all_b
WHERE event_header_id IS NOT NULL
AND ROWNUM = 1;

Line: 4692

l_object_list.delete;

Line: 4694

SELECT event_offer_id INTO l_eone_id FROM ams_event_offers_all_b
WHERE PARENT_ID IS NOT NULL
AND ROWNUM = 1;

Line: 4703

l_object_list.delete;

Line: 4722

PROCEDURE Update_History(p_commit IN VARCHAR2)
IS
   L_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_HISTORY';

Line: 4727

      SELECT ACT_MET_HST_ID,
         a.ACTIVITY_METRIC_ID,
         a.LAST_UPDATE_DATE,
         a.LAST_UPDATED_BY,
         a.CREATION_DATE,
         a.CREATED_BY,
         a.LAST_UPDATE_LOGIN,
         a.OBJECT_VERSION_NUMBER,
         a.ACT_METRIC_USED_BY_ID,
         a.ARC_ACT_METRIC_USED_BY,
         a.APPLICATION_ID,
         a.METRIC_ID,
         a.TRANSACTION_CURRENCY_CODE,
         a.TRANS_FORECASTED_VALUE,
         a.TRANS_COMMITTED_VALUE,
         a.TRANS_ACTUAL_VALUE,
         a.FUNCTIONAL_CURRENCY_CODE,
         a.FUNC_FORECASTED_VALUE,
         a.FUNC_COMMITTED_VALUE,
         a.DIRTY_FLAG,
         a.FUNC_ACTUAL_VALUE,
         a.LAST_CALCULATED_DATE,
         a.VARIABLE_VALUE,
         a.COMPUTED_USING_FUNCTION_VALUE,
         a.METRIC_UOM_CODE,
         a.ORG_ID,
         a.DIFFERENCE_SINCE_LAST_CALC,
         a.ACTIVITY_METRIC_ORIGIN_ID,
         a.ARC_ACTIVITY_METRIC_ORIGIN,
         a.DAYS_SINCE_LAST_REFRESH,
         a.SUMMARIZE_TO_METRIC,
         a.ROLLUP_TO_METRIC,
         a.SCENARIO_ID,
         a.ATTRIBUTE_CATEGORY,
         a.ATTRIBUTE1,
         a.ATTRIBUTE2,
         a.ATTRIBUTE3,
         a.ATTRIBUTE4,
         a.ATTRIBUTE5,
         a.ATTRIBUTE6,
         a.ATTRIBUTE7,
         a.ATTRIBUTE8,
         a.ATTRIBUTE9,
         a.ATTRIBUTE10,
         a.ATTRIBUTE11,
         a.ATTRIBUTE12,
         a.ATTRIBUTE13,
         a.ATTRIBUTE14,
         a.ATTRIBUTE15,
         a.DESCRIPTION,
         a.ACT_METRIC_DATE,
         a.ARC_FUNCTION_USED_BY,
         a.FUNCTION_USED_BY_ID,
         a.PURCHASE_REQ_RAISED_FLAG,
         a.SENSITIVE_DATA_FLAG,
         a.BUDGET_ID,
         a.FORECASTED_VARIABLE_VALUE,
         a.HIERARCHY_ID,
         a.PUBLISHED_FLAG,
         a.PRE_FUNCTION_NAME,
         a.POST_FUNCTION_NAME,
         a.START_NODE,
         a.FROM_LEVEL,
         a.TO_LEVEL,
         a.FROM_DATE,
         a.TO_DATE,
         a.AMOUNT1,
         a.AMOUNT2,
         a.AMOUNT3,
         a.PERCENT1,
         a.PERCENT2,
         a.PERCENT3,
         a.STATUS_CODE,
         a.ACTION_CODE,
         a.METHOD_CODE,
         a.BASIS_YEAR,
         a.EX_START_NODE,
         a.HIERARCHY_TYPE,
         a.DEPEND_ACT_METRIC,
         b.FUNC_FORECASTED_DELTA,
         b.FUNC_ACTUAL_DELTA
         FROM ams_act_metrics_all a, ams_act_metric_hst b
         WHERE b.activity_metric_id = a.activity_metric_id
         AND trunc(a.last_update_date) = trunc(b.last_update_date)
         AND a.last_update_date > b.last_update_date
         AND b.last_update_date =
            (SELECT MAX(last_update_date)
             FROM ams_act_metric_hst c
             WHERE c.activity_metric_id = b.activity_metric_id)
         ;

Line: 4820

   l_LAST_UPDATE_DATE date_table_type;

Line: 4821

   l_LAST_UPDATED_BY num_table_type;

Line: 4824

   l_LAST_UPDATE_LOGIN num_table_type;

Line: 4905

   CURSOR c_get_deleted_today IS
      SELECT act_met_hst_id
      FROM ams_act_metric_hst a
      WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
            WHERE a.activity_metric_id = b.activity_metric_id)
      AND last_update_date =
            (SELECT MAX(c.last_update_date)
            FROM ams_act_metric_hst c
            WHERE c.activity_metric_id = a.activity_metric_id)
      AND trunc(last_update_date) = trunc(l_today)
      AND (NVL(func_actual_value,0) <> 0 OR NVL(func_forecasted_value,0) <> 0);

Line: 4924

   INSERT INTO ams_act_metric_hst
       (ACT_MET_HST_ID,
         ACTIVITY_METRIC_ID,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATION_DATE,
         CREATED_BY,
         LAST_UPDATE_LOGIN,
         OBJECT_VERSION_NUMBER,
         ACT_METRIC_USED_BY_ID,
         ARC_ACT_METRIC_USED_BY,
         APPLICATION_ID,
         METRIC_ID,
         TRANSACTION_CURRENCY_CODE,
         TRANS_FORECASTED_VALUE,
         TRANS_COMMITTED_VALUE,
         TRANS_ACTUAL_VALUE,
         FUNCTIONAL_CURRENCY_CODE,
         FUNC_FORECASTED_VALUE,
         FUNC_COMMITTED_VALUE,
         DIRTY_FLAG,
         FUNC_ACTUAL_VALUE,
         LAST_CALCULATED_DATE,
         VARIABLE_VALUE,
         COMPUTED_USING_FUNCTION_VALUE,
         METRIC_UOM_CODE,
         ORG_ID,
         DIFFERENCE_SINCE_LAST_CALC,
         ACTIVITY_METRIC_ORIGIN_ID,
         ARC_ACTIVITY_METRIC_ORIGIN,
         DAYS_SINCE_LAST_REFRESH,
         SUMMARIZE_TO_METRIC,
         ROLLUP_TO_METRIC,
         SCENARIO_ID,
         ATTRIBUTE_CATEGORY,
         ATTRIBUTE1,
         ATTRIBUTE2,
         ATTRIBUTE3,
         ATTRIBUTE4,
         ATTRIBUTE5,
         ATTRIBUTE6,
         ATTRIBUTE7,
         ATTRIBUTE8,
         ATTRIBUTE9,
         ATTRIBUTE10,
         ATTRIBUTE11,
         ATTRIBUTE12,
         ATTRIBUTE13,
         ATTRIBUTE14,
         ATTRIBUTE15,
         DESCRIPTION,
         ACT_METRIC_DATE,
         ARC_FUNCTION_USED_BY,
         FUNCTION_USED_BY_ID,
         PURCHASE_REQ_RAISED_FLAG,
         SENSITIVE_DATA_FLAG,
         BUDGET_ID,
         FORECASTED_VARIABLE_VALUE,
         HIERARCHY_ID,
         PUBLISHED_FLAG,
         PRE_FUNCTION_NAME,
         POST_FUNCTION_NAME,
         START_NODE,
         FROM_LEVEL,
         TO_LEVEL,
         FROM_DATE,
         TO_DATE,
         AMOUNT1,
         AMOUNT2,
         AMOUNT3,
         PERCENT1,
         PERCENT2,
         PERCENT3,
         STATUS_CODE,
         ACTION_CODE,
         METHOD_CODE,
         BASIS_YEAR,
         EX_START_NODE,
         HIERARCHY_TYPE,
         DEPEND_ACT_METRIC,
         FUNC_FORECASTED_DELTA,
         FUNC_ACTUAL_DELTA)
    SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,
         a.ACTIVITY_METRIC_ID,
         a.LAST_UPDATE_DATE,
         a.LAST_UPDATED_BY,
         a.CREATION_DATE,
         a.CREATED_BY,
         a.LAST_UPDATE_LOGIN,
         a.OBJECT_VERSION_NUMBER,
         a.ACT_METRIC_USED_BY_ID,
         a.ARC_ACT_METRIC_USED_BY,
         a.APPLICATION_ID,
         a.METRIC_ID,
         a.TRANSACTION_CURRENCY_CODE,
         a.TRANS_FORECASTED_VALUE,
         a.TRANS_COMMITTED_VALUE,
         a.TRANS_ACTUAL_VALUE,
         a.FUNCTIONAL_CURRENCY_CODE,
         a.FUNC_FORECASTED_VALUE,
         a.FUNC_COMMITTED_VALUE,
         a.DIRTY_FLAG,
         a.FUNC_ACTUAL_VALUE,
         a.LAST_CALCULATED_DATE,
         a.VARIABLE_VALUE,
         a.COMPUTED_USING_FUNCTION_VALUE,
         a.METRIC_UOM_CODE,
         a.ORG_ID,
         a.DIFFERENCE_SINCE_LAST_CALC,
         a.ACTIVITY_METRIC_ORIGIN_ID,
         a.ARC_ACTIVITY_METRIC_ORIGIN,
         a.DAYS_SINCE_LAST_REFRESH,
         a.SUMMARIZE_TO_METRIC,
         a.ROLLUP_TO_METRIC,
         a.SCENARIO_ID,
         a.ATTRIBUTE_CATEGORY,
         a.ATTRIBUTE1,
         a.ATTRIBUTE2,
         a.ATTRIBUTE3,
         a.ATTRIBUTE4,
         a.ATTRIBUTE5,
         a.ATTRIBUTE6,
         a.ATTRIBUTE7,
         a.ATTRIBUTE8,
         a.ATTRIBUTE9,
         a.ATTRIBUTE10,
         a.ATTRIBUTE11,
         a.ATTRIBUTE12,
         a.ATTRIBUTE13,
         a.ATTRIBUTE14,
         a.ATTRIBUTE15,
         a.DESCRIPTION,
         a.ACT_METRIC_DATE,
         a.ARC_FUNCTION_USED_BY,
         a.FUNCTION_USED_BY_ID,
         a.PURCHASE_REQ_RAISED_FLAG,
         a.SENSITIVE_DATA_FLAG,
         a.BUDGET_ID,
         a.FORECASTED_VARIABLE_VALUE,
         a.HIERARCHY_ID,
         a.PUBLISHED_FLAG,
         a.PRE_FUNCTION_NAME,
         a.POST_FUNCTION_NAME,
         a.START_NODE,
         a.FROM_LEVEL,
         a.TO_LEVEL,
         a.FROM_DATE,
         a.TO_DATE,
         a.AMOUNT1,
         a.AMOUNT2,
         a.AMOUNT3,
         a.PERCENT1,
         a.PERCENT2,
         a.PERCENT3,
         a.STATUS_CODE,
         a.ACTION_CODE,
         a.METHOD_CODE,
         a.BASIS_YEAR,
         a.EX_START_NODE,
         a.HIERARCHY_TYPE,
         a.DEPEND_ACT_METRIC,
         NVL(a.FUNC_FORECASTED_VALUE,0) - NVL(b.FUNC_FORECASTED_VALUE,0),
         NVL(a.FUNC_ACTUAL_VALUE,0) - NVL(b.FUNC_ACTUAL_VALUE,0)
         FROM ams_act_metrics_all a, ams_act_metric_hst b
         WHERE a.activity_metric_id = b.activity_metric_id
         AND b.last_update_date =
            (SELECT MAX(last_update_date)
             FROM ams_act_metric_hst c
             WHERE c.activity_metric_id = a.activity_metric_id)
         AND TRUNC(a.last_update_date) > TRUNC(b.last_update_date)
         ;

Line: 5096

      write_msg(L_API_NAME, 'INSERTED EXISTING METRICS: '||SQL%ROWCOUNT);

Line: 5108

  INSERT INTO ams_act_metric_hst
       (ACT_MET_HST_ID,
         ACTIVITY_METRIC_ID,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATION_DATE,
         CREATED_BY,
         LAST_UPDATE_LOGIN,
         OBJECT_VERSION_NUMBER,
         ACT_METRIC_USED_BY_ID,
         ARC_ACT_METRIC_USED_BY,
         APPLICATION_ID,
         METRIC_ID,
         TRANSACTION_CURRENCY_CODE,
         TRANS_FORECASTED_VALUE,
         TRANS_COMMITTED_VALUE,
         TRANS_ACTUAL_VALUE,
         FUNCTIONAL_CURRENCY_CODE,
         FUNC_FORECASTED_VALUE,
         FUNC_COMMITTED_VALUE,
         DIRTY_FLAG,
         FUNC_ACTUAL_VALUE,
         LAST_CALCULATED_DATE,
         VARIABLE_VALUE,
         COMPUTED_USING_FUNCTION_VALUE,
         METRIC_UOM_CODE,
         ORG_ID,
         DIFFERENCE_SINCE_LAST_CALC,
         ACTIVITY_METRIC_ORIGIN_ID,
         ARC_ACTIVITY_METRIC_ORIGIN,
         DAYS_SINCE_LAST_REFRESH,
         SUMMARIZE_TO_METRIC,
         ROLLUP_TO_METRIC,
         SCENARIO_ID,
         ATTRIBUTE_CATEGORY,
         ATTRIBUTE1,
         ATTRIBUTE2,
         ATTRIBUTE3,
         ATTRIBUTE4,
         ATTRIBUTE5,
         ATTRIBUTE6,
         ATTRIBUTE7,
         ATTRIBUTE8,
         ATTRIBUTE9,
         ATTRIBUTE10,
         ATTRIBUTE11,
         ATTRIBUTE12,
         ATTRIBUTE13,
         ATTRIBUTE14,
         ATTRIBUTE15,
         DESCRIPTION,
         ACT_METRIC_DATE,
         ARC_FUNCTION_USED_BY,
         FUNCTION_USED_BY_ID,
         PURCHASE_REQ_RAISED_FLAG,
         SENSITIVE_DATA_FLAG,
         BUDGET_ID,
         FORECASTED_VARIABLE_VALUE,
         HIERARCHY_ID,
         PUBLISHED_FLAG,
         PRE_FUNCTION_NAME,
         POST_FUNCTION_NAME,
         START_NODE,
         FROM_LEVEL,
         TO_LEVEL,
         FROM_DATE,
         TO_DATE,
         AMOUNT1,
         AMOUNT2,
         AMOUNT3,
         PERCENT1,
         PERCENT2,
         PERCENT3,
         STATUS_CODE,
         ACTION_CODE,
         METHOD_CODE,
         BASIS_YEAR,
         EX_START_NODE,
         HIERARCHY_TYPE,
         DEPEND_ACT_METRIC,
         FUNC_FORECASTED_DELTA,
         FUNC_ACTUAL_DELTA)
    SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,
         a.ACTIVITY_METRIC_ID,
         a.LAST_UPDATE_DATE,
         a.LAST_UPDATED_BY,
         a.CREATION_DATE,
         a.CREATED_BY,
         a.LAST_UPDATE_LOGIN,
         a.OBJECT_VERSION_NUMBER,
         a.ACT_METRIC_USED_BY_ID,
         a.ARC_ACT_METRIC_USED_BY,
         a.APPLICATION_ID,
         a.METRIC_ID,
         a.TRANSACTION_CURRENCY_CODE,
         a.TRANS_FORECASTED_VALUE,
         a.TRANS_COMMITTED_VALUE,
         a.TRANS_ACTUAL_VALUE,
         a.FUNCTIONAL_CURRENCY_CODE,
         a.FUNC_FORECASTED_VALUE,
         a.FUNC_COMMITTED_VALUE,
         a.DIRTY_FLAG,
         a.FUNC_ACTUAL_VALUE,
         a.LAST_CALCULATED_DATE,
         a.VARIABLE_VALUE,
         a.COMPUTED_USING_FUNCTION_VALUE,
         a.METRIC_UOM_CODE,
         a.ORG_ID,
         a.DIFFERENCE_SINCE_LAST_CALC,
         a.ACTIVITY_METRIC_ORIGIN_ID,
         a.ARC_ACTIVITY_METRIC_ORIGIN,
         a.DAYS_SINCE_LAST_REFRESH,
         a.SUMMARIZE_TO_METRIC,
         a.ROLLUP_TO_METRIC,
         a.SCENARIO_ID,
         a.ATTRIBUTE_CATEGORY,
         a.ATTRIBUTE1,
         a.ATTRIBUTE2,
         a.ATTRIBUTE3,
         a.ATTRIBUTE4,
         a.ATTRIBUTE5,
         a.ATTRIBUTE6,
         a.ATTRIBUTE7,
         a.ATTRIBUTE8,
         a.ATTRIBUTE9,
         a.ATTRIBUTE10,
         a.ATTRIBUTE11,
         a.ATTRIBUTE12,
         a.ATTRIBUTE13,
         a.ATTRIBUTE14,
         a.ATTRIBUTE15,
         a.DESCRIPTION,
         a.ACT_METRIC_DATE,
         a.ARC_FUNCTION_USED_BY,
         a.FUNCTION_USED_BY_ID,
         a.PURCHASE_REQ_RAISED_FLAG,
         a.SENSITIVE_DATA_FLAG,
         a.BUDGET_ID,
         a.FORECASTED_VARIABLE_VALUE,
         a.HIERARCHY_ID,
         a.PUBLISHED_FLAG,
         a.PRE_FUNCTION_NAME,
         a.POST_FUNCTION_NAME,
         a.START_NODE,
         a.FROM_LEVEL,
         a.TO_LEVEL,
         a.FROM_DATE,
         a.TO_DATE,
         a.AMOUNT1,
         a.AMOUNT2,
         a.AMOUNT3,
         a.PERCENT1,
         a.PERCENT2,
         a.PERCENT3,
         a.STATUS_CODE,
         a.ACTION_CODE,
         a.METHOD_CODE,
         a.BASIS_YEAR,
         a.EX_START_NODE,
         a.HIERARCHY_TYPE,
         a.DEPEND_ACT_METRIC,
         -- BUG2214496: Initialize to original value.
         a.FUNC_FORECASTED_VALUE,
         a.FUNC_ACTUAL_VALUE
         FROM ams_act_metrics_all a
         WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metric_hst b
                  WHERE a.activity_metric_id = b.activity_metric_id);

Line: 5276

      write_msg(L_API_NAME, 'INSERTED new metrics: '|| SQL%ROWCOUNT);

Line: 5292

         l_LAST_UPDATE_DATE,
         l_LAST_UPDATED_BY,
         l_CREATION_DATE,
         l_CREATED_BY,
         l_LAST_UPDATE_LOGIN,
         l_OBJECT_VERSION_NUMBER,
         l_ACT_METRIC_USED_BY_ID,
         l_ARC_ACT_METRIC_USED_BY,
         l_APPLICATION_ID,
         l_METRIC_ID,
         l_TRANSACTION_CURRENCY_CODE,
         l_TRANS_FORECASTED_VALUE,
         l_TRANS_COMMITTED_VALUE,
         l_TRANS_ACTUAL_VALUE,
         l_FUNCTIONAL_CURRENCY_CODE,
         l_FUNC_FORECASTED_VALUE,
         l_FUNC_COMMITTED_VALUE,
         l_DIRTY_FLAG,
         l_FUNC_ACTUAL_VALUE,
         l_LAST_CALCULATED_DATE,
         l_VARIABLE_VALUE,
         l_COMPUTED_USING_FUNCTION_VALU,
         l_METRIC_UOM_CODE,
         l_ORG_ID,
         l_DIFFERENCE_SINCE_LAST_CALC,
         l_ACTIVITY_METRIC_ORIGIN_ID,
         l_ARC_ACTIVITY_METRIC_ORIGIN,
         l_DAYS_SINCE_LAST_REFRESH,
         l_SUMMARIZE_TO_METRIC,
         l_ROLLUP_TO_METRIC,
         l_SCENARIO_ID,
         l_ATTRIBUTE_CATEGORY,
         l_ATTRIBUTE1,
         l_ATTRIBUTE2,
         l_ATTRIBUTE3,
         l_ATTRIBUTE4,
         l_ATTRIBUTE5,
         l_ATTRIBUTE6,
         l_ATTRIBUTE7,
         l_ATTRIBUTE8,
         l_ATTRIBUTE9,
         l_ATTRIBUTE10,
         l_ATTRIBUTE11,
         l_ATTRIBUTE12,
         l_ATTRIBUTE13,
         l_ATTRIBUTE14,
         l_ATTRIBUTE15,
         l_DESCRIPTION,
         l_ACT_METRIC_DATE,
         l_ARC_FUNCTION_USED_BY,
         l_FUNCTION_USED_BY_ID,
         l_PURCHASE_REQ_RAISED_FLAG,
         l_SENSITIVE_DATA_FLAG,
         l_BUDGET_ID,
         l_FORECASTED_VARIABLE_VALUE,
         l_HIERARCHY_ID,
         l_PUBLISHED_FLAG,
         l_PRE_FUNCTION_NAME,
         l_POST_FUNCTION_NAME,
         l_START_NODE,
         l_FROM_LEVEL,
         l_TO_LEVEL,
         l_FROM_DATE,
         l_TO_DATE,
         l_AMOUNT1,
         l_AMOUNT2,
         l_AMOUNT3,
         l_PERCENT1,
         l_PERCENT2,
         l_PERCENT3,
         l_STATUS_CODE,
         l_ACTION_CODE,
         l_METHOD_CODE,
         l_BASIS_YEAR,
         l_EX_START_NODE,
         l_HIERARCHY_TYPE,
         l_DEPEND_ACT_METRIC,
         l_FUNC_FORECASTED_DELTA,
         l_FUNC_ACTUAL_DELTA;

Line: 5374

      write_msg(L_API_NAME,'UPDATE repeat updates selected: '||l_act_met_hst_id.COUNT);

Line: 5381

         UPDATE ams_act_metric_hst
         SET LAST_UPDATE_DATE = l_LAST_UPDATE_DATE(l_count),
            LAST_UPDATED_BY = l_LAST_UPDATED_BY(l_count),
            CREATION_DATE = l_CREATION_DATE(l_count),
            CREATED_BY = l_CREATED_BY(l_count),
            LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN(l_count),
            OBJECT_VERSION_NUMBER = l_OBJECT_VERSION_NUMBER(l_count),
            ACT_METRIC_USED_BY_ID = l_ACT_METRIC_USED_BY_ID(l_count),
            ARC_ACT_METRIC_USED_BY = l_ARC_ACT_METRIC_USED_BY(l_count),
            APPLICATION_ID = l_APPLICATION_ID(l_count),
            METRIC_ID = l_METRIC_ID(l_count),
            TRANSACTION_CURRENCY_CODE = l_TRANSACTION_CURRENCY_CODE(l_count),
            TRANS_FORECASTED_VALUE = l_TRANS_FORECASTED_VALUE(l_count),
            TRANS_COMMITTED_VALUE = l_TRANS_COMMITTED_VALUE(l_count),
            TRANS_ACTUAL_VALUE = l_TRANS_ACTUAL_VALUE(l_count),
            FUNCTIONAL_CURRENCY_CODE = l_FUNCTIONAL_CURRENCY_CODE(l_count),
            FUNC_FORECASTED_VALUE = l_FUNC_FORECASTED_VALUE(l_count),
            FUNC_COMMITTED_VALUE = l_FUNC_COMMITTED_VALUE(l_count),
            DIRTY_FLAG = l_DIRTY_FLAG(l_count),
            FUNC_ACTUAL_VALUE = l_FUNC_ACTUAL_VALUE(l_count),
            LAST_CALCULATED_DATE = l_LAST_CALCULATED_DATE(l_count),
            VARIABLE_VALUE = l_VARIABLE_VALUE(l_count),
            COMPUTED_USING_FUNCTION_VALUE = l_COMPUTED_USING_FUNCTION_VALU(l_count),
            METRIC_UOM_CODE = l_METRIC_UOM_CODE(l_count),
            ORG_ID = l_ORG_ID(l_count),
            DIFFERENCE_SINCE_LAST_CALC = l_DIFFERENCE_SINCE_LAST_CALC(l_count),
            ACTIVITY_METRIC_ORIGIN_ID = l_ACTIVITY_METRIC_ORIGIN_ID(l_count),
            ARC_ACTIVITY_METRIC_ORIGIN = l_ARC_ACTIVITY_METRIC_ORIGIN(l_count),
            DAYS_SINCE_LAST_REFRESH = l_DAYS_SINCE_LAST_REFRESH(l_count),
            SUMMARIZE_TO_METRIC = l_SUMMARIZE_TO_METRIC(l_count),
            ROLLUP_TO_METRIC = l_ROLLUP_TO_METRIC(l_count),
            SCENARIO_ID = l_SCENARIO_ID(l_count),
            ATTRIBUTE_CATEGORY = l_ATTRIBUTE_CATEGORY(l_count),
            ATTRIBUTE1 = l_ATTRIBUTE1(l_count),
            ATTRIBUTE2 = l_ATTRIBUTE2(l_count),
            ATTRIBUTE3 = l_ATTRIBUTE3(l_count),
            ATTRIBUTE4 = l_ATTRIBUTE4(l_count),
            ATTRIBUTE5 = l_ATTRIBUTE5(l_count),
            ATTRIBUTE6 = l_ATTRIBUTE6(l_count),
            ATTRIBUTE7 = l_ATTRIBUTE7(l_count),
            ATTRIBUTE8 = l_ATTRIBUTE8(l_count),
            ATTRIBUTE9 = l_ATTRIBUTE9(l_count),
            ATTRIBUTE10 = l_ATTRIBUTE10(l_count),
            ATTRIBUTE11 = l_ATTRIBUTE11(l_count),
            ATTRIBUTE12 = l_ATTRIBUTE12(l_count),
            ATTRIBUTE13 = l_ATTRIBUTE13(l_count),
            ATTRIBUTE14 = l_ATTRIBUTE14(l_count),
            ATTRIBUTE15 = l_ATTRIBUTE15(l_count),
            DESCRIPTION = l_DESCRIPTION(l_count),
            ACT_METRIC_DATE = l_ACT_METRIC_DATE(l_count),
            ARC_FUNCTION_USED_BY = l_ARC_FUNCTION_USED_BY(l_count),
            FUNCTION_USED_BY_ID = l_FUNCTION_USED_BY_ID(l_count),
            PURCHASE_REQ_RAISED_FLAG = l_PURCHASE_REQ_RAISED_FLAG(l_count),
            SENSITIVE_DATA_FLAG = l_SENSITIVE_DATA_FLAG(l_count),
            BUDGET_ID = l_BUDGET_ID(l_count),
            FORECASTED_VARIABLE_VALUE = l_FORECASTED_VARIABLE_VALUE(l_count),
            HIERARCHY_ID = l_HIERARCHY_ID(l_count),
            PUBLISHED_FLAG = l_PUBLISHED_FLAG(l_count),
            PRE_FUNCTION_NAME = l_PRE_FUNCTION_NAME(l_count),
            POST_FUNCTION_NAME = l_POST_FUNCTION_NAME(l_count),
            START_NODE = l_START_NODE(l_count),
            FROM_LEVEL = l_FROM_LEVEL(l_count),
            TO_LEVEL = l_TO_LEVEL(l_count),
            FROM_DATE = l_FROM_DATE(l_count),
            TO_DATE = l_TO_DATE(l_count),
            AMOUNT1 = l_AMOUNT1(l_count),
            AMOUNT2 = l_AMOUNT2(l_count),
            AMOUNT3 = l_AMOUNT3(l_count),
            PERCENT1 = l_PERCENT1(l_count),
            PERCENT2 = l_PERCENT2(l_count),
            PERCENT3 = l_PERCENT3(l_count),
            STATUS_CODE = l_STATUS_CODE(l_count),
            ACTION_CODE = l_ACTION_CODE(l_count),
            METHOD_CODE = l_METHOD_CODE(l_count),
            BASIS_YEAR = l_BASIS_YEAR(l_count),
            EX_START_NODE = l_EX_START_NODE(l_count),
            HIERARCHY_TYPE = l_HIERARCHY_TYPE(l_count),
            DEPEND_ACT_METRIC = l_DEPEND_ACT_METRIC(l_count),
             -- BUG2214496: Wrap values with NVL.
             FUNC_FORECASTED_DELTA = NVL(l_FUNC_FORECASTED_DELTA(l_count),0) +
                     NVL(l_FUNC_FORECASTED_VALUE(l_count),0) -
                     NVL(FUNC_FORECASTED_VALUE,0),
             FUNC_ACTUAL_DELTA = NVL(l_FUNC_ACTUAL_DELTA(l_count),0) +
                     NVL(l_FUNC_ACTUAL_VALUE(l_count),0) -
                     NVL(FUNC_ACTUAL_VALUE,0)
         WHERE act_met_hst_id = l_ACT_MET_HST_ID(l_count);

Line: 5468

         l_ACT_MET_HST_ID.delete;

Line: 5469

         l_ACTIVITY_METRIC_ID.delete;

Line: 5470

         l_LAST_UPDATE_DATE.delete;

Line: 5471

         l_LAST_UPDATED_BY.delete;

Line: 5472

         l_CREATION_DATE.delete;

Line: 5473

         l_CREATED_BY.delete;

Line: 5474

         l_LAST_UPDATE_LOGIN.delete;

Line: 5475

         l_OBJECT_VERSION_NUMBER.delete;

Line: 5476

         l_ACT_METRIC_USED_BY_ID.delete;

Line: 5477

         l_ARC_ACT_METRIC_USED_BY.delete;

Line: 5478

         l_APPLICATION_ID.delete;

Line: 5479

         l_METRIC_ID.delete;

Line: 5480

         l_TRANSACTION_CURRENCY_CODE.delete;

Line: 5481

         l_TRANS_FORECASTED_VALUE.delete;

Line: 5482

         l_TRANS_COMMITTED_VALUE.delete;

Line: 5483

         l_TRANS_ACTUAL_VALUE.delete;

Line: 5484

         l_FUNCTIONAL_CURRENCY_CODE.delete;

Line: 5485

         l_FUNC_FORECASTED_VALUE.delete;

Line: 5486

         l_FUNC_COMMITTED_VALUE.delete;

Line: 5487

         l_DIRTY_FLAG.delete;

Line: 5488

         l_FUNC_ACTUAL_VALUE.delete;

Line: 5489

         l_LAST_CALCULATED_DATE.delete;

Line: 5490

         l_VARIABLE_VALUE.delete;

Line: 5491

         l_COMPUTED_USING_FUNCTION_VALU.delete;

Line: 5492

         l_METRIC_UOM_CODE.delete;

Line: 5493

         l_ORG_ID.delete;

Line: 5494

         l_DIFFERENCE_SINCE_LAST_CALC.delete;

Line: 5495

         l_ACTIVITY_METRIC_ORIGIN_ID.delete;

Line: 5496

         l_ARC_ACTIVITY_METRIC_ORIGIN.delete;

Line: 5497

         l_DAYS_SINCE_LAST_REFRESH.delete;

Line: 5498

         l_SUMMARIZE_TO_METRIC.delete;

Line: 5499

         l_ROLLUP_TO_METRIC.delete;

Line: 5500

         l_SCENARIO_ID.delete;

Line: 5501

         l_ATTRIBUTE_CATEGORY.delete;

Line: 5502

         l_ATTRIBUTE1.delete;

Line: 5503

         l_ATTRIBUTE2.delete;

Line: 5504

         l_ATTRIBUTE3.delete;

Line: 5505

         l_ATTRIBUTE4.delete;

Line: 5506

         l_ATTRIBUTE5.delete;

Line: 5507

         l_ATTRIBUTE6.delete;

Line: 5508

         l_ATTRIBUTE7.delete;

Line: 5509

         l_ATTRIBUTE8.delete;

Line: 5510

         l_ATTRIBUTE9.delete;

Line: 5511

         l_ATTRIBUTE10.delete;

Line: 5512

         l_ATTRIBUTE11.delete;

Line: 5513

         l_ATTRIBUTE12.delete;

Line: 5514

         l_ATTRIBUTE13.delete;

Line: 5515

         l_ATTRIBUTE14.delete;

Line: 5516

         l_ATTRIBUTE15.delete;

Line: 5517

         l_DESCRIPTION.delete;

Line: 5518

         l_ACT_METRIC_DATE.delete;

Line: 5519

         l_ARC_FUNCTION_USED_BY.delete;

Line: 5520

         l_FUNCTION_USED_BY_ID.delete;

Line: 5521

         l_PURCHASE_REQ_RAISED_FLAG.delete;

Line: 5522

         l_SENSITIVE_DATA_FLAG.delete;

Line: 5523

         l_BUDGET_ID.delete;

Line: 5524

         l_FORECASTED_VARIABLE_VALUE.delete;

Line: 5525

         l_HIERARCHY_ID.delete;

Line: 5526

         l_PUBLISHED_FLAG.delete;

Line: 5527

         l_PRE_FUNCTION_NAME.delete;

Line: 5528

         l_POST_FUNCTION_NAME.delete;

Line: 5529

         l_START_NODE.delete;

Line: 5530

         l_FROM_LEVEL.delete;

Line: 5531

         l_TO_LEVEL.delete;

Line: 5532

         l_FROM_DATE.delete;

Line: 5533

         l_TO_DATE.delete;

Line: 5534

         l_AMOUNT1.delete;

Line: 5535

         l_AMOUNT2.delete;

Line: 5536

         l_AMOUNT3.delete;

Line: 5537

         l_PERCENT1.delete;

Line: 5538

         l_PERCENT2.delete;

Line: 5539

         l_PERCENT3.delete;

Line: 5540

         l_STATUS_CODE.delete;

Line: 5541

         l_ACTION_CODE.delete;

Line: 5542

         l_METHOD_CODE.delete;

Line: 5543

         l_BASIS_YEAR.delete;

Line: 5544

         l_EX_START_NODE.delete;

Line: 5545

         l_HIERARCHY_TYPE.delete;

Line: 5546

         l_DEPEND_ACT_METRIC.delete;

Line: 5547

         l_FUNC_FORECASTED_DELTA.DELETE;

Line: 5548

         l_FUNC_ACTUAL_DELTA.DELETE;

Line: 5552

      write_msg(L_API_NAME, 'UPDATED repeat updates: count='||SQL%ROWCOUNT);

Line: 5562

   OPEN c_get_deleted_today;

Line: 5563

   FETCH c_get_deleted_today
         BULK COLLECT INTO l_ACT_MET_HST_ID;

Line: 5565

   CLOSE c_get_deleted_today;

Line: 5568

      write_msg(L_API_NAME, 'Deleted before today selected: '||l_ACT_MET_HST_ID.COUNT);

Line: 5573

         UPDATE ams_act_metric_hst
         SET LAST_UPDATE_DATE = SYSDATE,
             TRANS_FORECASTED_VALUE = 0,
             TRANS_COMMITTED_VALUE = 0,
             TRANS_ACTUAL_VALUE = 0,
             FUNC_FORECASTED_VALUE = 0,
             FUNC_COMMITTED_VALUE = 0,
             FUNC_ACTUAL_VALUE = 0,
             -- BUG2214496: Wrap values with NVL.
             FUNC_FORECASTED_DELTA = NVL(FUNC_FORECASTED_DELTA,0) -
                                     NVL(FUNC_FORECASTED_VALUE,0),
             FUNC_ACTUAL_DELTA = NVL(FUNC_ACTUAL_DELTA,0) -
                                 NVL(FUNC_ACTUAL_VALUE,0)
         WHERE act_met_hst_id = l_ACT_MET_HST_ID(l_count);

Line: 5587

      l_ACT_MET_HST_ID.DELETE;

Line: 5592

      write_msg(L_API_NAME, 'Deleted today UPDATED: '||SQL%ROWCOUNT);

Line: 5602

  INSERT INTO ams_act_metric_hst
       (ACT_MET_HST_ID,
         ACTIVITY_METRIC_ID,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATION_DATE,
         CREATED_BY,
         LAST_UPDATE_LOGIN,
         OBJECT_VERSION_NUMBER,
         ACT_METRIC_USED_BY_ID,
         ARC_ACT_METRIC_USED_BY,
         APPLICATION_ID,
         METRIC_ID,
         TRANSACTION_CURRENCY_CODE,
         TRANS_FORECASTED_VALUE,
         TRANS_COMMITTED_VALUE,
         TRANS_ACTUAL_VALUE,
         FUNCTIONAL_CURRENCY_CODE,
         FUNC_FORECASTED_VALUE,
         FUNC_COMMITTED_VALUE,
         DIRTY_FLAG,
         FUNC_ACTUAL_VALUE,
         LAST_CALCULATED_DATE,
         VARIABLE_VALUE,
         COMPUTED_USING_FUNCTION_VALUE,
         METRIC_UOM_CODE,
         ORG_ID,
         DIFFERENCE_SINCE_LAST_CALC,
         ACTIVITY_METRIC_ORIGIN_ID,
         ARC_ACTIVITY_METRIC_ORIGIN,
         DAYS_SINCE_LAST_REFRESH,
         SUMMARIZE_TO_METRIC,
         ROLLUP_TO_METRIC,
         SCENARIO_ID,
         ATTRIBUTE_CATEGORY,
         ATTRIBUTE1,
         ATTRIBUTE2,
         ATTRIBUTE3,
         ATTRIBUTE4,
         ATTRIBUTE5,
         ATTRIBUTE6,
         ATTRIBUTE7,
         ATTRIBUTE8,
         ATTRIBUTE9,
         ATTRIBUTE10,
         ATTRIBUTE11,
         ATTRIBUTE12,
         ATTRIBUTE13,
         ATTRIBUTE14,
         ATTRIBUTE15,
         DESCRIPTION,
         ACT_METRIC_DATE,
         ARC_FUNCTION_USED_BY,
         FUNCTION_USED_BY_ID,
         PURCHASE_REQ_RAISED_FLAG,
         SENSITIVE_DATA_FLAG,
         BUDGET_ID,
         FORECASTED_VARIABLE_VALUE,
         HIERARCHY_ID,
         PUBLISHED_FLAG,
         PRE_FUNCTION_NAME,
         POST_FUNCTION_NAME,
         START_NODE,
         FROM_LEVEL,
         TO_LEVEL,
         FROM_DATE,
         TO_DATE,
         AMOUNT1,
         AMOUNT2,
         AMOUNT3,
         PERCENT1,
         PERCENT2,
         PERCENT3,
         STATUS_CODE,
         ACTION_CODE,
         METHOD_CODE,
         BASIS_YEAR,
         EX_START_NODE,
         HIERARCHY_TYPE,
         DEPEND_ACT_METRIC,
         FUNC_FORECASTED_DELTA,
         FUNC_ACTUAL_DELTA)
    SELECT AMS_ACT_METRIC_HST_S.NEXTVAL,
         a.ACTIVITY_METRIC_ID,
         l_today,
         a.LAST_UPDATED_BY,
         a.CREATION_DATE,
         a.CREATED_BY,
         a.LAST_UPDATE_LOGIN,
         a.OBJECT_VERSION_NUMBER+1,
         a.ACT_METRIC_USED_BY_ID,
         a.ARC_ACT_METRIC_USED_BY,
         a.APPLICATION_ID,
         a.METRIC_ID,
         a.TRANSACTION_CURRENCY_CODE,
         0,
         0,
         0,
         a.FUNCTIONAL_CURRENCY_CODE,
         0,
         0,
         a.DIRTY_FLAG,
         0,
         a.LAST_CALCULATED_DATE,
         0,
         0,
         a.METRIC_UOM_CODE,
         a.ORG_ID,
         a.DIFFERENCE_SINCE_LAST_CALC,
         a.ACTIVITY_METRIC_ORIGIN_ID,
         a.ARC_ACTIVITY_METRIC_ORIGIN,
         a.DAYS_SINCE_LAST_REFRESH,
         a.SUMMARIZE_TO_METRIC,
         a.ROLLUP_TO_METRIC,
         a.SCENARIO_ID,
         a.ATTRIBUTE_CATEGORY,
         a.ATTRIBUTE1,
         a.ATTRIBUTE2,
         a.ATTRIBUTE3,
         a.ATTRIBUTE4,
         a.ATTRIBUTE5,
         a.ATTRIBUTE6,
         a.ATTRIBUTE7,
         a.ATTRIBUTE8,
         a.ATTRIBUTE9,
         a.ATTRIBUTE10,
         a.ATTRIBUTE11,
         a.ATTRIBUTE12,
         a.ATTRIBUTE13,
         a.ATTRIBUTE14,
         a.ATTRIBUTE15,
         a.DESCRIPTION,
         a.ACT_METRIC_DATE,
         a.ARC_FUNCTION_USED_BY,
         a.FUNCTION_USED_BY_ID,
         a.PURCHASE_REQ_RAISED_FLAG,
         a.SENSITIVE_DATA_FLAG,
         a.BUDGET_ID,
         0,
         a.HIERARCHY_ID,
         a.PUBLISHED_FLAG,
         a.PRE_FUNCTION_NAME,
         a.POST_FUNCTION_NAME,
         a.START_NODE,
         a.FROM_LEVEL,
         a.TO_LEVEL,
         a.FROM_DATE,
         a.TO_DATE,
         a.AMOUNT1,
         a.AMOUNT2,
         a.AMOUNT3,
         a.PERCENT1,
         a.PERCENT2,
         a.PERCENT3,
         a.STATUS_CODE,
         a.ACTION_CODE,
         a.METHOD_CODE,
         a.BASIS_YEAR,
         a.EX_START_NODE,
         a.HIERARCHY_TYPE,
         a.DEPEND_ACT_METRIC,
         -NVL(a.FUNC_FORECASTED_VALUE,0),
         -NVL(a.FUNC_ACTUAL_VALUE,0)
      FROM ams_act_metric_hst a
      WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
            WHERE a.activity_metric_id = b.activity_metric_id)
      AND last_update_date =
            (SELECT MAX(c.last_update_date)
            FROM ams_act_metric_hst c
            WHERE c.activity_metric_id = a.activity_metric_id)
      AND last_update_date < TRUNC(l_today)
      AND (NVL(func_actual_value,0) <> 0 OR NVL(func_forecasted_value,0) <> 0);

Line: 5776

      write_msg(L_API_NAME, 'Inserted delete before today: '||SQL%ROWCOUNT);

Line: 5790

END Update_History;

Line: 5812

      g_stack.delete(l_index);

Line: 5934

   G_STACK.delete;

Line: 5948

PROCEDURE Update_formulas(
         x_errbuf       OUT NOCOPY   VARCHAR2,
         x_retcode      OUT NOCOPY   NUMBER,
         p_commit       IN VARCHAR2 := Fnd_Api.G_TRUE,
         p_object_list  IN object_currency_table := Empty_object_currency_table,
         p_current_date IN date,
         p_func_currency IN varchar2
)
IS
   L_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_FORMULAS';

Line: 5973

     select a.activity_metric_id, a.metric_id ,
            a.arc_act_metric_used_by, a.act_metric_used_by_id,
            a.last_calculated_date, b.display_type
     from ams_act_metrics_all a, ams_metrics_all_b b
     where a.metric_id = b.metric_id
     and b.metric_calculation_type = G_FORMULA
     --and a.dirty_flag = G_IS_DIRTY
     order by a.metric_id;

Line: 5984

     select a.activity_metric_id, a.metric_id,
            a.arc_act_metric_used_by, a.act_metric_used_by_id,
            a.last_calculated_date, b.display_type
     from ams_act_metrics_all a, ams_metrics_all_b b
     where a.metric_id = b.metric_id
     and b.metric_calculation_type = G_FORMULA
     --and a.dirty_flag = G_IS_DIRTY
     and a.arc_act_metric_used_by = p_object_type
     and a.act_metric_used_by_id = p_object_id
     order by a.metric_id;

Line: 6009

     select source_type, source_id, source_sub_id, source_value, token,
            use_sub_id_flag
     from ams_metric_formulas f
     where f.metric_id = p_metric_id
     and f.notation_type = G_POSTFIX
     order by sequence;

Line: 6017

     select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
     from ams_act_metrics_all a
     where a.metric_id = p_metric_id
     and a.arc_act_metric_used_by = p_object_type
     and a.act_metric_used_by_id = p_object_id
     group by functional_currency_code;

Line: 6025

     select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
     from ams_act_metrics_all a, ams_metrics_all_b b
     where a.metric_id = b.metric_id
     and b.metric_category = p_category_id
     and a.arc_act_metric_used_by = p_object_type
     and a.act_metric_used_by_id = p_object_id
     group by functional_currency_code;

Line: 6034

     select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
     from ams_act_metrics_all a, ams_metrics_all_b b
     where a.metric_id = b.metric_id
     and b.metric_category = p_category_id
     and b.metric_sub_category is null
     and a.arc_act_metric_used_by = p_object_type
     and a.act_metric_used_by_id = p_object_id
     group by functional_currency_code;

Line: 6044

     select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
     from ams_act_metrics_all a, ams_metrics_all_b b
     where a.metric_id = b.metric_id
     and b.metric_category = p_category_id
     and b.metric_sub_category = p_sub_category_id
     and a.arc_act_metric_used_by = p_object_type
     and a.act_metric_used_by_id = p_object_id
     and b.metric_calculation_type in (G_MANUAL, G_FUNCTION, G_ROLLUP)
     group by functional_currency_code;

Line: 6125

            l_temp_activity_metric_ids.delete;

Line: 6126

            l_temp_metric_ids.delete;

Line: 6127

            l_temp_arc_act_metric_used_bys.delete;

Line: 6128

            l_temp_act_metric_used_by_ids.delete;

Line: 6129

            l_temp_last_calculated_dates.delete;

Line: 6130

            l_temp_display_types.delete;

Line: 6157

            l_metric_formula_table.delete;

Line: 6313

      l_metric_formula_table.delete;

Line: 6330

            UPDATE ams_act_metrics_all
               SET last_calculated_date = p_current_date,
                   last_update_date = p_current_date,
                   object_version_number = object_version_number + 1,
                   days_since_last_refresh = l_days_since_last_refreshs(l_index),
                   transaction_currency_code = l_transactional_currency_codes(l_index),
                   functional_currency_code = l_functional_currency_codes(l_index),
                   trans_forecasted_value = l_trans_forecasted_values(l_index),
                   func_forecasted_value = l_func_forecasted_values(l_index),
                   trans_actual_value = l_trans_actual_values(l_index),
                   func_actual_value = l_func_actual_values(l_index),
                   dirty_flag = G_NOT_DIRTY
             WHERE activity_metric_id = l_activity_metric_ids(l_index);

Line: 6353

      l_activity_metric_ids.delete;

Line: 6354

      l_metric_ids.delete;

Line: 6355

      l_arc_act_metric_used_bys.delete;

Line: 6356

      l_act_metric_used_by_ids.delete;

Line: 6357

      l_last_calculated_dates.delete;

Line: 6358

      l_display_types.delete;

Line: 6359

      l_functional_currency_codes.delete;

Line: 6360

      l_func_forecasted_values.delete;

Line: 6361

      l_func_actual_values.delete;

Line: 6362

      l_transactional_currency_codes.delete;

Line: 6363

      l_trans_forecasted_values.delete;

Line: 6364

      l_trans_actual_values.delete;

Line: 6365

      l_days_since_last_refreshs.delete;

Line: 6366

      l_obj_currencies.delete;

Line: 6373

end Update_formulas;

Line: 6383

PROCEDURE Update_formulas (
   p_api_version                IN  NUMBER,
   p_init_msg_list              IN  VARCHAR2 := Fnd_Api.G_FALSE,
   p_commit                     IN  VARCHAR2 := Fnd_Api.G_FALSE,

   x_return_status              OUT NOCOPY VARCHAR2,
   x_msg_count                  OUT NOCOPY NUMBER,
   x_msg_data                   OUT NOCOPY VARCHAR2,

   p_arc_act_metric_used_by     IN VARCHAR2,
   p_act_metric_used_by_id      IN NUMBER
)
is
   L_API_NAME CONSTANT VARCHAR2(100) := 'UPDATE_FORMULAS';

Line: 6406

   savepoint sp_Update_formulas;

Line: 6414

   Update_formulas(x_errbuf => l_errbuf,
                 x_retcode => l_retcode,
                 p_commit => FND_API.G_FALSE,
                 p_object_list => l_object_list,
                 p_current_date => l_current_date,
                 p_func_currency => l_default_currency);

Line: 6444

      ROLLBACK TO sp_Update_formulas;

Line: 6452

      ROLLBACK TO sp_Update_formulas;

Line: 6460

      ROLLBACK TO sp_Update_formulas;

Line: 6471

end Update_formulas;

Line: 6506

delete
from ams_act_metrics_all_denorm;

Line: 6513

   write_msg('populate_all','inserting all entries with metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));

Line: 6517

insert  into ams_act_metrics_all_denorm (creation_date,created_by,last_update_date,last_updated_by,last_update_login,act_metrics_denorm_id,metric_collection_date,object_type
,object_id,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate,dead_leads,dead_leads_ratio,opportunities
,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount,booked_revenue,invoiced_revenue
,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,invoiced_roi
,responses,contact_group_size,target_group_size
,leads_forecast,top_leads_forecast,top_leads_ratio_forecast,leads_accepted_forecast,leads_accepted_ratio_forecast
,dead_leads_forecast,dead_leads_ratio_forecast,opportunities_forecast,lead_opportunity_rate_forecast
,quotes_forecast,quotes_amount_forecast,orders_forecast,orders_amount_forecast
,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
invoiced_roi_forecast,contact_group_size_forecast,target_group_size_forecast,responses_forecast
)
select sysdate,Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,Fnd_Global.Conc_Login_Id,AMS_ACT_METRICS_ALL_DENORM_S.NEXTVAL,
sysdate,object_type,object_id
,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate
,dead_leads,dead_leads_ratio,opportunities,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount
,booked_revenue,invoiced_revenue,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,
invoiced_roi,responses,contact_group_size,target_group_size
,leads_forecast,top_leads_forecast,top_leads_ratio_forecast,leads_accepted_forecast,leads_accepted_ratio_forecast
,dead_leads_forecast,dead_leads_ratio_forecast,opportunities_forecast,lead_opportunity_rate_forecast
,quotes_forecast,quotes_amount_forecast,orders_forecast,orders_amount_forecast
,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
invoiced_roi_forecast,responses_forecast,contact_group_size_forecast,target_group_size_forecast
from (
select h1.arc_act_metric_used_by object_type,h1.act_metric_used_by_id object_id,
sum(decode(met.denorm_code,'METRIC_01',h1.func_actual_value,0)) leads,
sum(decode(met.denorm_code,'METRIC_02',h1.func_actual_value,0)) top_leads,
sum(decode(met.denorm_code,'METRIC_03',h1.func_actual_value,0)) top_leads_ratio,
sum(decode(met.denorm_code,'METRIC_04',h1.func_actual_value,0)) leads_accepted,
sum(decode(met.denorm_code,'METRIC_05',h1.func_actual_value,0)) lead_acceptance_rate,
sum(decode(met.denorm_code,'METRIC_06',h1.func_actual_value,0)) dead_leads,
sum(decode(met.denorm_code,'METRIC_07',h1.func_actual_value,0)) dead_leads_ratio,
sum(decode(met.denorm_code,'METRIC_08',h1.func_actual_value,0)) opportunities,
sum(decode(met.denorm_code,'METRIC_09',h1.func_actual_value,0)) leads_to_opportunity_rate,
sum(decode(met.denorm_code,'METRIC_10',h1.func_actual_value,0)) quotes,
sum(decode(met.denorm_code,'METRIC_11',h1.func_actual_value,0)) quotes_amount,
sum(decode(met.denorm_code,'METRIC_12',h1.func_actual_value,0)) orders,
sum(decode(met.denorm_code,'METRIC_13',h1.func_actual_value,0)) orders_amount,
sum(decode(met.denorm_code,'METRIC_14',h1.func_actual_value,0)) booked_revenue,
sum(decode(met.denorm_code,'METRIC_15',h1.func_actual_value,0)) invoiced_revenue,
sum(decode(met.denorm_code,'METRIC_16',h1.func_actual_value,0)) booked_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_17',h1.func_actual_value,0)) invoiced_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_18',h1.func_actual_value,0)) cost,
sum(decode(met.denorm_code,'METRIC_19',h1.func_actual_value,0)) cost_per_lead,
sum(decode(met.denorm_code,'METRIC_20',h1.func_actual_value,0)) booked_roi,
sum(decode(met.denorm_code,'METRIC_21',h1.func_actual_value,0)) invoiced_roi,
sum(decode(met.denorm_code,'METRIC_22',h1.func_actual_value,0)) responses,
sum(decode(met.denorm_code,'METRIC_23',h1.func_actual_value,0)) contact_group_size,
sum(decode(met.denorm_code,'METRIC_24',h1.func_actual_value,0)) target_group_size,
sum(decode(met.denorm_code,'METRIC_01',h1.func_forecasted_value,0)) leads_forecast,
sum(decode(met.denorm_code,'METRIC_02',h1.func_forecasted_value,0)) top_leads_forecast,
sum(decode(met.denorm_code,'METRIC_03',h1.func_forecasted_value,0)) top_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_04',h1.func_forecasted_value,0)) leads_accepted_forecast,
sum(decode(met.denorm_code,'METRIC_05',h1.func_forecasted_value,0)) leads_accepted_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_06',h1.func_forecasted_value,0)) dead_leads_forecast,
sum(decode(met.denorm_code,'METRIC_07',h1.func_forecasted_value,0)) dead_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_08',h1.func_forecasted_value,0)) opportunities_forecast,
sum(decode(met.denorm_code,'METRIC_09',h1.func_forecasted_value,0)) lead_opportunity_rate_forecast,
sum(decode(met.denorm_code,'METRIC_10',h1.func_forecasted_value,0)) quotes_forecast,
sum(decode(met.denorm_code,'METRIC_11',h1.func_forecasted_value,0)) quotes_amount_forecast,
sum(decode(met.denorm_code,'METRIC_12',h1.func_forecasted_value,0)) orders_forecast,
sum(decode(met.denorm_code,'METRIC_13',h1.func_forecasted_value,0)) orders_amount_forecast,
sum(decode(met.denorm_code,'METRIC_14',h1.func_forecasted_value,0)) booked_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_15',h1.func_forecasted_value,0)) invoiced_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_16',h1.func_forecasted_value,0)) booked_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_17',h1.func_forecasted_value,0)) invoiced_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_18',h1.func_forecasted_value,0)) cost_forecast,
sum(decode(met.denorm_code,'METRIC_19',h1.func_forecasted_value,0)) cost_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_20',h1.func_forecasted_value,0)) booked_roi_forecast,
sum(decode(met.denorm_code,'METRIC_21',h1.func_forecasted_value,0)) invoiced_roi_forecast,
sum(decode(met.denorm_code,'METRIC_22',h1.func_forecasted_value,0)) responses_forecast,
sum(decode(met.denorm_code,'METRIC_23',h1.func_forecasted_value,0)) contact_group_size_forecast,
sum(decode(met.denorm_code,'METRIC_24',h1.func_forecasted_value,0)) target_group_size_forecast
from ams_act_metrics_all h1, ams_metrics_all_b met
where h1.metric_id = met.metric_id
and met.denorm_code in
(
'METRIC_01'
,'METRIC_02'
,'METRIC_03'
,'METRIC_04'
,'METRIC_05'
,'METRIC_06'
,'METRIC_07'
,'METRIC_08'
,'METRIC_09'
,'METRIC_10'
,'METRIC_11'
,'METRIC_12'
,'METRIC_13'
,'METRIC_14'
,'METRIC_15'
,'METRIC_16'
,'METRIC_17'
,'METRIC_18'
,'METRIC_19'
,'METRIC_20'
,'METRIC_21'
,'METRIC_22'
,'METRIC_23'
,'METRIC_24')
group by h1.arc_act_metric_used_by ,h1.act_metric_used_by_id
);

Line: 6623

   write_msg('populate_all','Done Inserting entries ams_act_metrics_all_denorm');

Line: 6649

select distinct concat(concat(arc_act_metric_used_by,'_'),TO_CHAR(act_metric_used_by_id))
from ams_act_metrics_all
where last_update_date > p_date;

Line: 6679

update ams_act_metrics_all_denorm a
set
(
a.last_updated_by
,a.last_update_date
,a.last_update_login
,a.metric_collection_date
,a.object_version_number
,a.leads,top_leads,a.top_leads_ratio,a.leads_accepted,a.lead_acceptance_rate
,a.dead_leads,a.dead_leads_ratio,opportunities,a.leads_to_opportunity_rate,a.quotes,a.quotes_amount,a.orders,a.orders_amount
,a.booked_revenue,invoiced_revenue,a.booked_revenue_per_lead,a.invoiced_revenue_per_lead,a.cost,a.cost_per_lead,a.booked_roi
,a.invoiced_roi,a.responses,a.contact_group_size,a.target_group_size
,a.leads_forecast,a.top_leads_forecast,a.top_leads_ratio_forecast,a.leads_accepted_forecast,a.leads_accepted_ratio_forecast
,a.dead_leads_forecast,a.dead_leads_ratio_forecast,a.opportunities_forecast,a.lead_opportunity_rate_forecast
,a.quotes_forecast,a.quotes_amount_forecast,a.orders_forecast,a.orders_amount_forecast
,a.booked_revenue_forecast,a.invoiced_revenue_forecast,a.booked_rev_per_lead_forecast,a.invoiced_rev_per_lead_forecast
,a.cost_forecast,a.cost_per_lead_forecast,a.booked_roi_forecast
,a.invoiced_roi_forecast,a.responses_forecast,a.contact_group_size_forecast,a.target_group_size_forecast
) =
(select Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,sysdate,
nvl(a.object_version_number,1) + 1,
sum(decode(met.denorm_code,'METRIC_01',h1.func_actual_value,0)) leads,
sum(decode(met.denorm_code,'METRIC_02',h1.func_actual_value,0)) top_leads,
sum(decode(met.denorm_code,'METRIC_03',h1.func_actual_value,0)) top_leads_ratio,
sum(decode(met.denorm_code,'METRIC_04',h1.func_actual_value,0)) leads_accepted,
sum(decode(met.denorm_code,'METRIC_05',h1.func_actual_value,0)) lead_acceptance_rate,
sum(decode(met.denorm_code,'METRIC_06',h1.func_actual_value,0)) dead_leads,
sum(decode(met.denorm_code,'METRIC_07',h1.func_actual_value,0)) dead_leads_ratio,
sum(decode(met.denorm_code,'METRIC_08',h1.func_actual_value,0)) opportunities,
sum(decode(met.denorm_code,'METRIC_09',h1.func_actual_value,0)) leads_to_opportunity_rate,
sum(decode(met.denorm_code,'METRIC_10',h1.func_actual_value,0)) quotes,
sum(decode(met.denorm_code,'METRIC_11',h1.func_actual_value,0)) quotes_amount,
sum(decode(met.denorm_code,'METRIC_12',h1.func_actual_value,0)) orders,
sum(decode(met.denorm_code,'METRIC_13',h1.func_actual_value,0)) orders_amount,
sum(decode(met.denorm_code,'METRIC_14',h1.func_actual_value,0)) booked_revenue,
sum(decode(met.denorm_code,'METRIC_15',h1.func_actual_value,0)) invoiced_revenue,
sum(decode(met.denorm_code,'METRIC_16',h1.func_actual_value,0)) booked_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_17',h1.func_actual_value,0)) invoiced_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_18',h1.func_actual_value,0)) cost,
sum(decode(met.denorm_code,'METRIC_19',h1.func_actual_value,0)) cost_per_lead,
sum(decode(met.denorm_code,'METRIC_20',h1.func_actual_value,0)) booked_roi,
sum(decode(met.denorm_code,'METRIC_21',h1.func_actual_value,0)) invoiced_roi,
sum(decode(met.denorm_code,'METRIC_22',h1.func_actual_value,0)) responses,
sum(decode(met.denorm_code,'METRIC_23',h1.func_actual_value,0)) contact_group_size,
sum(decode(met.denorm_code,'METRIC_24',h1.func_actual_value,0)) target_group_size,
sum(decode(met.denorm_code,'METRIC_01',h1.func_forecasted_value,0)) leads_forecast,
sum(decode(met.denorm_code,'METRIC_02',h1.func_forecasted_value,0)) top_leads_forecast,
sum(decode(met.denorm_code,'METRIC_03',h1.func_forecasted_value,0)) top_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_04',h1.func_forecasted_value,0)) leads_accepted_forecast,
sum(decode(met.denorm_code,'METRIC_05',h1.func_forecasted_value,0)) leads_accepted_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_06',h1.func_forecasted_value,0)) dead_leads_forecast,
sum(decode(met.denorm_code,'METRIC_07',h1.func_forecasted_value,0)) dead_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_08',h1.func_forecasted_value,0)) opportunities_forecast,
sum(decode(met.denorm_code,'METRIC_09',h1.func_forecasted_value,0)) lead_opportunity_rate_forecast,
sum(decode(met.denorm_code,'METRIC_10',h1.func_forecasted_value,0)) quotes_forecast,
sum(decode(met.denorm_code,'METRIC_11',h1.func_forecasted_value,0)) quotes_amount_forecast,
sum(decode(met.denorm_code,'METRIC_12',h1.func_forecasted_value,0)) orders_forecast,
sum(decode(met.denorm_code,'METRIC_13',h1.func_forecasted_value,0)) orders_amount_forecast,
sum(decode(met.denorm_code,'METRIC_14',h1.func_forecasted_value,0)) booked_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_15',h1.func_forecasted_value,0)) invoiced_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_16',h1.func_forecasted_value,0)) booked_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_17',h1.func_forecasted_value,0)) invoiced_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_18',h1.func_forecasted_value,0)) cost_forecast,
sum(decode(met.denorm_code,'METRIC_19',h1.func_forecasted_value,0)) cost_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_20',h1.func_forecasted_value,0)) booked_roi_forecast,
sum(decode(met.denorm_code,'METRIC_21',h1.func_forecasted_value,0)) invoiced_roi_forecast,
sum(decode(met.denorm_code,'METRIC_22',h1.func_forecasted_value,0)) responses_forecast,
sum(decode(met.denorm_code,'METRIC_23',h1.func_forecasted_value,0)) contact_group_size_forecast,
sum(decode(met.denorm_code,'METRIC_24',h1.func_forecasted_value,0)) target_group_size_forecast
from ams_act_metrics_all h1, ams_metrics_all_b met
where h1.metric_id = met.metric_id
and met.denorm_code in
(
'METRIC_01'
,'METRIC_02'
,'METRIC_03'
,'METRIC_04'
,'METRIC_05'
,'METRIC_06'
,'METRIC_07'
,'METRIC_08'
,'METRIC_09'
,'METRIC_10'
,'METRIC_11'
,'METRIC_12'
,'METRIC_13'
,'METRIC_14'
,'METRIC_15'
,'METRIC_16'
,'METRIC_17'
,'METRIC_18'
,'METRIC_19'
,'METRIC_20'
,'METRIC_21'
,'METRIC_22'
,'METRIC_23'
,'METRIC_24')
and h1.arc_act_metric_used_by(+) = a.object_type
and h1.act_metric_used_by_id(+) = a.object_id
)
where a.last_update_date > l_calc_since
and exists (select 1 from ams_act_metrics_all amet, ams_metrics_all_b met
  where amet.last_update_date > a.last_update_date
  and amet.metric_id = met.metric_id
  and met.denorm_code is not null
  and amet.arc_act_metric_used_by = a.object_type
  and amet.act_metric_used_by_id = a.object_id
  and rownum = 1
  )
;

Line: 6791

insert  into ams_act_metrics_all_denorm (creation_date,created_by,last_update_date,last_updated_by,last_update_login
,act_metrics_denorm_id,metric_collection_date,object_type
,object_id,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate,dead_leads,dead_leads_ratio,opportunities
,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount,booked_revenue,invoiced_revenue
,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,invoiced_roi
,responses,contact_group_size,target_group_size
,leads_forecast,top_leads_forecast,top_leads_ratio_forecast,leads_accepted_forecast,leads_accepted_ratio_forecast
,dead_leads_forecast,dead_leads_ratio_forecast,opportunities_forecast,lead_opportunity_rate_forecast
,quotes_forecast,quotes_amount_forecast,orders_forecast,orders_amount_forecast
,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
invoiced_roi_forecast,contact_group_size_forecast,target_group_size_forecast,responses_forecast
)
select sysdate,Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,Fnd_Global.Conc_Login_Id,AMS_ACT_METRICS_ALL_DENORM_S.NEXTVAL,
sysdate,object_type,object_id
,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate
,dead_leads,dead_leads_ratio,opportunities,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount
,booked_revenue,invoiced_revenue,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,
invoiced_roi,responses,contact_group_size,target_group_size
,leads_forecast,top_leads_forecast,top_leads_ratio_forecast,leads_accepted_forecast,leads_accepted_ratio_forecast
,dead_leads_forecast,dead_leads_ratio_forecast,opportunities_forecast,lead_opportunity_rate_forecast
,quotes_forecast,quotes_amount_forecast,orders_forecast,orders_amount_forecast
,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
invoiced_roi_forecast,responses_forecast,contact_group_size_forecast,target_group_size_forecast
from (
select h1.arc_act_metric_used_by object_type,h1.act_metric_used_by_id object_id,
sum(decode(met.denorm_code,'METRIC_01',h1.func_actual_value,0)) leads,
sum(decode(met.denorm_code,'METRIC_02',h1.func_actual_value,0)) top_leads,
sum(decode(met.denorm_code,'METRIC_03',h1.func_actual_value,0)) top_leads_ratio,
sum(decode(met.denorm_code,'METRIC_04',h1.func_actual_value,0)) leads_accepted,
sum(decode(met.denorm_code,'METRIC_05',h1.func_actual_value,0)) lead_acceptance_rate,
sum(decode(met.denorm_code,'METRIC_06',h1.func_actual_value,0)) dead_leads,
sum(decode(met.denorm_code,'METRIC_07',h1.func_actual_value,0)) dead_leads_ratio,
sum(decode(met.denorm_code,'METRIC_08',h1.func_actual_value,0)) opportunities,
sum(decode(met.denorm_code,'METRIC_09',h1.func_actual_value,0)) leads_to_opportunity_rate,
sum(decode(met.denorm_code,'METRIC_10',h1.func_actual_value,0)) quotes,
sum(decode(met.denorm_code,'METRIC_11',h1.func_actual_value,0)) quotes_amount,
sum(decode(met.denorm_code,'METRIC_12',h1.func_actual_value,0)) orders,
sum(decode(met.denorm_code,'METRIC_13',h1.func_actual_value,0)) orders_amount,
sum(decode(met.denorm_code,'METRIC_14',h1.func_actual_value,0)) booked_revenue,
sum(decode(met.denorm_code,'METRIC_15',h1.func_actual_value,0)) invoiced_revenue,
sum(decode(met.denorm_code,'METRIC_16',h1.func_actual_value,0)) booked_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_17',h1.func_actual_value,0)) invoiced_revenue_per_lead,
sum(decode(met.denorm_code,'METRIC_18',h1.func_actual_value,0)) cost,
sum(decode(met.denorm_code,'METRIC_19',h1.func_actual_value,0)) cost_per_lead,
sum(decode(met.denorm_code,'METRIC_20',h1.func_actual_value,0)) booked_roi,
sum(decode(met.denorm_code,'METRIC_21',h1.func_actual_value,0)) invoiced_roi,
sum(decode(met.denorm_code,'METRIC_22',h1.func_actual_value,0)) responses,
sum(decode(met.denorm_code,'METRIC_23',h1.func_actual_value,0)) contact_group_size,
sum(decode(met.denorm_code,'METRIC_24',h1.func_actual_value,0)) target_group_size,
sum(decode(met.denorm_code,'METRIC_01',h1.func_forecasted_value,0)) leads_forecast,
sum(decode(met.denorm_code,'METRIC_02',h1.func_forecasted_value,0)) top_leads_forecast,
sum(decode(met.denorm_code,'METRIC_03',h1.func_forecasted_value,0)) top_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_04',h1.func_forecasted_value,0)) leads_accepted_forecast,
sum(decode(met.denorm_code,'METRIC_05',h1.func_forecasted_value,0)) leads_accepted_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_06',h1.func_forecasted_value,0)) dead_leads_forecast,
sum(decode(met.denorm_code,'METRIC_07',h1.func_forecasted_value,0)) dead_leads_ratio_forecast,
sum(decode(met.denorm_code,'METRIC_08',h1.func_forecasted_value,0)) opportunities_forecast,
sum(decode(met.denorm_code,'METRIC_09',h1.func_forecasted_value,0)) lead_opportunity_rate_forecast,
sum(decode(met.denorm_code,'METRIC_10',h1.func_forecasted_value,0)) quotes_forecast,
sum(decode(met.denorm_code,'METRIC_11',h1.func_forecasted_value,0)) quotes_amount_forecast,
sum(decode(met.denorm_code,'METRIC_12',h1.func_forecasted_value,0)) orders_forecast,
sum(decode(met.denorm_code,'METRIC_13',h1.func_forecasted_value,0)) orders_amount_forecast,
sum(decode(met.denorm_code,'METRIC_14',h1.func_forecasted_value,0)) booked_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_15',h1.func_forecasted_value,0)) invoiced_revenue_forecast,
sum(decode(met.denorm_code,'METRIC_16',h1.func_forecasted_value,0)) booked_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_17',h1.func_forecasted_value,0)) invoiced_rev_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_18',h1.func_forecasted_value,0)) cost_forecast,
sum(decode(met.denorm_code,'METRIC_19',h1.func_forecasted_value,0)) cost_per_lead_forecast,
sum(decode(met.denorm_code,'METRIC_20',h1.func_forecasted_value,0)) booked_roi_forecast,
sum(decode(met.denorm_code,'METRIC_21',h1.func_forecasted_value,0)) invoiced_roi_forecast,
sum(decode(met.denorm_code,'METRIC_22',h1.func_forecasted_value,0)) responses_forecast,
sum(decode(met.denorm_code,'METRIC_23',h1.func_forecasted_value,0)) contact_group_size_forecast,
sum(decode(met.denorm_code,'METRIC_24',h1.func_forecasted_value,0)) target_group_size_forecast
from ams_act_metrics_all h1, ams_metrics_all_b met
where h1.metric_id = met.metric_id
and met.denorm_code in
(
'METRIC_01'
,'METRIC_02'
,'METRIC_03'
,'METRIC_04'
,'METRIC_05'
,'METRIC_06'
,'METRIC_07'
,'METRIC_08'
,'METRIC_09'
,'METRIC_10'
,'METRIC_11'
,'METRIC_12'
,'METRIC_13'
,'METRIC_14'
,'METRIC_15'
,'METRIC_16'
,'METRIC_17'
,'METRIC_18'
,'METRIC_19'
,'METRIC_20'
,'METRIC_21'
,'METRIC_22'
,'METRIC_23'
,'METRIC_24')
and (h1.arc_act_metric_used_by,h1.act_metric_used_by_id) not in
(
select distinct object_type, object_id
from ams_act_metrics_all_denorm
)
group by h1.arc_act_metric_used_by ,h1.act_metric_used_by_id
);

Line: 6936

  select max(metric_collection_date)
  from ams_act_metrics_all_denorm;