1 PACKAGE BODY cn_mark_events_pub AS
2 /* $Header: cnpmkevb.pls 120.0 2006/08/25 00:19:15 ymao noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_MARK_EVENTS_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpmkevb.pls';
6
7 cursor check_period_id(p_period_id number, p_org_id number) is
8 select 1 from cn_period_statuses_all
9 where org_id = p_org_id
10 and period_id = p_period_id;
11
12 cursor check_salesrep_id(p_salesrep_id number, p_period_id number, p_org_id number) is
13 select 1 from cn_srp_intel_periods_all
14 where salesrep_id = p_salesrep_id
15 and period_id = p_period_id
16 and org_id = p_org_id;
17
18 cursor check_quota_id(p_quota_id number, p_org_id number) is
19 select 1 from cn_quotas_all
20 where org_id = p_org_id
21 and quota_id = p_quota_id;
22
23 cursor check_date_in_period(p_period_id number, p_date date, p_org_id number) is
24 select 1 from cn_period_statuses_all
25 where period_id = p_period_id
26 and org_id = p_org_id
27 and p_date between start_date and end_date;
28
29 -- Start of Comments
30 -- API name : Mark_Event_Calc
31 -- Type : Public.
32 -- Pre-reqs : None.
33 -- Usage : Used to create notification log records to re-compute commissions incrementally for the specified
34 -- salesrep within the given parameters
35 -- Desc : Procedure to create notification log records for the specified salesrep in the given time period
36 -- and optionally for the given plan element
37 -- Parameters :
38 -- IN p_api_version IN NUMBER Required
39 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
40 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
41 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
42 -- OUT x_return_status OUT VARCHAR2(1)
43 -- x_msg_count OUT NUMBER
44 -- x_msg_data OUT VARCHAR2(2000)
45 -- IN p_salesrep_id IN NUMBER
46 -- p_period_id IN NUMBER
47 -- p_start_date IN DATE Optional Default = NULL
48 -- p_end_date IN DATE Optional Default = NULL
49 -- p_quota_id IN NUMBER Optional Default = NULL
50 -- p_org_id IN NUMBER
51 -- Version : Current version 1.0
52 -- Initial version 1.0
53 --
54 -- Notes :
55 -- p_salesrep_id should be a valid salesrep identified in the operating unit specified by p_org_id.
56 -- p_period_id should specify the period for which calculation needs to be rerun
57 -- p_start_date should be within the period specified by p_period_id. It has a default value of null,
58 -- which is treated as the beginning of the specified period
59 -- p_end_date should be within the period specified by p_period_id. It has a default value of null,
60 -- which is treated as the end of the specified period
61 -- p_quota_id is the identifier of the plan element that needs to be recalculated. If it is null, all
62 -- plan elements of the specified salesrep will be calculated
63 -- p_org_id is the identifier of the operating unit
64 -- End of comments
65
66 PROCEDURE Mark_Event_Calc
67 (p_api_version IN NUMBER,
68 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
69 p_commit IN VARCHAR2 := FND_API.G_FALSE,
70 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
71 x_return_status OUT NOCOPY VARCHAR2,
72 x_msg_count OUT NOCOPY NUMBER,
73 x_msg_data OUT NOCOPY VARCHAR2,
74 p_salesrep_id IN NUMBER,
75 p_period_id IN NUMBER,
76 p_start_date IN DATE := NULL,
77 p_end_date IN DATE := NULL,
78 p_quota_id IN NUMBER := NULL,
79 p_org_id IN NUMBER)
80 IS
81 l_api_name CONSTANT VARCHAR2(30) := 'Mark_Event_Calc';
82 l_api_version CONSTANT NUMBER := 1.0;
83 l_status VARCHAR2(30);
84 l_org_id NUMBER;
85 l_dummy NUMBER := 0;
86 BEGIN
87 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
88 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
89 'cn.plsql.cn_mark_events_pub.mark_event_calc.begin',
90 'Beginning of mark_event_calc for resource ('||p_salesrep_id||') ...');
91 end if;
92
93 -- Standard Start of API savepoint
94 SAVEPOINT mark_event_calc;
95 -- Standard call to check for call compatibility.
96 IF NOT FND_API.compatible_api_call
97 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
98 THEN
99 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
100 END IF;
101 -- Initialize message list if p_init_msg_list is set to TRUE.
102 IF FND_API.to_Boolean( p_init_msg_list ) THEN
103 FND_MSG_PUB.initialize;
104 END IF;
105
106 -- Initialize API return status to success
107 x_return_status := FND_API.G_RET_STS_SUCCESS;
108
109 -- API body starts here
110 l_org_id := p_org_id;
111 mo_global.validate_orgid_pub_api(org_id => l_org_id,
112 status => l_status);
113
114 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
115 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
116 'cn.plsql.cn_mark_events_pub.mark_event_calc.org_validate',
117 'Validated org_id = ' || l_org_id || ' status = '||l_status);
118 end if;
119
120 IF (p_period_id IS NULL) THEN
121 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
122 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
123 'cn.plsql.cn_mark_events_pub.mark_event_calc.error',
124 'p_period_id is null');
125 end if;
126 x_return_status := fnd_api.g_ret_sts_error;
127 ELSE
128 open check_period_id(p_period_id, l_org_id);
129 fetch check_period_id into l_dummy;
130 close check_period_id;
131
132 if (l_dummy <> 1) then
133 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
134 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
135 'cn.plsql.cn_mark_events_pub.mark_event_calc.error',
136 'p_period_id is not valid');
137 end if;
138 x_return_status := fnd_api.g_ret_sts_error;
139 end if;
140 END IF;
141
142 IF (p_salesrep_id IS NULL) THEN
143 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
144 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
145 'cn.plsql.cn_mark_events_pub.mark_event_calc.error',
146 'p_salesrep_id is null');
147 end if;
148 x_return_status := fnd_api.g_ret_sts_error;
149 ELSIF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
150 l_dummy := 0;
151 open check_salesrep_id(p_salesrep_id, p_period_id, l_org_id);
152 fetch check_salesrep_id into l_dummy;
153 close check_salesrep_id;
154
155 if (l_dummy <> 1) then
156 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
157 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
158 'cn.plsql.cn_mark_events_pub.mark_event_calc.error',
159 'p_salesrep_id does not have valid setup in the given operating unit');
160 end if;
161 x_return_status := fnd_api.g_ret_sts_error;
162 end if;
163 END IF;
164
165 IF (p_quota_id IS NOT NULL) THEN
166 l_dummy := 0;
167 open check_quota_id(p_quota_id, p_org_id);
168 fetch check_quota_id into l_dummy;
169 close check_quota_id;
170
171 if (l_dummy <> 1) then
172 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
173 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
174 'cn.plsql.cn_mark_events_pub.mark_event_calc.error',
175 'p_quota_id is not valid in the given operating unit');
176 end if;
177 x_return_status := fnd_api.g_ret_sts_error;
178 end if;
179 END IF;
180
181 if (p_start_date is not null) then
182 l_dummy := 0;
183 open check_date_in_period(p_period_id, p_start_date, p_org_id);
184 fetch check_date_in_period into l_dummy;
185 close check_date_in_period;
186
187 if (l_dummy <> 1) then
188 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
189 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
190 'cn.plsql.cn_mark_events_pub.mark_event_calc.error',
191 'p_start_date is not within the given period');
192 end if;
193 x_return_status := fnd_api.g_ret_sts_error;
194 end if;
195 end if;
196
197 if (p_end_date is not null) then
198 l_dummy := 0;
199 open check_date_in_period(p_period_id, p_end_date, p_org_id);
200 fetch check_date_in_period into l_dummy;
201 close check_date_in_period;
202
203 if (l_dummy <> 1) then
204 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
205 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
206 'cn.plsql.cn_mark_events_pub.mark_event_calc.error',
207 'p_end_date is not within the given period');
208 end if;
209 x_return_status := fnd_api.g_ret_sts_error;
210 end if;
211 end if;
212
213 if (p_start_date is not null and p_end_date is not null and p_start_date > p_end_date) then
214 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
215 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
216 'cn.plsql.cn_mark_events_pub.mark_event_calc.error',
217 'p_start_date is greater than p_end_date');
218 end if;
219 x_return_status := fnd_api.g_ret_sts_error;
220 end if;
221
222 if (x_return_status <> FND_API.g_ret_sts_success) then
223 raise FND_API.G_EXC_ERROR;
224 end if;
225
226 -- if passing all validations, call mark_notify
227 cn_mark_events_pkg.mark_notify
228 (p_salesrep_id => p_salesrep_id,
229 p_period_id => p_period_id,
230 p_start_date => p_start_date,
231 p_end_date => p_end_date,
232 p_quota_id => p_quota_id,
233 p_revert_to_state => 'CALC',
234 p_event_log_id => null,
235 p_org_id => p_org_id);
236
237 -- End of API body.
238
239 -- Standard check of p_commit.
240 IF FND_API.To_Boolean( p_commit ) THEN
241 COMMIT WORK;
242 END IF;
243 -- Standard call to get message count and if count is 1, get message info.
244 FND_MSG_PUB.Count_And_Get
245 (
246 p_count => x_msg_count ,
247 p_data => x_msg_data ,
248 p_encoded => FND_API.G_FALSE
249 );
250
251 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
252 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
253 'cn.plsql.cn_mark_events_pub.mark_event_calc.end',
254 'End of mark_event_calc.');
255 end if;
256
257 EXCEPTION
258 WHEN FND_API.G_EXC_ERROR THEN
259 ROLLBACK TO mark_event_calc;
260 x_return_status := FND_API.G_RET_STS_ERROR ;
261 FND_MSG_PUB.Count_And_Get
262 (
263 p_count => x_msg_count ,
264 p_data => x_msg_data ,
265 p_encoded => FND_API.G_FALSE
266 );
267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
268 ROLLBACK TO mark_event_calc;
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
270 FND_MSG_PUB.Count_And_Get
271 (
272 p_count => x_msg_count ,
273 p_data => x_msg_data ,
274 p_encoded => FND_API.G_FALSE
275 );
276 WHEN OTHERS THEN
277 ROLLBACK TO mark_event_calc;
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
279 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
280 THEN
281 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
282 END IF;
283 FND_MSG_PUB.Count_And_Get
284 (
285 p_count => x_msg_count ,
286 p_data => x_msg_data ,
287 p_encoded => FND_API.G_FALSE
288 );
289
290 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
291 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
292 'cn.plsql.cn_mark_events_pub.mark_event_calc.exception',
293 sqlerrm);
294 end if;
295
296 END Mark_Event_Calc;
297
298
299 END cn_mark_events_pub ;
[Home] [Help]