1 PACKAGE BODY CN_CALC_SUBMISSION_PVT AS
2 /*$Header: cnvsbcsb.pls 120.5 2006/05/25 01:40:33 ymao ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_CALC_SUBMISSION_PVT';
5
6 TYPE sub_batch_rec_type IS RECORD
7 (name cn_calc_submission_batches.name%TYPE,
8 org_id cn_calc_submission_batches.org_id%TYPE,
9 calc_sub_batch_id cn_calc_submission_batches.calc_sub_batch_id%TYPE,
10 calc_type cn_calc_submission_batches.calc_type%TYPE,
11 start_date cn_calc_submission_batches.start_date%TYPE,
12 end_date cn_calc_submission_batches.end_date%TYPE,
13 salesrep_option cn_calc_submission_batches.salesrep_option%TYPE,
14 hierarchy_flag cn_calc_submission_batches.hierarchy_flag%TYPE,
15 concurrent_flag cn_calc_submission_batches.concurrent_flag%TYPE,
16 intelligent_flag cn_calc_submission_batches.intelligent_flag%TYPE,
17 status cn_calc_submission_batches.status%TYPE,
18 interval_type_id cn_calc_submission_batches.interval_type_id%TYPE,
19 process_audit_id cn_calc_submission_batches.process_audit_id%TYPE,
20 object_version_number cn_calc_submission_batches.object_version_number%TYPE,
21 concurrent_request_id cn_process_audits.concurrent_request_id%TYPE);
22
23 TYPE rep_entry_rec_type IS RECORD
24 (name jtf_rs_salesreps.name%TYPE,
25 employee_number jtf_rs_salesreps.salesrep_number%TYPE,
26 salesrep_id jtf_rs_salesreps.salesrep_id%TYPE,
27 calc_sub_entry_id cn_calc_submission_entries.calc_sub_entry_id%TYPE);
28
29 TYPE quota_entry_rec_type IS RECORD
30 (name cn_quotas.name%TYPE,
31 quota_id cn_quotas.quota_id%TYPE,
32 calc_sub_quota_id cn_calc_sub_quotas.calc_sub_quota_id%TYPE);
33
34 TYPE rep_entry_tbl_type IS TABLE OF rep_entry_rec_type INDEX BY BINARY_INTEGER;
35 TYPE quota_entry_tbl_type IS TABLE OF quota_entry_rec_type INDEX BY BINARY_INTEGER;
36 TYPE name_tbl_type IS TABLE OF cn_interval_types_all_tl.name%TYPE INDEX BY BINARY_INTEGER;
37
38 --
39 -- Name
40 -- check_end_of_interval
41 -- Purpose
42 -- Returns 1 if the specified period is the end of an interval of the
43 -- type listed int he X_Interval string.
44 -- History
45 -- 06/13/95 Created Rjin
46 --
47 FUNCTION check_end_of_interval(p_period_id NUMBER,
48 p_interval_type_id NUMBER,
49 p_org_id NUMBER)
50 RETURN BOOLEAN IS
51 l_end_period_id NUMBER(15);
52 BEGIN
53 SELECT MAX(ps2.period_id)
54 INTO l_end_period_id
55 FROM cn_period_statuses_all ps1,
56 cn_period_statuses_all ps2
57 WHERE ps1.period_id = p_period_id
58 AND ps1.org_id = p_org_id
59 AND ps2.period_set_id = ps1.period_set_id
60 AND ps2.org_id = p_org_id
61 AND ps2.period_type_id = ps1.period_type_id
62 AND ps2.period_year = ps1.period_year
63 AND ( ( p_interval_type_id = -1001 AND ps2.quarter_num = ps1.quarter_num ) -- quarter interval
64 OR p_interval_type_id = -1002 ) ; -- year interval
65
66 IF p_period_id = l_end_period_id THEN
67 RETURN TRUE;
68 ELSE
69 RETURN FALSE;
70 END IF;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
75 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
76 'cn.plsql.cn_calc_submission_pvt.check_end_of_interval.exception',
77 sqlerrm);
78 end if;
79 raise ;
80 END check_end_of_interval;
81
82 --
83 -- Name
84 -- check_active_plan_assign
85 -- Purpose
86 -- Returns 1 if the specified period is the end of an interval of the
87 -- type listed int he X_Interval string.
88 -- History
89 -- 06/13/95 Created Tony Lower
90 --
91 FUNCTION check_active_plan_assign (p_salesrep_id NUMBER,
92 p_end_date DATE,
93 p_org_id NUMBER )
94 RETURN BOOLEAN IS
95
96 CURSOR l_active_plan_csr IS
97 SELECT 1
98 FROM cn_srp_plan_assigns_all spa,
99 cn_comp_plans_all plan
100 WHERE spa.salesrep_id = p_salesrep_id
101 AND spa.org_id = p_org_id
102 AND ((spa.end_date IS NOT NULL
103 AND p_end_date BETWEEN spa.start_date AND spa.end_date)
104 OR (p_end_date >= spa.start_date AND spa.end_date IS NULL ) )
105 AND spa.comp_plan_id = plan.comp_plan_id
106 AND plan.status_code = 'COMPLETE';
107
108 dummy NUMBER;
109
110 BEGIN
111 OPEN l_active_plan_csr;
112 FETCH l_active_plan_csr INTO dummy;
113 CLOSE l_active_plan_csr;
114
115 IF dummy = 1 THEN
116 RETURN TRUE;
117 ELSE
118 RETURN FALSE;
119 END IF;
120 END check_active_plan_assign;
121
122 -- validate the calculation request
123 PROCEDURE validate_submission_records
124 (p_sub_batch_rec IN sub_batch_rec_type,
125 p_rep_entry_tbl IN rep_entry_tbl_type,
126 p_quota_entry_tbl IN quota_entry_tbl_type,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2)
130 IS
131 l_dummy NUMBER;
132 l_quota_name cn_quotas.name%TYPE;
133
134 CURSOR name_check (p_batch_name cn_calc_submission_batches.name%TYPE ) IS
135 SELECT 1
136 FROM cn_calc_submission_batches_all
137 WHERE name = p_batch_name
138 AND org_id = p_sub_batch_rec.org_id
139 AND (p_sub_batch_rec.calc_sub_batch_id IS NULL OR calc_sub_batch_id <> p_sub_batch_rec.calc_sub_batch_id);
140
141 CURSOR open_period_check(p_start_date DATE) IS
142 SELECT 1
143 FROM cn_period_statuses_all
144 WHERE (period_set_id, period_type_id) = (SELECT period_set_id, period_type_id FROM cn_repositories_all
145 WHERE org_id = p_sub_batch_rec.org_id)
146 AND period_status = 'O'
147 AND org_id = p_sub_batch_rec.org_id
148 AND trunc(p_start_date) BETWEEN trunc(start_date) AND trunc(end_date);
149
150 CURSOR quota_check(p_quota_id NUMBER) IS
151 SELECT name
152 FROM cn_quotas_all
153 WHERE quota_id = p_quota_id
154 AND org_id = p_sub_batch_rec.org_id
155 AND incentive_type_code = 'BONUS'
156 AND (interval_type_id = p_sub_batch_rec.interval_type_id OR
157 (p_sub_batch_rec.interval_type_id = -1003 AND interval_type_id IN (-1000, -1001, -1002)));
158 BEGIN
159 -- Initialize API return status to success
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161
162 -- 1. name can not be null and should be unique(there is a unique index on name)
163 IF (p_sub_batch_rec.name IS NULL) THEN
164 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
165 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('NAME', 'CALC_SUBMISSION_OBJECT_TYPE'));
166 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
167 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
168 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
169 TRUE);
170 end if;
171
172 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
173 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
174 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('NAME', 'CALC_SUBMISSION_OBJECT_TYPE'));
175 fnd_msg_pub.ADD;
176 END IF;
177 x_return_status := fnd_api.g_ret_sts_error;
178 END IF;
179
180 OPEN name_check(p_sub_batch_rec.name);
181 FETCH name_check INTO l_dummy;
182 IF (name_check%found) THEN
183 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_EXISTS');
184 fnd_message.set_token('BATCH_NAME', cn_api.get_lkup_meaning('NAME', 'CALC_SUBMISSION_OBJECT_TYPE'));
185 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
186 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
187 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
188 TRUE);
189 end if;
190
191 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
192 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_EXISTS');
193 fnd_message.set_token('BATCH_NAME', cn_api.get_lkup_meaning('NAME', 'CALC_SUBMISSION_OBJECT_TYPE'));
194 FND_MSG_PUB.Add;
195 END IF;
196 x_return_status := fnd_api.g_ret_sts_error;
197 END IF;
198 CLOSE name_check;
199
200 -- 2. start_date and end_date can not be null and end_date >= start_date
201 IF (p_sub_batch_rec.start_date IS NULL) THEN
202 fnd_message.set_name('CN', 'CN_START_DATE_CANNOT_NULL');
203 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
204 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
205 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
206 TRUE);
207 end if;
208
209 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
210 fnd_message.set_name('CN', 'CN_START_DATE_CANNOT_NULL');
211 fnd_msg_pub.ADD;
212 END IF;
213 x_return_status := fnd_api.g_ret_sts_error;
214 END IF;
215 IF (p_sub_batch_rec.end_date IS NULL) THEN
216 fnd_message.set_name('CN', 'CN_END_DATE_CANNOT_NULL');
217 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
218 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
219 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
220 TRUE);
221 end if;
222
223 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
224 fnd_message.set_name('CN', 'CN_END_DATE_CANNOT_NULL');
225 fnd_msg_pub.ADD;
226 END IF;
227 x_return_status := fnd_api.g_ret_sts_error;
228 END IF;
229 IF (p_sub_batch_rec.start_date > p_sub_batch_rec.end_date) THEN
230 fnd_message.set_name('CN', 'CN_INVALID_DATE_RANGE');
231 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
232 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
233 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
234 TRUE);
235 end if;
236
237 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
238 fnd_message.set_name('CN', 'CN_INVALID_DATE_RANGE');
239 fnd_msg_pub.ADD;
240 END IF;
241 x_return_status := fnd_api.g_ret_sts_error;
242 END IF;
243
244 -- 3. start_date and end_date must be within an open period
245 OPEN open_period_check(p_sub_batch_rec.start_date);
246 FETCH open_period_check INTO l_dummy;
247 IF (open_period_check%notfound) THEN
248 fnd_message.set_name('CN' , 'CN_CALC_SUB_OPEN_DATE');
249 fnd_message.set_token('DATE', p_sub_batch_rec.start_date);
250 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
251 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
252 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
253 TRUE);
254 end if;
255
256 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
257 fnd_message.set_name('CN' , 'CN_CALC_SUB_OPEN_DATE');
258 fnd_message.set_token('DATE', p_sub_batch_rec.start_date);
259 fnd_msg_pub.ADD;
260 END IF;
261 x_return_status := fnd_api.g_ret_sts_error;
262 END IF;
263 CLOSE open_period_check;
264 OPEN open_period_check(p_sub_batch_rec.end_date);
265 FETCH open_period_check INTO l_dummy;
266 IF (open_period_check%notfound) THEN
267 fnd_message.set_name('CN' , 'CN_CALC_SUB_OPEN_DATE');
268 fnd_message.set_token('DATE', p_sub_batch_rec.end_date);
269 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
270 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
271 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
272 TRUE);
273 end if;
274
275 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
276 fnd_message.set_name('CN' , 'CN_CALC_SUB_OPEN_DATE');
277 fnd_message.set_token('DATE', p_sub_batch_rec.end_date);
278 fnd_msg_pub.ADD;
279 END IF;
280 x_return_status := fnd_api.g_ret_sts_error;
281 END IF;
282 CLOSE open_period_check;
283
284 -- 4. calc_type can not be null, must be 'COMMISSION' or 'BONUS'
285 IF (p_sub_batch_rec.calc_type IS NULL) THEN
286 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
287 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('CALC_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE'));
288 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
289 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
290 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
291 TRUE);
292 end if;
293
294 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
295 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
296 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('CALC_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE'));
297 fnd_msg_pub.ADD;
298 END IF;
299 x_return_status := fnd_api.g_ret_sts_error;
300 END IF;
301 IF (p_sub_batch_rec.calc_type NOT IN ('COMMISSION', 'BONUS')) THEN
302 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
303 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('CALC_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE'));
304 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
305 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
306 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
307 TRUE);
308 end if;
309
310 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
311 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
312 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('CALC_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE'));
313 fnd_msg_pub.ADD;
314 END IF;
315 x_return_status := fnd_api.g_ret_sts_error;
316 END IF;
317
318 -- 5. salesrep_option can not be null and must be a valid value
319 IF (p_sub_batch_rec.salesrep_option IS NULL) THEN
320 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
321 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('SALESREP_OPTION', 'CALC_SUBMISSION_OBJECT_TYPE'));
322 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
323 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
324 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
325 TRUE);
326 end if;
327
328 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
329 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
330 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('SALESREP_OPTION', 'CALC_SUBMISSION_OBJECT_TYPE'));
331 fnd_msg_pub.ADD;
332 END IF;
333 x_return_status := fnd_api.g_ret_sts_error;
334 END IF;
335 IF (p_sub_batch_rec.salesrep_option NOT IN ('ALL_REPS', 'USER_SPECIFY', 'REPS_IN_NOTIFY_LOG') OR
336 (p_sub_batch_rec.calc_type = 'BONUS' AND p_sub_batch_rec.salesrep_option = 'REPS_IN_NOTIFY_LOG'))
337 THEN
338 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
339 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('SALESREP_OPTION', 'CALC_SUBMISSION_OBJECT_TYPE'));
340 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
341 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
342 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
343 TRUE);
344 end if;
345
346 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
347 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
348 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('SALESREP_OPTION', 'CALC_SUBMISSION_OBJECT_TYPE'));
349 fnd_msg_pub.ADD;
350 END IF;
351 x_return_status := fnd_api.g_ret_sts_error;
352 END IF;
353
354 -- 6. hierarchy_flag can not be null, must be a valid value
355 -- TO DO: remove this check if hierarchy flag is only supported at rep level in R12
356 IF (p_sub_batch_rec.hierarchy_flag IS NULL) THEN
357 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
358 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('HIERARCHY_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
359 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
360 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
361 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
362 TRUE);
363 end if;
364
365 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
366 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
367 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('HIERARCHY_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
368 fnd_msg_pub.ADD;
369 END IF;
370 x_return_status := fnd_api.g_ret_sts_error;
371 END IF;
372 IF (p_sub_batch_rec.hierarchy_flag NOT IN ('Y', 'N') OR
373 (p_sub_batch_rec.salesrep_option IN ('ALL_REPS', 'REPS_IN_NOTIFY_LOG') AND p_sub_batch_rec.hierarchy_flag = 'Y'))
374 THEN
375 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
376 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('HIERARCHY_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
377 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
378 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
379 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
380 TRUE);
381 end if;
382
383 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
384 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
385 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('HIERARCHY_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
386 fnd_msg_pub.ADD;
387 END IF;
388 x_return_status := fnd_api.g_ret_sts_error;
389 END IF;
390
391 -- 7. concurrent_flag can not be null, must be a valid value
392 IF (p_sub_batch_rec.concurrent_flag IS NULL) THEN
393 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
394 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('CONCURRENT_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
395 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
396 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
397 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
398 TRUE);
399 end if;
400
401 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
402 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
403 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('CONCURRENT_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
404 fnd_msg_pub.ADD;
405 END IF;
406 x_return_status := fnd_api.g_ret_sts_error;
407 END IF;
408 IF (p_sub_batch_rec.concurrent_flag NOT IN ('Y', 'N')) THEN
409 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
410 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('CONCURRENT_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
411 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
412 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
413 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
414 TRUE);
415 end if;
416
417 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
418 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
419 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('CONCURRENT_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
420 fnd_msg_pub.ADD;
421 END IF;
422 x_return_status := fnd_api.g_ret_sts_error;
423 END IF;
424
425 -- 8. intelligent_flag can not be null, must be a valid value
426 IF (p_sub_batch_rec.intelligent_flag IS NULL) THEN
427 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
428 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('INTELLIGENT_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
429 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
430 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
431 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
432 TRUE);
433 end if;
434
435 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
436 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
437 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('INTELLIGENT_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
438 fnd_msg_pub.ADD;
439 END IF;
440 x_return_status := fnd_api.g_ret_sts_error;
441 END IF;
442 IF (p_sub_batch_rec.intelligent_flag NOT IN ('Y', 'N') OR
443 (p_sub_batch_rec.calc_type = 'BONUS' AND p_sub_batch_rec.intelligent_flag = 'Y') OR
444 (p_sub_batch_rec.salesrep_option = 'REPS_IN_NOTIFY_LOG' AND p_sub_batch_rec.intelligent_flag = 'N'))
445 THEN
446 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
447 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('INTELLIGENT_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
448 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
449 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
450 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
451 TRUE);
452 end if;
453
454 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
455 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
456 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('INTELLIGENT_FLAG', 'CALC_SUBMISSION_OBJECT_TYPE'));
457 fnd_msg_pub.ADD;
458 END IF;
459 x_return_status := fnd_api.g_ret_sts_error;
460 END IF;
461
462 -- 9. interval_type can not be null, must be a valid value if calc_type = 'BONUS'
463 IF (p_sub_batch_rec.calc_type = 'BONUS') THEN
464 IF (p_sub_batch_rec.interval_type_id IS NULL) THEN
465 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
466 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('INTERVAL_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE'));
467 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
468 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
469 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
470 TRUE);
471 end if;
472
473 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
474 fnd_message.set_name('CN', 'CN_CANNOT_NULL');
475 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('INTERVAL_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE'));
476 fnd_msg_pub.ADD;
477 END IF;
478 x_return_status := fnd_api.g_ret_sts_error;
479 END IF;
480 IF (p_sub_batch_rec.interval_type_id NOT IN (-1000, -1001, -1002, -1003)) THEN
481 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
482 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('INTERVAL_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE'));
483 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
484 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
485 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
486 TRUE);
487 end if;
488
489 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
490 fnd_message.set_name('CN' , 'CN_INVALID_DATA');
491 fnd_message.set_token('OBJ_NAME', cn_api.get_lkup_meaning('INTERVAL_TYPE', 'CALC_SUBMISSION_OBJECT_TYPE'));
492 fnd_msg_pub.ADD;
493 END IF;
494 x_return_status := fnd_api.g_ret_sts_error;
495 END IF;
496 END IF;
497
498 -- 10. salesrep validation
499 IF (p_sub_batch_rec.salesrep_option = 'USER_SPECIFY') THEN
500 IF (p_rep_entry_tbl.COUNT = 0) THEN
501 fnd_message.set_name('CN' , 'CN_CALC_NO_SALESREP');
502 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
503 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
504 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
505 TRUE);
506 end if;
507
508 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
509 fnd_message.set_name('CN' , 'CN_CALC_NO_SALESREP');
510 fnd_msg_pub.ADD;
511 END IF;
512 x_return_status := fnd_api.g_ret_sts_error;
513 END IF;
514
515 IF (p_sub_batch_rec.calc_sub_batch_id IS NOT NULL AND p_rep_entry_tbl.COUNT > 0) THEN
516 l_dummy := 0;
517 FOR i IN p_rep_entry_tbl.first..p_rep_entry_tbl.last LOOP
518 IF (p_rep_entry_tbl(i).salesrep_id IS NOT NULL) THEN
519 l_dummy := 1;
520 EXIT;
521 END IF;
522 END LOOP;
523
524 IF (l_dummy = 0) THEN
525 fnd_message.set_name('CN' , 'CN_CALC_NO_SALESREP');
526 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
527 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
528 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
529 TRUE);
530 end if;
531
532 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
533 fnd_message.set_name('CN' , 'CN_CALC_NO_SALESREP');
534 fnd_msg_pub.ADD;
535 END IF;
536 x_return_status := fnd_api.g_ret_sts_error;
537 END IF;
538 END IF;
539
540 IF (p_rep_entry_tbl.COUNT > 0) THEN
541 FOR i IN p_rep_entry_tbl.first..p_rep_entry_tbl.last LOOP
542 FOR j IN p_rep_entry_tbl.first..p_rep_entry_tbl.last LOOP
543 IF (j <> i) THEN
544 IF (p_rep_entry_tbl(j).salesrep_id = p_rep_entry_tbl(i).salesrep_id) THEN
545 x_return_status := fnd_api.g_ret_sts_error;
546
547 fnd_message.set_name('CN', 'CN_CALC_SALESREP_EXIST');
548 fnd_message.set_token('NAME', p_rep_entry_tbl(i).name);
549 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
550 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
551 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
552 TRUE);
553 end if;
554
555 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
556 fnd_message.set_name('CN', 'CN_CALC_SALESREP_EXIST');
557 fnd_message.set_token('NAME', p_rep_entry_tbl(i).name);
558 fnd_msg_pub.ADD;
559 END IF;
560 EXIT;
561 END IF;
562 END IF;
563 END LOOP;
564 END LOOP;
565 END IF;
566 END IF;
567
568 -- 11. plan element validation
569 IF (p_sub_batch_rec.calc_type = 'BONUS' AND p_sub_batch_rec.interval_type_id IS NOT NULL AND p_quota_entry_tbl.COUNT > 0) THEN
570 FOR i IN p_quota_entry_tbl.first..p_quota_entry_tbl.last LOOP
571 IF (p_quota_entry_tbl(i).quota_id IS NOT NULL) THEN
572 OPEN quota_check(p_quota_entry_tbl(i).quota_id);
573 FETCH quota_check INTO l_quota_name;
574 IF (quota_check%notfound) THEN
575 fnd_message.set_name('CN', 'CN_CALC_PE_NO_MATCH');
576 fnd_message.set_token('QUOTA_NAME', l_quota_name);
577 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
578 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
579 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.error',
580 TRUE);
581 end if;
582
583 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
584 fnd_message.set_name('CN', 'CN_CALC_PE_NO_MATCH');
585 fnd_message.set_token('QUOTA_NAME', l_quota_name);
586 fnd_msg_pub.ADD;
587 END IF;
588 x_return_status := fnd_api.g_ret_sts_error;
589 END IF;
590 CLOSE quota_check;
591 END IF;
592 END LOOP;
593 END IF;
594
595 -- Standard call to get message count and if count is 1, get message info.
596 FND_MSG_PUB.Count_And_Get
597 (p_count => x_msg_count ,
598 p_data => x_msg_data ,
599 p_encoded => FND_API.G_FALSE );
600 EXCEPTION
601 WHEN FND_API.G_EXC_ERROR THEN
602 x_return_status := FND_API.G_RET_STS_ERROR ;
603 FND_MSG_PUB.count_and_get
604 (p_count => x_msg_count ,
605 p_data => x_msg_data ,
606 p_encoded => FND_API.G_FALSE );
607 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
609 FND_MSG_PUB.count_and_get
610 (p_count => x_msg_count ,
611 p_data => x_msg_data ,
612 p_encoded => FND_API.G_FALSE );
613 WHEN OTHERS THEN
614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
615 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
616 FND_MSG_PUB.add_exc_msg
617 (G_PKG_NAME ,
618 'validate_submission_records');
619 END IF;
620 FND_MSG_PUB.count_and_get
621 (p_count => x_msg_count ,
622 p_data => x_msg_data ,
623 p_encoded => FND_API.G_FALSE );
624
625 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
626 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
627 'cn.plsql.cn_calc_submission_pvt.validate_submission_records.exception',
628 sqlerrm);
629 end if;
630 END validate_submission_records;
631
632 FUNCTION translate(Encoded_Message VARCHAR2) Return VARCHAR2 IS
633 temp_message VARCHAR2(500);
634 appl_short_name VARCHAR2(3);
635 translate BOOLEAN;
636 temp_buf VARCHAR2(255);
637 token_value VARCHAR2(50);
638 translate_arg VARCHAR2(10);
639 pos1 NUMBER;
640 pos2 NUMBER;
641 BEGIN
642 temp_message := ltrim(encoded_message);
643
644 -- Extract the Application Short Name and Message Name
645 pos1 := instr(temp_message, ' ', 1);
646 appl_short_name := substr(temp_message, 1, pos1 - 1);
647 temp_message := ltrim(substr(temp_message, pos1 + 1));
648 pos1 := instr(temp_message, ' ', 1);
649
650 -- Store the Message name in variable Temp_Buf
651 IF (pos1 = 0) THEN
652 -- There are no tokens, just a message name
653 temp_buf := temp_message;
654 temp_message := NULL;
655
656 fnd_message.set_name(appl_short_name, temp_buf);
657 temp_buf := fnd_message.get;
658
659 return(temp_buf);
660
661 ELSE
662 -- there are tokens
663 temp_buf := substr(temp_message, 1, pos1 - 1);
664 temp_message := ltrim(substr(temp_message, pos1 + 1));
665 fnd_message.set_name(appl_short_name, temp_buf);
666
667 END IF;
668
669
670 -- Extract the token information if necessary.
671 IF (temp_message IS NOT NULL) THEN
672 LOOP
673 -- Store the token name in Temp_Buf
674 pos1 := instr(temp_message, ' ', 1);
675 temp_buf := substr(temp_message, 1, pos1 - 1);
676
677 -- locate the Token Value Delimiters and extract the token value.
678 pos1 := instr(temp_message, '\"', 1);
679 pos2 := instr(temp_message, '\"', Pos1 + 2, 1);
680 token_value := substr(temp_message, pos1 + 2, pos2 - pos1 - 2);
681 temp_message := ltrim(substr(temp_message, pos2 + 2));
682 pos1 := instr(temp_message, ' ', 1);
683
684 -- Pos1 will equal 0 when Temp_Message is NULL which means that
685 -- there are no more tokens to process.
686 IF (Pos1 <> 0) THEN
687 translate_arg := upper(substr(temp_message, 1, pos1 - 1));
688 temp_message := ltrim(substr(temp_message, pos1 + 1));
689 ELSE
690 translate_arg := upper(temp_message);
691 temp_message := NULL;
692 END IF;
693 IF (translate_arg = 'TRUE') then
694 Translate := True;
695 ELSIF (translate_arg = 'FALSE') then
696 Translate := False;
697 end if;
698
699 fnd_message.set_token(temp_buf, token_value, translate);
700
701 EXIT WHEN (temp_message IS NULL);
702
703 END LOOP;
704
705 END IF;
706
707 temp_message := fnd_message.get;
708 return(temp_message);
709
710 END translate;
711
712 -- get submission batch detail
713 PROCEDURE get_submission_batch
714 (p_calc_sub_batch_id IN cn_calc_submission_batches.calc_sub_batch_id%TYPE,
715 x_sub_batch_rec OUT NOCOPY sub_batch_rec_type,
716 x_rep_entry_tbl OUT NOCOPY rep_entry_tbl_type,
717 x_quota_entry_tbl OUT NOCOPY quota_entry_tbl_type,
718 x_interval_type_tbl OUT NOCOPY name_tbl_type)
719 IS
720 i pls_integer := 0;
721
722 CURSOR batch IS
723 SELECT name,
724 org_id,
725 calc_sub_batch_id,
726 calc_type,
727 start_date,
728 end_date,
729 salesrep_option,
730 hierarchy_flag,
731 concurrent_flag,
732 intelligent_flag,
733 status,
734 interval_type_id,
735 process_audit_id,
736 object_version_number,
737 null
738 FROM cn_calc_submission_batches
739 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
740
741 CURSOR reps IS
742 SELECT b.name, b.employee_number, a.salesrep_id, a.calc_sub_entry_id
743 FROM cn_calc_submission_entries a,
744 cn_salesreps b
745 WHERE a.calc_sub_batch_id = p_calc_sub_batch_id
746 AND a.salesrep_id = b.salesrep_id;
747
748 CURSOR quotas IS
749 SELECT b.name, a.quota_id, a.calc_sub_quota_id
750 FROM cn_calc_sub_quotas a,
751 cn_quotas b
752 WHERE a.calc_sub_batch_id = p_calc_sub_batch_id
753 AND a.quota_id = b.quota_id;
754
755 CURSOR interval_types IS
756 SELECT name
757 FROM cn_interval_types
758 WHERE interval_type_id IN (-1000, -1001, -1002, -1003)
759 ORDER BY interval_type_id desc;
760
761 -- Added to retrieve concurrent request id for enhancment#2651798
762 CURSOR get_conc_req_id (a_process_audit_id NUMBER) IS
763 SELECT concurrent_request_id
764 FROM cn_process_audits
765 WHERE process_audit_id = a_process_audit_id;
766
767
768 BEGIN
769 OPEN batch;
770 FETCH batch INTO x_sub_batch_rec;
771 CLOSE batch;
772
773
774 -- Added to retrieve concurrent request id for enhancment#2651798
775 IF (x_sub_batch_rec.process_audit_id IS NOT NULL) AND
776 (x_sub_batch_rec.concurrent_flag = 'Y')
777 THEN
778 x_sub_batch_rec.concurrent_request_id := NULL;
779 OPEN get_conc_req_id(x_sub_batch_rec.process_audit_id);
780 FETCH get_conc_req_id INTO x_sub_batch_rec.concurrent_request_id;
781 CLOSE get_conc_req_id;
782 END IF;
783
784 FOR rep IN reps LOOP
785 x_rep_entry_tbl(i).name := rep.name;
786 x_rep_entry_tbl(i).employee_number := rep.employee_number;
787 x_rep_entry_tbl(i).salesrep_id := rep.salesrep_id;
788 x_rep_entry_tbl(i).calc_sub_entry_id := rep.calc_sub_entry_id;
789 i := i + 1;
790 END LOOP;
791
792 i := 0;
793 FOR quota IN quotas LOOP
794 x_quota_entry_tbl(i) := quota;
795 i := i + 1;
796 END LOOP;
797
798 i := 0;
799 FOR interval_type IN interval_types LOOP
800 x_interval_type_tbl(i) := interval_type.name;
801 i := i + 1;
802 END LOOP;
803 END get_submission_batch;
804
805 -- This procedure should be invoked only when retrieving calculation batch records in response to a search request
806 -- from the calculation batch search page.
807 PROCEDURE maintain_batch_status IS
808 BEGIN
809 update cn_calc_submission_batches_all sb
810 set status = 'FAILED'
811 where status = 'PROCESSING'
812 and concurrent_flag = 'Y'
813 and exists (select 1
814 from fnd_concurrent_requests
815 where request_id = (select concurrent_request_id
816 from cn_process_audits_all
817 where process_audit_id = sb.process_audit_id)
818 and status_code in ('E', 'X'));
819 commit;
820 EXCEPTION
821 WHEN OTHERS THEN
822 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
823 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
824 'cn.plsql.cn_calc_submission_pvt.maintain_batch_status.exception',
825 sqlerrm);
826 end if;
827 END maintain_batch_status;
828
829 PROCEDURE Validate
830 (p_api_version IN NUMBER,
831 p_init_msg_list IN VARCHAR2,
832 p_commit IN VARCHAR2,
833 p_validation_level IN NUMBER,
834 x_return_status OUT NOCOPY VARCHAR2,
835 x_msg_count OUT NOCOPY NUMBER,
836 x_msg_data OUT NOCOPY VARCHAR2,
837 p_calc_sub_batch_id IN cn_calc_submission_batches.calc_sub_batch_id%TYPE
838 )
839 IS
840 l_api_name CONSTANT VARCHAR2(30) := 'Validate';
841 l_api_version CONSTANT NUMBER := 1.0;
842 l_sub_batch_rec sub_batch_rec_type;
843 l_rep_entry_tbl rep_entry_tbl_type;
844 l_quota_entry_tbl quota_entry_tbl_type;
845 l_interval_type_tbl name_tbl_type;
846 BEGIN
847 -- Standard Start of API savepoint
848
849 -- Standard call to check for call compatibility
850 IF NOT FND_API.Compatible_API_Call
851 (l_api_version,
852 p_api_version,
853 l_api_name,
854 G_PKG_NAME)
855 THEN
856 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
857 END IF;
858
859 -- Initialize message list if p_init_msg_list is set to TRUE.
860 IF FND_API.to_Boolean( p_init_msg_list) THEN
861 FND_MSG_PUB.initialize;
862 END IF;
863
864 -- Initialize API return status to success
865 x_return_status := FND_API.G_RET_STS_SUCCESS;
866
867 -- API body
868 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
869 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
870 'cn.plsql.cn_calc_submission_pvt.validate.begin',
871 'Beginning of validate...' );
872 end if;
873
874 get_submission_batch
875 (p_calc_sub_batch_id => p_calc_sub_batch_id,
876 x_sub_batch_rec => l_sub_batch_rec,
877 x_rep_entry_tbl => l_rep_entry_tbl,
878 x_quota_entry_tbl => l_quota_entry_tbl,
879 x_interval_type_tbl => l_interval_type_tbl);
880
881 validate_submission_records(p_sub_batch_rec => l_sub_batch_rec,
882 p_rep_entry_tbl => l_rep_entry_tbl,
883 p_quota_entry_tbl => l_quota_entry_tbl,
884 x_return_status => x_return_status,
885 x_msg_count => x_msg_count,
886 x_msg_data => x_msg_data);
887 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
888 RAISE fnd_api.g_exc_error;
889 END IF;
890
891 IF (cn_proc_batches_pkg.find_srp_incomplete_plan(p_calc_sub_batch_id)) THEN
892 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)) THEN
893 IF FND_API.To_Boolean(p_commit) THEN
894 COMMIT WORK;
895 END IF;
896
897 fnd_message.set_name('CN', 'CNSBCS_INCOMPLETE_PLAN');
898 fnd_msg_pub.add;
899 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
900 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
901 'cn.plsql.cn_calc_submission_pvt.validate.error',
902 FALSE);
903 end if;
904
905 END IF;
906 RAISE fnd_api.g_exc_error;
907 END IF;
908
909 IF FND_API.To_Boolean(p_commit) THEN
910 COMMIT WORK;
911 END IF;
912
913 -- Standard call to get message count and if count is 1, get message info.
914 FND_MSG_PUB.Count_And_Get
915 (p_count => x_msg_count ,
916 p_data => x_msg_data ,
917 p_encoded => FND_API.G_FALSE );
918
919 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
920 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
921 'cn.plsql.cn_calc_submission_pvt.validate.end',
922 'End of validate.' );
923 end if;
924
925 EXCEPTION
926 WHEN FND_API.G_EXC_ERROR THEN
927 x_return_status := FND_API.G_RET_STS_ERROR ;
928 FND_MSG_PUB.count_and_get
929 (p_count => x_msg_count ,
930 p_data => x_msg_data ,
931 p_encoded => FND_API.G_FALSE );
932 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
934 FND_MSG_PUB.count_and_get
935 (p_count => x_msg_count ,
936 p_data => x_msg_data ,
937 p_encoded => FND_API.G_FALSE );
938 WHEN OTHERS THEN
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
940 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
941 FND_MSG_PUB.add_exc_msg
942 (G_PKG_NAME ,
943 'Validate');
944 END IF;
945 FND_MSG_PUB.count_and_get
946 (p_count => x_msg_count ,
947 p_data => x_msg_data ,
948 p_encoded => FND_API.G_FALSE );
949
950 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
951 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
952 'cn.plsql.cn_calc_submission_pvt.validate.exception',
953 sqlerrm);
954 end if;
955
956 END Validate;
957
958 PROCEDURE Calculate
959 (p_api_version IN NUMBER,
960 p_init_msg_list IN VARCHAR2,
961 p_validation_level IN NUMBER,
962 x_return_status OUT NOCOPY VARCHAR2,
963 x_msg_count OUT NOCOPY NUMBER,
964 x_msg_data OUT NOCOPY VARCHAR2,
965 p_calc_sub_batch_id IN cn_calc_submission_batches.calc_sub_batch_id%TYPE)
966 IS
967 l_api_name CONSTANT VARCHAR2(30) := 'Calculate';
968 l_api_version CONSTANT NUMBER := 1.0;
969
970 l_process_audit_id NUMBER;
971 l_process_status_code VARCHAR2(30);
972 l_concurrent_flag VARCHAR2(1);
973
974 BEGIN
975 -- Standard call to check for call compatibility
976 IF NOT FND_API.Compatible_API_Call
977 (l_api_version,
978 p_api_version,
979 l_api_name,
980 G_PKG_NAME)
981 THEN
982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
983 END IF;
984
985 -- Initialize message list if p_init_msg_list is set to TRUE.
986 IF FND_API.to_Boolean( p_init_msg_list) THEN
987 FND_MSG_PUB.initialize;
988 END IF;
989
990 -- Initialize API return status to success
991 x_return_status := FND_API.G_RET_STS_SUCCESS;
992
993 -- API body
994 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
995 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
996 'cn.plsql.cn_calc_submission_pvt.calculate.begin',
997 'Beginning of calculate...' );
998 end if;
999
1000 validate
1001 (p_api_version => 1.0,
1002 p_init_msg_list => p_init_msg_list,
1003 p_commit => fnd_api.g_true,
1004 p_validation_level => p_validation_level,
1005 x_return_status => x_return_status,
1006 x_msg_count => x_msg_count,
1007 x_msg_data => x_msg_data,
1008 p_calc_sub_batch_id => p_calc_sub_batch_id);
1009
1010 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1011 raise fnd_api.g_exc_error;
1012 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1013 raise fnd_api.g_exc_unexpected_error;
1014 END IF;
1015
1016 cn_proc_batches_pkg.calculation_submission
1017 (p_calc_sub_batch_id => p_calc_sub_batch_id,
1018 x_process_audit_id => l_process_audit_id,
1019 x_process_status_code => l_process_status_code);
1020
1021 select concurrent_flag
1022 into l_concurrent_flag
1023 from cn_calc_submission_batches_all
1024 where calc_sub_batch_id = p_calc_sub_batch_id;
1025
1026 IF (l_concurrent_flag = 'N') THEN
1027 IF nvl(l_process_status_code,'FAIL') = 'SUCCESS' THEN
1028 fnd_message.set_name('CN','ALL_PROCESS_DONE_OK_LOG');
1029 ELSIF nvl(l_process_status_code,'FAIL') = 'FAIL' THEN
1030 fnd_message.set_name('CN','ALL_PROCESS_DONE_FAIL_LOG');
1031 END IF;
1032 ELSE
1033 IF nvl(l_process_status_code,'FAIL') = 'SUCCESS' THEN
1034 fnd_message.set_name('CN','ALL_PROCESS_SUBMIT_OK_LOG');
1035 ELSIF nvl(l_process_status_code,'FAIL') = 'FAIL' THEN
1036 fnd_message.set_name('CN','ALL_PROCESS_SUBMIT_FAIL_LOG');
1037 END IF;
1038 END IF;
1039 fnd_message.set_token('AUDIT_ID', l_process_audit_id);
1040 fnd_msg_pub.ADD;
1041
1042 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1043 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1044 'cn.plsql.cn_calc_submission_pvt.calculate.end',
1045 'End of calculate' );
1046 end if;
1047
1048 -- Standard call to get message count and if count is 1, get message info.
1049 FND_MSG_PUB.Count_And_Get
1050 (p_count => x_msg_count ,
1051 p_data => x_msg_data ,
1052 p_encoded => FND_API.G_FALSE );
1053 EXCEPTION
1054 WHEN FND_API.G_EXC_ERROR THEN
1055 x_return_status := FND_API.G_RET_STS_ERROR ;
1056 FND_MSG_PUB.count_and_get
1057 (p_count => x_msg_count ,
1058 p_data => x_msg_data ,
1059 p_encoded => FND_API.G_FALSE );
1060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1062 FND_MSG_PUB.count_and_get
1063 (p_count => x_msg_count ,
1064 p_data => x_msg_data ,
1065 p_encoded => FND_API.G_FALSE );
1066 WHEN OTHERS THEN
1067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1068 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1069 FND_MSG_PUB.add_exc_msg
1070 (G_PKG_NAME ,
1071 'Calculate');
1072 END IF;
1073 FND_MSG_PUB.count_and_get
1074 (p_count => x_msg_count ,
1075 p_data => x_msg_data ,
1076 p_encoded => FND_API.G_FALSE );
1077 END Calculate;
1078
1079 PROCEDURE CopyBatch
1080 (p_api_version IN NUMBER,
1081 p_init_msg_list IN VARCHAR2,
1082 p_commit IN VARCHAR2,
1083 p_validation_level IN NUMBER,
1084 x_return_status OUT NOCOPY VARCHAR2,
1085 x_msg_count OUT NOCOPY NUMBER,
1086 x_msg_data OUT NOCOPY VARCHAR2,
1087 p_calc_sub_batch_id IN cn_calc_submission_batches.calc_sub_batch_id%TYPE,
1088 p_out_calc_sub_batch_id OUT NOCOPY cn_calc_submission_batches.calc_sub_batch_id%TYPE)
1089 IS
1090 l_api_name CONSTANT VARCHAR2(30) := 'copybatch';
1091 l_api_version CONSTANT NUMBER := 1.0;
1092
1093 seq_batchid NUMBER;
1094 dummy_char VARCHAR2(30);
1095 batch_record cn_calc_submission_batches%rowtype;
1096 batch_entries_record cn_calc_submission_entries%rowtype;
1097 batch_bonus_record cn_calc_sub_quotas%rowtype;
1098
1099 temp_batch_name VARCHAR2(30);
1100 dummy_batch cn_calc_submission_batches%rowtype;
1101 dup_name_counter NUMBER := 1;
1102 record_checker BOOLEAN := FALSE;
1103
1104 cursor batch_cursor is
1105 select * from cn_calc_submission_batches where calc_sub_batch_id = p_calc_sub_batch_id;
1106
1107 cursor rep_cursor is
1108 select * from cn_calc_submission_entries where calc_sub_batch_id = p_calc_sub_batch_id;
1109
1110 cursor bonus_cursor is
1111 select * from cn_calc_sub_quotas where calc_sub_batch_id = p_calc_sub_batch_id;
1112
1113 cursor namecheck_cursor(p_batch_name_to_check cn_calc_submission_batches.name%TYPE) is
1114 select * from cn_calc_submission_batches where name = p_batch_name_to_check and rownum = 1;
1115
1116 BEGIN
1117 -- Standard Start of API savepoint
1118 SAVEPOINT copybatch_PVT;
1119
1120 -- Standard call to check for call compatibility
1121 IF NOT FND_API.Compatible_API_Call
1122 (l_api_version,
1123 p_api_version,
1124 l_api_name,
1125 G_PKG_NAME)
1126 THEN
1127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1128 END IF;
1129
1130 -- Initialize message list if p_init_msg_list is set to TRUE.
1131 IF FND_API.to_Boolean( p_init_msg_list) THEN
1132 FND_MSG_PUB.initialize;
1133 END IF;
1134
1135 -- Initialize API return status to success
1136 x_return_status := FND_API.G_RET_STS_SUCCESS;
1137
1138
1139 -- Body Begins.
1140 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1141 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1142 'cn.plsql.cn_calc_submission_pvt.copybatch.begin',
1143 'Beginning of copybatch ...' );
1144 end if;
1145
1146 select CN_CALC_SUBMISSION_BATCHES_S1.nextval into seq_batchid from dual;
1147
1148 open batch_cursor;
1149 FETCH batch_cursor INTO batch_record;
1150 if batch_cursor%NOTFOUND then
1151 raise_application_error(-20000, 'Cannot continue.. Calculation Batch doesnot exist');
1152 end if;
1153 --batch_record.name := 'Copy of '||substr(batch_record.name,1,20);
1154 batch_record.calc_sub_batch_id := seq_batchid;
1155 batch_record.process_audit_id := null;
1156 batch_record.object_version_number := 1;
1157 batch_record.logical_batch_id := null;
1158 batch_record.log_name := null;
1159 batch_record.ledger_je_batch_id := null;
1160 batch_record.concurrent_flag := 'N';
1161 batch_record.status := 'INCOMPLETE';
1162
1163
1164 LOOP
1165 -- First create the initial batch name
1166 temp_batch_name := 'Copy_'||dup_name_counter||' '||substr(batch_record.name,1,18);
1167 --dbms_output.put_line('The value of temp_batch_name is ' || temp_batch_name);
1168
1169 if NOT namecheck_cursor%ISOPEN then
1170 open namecheck_cursor(temp_batch_name);
1171 fetch namecheck_cursor into dummy_batch;
1172
1173 if namecheck_cursor%FOUND then
1174 -- duplicate record found, so increment the counter, and continue the loop
1175 record_checker := FALSE;
1176 dup_name_counter := dup_name_counter + 1;
1177 else
1178 -- duplicate record not found, current record is good
1179 record_checker := TRUE;
1180 end if;
1181
1182 if (namecheck_cursor%ISOPEN) then
1183 close namecheck_cursor;
1184 end if;
1185
1186 end if;
1187
1188 EXIT WHEN record_checker;
1189 END LOOP;
1190
1191 -- Now the non duplicated value must be available
1192 batch_record.name := temp_batch_name;
1193
1194 insert into
1195 cn_calc_submission_batches
1196 (ORG_ID,CALC_SUB_BATCH_ID,NAME,CALC_FROM_PERIOD_ID,CALC_TO_PERIOD_ID,INTELLIGENT_FLAG,HIERARCHY_FLAG,
1197 SALESREP_OPTION,CONCURRENT_FLAG,LOG_NAME,STATUS,LOGICAL_BATCH_ID,FORECAST_FLAG,ATTRIBUTE_CATEGORY,
1198 ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,
1199 ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,START_DATE,END_DATE,CALC_TYPE,
1200 INTERVAL_TYPE_ID,LEDGER_JE_BATCH_ID,PROCESS_AUDIT_ID,CREATED_BY,CREATION_DATE,OBJECT_VERSION_NUMBER,
1201 SECURITY_GROUP_ID)
1202 values(
1203 BATCH_RECORD.ORG_ID,BATCH_RECORD.CALC_SUB_BATCH_ID,BATCH_RECORD.NAME,BATCH_RECORD.CALC_FROM_PERIOD_ID,
1204 BATCH_RECORD.CALC_TO_PERIOD_ID,BATCH_RECORD.INTELLIGENT_FLAG,BATCH_RECORD.HIERARCHY_FLAG,
1205 BATCH_RECORD.SALESREP_OPTION,BATCH_RECORD.CONCURRENT_FLAG,BATCH_RECORD.LOG_NAME,BATCH_RECORD.STATUS,
1206 BATCH_RECORD.LOGICAL_BATCH_ID,BATCH_RECORD.FORECAST_FLAG,BATCH_RECORD.ATTRIBUTE_CATEGORY,BATCH_RECORD.ATTRIBUTE1,
1207 BATCH_RECORD.ATTRIBUTE2,BATCH_RECORD.ATTRIBUTE3,BATCH_RECORD.ATTRIBUTE4,BATCH_RECORD.ATTRIBUTE5,
1208 BATCH_RECORD.ATTRIBUTE6,BATCH_RECORD.ATTRIBUTE7,BATCH_RECORD.ATTRIBUTE8,BATCH_RECORD.ATTRIBUTE9,
1209 BATCH_RECORD.ATTRIBUTE10,BATCH_RECORD.ATTRIBUTE11,BATCH_RECORD.ATTRIBUTE12,BATCH_RECORD.ATTRIBUTE13,
1210 BATCH_RECORD.ATTRIBUTE14,BATCH_RECORD.ATTRIBUTE15,BATCH_RECORD.START_DATE,BATCH_RECORD.END_DATE,
1211 BATCH_RECORD.CALC_TYPE,BATCH_RECORD.INTERVAL_TYPE_ID,BATCH_RECORD.LEDGER_JE_BATCH_ID,
1212 BATCH_RECORD.PROCESS_AUDIT_ID,fnd_global.user_id,sysdate,
1213 BATCH_RECORD.OBJECT_VERSION_NUMBER,BATCH_RECORD.SECURITY_GROUP_ID);
1214
1215 -- Now you start working on entering the Batch Records.
1216 -- if the Salesrep option is USER_SPECIFY then only you have to copy records for the Child.
1217 --
1218 if (batch_record.salesrep_option = 'USER_SPECIFY') then
1219 -- Star
1220 for calc_entries in rep_cursor
1221 loop
1222 -- Process the Entry Records here.
1223 insert into cn_calc_submission_entries(ORG_ID,CALC_SUB_ENTRY_ID,CALC_SUB_BATCH_ID,SALESREP_ID,ATTRIBUTE_CATEGORY,
1224 ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,
1225 ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,CREATED_BY,
1226 CREATION_DATE,SECURITY_GROUP_ID,HIERARCHY_FLAG)
1227 values
1228 (calc_entries.ORG_ID,CN_CALC_SUBMISSION_ENTRIES_S1.nextval,seq_batchid,calc_entries.SALESREP_ID,
1229 calc_entries.ATTRIBUTE_CATEGORY,calc_entries.ATTRIBUTE1,calc_entries.ATTRIBUTE2,calc_entries.ATTRIBUTE3,
1230 calc_entries.ATTRIBUTE4,calc_entries.ATTRIBUTE5,calc_entries.ATTRIBUTE6,calc_entries.ATTRIBUTE7,
1231 calc_entries.ATTRIBUTE8,calc_entries.ATTRIBUTE9,calc_entries.ATTRIBUTE10,calc_entries.ATTRIBUTE11,
1232 calc_entries.ATTRIBUTE12,calc_entries.ATTRIBUTE13,calc_entries.ATTRIBUTE14,calc_entries.ATTRIBUTE15,
1233 fnd_global.user_id,sysdate,calc_entries.SECURITY_GROUP_ID,calc_entries.HIERARCHY_FLAG);
1234
1235 end loop;
1236 end if;
1237
1238
1239 -- Now Start processing the Bonus Plan Elements Copy
1240 if (batch_record.calc_type = 'BONUS') then
1241 -- Star
1242 for bonus_entries in bonus_cursor
1243 loop
1244 -- Process the Entry Records here.
1245 insert into cn_calc_sub_quotas
1246 (ORG_ID,CALC_SUB_QUOTA_ID,CALC_SUB_BATCH_ID,QUOTA_ID,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,
1247 ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,
1248 ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,CREATED_BY,CREATION_DATE,SECURITY_GROUP_ID)
1249 values
1250 (bonus_entries.ORG_ID,CN_CALC_SUB_QUOTAS_S.nextval,seq_batchid,bonus_entries.QUOTA_ID,
1251 bonus_entries.ATTRIBUTE_CATEGORY,bonus_entries.ATTRIBUTE1,bonus_entries.ATTRIBUTE2,
1252 bonus_entries.ATTRIBUTE3,bonus_entries.ATTRIBUTE4,bonus_entries.ATTRIBUTE5,bonus_entries.ATTRIBUTE6,
1253 bonus_entries.ATTRIBUTE7,bonus_entries.ATTRIBUTE8,bonus_entries.ATTRIBUTE9,bonus_entries.ATTRIBUTE10,
1254 bonus_entries.ATTRIBUTE11,bonus_entries.ATTRIBUTE12,bonus_entries.ATTRIBUTE13,bonus_entries.ATTRIBUTE14,
1255 bonus_entries.ATTRIBUTE15,fnd_global.user_id,sysdate,bonus_entries.SECURITY_GROUP_ID);
1256
1257 end loop;
1258 end if;
1259
1260 -- Store the new batchid in the out variable.
1261 p_out_calc_sub_batch_id := seq_batchid;
1262
1263 -- Close all cursors.
1264 if(batch_cursor%ISOPEN) then
1265 close batch_cursor;
1266 end if;
1267
1268 IF FND_API.To_Boolean(p_commit) THEN
1269 COMMIT WORK;
1270 END IF;
1271
1272 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1273 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1274 'cn.plsql.cn_calc_submission_pvt.copybatch.end',
1275 'End of copybatch' );
1276 end if;
1277 EXCEPTION
1278 WHEN FND_API.G_EXC_ERROR THEN
1279 ROLLBACK TO copybatch_PVT;
1280 x_return_status := FND_API.G_RET_STS_ERROR ;
1281 FND_MSG_PUB.count_and_get
1282 (p_count => x_msg_count ,
1283 p_data => x_msg_data ,
1284 p_encoded => FND_API.G_FALSE );
1285 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1286 ROLLBACK TO copybatch_PVT;
1287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1288 FND_MSG_PUB.count_and_get
1289 (p_count => x_msg_count ,
1290 p_data => x_msg_data ,
1291 p_encoded => FND_API.G_FALSE );
1292 WHEN OTHERS THEN
1293 ROLLBACK TO copybatch_PVT;
1294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1295 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1296 FND_MSG_PUB.add_exc_msg
1297 (G_PKG_NAME ,
1298 'copybatch');
1299 END IF;
1300 FND_MSG_PUB.count_and_get
1301 (p_count => x_msg_count ,
1302 p_data => x_msg_data ,
1303 p_encoded => FND_API.G_FALSE );
1304
1305 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1306 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1307 'cn.plsql.cn_calc_submission_pvt.CopyBatch.exception',
1308 sqlerrm);
1309 end if;
1310
1311 END CopyBatch;
1312
1313 END CN_CALC_SUBMISSION_PVT;
[Home] [Help]