[Home] [Help]
117: -- PURPOSE
118: -- procedure below checks for the existence of the
119: -- rollup metrics at the parent Marketing entity level
120: -- if this is not existing a new row is created in the
121: -- ams_act_metrics_all and also calls refresh for the
122: -- parent entity..
123: --
124: -- NOTES
125: --
202: CURSOR c_verify_act_metric(l_metric_parent_id NUMBER,
203: l_obj_id NUMBER,
204: l_obj_code VARCHAR2) IS
205: SELECT activity_metric_id
206: FROM ams_act_metrics_all
207: WHERE metric_id = l_metric_parent_id
208: AND act_metric_used_by_id = l_obj_id
209: AND arc_act_metric_used_by = l_obj_code;
210:
332: END IF; -- IF c_verify_act_metric%NOTFOUND THEN
333: CLOSE c_verify_act_metric;
334:
335: -- DMVINCEN 04/30/2001: update the rollup to field.
336: UPDATE ams_act_metrics_all
337: SET rollup_to_metric = l_act_met_id
338: WHERE activity_metric_id = p_activity_metric_id;
339:
340: END IF; -- parent_obj not null
357: -- dependent objects have any top level metrics, this procedure creates or
358: -- refreshes the association metric for the master object.
359: --
360: -- NOTES
361: -- Tables used: ams_object_associations,ams_metric_accruals,ams_act_metrics_all
362: -- This algorithm is driven on ams_metric_accruals table.
363: -- HISTORY
364: -- 10/04/2000 sveerave Created.
365: -- 08/16/2001 dmvincen Not all cursors were being closed.
387: actmet.func_actual_value,
388: actmet.func_forecasted_value,
389: actmet.functional_currency_code,
390: objassoc.object_association_id
391: FROM ams_act_metrics_all actmet,
392: ams_metrics_all_b met,
393: ams_object_associations objassoc
394: WHERE actmet.metric_id = met.metric_id
395: AND objassoc.master_object_type = l_master_obj_type
405: CURSOR c_get_master_accrual_actmet(l_master_object_type VARCHAR2,
406: l_master_object_id NUMBER,
407: l_metric_id NUMBER) IS
408: SELECT activity_metric_id
409: FROM ams_act_metrics_all
410: WHERE arc_act_metric_used_by = l_master_object_type
411: AND act_metric_used_by_id = l_master_object_id
412: AND metric_id = l_metric_id;
413: l_master_found VARCHAR2(1) := Fnd_Api.G_FALSE;
561: -- dependent objects have any top level metrics, this procedure creates
562: -- or refreshes the association metric for the master object.
563: --
564: -- NOTES
565: -- Tables used: ams_object_associations,ams_metric_accruals,ams_metrics_all_b,ams_act_metrics_all
566: -- This procedure is called in Refresh_metrics for each distinct combination
567: -- of parent_object_id, parent_object_type
568: -- This method only creates act metric accruals and calls
569: -- refresh_assoc_metrics for creation/update of activity metrics.
594: SELECT actmet.activity_metric_id child_actmet_id,
595: met.metric_category child_metric_type_id ,
596: actmet.metric_id child_metric_id,
597: met.value_type child_met_value_type
598: FROM ams_act_metrics_all actmet, ams_metrics_all_b met
599: WHERE actmet.arc_act_metric_used_by = l_child_object_type
600: AND actmet.act_metric_used_by_id = l_child_object_id
601: AND met.summary_metric_id IS NULL
602: AND actmet.metric_id = met.metric_id;
608: l_master_object_id NUMBER,
609: l_metric_type_id NUMBER) IS
610: SELECT activity_metric_id parent_summary_actmet_id,
611: met.metric_id parent_summary_met_id
612: FROM ams_act_metrics_all actmet, ams_metrics_all_b met
613: WHERE actmet.metric_id = met.metric_id
614: AND arc_act_metric_used_by = l_master_object_type
615: AND act_metric_used_by_id = l_master_object_id
616: AND metric_category = l_metric_type_id
827:
828: CURSOR c_cat_metrics IS
829: SELECT act.activity_metric_id activity_metric_id,
830: met.metric_id metric_id
831: FROM ams_act_metrics_all act,ams_metrics_vl met
832: WHERE met.metric_id = act.metric_id
833: AND act.arc_act_metric_used_by = p_arc_act_metric_used_by
834: AND act.act_metric_used_by_id = p_act_metric_used_by_id
835: AND met.metric_category = p_metric_category ;
836: CURSOR c_amount(l_met_id IN NUMBER) IS
837: SELECT NVL(func_actual_value,0) func_actual_value,
838: NVL(func_forecasted_value,0) func_forecasted_value,
839: NVL(func_committed_value,0) func_committed_value
840: FROM ams_act_metrics_all
841: WHERE metric_id = l_met_id
842: AND arc_act_metric_used_by = p_arc_act_metric_used_by
843: AND act_metric_used_by_id = p_act_metric_used_by_id ;
844: l_cat_met_rec c_cat_metrics%ROWTYPE ;
921: FROM ams_object_associations
922: WHERE object_association_id = p_obj_association_id ;
923: CURSOR c_amt_met IS
924: SELECT func_actual_value
925: FROM ams_act_metrics_all
926: WHERE activity_metric_origin_id = p_obj_association_id ;
927: l_obj_det_rec c_obj_det%ROWTYPE ;
928: l_amount NUMBER ;
929: l_apport_value NUMBER ;
1046: transaction_currency_code,
1047: NVL(func_actual_value,0) actual,
1048: NVL(func_forecasted_value, 0) forecasted,
1049: functional_currency_code
1050: FROM ams_act_metrics_all
1051: WHERE activity_metric_id = l_activity_metric_id;
1052: l_trans_actual_value NUMBER;
1053: l_trans_forecasted_value NUMBER;
1054: l_trans_currency_code VARCHAR2(15);
1136: function_name,
1137: func_actual_value,
1138: last_calculated_date,
1139: functional_currency_code
1140: FROM ams_act_metrics_all actmet, ams_metrics_all_b met
1141: WHERE actmet.metric_id = met.metric_id
1142: AND arc_act_metric_used_by IN
1143: ('RCAM', 'CAMP', 'CSCH', 'DELV', 'EVEH', 'EVEO', 'EONE')
1144: --BUG2845365: Remove dialogue components.
1328: functional_currency_code,
1329: NVL(trans_actual_value, 0) trans_actual_value,
1330: NVL(trans_forecasted_value, 0) trans_forecasted_value,
1331: transaction_currency_code
1332: FROM ams_act_metrics_all
1333: WHERE rollup_to_metric = l_activity_metric_id
1334: ORDER BY arc_act_metric_used_by, act_metric_used_by_id;
1335: l_met_child_rec c_met_child_rollup%ROWTYPE;
1336:
1530: SELECT activity_metric_id,
1531: arc_act_metric_used_by,
1532: act_metric_used_by_id,
1533: metric_uom_code
1534: FROM ams_act_metrics_all
1535: WHERE summarize_to_metric = l_activity_metric_id;
1536: l_met_child_sum_rec c_met_child_sum%ROWTYPE;
1537:
1538: -- Variables to store calculated Values
1781: transaction_currency_code,
1782: functional_currency_code,
1783: NVL(trans_actual_value,0) trans_actual_value,
1784: NVL(trans_forecasted_value,0) trans_forecasted_value
1785: FROM ams_act_metrics_all
1786: WHERE activity_metric_id = l_activity_metric_id
1787: for update of trans_actual_value, trans_forecasted_value,
1788: func_actual_value, func_forecasted_value,
1789: functional_currency_code, computed_using_function_value,
1805:
1806: -- huili on 06/22/2001 changed the calculation method for variable metrics
1807: CURSOR c_var_met_info (l_act_metric_id NUMBER) IS
1808: SELECT func_actual_value, func_forecasted_value
1809: FROM ams_act_metrics_all
1810: WHERE activity_metric_id = l_act_metric_id;
1811:
1812: l_return_status VARCHAR2(1);
1813:
1856: -------------------------------------------------------------
1857: -- procedure below checks for the existence of the
1858: -- rollup metrics at the parent Marketing entity level
1859: -- if this is not existing a new row is created in the
1860: -- ams_act_metrics_all and also calls refresh for the
1861: -- parent entity.
1862: -------------------------------------------------------------
1863:
1864: create_refresh_parent_level(
2734: -- Create_Apport_Metric
2735: --
2736: -- PURPOSE
2737: -- This Procedure is called when a new Object association is created.
2738: -- This will create Activity Metric in AMS_ACT_METRICS_ALL with the details
2739: -- of the association.
2740: --
2741: -- NOTES
2742: --
2763: FROM ams_object_associations
2764: WHERE object_association_id = p_obj_association_id ;
2765: CURSOR c_act_met IS
2766: SELECT COUNT(1)
2767: FROM ams_act_metrics_all
2768: WHERE activity_metric_origin_id = p_obj_association_id ;
2769: l_obj_det_rec c_obj_det%ROWTYPE ;
2770: -- Initialize Activity Metric Record type for Insertion/Updation of Act. Metric
2771: l_act_metrics_rec Ams_Actmetric_Pvt.act_metric_rec_type ;
2990: SELECT metric_id,
2991: arc_act_metric_used_by,
2992: act_metric_used_by_id,
2993: metric_uom_code
2994: FROM ams_act_metrics_all
2995: WHERE activity_metric_id = l_act_metric_id;
2996: l_act_metric_rec c_act_metric%ROWTYPE;
2997: --
2998: -- Retrieve all associated activities for the given
3002: CURSOR c_all_metrics(l_arc_act_metric_used_by VARCHAR2,
3003: l_act_metric_used_by_id NUMBER) IS
3004: SELECT activity_metric_id,actmet.metric_id,actmet.dirty_flag,
3005: met.metric_calculation_type
3006: FROM ams_act_metrics_all actmet, ams_metrics_all_b met
3007: WHERE actmet.metric_id = met.metric_id
3008: AND actmet.arc_act_metric_used_by = l_arc_act_metric_used_by
3009: AND actmet.act_metric_used_by_id = l_act_metric_used_by_id
3010: AND actmet.summarize_to_metric IS NULL;
3229: -- SELECT metric_id,
3230: -- arc_act_metric_used_by,
3231: -- act_metric_used_by_id,
3232: -- metric_uom_code
3233: -- FROM ams_act_metrics_all
3234: -- WHERE activity_metric_id = l_act_metric_id;
3235: -- l_act_metric_rec c_act_metric%ROWTYPE;
3236: --
3237: -- Retrieve all associated activities for the given
3242: CURSOR c_all_metrics(l_arc_act_metric_used_by VARCHAR2,
3243: l_act_metric_used_by_id NUMBER) IS
3244: SELECT activity_metric_id,actmet.metric_id,actmet.dirty_flag,
3245: met.metric_calculation_type,actmet.metric_uom_code
3246: FROM ams_act_metrics_all actmet, ams_metrics_all_b met
3247: WHERE actmet.metric_id = met.metric_id
3248: AND actmet.arc_act_metric_used_by = l_arc_act_metric_used_by
3249: AND actmet.act_metric_used_by_id = l_act_metric_used_by_id
3250: AND actmet.summarize_to_metric IS NULL;
3508: IS
3509: SELECT DISTINCT function_name
3510: FROM ams_metrics_all_b
3511: WHERE metric_id IN (SELECT metric_id
3512: FROM ams_act_metrics_all
3513: WHERE arc_act_metric_used_by = p_arc_act_metric_used_by
3514: AND act_metric_used_by_id = p_act_metric_used_by_id)
3515: AND function_type = 'N';
3516: BEGIN