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

APPS.AMS_ACTMETRICS_ENGINE_PVT dependencies on AMS_ACT_METRICS_ALL

Line 630: FROM ams_act_metrics_all a, ams_metrics_all_b b

626: metric_calculation_type ,
627: accrual_type ,
628: compute_using_function ,
629: default_uom_code, display_type
630: FROM ams_act_metrics_all a, ams_metrics_all_b b
631: WHERE arc_act_metric_used_by IN
632: (select lookup_code from ams_lookups
633: where lookup_type in ('AMS_METRIC_OBJECT_TYPE', 'AMS_METRIC_ROLLUP_TYPE'))
634: -- Replaced with metadata lookups above.

Line 666: FROM ams_act_metrics_all a, ams_metrics_all_b b

662: metric_calculation_type ,
663: accrual_type ,
664: compute_using_function ,
665: default_uom_code, display_type
666: FROM ams_act_metrics_all a, ams_metrics_all_b b
667: WHERE dirty_flag = G_IS_DIRTY
668: AND a.metric_id = b.metric_id
669: AND b.metric_calculation_type = l_calc_type
670: AND a.arc_act_metric_used_by = l_object_type

Line 705: FROM ams_act_metrics_all a, ams_metrics_all_b b

701: metric_calculation_type ,
702: accrual_type ,
703: compute_using_function ,
704: default_uom_code, display_type
705: FROM ams_act_metrics_all a, ams_metrics_all_b b
706: WHERE activity_metric_id = l_parent_act_metric_id
707: AND a.metric_id = b.metric_id;
708:
709: l_act_metric_rec act_met_ref_rec_type;--c_act_metric_rec%ROWTYPE;

Line 1058: TYPE t_actmet_id IS TABLE OF ams_act_metrics_all.activity_metric_id%TYPE;

1054: x_return_status OUT NOCOPY VARCHAR2,
1055: p_default_currency IN VARCHAR2
1056: )
1057: IS
1058: TYPE t_actmet_id IS TABLE OF ams_act_metrics_all.activity_metric_id%TYPE;
1059: TYPE t_value IS TABLE OF ams_act_metrics_all.FUNC_ACTUAL_VALUE%TYPE;
1060: TYPE t_curr IS TABLE OF ams_act_metrics_all.FUNCTIONAL_CURRENCY_CODE%TYPE;
1061: TYPE t_uom IS TABLE OF ams_act_metrics_all.METRIC_UOM_CODE%TYPE;
1062: TYPE t_objtype IS TABLE OF ams_act_metrics_all.ARC_ACT_METRIC_USED_BY%TYPE;

Line 1059: TYPE t_value IS TABLE OF ams_act_metrics_all.FUNC_ACTUAL_VALUE%TYPE;

1055: p_default_currency IN VARCHAR2
1056: )
1057: IS
1058: TYPE t_actmet_id IS TABLE OF ams_act_metrics_all.activity_metric_id%TYPE;
1059: TYPE t_value IS TABLE OF ams_act_metrics_all.FUNC_ACTUAL_VALUE%TYPE;
1060: TYPE t_curr IS TABLE OF ams_act_metrics_all.FUNCTIONAL_CURRENCY_CODE%TYPE;
1061: TYPE t_uom IS TABLE OF ams_act_metrics_all.METRIC_UOM_CODE%TYPE;
1062: TYPE t_objtype IS TABLE OF ams_act_metrics_all.ARC_ACT_METRIC_USED_BY%TYPE;
1063: TYPE t_objid IS TABLE OF ams_act_metrics_all.ACT_METRIC_USED_BY_ID%TYPE;

Line 1060: TYPE t_curr IS TABLE OF ams_act_metrics_all.FUNCTIONAL_CURRENCY_CODE%TYPE;

1056: )
1057: IS
1058: TYPE t_actmet_id IS TABLE OF ams_act_metrics_all.activity_metric_id%TYPE;
1059: TYPE t_value IS TABLE OF ams_act_metrics_all.FUNC_ACTUAL_VALUE%TYPE;
1060: TYPE t_curr IS TABLE OF ams_act_metrics_all.FUNCTIONAL_CURRENCY_CODE%TYPE;
1061: TYPE t_uom IS TABLE OF ams_act_metrics_all.METRIC_UOM_CODE%TYPE;
1062: TYPE t_objtype IS TABLE OF ams_act_metrics_all.ARC_ACT_METRIC_USED_BY%TYPE;
1063: TYPE t_objid IS TABLE OF ams_act_metrics_all.ACT_METRIC_USED_BY_ID%TYPE;
1064:

Line 1061: TYPE t_uom IS TABLE OF ams_act_metrics_all.METRIC_UOM_CODE%TYPE;

1057: IS
1058: TYPE t_actmet_id IS TABLE OF ams_act_metrics_all.activity_metric_id%TYPE;
1059: TYPE t_value IS TABLE OF ams_act_metrics_all.FUNC_ACTUAL_VALUE%TYPE;
1060: TYPE t_curr IS TABLE OF ams_act_metrics_all.FUNCTIONAL_CURRENCY_CODE%TYPE;
1061: TYPE t_uom IS TABLE OF ams_act_metrics_all.METRIC_UOM_CODE%TYPE;
1062: TYPE t_objtype IS TABLE OF ams_act_metrics_all.ARC_ACT_METRIC_USED_BY%TYPE;
1063: TYPE t_objid IS TABLE OF ams_act_metrics_all.ACT_METRIC_USED_BY_ID%TYPE;
1064:
1065: l_activity_metric_ids t_actmet_id;

Line 1062: TYPE t_objtype IS TABLE OF ams_act_metrics_all.ARC_ACT_METRIC_USED_BY%TYPE;

1058: TYPE t_actmet_id IS TABLE OF ams_act_metrics_all.activity_metric_id%TYPE;
1059: TYPE t_value IS TABLE OF ams_act_metrics_all.FUNC_ACTUAL_VALUE%TYPE;
1060: TYPE t_curr IS TABLE OF ams_act_metrics_all.FUNCTIONAL_CURRENCY_CODE%TYPE;
1061: TYPE t_uom IS TABLE OF ams_act_metrics_all.METRIC_UOM_CODE%TYPE;
1062: TYPE t_objtype IS TABLE OF ams_act_metrics_all.ARC_ACT_METRIC_USED_BY%TYPE;
1063: TYPE t_objid IS TABLE OF ams_act_metrics_all.ACT_METRIC_USED_BY_ID%TYPE;
1064:
1065: l_activity_metric_ids t_actmet_id;
1066: l_func_forecasted_values t_value;

Line 1063: TYPE t_objid IS TABLE OF ams_act_metrics_all.ACT_METRIC_USED_BY_ID%TYPE;

1059: TYPE t_value IS TABLE OF ams_act_metrics_all.FUNC_ACTUAL_VALUE%TYPE;
1060: TYPE t_curr IS TABLE OF ams_act_metrics_all.FUNCTIONAL_CURRENCY_CODE%TYPE;
1061: TYPE t_uom IS TABLE OF ams_act_metrics_all.METRIC_UOM_CODE%TYPE;
1062: TYPE t_objtype IS TABLE OF ams_act_metrics_all.ARC_ACT_METRIC_USED_BY%TYPE;
1063: TYPE t_objid IS TABLE OF ams_act_metrics_all.ACT_METRIC_USED_BY_ID%TYPE;
1064:
1065: l_activity_metric_ids t_actmet_id;
1066: l_func_forecasted_values t_value;
1067: l_func_actual_values t_value;

Line 1219: FROM ams_act_metrics_all

1215: l_functional_currency_codes,
1216: l_metric_uom_codes,
1217: l_arc_act_metric_used_bys,
1218: l_act_metric_used_by_ids
1219: FROM ams_act_metrics_all
1220: WHERE summarize_to_metric = l_id;
1221: --ORDER BY arc_act_metric_used_by, act_metric_used_by_id;
1222: ELSE
1223: SELECT activity_metric_id,

Line 1237: FROM ams_act_metrics_all

1233: l_functional_currency_codes,
1234: l_metric_uom_codes,
1235: l_arc_act_metric_used_bys,
1236: l_act_metric_used_by_ids
1237: FROM ams_act_metrics_all
1238: WHERE rollup_to_metric = l_id;
1239: --ORDER BY arc_act_metric_used_by, act_metric_used_by_id;
1240: END IF;
1241:

Line 1653: UPDATE ams_act_metrics_all

1649:
1650: IF l_act_met_id.count >= G_BATCH_SIZE
1651: OR (l_index IS NULL AND l_act_met_id.count > 0) THEN
1652: FORALL l_count IN l_act_met_id.FIRST .. l_act_met_id.LAST
1653: UPDATE ams_act_metrics_all
1654: SET last_calculated_date = l_cal_date_table (l_count),
1655: days_since_last_refresh = l_sin_last_ref(l_count),
1656: func_actual_value = l_func_act_va(l_count),
1657: trans_actual_value = l_tran_act_va(l_count),

Line 1670: UPDATE ams_act_metrics_all

1666: WHERE activity_metric_id = l_act_met_id (l_count)
1667: AND OBJECT_VERSION_NUMBER = l_obj_version(l_count);
1668: IF p_calc_type = G_ROLLUP AND l_summarize_ids.COUNT > 0 THEN
1669: FORALL l_count IN l_summarize_ids.FIRST .. l_summarize_ids.LAST
1670: UPDATE ams_act_metrics_all
1671: SET dirty_flag = G_IS_DIRTY
1672: WHERE activity_metric_id = l_summarize_ids(l_count);
1673: END IF;
1674: -- Set the formulas to dirty that are effected by this update.

Line 1676: /* update ams_act_metrics_all

1672: WHERE activity_metric_id = l_summarize_ids(l_count);
1673: END IF;
1674: -- Set the formulas to dirty that are effected by this update.
1675: FORALL l_count IN l_act_met_id.FIRST .. l_act_met_id.LAST
1676: /* update ams_act_metrics_all
1677: set dirty_flag = G_IS_DIRTY
1678: where activity_metric_id in
1679: (select a.activity_metric_id
1680: from ams_act_metrics_all a, ams_metrics_all_b m,

Line 1680: from ams_act_metrics_all a, ams_metrics_all_b m,

1676: /* update ams_act_metrics_all
1677: set dirty_flag = G_IS_DIRTY
1678: where activity_metric_id in
1679: (select a.activity_metric_id
1680: from ams_act_metrics_all a, ams_metrics_all_b m,
1681: ams_metric_formulas f, ams_act_metrics_all b, ams_metrics_all_b c
1682: where a.metric_id = m.metric_id
1683: and m.metric_id = f.metric_id
1684: and b.metric_id = c.metric_id

Line 1681: ams_metric_formulas f, ams_act_metrics_all b, ams_metrics_all_b c

1677: set dirty_flag = G_IS_DIRTY
1678: where activity_metric_id in
1679: (select a.activity_metric_id
1680: from ams_act_metrics_all a, ams_metrics_all_b m,
1681: ams_metric_formulas f, ams_act_metrics_all b, ams_metrics_all_b c
1682: where a.metric_id = m.metric_id
1683: and m.metric_id = f.metric_id
1684: and b.metric_id = c.metric_id
1685: and a.arc_act_metric_used_by = b.arc_act_metric_used_by

Line 1692: update ams_act_metrics_all

1688: and ((b.metric_id = f.source_id and f.source_type = G_METRIC)
1689: or (c.metric_category = f.source_id and f.source_type = G_CATEGORY))
1690: and b.activity_metric_id = l_act_met_id (l_count)); */
1691: --batoleti bug# 5879514
1692: update ams_act_metrics_all
1693: set dirty_flag = G_IS_DIRTY
1694: where activity_metric_id in
1695: (select a.activity_metric_id
1696: from ams_act_metrics_all a, ams_metrics_all_b m,

Line 1696: from ams_act_metrics_all a, ams_metrics_all_b m,

1692: update ams_act_metrics_all
1693: set dirty_flag = G_IS_DIRTY
1694: where activity_metric_id in
1695: (select a.activity_metric_id
1696: from ams_act_metrics_all a, ams_metrics_all_b m,
1697: ams_metric_formulas f, ams_act_metrics_all b, ams_metrics_all_b c
1698: where a.metric_id = m.metric_id
1699: and m.metric_id = f.metric_id
1700: and b.metric_id = c.metric_id

Line 1697: ams_metric_formulas f, ams_act_metrics_all b, ams_metrics_all_b c

1693: set dirty_flag = G_IS_DIRTY
1694: where activity_metric_id in
1695: (select a.activity_metric_id
1696: from ams_act_metrics_all a, ams_metrics_all_b m,
1697: ams_metric_formulas f, ams_act_metrics_all b, ams_metrics_all_b c
1698: where a.metric_id = m.metric_id
1699: and m.metric_id = f.metric_id
1700: and b.metric_id = c.metric_id
1701: and a.arc_act_metric_used_by = b.arc_act_metric_used_by

Line 2224: -- the "ams_act_metrics_all" table.

2220: -- The following procedure collects all activity metrics with missing
2221: -- "rollup_to" field, while the corresponding metric template and
2222: -- business objects that have parents, checks their ancestors along
2223: -- the tree hierarchy, fills all the missing "rollup_to" fields of
2224: -- the "ams_act_metrics_all" table.
2225: --
2226: -- NOTES
2227: --
2228: -- HISTORY

Line 2281: FROM ams_metrics_all_b a, ams_act_metrics_all b

2277: CURSOR c_act_metrics_parents IS
2278: SELECT --/*+ first_rows */
2279: a.metric_parent_id, b.activity_metric_id, b.act_metric_used_by_id,
2280: b.arc_act_metric_used_by
2281: FROM ams_metrics_all_b a, ams_act_metrics_all b
2282: WHERE a.metric_parent_id IS NOT NULL
2283: AND b.rollup_to_metric IS NULL
2284: AND a.metric_id = b.metric_id;
2285: --AND a.arc_metric_used_for_object = b.arc_act_metric_used_by;

Line 2292: FROM ams_metrics_all_b a, ams_act_metrics_all b

2288: CURSOR c_act_metrics_parents_by_obj(p_object_type varchar2, p_object_id NUMBER) IS
2289: SELECT /*+ first_rows */
2290: a.metric_parent_id, b.activity_metric_id, b.act_metric_used_by_id,
2291: b.arc_act_metric_used_by
2292: FROM ams_metrics_all_b a, ams_act_metrics_all b
2293: WHERE a.metric_parent_id IS NOT NULL
2294: AND b.rollup_to_metric IS NULL
2295: AND a.metric_id = b.metric_id
2296: AND b.arc_act_metric_used_by = p_object_type

Line 2447: UPDATE ams_act_metrics_all

2443: ELSE
2444: l_last := l_first + G_BATCH_SIZE - 1;
2445: END IF;
2446: FORALL l_row_count IN l_first .. l_last
2447: UPDATE ams_act_metrics_all
2448: SET rollup_to_metric = l_act_metric_parents (l_row_count)
2449: WHERE activity_metric_id = l_act_metrics (l_row_count);
2450:
2451: -- 06/13/2001 huili added to set the dirty_flag of parent activity metrics

Line 2453: UPDATE ams_act_metrics_all

2449: WHERE activity_metric_id = l_act_metrics (l_row_count);
2450:
2451: -- 06/13/2001 huili added to set the dirty_flag of parent activity metrics
2452: FORALL l_row_count IN l_first .. l_last
2453: UPDATE ams_act_metrics_all
2454: SET dirty_flag = G_IS_DIRTY
2455: WHERE activity_metric_id = l_act_metric_parents (l_row_count)
2456: AND dirty_flag = G_NOT_DIRTY;
2457: -- Commit a batch of rollup ids.

Line 2531: -- be saved on the "ROLLUP_TO_METRIC" column of the "AMS_ACT_METRICS_ALL" table.

2527: -- PURPOSE
2528: -- The following procedure checks the existance of a rollup activity metric for the
2529: -- parent Marketing objects. If none eixists, one will be generated. The checking
2530: -- will recursively happen along the hierarchy. The id will
2531: -- be saved on the "ROLLUP_TO_METRIC" column of the "AMS_ACT_METRICS_ALL" table.
2532: --
2533: -- NOTES
2534: --
2535: -- HISTORY

Line 2640: FROM ams_act_metrics_all

2636: CURSOR c_verify_act_metric(l_metric_parent_id NUMBER,
2637: l_obj_id NUMBER,
2638: l_obj_code VARCHAR2) IS
2639: SELECT activity_metric_id
2640: FROM ams_act_metrics_all
2641: WHERE metric_id = l_metric_parent_id
2642: AND act_metric_used_by_id = l_obj_id
2643: AND arc_act_metric_used_by = l_obj_code;
2644:

Line 2960: l_new_func_value AMS_ACT_METRICS_ALL.FUNC_ACTUAL_VALUE%TYPE;

2956: )
2957: IS
2958: L_API_NAME VARCHAR2(30) := 'Run_Functions';
2959: L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2960: l_new_func_value AMS_ACT_METRICS_ALL.FUNC_ACTUAL_VALUE%TYPE;
2961: -- l_act_metric_rec Ams_Actmetric_Pvt.Act_Metric_Rec_Type;
2962:
2963: l_return_status VARCHAR2(1);
2964: l_msg_count NUMBER;

Line 2982: FROM ams_metrics_all_b a, ams_act_metrics_all b,

2978: last_calculated_date,
2979: metric_category,
2980: TRANSACTION_CURRENCY_CODE,
2981: functional_currency_code
2982: FROM ams_metrics_all_b a, ams_act_metrics_all b,
2983: ams_lookups lkup
2984: WHERE a.metric_id = b.metric_id
2985: -- BUG4924982: Performance, join to metric definition.
2986: AND a.arc_metric_used_for_object = lkup.lookup_code

Line 3000: FROM ams_metrics_all_b a, ams_act_metrics_all b

2996: last_calculated_date,
2997: metric_category,
2998: TRANSACTION_CURRENCY_CODE,
2999: functional_currency_code
3000: FROM ams_metrics_all_b a, ams_act_metrics_all b
3001: WHERE a.metric_id = b.metric_id
3002: AND b.arc_act_metric_used_by = l_object_type
3003: AND b.act_metric_used_by_id = l_object_id
3004: AND a.metric_calculation_type = G_FUNCTION

Line 3024: FROM ams_act_metrics_all a, ams_metrics_all_b b

3020:
3021: CURSOR c_has_procedure(l_function_name varchar2,
3022: l_obj_type varchar2, l_obj_id number) IS
3023: SELECT count(1)
3024: FROM ams_act_metrics_all a, ams_metrics_all_b b
3025: WHERE a.metric_id = b.metric_id
3026: AND b.function_name = l_function_name
3027: AND a.arc_act_metric_used_by = l_obj_type
3028: AND a.act_metric_used_by_id = l_obj_id

Line 3232: UPDATE ams_act_metrics_all

3228: ELSE
3229: l_last := l_first + G_BATCH_SIZE - 1;
3230: END IF;
3231: FORALL l_count IN l_first .. l_last
3232: UPDATE ams_act_metrics_all
3233: SET last_calculated_date = l_current_date,
3234: last_update_date = l_current_date,
3235: func_actual_value = l_new_func_actuals(l_count),
3236: -- FUNCTIONAL_CURRENCY_CODE = l_func_currencies(l_count),

Line 3242: UPDATE ams_act_metrics_all

3238: trans_actual_value = l_new_trans_actuals(l_count)
3239: WHERE activity_metric_id = l_new_act_metric_ids(l_count);
3240:
3241: FORALL l_count IN l_first .. l_last
3242: UPDATE ams_act_metrics_all
3243: SET dirty_flag = G_IS_DIRTY
3244: WHERE activity_metric_id IN
3245: (SELECT rollup_to_metric FROM ams_act_metrics_all
3246: WHERE activity_metric_id = l_new_act_metric_ids(l_count)

Line 3245: (SELECT rollup_to_metric FROM ams_act_metrics_all

3241: FORALL l_count IN l_first .. l_last
3242: UPDATE ams_act_metrics_all
3243: SET dirty_flag = G_IS_DIRTY
3244: WHERE activity_metric_id IN
3245: (SELECT rollup_to_metric FROM ams_act_metrics_all
3246: WHERE activity_metric_id = l_new_act_metric_ids(l_count)
3247: AND rollup_to_metric IS NOT NULL
3248: UNION ALL
3249: SELECT summarize_to_metric FROM ams_act_metrics_all

Line 3249: SELECT summarize_to_metric FROM ams_act_metrics_all

3245: (SELECT rollup_to_metric FROM ams_act_metrics_all
3246: WHERE activity_metric_id = l_new_act_metric_ids(l_count)
3247: AND rollup_to_metric IS NOT NULL
3248: UNION ALL
3249: SELECT summarize_to_metric FROM ams_act_metrics_all
3250: WHERE activity_metric_id = l_new_act_metric_ids(l_count)
3251: AND summarize_to_metric IS NOT NULL);
3252:
3253: l_first := l_last + 1;

Line 3415: FROM ams_act_metrics_all

3411: FROM ams_object_associations
3412: WHERE object_association_id = p_obj_association_id ;
3413: CURSOR c_amt_met IS
3414: SELECT func_actual_value
3415: FROM ams_act_metrics_all
3416: WHERE activity_metric_origin_id = p_obj_association_id ;
3417: l_obj_det_rec c_obj_det%ROWTYPE ;
3418: l_amount NUMBER ;
3419: l_apport_value NUMBER ;

Line 3502: FROM ams_act_metrics_all act,ams_metrics_all_b met

3498: l_arc_act_metric_used_by VARCHAR2,
3499: l_act_metric_used_by_id NUMBER) IS
3500: SELECT act.activity_metric_id activity_metric_id,
3501: met.metric_id metric_id
3502: FROM ams_act_metrics_all act,ams_metrics_all_b met
3503: WHERE met.metric_id = act.metric_id
3504: AND act.arc_act_metric_used_by = l_arc_act_metric_used_by
3505: AND act.act_metric_used_by_id = l_act_metric_used_by_id
3506: AND met.metric_category = l_metric_category ;

Line 3514: FROM ams_act_metrics_all

3510: l_act_metric_used_by_id NUMBER) IS
3511: SELECT NVL(func_actual_value,0) func_actual_value,
3512: NVL(func_forecasted_value,0) func_forecasted_value,
3513: NVL(func_committed_value,0) func_committed_value
3514: FROM ams_act_metrics_all
3515: WHERE metric_id = l_met_id
3516: AND arc_act_metric_used_by = l_arc_act_metric_used_by
3517: AND act_metric_used_by_id = l_act_metric_used_by_id ;
3518:

Line 3741: -- Added Forecasted_variable_value to ams_act_metrics_all.

3737: -- PURPOSE
3738: -- Calculates the variable metrics based on multiplier metrics.
3739: -- The indication to recalculate is when the multipliers last_update_date
3740: -- is greater than the variable metrics last_calculated_date.
3741: -- Added Forecasted_variable_value to ams_act_metrics_all.
3742: -- The forecasted value is calculated by using the actual multiplier value
3743: -- if non-null and non-zero, otherwise use the forecasted multiplier value.
3744: -- The trans_actual_value for the variable metric is fixed and the actual
3745: -- unit value (variable_value) is calculated by dividing trans_actual_value

Line 3791: FROM ams_act_metrics_all a, ams_metrics_all_b b, ams_act_metrics_all c

3787: a.dirty_flag,
3788: sum(c.trans_actual_value) actual_multiplier,
3789: sum(c.trans_forecasted_value) forecast_multiplier,
3790: max(c.last_update_date) last_update_date
3791: FROM ams_act_metrics_all a, ams_metrics_all_b b, ams_act_metrics_all c
3792: WHERE a.metric_id = b.metric_id
3793: AND b.accrual_type = G_VARIABLE
3794: AND a.arc_act_metric_used_by = c.arc_act_metric_used_by(+)
3795: AND a.act_metric_used_by_id = c.act_metric_used_by_id(+)

Line 3835: FROM ams_act_metrics_all a, ams_metrics_all_b b, ams_act_metrics_all c

3831: a.dirty_flag,
3832: sum(c.trans_actual_value) actual_multiplier,
3833: sum(c.trans_forecasted_value) forecast_multiplier,
3834: max(c.last_update_date) last_update_date
3835: FROM ams_act_metrics_all a, ams_metrics_all_b b, ams_act_metrics_all c
3836: WHERE a.metric_id = b.metric_id
3837: AND b.accrual_type = G_VARIABLE
3838: AND a.arc_act_metric_used_by = l_obj_type
3839: AND a.act_metric_used_by_id = l_obj_id

Line 4031: UPDATE ams_act_metrics_all

4027: l_last := l_first + G_BATCH_SIZE - 1;
4028: END IF;
4029: --FORALL l_index IN l_act_metric_ids.FIRST .. l_act_metric_ids.LAST
4030: FORALL l_index IN l_first .. l_last
4031: UPDATE ams_act_metrics_all
4032: SET last_calculated_date = l_current_date,
4033: last_update_date = l_current_date,
4034: object_version_number = object_version_number + 1,
4035: functional_currency_code = l_new_func_currencies(l_index),

Line 4050: UPDATE ams_act_metrics_all

4046:
4047: -- set the dirty flags for all summary and rollup parents.
4048: --FORALL l_index IN l_act_metric_ids.FIRST .. l_act_metric_ids.LAST
4049: FORALL l_index IN l_first .. l_last
4050: UPDATE ams_act_metrics_all
4051: SET dirty_flag = G_IS_DIRTY
4052: WHERE activity_metric_id IN
4053: (SELECT rollup_to_metric FROM ams_act_metrics_all
4054: WHERE activity_metric_id = l_activity_metric_ids(l_index)

Line 4053: (SELECT rollup_to_metric FROM ams_act_metrics_all

4049: FORALL l_index IN l_first .. l_last
4050: UPDATE ams_act_metrics_all
4051: SET dirty_flag = G_IS_DIRTY
4052: WHERE activity_metric_id IN
4053: (SELECT rollup_to_metric FROM ams_act_metrics_all
4054: WHERE activity_metric_id = l_activity_metric_ids(l_index)
4055: UNION ALL
4056: SELECT summarize_to_metric FROM ams_act_metrics_all
4057: WHERE activity_metric_id = l_activity_metric_ids(l_index));

Line 4056: SELECT summarize_to_metric FROM ams_act_metrics_all

4052: WHERE activity_metric_id IN
4053: (SELECT rollup_to_metric FROM ams_act_metrics_all
4054: WHERE activity_metric_id = l_activity_metric_ids(l_index)
4055: UNION ALL
4056: SELECT summarize_to_metric FROM ams_act_metrics_all
4057: WHERE activity_metric_id = l_activity_metric_ids(l_index));
4058: l_first := l_last + 1;
4059: IF p_commit = FND_API.G_TRUE THEN
4060: COMMIT;

Line 4808: FROM ams_act_metrics_all a, ams_act_metric_hst b

4804: a.HIERARCHY_TYPE,
4805: a.DEPEND_ACT_METRIC,
4806: b.FUNC_FORECASTED_DELTA,
4807: b.FUNC_ACTUAL_DELTA
4808: FROM ams_act_metrics_all a, ams_act_metric_hst b
4809: WHERE b.activity_metric_id = a.activity_metric_id
4810: AND trunc(a.last_update_date) = trunc(b.last_update_date)
4811: AND a.last_update_date > b.last_update_date
4812: AND b.last_update_date =

Line 4908: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b

4904:
4905: CURSOR c_get_deleted_today IS
4906: SELECT act_met_hst_id
4907: FROM ams_act_metric_hst a
4908: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
4909: WHERE a.activity_metric_id = b.activity_metric_id)
4910: AND last_update_date =
4911: (SELECT MAX(c.last_update_date)
4912: FROM ams_act_metric_hst c

Line 5087: FROM ams_act_metrics_all a, ams_act_metric_hst b

5083: a.HIERARCHY_TYPE,
5084: a.DEPEND_ACT_METRIC,
5085: NVL(a.FUNC_FORECASTED_VALUE,0) - NVL(b.FUNC_FORECASTED_VALUE,0),
5086: NVL(a.FUNC_ACTUAL_VALUE,0) - NVL(b.FUNC_ACTUAL_VALUE,0)
5087: FROM ams_act_metrics_all a, ams_act_metric_hst b
5088: WHERE a.activity_metric_id = b.activity_metric_id
5089: AND b.last_update_date =
5090: (SELECT MAX(last_update_date)
5091: FROM ams_act_metric_hst c

Line 5272: FROM ams_act_metrics_all a

5268: a.DEPEND_ACT_METRIC,
5269: -- BUG2214496: Initialize to original value.
5270: a.FUNC_FORECASTED_VALUE,
5271: a.FUNC_ACTUAL_VALUE
5272: FROM ams_act_metrics_all a
5273: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metric_hst b
5274: WHERE a.activity_metric_id = b.activity_metric_id);
5275: IF AMS_DEBUG_HIGH_ON THEN
5276: write_msg(L_API_NAME, 'INSERTED new metrics: '|| SQL%ROWCOUNT);

Line 5766: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b

5762: a.DEPEND_ACT_METRIC,
5763: -NVL(a.FUNC_FORECASTED_VALUE,0),
5764: -NVL(a.FUNC_ACTUAL_VALUE,0)
5765: FROM ams_act_metric_hst a
5766: WHERE NOT EXISTS (SELECT 'x' FROM ams_act_metrics_all b
5767: WHERE a.activity_metric_id = b.activity_metric_id)
5768: AND last_update_date =
5769: (SELECT MAX(c.last_update_date)
5770: FROM ams_act_metric_hst c

Line 5976: from ams_act_metrics_all a, ams_metrics_all_b b

5972: return act_metric_formula_type is
5973: select a.activity_metric_id, a.metric_id ,
5974: a.arc_act_metric_used_by, a.act_metric_used_by_id,
5975: a.last_calculated_date, b.display_type
5976: from ams_act_metrics_all a, ams_metrics_all_b b
5977: where a.metric_id = b.metric_id
5978: and b.metric_calculation_type = G_FORMULA
5979: --and a.dirty_flag = G_IS_DIRTY
5980: order by a.metric_id;

Line 5987: from ams_act_metrics_all a, ams_metrics_all_b b

5983: return act_metric_formula_type is
5984: select a.activity_metric_id, a.metric_id,
5985: a.arc_act_metric_used_by, a.act_metric_used_by_id,
5986: a.last_calculated_date, b.display_type
5987: from ams_act_metrics_all a, ams_metrics_all_b b
5988: where a.metric_id = b.metric_id
5989: and b.metric_calculation_type = G_FORMULA
5990: --and a.dirty_flag = G_IS_DIRTY
5991: and a.arc_act_metric_used_by = p_object_type

Line 6018: from ams_act_metrics_all a

6014: order by sequence;
6015:
6016: cursor c_get_values_by_metric(p_metric_id number, p_object_type VARCHAR2, p_object_id NUMBER) is
6017: select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
6018: from ams_act_metrics_all a
6019: where a.metric_id = p_metric_id
6020: and a.arc_act_metric_used_by = p_object_type
6021: and a.act_metric_used_by_id = p_object_id
6022: group by functional_currency_code;

Line 6026: from ams_act_metrics_all a, ams_metrics_all_b b

6022: group by functional_currency_code;
6023:
6024: cursor c_get_values_by_category(p_category_id number, p_object_type VARCHAR2, p_object_id NUMBER) is
6025: select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
6026: from ams_act_metrics_all a, ams_metrics_all_b b
6027: where a.metric_id = b.metric_id
6028: and b.metric_category = p_category_id
6029: and a.arc_act_metric_used_by = p_object_type
6030: and a.act_metric_used_by_id = p_object_id

Line 6035: from ams_act_metrics_all a, ams_metrics_all_b b

6031: group by functional_currency_code;
6032:
6033: cursor c_get_values_by_category_only(p_category_id number, p_object_type VARCHAR2, p_object_id NUMBER) is
6034: select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
6035: from ams_act_metrics_all a, ams_metrics_all_b b
6036: where a.metric_id = b.metric_id
6037: and b.metric_category = p_category_id
6038: and b.metric_sub_category is null
6039: and a.arc_act_metric_used_by = p_object_type

Line 6045: from ams_act_metrics_all a, ams_metrics_all_b b

6041: group by functional_currency_code;
6042:
6043: cursor c_get_values_by_sub_category(p_category_id number, p_sub_category_id number, p_object_type VARCHAR2, p_object_id NUMBER) is
6044: select sum(func_forecasted_value), sum(func_actual_value), functional_currency_code
6045: from ams_act_metrics_all a, ams_metrics_all_b b
6046: where a.metric_id = b.metric_id
6047: and b.metric_category = p_category_id
6048: and b.metric_sub_category = p_sub_category_id
6049: and a.arc_act_metric_used_by = p_object_type

Line 6330: UPDATE ams_act_metrics_all

6326: ELSE
6327: l_last := l_first + G_BATCH_SIZE - 1;
6328: END IF;
6329: FORALL l_index IN l_first .. l_last
6330: UPDATE ams_act_metrics_all
6331: SET last_calculated_date = p_current_date,
6332: last_update_date = p_current_date,
6333: object_version_number = object_version_number + 1,
6334: days_since_last_refresh = l_days_since_last_refreshs(l_index),

Line 6502: write_msg('populate_all','Deleting all entris from ams_act_metrics_all_denorm');

6498: x_msg_data := l_msg_data;
6499:
6500: --bms_output.put_line('deleting all entries');
6501: IF AMS_DEBUG_HIGH_ON THEN
6502: write_msg('populate_all','Deleting all entris from ams_act_metrics_all_denorm');
6503: END IF;
6504:
6505:
6506: delete

Line 6507: from ams_act_metrics_all_denorm;

6503: END IF;
6504:
6505:
6506: delete
6507: from ams_act_metrics_all_denorm;
6508:
6509: ----Ams_Utility_Pvt.Write_Conc_log('Inserting entries ams_act_metrics_all_denorm') ;
6510: l_metric_col_date := SYSDATE;
6511: --bms_output.put_line('inserting all entries with metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));

Line 6509: ----Ams_Utility_Pvt.Write_Conc_log('Inserting entries ams_act_metrics_all_denorm') ;

6505:
6506: delete
6507: from ams_act_metrics_all_denorm;
6508:
6509: ----Ams_Utility_Pvt.Write_Conc_log('Inserting entries ams_act_metrics_all_denorm') ;
6510: l_metric_col_date := SYSDATE;
6511: --bms_output.put_line('inserting all entries with metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6512: IF AMS_DEBUG_HIGH_ON THEN
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

6513: write_msg('populate_all','inserting all entries with metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6514: END IF;
6515:
6516:
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
6518: ,object_id,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate,dead_leads,dead_leads_ratio,opportunities
6519: ,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount,booked_revenue,invoiced_revenue
6520: ,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,invoiced_roi
6521: ,responses,contact_group_size,target_group_size

Line 6529: select sysdate,Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,Fnd_Global.Conc_Login_Id,AMS_ACT_METRICS_ALL_DENORM_S.NEXTVAL,

6525: ,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
6526: ,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
6527: invoiced_roi_forecast,contact_group_size_forecast,target_group_size_forecast,responses_forecast
6528: )
6529: select sysdate,Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,Fnd_Global.Conc_Login_Id,AMS_ACT_METRICS_ALL_DENORM_S.NEXTVAL,
6530: sysdate,object_type,object_id
6531: ,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate
6532: ,dead_leads,dead_leads_ratio,opportunities,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount
6533: ,booked_revenue,invoiced_revenue,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,

Line 6591: from ams_act_metrics_all h1, ams_metrics_all_b met

6587: sum(decode(met.denorm_code,'METRIC_21',h1.func_forecasted_value,0)) invoiced_roi_forecast,
6588: sum(decode(met.denorm_code,'METRIC_22',h1.func_forecasted_value,0)) responses_forecast,
6589: sum(decode(met.denorm_code,'METRIC_23',h1.func_forecasted_value,0)) contact_group_size_forecast,
6590: sum(decode(met.denorm_code,'METRIC_24',h1.func_forecasted_value,0)) target_group_size_forecast
6591: from ams_act_metrics_all h1, ams_metrics_all_b met
6592: where h1.metric_id = met.metric_id
6593: and met.denorm_code in
6594: (
6595: 'METRIC_01'

Line 6623: write_msg('populate_all','Done Inserting entries ams_act_metrics_all_denorm');

6619: group by h1.arc_act_metric_used_by ,h1.act_metric_used_by_id
6620: );
6621:
6622: IF AMS_DEBUG_HIGH_ON THEN
6623: write_msg('populate_all','Done Inserting entries ams_act_metrics_all_denorm');
6624: END IF;
6625:
6626: --bms_output.put_line('Done Inserting entries ');
6627:

Line 6650: from ams_act_metrics_all

6646: l_msg_data VARCHAR2(2000);
6647:
6648: CURSOR c_new_objects_csr(p_date DATE) IS
6649: select distinct concat(concat(arc_act_metric_used_by,'_'),TO_CHAR(act_metric_used_by_id))
6650: from ams_act_metrics_all
6651: where last_update_date > p_date;
6652:
6653: new_object_row VARCHAR2(100);
6654: l_obj_type VARCHAR2(30);

Line 6666: ----Ams_Utility_Pvt.Write_Conc_log('Inserting entries ams_act_metrics_all_denorm') ;

6662: BEGIN
6663:
6664: x_return_status := FND_API.G_RET_STS_SUCCESS;
6665:
6666: ----Ams_Utility_Pvt.Write_Conc_log('Inserting entries ams_act_metrics_all_denorm') ;
6667: l_metric_col_date := SYSDATE;
6668:
6669: IF AMS_DEBUG_HIGH_ON THEN
6670: write_msg('populate_incremental','max last run date : '||TO_CHAR(p_last_run_date,'DD-MON-YYYY HH:MI:SS'));

Line 6677: --first update those rows in ams_act_metrics_all_denorm whose values have been changed in

6673:
6674: --bms_output.put_line('last run date : '||TO_CHAR(p_last_run_date,'DD-MON-YYYY HH:MI:SS'));
6675: --bms_output.put_line('metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6676:
6677: --first update those rows in ams_act_metrics_all_denorm whose values have been changed in
6678: --ams_act_metrics_all since last metrics collection date
6679: update ams_act_metrics_all_denorm a
6680: set
6681: (

Line 6678: --ams_act_metrics_all since last metrics collection date

6674: --bms_output.put_line('last run date : '||TO_CHAR(p_last_run_date,'DD-MON-YYYY HH:MI:SS'));
6675: --bms_output.put_line('metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6676:
6677: --first update those rows in ams_act_metrics_all_denorm whose values have been changed in
6678: --ams_act_metrics_all since last metrics collection date
6679: update ams_act_metrics_all_denorm a
6680: set
6681: (
6682: a.last_updated_by

Line 6679: update ams_act_metrics_all_denorm a

6675: --bms_output.put_line('metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6676:
6677: --first update those rows in ams_act_metrics_all_denorm whose values have been changed in
6678: --ams_act_metrics_all since last metrics collection date
6679: update ams_act_metrics_all_denorm a
6680: set
6681: (
6682: a.last_updated_by
6683: ,a.last_update_date

Line 6748: from ams_act_metrics_all h1, ams_metrics_all_b met

6744: sum(decode(met.denorm_code,'METRIC_21',h1.func_forecasted_value,0)) invoiced_roi_forecast,
6745: sum(decode(met.denorm_code,'METRIC_22',h1.func_forecasted_value,0)) responses_forecast,
6746: sum(decode(met.denorm_code,'METRIC_23',h1.func_forecasted_value,0)) contact_group_size_forecast,
6747: sum(decode(met.denorm_code,'METRIC_24',h1.func_forecasted_value,0)) target_group_size_forecast
6748: from ams_act_metrics_all h1, ams_metrics_all_b met
6749: where h1.metric_id = met.metric_id
6750: and met.denorm_code in
6751: (
6752: 'METRIC_01'

Line 6780: and exists (select 1 from ams_act_metrics_all amet, ams_metrics_all_b met

6776: and h1.arc_act_metric_used_by(+) = a.object_type
6777: and h1.act_metric_used_by_id(+) = a.object_id
6778: )
6779: where a.last_update_date > l_calc_since
6780: and exists (select 1 from ams_act_metrics_all amet, ams_metrics_all_b met
6781: where amet.last_update_date > a.last_update_date
6782: and amet.metric_id = met.metric_id
6783: and met.denorm_code is not null
6784: and amet.arc_act_metric_used_by = a.object_type

Line 6791: insert into ams_act_metrics_all_denorm (creation_date,created_by,last_update_date,last_updated_by,last_update_login

6787: )
6788: ;
6789:
6790:
6791: insert into ams_act_metrics_all_denorm (creation_date,created_by,last_update_date,last_updated_by,last_update_login
6792: ,act_metrics_denorm_id,metric_collection_date,object_type
6793: ,object_id,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate,dead_leads,dead_leads_ratio,opportunities
6794: ,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount,booked_revenue,invoiced_revenue
6795: ,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,invoiced_roi

Line 6804: select sysdate,Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,Fnd_Global.Conc_Login_Id,AMS_ACT_METRICS_ALL_DENORM_S.NEXTVAL,

6800: ,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
6801: ,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
6802: invoiced_roi_forecast,contact_group_size_forecast,target_group_size_forecast,responses_forecast
6803: )
6804: select sysdate,Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,Fnd_Global.Conc_Login_Id,AMS_ACT_METRICS_ALL_DENORM_S.NEXTVAL,
6805: sysdate,object_type,object_id
6806: ,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate
6807: ,dead_leads,dead_leads_ratio,opportunities,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount
6808: ,booked_revenue,invoiced_revenue,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,

Line 6866: from ams_act_metrics_all h1, ams_metrics_all_b met

6862: sum(decode(met.denorm_code,'METRIC_21',h1.func_forecasted_value,0)) invoiced_roi_forecast,
6863: sum(decode(met.denorm_code,'METRIC_22',h1.func_forecasted_value,0)) responses_forecast,
6864: sum(decode(met.denorm_code,'METRIC_23',h1.func_forecasted_value,0)) contact_group_size_forecast,
6865: sum(decode(met.denorm_code,'METRIC_24',h1.func_forecasted_value,0)) target_group_size_forecast
6866: from ams_act_metrics_all h1, ams_metrics_all_b met
6867: where h1.metric_id = met.metric_id
6868: and met.denorm_code in
6869: (
6870: 'METRIC_01'

Line 6897: from ams_act_metrics_all_denorm

6893: ,'METRIC_24')
6894: and (h1.arc_act_metric_used_by,h1.act_metric_used_by_id) not in
6895: (
6896: select distinct object_type, object_id
6897: from ams_act_metrics_all_denorm
6898: )
6899: group by h1.arc_act_metric_used_by ,h1.act_metric_used_by_id
6900: );
6901:

Line 6937: from ams_act_metrics_all_denorm;

6933: l_last_run_date DATE := NULL;
6934:
6935: CURSOR c_last_run_date_csr is
6936: select max(metric_collection_date)
6937: from ams_act_metrics_all_denorm;
6938:
6939: BEGIN
6940: FND_MSG_PUB.initialize;
6941: