[Home] [Help]
PACKAGE BODY: APPS.AMS_METRICCUSTOM_PVT
Source
1 PACKAGE BODY ams_metriccustom_pvt AS
2 /*$Header: amsvrcsb.pls 115.7 2002/11/14 22:05:59 jieli noship $*/
3 --
4 -- NAME
5 -- AMS_METRICCUSTOM_PVT
6 -- PURPOSE
7 -- To get the metric rollup values for events and campaigns
8 -- FUNCTION
9 -- get_rollup_value
10 -- HISTORY
11 -- Date Owner Changes
12 -- 09/29/2000 MUSMAN CREATED
13 ---------------------------------------------------------------------
14
15
16 ---------------------------------------------------------------------
17 -- FUNCTION
18 -- get_rollup_value
19 --
20 -- PURPOSE
21 -- Get the rollup values for the events and campaigns
22 --
23 -- PARAMETERS
24 -- p_act_met_id: the new record to be inserted
25 -- RETURNS
26 -- l_actual_tot_value : sum of the roolup metrics value
27 --
28 -- NOTES
29 -- 1. Checks whether metrics is used by campaigns.
30 -- 2. Checks for the child campaign if exists and select the functional actual value, functional
31 -- forecasted value used by it (passing the category id and sub_category_id) and sum it up.
32 -- 3. Checks whether the metrics is used by events.
33 -- 4. Find out the sub events associated to it and get the functional actual value,functional
34 -- forcasted value and it adds it up.
35 -- 5. Calls update API and passes the functional actual value .
36 -- 6. Returns the total sum
37 ---------------------------------------------------------------------
38 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
39 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
40 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
41
42 FUNCTION get_rollup_value(p_act_met_id NUMBER)
43 RETURN NUMBER is
44
45 CURSOR c_act_metric_row(p_act_metric_id number) IS
46 SELECT a.arc_act_metric_used_by,
47 a.act_metric_used_by_id,
48 a.metric_id,
49 b.metric_category,
50 b.metric_sub_category,
51 a.object_version_number,
52 a.transaction_currency_code
53 FROM ams_act_metrics_all a, ams_metrics_all_b b
54 WHERE a.metric_id = b.metric_id
55 and activity_metric_id = p_act_met_id;
56
57 l_act_metric_row c_act_metric_row%ROWTYPE;
58 l_child_camp_id NUMBER;
59 l_child_event_id NUMBER;
60 l_actual_ret_value NUMBER := 0;
61 l_actual_tot_value NUMBER := 0;
62 l_forecas_ret_value NUMBER := 0;
63 l_forecas_tot_value NUMBER := 0;
64 l_metric_rec AMS_actmetric_pvt.act_metric_rec_type;
65 l_return_status VARCHAR2(1);
66 l_msg_count NUMBER;
67 l_msg_data VARCHAR2(2000);
68 l_object_version_number NUMBER;
69
70
71 CURSOR c_camp(p_current_campaign_id NUMBER) IS
72 SELECT campaign_id
73 FROM ams_campaigns_all_b
74 WHERE parent_campaign_id = p_current_campaign_id ;
75
76 CURSOR c_even(p_current_event_id NUMBER) IS
77 SELECT event_offer_id
78 FROM ams_event_offers_all_b
79 WHERE event_header_id = p_current_event_id;
80
81 CURSOR c_obj_assocs(p_obj_id NUMBER,
82 p_obj_cd VARCHAR2) IS
83 SELECT using_object_type, using_object_id
84 FROM ams_object_associations
85 WHERE master_object_type = p_obj_cd
86 AND master_object_id = p_obj_id
87 AND usage_type= 'CREATED';
88
89 l_obj_assocs c_obj_assocs%ROWTYPE;
90
91 CURSOR c_get_value (
92 p_arc VARCHAR2,
93 p_id NUMBER,
94 p_category_id NUMBER,
95 p_sub_category_id NUMBER) IS
96 SELECT max(func_actual_value),
97 max(func_forecasted_value)
98 FROM ams_act_metrics_all a
99 WHERE arc_act_metric_used_by = p_arc
100 AND act_metric_used_by_id = p_id
101 AND EXISTS (select 'x' from ams_metrics_all_b b
102 where b.metric_id = a.metric_id
103 AND metric_category = p_category_id
104 AND metric_sub_category = p_sub_category_id);
105
106 BEGIN
107
108 OPEN c_act_metric_row(p_act_met_id);
109 FETCH c_act_metric_row INTO l_act_metric_row;
110 IF c_act_metric_row%FOUND THEN
111 IF l_act_metric_row.arc_act_metric_used_by='CAMP' THEN
112
113 OPEN c_camp(l_act_metric_row.act_metric_used_by_id);
114 LOOP
115 FETCH c_camp INTO l_child_camp_id;
116 EXIT WHEN c_camp%NOTFOUND;
117 OPEN c_get_value(
118 l_act_metric_row.arc_act_metric_used_by,
119 l_child_camp_id,
120 l_act_metric_row.metric_category,
121 l_act_metric_row.metric_sub_category);
122 FETCH c_get_value INTO l_actual_ret_value,l_forecas_ret_value;
123 IF c_get_value%FOUND THEN
124 l_actual_tot_value := l_actual_tot_value + l_actual_ret_value;
125 l_forecas_tot_value := l_forecas_tot_value + l_forecas_ret_value;
126 END IF;
127 l_actual_ret_value := 0;
128 l_forecas_ret_value := 0;
129 CLOSE c_get_value;
130 END LOOP;
131 CLOSE c_camp;
132 ELSIF l_act_metric_row.arc_act_metric_used_by ='EVEH' THEN
133
134 OPEN c_even(l_act_metric_row.act_metric_used_by_id);
135 LOOP
136 FETCH c_even INTO l_child_event_id;
137 EXIT WHEN c_even%NOTFOUND;
138 OPEN c_get_value(
139 'EVEO',
140 l_child_event_id,
141 l_act_metric_row.metric_category,
142 l_act_metric_row.metric_sub_category);
143 FETCH c_get_value INTO l_actual_ret_value, l_forecas_ret_value;
144 IF c_get_value%FOUND THEN
145 l_actual_tot_value := l_actual_tot_value + l_actual_ret_value;
146 l_forecas_tot_value := l_forecas_tot_value + l_forecas_ret_value;
147 END IF;
148 l_actual_ret_value := 0;
149 l_forecas_ret_value := 0;
150 CLOSE c_get_value;
151 END LOOP;
152 CLOSE c_even;
153
154 END IF;
155
156 /*Look for associated objects with CREATED_FOR*/
157 OPEN c_obj_assocs(l_act_metric_row.act_metric_used_by_id,
158 l_act_metric_row.arc_act_metric_used_by);
159 LOOP
160 FETCH c_obj_assocs INTO l_obj_assocs;
161 EXIT WHEN c_obj_assocs%NOTFOUND;
162 OPEN c_get_value(
163 l_obj_assocs.using_object_type,
164 l_obj_assocs.using_object_id,
165 l_act_metric_row.metric_category,
166 l_act_metric_row.metric_sub_category);
167 FETCH c_get_value INTO l_actual_ret_value, l_forecas_ret_value;
168 IF c_get_value%FOUND THEN
169 l_actual_tot_value := l_actual_tot_value + l_actual_ret_value;
170 l_forecas_tot_value := l_forecas_tot_value + l_forecas_ret_value;
171 END IF;
172 l_actual_ret_value := 0;
173 l_forecas_ret_value := 0;
174 CLOSE c_get_value;
175 END LOOP;
176 CLOSE c_obj_assocs;
177
178 l_object_version_number := l_act_metric_row.object_version_number;
179 END IF;
180 CLOSE c_act_metric_row;
181
182 AMS_ACTMETRIC_PVT.Init_ActMetric_Rec(x_act_metric_rec => l_metric_rec );
183 l_metric_rec.act_metric_used_by_id := l_act_metric_row.act_metric_used_by_id;
184 l_metric_rec.arc_act_metric_used_by := l_act_metric_row.arc_act_metric_used_by;
185 l_metric_rec.metric_id := l_act_metric_row.metric_id;
186 l_metric_rec.object_version_number := l_object_version_number;
187 l_metric_rec.activity_metric_id := p_act_met_id;
188 l_metric_rec.func_forecasted_value := l_forecas_tot_value;
189 l_metric_rec.transaction_currency_code := l_act_metric_row.transaction_currency_code;
190 l_metric_rec.sensitive_data_flag:='N';
191 l_metric_rec.application_id:=530;
192
193 Ams_ActMetric_pvt.Update_ActMetric (
194 p_api_version => '1.0',
195 p_commit=>FND_API.G_TRUE,
196 x_return_status => l_return_status,
197 x_msg_count => l_msg_count,
198 x_msg_data => l_msg_data,
199 p_act_metric_rec => l_metric_rec);
200
201 RETURN l_actual_tot_value;
202
203 END get_rollup_value;
204
205 -----------------------------------------------------------------------
206
207 END ams_metriccustom_pvt;