[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_SUBMISSION_PUB
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_CALC_SUBMISSION_PUB';
1 PACKAGE BODY cn_calc_submission_pub AS
2 /* $Header: cnpcsbb.pls 120.4 2005/10/27 14:09:06 ymao noship $ */
3
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpcsbb.pls';
6
7 G_LAST_UPDATE_DATE DATE := sysdate;
8 G_LAST_UPDATED_BY NUMBER := FND_GLOBAL.USER_ID;
9 G_CREATION_DATE DATE := sysdate;
10 G_CREATED_BY NUMBER := FND_GLOBAL.USER_ID;
11 G_LAST_UPDATE_LOGIN NUMBER := FND_GLOBAL.LOGIN_ID;
12
13 g_org_id NUMBER;
14
15 g_calc_sub_name CONSTANT VARCHAR2(80)
16 := cn_api.get_lkup_meaning('NAME', 'CALC_SUBMISSION_OBJECT_TYPE');
20 := cn_api.get_lkup_meaning('END_DATE', 'CALC_SUBMISSION_OBJECT_TYPE');
17 g_calc_sub_start_date CONSTANT VARCHAR2(80)
18 := cn_api.get_lkup_meaning('START_DATE', 'CALC_SUBMISSION_OBJECT_TYPE');
19 g_calc_sub_end_date CONSTANT VARCHAR2(80)
21 g_calc_sub_calc_type CONSTANT VARCHAR2(80)
22 := cn_api.get_lkup_meaning('CALC_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE');
23 g_calc_sub_salesrep_option CONSTANT VARCHAR2(80)
24 := cn_api.get_lkup_meaning('SALESREP_OPTION', 'CALC_SUBMISSION_OBJECT_TYPE');
25 g_calc_sub_hierarchy_flag CONSTANT VARCHAR2(80)
26 := cn_api.get_lkup_meaning('HIERARCHY_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE');
27 g_calc_sub_concurrent_flag CONSTANT VARCHAR2(80)
28 := cn_api.get_lkup_meaning('CONCURRENT_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE');
29 g_calc_sub_intelligent_flag CONSTANT VARCHAR2(80)
30 := cn_api.get_lkup_meaning('INTELLIGENT_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE');
31 g_calc_sub_interval_type CONSTANT VARCHAR2(80)
32 := cn_api.get_lkup_meaning('INTERVAL_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE');
33 g_calc_sub_emp_num CONSTANT VARCHAR2(80)
34 := cn_api.get_lkup_meaning('EMPLOYEE_NUMBER', 'CALC_SUBMISSION_OBJECT_TYPE');
35 g_calc_sub_emp_type CONSTANT VARCHAR2(80)
36 := cn_api.get_lkup_meaning('EMPLOYEE_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE');
37 g_calc_sub_user_name CONSTANT VARCHAR2(80)
38 := cn_api.get_lkup_meaning('USER_NAME', 'CALC_SUBMISSION_OBJECT_TYPE');
39 g_calc_sub_resp_name CONSTANT VARCHAR2(80)
40 := cn_api.get_lkup_meaning('RESPONSIBILITY_NAME', 'CALC_SUBMISSION_OBJECT_TYPE');
41
42 TYPE salesrep_id_tbl_type IS TABLE OF cn_salesreps.salesrep_id%TYPE
43 INDEX BY BINARY_INTEGER;
44
45 TYPE plan_element_id_tbl_type IS TABLE OF cn_quotas.quota_id%TYPE
46 INDEX BY BINARY_INTEGER;
47
48 -- ----------------------------------------------------------------------------+
49 -- Procedure: validate_calc_sub_batch
50 -- Desc : check if the record is valid to insert into cn_calc_submission_batches
51 -- ----------------------------------------------------------------------------+
52 PROCEDURE validate_calc_sub_batch
53 (
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2,
57 p_calc_submission_rec IN calc_submission_rec_type,
58 p_loading_status IN VARCHAR2,
59 p_name_validate_flag IN VARCHAR2 := 'Y',
60 x_loading_status OUT NOCOPY VARCHAR2
61 ) IS
62
63 l_api_name CONSTANT VARCHAR2(30) := 'validate_calc_sub_batch';
64 l_loading_status VARCHAR2(100);
65
66 cursor l_ctr_csr (l_start_date date) is
67 select 1
68 from cn_period_statuses_all
69 where period_status = 'O'
70 and org_id = g_org_id
71 and (period_set_id, period_type_id) = (select period_set_id, period_type_id
72 from cn_repositories_all
73 where org_id = g_org_id)
74 and l_start_date between start_date and end_date;
75
76 l_counter number := 0;
77
78 CURSOR l_batch_name_csr IS
79 SELECT COUNT(*)
80 FROM cn_calc_submission_batches_all
81 WHERE name = p_calc_submission_rec.batch_name
82 AND org_id = g_org_id;
83 BEGIN
84 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
85 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
86 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.begin',
87 'Beginning of validate_calc_sub_batch ...');
88 end if;
89
90 -- Initialize API return status to success
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92 x_loading_status := p_loading_status;
93
94 -- Start of API body
95
96 -- batch_name can not be missing or null
97 -- and should uniquely identify the batch
98 IF p_name_validate_flag = 'Y' THEN
99 IF (cn_api.chk_miss_null_char_para
100 (p_char_para => p_calc_submission_rec.batch_name,
101 p_obj_name => g_calc_sub_name,
102 p_loading_status => x_loading_status,
103 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
104 RAISE FND_API.G_EXC_ERROR ;
105 END IF;
106
107 OPEN l_batch_name_csr;
108 FETCH l_batch_name_csr INTO l_counter;
109 CLOSE l_batch_name_csr;
110
111 IF l_counter <> 0 THEN
112 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_EXISTS');
113 fnd_message.set_token('BATCH_NAME', p_calc_submission_rec.batch_name);
114 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
115 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
116 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.error',
117 TRUE);
118 end if;
119
120 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
121 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_EXISTS');
122 fnd_message.set_token('BATCH_NAME', p_calc_submission_rec.batch_name);
123 FND_MSG_PUB.Add;
124 END IF;
125
126 x_loading_status := 'CN_CALC_SUB_EXISTS';
127 RAISE FND_API.G_EXC_ERROR ;
128 END IF;
129 END IF; -- end of p_name_validate_flag
130
131 -- start_date can not be null/missing
132 -- end_date can not be null/missing
133 -- start_date < end_date
134 IF ( (cn_api.invalid_date_range
135 (p_start_date => p_calc_submission_rec.start_date,
136 p_end_date => p_calc_submission_rec.end_date,
137 p_end_date_nullable => FND_API.G_TRUE,
138 p_loading_status => x_loading_status,
139 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
140 RAISE FND_API.G_EXC_ERROR ;
141 END IF;
142
143 -- start_date / end_date must be within open period
144 open l_ctr_csr( p_calc_submission_rec.start_date);
145 fetch l_ctr_csr into l_counter;
149 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_OPEN_DATE');
146 close l_ctr_csr;
147
148 IF l_counter <> 1 then
150 FND_MESSAGE.SET_TOKEN('DATE', p_calc_submission_rec.start_date);
154 TRUE);
151 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
152 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
153 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.error',
155 end if;
156
157 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
158 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_OPEN_DATE');
159 FND_MESSAGE.SET_TOKEN('DATE', p_calc_submission_rec.start_date);
160 FND_MSG_PUB.Add;
161 END IF;
162
163 x_loading_status := 'CN_CALC_SUB_OPEN_DATE';
164 RAISE FND_API.G_EXC_ERROR ;
165 END IF;
166
167 open l_ctr_csr( p_calc_submission_rec.end_date);
168 fetch l_ctr_csr into l_counter;
169 close l_ctr_csr;
170
171 IF l_counter <> 1 then
172 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_OPEN_DATE');
173 FND_MESSAGE.SET_TOKEN('DATE', p_calc_submission_rec.end_date );
174 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
178 end if;
175 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
176 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.error',
177 TRUE);
179
180 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
181 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_OPEN_DATE');
182 FND_MESSAGE.SET_TOKEN('DATE', p_calc_submission_rec.end_date );
183 FND_MSG_PUB.Add;
184 END IF;
185
186 x_loading_status := 'CN_CALC_SUB_OPEN_DATE';
187 RAISE FND_API.G_EXC_ERROR ;
188 END IF;
189
190 -- calculation_type can not be null/missing, must be valid value
191 IF ( (cn_api.chk_miss_null_char_para
192 (p_char_para => p_calc_submission_rec.calculation_type,
193 p_obj_name => g_calc_sub_calc_type,
194 p_loading_status => x_loading_status,
195 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
196 RAISE FND_API.G_EXC_ERROR ;
197 END IF;
198
199 IF ( p_calc_submission_rec.calculation_type NOT IN ('COMMISSION', 'BONUS')) THEN
200 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
201 FND_MESSAGE.SET_TOKEN('OBJ_NAME', g_calc_sub_calc_type);
202 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
203 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
204 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.error',
205 TRUE);
206 end if;
207
208 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
209 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
210 FND_MESSAGE.SET_TOKEN('OBJ_NAME', g_calc_sub_calc_type);
211 FND_MSG_PUB.Add;
212 END IF;
213 x_loading_status := 'CN_INVALID_DATA';
214 RAISE FND_API.G_EXC_ERROR ;
215 END IF;
216
217 -- salesrep_option can not be null/missing, must be valid value
218 IF ( (cn_api.chk_miss_null_char_para
219 (p_char_para => p_calc_submission_rec.salesrep_option,
220 p_obj_name => g_calc_sub_salesrep_option,
221 p_loading_status => x_loading_status,
222 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
223 RAISE FND_API.G_EXC_ERROR ;
224 END IF;
225
226 IF p_calc_submission_rec.salesrep_option NOT IN ('ALL_REPS', 'USER_SPECIFY', 'REPS_IN_NOTIFY_LOG')
227 OR ( p_calc_submission_rec.calculation_type = 'BONUS'
228 AND p_calc_submission_rec.salesrep_option = 'REPS_IN_NOTIFY_LOG' )
229 THEN
230 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
231 FND_MESSAGE.SET_TOKEN('OBJ_NAME',g_calc_sub_salesrep_option );
232 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
233 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
234 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.error',
235 TRUE);
236 end if;
237
238 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
239 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
240 FND_MESSAGE.SET_TOKEN('OBJ_NAME',g_calc_sub_salesrep_option );
241 FND_MSG_PUB.Add;
242 END IF;
243 x_loading_status := 'CN_INVALID_DATA';
244 RAISE FND_API.G_EXC_ERROR ;
245 END IF;
246
247 -- entire_hierarchy can not be null/missing, must be valid value
248 IF ( (cn_api.chk_miss_null_char_para
249 (p_char_para => p_calc_submission_rec.entire_hierarchy,
250 p_obj_name => g_calc_sub_hierarchy_flag,
251 p_loading_status => x_loading_status,
252 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
253 RAISE FND_API.G_EXC_ERROR ;
254 END IF;
255
256 IF p_calc_submission_rec.entire_hierarchy NOT IN ('Y', 'N')
257 OR ( p_calc_submission_rec.salesrep_option IN ('ALL_REPS', 'REPS_IN_NOTIFY_LOG')
258 AND p_calc_submission_rec.entire_hierarchy = 'Y' )
259 THEN
260 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
261 FND_MESSAGE.SET_TOKEN('OBJ_NAME', g_calc_sub_hierarchy_flag );
262 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
263 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
264 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.error',
265 TRUE);
266 end if;
267
268 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
269 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
270 FND_MESSAGE.SET_TOKEN('OBJ_NAME', g_calc_sub_hierarchy_flag );
271 FND_MSG_PUB.Add;
272 END IF;
273 x_loading_status := 'CN_INVALID_DATA';
274 RAISE FND_API.G_EXC_ERROR ;
275 END IF;
276
277 -- concurrent_calculation can not be null/missing, must be valid value
278 IF ( (cn_api.chk_miss_null_char_para
279 (p_char_para => p_calc_submission_rec.concurrent_calculation,
280 p_obj_name => g_calc_sub_concurrent_flag,
281 p_loading_status => x_loading_status,
282 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
283 RAISE FND_API.G_EXC_ERROR ;
284 END IF;
285
286 IF p_calc_submission_rec.concurrent_calculation NOT IN ('Y', 'N') THEN
287 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
288 FND_MESSAGE.SET_TOKEN('OBJ_NAME',g_calc_sub_concurrent_flag );
289 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
290 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
291 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.error',
292 TRUE);
293 end if;
294
295 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
296 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
297 FND_MESSAGE.SET_TOKEN('OBJ_NAME',g_calc_sub_concurrent_flag );
298 FND_MSG_PUB.Add;
302 END IF;
299 END IF;
300 x_loading_status := 'CN_INVALID_DATA';
301 RAISE FND_API.G_EXC_ERROR ;
303
304 -- incremental_calculation can not be null/missing , must be valid value
305 IF ( (cn_api.chk_miss_null_char_para
306 (p_char_para => p_calc_submission_rec.incremental_calculation,
307 p_obj_name => g_calc_sub_intelligent_flag,
308 p_loading_status => x_loading_status,
309 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
310 RAISE FND_API.G_EXC_ERROR ;
311 END IF;
312
313 IF p_calc_submission_rec.incremental_calculation NOT IN ('Y', 'N')
314 OR ( p_calc_submission_rec.calculation_type = 'BONUS'
315 AND p_calc_submission_rec.incremental_calculation = 'Y' )
316 OR ( p_calc_submission_rec.salesrep_option = 'REPS_IN_NOTIFY_LOG'
317 AND p_calc_submission_rec.incremental_calculation = 'N' )
318 THEN
319 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
320 FND_MESSAGE.SET_TOKEN('OBJ_NAME',g_calc_sub_intelligent_flag );
321 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
322 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
323 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.error',
324 TRUE);
325 end if;
326
327 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
328 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
329 FND_MESSAGE.SET_TOKEN('OBJ_NAME',g_calc_sub_intelligent_flag );
330 FND_MSG_PUB.Add;
331 END IF;
332 x_loading_status := 'CN_INVALID_DATA';
333 RAISE FND_API.G_EXC_ERROR ;
334 END IF;
335
336 -- interval_type can not be null/missing, must be valid value if calc_type = 'BONUS'
337 IF p_calc_submission_rec.calculation_type = 'BONUS' THEN
338 IF ( (cn_api.chk_miss_null_char_para
339 (p_char_para => p_calc_submission_rec.interval_type,
340 p_obj_name => g_calc_sub_interval_type,
341 p_loading_status => x_loading_status,
342 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
343 RAISE FND_API.G_EXC_ERROR ;
344 END IF;
345
346 IF p_calc_submission_rec.interval_type NOT IN ('ALL', 'PERIOD', 'QUARTER', 'YEAR' ) THEN
347 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
348 FND_MESSAGE.SET_TOKEN('OBJ_NAME', g_calc_sub_interval_type );
349 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
350 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
351 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.error',
352 TRUE);
353 end if;
354
355 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
356 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
357 FND_MESSAGE.SET_TOKEN('OBJ_NAME', g_calc_sub_interval_type );
358 FND_MSG_PUB.Add;
359 END IF;
360 x_loading_status := 'CN_INVALID_DATA';
361 RAISE FND_API.G_EXC_ERROR ;
362 END IF;
363 END IF;
364
365 -- End of API body.
366
367 -- Standard call to get message count and if count is 1, get message info.
368 FND_MSG_PUB.Count_And_Get
369 (p_count => x_msg_count ,
370 p_data => x_msg_data ,
371 p_encoded => FND_API.G_FALSE
372 );
373
374 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
375 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
376 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.end',
377 'End of validate_calc_sub_batch.');
378 end if;
379
380 EXCEPTION
381 WHEN FND_API.G_EXC_ERROR THEN
382 x_return_status := FND_API.G_RET_STS_ERROR ;
383 FND_MSG_PUB.Count_And_Get
384 (
385 p_count => x_msg_count ,
386 p_data => x_msg_data ,
387 p_encoded => FND_API.G_FALSE
388 );
389 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
391 x_loading_status := 'UNEXPECTED_ERR';
392 FND_MSG_PUB.Count_And_Get
393 (
394 p_count => x_msg_count ,
395 p_data => x_msg_data ,
396 p_encoded => FND_API.G_FALSE
397 );
398 WHEN OTHERS THEN
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
400 x_loading_status := 'UNEXPECTED_ERR';
401 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
402 THEN
403 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
404 END IF;
405 FND_MSG_PUB.Count_And_Get
406 (
407 p_count => x_msg_count ,
408 p_data => x_msg_data ,
409 p_encoded => FND_API.G_FALSE
410 );
411
412 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
413 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
414 'cn.plsql.cn_calc_submission_pub.validate_calc_sub_batch.exception',
415 sqlerrm);
416 end if;
417
418 END validate_calc_sub_batch;
419
420 FUNCTION validate_salesrep ( p_salesrep_rec salesrep_rec_type,
421 x_salesrep_id OUT NOCOPY cn_salesreps.salesrep_id%TYPE,
422 x_loading_status OUT NOCOPY VARCHAR2 ) RETURN BOOLEAN IS
423
424 CURSOR l_has_had_comp_plan_csr ( l_salesrep_id cn_salesreps.salesrep_id%TYPE) IS
425 SELECT 1
426 FROM cn_srp_intel_periods_all
427 WHERE salesrep_id = l_salesrep_id
428 AND org_id = g_org_id;
429
430 l_salesrep_id NUMBER;
431 l_counter NUMBER;
432 l_return_status VARCHAR2(30);
433
434 BEGIN
435
436 -- emp_num can not be missing
437 IF (cn_api.chk_miss_char_para
438 (p_char_para => p_salesrep_rec.employee_number,
442 RETURN FALSE;
439 p_para_name => g_calc_sub_emp_num,
440 p_loading_status => x_loading_status,
441 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
443 END IF;
444
445 -- type can not be missing
446 IF (cn_api.chk_miss_char_para
447 (p_char_para => p_salesrep_rec.TYPE,
448 p_para_name => g_calc_sub_emp_type,
449 p_loading_status => x_loading_status,
450 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
451 RETURN FALSE;
452 END IF;
453
454 if (nvl(p_salesrep_rec.hierarchy_flag, 'N') not in ('N', 'Y')) then
455 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
456 FND_MESSAGE.SET_TOKEN('OBJ_NAME', g_calc_sub_hierarchy_flag );
457 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
458 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
459 'cn.plsql.cn_calc_submission_pub.validate_salesrep.error',
460 TRUE);
461 end if;
462
463 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
464 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_DATA');
465 FND_MESSAGE.SET_TOKEN('OBJ_NAME', g_calc_sub_hierarchy_flag );
466 FND_MSG_PUB.Add;
467 END IF;
468 return false;
469 end if;
470
471 -- (employee_number + type) must uniquely identify one salesrep
472 -- in cn_salesreps
473 cn_api.chk_and_get_salesrep_id( p_emp_num => p_salesrep_rec.employee_number,
474 p_type => p_salesrep_rec.TYPE,
475 p_org_id => g_org_id,
476 x_salesrep_id => l_salesrep_id,
477 x_return_status => l_return_status,
478 x_loading_status => x_loading_status,
479 p_show_message => FND_API.G_TRUE);
480 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
481 RETURN FALSE;
482 END IF;
483
484 OPEN l_has_had_comp_plan_csr( l_salesrep_id );
485 FETCH l_has_had_comp_plan_csr INTO l_counter;
486
487 IF l_has_had_comp_plan_csr%notfound THEN
488 CLOSE l_has_had_comp_plan_csr;
489
490 FND_MESSAGE.SET_NAME ('CN', 'CN_CALC_PLAN_NOT_ASSIGNED');
491 FND_MESSAGE.SET_TOKEN('EMPLOYEE_NUMBER', p_salesrep_rec.employee_number );
492 FND_MESSAGE.SET_TOKEN('EMPLOYEE_TYPE', p_salesrep_rec.TYPE );
493 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
494 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
495 'cn.plsql.cn_calc_submission_pub.validate_salesrep.error',
496 TRUE);
497 end if;
498
499 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
500 FND_MESSAGE.SET_NAME ('CN', 'CN_CALC_PLAN_NOT_ASSIGNED');
501 FND_MESSAGE.SET_TOKEN('EMPLOYEE_NUMBER', p_salesrep_rec.employee_number );
502 FND_MESSAGE.SET_TOKEN('EMPLOYEE_TYPE', p_salesrep_rec.TYPE );
503 FND_MSG_PUB.Add;
504 END IF;
505
506 x_loading_status := 'CN_CALC_PLAN_NOT_ASSIGNED';
507 RETURN FALSE;
508 END IF;
509 CLOSE l_has_had_comp_plan_csr;
510
511 x_salesrep_id := l_salesrep_id;
512 RETURN TRUE;
513 END validate_salesrep;
514
515 FUNCTION validate_bonus_pe ( p_quota_name IN cn_quotas.name%TYPE ,
516 p_interval_type IN VARCHAR2,
517 x_quota_id OUT NOCOPY cn_quotas.quota_id%TYPE,
518 x_loading_status OUT NOCOPY VARCHAR2 ) RETURN BOOLEAN IS
519
520 CURSOR l_bonus_pe_csr IS
521 SELECT quota_id
522 FROM cn_quotas_all
523 WHERE name = p_quota_name
524 AND org_id = g_org_id
525 AND incentive_type_code = 'BONUS'
526 AND ( (interval_type_id = -1000 AND p_interval_type = 'PERIOD')
527 OR (interval_type_id = -1001 AND p_interval_type = 'QUARTER')
528 OR (interval_type_id = -1002 AND p_interval_type = 'YEAR')
529 OR (interval_type_id IN (-1000, -1001, -1002) AND p_interval_type = 'ALL')
530 );
531 BEGIN
532 OPEN l_bonus_pe_csr;
533 FETCH l_bonus_pe_csr INTO x_quota_id;
534
535 IF l_bonus_pe_csr%notfound THEN
536 CLOSE l_bonus_pe_csr;
537
538 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_PE_NO_MATCH');
539 FND_MESSAGE.SET_TOKEN('QUOTA_NAME',p_quota_name);
540 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
541 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
542 'cn.plsql.cn_calc_submission_pub.validate_bonus_pe.error',
543 TRUE);
544 end if;
545
546 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
547 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_PE_NO_MATCH');
548 FND_MESSAGE.SET_TOKEN('QUOTA_NAME',p_quota_name);
549 FND_MSG_PUB.Add;
550 END IF;
551
552 x_loading_status := 'CN_CALC_PE_NO_MATCH';
553 RETURN FALSE;
554 ELSE
555 CLOSE l_bonus_pe_csr;
556 RETURN TRUE;
557 END IF;
558
559 END validate_bonus_pe;
560
561
562 -- ----------------------------------------------------------------------------+
563 -- Procedure: validate_salesrep_entries
564 -- Desc : check if the record is valid to insert into cn_calc_submission_entries
565 -- ----------------------------------------------------------------------------+
566 PROCEDURE validate_salesrep_entries
567 (
568 x_return_status OUT NOCOPY VARCHAR2,
569 x_msg_count OUT NOCOPY NUMBER,
570 x_msg_data OUT NOCOPY VARCHAR2,
571 p_salesrep_tbl IN salesrep_tbl_type,
572 p_loading_status IN VARCHAR2,
573 x_salesreps_id_tbl OUT NOCOPY salesrep_id_tbl_type,
574 x_loading_status OUT NOCOPY VARCHAR2
575 ) IS
576
577 l_api_name CONSTANT VARCHAR2(30) := 'validate_salesrep_entries';
578 l_loading_status VARCHAR2(100);
579
580 l_salesrep_id NUMBER;
584 'cn.plsql.cn_calc_submission_pub.validate_salesrep_entries.begin',
581 BEGIN
582 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
583 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
585 'Beginning of validate_salesrep_entries ...');
586 end if;
587
588 -- Initialize API return status to success
589 x_return_status := FND_API.G_RET_STS_SUCCESS;
590 x_loading_status := p_loading_status;
591
592 -- Start of API body
593
594 IF p_salesrep_tbl.COUNT = 0 THEN
595 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_NO_SALESREP');
596 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
597 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
598 'cn.plsql.cn_calc_submission_pub.validate_salesrep_entries.error',
599 TRUE);
600 end if;
601
602 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
603 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_NO_SALESREP');
604 FND_MSG_PUB.Add;
605 END IF;
606 x_loading_status := 'CN_CALC_NO_SALESREP';
607 RAISE FND_API.g_exc_error;
608 ELSE
609 FOR ctr IN 1 .. p_salesrep_tbl.COUNT LOOP
610 IF validate_salesrep( p_salesrep_rec => p_salesrep_tbl(ctr),
611 x_salesrep_id => l_salesrep_id,
612 x_loading_status => x_loading_status ) THEN
613 x_salesreps_id_tbl(ctr) := l_salesrep_id;
614 ELSE
615 RAISE FND_API.g_exc_error;
616 END IF;
617 END LOOP;
618 END IF;
619
620 -- End of API body.
621
622 -- Standard call to get message count and if count is 1, get message info.
623 FND_MSG_PUB.Count_And_Get
624 (p_count => x_msg_count ,
625 p_data => x_msg_data ,
626 p_encoded => FND_API.G_FALSE
627 );
628
629 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
630 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
631 'cn.plsql.cn_calc_submission_pub.validate_salesrep_entries.end',
632 'End of validate_salesrep_entries.');
633 end if;
634
635 EXCEPTION
636 WHEN FND_API.G_EXC_ERROR THEN
637 x_return_status := FND_API.G_RET_STS_ERROR ;
638 FND_MSG_PUB.Count_And_Get
639 (
640 p_count => x_msg_count ,
641 p_data => x_msg_data ,
642 p_encoded => FND_API.G_FALSE
643 );
644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
646 x_loading_status := 'UNEXPECTED_ERR';
647 FND_MSG_PUB.Count_And_Get
648 (
649 p_count => x_msg_count ,
650 p_data => x_msg_data ,
651 p_encoded => FND_API.G_FALSE
652 );
653 WHEN OTHERS THEN
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
655 x_loading_status := 'UNEXPECTED_ERR';
656 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
657 THEN
658 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
659 END IF;
660 FND_MSG_PUB.Count_And_Get
661 (
662 p_count => x_msg_count ,
663 p_data => x_msg_data ,
664 p_encoded => FND_API.G_FALSE
665 );
666
667 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
668 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
669 'cn.plsql.cn_calc_submission_pub.validate_salesrep_entries.exception',
670 sqlerrm);
671 end if;
672
673 END validate_salesrep_entries;
674
675
676 -- ----------------------------------------------------------------------------+
677 -- Procedure: validate_bonus_pe_entries
678 -- Desc : check if the record is valid to insert into cn_calc_submission_batches
679 -- ----------------------------------------------------------------------------+
680 PROCEDURE validate_bonus_pe_entries
681 (
682 x_return_status OUT NOCOPY VARCHAR2,
683 x_msg_count OUT NOCOPY NUMBER,
684 x_msg_data OUT NOCOPY VARCHAR2,
685 p_bonus_pe_tbl IN plan_element_tbl_type,
686 p_interval_type IN VARCHAR2,
687 p_loading_status IN VARCHAR2,
688 x_bonus_pe_id_tbl OUT NOCOPY plan_element_id_tbl_type,
689 x_loading_status OUT NOCOPY VARCHAR2
690 ) IS
691
692 l_api_name CONSTANT VARCHAR2(30) := 'validate_bonus_pe_entries';
693 l_loading_status VARCHAR2(100);
694
695 l_quota_id NUMBER;
696 BEGIN
697 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
698 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
699 'cn.plsql.cn_calc_submission_pub.validate_bonus_pe_entries.begin',
700 'Beginning of validate_bonus_pe_entries ...');
701 end if;
702
703 -- Initialize API return status to success
704 x_return_status := FND_API.G_RET_STS_SUCCESS;
705 x_loading_status := p_loading_status;
706
707 -- Start of API body
708 FOR ctr IN 1 .. p_bonus_pe_tbl.COUNT LOOP
709 IF validate_bonus_pe ( p_quota_name => p_bonus_pe_tbl(ctr),
710 p_interval_type => p_interval_type,
711 x_quota_id => l_quota_id,
712 x_loading_status => x_loading_status ) THEN
713 x_bonus_pe_id_tbl(ctr) := l_quota_id;
714 ELSE
715 RAISE FND_API.g_exc_error;
716 END IF;
717 END LOOP;
718
719 -- End of API body.
720
721 -- Standard call to get message count and if count is 1, get message info.
722 FND_MSG_PUB.Count_And_Get
723 (p_count => x_msg_count ,
724 p_data => x_msg_data ,
725 p_encoded => FND_API.G_FALSE
729 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
726 );
727
728 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
730 'cn.plsql.cn_calc_submission_pub.validate_bonus_pe_entries.end',
731 'End of validate_bonus_pe_entries.');
732 end if;
733
734 EXCEPTION
735 WHEN FND_API.G_EXC_ERROR THEN
736 x_return_status := FND_API.G_RET_STS_ERROR ;
737 FND_MSG_PUB.Count_And_Get
738 (
739 p_count => x_msg_count ,
740 p_data => x_msg_data ,
741 p_encoded => FND_API.G_FALSE
742 );
743 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
745 x_loading_status := 'UNEXPECTED_ERR';
746 FND_MSG_PUB.Count_And_Get
747 (
748 p_count => x_msg_count ,
749 p_data => x_msg_data ,
750 p_encoded => FND_API.G_FALSE
751 );
752 WHEN OTHERS THEN
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
754 x_loading_status := 'UNEXPECTED_ERR';
755 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
756 THEN
757 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
758 END IF;
759 FND_MSG_PUB.Count_And_Get
760 (
761 p_count => x_msg_count ,
762 p_data => x_msg_data ,
763 p_encoded => FND_API.G_FALSE
764 );
765
766 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
767 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
768 'cn.plsql.cn_calc_submission_pub.validate_bonus_pe_entries.exception',
769 sqlerrm);
770 end if;
771
772 END validate_bonus_pe_entries;
773
774 -- ----------------------------------------------------------------------------+
775 -- Procedure: validate_app_user_resp
776 -- Desc : check if the record is valid to insert into cn_calc_submission_batches
777 -- ----------------------------------------------------------------------------+
778 PROCEDURE validate_app_user_resp
779 ( x_return_status OUT NOCOPY VARCHAR2,
780 x_msg_count OUT NOCOPY NUMBER,
781 x_msg_data OUT NOCOPY VARCHAR2,
782 p_app_user_resp_rec IN app_user_resp_rec_type,
783 p_loading_status IN VARCHAR2,
784 x_user_id OUT NOCOPY NUMBER,
785 x_responsibility_id OUT NOCOPY NUMBER,
786 x_loading_status OUT NOCOPY VARCHAR2
787 ) IS
788
789 l_api_name CONSTANT VARCHAR2(30) := 'validate_app_user_resp';
790 l_loading_status VARCHAR2(100);
791
792 BEGIN
793 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
794 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
795 'cn.plsql.cn_calc_submission_pub.validate_app_user_resp.begin',
796 'Beginning of validate_app_user_resp ...');
797 end if;
798
802
799 -- Initialize API return status to success
800 x_return_status := FND_API.G_RET_STS_SUCCESS;
801 x_loading_status := p_loading_status;
803 -- Start of API body
804
805 -- user_name cannot be missing/null
806 IF ( (cn_api.chk_miss_null_char_para
807 (p_char_para => p_app_user_resp_rec.user_name,
808 p_obj_name => g_calc_sub_user_name,
809 p_loading_status => x_loading_status,
810 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
811 RAISE FND_API.G_EXC_ERROR ;
812 END IF;
813
814 BEGIN
815 SELECT user_id INTO x_user_id
816 FROM fnd_user
817 WHERE user_name = p_app_user_resp_rec.user_name;
818
819 EXCEPTION
820 WHEN no_data_found THEN
821 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_USER_NOT_EXIST');
822 fnd_message.set_token('USER_NAME', p_app_user_resp_rec.user_name );
826 TRUE);
823 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
824 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
825 'cn.plsql.cn_calc_submission_pub.validate_app_user_resp.validation',
827 end if;
828
829 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
830 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_USER_NOT_EXIST');
831 fnd_message.set_token('USER_NAME', p_app_user_resp_rec.user_name );
832 FND_MSG_PUB.Add;
833 END IF;
834 x_loading_status := 'CN_CALC_USER_NOT_EXIST';
835 RAISE FND_API.G_EXC_ERROR ;
836 WHEN OTHERS THEN
837 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
838 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
839 'cn.plsql.cn_calc_submission_pub.validate_app_user_resp.exception',
840 sqlerrm);
841 end if;
842
843 RAISE FND_API.G_EXC_ERROR ;
844 END;
845
846 -- responsibility_name can not be missing/null
847 IF ( (cn_api.chk_miss_null_char_para
848 (p_char_para => p_app_user_resp_rec.responsibility_name,
849 p_obj_name => g_calc_sub_resp_name,
850 p_loading_status => x_loading_status,
851 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
852 RAISE FND_API.G_EXC_ERROR ;
853 END IF;
854
855 BEGIN
856 -- clku, bug 3683443, added hints to do index skip scan
857 SELECT /*+ index_ss(V.T) */ responsibility_id
858 INTO x_responsibility_id
859 FROM fnd_responsibility_vl
860 WHERE responsibility_name = p_app_user_resp_rec.responsibility_name;
861
862 EXCEPTION
863 WHEN no_data_found THEN
864 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_RESP_NOT_EXIST');
865 fnd_message.set_token('RESP_NAME', p_app_user_resp_rec.responsibility_name );
866 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
867 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
868 'cn.plsql.cn_calc_submission_pub.validate_app_user_resp.validation',
869 TRUE);
870 end if;
871
872 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
873 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_RESP_NOT_EXIST');
874 fnd_message.set_token('RESP_NAME', p_app_user_resp_rec.responsibility_name );
875 FND_MSG_PUB.Add;
876 END IF;
877 x_loading_status := 'CN_CALC_RESP_NOT_EXIST';
878 RAISE FND_API.G_EXC_ERROR ;
879
880 WHEN OTHERS THEN
881 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
882 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
883 'cn.plsql.cn_calc_submission_pub.validate_app_user_resp.exception',
884 sqlerrm);
885 end if;
886
887 RAISE FND_API.G_EXC_ERROR ;
888 END;
889
890 -- End of API body.
891
892 -- Standard call to get message count and if count is 1, get message info.
893 FND_MSG_PUB.Count_And_Get
894 (p_count => x_msg_count ,
895 p_data => x_msg_data ,
896 p_encoded => FND_API.G_FALSE
897 );
898
899 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
900 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
901 'cn.plsql.cn_calc_submission_pub.validate_app_user_resp.end',
902 'End of validate_app_user_resp.');
903 end if;
904
905 EXCEPTION
906 WHEN FND_API.G_EXC_ERROR THEN
907 x_return_status := FND_API.G_RET_STS_ERROR ;
908 FND_MSG_PUB.Count_And_Get
909 (
910 p_count => x_msg_count ,
911 p_data => x_msg_data ,
912 p_encoded => FND_API.G_FALSE
913 );
914 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
916 x_loading_status := 'UNEXPECTED_ERR';
917 FND_MSG_PUB.Count_And_Get
918 (
919 p_count => x_msg_count ,
920 p_data => x_msg_data ,
921 p_encoded => FND_API.G_FALSE
922 );
923 WHEN OTHERS THEN
924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
925 x_loading_status := 'UNEXPECTED_ERR';
926 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
927 THEN
928 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
929 END IF;
930 FND_MSG_PUB.Count_And_Get
931 (
932 p_count => x_msg_count ,
933 p_data => x_msg_data ,
934 p_encoded => FND_API.G_FALSE
935 );
936
937 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
938 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
939 'cn.plsql.cn_calc_submission_pub.validate_app_user_resp.exception',
940 sqlerrm);
941 end if;
942
943 END validate_app_user_resp;
944
945 FUNCTION get_calc_sub_batch_status ( p_calc_sub_batch_id NUMBER) RETURN VARCHAR2 IS
946 CURSOR l_status_csr IS
947 SELECT status
948 FROM cn_calc_submission_batches_all
949 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
950
951 x_status VARCHAR2(30);
952
953 BEGIN
954 OPEN l_status_csr;
955 FETCH l_status_csr INTO x_status;
956 CLOSE l_status_csr;
957
958 RETURN x_status;
959 END get_calc_sub_batch_status;
960
961
962 -- Start of Comments
963 -- API name : Create_Calc_Submission
964 -- Type : Public.
965 -- Pre-reqs : None.
969 -- salesreps/ passed_in bonus plan elements
966 -- Usage : Used to create a new calculation submission batch with passed_in
967 -- salesreps/ passed_in bonus plan elements
968 -- Desc : Procedure to create a new calculation submission batch with passed_in
970 -- Parameters :
971 -- IN : p_api_version IN NUMBER Require
972 -- p_init_msg_list IN VARCHAR2 Optional
973 -- Default = FND_API.G_FALSE
974 -- p_commit IN VARCHAR2 Optional
975 -- Default = FND_API.G_FALSE
976 -- p_validation_level IN NUMBER Optional
977 -- Default = FND_API.G_VALID_LEVEL_FULL
978 -- OUT : x_return_status OUT VARCHAR2(1)
979 -- x_msg_count OUT NUMBER
980 -- x_msg_data OUT VARCHAR2(2000)
981 -- IN : p_calc_submission_rec IN calc_submission_rec_type
982 -- Version : Current version 1.0
983 -- Initial version 1.0
984 --
985 -- Notes
986 --
987 --
988 -- Description :
989 -- Create Calc Submission is a Public Package which allows us to create
990 -- the calculation submission batch.
991 ------------------+
992 -- p_calc_submission_rec Input parameter
993 -- name calculation submission batch name, Mandatory
994 -- Should uniquely identify the batch
995 -- start_date start date Mandatory
996 -- Must be within opened period
997 -- end_date end date must be within opened period Mandatory
998 -- Must be within opened period
999 -- calc_type type of calculation Mandatory
1000 -- Valid values: COMMISSION/BONUS
1001 -- salesrep_option salesrep option Mandatory
1002 -- Valid values: ALL_REPS/USER_SPECIFY/REPS_IN_NOTIFY_LOG
1003 -- IF calc_type = BONUS , REPS_IN_NOTIFY_LOG is not valid.
1004 -- hierarchy_flag entire hierarchy or not Mandatory
1005 -- Valid values: Y/N
1006 -- IF salesrep_option = ALL_REPS or REPS_IN_NOTIFY_LOG,
1007 -- hierarchy_flag should be 'N'.
1008 -- concurrent_flag concurrent calculation or not ( Y/N ) Mandatory
1009 -- Valid values: Y/N
1010 -- intelligent_flag incremental calculation or not ( Y/N) Mandatory
1011 -- Valid values: Y/N
1012 -- IF salesrep_option = REPS_IN_NOTIFY_LOG,
1013 -- intelligent_flag should be 'Y'.
1014 -- interval_type interval type for bonus plan elements Optional
1015 -- Valid values: PERIOD/QUARTER/YEAR/ALL
1016 -- Mandatory when calc_type = 'COMMISSION'
1017 --
1018 -- salesrep_tbl list of salesreps' name Optional
1019 -- Valid when salesrep_option = 'USER_SPECIFY'
1020 -- Sales persons listed currently have or previously had
1021 -- compensation plan assigned.
1022 -- bonus_pe_tbl list of bonus plan elements' name Optional
1023 -- Valid when calc_type = BONUS
1024 -- Plan elements listed should be 'BONUS' type and their interval type should
1025 -- match the value of interval_type.
1026 --
1027 ------------------------+
1028 -- End of comments
1029 PROCEDURE Create_Calc_Submission
1030 (
1031 p_api_version IN NUMBER,
1032 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1033 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1034 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1035 x_return_status OUT NOCOPY VARCHAR2,
1036 x_msg_count OUT NOCOPY NUMBER,
1037 x_msg_data OUT NOCOPY VARCHAR2,
1038 p_calc_submission_rec IN calc_submission_rec_type := g_miss_calc_submission_rec,
1039 p_app_user_resp_rec IN app_user_resp_rec_type := g_miss_app_user_resp_rec,
1040 p_salesrep_tbl IN salesrep_tbl_type := g_miss_salesrep_tbl,
1041 p_bonus_pe_tbl IN plan_element_tbl_type := g_miss_pe_tbl,
1042 x_loading_status OUT NOCOPY VARCHAR2
1043 ) IS
1044
1045 l_api_name CONSTANT VARCHAR2(30) := 'Create_Calc_Submission';
1046 l_api_version CONSTANT NUMBER := 1.0;
1047
1048 l_calc_sub_batch_id NUMBER;
1049 l_interval_type_id NUMBER;
1050 l_hierarchy_flag VARCHAR2(1);
1051
1052 l_p_calc_submission_rec calc_submission_rec_type;
1053 l_OAI_array JTF_USR_HKS.OAI_data_array_type;
1054
1055 l_salesreps_id_tbl salesrep_id_tbl_type;
1056 l_bonus_pe_id_tbl plan_element_id_tbl_type;
1057 l_user_id NUMBER;
1058 l_responsibility_id NUMBER;
1059 l_unfinished BOOLEAN := TRUE;
1060
1061 l_calc_sub_status cn_calc_submission_batches.status%TYPE;
1062 l_process_audit_id NUMBER;
1063 l_process_audit_status VARCHAR2(30);
1064
1065 l_bind_data_id NUMBER;
1066
1067 l_status VARCHAR2(30);
1068 BEGIN
1069 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1070 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1071 'cn.plsql.cn_calc_submission_pub.create_calc_submission.begin',
1072 'Beginning of create_calc_submission ...');
1073 end if;
1074
1075 -- Standard Start of API savepoint
1076 SAVEPOINT create_calc_submission;
1077 -- Standard call to check for call compatibility.
1078 IF NOT FND_API.compatible_api_call
1082 END IF;
1079 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
1080 THEN
1081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1083 -- Initialize message list if p_init_msg_list is set to TRUE.
1084 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1085 FND_MSG_PUB.initialize;
1086 END IF;
1087
1088 -- Initialize API return status to success
1089 x_return_status := FND_API.G_RET_STS_SUCCESS;
1090 x_loading_status := 'CN_INSERTED';
1091
1092 -- API body starts here
1093 l_p_calc_submission_rec := p_calc_submission_rec;
1094
1095 -- Due to the change of moving this flag to the rep level, the caller can choose not to
1096 -- pass a value for entire_hierarchy when running calculation. However, for backward
1097 -- compatibility, we still recognize entire_hierarchy if it is specified as 'Y'
1098 if (p_calc_submission_rec.entire_hierarchy = fnd_api.g_miss_char) then
1099 l_p_calc_submission_rec.entire_hierarchy := 'N';
1100 end if;
1101
1102 -- validate user_name/responsibility name
1103 IF l_p_calc_submission_rec.concurrent_calculation = 'Y' THEN
1104 validate_app_user_resp( x_return_status => x_return_status,
1105 x_msg_count => x_msg_count,
1106 x_msg_data => x_msg_data,
1107 p_app_user_resp_rec => p_app_user_resp_rec,
1108 p_loading_status => x_loading_status,
1109 x_user_id => l_user_id,
1110 x_responsibility_id => l_responsibility_id,
1111 x_loading_status => x_loading_status
1112 );
1113
1114 IF x_return_status <> FND_API.g_ret_sts_success THEN
1115 RAISE FND_API.G_EXC_ERROR ;
1116 END IF;
1117 END IF;
1118
1119 fnd_global.apps_initialize (user_id => l_user_id,
1120 resp_id => l_responsibility_id,
1121 resp_appl_id => 283
1122 );
1123
1124 g_org_id := p_calc_submission_rec.org_id;
1125 mo_global.validate_orgid_pub_api(org_id => g_org_id,
1126 status => l_status);
1127
1128 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1129 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1130 'cn.plsql.cn_calc_submission_pub.create_calc_submission.org_validate',
1131 'Validated org_id = ' || g_org_id || ' status = '||l_status);
1132 end if;
1133
1134 l_p_calc_submission_rec.org_id := g_org_id;
1135
1136
1137 if JTF_USR_HKS.Ok_to_Execute( 'CN_CALC_SUBMISSION_PUB',
1138 'CREATE_CALC_SUBMISSION', 'B', 'C' ) then
1139
1140 CN_CALC_SUBMISSION_CUHK.create_calc_submission_pre
1141 ( p_api_version => p_api_version,
1142 p_init_msg_list => p_init_msg_list,
1143 p_commit => FND_API.G_FALSE,
1144 p_validation_level => p_validation_level,
1145 x_return_status => x_return_status,
1146 x_msg_count => x_msg_count,
1147 x_msg_data => x_msg_data,
1148 p_calc_submission_rec => l_p_calc_submission_rec,
1149 x_loading_status => x_loading_status
1150 );
1151 if ( x_return_status = FND_API.G_RET_STS_ERROR ) then
1152 RAISE FND_API.G_EXC_ERROR;
1153 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1155 end if;
1156 end if;
1157
1158 if JTF_USR_HKS.Ok_to_Execute( 'CN_CALC_SUBMISSION_PUB',
1159 'CREATE_CALC_SUBMISSION', 'B', 'V' ) then
1160 cn_calc_submission_VUHK.create_calc_submission_pre
1161 ( p_api_version => p_api_version,
1162 p_init_msg_list => p_init_msg_list,
1163 p_commit => FND_API.G_FALSE,
1164 p_validation_level => p_validation_level,
1165 x_return_status => x_return_status,
1166 x_msg_count => x_msg_count,
1167 x_msg_data => x_msg_data,
1168 p_calc_submission_rec => l_p_calc_submission_rec,
1169 x_loading_status => x_loading_status
1170 );
1171 if ( x_return_status = FND_API.G_RET_STS_ERROR ) then
1172 RAISE FND_API.G_EXC_ERROR;
1173 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1175 end if;
1176 END IF;
1177
1178 --+
1179 -- Validate calculation submission batch level
1180 --+
1181 validate_calc_sub_batch( x_return_status => x_return_status,
1182 x_msg_count => x_msg_count,
1183 x_msg_data => x_msg_data,
1184 p_calc_submission_rec => l_p_calc_submission_rec,
1185 p_loading_status => x_loading_status,
1186 x_loading_status => x_loading_status
1187 );
1188
1189 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
1190 RAISE FND_API.G_EXC_ERROR ;
1191 END IF;
1192
1193 -- continue to validate salesrep_entries
1194 IF l_p_calc_submission_rec.salesrep_option = 'USER_SPECIFY' THEN
1195 validate_salesrep_entries( x_return_status => x_return_status,
1196 x_msg_count => x_msg_count,
1197 x_msg_data => x_msg_data,
1198 p_salesrep_tbl => p_salesrep_tbl,
1199 p_loading_status => x_loading_status,
1200 x_salesreps_id_tbl => l_salesreps_id_tbl,
1201 x_loading_status => x_loading_status
1202 );
1203
1204 IF x_return_status <> FND_API.g_ret_sts_success THEN
1205 RAISE FND_API.G_EXC_ERROR ;
1206 END IF;
1207 END IF;
1208
1209 -- continue to validate bonus plan elements
1210 IF l_p_calc_submission_rec.calculation_type = 'BONUS' THEN
1211 validate_bonus_pe_entries ( x_return_status => x_return_status,
1212 x_msg_count => x_msg_count,
1213 x_msg_data => x_msg_data,
1214 p_bonus_pe_tbl => p_bonus_pe_tbl,
1218 x_loading_status => x_loading_status
1215 p_interval_type => l_p_calc_submission_rec.interval_type,
1216 p_loading_status => x_loading_status,
1217 x_bonus_pe_id_tbl => l_bonus_pe_id_tbl,
1219 );
1220
1221 IF x_return_status <> FND_API.g_ret_sts_success THEN
1222 RAISE FND_API.G_EXC_ERROR ;
1223 END IF;
1224 END IF;
1225
1226
1227 -- IF program reaches here, all validations are successful.
1228 -- start to create calc_submission_batch
1229 l_calc_sub_batch_id := cn_calc_sub_batches_pkg.get_calc_sub_batch_id;
1230
1231 IF l_p_calc_submission_rec.calculation_type = 'BONUS' THEN
1232 --clku, bug 3428365
1233 /*SELECT interval_type_id INTO l_interval_type_id
1234 FROM cn_interval_types
1235 WHERE name = l_p_calc_submission_rec.interval_type;*/
1236
1237 IF l_p_calc_submission_rec.interval_type = 'PERIOD' THEN
1238 l_interval_type_id := -1000;
1239 END IF;
1240
1241 IF l_p_calc_submission_rec.interval_type = 'QUARTER' THEN
1242 l_interval_type_id := -1001;
1243 END IF;
1244
1245 IF l_p_calc_submission_rec.interval_type = 'YEAR' THEN
1246 l_interval_type_id := -1002;
1247 END IF;
1248
1249 IF l_p_calc_submission_rec.interval_type = 'ALL' THEN
1250 l_interval_type_id := -1003;
1251 END IF;
1252
1253 END IF;
1254
1255 -- insert into cn_calc_submission_batches
1256 cn_calc_sub_batches_pkg.begin_record
1257 ( p_operation => 'INSERT',
1258 p_calc_sub_batch_id => l_calc_sub_batch_id,
1259 p_name => l_p_calc_submission_rec.batch_name,
1260 p_start_date => l_p_calc_submission_rec.start_date,
1261 p_end_date => l_p_calc_submission_rec.end_date,
1262 p_intelligent_flag => l_p_calc_submission_rec.incremental_calculation,
1263 p_hierarchy_flag => l_p_calc_submission_rec.entire_hierarchy,
1264 p_salesrep_option => l_p_calc_submission_rec.salesrep_option,
1265 p_concurrent_flag => l_p_calc_submission_rec.concurrent_calculation,
1266 p_status => 'INCOMPLETE',
1267 p_interval_type_id => l_interval_type_id,
1268 p_org_id => g_org_id,
1269 p_calc_type => l_p_calc_submission_rec.calculation_type,
1270 p_attribute_category => l_p_calc_submission_rec.attribute_category,
1271 p_attribute1 => l_p_calc_submission_rec.attribute1,
1272 p_attribute2 => l_p_calc_submission_rec.attribute2,
1273 p_attribute3 => l_p_calc_submission_rec.attribute3,
1274 p_attribute4 => l_p_calc_submission_rec.attribute4,
1275 p_attribute5 => l_p_calc_submission_rec.attribute5,
1276 p_attribute6 => l_p_calc_submission_rec.attribute6,
1277 p_attribute7 => l_p_calc_submission_rec.attribute7,
1278 p_attribute8 => l_p_calc_submission_rec.attribute8,
1279 p_attribute9 => l_p_calc_submission_rec.attribute9,
1280 p_attribute10 => l_p_calc_submission_rec.attribute10,
1281 p_attribute11 => l_p_calc_submission_rec.attribute11,
1282 p_attribute12 => l_p_calc_submission_rec.attribute12,
1283 p_attribute13 => l_p_calc_submission_rec.attribute13,
1284 p_attribute14 => l_p_calc_submission_rec.attribute14,
1285 p_attribute15 => l_p_calc_submission_rec.attribute15,
1286 p_last_update_date => g_last_update_date,
1287 p_last_updated_by => g_last_updated_by,
1288 p_creation_date => g_creation_date,
1289 p_created_by => g_created_by,
1290 p_last_update_login => g_last_update_login
1291 );
1292
1293 -- insert into cn_calc_submission_entries
1294 IF l_p_calc_submission_rec.salesrep_option = 'USER_SPECIFY' THEN
1295 FOR ctr IN 1 .. l_salesreps_id_tbl.COUNT LOOP
1296
1297 -- for backward compatibility
1298 if (l_p_calc_submission_rec.entire_hierarchy = 'Y') then
1299 l_hierarchy_flag := 'Y';
1300 else
1301 l_hierarchy_flag := p_salesrep_tbl(ctr).hierarchy_flag;
1302 end if;
1303
1304 cn_calc_sub_entries_pkg.begin_record
1305 ( p_operation => 'INSERT',
1306 p_calc_sub_batch_id => l_calc_sub_batch_id,
1307 p_salesrep_id => l_salesreps_id_tbl(ctr),
1308 p_hierarchy_flag => l_hierarchy_flag,
1309 p_org_id => g_org_id,
1310 p_last_update_date => g_last_update_date,
1311 p_last_updated_by => g_last_updated_by,
1312 p_creation_date => g_creation_date,
1313 p_created_by => g_created_by,
1314 p_last_update_login => g_last_update_login
1315 );
1316 END LOOP;
1317 END IF;
1318
1319 -- insert into cn_calc_sub_quotas
1320 IF l_p_calc_submission_rec.calculation_type = 'BONUS' THEN
1321 FOR ctr IN 1 .. l_bonus_pe_id_tbl.COUNT LOOP
1322 cn_calc_sub_quotas_pkg.begin_record
1323 ( p_operation => 'INSERT',
1324 p_calc_sub_batch_id => l_calc_sub_batch_id,
1325 p_quota_id => l_bonus_pe_id_tbl(ctr),
1326 p_org_id => g_org_id,
1327 p_last_update_date => g_last_update_date,
1328 p_last_updated_by => g_last_updated_by,
1329 p_creation_date => g_creation_date,
1330 p_created_by => g_created_by,
1331 p_last_update_login => g_last_update_login
1332 );
1333 END LOOP;
1334 END IF;
1335
1336 -- only if p_commit is true then submit the calculation
1337 IF FND_API.To_Boolean( p_commit ) THEN
1338 -- initialize apps enviroment for concurrent submission
1339 IF l_p_calc_submission_rec.concurrent_calculation = 'Y' THEN
1340
1344 END IF;
1341 -- we have to do commit first
1342 COMMIT WORK;
1343
1345
1346 cn_proc_batches_pkg.calculation_submission
1347 ( p_calc_sub_batch_id => l_calc_sub_batch_id,
1348 x_process_audit_id => l_process_audit_id,
1349 x_process_status_code => l_process_audit_status
1350 );
1351
1352 l_calc_sub_status := get_calc_sub_batch_status( l_calc_sub_batch_id);
1353
1354 IF l_p_calc_submission_rec.concurrent_calculation = 'Y' THEN
1355 l_unfinished := TRUE;
1356
1357 WHILE l_unfinished LOOP
1358 l_calc_sub_status := get_calc_sub_batch_status( l_calc_sub_batch_id);
1359
1360 IF l_calc_sub_status = 'PROCESSING' THEN
1361 dbms_lock.sleep(180);
1362 ELSE
1363 l_unfinished := FALSE;
1364 END IF;
1365 END LOOP;
1366 END IF;
1367
1368 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1369 THEN
1370 IF l_calc_sub_status = 'FAILED' THEN
1371 FND_MESSAGE.SET_NAME ('CN' , 'ALL_PROCESS_DONE_FAIL_LOG');
1372 fnd_message.set_token( 'AUDIT_ID', To_char(l_process_audit_id) );
1373 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1374 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
1375 'cn.plsql.cn_calc_submission_pub.create_calc_submission.error',
1376 false);
1377 end if;
1378
1379 x_loading_status := 'ALL_PROCESS_DONE_FAIL_LOG';
1380 ELSIF l_calc_sub_status = 'COMPLETE' THEN
1381 FND_MESSAGE.SET_NAME ('CN' , 'ALL_PROCESS_DONE_OK_LOG');
1382 fnd_message.set_token( 'AUDIT_ID', To_char(l_process_audit_id) );
1383 x_loading_status := 'ALL_PROCESS_DONE_OK_LOG';
1384 END IF;
1385
1386 FND_MSG_PUB.Add;
1387 END IF;
1388 END IF; -- p_commit;
1389
1390 -- API return status to success
1391 x_return_status := FND_API.G_RET_STS_SUCCESS;
1392 x_loading_status := 'CN_INSERTED';
1393
1394
1395 -- End of API body.
1396
1397 /* Post processing */
1398 -- dbms_output.put_line('calling post processing API');
1399 if JTF_USR_HKS.Ok_to_Execute( 'CN_CALC_SUBMISSION_PUB',
1400 'CREATE_CALC_SUBMISSION', 'A', 'V' ) then
1401 cn_calc_submission_VUHK.create_calc_submission_post
1402 ( p_api_version => p_api_version,
1403 p_init_msg_list => p_init_msg_list,
1404 p_commit => FND_API.G_FALSE,
1405 p_validation_level => p_validation_level,
1406 x_return_status => x_return_status,
1407 x_msg_count => x_msg_count,
1408 x_msg_data => x_msg_data,
1409 p_calc_submission_rec => l_p_calc_submission_rec,
1410 x_loading_status => x_loading_status
1411 );
1412 if ( x_return_status = FND_API.G_RET_STS_ERROR ) then
1413 RAISE FND_API.G_EXC_ERROR;
1414 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1416 end if;
1417 end if;
1418
1419 if JTF_USR_HKS.Ok_to_Execute( 'CN_CALC_SUBMISSION_PUB',
1420 'CREATE_CALC_SUBMISSION', 'A', 'C' ) then
1421
1422 CN_CALC_SUBMISSION_CUHK.create_calc_submission_post
1423 ( p_api_version => p_api_version,
1424 p_init_msg_list => p_init_msg_list,
1425 p_commit => FND_API.G_FALSE,
1426 p_validation_level => p_validation_level,
1427 x_return_status => x_return_status,
1428 x_msg_count => x_msg_count,
1429 x_msg_data => x_msg_data,
1430 p_calc_submission_rec => l_p_calc_submission_rec,
1431 x_loading_status => x_loading_status
1432 );
1433 if ( x_return_status = FND_API.G_RET_STS_ERROR ) then
1434 RAISE FND_API.G_EXC_ERROR;
1435 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1437 end if;
1438 end if;
1439
1440 -- Following code is for message generation
1441 if JTF_USR_HKS.Ok_to_Execute( 'CN_CALC_SUBMISSION_PUB',
1442 'CREATE_CALC_SUBMISSION', 'M', 'M' ) then
1443
1444 IF ( CN_CALC_SUBMISSION_CUHK.ok_to_generate_msg
1445 ( p_calc_submission_rec => l_p_calc_submission_rec )
1446 ) THEN
1447
1448 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
1449 JTF_USR_HKS.Load_Bind_Data( l_bind_data_id, 'CALC_SUB_BATCH_ID',
1450 l_calc_sub_batch_id, 'S', 'N' );
1451
1452 JTF_USR_HKS.generate_message( p_prod_code => 'CN',
1453 p_bus_obj_code => 'CALC_SUB',
1454 p_bus_obj_name => 'CALC_SUBMISSION',
1455 p_action_code => 'I', -- Insert
1456 p_bind_data_id => l_bind_data_id,
1457 x_return_code => x_return_status
1458 );
1459 if ( x_return_status = FND_API.G_RET_STS_ERROR ) then
1460 RAISE FND_API.G_EXC_ERROR;
1461 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1463 end if;
1464 END IF;
1465 end if;
1466
1467 x_return_status := FND_API.G_RET_STS_SUCCESS;
1468
1469 -- Standard check of p_commit.
1470 IF FND_API.To_Boolean( p_commit ) THEN
1471 COMMIT WORK;
1472 END IF;
1473 -- Standard call to get message count and if count is 1, get message info.
1474 FND_MSG_PUB.Count_And_Get
1475 (
1476 p_count => x_msg_count ,
1477 p_data => x_msg_data ,
1478 p_encoded => FND_API.G_FALSE
1479 );
1480
1481 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1482 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1483 'cn.plsql.cn_calc_submission_pub.create_calc_submission.end',
1484 'End of create_calc_submission.');
1485 end if;
1486
1490 x_return_status := FND_API.G_RET_STS_ERROR ;
1487 EXCEPTION
1488 WHEN FND_API.G_EXC_ERROR THEN
1489 ROLLBACK TO create_calc_submission;
1491 FND_MSG_PUB.Count_And_Get
1492 (
1493 p_count => x_msg_count ,
1494 p_data => x_msg_data ,
1495 p_encoded => FND_API.G_FALSE
1496 );
1497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1498 ROLLBACK TO create_calc_submission;
1499 x_loading_status := 'UNEXPECTED_ERR';
1500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1501 FND_MSG_PUB.Count_And_Get
1502 (
1503 p_count => x_msg_count ,
1504 p_data => x_msg_data ,
1505 p_encoded => FND_API.G_FALSE
1506 );
1507 WHEN OTHERS THEN
1508 ROLLBACK TO create_calc_submission;
1509 x_loading_status := 'UNEXPECTED_ERR';
1510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1511 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1512 THEN
1513 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1514 END IF;
1515 FND_MSG_PUB.Count_And_Get
1516 (
1517 p_count => x_msg_count ,
1518 p_data => x_msg_data ,
1519 p_encoded => FND_API.G_FALSE
1520 );
1521
1522 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1523 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1524 'cn.plsql.cn_calc_submission_pub.create_calc_submission.exception',
1525 sqlerrm);
1526 end if;
1527
1528 END Create_Calc_Submission;
1529
1530 -- Start of Comments
1531 -- API name : Update_Calc_Submission
1532 -- Type : Public.
1533 -- Pre-reqs : None.
1534 -- Usage : Used to update a calculation submission batch with passed_in
1535 -- salesreps/ passed_in bonus plan elements
1536 -- And submit the calculation after all validations are successful
1537 -- Desc : Procedure to update calculation submission batch with passed_in
1538 -- salesreps/ passed_in bonus plan elements
1539 -- Parameters :
1540 -- IN : p_api_version IN NUMBER Require
1541 -- p_init_msg_list IN VARCHAR2 Optional
1542 -- Default = FND_API.G_FALSE
1543 -- p_commit IN VARCHAR2 Optional
1544 -- Default = FND_API.G_FALSE
1545 -- p_validation_level IN NUMBER Optional
1546 -- Default = FND_API.G_VALID_LEVEL_FULL
1547 -- OUT : x_return_status OUT VARCHAR2(1)
1548 -- x_msg_count OUT NUMBER
1549 -- x_msg_data OUT VARCHAR2(2000)
1550 -- IN : p_calc_submission_rec IN calc_submission_rec_type
1551 -- Version : Current version 1.0
1552 -- Initial version 1.0
1553 --
1554 -- Notes
1555 --
1556 -- Description : This procedure is used to update a calculation submission
1557 -- Notes :
1558 --
1559 -- p_calc_submission_rec_old Mandatory
1560 -- old calculation submission batch must be found based
1561 -- on p_calc_submission_rec_old.name
1562 -- p_calc_submission_rec_new Mandatory
1563 -- all the validation rules in create_calc_submission holds here
1564 --
1565 -- p_app_user_resp_rec IN parameter Optional
1566 -- Information required to submit concurrent calculation
1567 -- Valid when concurrent_calculation = 'Y'
1568 -- user_name should be a valid application user name.
1569 -- responsibility_name should be a valid responsibility name
1570 --
1571 -- p_salesrep_tbl IN parameter Optional
1572 -- list of salesreps' employee number /employee type
1573 -- Valid when salesrep_option = 'USER_SPECIFY'
1574 -- salesrep_rec_type.employee number can not be missing or null
1575 -- salesrep_rec_type.type can not be missing or null
1576 -- Sales persons listed currently have or previously had
1577 -- compensation plan assigned.
1578 -- p_salesrep_tbl_action Mandatory
1579 -- Valid Values: ADD/DELETE
1580 -- either add the listed sales persons to table or delete the listed
1581 -- sales persons from the table.
1582 --
1583 -- p_bonus_pe_tbl IN parameter Optional
1584 -- list of bonus plan elements' name
1585 -- Valid when calc_type = BONUS
1586 -- Plan elements listed should be 'BONUS' type and their interval type should
1587 -- match the value of p_calc_submission_rec.interval_type
1588 -- or if p_calc_submission_rec.interval_type = 'ALL', then their interval
1589 -- type can be any of 'PERIOD'/'QUARTER'/'YEAR'
1590 -- p_bonus_pe_tbl_action Mandatory
1591 -- Valid Values: ADD/DELETE
1592 -- either add the listed bonus plan elements to table or delete the listed
1593 -- bonus plan elements from the table.
1594 -- if the plan element already exists or there are duplicates in p_bonus_pe_tbl,
1595 -- give out a message without failing the call
1596 --
1597 -- Special Notes:
1598 -- IF p_commit is not fnd_api.g_true, then the calculation will not be submitted even if all
1599 -- the validations are successful.
1600 --
1601 --
1602 -- End of comments
1603 ------------------------+
1604 -- End of comments
1605
1606 PROCEDURE Update_Calc_Submission
1607 (
1608 p_api_version IN NUMBER,
1609 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1610 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1611 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1615 p_calc_submission_rec_old IN calc_submission_rec_type := g_miss_calc_submission_rec,
1612 x_return_status OUT NOCOPY VARCHAR2,
1613 x_msg_count OUT NOCOPY NUMBER,
1614 x_msg_data OUT NOCOPY VARCHAR2,
1616 p_calc_submission_rec_new IN calc_submission_rec_type := g_miss_calc_submission_rec,
1617 p_app_user_resp_rec IN app_user_resp_rec_type := g_miss_app_user_resp_rec,
1618 p_salesrep_tbl IN salesrep_tbl_type := g_miss_salesrep_tbl,
1619 p_salesrep_tbl_action IN VARCHAR2,
1620 p_bonus_pe_tbl IN plan_element_tbl_type := g_miss_pe_tbl,
1621 p_bonus_pe_tbl_action IN VARCHAR2,
1622 x_loading_status OUT NOCOPY VARCHAR2
1623 ) IS
1624
1625 l_api_name CONSTANT VARCHAR2(30) := 'Update_Calc_Submission';
1626 l_api_version CONSTANT NUMBER := 1.0;
1627
1628 l_calc_sub_batch_id NUMBER;
1629 l_interval_type_id NUMBER;
1630 l_hierarchy_flag VARCHAR2(1);
1631
1632 l_p_calc_submission_rec calc_submission_rec_type;
1633 l_OAI_array JTF_USR_HKS.OAI_data_array_type;
1634
1635 l_salesreps_id_tbl salesrep_id_tbl_type;
1636 l_bonus_pe_id_tbl plan_element_id_tbl_type;
1637 l_user_id NUMBER;
1638 l_responsibility_id NUMBER;
1639 l_unfinished BOOLEAN := TRUE;
1640
1641 l_calc_sub_status cn_calc_submission_batches.status%TYPE;
1642 l_process_audit_id NUMBER;
1643 l_process_audit_status VARCHAR2(30);
1644 l_status VARCHAR2(30);
1645
1646 CURSOR l_calc_sub_batch_csr( l_name cn_calc_submission_batches.name%TYPE)
1647 IS
1648 SELECT *
1649 FROM cn_calc_submission_batches_all
1650 WHERE name = l_name
1651 AND org_id = g_org_id;
1652
1653 l_calc_sub_batch_rec l_calc_sub_batch_csr%ROWTYPE;
1654
1655 CURSOR l_bonus_pe_exists_csr ( l_calc_sub_batch_id NUMBER,
1656 l_quota_id NUMBER )
1657 IS
1658 SELECT COUNT(*)
1659 FROM cn_calc_sub_quotas_all
1660 WHERE calc_sub_batch_id = l_calc_sub_batch_id
1661 AND quota_id = l_quota_id;
1662
1663 CURSOR l_salesrep_exists_csr ( l_calc_sub_batch_id NUMBER,
1664 l_salesrep_id NUMBER )
1665 IS
1666 SELECT COUNT(*)
1667 FROM cn_calc_submission_entries_all
1668 WHERE calc_sub_batch_id = l_calc_sub_batch_id
1669 AND salesrep_id = l_salesrep_id;
1670
1671 l_counter NUMBER;
1672 l_name_validate_flag VARCHAR2(1);
1673
1674 l_bind_data_id NUMBER;
1675
1676 BEGIN
1677 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1678 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1679 'cn.plsql.cn_calc_submission_pub.update_calc_submission.begin',
1680 'Beginning of update_calc_submission ...');
1681 end if;
1682
1683 -- Standard Start of API savepoint
1684
1685 SAVEPOINT update_calc_submission;
1686
1687 -- Standard call to check for call compatibility.
1688
1689 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1690 p_api_version ,
1691 l_api_name ,
1692 G_PKG_NAME )
1693 THEN
1694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1695 END IF;
1696
1697 -- Initialize message list if p_init_msg_list is set to TRUE.
1698 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1699 FND_MSG_PUB.initialize;
1700 END IF;
1701
1702 -- Initialize API return status to success
1703 x_return_status := FND_API.G_RET_STS_SUCCESS;
1704 x_loading_status := 'CN_UPDATED';
1705
1706 -- continue to validate user_name/responsibility name
1707 IF l_p_calc_submission_rec.concurrent_calculation = 'Y' THEN
1708 validate_app_user_resp( x_return_status => x_return_status,
1709 x_msg_count => x_msg_count,
1710 x_msg_data => x_msg_data,
1711 p_app_user_resp_rec => p_app_user_resp_rec,
1712 p_loading_status => x_loading_status,
1713 x_user_id => l_user_id,
1714 x_responsibility_id => l_responsibility_id,
1715 x_loading_status => x_loading_status
1716 );
1717
1718 IF x_return_status <> FND_API.g_ret_sts_success THEN
1719 RAISE FND_API.G_EXC_ERROR ;
1720 END IF;
1721 END IF;
1722
1723 fnd_global.apps_initialize ( user_id => l_user_id,
1724 resp_id => l_responsibility_id,
1725 resp_appl_id => 283
1726 );
1727
1728 g_org_id := p_calc_submission_rec_old.org_id;
1729 mo_global.validate_orgid_pub_api(org_id => g_org_id,
1730 status => l_status);
1731
1732 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1733 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1734 'cn.plsql.cn_calc_submission_pub.update_calc_submission.org_validate',
1735 'Validated org_id = ' || g_org_id || ' status = '||l_status);
1736 end if;
1737
1738 if (nvl(p_calc_submission_rec_new.org_id, g_org_id) <> nvl(p_calc_submission_rec_old.org_id, g_org_id)) then
1739 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
1740 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1741 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
1742 'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
1743 true);
1744 end if;
1745
1746 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1747 FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
1748 FND_MSG_PUB.Add;
1752 end if;
1749 END IF;
1750
1751 RAISE FND_API.G_EXC_ERROR ;
1753
1754 --+
1755 -- Start of API body
1756 --+
1757 l_p_calc_submission_rec := p_calc_submission_rec_new;
1758 l_p_calc_submission_rec.org_id := g_org_id;
1759
1760 --dbms_output.put_line('Going into pre processing ');
1761 if JTF_USR_HKS.Ok_to_Execute( 'CN_CALC_SUBMISSION_PUB',
1762 'CREATE_CALC_SUBMISSION', 'B', 'C' ) then
1763
1764 CN_CALC_SUBMISSION_CUHK.create_calc_submission_pre
1765 ( p_api_version => p_api_version,
1766 p_init_msg_list => p_init_msg_list,
1767 p_commit => FND_API.G_FALSE,
1768 p_validation_level => p_validation_level,
1769 x_return_status => x_return_status,
1770 x_msg_count => x_msg_count,
1771 x_msg_data => x_msg_data,
1772 p_calc_submission_rec => l_p_calc_submission_rec,
1773 x_loading_status => x_loading_status
1774 );
1775 if ( x_return_status = FND_API.G_RET_STS_ERROR ) then
1776 RAISE FND_API.G_EXC_ERROR;
1777 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1779 end if;
1780 end if;
1781
1782 if JTF_USR_HKS.Ok_to_Execute( 'CN_CALC_SUBMISSION_PUB',
1783 'CREATE_CALC_SUBMISSION', 'B', 'V' ) then
1784 cn_calc_submission_VUHK.create_calc_submission_pre
1785 ( p_api_version => p_api_version,
1786 p_init_msg_list => p_init_msg_list,
1787 p_commit => FND_API.G_FALSE,
1788 p_validation_level => p_validation_level,
1789 x_return_status => x_return_status,
1790 x_msg_count => x_msg_count,
1791 x_msg_data => x_msg_data,
1792 p_calc_submission_rec => l_p_calc_submission_rec,
1793 x_loading_status => x_loading_status
1794 );
1795 if ( x_return_status = FND_API.G_RET_STS_ERROR ) then
1796 RAISE FND_API.G_EXC_ERROR;
1797 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1799 end if;
1800 END IF;
1801
1802
1803 -- validate the p_calc_submission_rec_old exists
1804 -- old batch_name can not be missing or null
1805 -- and should uniquely identify the batch
1806 IF (cn_api.chk_miss_null_char_para
1807 (p_char_para => p_calc_submission_rec_old.batch_name,
1808 p_obj_name => g_calc_sub_name,
1809 p_loading_status => x_loading_status,
1810 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
1811 RAISE FND_API.G_EXC_ERROR ;
1812 END IF;
1813
1814 OPEN l_calc_sub_batch_csr( p_calc_submission_rec_old.batch_name);
1815 FETCH l_calc_sub_batch_csr INTO l_calc_sub_batch_rec;
1816
1817 IF l_calc_sub_batch_csr%notfound THEN
1818 CLOSE l_calc_sub_batch_csr;
1819 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_NOT_EXIST');
1820 fnd_message.set_token ('BATCH_NAME', p_calc_submission_rec_old.batch_name );
1821 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1822 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
1823 'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
1824 true);
1825 end if;
1826
1827 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1828 THEN
1829 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_NOT_EXIST');
1830 fnd_message.set_token ('BATCH_NAME', p_calc_submission_rec_old.batch_name );
1831 FND_MSG_PUB.Add;
1832 END IF;
1833
1834 x_loading_status := 'CN_CALC_SUB_NOT_EXIST';
1835 RAISE FND_API.G_EXC_ERROR ;
1836 END IF;
1837
1838 CLOSE l_calc_sub_batch_csr;
1839
1840 IF l_calc_sub_batch_rec.status = 'COMPLETE' OR l_calc_sub_batch_rec.status = 'PROCESSING' THEN
1841 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_NOT_UPDATEABLE');
1842 fnd_message.set_token ('BATCH_NAME', p_calc_submission_rec_old.batch_name );
1843 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1844 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
1845 'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
1846 true);
1847 end if;
1848
1849 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1850 THEN
1851 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_NOT_UPDATEABLE');
1852 fnd_message.set_token ('BATCH_NAME', p_calc_submission_rec_old.batch_name );
1853 FND_MSG_PUB.Add;
1854 END IF;
1855
1856 x_loading_status := 'CN_CALC_SUB_NOT_UPDATEABLE';
1857 RAISE FND_API.G_EXC_ERROR ;
1858 END IF;
1859
1860 -- handling g_miss_* in l_p_calc_submission_rec_new
1861 SELECT Decode( l_p_calc_submission_rec.batch_name, fnd_api.g_miss_char,
1862 l_calc_sub_batch_rec.name, l_p_calc_submission_rec.batch_name )
1863 INTO l_p_calc_submission_rec.batch_name
1864 FROM dual;
1865
1866 IF l_p_calc_submission_rec.batch_name = p_calc_submission_rec_old.batch_name THEN
1867 l_name_validate_flag := 'N';
1868 ELSE
1869 l_name_validate_flag := 'Y';
1870 END IF;
1871
1872 SELECT Decode( l_p_calc_submission_rec.start_date, fnd_api.g_miss_date,
1873 l_calc_sub_batch_rec.start_date, l_p_calc_submission_rec.start_date )
1874 INTO l_p_calc_submission_rec.start_date
1875 FROM dual;
1876
1877 SELECT Decode( l_p_calc_submission_rec.end_date, fnd_api.g_miss_date,
1878 l_calc_sub_batch_rec.end_date, l_p_calc_submission_rec.end_date )
1879 INTO l_p_calc_submission_rec.end_date
1880 FROM dual;
1881
1885 FROM dual;
1882 SELECT Decode( l_p_calc_submission_rec.calculation_type, fnd_api.g_miss_char,
1883 l_calc_sub_batch_rec.calc_type, l_p_calc_submission_rec.calculation_type )
1884 INTO l_p_calc_submission_rec.calculation_type
1886
1887 SELECT Decode( l_p_calc_submission_rec.salesrep_option, fnd_api.g_miss_char,
1888 l_calc_sub_batch_rec.salesrep_option, l_p_calc_submission_rec.salesrep_option )
1889 INTO l_p_calc_submission_rec.salesrep_option
1890 FROM dual;
1891
1892 SELECT Decode( l_p_calc_submission_rec.entire_hierarchy, fnd_api.g_miss_char,
1893 l_calc_sub_batch_rec.hierarchy_flag, l_p_calc_submission_rec.entire_hierarchy )
1894 INTO l_p_calc_submission_rec.entire_hierarchy
1895 FROM dual;
1896
1897 SELECT Decode( l_p_calc_submission_rec.concurrent_calculation, fnd_api.g_miss_char,
1898 l_calc_sub_batch_rec.concurrent_flag, l_p_calc_submission_rec.concurrent_calculation )
1899 INTO l_p_calc_submission_rec.concurrent_calculation
1900 FROM dual;
1901
1902 SELECT Decode( l_p_calc_submission_rec.incremental_calculation, fnd_api.g_miss_char,
1903 l_calc_sub_batch_rec.intelligent_flag, l_p_calc_submission_rec.incremental_calculation )
1904 INTO l_p_calc_submission_rec.incremental_calculation
1905 FROM dual;
1906
1907 SELECT Decode( l_p_calc_submission_rec.interval_type, fnd_api.g_miss_char,
1908 Decode( l_calc_sub_batch_rec.interval_type_id, NULL, NULL, -1000, 'PERIOD',
1909 -1001, 'QUARTER', -1002, 'YEAR', -1003 , 'ALL' ),
1910 l_p_calc_submission_rec.interval_type )
1911 INTO l_p_calc_submission_rec.interval_type
1912 FROM dual;
1913
1914 SELECT Decode( l_p_calc_submission_rec.attribute_category, fnd_api.g_miss_char,
1915 l_calc_sub_batch_rec.attribute_category, l_p_calc_submission_rec.attribute_category )
1916 INTO l_p_calc_submission_rec.attribute_category
1917 FROM dual;
1918
1919 SELECT Decode( l_p_calc_submission_rec.attribute1, fnd_api.g_miss_char,
1920 l_calc_sub_batch_rec.attribute1, l_p_calc_submission_rec.attribute1 )
1921 INTO l_p_calc_submission_rec.attribute1
1922 FROM dual;
1923
1924 SELECT Decode( l_p_calc_submission_rec.attribute2, fnd_api.g_miss_char,
1925 l_calc_sub_batch_rec.attribute2, l_p_calc_submission_rec.attribute2 )
1926 INTO l_p_calc_submission_rec.attribute2
1927 FROM dual;
1928
1929 SELECT Decode( l_p_calc_submission_rec.attribute3, fnd_api.g_miss_char,
1930 l_calc_sub_batch_rec.attribute3, l_p_calc_submission_rec.attribute3 )
1931 INTO l_p_calc_submission_rec.attribute3
1932 FROM dual;
1933
1934 SELECT Decode( l_p_calc_submission_rec.attribute4, fnd_api.g_miss_char,
1935 l_calc_sub_batch_rec.attribute4, l_p_calc_submission_rec.attribute4 )
1936 INTO l_p_calc_submission_rec.attribute4
1937 FROM dual;
1938
1939 SELECT Decode( l_p_calc_submission_rec.attribute5, fnd_api.g_miss_char,
1940 l_calc_sub_batch_rec.attribute5, l_p_calc_submission_rec.attribute5 )
1941 INTO l_p_calc_submission_rec.attribute5
1942 FROM dual;
1943
1944 SELECT Decode( l_p_calc_submission_rec.attribute6, fnd_api.g_miss_char,
1945 l_calc_sub_batch_rec.attribute6, l_p_calc_submission_rec.attribute6 )
1946 INTO l_p_calc_submission_rec.attribute6
1947 FROM dual;
1948
1949 SELECT Decode( l_p_calc_submission_rec.attribute7, fnd_api.g_miss_char,
1950 l_calc_sub_batch_rec.attribute7, l_p_calc_submission_rec.attribute7 )
1951 INTO l_p_calc_submission_rec.attribute7
1952 FROM dual;
1953
1954 SELECT Decode( l_p_calc_submission_rec.attribute8, fnd_api.g_miss_char,
1955 l_calc_sub_batch_rec.attribute8, l_p_calc_submission_rec.attribute8 )
1956 INTO l_p_calc_submission_rec.attribute8
1957 FROM dual;
1958
1959 SELECT Decode( l_p_calc_submission_rec.attribute9, fnd_api.g_miss_char,
1960 l_calc_sub_batch_rec.attribute9, l_p_calc_submission_rec.attribute9 )
1961 INTO l_p_calc_submission_rec.attribute9
1962 FROM dual;
1963
1964 SELECT Decode( l_p_calc_submission_rec.attribute10, fnd_api.g_miss_char,
1965 l_calc_sub_batch_rec.attribute10, l_p_calc_submission_rec.attribute10 )
1966 INTO l_p_calc_submission_rec.attribute10
1967 FROM dual;
1968
1969 SELECT Decode( l_p_calc_submission_rec.attribute11, fnd_api.g_miss_char,
1970 l_calc_sub_batch_rec.attribute11, l_p_calc_submission_rec.attribute11 )
1971 INTO l_p_calc_submission_rec.attribute11
1972 FROM dual;
1973
1974 SELECT Decode( l_p_calc_submission_rec.attribute12, fnd_api.g_miss_char,
1975 l_calc_sub_batch_rec.attribute12, l_p_calc_submission_rec.attribute12 )
1976 INTO l_p_calc_submission_rec.attribute12
1977 FROM dual;
1978
1979 SELECT Decode( l_p_calc_submission_rec.attribute13, fnd_api.g_miss_char,
1980 l_calc_sub_batch_rec.attribute13, l_p_calc_submission_rec.attribute13 )
1981 INTO l_p_calc_submission_rec.attribute13
1982 FROM dual;
1983
1984 SELECT Decode( l_p_calc_submission_rec.attribute14, fnd_api.g_miss_char,
1985 l_calc_sub_batch_rec.attribute14, l_p_calc_submission_rec.attribute14 )
1986 INTO l_p_calc_submission_rec.attribute14
1987 FROM dual;
1988
1989 SELECT Decode( l_p_calc_submission_rec.attribute15, fnd_api.g_miss_char,
1990 l_calc_sub_batch_rec.attribute15, l_p_calc_submission_rec.attribute1 )
1991 INTO l_p_calc_submission_rec.attribute15
1992 FROM dual;
1993
1994 --+
1995 -- Validate calculation submission batch level
1996 --+
1997 validate_calc_sub_batch( x_return_status => x_return_status,
1998 x_msg_count => x_msg_count,
1999 x_msg_data => x_msg_data,
2000 p_calc_submission_rec => l_p_calc_submission_rec,
2001 p_loading_status => x_loading_status,
2002 p_name_validate_flag => l_name_validate_flag,
2003 x_loading_status => x_loading_status
2004 );
2005
2009
2006 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
2007 RAISE FND_API.G_EXC_ERROR ;
2008 END IF;
2010 -- continue to validate salesrep_entries
2011 IF l_p_calc_submission_rec.salesrep_option = 'USER_SPECIFY' THEN
2012 IF p_salesrep_tbl.COUNT > 0 THEN
2013 validate_salesrep_entries( x_return_status => x_return_status,
2014 x_msg_count => x_msg_count,
2015 x_msg_data => x_msg_data,
2016 p_salesrep_tbl => p_salesrep_tbl,
2017 p_loading_status => x_loading_status,
2018 x_salesreps_id_tbl => l_salesreps_id_tbl,
2019 x_loading_status => x_loading_status
2020 );
2021 END IF;
2022
2023 IF x_return_status <> FND_API.g_ret_sts_success THEN
2024 RAISE FND_API.G_EXC_ERROR ;
2025 END IF;
2026 END IF;
2027
2028 -- continue to validate bonus plan elements
2029 IF l_p_calc_submission_rec.calculation_type = 'BONUS' THEN
2030 validate_bonus_pe_entries ( x_return_status => x_return_status,
2031 x_msg_count => x_msg_count,
2032 x_msg_data => x_msg_data,
2033 p_bonus_pe_tbl => p_bonus_pe_tbl,
2034 p_interval_type => l_p_calc_submission_rec.interval_type,
2035 p_loading_status => x_loading_status,
2036 x_bonus_pe_id_tbl => l_bonus_pe_id_tbl,
2037 x_loading_status => x_loading_status
2038 );
2039
2040 IF x_return_status <> FND_API.g_ret_sts_success THEN
2041 RAISE FND_API.G_EXC_ERROR ;
2042 END IF;
2043 END IF;
2044
2045
2046 -- IF program reaches here, all validations are successful.
2047 -- start to create calc_submission_batch
2048 l_calc_sub_batch_id := l_calc_sub_batch_rec.calc_sub_batch_id;
2049
2050 IF l_p_calc_submission_rec.calculation_type = 'BONUS' THEN
2051 --clku, bug 3428365
2052 /*SELECT interval_type_id INTO l_interval_type_id
2053 FROM cn_interval_types
2054 WHERE name = l_p_calc_submission_rec.interval_type;*/
2055
2056 IF l_p_calc_submission_rec.interval_type = 'PERIOD' THEN
2057 l_interval_type_id := -1000;
2058 END IF;
2059
2060 IF l_p_calc_submission_rec.interval_type = 'QUARTER' THEN
2061 l_interval_type_id := -1001;
2062 END IF;
2063
2064 IF l_p_calc_submission_rec.interval_type = 'YEAR' THEN
2065 l_interval_type_id := -1002;
2066 END IF;
2067
2068 IF l_p_calc_submission_rec.interval_type = 'ALL' THEN
2069 l_interval_type_id := -1003;
2070 END IF;
2071 END IF;
2072
2073 -- update cn_calc_submission_batches
2074 cn_calc_sub_batches_pkg.begin_record
2075 ( p_operation => 'UPDATE',
2076 p_calc_sub_batch_id => l_calc_sub_batch_id,
2077 p_name => l_p_calc_submission_rec.batch_name,
2078 p_start_date => l_p_calc_submission_rec.start_date,
2079 p_end_date => l_p_calc_submission_rec.end_date,
2080 p_intelligent_flag => l_p_calc_submission_rec.incremental_calculation,
2081 p_hierarchy_flag => l_p_calc_submission_rec.entire_hierarchy,
2082 p_salesrep_option => l_p_calc_submission_rec.salesrep_option,
2083 p_concurrent_flag => l_p_calc_submission_rec.concurrent_calculation,
2084 p_status => l_calc_sub_batch_rec.status,
2085 p_interval_type_id => l_interval_type_id,
2086 p_org_id => g_org_id,
2087 p_calc_type => l_p_calc_submission_rec.calculation_type,
2088 p_attribute_category => l_p_calc_submission_rec.attribute_category,
2089 p_attribute1 => l_p_calc_submission_rec.attribute1,
2090 p_attribute2 => l_p_calc_submission_rec.attribute2,
2091 p_attribute3 => l_p_calc_submission_rec.attribute3,
2092 p_attribute4 => l_p_calc_submission_rec.attribute4,
2093 p_attribute5 => l_p_calc_submission_rec.attribute5,
2094 p_attribute6 => l_p_calc_submission_rec.attribute6,
2095 p_attribute7 => l_p_calc_submission_rec.attribute7,
2096 p_attribute8 => l_p_calc_submission_rec.attribute8,
2097 p_attribute9 => l_p_calc_submission_rec.attribute9,
2098 p_attribute10 => l_p_calc_submission_rec.attribute10,
2099 p_attribute11 => l_p_calc_submission_rec.attribute11,
2100 p_attribute12 => l_p_calc_submission_rec.attribute12,
2101 p_attribute13 => l_p_calc_submission_rec.attribute13,
2102 p_attribute14 => l_p_calc_submission_rec.attribute14,
2103 p_attribute15 => l_p_calc_submission_rec.attribute15,
2104 p_last_update_date => g_last_update_date,
2105 p_last_updated_by => g_last_updated_by,
2106 p_creation_date => g_creation_date,
2107 p_created_by => g_created_by,
2108 p_last_update_login => g_last_update_login
2109 );
2110
2111 -- ADD/DELETE cn_calc_submission_entries
2112 IF l_p_calc_submission_rec.salesrep_option = 'USER_SPECIFY' THEN
2113 IF p_salesrep_tbl_action = 'ADD' THEN
2114 FOR ctr IN 1 .. l_salesreps_id_tbl.COUNT LOOP
2115 OPEN l_salesrep_exists_csr( l_calc_sub_batch_id,
2116 l_salesreps_id_tbl(ctr) );
2117 FETCH l_salesrep_exists_csr INTO l_counter;
2118 CLOSE l_salesrep_exists_csr;
2119
2120 IF l_counter = 0 THEN
2121 -- for backward compatibility
2122 if (l_p_calc_submission_rec.entire_hierarchy = 'Y') then
2123 l_hierarchy_flag := 'Y';
2124 else
2125 l_hierarchy_flag := p_salesrep_tbl(ctr).hierarchy_flag;
2126 end if;
2127
2128 cn_calc_sub_entries_pkg.begin_record
2129 ( p_operation => 'INSERT',
2130 p_calc_sub_batch_id => l_calc_sub_batch_id,
2131 p_salesrep_id => l_salesreps_id_tbl(ctr),
2135 p_last_updated_by => g_last_updated_by,
2132 p_hierarchy_flag => l_hierarchy_flag,
2133 p_org_id => g_org_id,
2134 p_last_update_date => g_last_update_date,
2136 p_creation_date => g_creation_date,
2137 p_created_by => g_created_by,
2138 p_last_update_login => g_last_update_login
2139 );
2140 ELSE
2141 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SALESREP_EXISTS');
2142 FND_MESSAGE.SET_TOKEN('EMPLOYEE_NUMBER', p_salesrep_tbl(ctr).employee_number );
2143 FND_MESSAGE.SET_TOKEN('EMPLOYEE_TYPE', p_salesrep_tbl(ctr).TYPE );
2144 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2145 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
2146 'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
2147 true);
2148 end if;
2149
2150 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2151 THEN
2152 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SALESREP_EXISTS');
2153 FND_MESSAGE.SET_TOKEN('EMPLOYEE_NUMBER', p_salesrep_tbl(ctr).employee_number );
2154 FND_MESSAGE.SET_TOKEN('EMPLOYEE_TYPE', p_salesrep_tbl(ctr).TYPE );
2155 FND_MSG_PUB.Add;
2156 END IF;
2157 END IF;
2158 END LOOP;
2159 ELSIF p_salesrep_tbl_action = 'DELETE' THEN
2160 forall j IN 1 .. l_salesreps_id_tbl.COUNT
2161 DELETE cn_calc_submission_entries_all
2162 WHERE calc_sub_batch_id = l_calc_sub_batch_id
2163 AND salesrep_id = l_salesreps_id_tbl(j);
2164
2165 DECLARE
2166 CURSOR l_salesrep_count_csr IS
2167 SELECT 1
2168 FROM cn_calc_submission_entries_all
2169 WHERE calc_sub_batch_id = l_calc_sub_batch_id;
2170 dummy NUMBER;
2171 BEGIN
2172 OPEN l_salesrep_count_csr;
2173 FETCH l_salesrep_count_csr INTO dummy;
2174
2175 IF l_salesrep_count_csr%notfound THEN
2176 CLOSE l_salesrep_count_csr;
2177
2178 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_NO_SALESREP');
2179 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2180 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
2181 'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
2182 true);
2183 end if;
2184
2185 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2186 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_NO_SALESREP');
2187 FND_MSG_PUB.Add;
2188 END IF;
2189 x_loading_status := 'CN_CALC_NO_SALESREP';
2190 RAISE FND_API.g_exc_error;
2191 ELSE
2192 CLOSE l_salesrep_count_csr;
2193 END IF;
2194 END ;
2195 END IF;
2196 END IF;
2197
2198 -- ADD/DELETE into cn_calc_sub_quotas
2199 IF l_p_calc_submission_rec.calculation_type = 'BONUS' THEN
2200 IF p_bonus_pe_tbl_action = 'ADD' THEN
2201 FOR ctr IN 1 .. l_bonus_pe_id_tbl.COUNT LOOP
2202 OPEN l_bonus_pe_exists_csr( l_calc_sub_batch_id,
2203 l_bonus_pe_id_tbl(ctr) );
2204 FETCH l_bonus_pe_exists_csr INTO l_counter;
2205 CLOSE l_bonus_pe_exists_csr;
2206
2207 IF l_counter = 0 THEN
2208 cn_calc_sub_quotas_pkg.begin_record
2209 ( p_operation => 'INSERT',
2210 p_calc_sub_batch_id => l_calc_sub_batch_id,
2211 p_quota_id => l_bonus_pe_id_tbl(ctr),
2212 p_org_id => g_org_id,
2213 p_last_update_date => g_last_update_date,
2214 p_last_updated_by => g_last_updated_by,
2215 p_creation_date => g_creation_date,
2216 p_created_by => g_created_by,
2217 p_last_update_login => g_last_update_login
2218 );
2219 ELSE
2220 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_QUOTA_EXISTS');
2221 fnd_message.set_token( 'QUOTA_NAME', p_bonus_pe_tbl(ctr) );
2222 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2223 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
2224 'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
2225 true);
2226 end if;
2227
2228 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2229 THEN
2230 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_QUOTA_EXISTS');
2231 fnd_message.set_token( 'QUOTA_NAME', p_bonus_pe_tbl(ctr) );
2232 FND_MSG_PUB.Add;
2233 END IF;
2234 END IF;
2235 END LOOP;
2236 ELSIF p_bonus_pe_tbl_action = 'DELETE' THEN
2237 forall j IN 1 .. l_bonus_pe_id_tbl.COUNT
2238 DELETE cn_calc_sub_quotas
2239 WHERE calc_sub_batch_id = l_calc_sub_batch_id
2240 AND quota_id = l_bonus_pe_id_tbl(j);
2241 END IF;
2242 END IF;
2243
2244 -- only if p_commit is true then submit the calculation
2245 IF FND_API.To_Boolean( p_commit ) THEN
2246 -- initialize apps enviroment for concurrent submission
2247 IF l_p_calc_submission_rec.concurrent_calculation = 'Y' THEN
2248
2249 -- we have to do commit first
2250 COMMIT WORK;
2251
2252 END IF;
2253
2254 cn_proc_batches_pkg.calculation_submission
2255 ( p_calc_sub_batch_id => l_calc_sub_batch_id,
2256 x_process_audit_id => l_process_audit_id,
2257 x_process_status_code => l_process_audit_status
2258 );
2259
2260 l_calc_sub_status := get_calc_sub_batch_status( l_calc_sub_batch_id);
2261
2262 IF l_p_calc_submission_rec.concurrent_calculation = 'Y' THEN
2263 l_unfinished := TRUE;
2264
2265 WHILE l_unfinished LOOP
2266 l_calc_sub_status := get_calc_sub_batch_status( l_calc_sub_batch_id);
2267
2268 IF l_calc_sub_status = 'PROCESSING' THEN
2269 dbms_lock.sleep(180);
2270 ELSE
2271 l_unfinished := FALSE;
2272 END IF;
2273 END LOOP;
2274 END IF;
2275
2279 FND_MESSAGE.SET_NAME ('CN' , 'ALL_PROCESS_DONE_FAIL_LOG');
2276 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2277 THEN
2278 IF l_calc_sub_status = 'FAILED' THEN
2280 fnd_message.set_token( 'AUDIT_ID', To_char(l_process_audit_id) );
2281 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2282 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
2283 'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
2284 false);
2285 end if;
2286
2287 x_loading_status := 'ALL_PROCESS_DONE_FAIL_LOG';
2288 ELSIF l_calc_sub_status = 'COMPLETE' THEN
2289 FND_MESSAGE.SET_NAME ('CN' , 'ALL_PROCESS_DONE_OK_LOG');
2290 fnd_message.set_token( 'AUDIT_ID', To_char(l_process_audit_id) );
2291 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2292 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
2293 'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
2294 false);
2295 end if;
2296
2297 x_loading_status := 'ALL_PROCESS_DONE_OK_LOG';
2298 END IF;
2299
2300 FND_MSG_PUB.Add;
2301 END IF;
2302 END IF; -- p_commit;
2303
2304 -- Initialize API return status to success
2305 x_return_status := FND_API.G_RET_STS_SUCCESS;
2306 x_loading_status := 'CN_UPDATED';
2307
2308 --+
2309 -- End of API body.
2310
2311 /* Post processing */
2312 --dbms_output.put_line('calling post processing API x_loading_status is ' || x_loading_status);
2313 if JTF_USR_HKS.Ok_to_Execute( 'CN_CALC_SUBMISSION_PUB',
2314 'CREATE_CALC_SUBMISSION', 'A', 'V' ) then
2315 cn_calc_submission_VUHK.create_calc_submission_post
2316 ( p_api_version => p_api_version,
2317 p_init_msg_list => p_init_msg_list,
2318 p_commit => FND_API.G_FALSE,
2319 p_validation_level => p_validation_level,
2320 x_return_status => x_return_status,
2321 x_msg_count => x_msg_count,
2322 x_msg_data => x_msg_data,
2323 p_calc_submission_rec => l_p_calc_submission_rec,
2324 x_loading_status => x_loading_status
2325 );
2326 if ( x_return_status = FND_API.G_RET_STS_ERROR ) then
2327 RAISE FND_API.G_EXC_ERROR;
2328 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
2329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2330 end if;
2331 end if;
2332
2333 --dbms_output.put_line('vertical post processing API x_loading_status is ' || x_loading_status);
2334
2335 if JTF_USR_HKS.Ok_to_Execute( 'CN_CALC_SUBMISSION_PUB',
2336 'CREATE_CALC_SUBMISSION', 'A', 'C' ) then
2337
2338 CN_CALC_SUBMISSION_CUHK.create_calc_submission_post
2339 ( p_api_version => p_api_version,
2340 p_init_msg_list => p_init_msg_list,
2341 p_commit => FND_API.G_FALSE,
2342 p_validation_level => p_validation_level,
2343 x_return_status => x_return_status,
2344 x_msg_count => x_msg_count,
2345 x_msg_data => x_msg_data,
2346 p_calc_submission_rec => l_p_calc_submission_rec,
2347 x_loading_status => x_loading_status
2348 );
2349 if ( x_return_status = FND_API.G_RET_STS_ERROR ) then
2350 RAISE FND_API.G_EXC_ERROR;
2351 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
2352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2353 end if;
2354 end if;
2355
2356 -- Following code is for message generation
2357 if JTF_USR_HKS.Ok_to_Execute( 'CN_CALC_SUBMISSION_PUB',
2358 'CREATE_CALC_SUBMISSION', 'M', 'M' ) then
2359
2360 IF ( CN_CALC_SUBMISSION_CUHK.ok_to_generate_msg
2361 ( p_calc_submission_rec => l_p_calc_submission_rec )
2362 ) THEN
2363
2364 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
2365 JTF_USR_HKS.Load_Bind_Data( l_bind_data_id, 'CALC_SUB_BATCH_ID',
2366 l_calc_sub_batch_id, 'S', 'N' );
2367
2368 JTF_USR_HKS.generate_message( p_prod_code => 'CN',
2369 p_bus_obj_code => 'CALC_SUB',
2370 p_bus_obj_name => 'CALC_SUBMISSION',
2371 p_action_code => 'U', -- update
2372 p_bind_data_id => l_bind_data_id,
2373 x_return_code => x_return_status
2374 );
2375
2376 if ( x_return_status = FND_API.G_RET_STS_ERROR ) then
2377 RAISE FND_API.G_EXC_ERROR;
2378 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
2379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2380 end if;
2381 END IF;
2382 end if;
2383
2384 x_return_status := FND_API.G_RET_STS_SUCCESS;
2385
2386 -- Standard check of p_commit.
2387 -- +
2388 IF FND_API.To_Boolean( p_commit ) THEN
2389 COMMIT WORK;
2390 END IF;
2391
2392 --+
2393 -- Standard call to get message count and if count is 1, get message info.
2394 --+
2395 FND_MSG_PUB.Count_And_Get
2396 (
2397 p_count => x_msg_count ,
2398 p_data => x_msg_data ,
2399 p_encoded => FND_API.G_FALSE
2400 );
2401
2402 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2403 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2404 'cn.plsql.cn_calc_submission_pub.update_calc_submission.end',
2405 'End of update_calc_submission.');
2406 end if;
2407
2408 EXCEPTION
2409 WHEN FND_API.G_EXC_ERROR THEN
2410 ROLLBACK TO update_calc_submission;
2411 x_return_status := FND_API.G_RET_STS_ERROR ;
2412 FND_MSG_PUB.Count_And_Get
2413 (
2414 p_count => x_msg_count ,
2415 p_data => x_msg_data ,
2416 p_encoded => FND_API.G_FALSE
2417 );
2418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2419 ROLLBACK TO update_calc_submission;
2420 x_loading_status := 'UNEXPECTED_ERR';
2421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2422 FND_MSG_PUB.Count_And_Get
2423 (
2424 p_count => x_msg_count ,
2425 p_data => x_msg_data ,
2426 p_encoded => FND_API.G_FALSE
2427 );
2428 WHEN OTHERS THEN
2429 ROLLBACK TO update_calc_submission;
2430 x_loading_status := 'UNEXPECTED_ERR';
2431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2432 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2433 THEN
2434 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2435 END IF;
2436 FND_MSG_PUB.Count_And_Get
2437 (
2438 p_count => x_msg_count ,
2439 p_data => x_msg_data ,
2440 p_encoded => FND_API.G_FALSE
2441 );
2442
2443 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2444 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
2445 'cn.plsql.cn_calc_submission_pub.update_calc_submission.exception',
2446 sqlerrm);
2447 end if;
2448
2449 END update_calc_submission;
2450
2451 END cn_calc_submission_pub ;