1 PACKAGE BODY CN_API as
2 /* $Header: cnputilb.pls 120.24 2011/09/09 01:00:15 rnagaraj ship $ */
3
4
5
6 TYPE salesrep_cache_rec_type IS RECORD(
7 salesrep_id NUMBER
8 , period_id NUMBER
9 , role_name VARCHAR2(2000)
10 );
11
12
13 /**
14 * PLSQL Index By Table Type to contain information about many Resources
15 * where each element is of type RESOURCE_CACHE_REC_TYPE.
16 */
17 TYPE salesrep_cache_tbl_type IS TABLE OF salesrep_cache_rec_type
18 INDEX BY BINARY_INTEGER;
19
20 g_salesrep_info_cache salesrep_cache_tbl_type;
21
22
23
24 g_user_tab jtf_number_table;
25
26 --| ----------------------------------------------------------------------+
27 --| Procedure Name : get_fnd_message
28 --| ----------------------------------------------------------------------+
29 PROCEDURE get_fnd_message( p_msg_count NUMBER,
30 p_msg_data VARCHAR2) IS
31
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(2000);
34
35 BEGIN
36 IF (p_msg_count IS NULL) THEN
37 FND_MSG_PUB.Count_And_Get
38 (
39 p_count => l_msg_count ,
40 p_data => l_msg_data ,
41 p_encoded => FND_API.G_FALSE
42 );
43 ELSE
44 l_msg_count := p_msg_count;
45 l_msg_data := p_msg_data;
46 END IF;
47
48 IF l_msg_count = 1 THEN
49 cn_message_pkg.debug(substrb(l_msg_data,1,240));
50 ELSIF l_msg_count = 0 THEN
51 cn_message_pkg.debug('No Message');
52 ELSE
53 FOR ctr IN 1..l_msg_count LOOP
54 cn_message_pkg.debug
55 (substrb((FND_MSG_PUB.get
56 (p_msg_index => ctr, p_encoded => FND_API.G_FALSE)),
57 1,
58 240)
59 );
60 END LOOP ;
61 END IF;
62
63 cn_message_pkg.flush;
64
65 FND_MSG_PUB.initialize;
66
67 END get_fnd_message;
68
69 --| ----------------------------------------------------------------------+
70 --| Function Name : get_rate_table_name
71 --| ----------------------------------------------------------------------+
72 FUNCTION get_rate_table_name( p_rate_table_id NUMBER)
73 RETURN cn_rate_schedules.name%TYPE IS
74
75 l_rs_name cn_rate_schedules.name%TYPE;
76
77 BEGIN
78 SELECT name INTO l_rs_name
79 FROM cn_rate_schedules_all
80 WHERE rate_schedule_id = p_rate_table_id;
81
82 RETURN l_rs_name;
83
84 EXCEPTION
85 WHEN no_data_found THEN
86 RETURN NULL;
87 END get_rate_table_name;
88
89 --| ----------------------------------------------------------------------+
90 --| Function Name : get_rate_table_id
91 --| ----------------------------------------------------------------------+
92 FUNCTION get_rate_table_id( p_rate_table_name VARCHAR2,
93 p_org_id NUMBER)
94 RETURN cn_rate_schedules.rate_schedule_id%TYPE IS
95
96 l_rs_id cn_rate_schedules.rate_schedule_id%TYPE;
97
98 BEGIN
99 SELECT rate_schedule_id INTO l_rs_id
100 FROM cn_rate_schedules_all
101 WHERE name = p_rate_table_name
102 AND org_id = p_org_id;
103
104 RETURN l_rs_id;
105
106 EXCEPTION
107 WHEN no_data_found THEN
108 RETURN NULL;
109 END get_rate_table_id;
110
111
112 --| ----------------------------------------------------------------------+
113 --| Function Name : get_period_name -- only get active periods
114 --| ----------------------------------------------------------------------+
115 FUNCTION get_period_name( p_period_id NUMBER, p_org_id NUMBER)
116 RETURN cn_periods.period_name%TYPE IS
117
118 l_period_name cn_periods.period_name%TYPE;
119
120 BEGIN
121 SELECT period_name
122 INTO l_period_name
123 FROM cn_period_statuses_all
124 WHERE period_id = p_period_id
125 AND org_id = p_org_id
126 AND period_status IN ('F', 'O');
127
128 RETURN l_period_name;
129
130 EXCEPTION
131 WHEN no_data_found THEN
132 RETURN NULL;
133 END get_period_name;
134
135 --| ----------------------------------------------------------------------+
136 --| Function Name : get_period_id -- only get active periods
137 --| ----------------------------------------------------------------------+
138 FUNCTION get_period_id( p_period_name VARCHAR2, p_org_id NUMBER)
139 RETURN cn_periods.period_id%TYPE IS
140
141 l_period_id cn_periods.period_id%TYPE;
142
143 BEGIN
144 SELECT period_id
145 INTO l_period_id
146 FROM cn_period_statuses_all
147 WHERE Upper(period_name) = Upper(p_period_name)
148 AND org_id = p_org_id
149 AND period_status IN ('F', 'O');
150
151 RETURN l_period_id;
152
153 EXCEPTION
154 WHEN no_data_found THEN
155 RETURN NULL;
156 END get_period_id;
157
158 --| ---------------------------------------------------------------------+
159 --| Function Name : get_rev_class_id
160 --| Desc : Pass in revenue class name then return revenue class id
161 --| ---------------------------------------------------------------------+
162 FUNCTION get_rev_class_id( p_rev_class_name VARCHAR2, p_org_id NUMBER)
163 RETURN cn_revenue_classes.revenue_class_id%TYPE IS
164
165 l_rev_class_id cn_revenue_classes.revenue_class_id%TYPE;
166
167 BEGIN
168 SELECT revenue_class_id
169 INTO l_rev_class_id
170 FROM cn_revenue_classes_all
171 WHERE name = p_rev_class_name
172 AND org_id = p_org_id
173 ;
174 RETURN l_rev_class_id;
175 EXCEPTION
176 WHEN no_data_found THEN
177 RETURN NULL;
178 END get_rev_class_id;
179
180 --| -----------------------------------------------------------------------+
181 --| Function Name : get_rev_class_name
182 --| Desc : Pass in revenue class name then return revenue class id
183 --| ---------------------------------------------------------------------+
184 FUNCTION get_rev_class_name( p_rev_class_id NUMBER)
185 RETURN cn_revenue_classes.name%TYPE IS
186
187 l_rev_class_name cn_revenue_classes.name%TYPE;
188
189 BEGIN
190 SELECT name
191 INTO l_rev_class_name
192 FROM cn_revenue_classes_all
193 WHERE revenue_class_id = p_rev_class_id
194 ;
195 RETURN l_rev_class_name;
196 EXCEPTION
197 WHEN no_data_found THEN
198 RETURN NULL;
199 END get_rev_class_name;
200
201 --| ---------------------------------------------------------------------+
202 --| Function Name : get_lkup_meaning
203 --| ---------------------------------------------------------------------+
204 FUNCTION get_lkup_meaning( p_lkup_code VARCHAR2,
205 p_lkup_type VARCHAR2 )
206 RETURN cn_lookups.meaning%TYPE IS
207
208 l_meaning cn_lookups.meaning%TYPE;
209
210 BEGIN
211 SELECT meaning
212 INTO l_meaning
213 FROM cn_lookups
214 WHERE lookup_type = p_lkup_type
215 AND lookup_code = p_lkup_code
216 ;
217
218 RETURN l_meaning;
219
220 EXCEPTION
221 WHEN no_data_found THEN
222 RETURN NULL;
223 END get_lkup_meaning;
224
225 --| ---------------------------------------------------------------------+
226 --| Procedure Name : chk_miss_char_para
227 --| Desc : Check for missing parameters -- Char type
228 --| ---------------------------------------------------------------------+
229 FUNCTION chk_miss_char_para
230 ( p_char_para IN VARCHAR2 ,
231 p_para_name IN VARCHAR2 ,
232 p_loading_status IN VARCHAR2 ,
233 x_loading_status OUT NOCOPY VARCHAR2 )
234 RETURN VARCHAR2 IS
235
236 l_return_code VARCHAR2(1);
237
238 BEGIN
239 l_return_code := FND_API.G_FALSE;
240 x_loading_status := p_loading_status;
241 IF (p_char_para = FND_API.G_MISS_CHAR) THEN
242 FND_MESSAGE.SET_NAME ('CN' , 'CN_MISS_PARAMETER');
243 FND_MESSAGE.SET_TOKEN('PARA_NAME',p_para_name);
244 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
245 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
246 'cn.plsql.cn_api.chk_miss_char_para.error',
247 true);
248 end if;
249
250 -- Error, check the msg level and add an error message to the
251 -- API message list
252 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
253 FND_MESSAGE.SET_NAME ('CN' , 'CN_MISS_PARAMETER');
254 FND_MESSAGE.SET_TOKEN('PARA_NAME',p_para_name);
255 FND_MSG_PUB.Add;
256 END IF;
257 x_loading_status := 'CN_MISS_PARAMETER';
258 l_return_code := FND_API.G_TRUE;
259 END IF;
260
261 RETURN l_return_code;
262
263 END chk_miss_char_para;
264
265 --| ---------------------------------------------------------------------+
266 --| Function Name : chk_miss_num_para
267 --| Desc : Check for missing parameters -- Number type
268 --| ---------------------------------------------------------------------+
269 FUNCTION chk_miss_num_para
270 ( p_num_para IN NUMBER ,
271 p_para_name IN VARCHAR2 ,
272 p_loading_status IN VARCHAR2 ,
273 x_loading_status OUT NOCOPY VARCHAR2 )
274 RETURN VARCHAR2 IS
275 l_return_code VARCHAR2(1);
276 BEGIN
277 l_return_code := FND_API.G_FALSE;
278 x_loading_status := p_loading_status;
279 IF (p_num_para = FND_API.G_MISS_NUM) THEN
280 FND_MESSAGE.SET_NAME ('CN' , 'CN_MISS_PARAMETER');
281 FND_MESSAGE.SET_TOKEN('PARA_NAME',p_para_name);
282 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
283 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
284 'cn.plsql.cn_api.chk_miss_num_para.error',
285 true);
286 end if;
287
288 -- Error, check the msg level and add an error message to the
289 -- API message list
290 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
291 FND_MESSAGE.SET_NAME ('CN' , 'CN_MISS_PARAMETER');
292 FND_MESSAGE.SET_TOKEN('PARA_NAME',p_para_name);
293 FND_MSG_PUB.Add;
294 END IF;
295 x_loading_status := 'CN_MISS_PARAMETER';
296 l_return_code := FND_API.G_TRUE;
297 END IF;
298 RETURN l_return_code;
299 END chk_miss_num_para;
300
301 --| ---------------------------------------------------------------------+
302 --| Procedure Name : chk_miss_date_para
303 --| Desc : Check for missing parameters -- Date type
304 --| ---------------------------------------------------------------------+
305 FUNCTION chk_miss_date_para
306 ( p_date_para IN DATE ,
307 p_para_name IN VARCHAR2 ,
308 p_loading_status IN VARCHAR2 ,
309 x_loading_status OUT NOCOPY VARCHAR2 )
310 RETURN VARCHAR2 IS
311
312 l_return_code VARCHAR2(1);
313
314 BEGIN
315 l_return_code := FND_API.G_FALSE;
316 x_loading_status := p_loading_status;
317 IF (p_date_para = FND_API.G_MISS_DATE) THEN
318 FND_MESSAGE.SET_NAME ('CN' , 'CN_MISS_PARAMETER');
319 FND_MESSAGE.SET_TOKEN('PARA_NAME',p_para_name);
320 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
321 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
322 'cn.plsql.cn_api.chk_miss_date_para.error',
323 true);
324 end if;
325
326 -- Error, check the msg level and add an error message to the
327 -- API message list
328 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
329 FND_MESSAGE.SET_NAME ('CN' , 'CN_MISS_PARAMETER');
330 FND_MESSAGE.SET_TOKEN('PARA_NAME',p_para_name);
331 FND_MSG_PUB.Add;
332 END IF;
333 x_loading_status := 'CN_MISS_PARAMETER';
334 l_return_code := FND_API.G_TRUE;
335 END IF;
336
337 RETURN l_return_code;
338
339 END chk_miss_date_para;
340
341 --| ---------------------------------------------------------------------+
342 --| Function Name : chk_null_num_para
343 --| Desc : Check for Null parameters -- Number type
344 --| ---------------------------------------------------------------------+
345 FUNCTION chk_null_num_para
346 ( p_num_para IN NUMBER ,
347 p_obj_name IN VARCHAR2 ,
348 p_loading_status IN VARCHAR2 ,
349 x_loading_status OUT NOCOPY VARCHAR2 )
350 RETURN VARCHAR2 IS
351 l_return_code VARCHAR2(1);
352 BEGIN
353 l_return_code := FND_API.G_FALSE;
354 x_loading_status := p_loading_status;
355 IF (p_num_para IS NULL ) THEN
356 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_NULL');
357 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
358 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
359 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
360 'cn.plsql.cn_api.chk_null_num_para.error',
361 true);
362 end if;
363
364 -- Error, check the msg level and add an error message to the
365 -- API message list
366 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
367 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_NULL');
368 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
369 FND_MSG_PUB.Add;
370 END IF;
371 x_loading_status := 'CN_CANNOT_NULL';
372 l_return_code := FND_API.G_TRUE;
373 END IF;
374 RETURN l_return_code;
375 END chk_null_num_para;
376
377 --| ---------------------------------------------------------------------+
378 --| Function Name : chk_null_char_para
379 --| Desc : Check for Null parameters -- Character type
380 --| ---------------------------------------------------------------------+
381 FUNCTION chk_null_char_para
382 ( p_char_para IN VARCHAR2 ,
383 p_obj_name IN VARCHAR2 ,
384 p_loading_status IN VARCHAR2 ,
385 x_loading_status OUT NOCOPY VARCHAR2 )
386 RETURN VARCHAR2 IS
387 l_return_code VARCHAR2(1);
388 BEGIN
389 l_return_code := FND_API.G_FALSE;
390 x_loading_status := p_loading_status;
391 IF (p_char_para IS NULL ) THEN
392 Fnd_message.SET_NAME ('CN' , 'CN_CANNOT_NULL');
393 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
394 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
395 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
396 'cn.plsql.cn_api.chk_null_char_para.error',
397 true);
398 end if;
399
400 -- Error, check the msg level and add an error message to the
401 -- API message list
402 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
403 Fnd_message.SET_NAME ('CN' , 'CN_CANNOT_NULL');
404 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
405 FND_MSG_PUB.Add;
406 END IF;
407 x_loading_status := 'CN_CANNOT_NULL';
408 l_return_code := FND_API.G_TRUE;
409 END IF;
410 RETURN l_return_code;
411 END chk_null_char_para;
412
413 --| ---------------------------------------------------------------------+
414 --| Function Name : chk_null_date_para
415 --| Desc : Check for Null parameters -- Date type
416 --| ---------------------------------------------------------------------+
417 FUNCTION chk_null_date_para
418 ( p_date_para IN DATE ,
419 p_obj_name IN VARCHAR2 ,
420 p_loading_status IN VARCHAR2 ,
421 x_loading_status OUT NOCOPY VARCHAR2 )
422 RETURN VARCHAR2 IS
423 l_return_code VARCHAR2(1);
424 BEGIN
425 l_return_code := FND_API.G_FALSE;
426 x_loading_status := p_loading_status;
427 IF (p_date_para IS NULL ) THEN
428 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_NULL');
429 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
430 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
431 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
432 'cn.plsql.cn_api.chk_null_date_para.error',
433 true);
434 end if;
435
436 -- Error, check the msg level and add an error message to the
437 -- API message list
438 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
439 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_NULL');
440 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
441 FND_MSG_PUB.Add;
442 END IF;
443 x_loading_status := 'CN_CANNOT_NULL';
444 l_return_code := FND_API.G_TRUE;
445 END IF;
446 RETURN l_return_code;
447 END chk_null_date_para;
448
449 --| ---------------------------------------------------------------------+
450 --| Function Name : chk_miss_null_num_para
451 --| Desc : Check for Missing or Null parameters -- Number type
452 --| ---------------------------------------------------------------------+
453 FUNCTION chk_miss_null_num_para
454 ( p_num_para IN NUMBER ,
455 p_obj_name IN VARCHAR2 ,
456 p_loading_status IN VARCHAR2 ,
457 x_loading_status OUT NOCOPY VARCHAR2 )
458 RETURN VARCHAR2 IS
459 l_return_code VARCHAR2(1);
460 BEGIN
461 l_return_code := FND_API.G_FALSE;
462 x_loading_status := p_loading_status;
463 IF ((p_num_para IS NULL) OR (p_num_para = FND_API.G_MISS_NUM)) THEN
464 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_MISS_OR_NULL');
465 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
466 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
467 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
468 'cn.plsql.cn_api.chk_miss_null_num_para.error',
469 true);
470 end if;
471
472 -- Error, check the msg level and add an error message to the
473 -- API message list
474 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
475 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_MISS_OR_NULL');
476 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
477 FND_MSG_PUB.Add;
478 END IF;
479 x_loading_status := 'CN_CANNOT_MISS_OR_NULL';
480 l_return_code := FND_API.G_TRUE;
481 END IF;
482 RETURN l_return_code;
483 END chk_miss_null_num_para;
484
485 --| ---------------------------------------------------------------------+
486 --| Function Name : chk_miss_null_char_para
487 --| Desc : Check for Missing or Null parameters -- Character type
488 --| ---------------------------------------------------------------------+
489 FUNCTION chk_miss_null_char_para
490 ( p_char_para IN VARCHAR2 ,
491 p_obj_name IN VARCHAR2 ,
492 p_loading_status IN VARCHAR2 ,
493 x_loading_status OUT NOCOPY VARCHAR2 )
494 RETURN VARCHAR2 IS
495 l_return_code VARCHAR2(1);
496 BEGIN
497 l_return_code := FND_API.G_FALSE;
498 x_loading_status := p_loading_status;
499 IF ((p_char_para IS NULL ) OR (p_char_para = FND_API.G_MISS_CHAR)) THEN
500 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_MISS_OR_NULL');
501 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
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_api.chk_miss_null_char_para.error',
505 true);
506 end if;
507
508 -- Error, check the msg level and add an error message to the
509 -- API message list
510 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
511 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_MISS_OR_NULL');
512 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
513 FND_MSG_PUB.Add;
514 END IF;
515 x_loading_status := 'CN_CANNOT_MISS_OR_NULL';
516 l_return_code := FND_API.G_TRUE;
517 END IF;
518 RETURN l_return_code;
519 END chk_miss_null_char_para;
520
521 --| ---------------------------------------------------------------------+
522 --| Function Name : chk_miss_null_date_para
523 --| Desc : Check for Missing or Null parameters -- Date type
524 --| ---------------------------------------------------------------------+
525 FUNCTION chk_miss_null_date_para
526 ( p_date_para IN DATE ,
527 p_obj_name IN VARCHAR2 ,
528 p_loading_status IN VARCHAR2 ,
529 x_loading_status OUT NOCOPY VARCHAR2 )
530 RETURN VARCHAR2 IS
531 l_return_code VARCHAR2(1);
532 BEGIN
533 l_return_code := FND_API.G_FALSE;
534 x_loading_status := p_loading_status;
535 IF ((p_date_para IS NULL ) OR (p_date_para = FND_API.G_MISS_DATE)) THEN
536 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_MISS_OR_NULL');
537 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
538 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
539 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
540 'cn.plsql.cn_api.chk_miss_null_date_para.error',
541 true);
542 end if;
543
544 -- Error, check the msg level and add an error message to the
545 -- API message list
546 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
547 THEN
548 FND_MESSAGE.SET_NAME ('CN' , 'CN_CANNOT_MISS_OR_NULL');
549 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
550 FND_MSG_PUB.Add;
551 END IF;
552 x_loading_status := 'CN_CANNOT_MISS_OR_NULL';
553 l_return_code := FND_API.G_TRUE;
554 END IF;
555 RETURN l_return_code;
556 END chk_miss_null_date_para;
557
558 --| ---------------------------------------------------------------------+
559 --| Function Name : pe_num_field_must_null
560 --| Desc : Check for Field (Number Type) must be Null
561 --| ---------------------------------------------------------------------+
562 FUNCTION pe_num_field_must_null
563 ( p_num_field IN NUMBER ,
564 p_pe_type IN VARCHAR2 ,
565 p_obj_name IN VARCHAR2 ,
566 p_token1 IN VARCHAR2 ,
567 p_token2 IN VARCHAR2 ,
568 p_token3 IN VARCHAR2 ,
569 p_loading_status IN VARCHAR2 ,
570 x_loading_status OUT NOCOPY VARCHAR2 )
571 RETURN VARCHAR2 IS
572 l_return_code VARCHAR2(1);
573 BEGIN
574 l_return_code := FND_API.G_TRUE;
575 x_loading_status := p_loading_status;
576 IF (p_num_field IS NOT NULL ) THEN
577 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_FIELD_MUST_NULL');
578 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
579 FND_MESSAGE.SET_TOKEN('PLAN_TYPE',
580 get_lkup_meaning(p_pe_type,'QUOTA_TYPE'));
581 FND_MESSAGE.SET_TOKEN('TOKEN1',p_token1);
582 FND_MESSAGE.SET_TOKEN('TOKEN2',p_token2);
583 FND_MESSAGE.SET_TOKEN('TOKEN3',p_token3);
584 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
585 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
586 'cn.plsql.cn_api.pe_num_field_must_null.error',
587 true);
588 end if;
589
590 -- Error, check the msg level and add an error message to the
591 -- API message list
592 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
593 THEN
594 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_FIELD_MUST_NULL');
595 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
596 FND_MESSAGE.SET_TOKEN('PLAN_TYPE',
597 get_lkup_meaning(p_pe_type,'QUOTA_TYPE'));
598 FND_MESSAGE.SET_TOKEN('TOKEN1',p_token1);
599 FND_MESSAGE.SET_TOKEN('TOKEN2',p_token2);
600 FND_MESSAGE.SET_TOKEN('TOKEN3',p_token3);
601 FND_MSG_PUB.Add;
602 END IF;
603 x_loading_status := 'CN_PE_FIELD_MUST_NULL';
604 l_return_code := FND_API.G_FALSE;
605 END IF;
606 RETURN l_return_code;
607 END pe_num_field_must_null;
608
609 --| ---------------------------------------------------------------------+
610 --| Function Name : pe_char_field_must_null
611 --| Desc : Check for Field (Char Type) must be Null
612 --| ---------------------------------------------------------------------+
613 FUNCTION pe_char_field_must_null
614 ( p_char_field IN VARCHAR2 ,
615 p_pe_type IN VARCHAR2 ,
616 p_obj_name IN VARCHAR2 ,
617 p_token1 IN VARCHAR2 ,
618 p_token2 IN VARCHAR2 ,
619 p_token3 IN VARCHAR2 ,
620 p_loading_status IN VARCHAR2 ,
621 x_loading_status OUT NOCOPY VARCHAR2 )
622 RETURN VARCHAR2 IS
623 l_return_code VARCHAR2(1);
624 BEGIN
625 l_return_code := FND_API.G_TRUE;
626 x_loading_status := p_loading_status;
627 IF (p_char_field IS NOT NULL ) THEN
628 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_FIELD_MUST_NULL');
629 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
630 FND_MESSAGE.SET_TOKEN('PLAN_TYPE',
631 get_lkup_meaning(p_pe_type,'QUOTA_TYPE'));
632 FND_MESSAGE.SET_TOKEN('TOKEN1',p_token1);
633 FND_MESSAGE.SET_TOKEN('TOKEN2',p_token2);
634 FND_MESSAGE.SET_TOKEN('TOKEN3',p_token3);
635
636 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
637 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
638 'cn.plsql.cn_api.pe_char_field_must_null.error',
639 true);
640 end if;
641
642 -- Error, check the msg level and add an error message to the
643 -- API message list
644 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
645 THEN
646 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_FIELD_MUST_NULL');
647 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
648 FND_MESSAGE.SET_TOKEN('PLAN_TYPE',
649 get_lkup_meaning(p_pe_type,'QUOTA_TYPE'));
650 FND_MESSAGE.SET_TOKEN('TOKEN1',p_token1);
651 FND_MESSAGE.SET_TOKEN('TOKEN2',p_token2);
652 FND_MESSAGE.SET_TOKEN('TOKEN3',p_token3);
653 FND_MSG_PUB.Add;
654 END IF;
655 x_loading_status := 'CN_PE_FIELD_MUST_NULL';
656 l_return_code := FND_API.G_FALSE;
657 END IF;
658 RETURN l_return_code;
659 END pe_char_field_must_null;
660
661 --| ---------------------------------------------------------------------+
662 --| Function Name : pe_num_field_cannot_null
663 --| Desc : Check for Field (Number Type) can not be Null
664 --| ---------------------------------------------------------------------+
665 FUNCTION pe_num_field_cannot_null
666 ( p_num_field IN NUMBER ,
667 p_pe_type IN VARCHAR2 ,
668 p_obj_name IN VARCHAR2 ,
669 p_token1 IN VARCHAR2 ,
670 p_token2 IN VARCHAR2 ,
671 p_token3 IN VARCHAR2 ,
672 p_loading_status IN VARCHAR2 ,
673 x_loading_status OUT NOCOPY VARCHAR2 )
674 RETURN VARCHAR2 IS
675 l_return_code VARCHAR2(1);
676 BEGIN
677 l_return_code := FND_API.G_TRUE;
678 x_loading_status := p_loading_status;
679 IF (p_num_field IS NULL ) THEN
680 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_FIELD_CANNOT_NULL');
681 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
682 FND_MESSAGE.SET_TOKEN('PLAN_TYPE',
683 get_lkup_meaning(p_pe_type,'QUOTA_TYPE'));
684 FND_MESSAGE.SET_TOKEN('TOKEN1',p_token1);
685 FND_MESSAGE.SET_TOKEN('TOKEN2',p_token2);
686 FND_MESSAGE.SET_TOKEN('TOKEN3',p_token3);
687
688 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
689 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
690 'cn.plsql.cn_api.pe_num_field_cannot_null.error',
691 true);
692 end if;
693
694 -- Error, check the msg level and add an error message to the
695 -- API message list
696 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
697 THEN
698 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_FIELD_CANNOT_NULL');
699 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
700 FND_MESSAGE.SET_TOKEN('PLAN_TYPE',
701 get_lkup_meaning(p_pe_type,'QUOTA_TYPE'));
702 FND_MESSAGE.SET_TOKEN('TOKEN1',p_token1);
703 FND_MESSAGE.SET_TOKEN('TOKEN2',p_token2);
704 FND_MESSAGE.SET_TOKEN('TOKEN3',p_token3);
705 FND_MSG_PUB.Add;
706 END IF;
707 x_loading_status := 'CN_PE_FIELD_CANNOT_NULL';
708 l_return_code := FND_API.G_FALSE;
709 END IF;
710 RETURN l_return_code;
711 END pe_num_field_cannot_null;
712
713 --| ---------------------------------------------------------------------+
714 --| Function Name : pe_char_field_cannot_null
715 --| Desc : Check for Field (Char Type) can not be Null
716 --| ---------------------------------------------------------------------+
717 FUNCTION pe_char_field_cannot_null
718 ( p_char_field IN VARCHAR2 ,
719 p_pe_type IN VARCHAR2 ,
720 p_obj_name IN VARCHAR2 ,
721 p_token1 IN VARCHAR2 ,
722 p_token2 IN VARCHAR2 ,
723 p_token3 IN VARCHAR2 ,
724 p_loading_status IN VARCHAR2 ,
725 x_loading_status OUT NOCOPY VARCHAR2 )
726 RETURN VARCHAR2 IS
727 l_return_code VARCHAR2(1);
728 BEGIN
729 l_return_code := FND_API.G_TRUE;
730 x_loading_status := p_loading_status;
731 IF (p_char_field IS NULL ) THEN
732 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_FIELD_CANNOT_NULL');
733 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
734 FND_MESSAGE.SET_TOKEN('PLAN_TYPE',
735 get_lkup_meaning(p_pe_type,'QUOTA_TYPE'));
736 FND_MESSAGE.SET_TOKEN('TOKEN1',p_token1);
737 FND_MESSAGE.SET_TOKEN('TOKEN2',p_token2);
738 FND_MESSAGE.SET_TOKEN('TOKEN3',p_token3);
739
740 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
741 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
742 'cn.plsql.cn_api.pe_char_field_cannot_null.error',
743 true);
744 end if;
745
746 -- Error, check the msg level and add an error message to the
747 -- API message list
748 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
749 THEN
750 FND_MESSAGE.SET_NAME ('CN' , 'CN_PE_FIELD_CANNOT_NULL');
751 FND_MESSAGE.SET_TOKEN('OBJ_NAME',p_obj_name);
752 FND_MESSAGE.SET_TOKEN('PLAN_TYPE',
753 get_lkup_meaning(p_pe_type,'QUOTA_TYPE'));
754 FND_MESSAGE.SET_TOKEN('TOKEN1',p_token1);
755 FND_MESSAGE.SET_TOKEN('TOKEN2',p_token2);
756 FND_MESSAGE.SET_TOKEN('TOKEN3',p_token3);
757 FND_MSG_PUB.Add;
758 END IF;
759 x_loading_status := 'CN_PE_FIELD_CANNOT_NULL';
760 l_return_code := FND_API.G_FALSE;
761 END IF;
762 RETURN l_return_code;
763 END pe_char_field_cannot_null;
764 --| ---------------------------------------------------------------------+
765 --| Function Name : get_cp_name
766 --| Desc : Pass in comp plan id then return comp plan name
767 --| ---------------------------------------------------------------------+
768 FUNCTION get_cp_name( p_comp_plan_id NUMBER)
769 RETURN cn_comp_plans.name%TYPE IS
770
771 l_comp_plan_name cn_comp_plans.name%TYPE;
772
773 BEGIN
774 SELECT name
775 INTO l_comp_plan_name
776 FROM cn_comp_plans_all
777 WHERE comp_plan_id = p_comp_plan_id;
778
779 RETURN l_comp_plan_name;
780
781 EXCEPTION
782 WHEN no_data_found THEN
783 RETURN NULL;
784 END get_cp_name;
785
786 --| ---------------------------------------------------------------------+
787 --| Function Name : get_cp_id
788 --| Desc : Pass in comp plan name then return comp plan id
789 --| ---------------------------------------------------------------------+
790 FUNCTION get_cp_id( p_comp_plan_name VARCHAR2, p_org_id NUMBER)
791 RETURN cn_comp_plans.comp_plan_id%TYPE IS
792
793 l_comp_plan_id cn_comp_plans.comp_plan_id%TYPE;
794
795 BEGIN
796 SELECT comp_plan_id
797 INTO l_comp_plan_id
798 FROM cn_comp_plans_all
799 WHERE name = p_comp_plan_name
800 AND org_id = p_org_id;
801
802 RETURN l_comp_plan_id;
803
804 EXCEPTION
805 WHEN no_data_found THEN
806 RETURN NULL;
807 END get_cp_id;
808
809 --| ---------------------------------------------------------------------+
810 --| Function Name : get_pp_name
811 --| Desc : Pass in payment plan id then return payment plan name
812 --| ---------------------------------------------------------------------+
813 FUNCTION get_pp_name( p_pmt_plan_id NUMBER)
814 RETURN cn_pmt_plans.name%TYPE IS
815
816 l_pmt_plan_name cn_pmt_plans.name%TYPE;
817
818 BEGIN
819 SELECT name
820 INTO l_pmt_plan_name
821 FROM cn_pmt_plans_all
822 WHERE pmt_plan_id = p_pmt_plan_id;
823
824 RETURN l_pmt_plan_name;
825
826 EXCEPTION
827 WHEN no_data_found THEN
828 RETURN NULL;
829 END get_pp_name;
830
831 --| ---------------------------------------------------------------------+
832 --| Function Name : get_pp_id
833 --| Desc : Pass in payment plan name then return payment plan id
834 --| ---------------------------------------------------------------------+
835 FUNCTION get_pp_id( p_pmt_plan_name VARCHAR2, p_org_id NUMBER)
836 RETURN cn_pmt_plans.pmt_plan_id%TYPE IS
837
838 l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE;
839
840 BEGIN
841 SELECT pmt_plan_id
842 INTO l_pmt_plan_id
843 FROM cn_pmt_plans_all
844 WHERE name = p_pmt_plan_name
845 AND org_id = p_org_id;
846
847 RETURN l_pmt_plan_id;
848
849 EXCEPTION
850 WHEN no_data_found THEN
851 RETURN NULL;
852 END get_pp_id;
853
854 --| ---------------------------------------------------------------------+
855 --| Function Name : get_salesrep_name
856 --| Desc : Pass in salesrep id then return salesrep name
857 --| ---------------------------------------------------------------------+
858 FUNCTION get_salesrep_name( p_salesrep_id NUMBER, p_org_id NUMBER)
859 RETURN cn_salesreps.name%TYPE IS
860
861 l_salesrep_name cn_salesreps.name%TYPE;
862
863 BEGIN
864 SELECT name
865 INTO l_salesrep_name
866 FROM cn_salesreps
867 WHERE salesrep_id = p_salesrep_id
868 AND org_id = p_org_id;
869
870 RETURN l_salesrep_name;
871
872 EXCEPTION
873 WHEN no_data_found THEN
874 RETURN NULL;
875 END get_salesrep_name;
876
877 --| -----------------------------------------------------------------------+
878 --| Function Name : chk_and get_salesrep_id
879 --| Desc : Based on the employee number and salesrep type passed in,
880 --| Check if only one rec retrieve, if yes get the salesrep_id
881 --| ---------------------------------------------------------------------+
882 PROCEDURE chk_and_get_salesrep_id
883 ( p_emp_num IN VARCHAR2,
884 p_type IN VARCHAR2,
885 p_org_id IN NUMBER,
886 x_salesrep_id OUT NOCOPY cn_salesreps.salesrep_id%TYPE,
887 x_return_status OUT NOCOPY VARCHAR2,
888 x_loading_status OUT NOCOPY VARCHAR2,
889 p_show_message IN VARCHAR2) IS
890
891 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
892 l_emp_num cn_salesreps.employee_number%TYPE;
893
894 BEGIN
895 -- change for performance. Force to hit index on employee_number
896 -- Bug 1508614
897 -- Fixed l_emp_num
898
899 -- employee number or type cannot be null - fixed for bug 5075017
900
901 l_emp_num := upper(p_emp_num);
902
903 /* IF p_emp_num IS NULL THEN
904 SELECT salesrep_id
905 INTO l_salesrep_id
906 FROM cn_salesreps
907 WHERE employee_number IS NULL
908 AND org_id = p_org_id
909 AND ((type = p_type) OR (type IS NULL AND p_type IS NULL));
910 ELSE
911 */
912 SELECT /*+ first_rows */ salesrep_id
913 INTO l_salesrep_id
914 FROM cn_salesreps
915 WHERE upper(employee_number) = l_emp_num
916 AND org_id = p_org_id
917 AND type = p_type;
918 -- END IF;
919 x_salesrep_id := l_salesrep_id;
920 x_return_status := FND_API.G_RET_STS_SUCCESS;
921 x_loading_status := 'CN_SALESREP_FOUND';
922
923 EXCEPTION
924 WHEN no_data_found THEN
925 x_salesrep_id := NULL;
926 x_return_status := FND_API.G_RET_STS_ERROR;
927 x_loading_status := 'CN_SALESREP_NOT_FOUND';
928 IF (p_show_message = FND_API.G_TRUE) THEN
929 -- Error, check the msg level and add an error message to the
930 -- API message list
931 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
932 THEN
933 FND_MESSAGE.SET_NAME ('CN',x_loading_status);
934 FND_MSG_PUB.Add;
935 END IF;
936 END IF;
937 WHEN too_many_rows THEN
938 x_salesrep_id := NULL;
939 x_return_status := FND_API.G_RET_STS_ERROR;
940 x_loading_status := 'CN_SALESREP_TOO_MANY_ROWS';
941 IF (p_show_message = FND_API.G_TRUE) THEN
942 -- Error, check the msg level and add an error message to the
943 -- API message list
944 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
945 THEN
946 FND_MESSAGE.SET_NAME ('CN',x_loading_status);
947 FND_MSG_PUB.Add;
948 END IF;
949 END IF;
950 END chk_and_get_salesrep_id;
951
952
953 --| -----------------------------------------------------------------------+
954 --| Function Name : get_salesrep_id
955 --| Desc : Pass in salesrep name and employee number then return salesrep id
956 --| ---------------------------------------------------------------------+
957 FUNCTION get_salesrep_id( p_salesrep_name VARCHAR2, p_emp_num VARCHAR2, p_org_id NUMBER)
958 RETURN cn_salesreps.salesrep_id%TYPE IS
959
960 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
961
962 BEGIN
963 SELECT salesrep_id
964 INTO l_salesrep_id
965 FROM cn_salesreps
966 WHERE name = p_salesrep_name AND employee_number = p_emp_num
967 AND org_id = p_org_id;
968
969 RETURN l_salesrep_id;
970
971 EXCEPTION
972 WHEN no_data_found THEN
973 RETURN NULL;
974 END get_salesrep_id;
975
976
977 -- --------------------------------------------------------------------------+
978 -- Function : date_range_overlap
979 -- Desc : Check if two set of dates (a_start_date,a_end_date) and
980 -- (b_start_date, b_end_date) are overlap or not.
981 -- Assuming
982 -- 1. a_start_date is not null and a_start_date > a_end_date
983 -- 2. b_start_date is not null and b_start_date > b_end_date
984 -- 3. both end_date can be open (null)
985 -- --------------------------------------------------------------------------+
986 FUNCTION date_range_overlap
987 (
988 a_start_date DATE,
989 a_end_date DATE,
990 b_start_date DATE,
991 b_end_date DATE
992 ) RETURN BOOLEAN IS
993
994 BEGIN
995
996 IF (a_end_date IS NOT NULL) THEN
997 IF (b_end_date IS NOT NULL) THEN
998 IF ((b_start_date BETWEEN a_start_date AND a_end_date) OR
999 (b_end_date BETWEEN a_start_date AND a_end_date) OR
1000 (a_start_date BETWEEN b_start_date AND b_end_date) OR
1001 (a_end_date BETWEEN b_start_date AND b_end_date)) THEN
1002 RETURN TRUE; -- overlap
1003 END IF;
1004 ELSE
1005 IF (b_start_date <= a_end_date) THEN
1006 RETURN TRUE; -- overlap
1007 END IF;
1008 END IF;
1009 ELSE
1010 IF (b_end_date IS NOT NULL) THEN
1011 IF (b_end_date >= a_start_date) THEN
1012 RETURN TRUE; -- overlap
1013 END IF;
1014 ELSE
1015 RETURN TRUE; -- overlap
1016 END IF;
1017 END IF;
1018
1019 RETURN FALSE; -- not overlap
1020
1021 END date_range_overlap;
1022
1023 -- ---------------------------------------------------------------------------+
1024 -- PROCEDURE : get_date_range_diff_action
1025 -- Desc : get the difference portion of the two date ranges
1026 -- Assuming
1027 -- 1. start_date_new is not null and start_date_new < end_date_new
1028 -- 2. start_date_old is not null and start_date_old < end_date_old
1029 -- 3. both end_date can be open (null)
1030 -- ---------------------------------------------------------------------------+
1031 PROCEDURE get_date_range_diff_action
1032 (
1033 start_date_new DATE,
1034 end_date_new DATE,
1035 start_date_old DATE,
1036 end_date_old DATE,
1037 x_date_range_action_tbl OUT NOCOPY date_range_action_tbl_type ) IS
1038
1039 l_counter NUMBER :=0;
1040
1041 BEGIN
1042
1043 IF cn_api.date_range_overlap
1044 (start_date_new,
1045 end_date_new,
1046 start_date_old,
1047 end_date_old) THEN
1048
1049 -- overlap exists
1050
1051 IF start_date_new > start_date_old THEN
1052
1053 l_counter := l_counter + 1;
1054 x_date_range_action_tbl(l_counter).start_date := start_date_old;
1055 x_date_range_action_tbl(l_counter).end_date := start_date_new - 1;
1056 x_date_range_action_tbl(l_counter).action_flag:= 'D';
1057
1058 -- clku, fixed compaison
1059 ELSIF start_date_new < start_date_old THEN
1060
1061 l_counter := l_counter + 1;
1062 x_date_range_action_tbl(l_counter).start_date := start_date_new;
1063 x_date_range_action_tbl(l_counter).end_date := start_date_old - 1;
1064 x_date_range_action_tbl(l_counter).action_flag:= 'I';
1065
1066 END IF;
1067
1068 IF end_date_new IS NULL AND end_date_old IS NULL THEN
1069
1070 NULL;
1071
1072 ELSIF end_date_new IS NULL AND end_date_old IS NOT NULL THEN
1073
1074 l_counter := l_counter + 1;
1075 x_date_range_action_tbl(l_counter).start_date := end_date_old + 1;
1076 x_date_range_action_tbl(l_counter).end_date := NULL;
1077 x_date_range_action_tbl(l_counter).action_flag:= 'I';
1078
1079 ELSIF end_date_new IS NOT NULL AND end_date_old IS NULL THEN
1080
1081 l_counter := l_counter + 1;
1082 x_date_range_action_tbl(l_counter).start_date := end_date_new + 1;
1083 x_date_range_action_tbl(l_counter).end_date := NULL;
1084 x_date_range_action_tbl(l_counter).action_flag:= 'D';
1085
1086 ELSE
1087
1088 IF end_date_new > end_date_old THEN
1089
1090 l_counter := l_counter + 1;
1091 x_date_range_action_tbl(l_counter).start_date := end_date_old + 1;
1092 x_date_range_action_tbl(l_counter).end_date := end_date_new;
1093 x_date_range_action_tbl(l_counter).action_flag:= 'I';
1094
1095 ELSIF end_date_new < end_date_old THEN
1096
1097 l_counter := l_counter + 1;
1098 x_date_range_action_tbl(l_counter).start_date := end_date_new + 1;
1099 x_date_range_action_tbl(l_counter).end_date := end_date_old;
1100 -- clku, fixed action
1101 x_date_range_action_tbl(l_counter).action_flag:= 'D';
1102
1103 END IF;
1104
1105 END IF;
1106
1107 ELSE
1108
1109 -- no overlap
1110 l_counter := l_counter + 1;
1111 x_date_range_action_tbl(l_counter).start_date := start_date_new ;
1112 x_date_range_action_tbl(l_counter).end_date := end_date_new;
1113 x_date_range_action_tbl(l_counter).action_flag:= 'I';
1114
1115 l_counter := l_counter + 1;
1116 x_date_range_action_tbl(l_counter).start_date := start_date_old;
1117 x_date_range_action_tbl(l_counter).end_date := end_date_old;
1118 x_date_range_action_tbl(l_counter).action_flag:= 'D';
1119
1120 END IF;
1121
1122 END get_date_range_diff_action;
1123
1124 -- ---------------------------------------------------------------------------+
1125 -- Function : date_range_within
1126 -- Desc : Check if (a_start_date,a_end_date) is within (b_start_date, b_end_date)
1127 -- Assuming
1128 -- 1. a_start_date is not null and a_start_date > a_end_date
1129 -- 2. b_start_date is not null and b_start_date > b_end_date
1130 -- 3. both end_date can be open (null)
1131 -- ---------------------------------------------------------------------------+
1132 FUNCTION date_range_within
1133 (
1134 a_start_date DATE,
1135 a_end_date DATE,
1136 b_start_date DATE,
1137 b_end_date DATE
1138 ) RETURN BOOLEAN IS
1139
1140 BEGIN
1141 IF (b_end_date IS NOT NULL) THEN
1142 IF (a_end_date IS NOT NULL) THEN
1143 IF ((a_start_date BETWEEN b_start_date AND b_end_date) AND
1144 (a_end_date BETWEEN b_start_date AND b_end_date)) THEN
1145 RETURN TRUE; -- within
1146 END IF;
1147 ELSE
1148 RETURN FALSE; -- not within
1149 END IF;
1150 ELSE
1151 IF (a_start_date >= b_start_date) THEN
1152 RETURN TRUE; -- within
1153 END IF;
1154 END IF;
1155
1156 RETURN FALSE; -- not within
1157
1158 END date_range_within;
1159
1160 --| ---------------------------------------------------------------------=
1161 --| Procedure Name : invalid_date_range
1162 --| Desc : Check if date range is invalid
1163 --| ---------------------------------------------------------------------=
1164 FUNCTION invalid_date_range
1165 ( p_start_date IN DATE ,
1166 p_end_date IN DATE ,
1167 p_end_date_nullable IN VARCHAR2,
1168 p_loading_status IN VARCHAR2,
1169 x_loading_status OUT NOCOPY VARCHAR2,
1170 p_show_message IN VARCHAR2)
1171 RETURN VARCHAR2 IS
1172
1173 l_return_code VARCHAR2(1);
1174
1175 BEGIN
1176 l_return_code := FND_API.G_FALSE;
1177 x_loading_status := p_loading_status;
1178 IF p_start_date IS NULL THEN
1179 x_loading_status := 'CN_START_DATE_CANNOT_NULL';
1180 l_return_code := FND_API.G_TRUE;
1181 ELSIF p_start_date = FND_API.G_MISS_DATE THEN
1182 x_loading_status := 'CN_START_DATE_CANNOT_MISSING';
1183 l_return_code := FND_API.G_TRUE;
1184 ELSIF p_end_date IS NULL AND p_end_date_nullable <> FND_API.G_TRUE THEN
1185 x_loading_status := 'CN_END_DATE_CANNOT_NULL';
1186 l_return_code := FND_API.G_TRUE;
1187 ELSIF p_end_date = FND_API.G_MISS_DATE
1188 AND p_end_date_nullable <> FND_API.G_TRUE THEN
1189 x_loading_status := 'CN_END_DATE_CANNOT_MISSING';
1190 l_return_code := FND_API.G_TRUE;
1191 ELSIF p_end_date IS NOT NULL AND p_start_date > p_end_date THEN
1192 x_loading_status := 'CN_INVALID_DATE_RANGE';
1193 l_return_code := FND_API.G_TRUE;
1194 ELSE
1195 l_return_code := FND_API.G_FALSE;
1196 END IF;
1197 IF (l_return_code = FND_API.g_true AND p_show_message = fnd_api.G_TRUE) THEN
1198 -- Error, check the msg level and add an error message to the
1199 -- API message list
1200 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1201 THEN
1202 FND_MESSAGE.SET_NAME ('CN',x_loading_status);
1203 FND_MSG_PUB.Add;
1204 END IF;
1205 END IF;
1206
1207 RETURN l_return_code;
1208
1209 END invalid_date_range;
1210 --| ---------------------------------------------------------------------=
1211 --| Function Name : get_role_id
1212 --| Desc : Get the role id using the role name
1213 --| ---------------------------------------------------------------------=
1214 FUNCTION get_role_id ( p_role_name VARCHAR2 )
1215 RETURN cn_roles.role_id%TYPE IS
1216
1217 l_role_id cn_roles.role_id%TYPE;
1218
1219 BEGIN
1220 -- added uppers to avoid FTS for bug 5075017
1221 SELECT role_id
1222 INTO l_role_id
1223 FROM cn_roles
1224 WHERE Upper(name) = Upper(p_role_name) ;
1225
1226 RETURN l_role_id;
1227
1228 EXCEPTION
1229 WHEN no_data_found THEN
1230 RETURN NULL;
1231
1232 END get_role_id;
1233 --| ---------------------------------------------------------------------=
1234 --| Function Name : get_role_name
1235 --| Desc : Get the role name using the role id
1236 --| ---------------------------------------------------------------------=
1237 FUNCTION get_role_name ( p_role_id VARCHAR2 )
1238 RETURN cn_roles.name%TYPE IS
1239
1240 l_role_name cn_roles.name%TYPE;
1241
1242 BEGIN
1243 SELECT name
1244 INTO l_role_name
1245 FROM cn_roles
1246 WHERE role_id = p_role_id ;
1247
1248 RETURN l_role_name;
1249
1250 EXCEPTION
1251 WHEN no_data_found THEN
1252 RETURN NULL;
1253
1254 END get_role_name;
1255
1256 -- --------------------------------------------------------------------------=
1257 -- Function : get_srp_role_id
1258 -- Desc : get the srp_role_id if it exists in cn_srp_roles
1259 -- --------------------------------------------------------------------------=
1260 FUNCTION get_srp_role_id
1261 (p_emp_num IN cn_salesreps.employee_number%type,
1262 p_type IN cn_salesreps.TYPE%type,
1263 p_role_name IN cn_roles.name%type,
1264 p_start_date IN cn_srp_roles.start_date%type,
1265 p_end_date IN cn_srp_roles.end_date%TYPE,
1266 p_org_id IN cn_salesreps.org_id%type
1267 ) RETURN cn_srp_roles.srp_role_id%TYPE IS
1268
1269 CURSOR l_cur(l_salesrep_id cn_srp_roles.salesrep_id%TYPE,
1270 l_role_id cn_srp_roles.role_id%TYPE,
1271 l_start_date cn_srp_roles.start_date%TYPE,
1272 l_end_date cn_srp_roles.end_date%TYPE) IS
1273 SELECT srp_role_id
1274 FROM cn_srp_roles
1275 WHERE role_id = l_role_id AND
1276 salesrep_id = l_salesrep_id AND
1277 start_date = l_start_date AND
1278 org_id = p_org_id AND
1279 ((end_date = l_end_date) OR
1280 (end_date IS NULL AND l_end_date IS NULL));
1281
1282 l_rec l_cur%ROWTYPE;
1283 l_role_id cn_srp_roles.role_id%TYPE;
1284 l_salesrep_id cn_srp_roles.salesrep_id%TYPE;
1285 l_return_status VARCHAR2(2000);
1286 l_loading_status VARCHAR2(2000);
1287
1288 BEGIN
1289
1290 l_role_id := cn_api.get_role_id(p_role_name);
1291 chk_and_get_salesrep_id
1292 (p_emp_num ,
1293 p_type,
1294 p_org_id,
1295 l_salesrep_id,
1296 l_return_status,
1297 l_loading_status,
1298 fnd_api.g_false);
1299
1300 OPEN l_cur(l_salesrep_id, l_role_id, p_start_date, p_end_date);
1301 FETCH l_cur INTO l_rec;
1302 IF (l_cur%notfound) THEN
1303 CLOSE l_cur;
1304 RETURN NULL;
1305 ELSE
1306 CLOSE l_cur;
1307 RETURN l_rec.srp_role_id;
1308 END IF;
1309
1310 END get_srp_role_id;
1311
1312 -- --------------------------------------------------------------------------=
1313 -- Function : get_role_plan_id
1314 -- Desc : get the role_plan_id if it exists in cn_roles
1315 -- --------------------------------------------------------------------------=
1316 FUNCTION get_role_plan_id
1317 (
1318 p_role_name IN VARCHAR2,
1319 p_comp_plan_name IN VARCHAR2,
1320 p_start_date IN DATE,
1321 p_end_date IN DATE,
1322 p_org_id IN NUMBER
1323 ) RETURN cn_role_plans.role_plan_id%TYPE IS
1324
1325 CURSOR l_cur(l_role_id cn_role_plans.role_id%TYPE,
1326 l_comp_plan_id cn_role_plans.comp_plan_id%TYPE,
1327 l_start_date cn_role_plans.start_date%TYPE,
1328 l_end_date cn_role_plans.end_date%TYPE) IS
1329 SELECT role_plan_id
1330 FROM cn_role_plans
1331 WHERE role_id = l_role_id AND
1332 comp_plan_id = l_comp_plan_id AND
1333 start_date = l_start_date AND
1334 ((end_date = l_end_date) OR
1335 (end_date IS NULL AND l_end_date IS NULL));
1336
1337 l_rec l_cur%ROWTYPE;
1338 l_role_id cn_role_plans.role_id%TYPE;
1339 l_comp_plan_id cn_role_plans.comp_plan_id%TYPE;
1340
1341 BEGIN
1342
1343 l_role_id := cn_api.get_role_id(p_role_name);
1344 l_comp_plan_id := cn_api.get_cp_id(p_comp_plan_name, p_org_id);
1345
1346 OPEN l_cur(l_role_id, l_comp_plan_id, p_start_date, p_end_date);
1347 FETCH l_cur INTO l_rec;
1348 IF (l_cur%notfound) THEN
1349 CLOSE l_cur;
1350 RETURN NULL;
1351 ELSE
1352 CLOSE l_cur;
1353 RETURN l_rec.role_plan_id;
1354 END IF;
1355
1356 END get_role_plan_id;
1357
1358 -- --------------------------------------------------------------------------=
1359 -- Function : get_role_pmt_plan_id
1360 -- Desc : get the role_pmt_plan_id if it exists in cn_roles_pmt_plans
1361 -- --------------------------------------------------------------------------=
1362 FUNCTION get_role_pmt_plan_id
1363 (
1364 p_role_name IN VARCHAR2,
1365 p_pmt_plan_name IN VARCHAR2,
1366 p_start_date IN DATE,
1367 p_end_date IN DATE,
1368 p_org_id IN NUMBER
1369 ) RETURN cn_role_pmt_plans.role_pmt_plan_id%TYPE IS
1370
1371 CURSOR l_cur(l_role_id cn_role_pmt_plans.role_id%TYPE,
1372 l_pmt_plan_id cn_role_pmt_plans.pmt_plan_id%TYPE,
1373 l_start_date cn_role_pmt_plans.start_date%TYPE,
1374 l_end_date cn_role_pmt_plans.end_date%TYPE) IS
1375 SELECT role_pmt_plan_id
1376 FROM cn_role_pmt_plans
1377 WHERE role_id = l_role_id AND
1378 pmt_plan_id = l_pmt_plan_id AND
1379 start_date = l_start_date AND
1380 ((end_date = l_end_date) OR
1381 (end_date IS NULL AND l_end_date IS NULL));
1382
1383 l_rec l_cur%ROWTYPE;
1384 l_role_id cn_role_pmt_plans.role_id%TYPE;
1385 l_pmt_plan_id cn_role_pmt_plans.pmt_plan_id%TYPE;
1386
1387 BEGIN
1388
1389 l_role_id := cn_api.get_role_id(p_role_name);
1390 l_pmt_plan_id := cn_api.get_pp_id(p_pmt_plan_name, p_org_id);
1391
1392 OPEN l_cur(l_role_id, l_pmt_plan_id, p_start_date, p_end_date);
1393 FETCH l_cur INTO l_rec;
1394 IF (l_cur%notfound) THEN
1395 CLOSE l_cur;
1396 RETURN NULL;
1397 ELSE
1398 CLOSE l_cur;
1399 RETURN l_rec.role_pmt_plan_id;
1400 END IF;
1401
1402 END get_role_pmt_plan_id;
1403
1404
1405 --| -----------------------------------------------------------------------=
1406 --| Function Name : get_srp_payee_assign_id
1407 --| Desc : Get the srp_payee_assign_id using the
1408 --| payee_id, salesrep_id, quota_id, start_date, end_date
1409 --| ---------------------------------------------------------------------=
1410 FUNCTION get_srp_payee_assign_id ( p_payee_id NUMBER,
1411 p_salesrep_id NUMBER,
1412 p_quota_id NUMBER,
1413 p_start_date DATE,
1414 p_end_date DATE,
1415 p_org_id NUMBER)
1416 RETURN cn_srp_payee_assigns.srp_payee_assign_id%TYPE IS
1417
1418 CURSOR get_srp_payee_assign_id_curs IS
1419 SELECT srp_payee_assign_id
1420 FROM cn_srp_payee_assigns
1421 WHERE payee_id = p_payee_id
1422 AND salesrep_id = p_salesrep_id
1423 AND org_id = p_org_id
1424 AND quota_id = p_quota_id
1425 AND start_date = p_start_date
1426 AND end_date = p_end_date ;
1427
1428 CURSOR get_srp_payee_assign_id_curs1 IS
1429 SELECT srp_payee_assign_id
1430 FROM cn_srp_payee_assigns
1431 WHERE payee_id = p_payee_id
1432 AND salesrep_id = p_salesrep_id
1433 AND org_id = p_org_id
1434 AND quota_id = p_quota_id
1435 AND start_date = p_start_date
1436 AND end_date IS NULL ;
1437
1438 l_srp_payee_assign_id cn_srp_payee_assigns.srp_payee_assign_id%TYPE;
1439
1440 BEGIN
1441
1442 IF p_end_date IS NOT NULL THEN
1443
1444 OPEN get_srp_payee_assign_id_curs;
1445 FETCH get_srp_payee_assign_id_curs INTO l_srp_payee_assign_id;
1446 CLOSE get_srp_payee_assign_id_curs;
1447 ELSE
1448 OPEN get_srp_payee_assign_id_curs1;
1449 FETCH get_srp_payee_assign_id_curs1 INTO l_srp_payee_assign_id;
1450 CLOSE get_srp_payee_assign_id_curs1;
1451 END IF;
1452
1453 RETURN l_srp_payee_assign_id;
1454
1455 END get_srp_payee_assign_id;
1456 --| ---------------------------------------------------------------------=
1457 --| Function Name : next_period
1458 --| ---------------------------------------------------------------------=
1459 FUNCTION next_period (p_end_date DATE, p_org_id NUMBER)
1460 RETURN cn_acc_period_statuses_v.end_date%TYPE IS
1461
1462 l_next_end_date cn_acc_period_statuses_v.end_date%TYPE;
1463
1464 BEGIN
1465
1466 SELECT MAX(end_date)
1467 INTO l_next_end_date
1468 FROM cn_acc_period_statuses_v
1469 WHERE period_status IN ('F', 'O')
1470 AND org_id = p_org_id;
1471
1472 IF trunc(l_next_end_date) > trunc(p_end_date) THEN
1473
1474 SELECT MIN(end_date)
1475 INTO l_next_end_date
1476 FROM cn_acc_period_statuses_v
1477 WHERE trunc(end_date) >= trunc(p_end_date)
1478 AND period_status IN ('F', 'O')
1479 AND org_id = p_org_id;
1480
1481 END IF;
1482
1483 RETURN l_next_end_date;
1484
1485 EXCEPTION
1486 WHEN no_data_found THEN
1487 RETURN NULL;
1488 END next_period;
1489
1490 FUNCTION get_pay_period(p_salesrep_id NUMBER,
1491 p_date DATE,
1492 p_org_id NUMBER )
1493 RETURN cn_commission_lines.pay_period_id%TYPE IS
1494
1495 l_pay_period_id cn_commission_lines.pay_period_id%TYPE;
1496
1497 BEGIN
1498
1499 SELECT a.period_id
1500 INTO l_pay_period_id
1501 FROM cn_period_statuses_all a, cn_srp_pay_groups_all b, cn_pay_groups_all c
1502 WHERE a.period_set_id = c.period_set_id
1503 AND a.org_id = p_org_id
1504 AND a.period_type_id = c.period_type_id
1505 AND b.pay_group_id = c.pay_group_id
1506 AND b.org_id = p_org_id
1507 AND c.org_id = p_org_id
1508 AND p_date BETWEEN a.start_date AND least(a.end_date, nvl(b.end_date,a.end_date))
1509 AND p_salesrep_id = b.salesrep_id
1510 AND p_date BETWEEN b.start_date AND nvl(b.end_date, p_date);
1511
1512 RETURN l_pay_period_id;
1513
1514 END get_pay_period;
1515
1516
1517 --| ---------------------------------------------------------------------=
1518 --| Function Name : Get Itd Flag
1519 --| ---------------------------------------------------------------------=
1520 FUNCTION get_itd_flag(p_calc_formula_id NUMBER)
1521 RETURN cn_calc_formulas.itd_flag%TYPE IS
1522
1523 l_itd_flag cn_calc_formulas.itd_flag%TYPE;
1524
1525 BEGIN
1526
1527 SELECT itd_flag
1528 INTO l_itd_flag
1529 FROM cn_calc_formulas_all
1530 WHERE calc_formula_id = p_calc_formula_id ;
1531
1532 RETURN l_itd_flag;
1533 EXCEPTION
1534 WHEN no_data_found THEN
1535 RETURN NULL;
1536
1537 END get_itd_flag;
1538
1539 --| -----------------------------------------------------------------------=
1540 --| Function Name : get_acc_period_id -- only get active periods
1541 --| ---------------------------------------------------------------------=
1542 FUNCTION get_acc_period_id( p_period_name VARCHAR2, p_org_id NUMBER)
1543 RETURN cn_periods.period_id%TYPE IS
1544
1545 l_period_id cn_periods.period_id%TYPE;
1546
1547 BEGIN
1548 SELECT period_id
1549 INTO l_period_id
1550 FROM cn_acc_period_statuses_v
1551 WHERE period_name = p_period_name
1552 AND org_id = p_org_id;
1553
1554 RETURN l_period_id;
1555
1556 EXCEPTION
1557 WHEN no_data_found THEN
1558 RETURN NULL;
1559 END get_acc_period_id;
1560
1561 --| -----------------------------------------------------------------------=
1562 --| Function Name : get_acc_period_name -- only get active periods
1563 --| ---------------------------------------------------------------------=
1564 FUNCTION get_acc_period_name( p_period_id NUMBER, p_org_id NUMBER)
1565 RETURN cn_periods.period_name%TYPE IS
1566
1567 l_period_name cn_periods.period_name%TYPE;
1568
1569 BEGIN
1570 SELECT period_name
1571 INTO l_period_name
1572 FROM cn_acc_period_statuses_v
1573 WHERE period_id = p_period_id
1574 AND org_id = p_org_id;
1575
1576 RETURN l_period_name;
1577
1578 EXCEPTION
1579 WHEN no_data_found THEN
1580 RETURN NULL;
1581 END get_acc_period_name;
1582 --| -----------------------------------------------------------------------=
1583 --| Function Name : get_quota_assign_id
1584 --| ---------------------------------------------------------------------=
1585 FUNCTION get_quota_assign_id( p_quota_id NUMBER,
1586 p_comp_plan_id NUMBER )
1587 RETURN cn_quota_assigns.quota_assign_id%TYPE IS
1588
1589 l_quota_assign_id cn_quota_assigns.quota_assign_id%TYPE;
1590
1591 BEGIN
1592 SELECT quota_assign_id INTO l_quota_assign_id
1593 FROM cn_quota_assigns_all
1594 WHERE quota_id = p_quota_id
1595 AND comp_plan_id = p_comp_plan_id;
1596
1597 RETURN l_quota_assign_id;
1598
1599 EXCEPTION
1600 WHEN no_data_found THEN
1601 RETURN NULL;
1602 END get_quota_assign_id ;
1603
1604 -- ---------------------------------------------------------------------------=
1605 -- PROCEDURE : get_date_range_diff
1606 -- Desc : get the difference portion of the two date ranges
1607 -- Assuming
1608 -- 1. a_start_date is not null and a_start_date < a_end_date
1609 -- 2. b_start_date is not null and b_start_date < b_end_date
1610 -- 3. both end_date can be open (null)
1611 -- --------------------------------------------------------------------------=
1612 PROCEDURE get_date_range_diff ( a_start_date DATE,
1613 a_end_date DATE,
1614 b_start_date DATE,
1615 b_end_date DATE,
1616 x_date_range_tbl OUT NOCOPY date_range_tbl_type ) IS
1617
1618 l_counter NUMBER := 0;
1619 BEGIN
1620
1621 IF cn_api.date_range_overlap( a_start_date, a_end_date,
1622 b_start_date, b_end_date )
1623 THEN -- there is overlap
1624 -- first, check the start dates
1625 IF a_start_date> b_start_date THEN
1626 l_counter := l_counter + 1;
1627 x_date_range_tbl(l_counter).start_date := b_start_date;
1628 x_date_range_tbl(l_counter).end_date := a_start_date-1;
1629 ELSIF a_start_date< b_start_date THEN
1630 l_counter := l_counter + 1;
1631 x_date_range_tbl(l_counter).start_date := a_start_date;
1632 x_date_range_tbl(l_counter).end_date := b_start_date-1;
1633 END IF;
1634
1635 -- second, check end dates
1636 IF a_end_date IS NULL AND b_end_date IS NULL THEN
1637 NULL;
1638 ELSIF (a_end_date IS NULL AND b_end_date IS NOT NULL ) THEN
1639 l_counter := l_counter + 1;
1640 x_date_range_tbl(l_counter).start_date := b_end_date + 1;
1641 x_date_range_tbl(l_counter).end_date := NULL;
1642 ELSIF (a_end_date IS NOT NULL AND b_end_date IS NULL ) THEN
1643 l_counter := l_counter + 1;
1644 x_date_range_tbl(l_counter).start_date := a_end_date + 1;
1645 x_date_range_tbl(l_counter).end_date := NULL;
1646 ELSE -- a_end_date IS NOT NULL AND b_end_date IS NOT NULL
1647 IF a_end_date > b_end_date THEN
1648 l_counter := l_counter + 1;
1649 x_date_range_tbl(l_counter).start_date := b_end_date + 1;
1650 x_date_range_tbl(l_counter).end_date := a_end_date;
1651 ELSIF a_end_date < b_end_date THEN
1652 l_counter := l_counter + 1;
1653 x_date_range_tbl(l_counter).start_date := a_end_date + 1;
1654 x_date_range_tbl(l_counter).end_date := b_end_date;
1655 END IF;
1656 END IF;
1657 ELSE -- there is no overlap
1658 l_counter := l_counter + 1;
1659 x_date_range_tbl(l_counter).start_date := a_start_date;
1660 x_date_range_tbl(l_counter).end_date := a_end_date;
1661
1662 l_counter := l_counter + 1;
1663 x_date_range_tbl(l_counter).start_date := b_start_date;
1664 x_date_range_tbl(l_counter).end_date := b_end_date;
1665 END IF;
1666 END get_date_range_diff ;
1667
1668 -- -------------------------------------------------------------------------+
1669 -- PROCEDURE : get_date_range_intersect
1670 -- Desc : get the intersection of two date ranges
1671 -- Assuming
1672 -- 1. a_start_date is not null and a_start_date < a_end_date
1673 -- 2. b_start_date is not null and b_start_date < b_end_date
1674 -- 3. both end_date can be open (null)
1675 -- 4. the two date ranges overlap
1676 -- -------------------------------------------------------------------------+
1677 PROCEDURE get_date_range_intersect
1678 (
1679 a_start_date DATE,
1680 a_end_date DATE,
1681 b_start_date DATE,
1682 b_end_date DATE,
1683 x_start_date OUT NOCOPY DATE,
1684 x_end_date OUT NOCOPY DATE) IS
1685
1686 BEGIN
1687
1688 IF a_start_date> b_start_date THEN
1689 x_start_date := a_start_date;
1690 ELSIF a_start_date<= b_start_date THEN
1691 x_start_date := b_start_date;
1692 END IF;
1693
1694 IF a_end_date IS NULL AND b_end_date IS NULL THEN
1695 x_end_date := NULL;
1696
1697 ELSIF (a_end_date IS NULL AND b_end_date IS NOT NULL ) THEN
1698 x_end_date := b_end_date;
1699
1700 ELSIF (a_end_date IS NOT NULL AND b_end_date IS NULL ) THEN
1701 x_end_date := a_end_date;
1702
1703 ELSE
1704 IF a_end_date > b_end_date THEN
1705 x_end_date := b_end_date;
1706 ELSIF a_end_date <= b_end_date THEN
1707 x_end_date := a_end_date;
1708 END IF;
1709 END IF;
1710
1711 END get_date_range_intersect;
1712
1713 -- ---------------------------------------------------------------------------=
1714 -- PROCEDURE : get_date_range_overlap
1715 -- Desc : get the overlap portion of the two date ranges
1716 -- Assuming
1717 -- 1. a_start_date is not null and a_start_date < a_end_date
1718 -- 2. b_start_date is not null and b_start_date < b_end_date
1719 -- 3. both end_date can be open (null)
1720 -- ---------------------------------------------------------------------------=
1721 PROCEDURE get_date_range_overlap (a_start_date DATE,
1722 a_end_date DATE,
1723 b_start_date DATE,
1724 b_end_date DATE,
1725 p_org_id NUMBER,
1726 x_date_range_tbl OUT NOCOPY date_range_tbl_type ) IS
1727
1728 l_start_date DATE;
1729 l_end_date DATE;
1730
1731 CURSOR l_last_date_cr IS
1732
1733 -- fixed query for bug 5075017
1734 SELECT MAX(end_date)
1735 FROM cn_acc_period_statuses_v
1736 WHERE period_status IN ('F', 'O')
1737 AND org_id = p_org_id;
1738
1739 BEGIN
1740 IF cn_api.date_range_overlap( a_start_date, a_end_date,
1741 b_start_date, b_end_date )
1742 THEN -- there is overlap
1743 -- first, check the start dates
1744 IF a_start_date> b_start_date THEN
1745 l_start_date := a_start_date;
1746 ELSIF a_start_date<= b_start_date THEN
1747 l_start_date := b_start_date;
1748 END IF;
1749
1750 -- second, check end dates
1751 IF a_end_date IS NULL AND b_end_date IS NULL THEN
1752
1753 OPEN l_last_date_cr;
1754 FETCH l_last_date_cr INTO l_end_date;
1755
1756 IF (l_last_date_cr%notfound) THEN
1757
1758 l_end_date := NULL;
1759
1760 END IF;
1761
1762 ELSIF (a_end_date IS NULL AND b_end_date IS NOT NULL ) THEN
1763 l_end_date := b_end_date;
1764 ELSIF (a_end_date IS NOT NULL AND b_end_date IS NULL ) THEN
1765 l_end_date := a_end_date;
1766 ELSE -- a_end_date IS NOT NULL AND b_end_date IS NOT NULL
1767 IF a_end_date > b_end_date THEN
1768 l_end_date := b_end_date;
1769 ELSIF a_end_date <= b_end_date THEN
1770 l_end_date := a_end_date;
1771 END IF;
1772 END IF;
1773
1774 x_date_range_tbl(1).start_date := l_start_date;
1775 x_date_range_tbl(1).end_date := l_end_date;
1776 END IF;
1777 END get_date_range_overlap;
1778
1779 -- ---------------------------------------------------------------------------=
1780 -- FUNCTION: get_acc_period_id
1781 -- Desc : get the accumulation period_id given the date
1782 -- If the date is null, will return the latest accumulation period
1783 -- with period_status = 'O'
1784 -- --------------------------------------------------------------------------=
1785 FUNCTION get_acc_period_id (p_date DATE, p_org_id NUMBER) RETURN NUMBER IS
1786 CURSOR l_date_period_csr IS
1787 SELECT period_id
1788 FROM cn_acc_period_statuses_v
1789 WHERE p_date BETWEEN start_date AND end_date
1790 AND org_id = p_org_id;
1791
1792 CURSOR l_null_date_period_csr IS
1793 SELECT MAX(period_id)
1794 FROM cn_acc_period_statuses_v
1795 WHERE period_status = 'O'
1796 AND org_id = p_org_id;
1797
1798 l_period_id NUMBER(15);
1799
1800 BEGIN
1801 IF p_date IS NOT NULL THEN
1802 OPEN l_date_period_csr;
1803 FETCH l_date_period_csr INTO l_period_id;
1804 CLOSE l_date_period_csr;
1805 END IF;
1806
1807 IF (l_period_id IS NULL) THEN
1808 OPEN l_null_date_period_csr;
1809 FETCH l_null_date_period_csr INTO l_period_id;
1810 CLOSE l_null_date_period_csr;
1811 END IF;
1812
1813 RETURN l_period_id;
1814 EXCEPTION WHEN OTHERS THEN
1815 IF l_date_period_csr%isopen THEN
1816 CLOSE l_date_period_csr;
1817 END IF;
1818
1819 IF l_null_date_period_csr%isopen THEN
1820 CLOSE l_null_date_period_csr;
1821 END IF;
1822
1823 RAISE;
1824
1825 END get_acc_period_id;
1826
1827
1828 -- ---------------------------------------------------------------------------=
1829 -- FUNCTION: get_acc_period_id_fo
1830 -- Desc : get the accumulation period_id given the date
1831 -- If the date is null, will return the first accumulation period
1832 -- with period_status = 'O'
1833 -- --------------------------------------------------------------------------=
1834 FUNCTION get_acc_period_id_fo (p_date DATE, p_org_id NUMBER) RETURN NUMBER IS
1835
1836 CURSOR l_date_period_csr IS
1837 SELECT period_id
1838 FROM cn_acc_period_statuses_v
1839 WHERE p_date BETWEEN start_date AND end_date
1840 AND org_id = p_org_id;
1841
1842 CURSOR l_null_date_period_csr IS
1843 SELECT MIN(period_id)
1844 FROM cn_acc_period_statuses_v
1845 WHERE period_status = 'O'
1846 AND org_id = p_org_id;
1847
1848 l_period_id NUMBER(15);
1849
1850 BEGIN
1851 IF p_date IS NOT NULL THEN
1852 OPEN l_date_period_csr;
1853 FETCH l_date_period_csr INTO l_period_id;
1854 CLOSE l_date_period_csr;
1855 END IF;
1856
1857 IF (l_period_id IS NULL) THEN
1858 OPEN l_null_date_period_csr;
1859 FETCH l_null_date_period_csr INTO l_period_id;
1860 CLOSE l_null_date_period_csr;
1861 END IF;
1862
1863 RETURN l_period_id;
1864 EXCEPTION WHEN OTHERS THEN
1865 IF l_date_period_csr%isopen THEN
1866 CLOSE l_date_period_csr;
1867 END IF;
1868
1869 IF l_null_date_period_csr%isopen THEN
1870 CLOSE l_null_date_period_csr;
1871 END IF;
1872
1873 RAISE;
1874
1875 END get_acc_period_id_fo;
1876
1877 --| -----------------------------------------------------------------------=
1878 --| Procedure Name : check_revenue_class_overlap
1879 --| Desc : Pass in Comp Plan ID
1880 --| pass in Comp Plan Name
1881 --| Note: Comented out the overlap check
1882 --| ---------------------------------------------------------------------=
1883 PROCEDURE check_revenue_class_overlap
1884 (
1885 p_comp_plan_id IN NUMBER,
1886 p_rc_overlap IN VARCHAR2,
1887 p_loading_status IN VARCHAR2,
1888 x_loading_status OUT NOCOPY VARCHAR2,
1889 x_return_status OUT NOCOPY VARCHAR2 ) IS
1890
1891 l_rev_class_total NUMBER := 0;
1892 l_rev_class_total_unique NUMBER := 0;
1893 l_comp_plan_name cn_comp_plans.name%TYPE;
1894 l_rc_overlap VARCHAR2(03);
1895 l_sum_trx_flag VARCHAR2(03); -- commented for bug 7655423 / uncommented for f port of 7330382
1896
1897 BEGIN
1898 x_return_status := FND_API.G_RET_STS_SUCCESS;
1899 x_loading_status := p_loading_status;
1900
1901 BEGIN
1902 SELECT name, Nvl(p_rc_overlap,allow_rev_class_overlap),sum_trx_flag -- commented for bug 7655423
1903 INTO l_comp_plan_name,l_rc_overlap,l_sum_trx_flag
1904 FROM cn_comp_plans_all
1905 WHERE comp_plan_id = p_comp_plan_id;
1906 EXCEPTION
1907 WHEN NO_DATA_FOUND THEN
1908 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1909 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_COMP_PLAN');
1910 FND_MSG_PUB.Add;
1911 END IF;
1912 x_loading_status := 'CN_INVALID_COMP_PLAN';
1913 x_return_status := FND_API.G_RET_STS_ERROR;
1914 END ;
1915
1916 IF l_rc_overlap = 'N' THEN
1917 -- The function below adds the message
1918 IF CN_COMP_PLANS_PKG.check_unique_rev_class
1919 (p_comp_plan_id,l_comp_plan_name,l_rc_overlap,l_sum_trx_flag) = FALSE THEN
1920 x_loading_status := 'PLN_PLAN_DUP_REV_CLASS';
1921 x_return_status := FND_API.G_RET_STS_ERROR;
1922 END IF ;
1923 END IF;
1924
1925 END check_revenue_class_overlap;
1926 --| -----------------------------------------------------------------------=
1927 --| Function Name : get_comp_group_name
1928 --| Desc : Pass in comp_group id then return comp_group name
1929 --| ---------------------------------------------------------------------=
1930 FUNCTION get_comp_group_name( p_comp_group_id NUMBER)
1931 RETURN cn_comp_groups.name%TYPE IS
1932
1933 l_comp_group_name cn_comp_groups.name%TYPE;
1934
1935 BEGIN
1936 SELECT name
1937 INTO l_comp_group_name
1938 FROM cn_comp_groups
1939 WHERE comp_group_id = p_comp_group_id;
1940
1941 RETURN l_comp_group_name;
1942
1943 EXCEPTION
1944 WHEN no_data_found THEN
1945 RETURN NULL;
1946 END get_comp_group_name;
1947
1948 --| -----------------------------------------------------------------------=
1949 --| Function Name : get_order_booked_date
1950 --| Desc : Pass in order header_id then return date order was booked (or NULL)
1951 --| ---------------------------------------------------------------------=
1952 /* See comment in file header (DEC-20-99)
1953 FUNCTION get_order_booked_date(p_order_header_id NUMBER) RETURN DATE IS
1954 l_booked_flag VARCHAR2(1);
1955 l_booked_date DATE;
1956 BEGIN
1957 -- This is what we need to call, but we can't - it violates the function pragma
1958 -- Because of this , I have copied in the code out of this procedure
1959 -- oe_header_status_pub.get_booked_status(p_order_header_id,
1960 -- l_booked_flag,
1961 -- l_booked_date);
1962 SELECT nvl(booked_flag, 'N')
1963 INTO l_booked_flag
1964 FROM aso_i_oe_order_headers_v
1965 WHERE header_id = p_order_header_id;
1966
1967 IF l_booked_flag = 'Y' THEN
1968 SELECT end_date
1969 INTO l_booked_date
1970 FROM wf_item_activity_statuses
1971 WHERE item_type = OE_GLOBALS.G_WFI_HDR
1972 AND item_key = p_order_header_id
1973 AND process_activity IN (SELECT wpa.instance_id
1974 FROM wf_process_activities wpa
1975 WHERE wpa.activity_item_type = OE_GLOBALS.G_WFI_HDR
1976 AND wpa.activity_name = 'BOOK_ORDER');
1977 ELSE
1978 l_booked_date := NULL;
1979 END IF;
1980 RETURN l_booked_date;
1981 END get_order_booked_date;
1982 */
1983
1984
1985 --| -----------------------------------------------------------------------=
1986 --| Function Name : get_site_address_id
1987 --| Desc : Pass in order site_use_id then return address_id of 'use site'
1988 --| (gets address_id out of RA_SITE_USES)
1989 --| ---------------------------------------------------------------------=
1990 FUNCTION get_site_address_id(p_site_use_id NUMBER, p_org_id NUMBER ) RETURN NUMBER IS
1991
1992 CURSOR l_address_id_csr IS
1993 SELECT cust_acct_site_id
1994 FROM hz_cust_site_uses
1995 WHERE site_use_id = p_site_use_id
1996 AND org_id = p_org_id;
1997 l_address_id NUMBER;
1998
1999 BEGIN
2000
2001 IF p_site_use_id IS NOT NULL THEN
2002
2003 OPEN l_address_id_csr;
2004 FETCH l_address_id_csr INTO l_address_id;
2005 CLOSE l_address_id_csr;
2006 RETURN l_address_id;
2007
2008
2009 ELSE
2010 RETURN NULL;
2011 END IF;
2012
2013 EXCEPTION WHEN OTHERS THEN
2014
2015 IF l_address_id_csr%isopen THEN
2016 CLOSE l_address_id_csr;
2017 END IF;
2018
2019 RAISE;
2020
2021 END get_site_address_id;
2022
2023 --| -----------------------------------------------------------------------=
2024 --| Function Name : get_order_revenue_type
2025 --| Desc : Derives the Revenue Type of an order, in the format required by
2026 --| CN
2027 --| ---------------------------------------------------------------------=
2028 FUNCTION get_order_revenue_type(p_sales_credit_type_id NUMBER) RETURN VARCHAR2 IS
2029
2030 CURSOR l_qu_flag_csr IS
2031 SELECT quota_flag
2032 FROM aso_i_sales_credit_types_v
2033 WHERE sales_credit_type_id = p_sales_credit_type_id;
2034 l_qu_flag VARCHAR2(1);
2035
2036 BEGIN
2037 IF p_sales_credit_type_id IS NOT NULL THEN
2038 OPEN l_qu_flag_csr;
2039 FETCH l_qu_flag_csr INTO l_qu_flag;
2040 CLOSE l_qu_flag_csr;
2041 IF l_qu_flag = 'Y' THEN
2042 RETURN 'REVENUE';
2043 ELSE
2044 RETURN 'NONREVENUE';
2045 END IF;
2046 ELSE
2047 RETURN NULL;
2048 END IF;
2049
2050 EXCEPTION WHEN OTHERS THEN
2051 IF l_qu_flag_csr%isopen THEN
2052 CLOSE l_qu_flag_csr;
2053 END IF;
2054 RAISE;
2055
2056 END get_order_revenue_type;
2057
2058
2059 -- |------------------------------------------------------------------------=
2060 -- | Function Name : get_credit_info
2061 -- |
2062 -- | Description : Procedure to return precision and extended precision for credit
2063 -- | types
2064 -- |-------------------------------------------------------------------------=
2065 PROCEDURE get_credit_info
2066 (p_credit_type_name IN cn_credit_types.name%TYPE, /* credit type name */
2067 x_precision OUT NOCOPY NUMBER, /* number of digits to right of decimal*/
2068 x_ext_precision OUT NOCOPY NUMBER, /* precision where more precision is needed*/
2069 p_org_id NUMBER
2070 ) IS
2071
2072 BEGIN
2073
2074 SELECT PRECISION, EXTENDED_PRECISION
2075 INTO x_precision, x_ext_precision
2076 FROM cn_credit_types
2077 WHERE name = p_credit_type_name
2078 AND org_id = p_org_id;
2079
2080 /* Precision should never be NULL; this is just so it works w/ bad data*/
2081 IF (x_precision IS NULL)
2082 THEN /* Default precision to two if necessary. */
2083 x_precision := 2;
2084 END IF;
2085
2086 /* Ext Precision should never be NULL; this is so it works w/ bad data*/
2087 IF (x_ext_precision IS NULL)
2088 THEN /* Default ext_precision if necc. */
2089 x_ext_precision := 5;
2090 END IF;
2091
2092 EXCEPTION
2093 WHEN NO_DATA_FOUND THEN
2094 x_precision := 0;
2095 x_ext_precision := 0;
2096 END;
2097
2098
2099 --| -----------------------------------------------------------------------=
2100 --| Function Name : convert_to_repcurr
2101 --| Desc : Convert from credit unit into salesrep currency amount
2102 --| ---------------------------------------------------------------------=
2103 FUNCTION convert_to_repcurr
2104 (p_credit_unit IN NUMBER,
2105 p_conv_date IN DATE ,
2106 p_conv_type IN VARCHAR2,
2107 p_from_credit_type_id IN NUMBER,
2108 p_funcurr_code IN VARCHAR2,
2109 p_repcurr_code IN VARCHAR2,
2110 p_org_id IN NUMBER
2111 ) RETURN NUMBER IS
2112
2113 l_monetary_flag cn_credit_types.monetary_flag%TYPE;
2114 l_conv_date DATE;
2115 l_conv_amount NUMBER;
2116 l_repcurr_amount NUMBER;
2117
2118 CURSOR get_closest_date IS
2119 SELECT start_date
2120 FROM cn_credit_conv_fcts_all
2121 WHERE from_credit_type_id = p_from_credit_type_id
2122 AND to_credit_type_id = -1000
2123 AND start_date <= p_conv_date
2124 AND org_id = p_org_id
2125 ORDER BY start_date DESC ;
2126
2127 l_date DATE;
2128
2129 BEGIN
2130
2131 SELECT monetary_flag into l_monetary_flag
2132 FROM cn_credit_types_all
2133 WHERE credit_type_id = p_from_credit_type_id
2134 AND org_id = p_org_id;
2135
2136 IF (l_monetary_flag = 'N') THEN
2137 BEGIN
2138 SELECT conversion_factor * Nvl(p_credit_unit,0)
2139 INTO l_conv_amount
2140 FROM cn_credit_conv_fcts_all
2141 WHERE from_credit_type_id = p_from_credit_type_id
2142 AND to_credit_type_id = -1000
2143 AND org_id = p_org_id
2144 AND p_conv_date between start_date and nvl(end_date, p_conv_date)
2145 ;
2146 EXCEPTION
2147 WHEN NO_DATA_FOUND THEN
2148 -- if no conv_factor defined for p_conv_date, trace back to
2149 -- closest defined date before p_conv_date
2150 OPEN get_closest_date;
2151 FETCH get_closest_date INTO l_date;
2152 IF NOT get_closest_date%FOUND THEN
2153 CLOSE get_closest_date;
2154 RAISE NO_DATA_FOUND;
2155 ELSE
2156 SELECT conversion_factor * Nvl(p_credit_unit,0)
2157 INTO l_conv_amount
2158 FROM cn_credit_conv_fcts_all
2159 WHERE from_credit_type_id = p_from_credit_type_id
2160 AND to_credit_type_id = -1000
2161 AND org_id = p_org_id
2162 AND l_date between start_date and nvl(end_date, l_date)
2163 ;
2164 END IF;
2165 CLOSE get_closest_date;
2166 END;
2167 ELSE
2168 SELECT Nvl(p_credit_unit,0) INTO l_conv_amount
2169 FROM sys.dual;
2170 END IF;
2171
2172 l_repcurr_amount :=
2173 gl_currency_api.convert_amount
2174 (x_from_currency => p_funcurr_code,
2175 x_to_currency => p_repcurr_code,
2176 x_conversion_date => p_conv_date,
2177 x_conversion_type => p_conv_type,
2178 x_amount => l_conv_amount
2179 );
2180
2181 RETURN l_repcurr_amount;
2182
2183 EXCEPTION
2184 WHEN NO_DATA_FOUND THEN
2185 FND_MESSAGE.SET_NAME('CN','CN_CRCVFT_NOT_EXIST');
2186 FND_MSG_PUB.Add;
2187 APP_EXCEPTION.raise_exception;
2188 END convert_to_repcurr;
2189
2190
2191
2192 --| -----------------------------------------------------------------------=
2193 --| PROCEDURE Name : convert_to_repcurr_report
2194 --| Desc : Convert from credit unit into salesrep currency amount
2195 --| Called by reports.
2196 --| ---------------------------------------------------------------------=
2197 PROCEDURE convert_to_repcurr_report
2198 (p_credit_unit IN NUMBER,
2199 p_conv_date IN DATE ,
2200 p_conv_type IN VARCHAR2,
2201 p_from_credit_type_id IN NUMBER,
2202 p_funcurr_code IN VARCHAR2,
2203 p_repcurr_code IN VARCHAR2,
2204 x_repcurr_amount OUT NOCOPY NUMBER,
2205 x_return_status OUT NOCOPY VARCHAR2,
2206 p_org_id IN NUMBER
2207 ) IS
2208
2209 l_monetary_flag cn_credit_types.monetary_flag%TYPE;
2210 l_conv_date DATE;
2211 l_conv_amount NUMBER;
2212
2213 CURSOR get_closest_date IS
2214 SELECT start_date
2215 FROM cn_credit_conv_fcts_all
2216 WHERE from_credit_type_id = p_from_credit_type_id
2217 AND to_credit_type_id = -1000
2218 AND Trunc(start_date) <= Trunc(p_conv_date)
2219 AND org_id = p_org_id
2220 ORDER BY start_date DESC ;
2221
2222 l_date DATE;
2223
2224 BEGIN
2225
2226 x_return_status := 'S';
2227 -- dbms_output.put_line('p_conv_date = ' || p_conv_date);
2228 -- dbms_output.put_line('p_conv_type = ' || p_conv_type);
2229 -- dbms_output.put_line('p_from_credit_type_id = ' || p_from_credit_type_id);
2230 -- dbms_output.put_line('p_funcurr_code = '|| p_funcurr_code);
2231 -- dbms_output.put_line('p_repcurr_code = ' || p_repcurr_code);
2232
2233 SELECT monetary_flag into l_monetary_flag
2234 FROM cn_credit_types_all
2235 WHERE credit_type_id = p_from_credit_type_id
2236 AND org_id = p_org_id;
2237
2238 -- dbms_output.put_line('l_monetary_flag = ' || l_monetary_flag);
2239
2240 IF (l_monetary_flag = 'N') THEN
2241 BEGIN
2242 SELECT conversion_factor * Nvl(p_credit_unit,0)
2243 INTO l_conv_amount
2244 FROM cn_credit_conv_fcts_all
2245 WHERE from_credit_type_id = p_from_credit_type_id
2246 AND to_credit_type_id = -1000
2247 AND org_id = p_org_id
2248 AND p_conv_date between start_date and nvl(end_date, p_conv_date)
2249 ;
2250 -- dbms_output.put_line('l_conv_amount = ' || l_conv_amount);
2251
2252 EXCEPTION
2253 WHEN NO_DATA_FOUND THEN
2254 -- if no conv_factor defined for p_conv_date, trace back to
2255 -- closest defined date before p_conv_date
2256 -- dbms_output.put_line('no data found -- 1');
2257 OPEN get_closest_date;
2258 FETCH get_closest_date INTO l_date;
2259 -- dbms_output.put_line('l_date = ' || l_date);
2260 IF NOT get_closest_date%FOUND THEN
2261 CLOSE get_closest_date;
2262 RAISE NO_DATA_FOUND;
2263 ELSE
2264 SELECT conversion_factor * Nvl(p_credit_unit,0)
2265 INTO l_conv_amount
2266 FROM cn_credit_conv_fcts_all
2267 WHERE from_credit_type_id = p_from_credit_type_id
2268 AND to_credit_type_id = -1000
2269 AND org_id = p_org_id
2270 AND l_date between start_date and nvl(end_date, l_date);
2271 -- dbms_output.put_line('l_conv_amount = ' || l_conv_amount);
2272 END IF;
2273 CLOSE get_closest_date;
2274 END;
2275 ELSE
2276 SELECT Nvl(p_credit_unit,0) INTO l_conv_amount
2277 FROM sys.dual;
2278 END IF;
2279
2280 -- dbms_output.put_line('p_conv_date = ' || p_conv_date);
2281 -- dbms_output.put_line('p_conv_type = ' || p_conv_type);
2282 -- dbms_output.put_line('l_conv_amount = ' || l_conv_amount);
2283 -- dbms_output.put_line('p_funcurr_code = '|| p_funcurr_code);
2284 -- dbms_output.put_line('p_repcurr_code = ' || p_repcurr_code);
2285
2286 x_repcurr_amount :=
2287 gl_currency_api.convert_amount
2288 (x_from_currency => p_funcurr_code,
2289 x_to_currency => p_repcurr_code,
2290 x_conversion_date => p_conv_date,
2291 x_conversion_type => p_conv_type,
2292 x_amount => l_conv_amount
2293 );
2294
2295
2296 EXCEPTION
2297 WHEN NO_DATA_FOUND THEN
2298 -- dbms_output.put_line('no data found --2 ');
2299 FND_MESSAGE.SET_NAME('CN','CN_CRCVFT_NOT_EXIST');
2300 FND_MSG_PUB.Add;
2301 x_return_status := 'E';
2302 END convert_to_repcurr_report;
2303
2304
2305
2306 --| -----------------------------------------------------------------------=
2307 --| Function Name : g_miss_char
2308 --| Desc : function to return FND_API.g_miss_char
2309 --| ---------------------------------------------------------------------=
2310 FUNCTION g_miss_char RETURN VARCHAR2 IS
2311 BEGIN
2312 RETURN fnd_api.g_miss_char;
2313 END g_miss_char;
2314
2315 --| -----------------------------------------------------------------------=
2316 --| Function Name : g_miss_date
2317 --| Desc : function to return FND_API.g_miss_date
2318 --| ---------------------------------------------------------------------=
2319 FUNCTION g_miss_date RETURN DATE IS
2320 BEGIN
2321 RETURN fnd_api.g_miss_date;
2322 END g_miss_date;
2323
2324 --| -----------------------------------------------------------------------=
2325 --| Function Name : g_miss_num
2326 --| Desc : function to return FND_API.g_miss_num
2327 --| ---------------------------------------------------------------------=
2328 FUNCTION g_miss_num RETURN NUMBER IS
2329 BEGIN
2330 RETURN fnd_api.g_miss_num;
2331 END g_miss_num;
2332
2333 --| -----------------------------------------------------------------------=
2334 --| Function Name : g_miss_id
2335 --| Desc : function to return -99999999999999
2336 --| ---------------------------------------------------------------------=
2337 FUNCTION g_miss_id RETURN NUMBER IS
2338 BEGIN
2339 RETURN -99999999999999;
2340 END g_miss_id;
2341
2342 --| -----------------------------------------------------------------------=
2343 --| Function Name : g_false
2344 --| Desc : function to return FND_API.g_false
2345 --| ---------------------------------------------------------------------=
2346 FUNCTION g_false RETURN VARCHAR2 IS
2347 BEGIN
2348 RETURN fnd_api.g_false;
2349 END g_false;
2350
2351 --| -----------------------------------------------------------------------=
2352 --| Function Name : g_true
2353 --| Desc : function to return FND_API.g_true
2354 --| ---------------------------------------------------------------------=
2355 FUNCTION g_true RETURN VARCHAR2 IS
2356 BEGIN
2357 RETURN fnd_api.g_true;
2358 END g_true;
2359
2360 --| -----------------------------------------------------------------------=
2361 --| Function Name : g_valid_level_none
2362 --| Desc : function to return FND_API.G_VALID_LEVEL_NONE
2363 --| ---------------------------------------------------------------------=
2364 FUNCTION g_valid_level_none RETURN NUMBER IS
2365 BEGIN
2366 RETURN fnd_api.g_valid_level_none;
2367 END g_valid_level_none;
2368
2369 --| -----------------------------------------------------------------------=
2370 --| Function Name : g_valid_level_full
2371 --| Desc : function to return FND_API.G_VALID_LEVEL_FULL
2372 --| ---------------------------------------------------------------------=
2373 FUNCTION g_valid_level_full RETURN NUMBER IS
2374 BEGIN
2375 RETURN fnd_api.g_valid_level_full;
2376 END g_valid_level_full;
2377
2378
2379
2380
2381 --| -----------------------------------------------------------------------=
2382 --| Function Name : generate code combinations
2383 --| Desc :
2384 --| ---------------------------------------------------------------------=
2385 PROCEDURE get_ccids
2386 (p_account_type IN varchar2,
2387 p_org_id IN NUMBER,
2388 x_account_structure OUT NOCOPY varchar2,
2389 x_code_combinations OUT NOCOPY code_combination_tbl) IS
2390
2391 kff fnd_flex_key_api.flexfield_type;
2392 str fnd_flex_key_api.structure_type;
2393 seg fnd_flex_key_api.segment_type;
2394 seg_list fnd_flex_key_api.segment_list;
2395 j number;
2396 i number;
2397 nsegs number;
2398 segment_descr varchar2(2000);
2399 sql_stmt varchar2(2000);
2400 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
2401
2402 l_counter NUMBER := 0;
2403 ccid NUMBER;
2404 ccid_value VARCHAR2(2000);
2405 l_account_type gl_code_combinations.account_type%type;
2406
2407 TYPE curtype IS ref CURSOR;
2408 ccid_cur curtype;
2409
2410 BEGIN
2411
2412 SELECT chart_of_accounts_id
2413 INTO l_chart_of_accounts_id
2414 FROM gl_sets_of_books gsb,
2415 cn_repositories_all cr
2416 WHERE cr.set_of_books_id = gsb.set_of_books_id
2417 AND cr.org_id = p_org_id;
2418
2419 fnd_flex_key_api.set_session_mode('customer_data');
2420 kff := fnd_flex_key_api.find_flexfield('SQLGL','GL#');
2421 str := fnd_flex_key_api.find_structure(kff, l_chart_of_accounts_id);
2422 fnd_flex_key_api.get_segments(kff, str, TRUE, nsegs, seg_list);
2423
2424 -- The segments in the seg_list array are sorted in display order.
2425 -- i.e. sorted by segment number.
2426 sql_stmt := 'SELECT ';
2427 for i in 1..nsegs loop
2428 seg := fnd_flex_key_api.find_segment(kff, str, seg_list(i));
2429 segment_descr := segment_descr || seg.segment_name;
2430 sql_stmt := sql_stmt || seg.column_name;
2431 If i <> nsegs
2432 then
2433 segment_descr := segment_descr || str.segment_separator;
2434 sql_stmt := sql_stmt || '||'''||str.segment_separator||'''||';
2435 end if;
2436 end loop;
2437 x_account_structure := segment_descr;
2438 sql_stmt := sql_stmt ||
2439 ', code_combination_id, account_type '||
2440 ' FROM gl_code_combinations '||
2441 ' WHERE chart_of_accounts_id = :B1' ||
2442 ' AND enabled_flag = ''Y''';
2443
2444 -- commented out by KS.
2445 -- Using Bind Variables
2446 -- ||l_chart_of_accounts_id||
2447
2448 OPEN ccid_cur FOR sql_stmt using l_chart_of_accounts_id;
2449 LOOP
2450
2451 ccid_value := null;
2452 ccid := null;
2453 l_account_type := null;
2454
2455 FETCH ccid_cur INTO
2456 ccid_value, ccid,
2457 l_account_type;
2458 EXIT WHEN ccid_cur%notfound;
2459
2460 IF l_account_type = p_account_type
2461 THEN
2462 x_code_combinations(l_counter).ccid := ccid;
2463 x_code_combinations(l_counter).code_combination := ccid_value;
2464 l_counter := l_counter + 1;
2465 END IF;
2466
2467 END LOOP;
2468 CLOSE ccid_cur;
2469 END get_ccids;
2470
2471 --| -----------------------------------------------------------------------=
2472 --| Function Name : get code combination in display format
2473 --| Desc :
2474 --| ---------------------------------------------------------------------=
2475 PROCEDURE get_ccid_disp
2476 (p_ccid IN varchar2,
2477 p_org_id IN NUMBER,
2478 x_code_combination OUT NOCOPY varchar2) IS
2479
2480 kff fnd_flex_key_api.flexfield_type;
2481 str fnd_flex_key_api.structure_type;
2482 seg fnd_flex_key_api.segment_type;
2483 seg_list fnd_flex_key_api.segment_list;
2484 j number;
2485 i number;
2486 nsegs number;
2487 segment_descr varchar2(2000);
2488 sql_stmt varchar2(2000);
2489 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
2490
2491 l_counter NUMBER := 0;
2492 ccid NUMBER;
2493 ccid_value VARCHAR2(2000);
2494 l_account_type gl_code_combinations.account_type%type;
2495
2496 TYPE curtype IS ref CURSOR;
2497 ccid_cur curtype;
2498
2499 BEGIN
2500
2501 SELECT chart_of_accounts_id
2502 INTO l_chart_of_accounts_id
2503 FROM gl_sets_of_books gsb,
2504 cn_repositories_all cr
2505 WHERE cr.set_of_books_id = gsb.set_of_books_id
2506 AND org_id = p_org_id;
2507
2508 fnd_flex_key_api.set_session_mode('customer_data');
2509 kff := fnd_flex_key_api.find_flexfield('SQLGL','GL#');
2510 str := fnd_flex_key_api.find_structure(kff, l_chart_of_accounts_id);
2511 fnd_flex_key_api.get_segments(kff, str, TRUE, nsegs, seg_list);
2512
2513 -- The segments in the seg_list array are sorted in display order.
2514 -- i.e. sorted by segment number.
2515 sql_stmt := 'SELECT ';
2516 for i in 1..nsegs loop
2517 seg := fnd_flex_key_api.find_segment(kff, str, seg_list(i));
2518 segment_descr := segment_descr || seg.segment_name;
2519 sql_stmt := sql_stmt || seg.column_name;
2520 If i <> nsegs
2521 then
2522 segment_descr := segment_descr || str.segment_separator;
2523 sql_stmt := sql_stmt || '||'''||str.segment_separator||'''||';
2524 end if;
2525 end loop;
2526 sql_stmt := sql_stmt ||
2527 ', code_combination_id, account_type '||
2528 ' FROM gl_code_combinations '||
2529 ' WHERE code_combination_id = :B1';
2530
2531 -- commented out by KS
2532 -- 09/28/01
2533 -- ||p_ccid;
2534
2535 OPEN ccid_cur FOR sql_stmt using p_ccid;
2536 LOOP
2537
2538 ccid_value := null;
2539 ccid := null;
2540 l_account_type := null;
2541
2542 FETCH ccid_cur INTO
2543 ccid_value, ccid,
2544 l_account_type;
2545 EXIT WHEN ccid_cur%notfound;
2546
2547 x_code_combination := ccid_value;
2548 l_counter := l_counter + 1;
2549 END LOOP;
2550 CLOSE ccid_cur;
2551 END get_ccid_disp;
2552
2553
2554 --| -----------------------------------------------------------------------=
2555 --| Function Name : get code combination in display format
2556 --| Desc :
2557 --| ---------------------------------------------------------------------=
2558 FUNCTION get_ccid_disp_func
2559 (p_ccid IN varchar2, p_org_id IN NUMBER ) RETURN VARCHAR2 IS
2560
2561 kff fnd_flex_key_api.flexfield_type;
2562 str fnd_flex_key_api.structure_type;
2563 seg fnd_flex_key_api.segment_type;
2564 seg_list fnd_flex_key_api.segment_list;
2565 j number;
2566 i number;
2567 nsegs number;
2568 segment_descr varchar2(2000);
2569 sql_stmt varchar2(2000);
2570
2571 l_code Varchar2(4000);
2572
2573
2574 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
2575
2576 l_counter NUMBER := 0;
2577 ccid NUMBER;
2578 ccid_value VARCHAR2(2000);
2579 l_account_type gl_code_combinations.account_type%type;
2580
2581 TYPE curtype IS ref CURSOR;
2582 ccid_cur curtype;
2583
2584 BEGIN
2585
2586 SELECT chart_of_accounts_id
2587 INTO l_chart_of_accounts_id
2588 FROM gl_sets_of_books gsb,
2589 cn_repositories_all cr
2590 WHERE cr.set_of_books_id = gsb.set_of_books_id
2591 AND org_id = p_org_id;
2592
2593 fnd_flex_key_api.set_session_mode('customer_data');
2594 kff := fnd_flex_key_api.find_flexfield('SQLGL','GL#');
2595 str := fnd_flex_key_api.find_structure(kff, l_chart_of_accounts_id);
2596 fnd_flex_key_api.get_segments(kff, str, TRUE, nsegs, seg_list);
2597
2598 -- The segments in the seg_list array are sorted in display order.
2599 -- i.e. sorted by segment number.
2600 sql_stmt := 'SELECT ';
2601 for i in 1..nsegs loop
2602 seg := fnd_flex_key_api.find_segment(kff, str, seg_list(i));
2603 segment_descr := segment_descr || seg.segment_name;
2604 sql_stmt := sql_stmt || seg.column_name;
2605 If i <> nsegs
2606 then
2607 segment_descr := segment_descr || str.segment_separator;
2608 sql_stmt := sql_stmt || '||'''||str.segment_separator||'''||';
2609 end if;
2610 end loop;
2611 sql_stmt := sql_stmt ||
2612 ', code_combination_id, account_type '||
2613 ' FROM gl_code_combinations '||
2614 ' WHERE code_combination_id = :B1' ;
2615
2616 -- commented out by KS
2617 -- 09/28/01
2618 -- ||p_ccid;
2619
2620
2621 OPEN ccid_cur FOR sql_stmt using p_ccid;
2622 LOOP
2623
2624 ccid_value := null;
2625 ccid := null;
2626 l_account_type := null;
2627
2628 FETCH ccid_cur INTO
2629 ccid_value, ccid,
2630 l_account_type;
2631 EXIT WHEN ccid_cur%notfound;
2632
2633 l_code := ccid_value;
2634 l_counter := l_counter + 1;
2635 END LOOP;
2636 CLOSE ccid_cur;
2637
2638 return l_code;
2639
2640 END get_ccid_disp_func;
2641
2642
2643 --| ---------------------------------------------------------------------+
2644 --| Function Name : attribute_desc
2645 --| Desc : Pass in rule_id, rule_attribute_id
2646 --| ---------------------------------------------------------------------+
2647 FUNCTION get_attribute_desc( p_rule_id NUMBER,
2648 p_attribute_id NUMBER )
2649 RETURN VARCHAR2 IS
2650
2651 l_desc cn_rule_attributes_desc_v.descriptive_rule_attribute%TYPE;
2652
2653 BEGIN
2654 SELECT descriptive_rule_attribute
2655 INTO l_desc
2656 FROM cn_rule_attributes_desc_v
2657 WHERE rule_id = p_rule_id
2658 AND attribute_rule_id = p_attribute_id ;
2659
2660 RETURN l_desc;
2661
2662 EXCEPTION
2663 WHEN no_data_found THEN
2664 RETURN NULL;
2665 END get_attribute_desc;
2666
2667
2668 --| ---------------------------------------------------------------------+
2669 --| Function Name : rule_count
2670 --| Desc : Pass in rule_id
2671 --| ---------------------------------------------------------------------+
2672 FUNCTION get_rule_count( p_rule_id NUMBER)
2673
2674 RETURN NUMBER IS
2675
2676 l_count NUMBER := 1;
2677
2678 BEGIN
2679 SELECT count(*)
2680 INTO l_count
2681 FROM cn_attribute_rules
2682 WHERE rule_id = p_rule_id;
2683
2684 if l_count = 0 then
2685 l_count := 1;
2686 end if;
2687
2688 RETURN l_count;
2689
2690 EXCEPTION
2691 WHEN no_data_found THEN
2692 RETURN 1;
2693 END get_rule_count;
2694
2695 -- ===========================================================================
2696 -- Function : chk_Payrun_status_paid
2697 -- Description : Check for valid payrun_id, Status must be unpaid
2698 -- ===========================================================================
2699 FUNCTION chk_payrun_status_paid
2700 ( p_payrun_id IN NUMBER,
2701 p_loading_status IN VARCHAR2,
2702 x_loading_status OUT NOCOPY VARCHAR2
2703 ) RETURN VARCHAR2 IS
2704
2705 CURSOR get_payrun_status IS
2706 SELECT status, payrun_id
2707 FROM cn_payruns
2708 WHERE payrun_id = p_payrun_id;
2709
2710 l_status cn_payruns.status%TYPE;
2711 l_payrun_id cn_payruns.payrun_id%TYPE;
2712
2713 BEGIN
2714 -- Initialize API return status to success
2715 x_loading_status := p_loading_status ;
2716
2717 -- Get Payrun Status
2718 OPEN get_payrun_status;
2719 FETCH get_payrun_status INTO l_status, l_payrun_id;
2720 CLOSE get_payrun_status;
2721
2722 -- check payrun status, No operation can be done for PAID
2723 IF l_status <> 'UNPAID' THEN
2724 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2725 THEN
2726 fnd_message.set_name('CN', 'CN_CANNOT_UPD_PAID_F_PAYRUN');
2727 fnd_msg_pub.add;
2728 END IF;
2729 x_loading_status := 'CN_CANNOT_UPD_PAID_F_PAYRUN';
2730 RETURN fnd_api.g_true;
2731 END IF;
2732
2733 -- check payrun exists
2734 IF l_payrun_id IS NULL THEN
2735 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2736 THEN
2737 fnd_message.set_name('CN', 'CN_PAYRUN_DOES_NOT_EXIST');
2738 fnd_msg_pub.add;
2739 END IF;
2740 x_loading_status := 'CN_PAYRUN_DOES_NOT_EXIST';
2741 RETURN fnd_api.g_true;
2742 END IF;
2743
2744 RETURN fnd_api.g_false;
2745
2746 END chk_payrun_status_paid;
2747 -- ===========================================================================
2748 -- Procedure : Chk_hold_status
2749 -- Description : This procedure is used to check if the salesrep is on hold
2750 -- and valid salesrep ID is passed
2751 -- ===========================================================================
2752 FUNCTION chk_hold_status
2753 (
2754 p_salesrep_id IN NUMBER,
2755 p_org_id IN NUMBER,
2756 p_loading_status IN VARCHAR2,
2757 x_loading_status OUT NOCOPY VARCHAR2
2758 ) RETURN VARCHAR2 IS
2759
2760 CURSOR get_hold_status IS
2761 SELECT hold_payment, salesrep_id
2762 FROM cn_salesreps
2763 WHERE salesrep_id = p_salesrep_id
2764 AND org_id = p_org_id;
2765
2766 l_status cn_payruns.status%TYPE;
2767 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
2768
2769 BEGIN
2770
2771 x_loading_status := p_loading_status ;
2772
2773 OPEN get_hold_status;
2774 FETCH get_hold_status INTO l_status,l_salesrep_id;
2775 CLOSE get_hold_status;
2776
2777 IF l_status = 'Y' THEN
2778 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2779 THEN
2780 fnd_message.set_name('CN', 'CN_SRP_ON_HOLD');
2781 fnd_msg_pub.add;
2782 END IF;
2783 x_loading_status := 'CN_SRP_ON_HOLD';
2784 RETURN fnd_api.g_true;
2785 END IF;
2786
2787 IF l_salesrep_id IS NULL THEN
2788 x_loading_status := 'CN_SRP_NOT_EXISTS';
2789 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2790 THEN
2791 fnd_message.set_name('CN', 'CN_SRP_NOT_EXISTS');
2792 fnd_msg_pub.add;
2793 END IF;
2794 x_loading_status := 'CN_SRP_NOT_EXISTS';
2795 RETURN fnd_api.g_true;
2796 END IF;
2797
2798 RETURN fnd_api.g_false;
2799 END chk_hold_status;
2800
2801 -- ===========================================================================
2802 -- Procedure : Chk_srp_hold_status
2803 -- Description : This procedure is used to check if the salesrep is on hold
2804 -- and valid salesrep ID is passed
2805 -- ===========================================================================
2806 FUNCTION chk_srp_hold_status
2807 (
2808 p_salesrep_id IN NUMBER,
2809 p_org_id IN NUMBER,
2810 p_loading_status IN VARCHAR2,
2811 x_loading_status OUT NOCOPY VARCHAR2
2812 ) RETURN VARCHAR2 IS
2813
2814 CURSOR get_hold_status IS
2815 SELECT hold_payment, salesrep_id
2816 FROM cn_salesreps
2817 WHERE salesrep_id = p_salesrep_id
2818 AND org_id = p_org_id;
2819
2820 l_status cn_payruns.status%TYPE;
2821 l_salesrep_id cn_salesreps.salesrep_id%TYPE;
2822
2823 BEGIN
2824
2825 x_loading_status := p_loading_status ;
2826
2827 OPEN get_hold_status;
2828 FETCH get_hold_status INTO l_status,l_salesrep_id;
2829 CLOSE get_hold_status;
2830
2831 IF l_status = 'Y' THEN
2832 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2833 THEN
2834 fnd_message.set_name('CN', 'CN_SRP_ON_HOLD');
2835 fnd_msg_pub.add;
2836 END IF;
2837 x_loading_status := 'CN_SRP_ON_HOLD';
2838 RETURN fnd_api.g_true;
2839 END IF;
2840
2841 IF l_salesrep_id IS NULL THEN
2842 x_loading_status := 'CN_SRP_NOT_EXISTS';
2843 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2844 THEN
2845 fnd_message.set_name('CN', 'CN_SRP_NOT_EXISTS');
2846 fnd_msg_pub.add;
2847 END IF;
2848 x_loading_status := 'CN_SRP_NOT_EXISTS';
2849 RETURN fnd_api.g_true;
2850 END IF;
2851
2852 RETURN fnd_api.g_false;
2853 END chk_srp_hold_status;
2854 --| ---------------------------------------------------------------------+
2855 -- Function : Get_pay_Element_ID(P_quota_id, p_salesrep_id, p_date)
2856 --| ---------------------------------------------------------------------+
2857 FUNCTION Get_pay_Element_ID
2858 (
2859 p_quota_id IN NUMBER,
2860 p_Salesrep_id IN cn_rs_salesreps.salesrep_id%TYPE,
2861 p_org_id IN NUMBER,
2862 p_date IN DATE
2863 ) RETURN NUMBER IS
2864
2865 -- Bug 2875120 3/27/03
2866 CURSOR /*+ ordered */ get_pay_element IS
2867 SELECT pay_element_type_id
2868 FROM cn_quota_pay_elements p,
2869 cn_rs_salesreps s
2870 WHERE p.quota_id = p_quota_id
2871 AND p_date between p.start_date and p.end_date
2872 AND s.salesrep_id = p_salesrep_id
2873 AND s.org_id = p_org_id
2874 AND nvl(s.status,'A') = p.status;
2875
2876 l_pay_element_type_id NUMBER;
2877 l_payroll_flag Varchar2(01);
2878
2879
2880 BEGIN
2881 select nvl(payroll_flag,'N')
2882 into l_payroll_flag
2883 from cn_repositories
2884 WHERE org_id = p_org_id;
2885
2886 if l_payroll_flag = 'Y' THEN
2887 open get_pay_element;
2888 fetch get_pay_element into l_pay_element_type_id;
2889 close get_pay_element;
2890 end if;
2891
2892 RETURN l_pay_element_type_id;
2893
2894 END;
2895 -- ===========================================================================
2896 -- Procedure : check_duplicate_worksheet
2897 -- Description : Check Duplicate Work sheet for salesrep and role in payrun
2898 -- ===========================================================================
2899 FUNCTION chk_duplicate_worksheet
2900 ( p_payrun_id IN NUMBER,
2901 p_salesrep_id IN NUMBER,
2902 p_org_id IN NUMBER,
2903 p_loading_status IN VARCHAR2,
2904 x_loading_status OUT NOCOPY VARCHAR2
2905 ) RETURN VARCHAR2 IS
2906
2907 CURSOR get_worksheet IS
2908 SELECT 1
2909 FROM cn_payment_worksheets
2910 WHERE payrun_id = p_payrun_id
2911 AND salesrep_id = p_salesrep_id
2912 AND org_id = p_org_id;
2913
2914 l_found number;
2915
2916 BEGIN
2917
2918 x_loading_status := p_loading_status ;
2919 OPEN get_worksheet;
2920 FETCH get_worksheet INTO l_found;
2921 CLOSE get_worksheet;
2922 IF l_found = 1
2923 THEN
2924 --Error condition
2925 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2926 THEN
2927 fnd_message.set_name('CN', 'CN_DUPLICATE_WORKSHEET');
2928 fnd_msg_pub.add;
2929 END IF;
2930 x_loading_status := 'CN_DUPLICATE_WORKSHEET';
2931 RAISE FND_API.G_EXC_ERROR;
2932 END IF;
2933 RETURN fnd_api.g_false;
2934
2935 EXCEPTION
2936 WHEN FND_API.G_EXC_ERROR THEN
2937 RETURN fnd_api.g_true;
2938 END chk_duplicate_worksheet;
2939
2940 -- ===========================================================================
2941 -- Procedure : check_worksheet_status
2942 -- Description : Check Worksheet Status
2943 -- ===========================================================================
2944 FUNCTION chk_worksheet_status
2945 ( p_payrun_id IN NUMBER,
2946 p_salesrep_id IN NUMBER,
2947 p_org_id IN NUMBER,
2948 p_loading_status IN VARCHAR2,
2949 x_loading_status OUT NOCOPY VARCHAR2
2950 ) RETURN VARCHAR2 IS
2951
2952 CURSOR get_worksheet IS
2953 SELECT worksheet_status
2954 FROM cn_payment_worksheets
2955 WHERE payrun_id = p_payrun_id
2956 AND salesrep_id = p_salesrep_id
2957 AND org_id = p_org_id
2958 AND quota_id iS NULL;
2959
2960
2961 l_status Varchar2(30);
2962
2963 BEGIN
2964
2965 x_loading_status := p_loading_status ;
2966 OPEN get_worksheet;
2967 FETCH get_worksheet INTO l_status;
2968 CLOSE get_worksheet;
2969 IF l_status NOT IN ( 'UNPAID','PROCESSING' )
2970 THEN
2971 --Error condition
2972 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
2973 THEN
2974 fnd_message.set_name('CN', 'CN_WKSHT_CANNOT_BE_MODIFIED');
2975 fnd_msg_pub.add;
2976 END IF;
2977 x_loading_status := 'CN_WKSHT_CANNOT_BE_MODIFIED';
2978 RAISE FND_API.G_EXC_ERROR;
2979 END IF;
2980 RETURN fnd_api.g_false;
2981
2982 EXCEPTION
2983 WHEN FND_API.G_EXC_ERROR THEN
2984 RETURN fnd_api.g_true;
2985 END chk_worksheet_status;
2986
2987 --| ---------------------------------------------------------------------+
2988 -- Function : Get_pay_Element_Name(P_element_type_id)
2989 --| ---------------------------------------------------------------------+
2990 FUNCTION Get_pay_Element_Name
2991 (
2992 p_element_type_id IN NUMBER
2993 ) RETURN VARCHAR2 IS
2994
2995 Cursor get_element_name IS
2996 SELECT element_name
2997 FROM pay_element_types_f
2998 WHERE element_type_id = p_element_type_id;
2999
3000 l_element_name pay_element_types_f.element_name%TYPE;
3001
3002 BEGIN
3003
3004 OPEN get_element_name;
3005 FETCH get_element_name into l_element_name;
3006 CLOSE get_element_name;
3007
3008 RETURN l_element_name;
3009
3010 EXCEPTION
3011 WHEN OTHERS THEN
3012 RETURN l_element_name;
3013 END get_pay_element_name;
3014
3015 --| -----------------------------------------------------------------------+
3016 --| Function Name : can_user_view_page()
3017 --| Desc : procedure to test if a HTML page is accessible to a user
3018 --| Return true if yes, else return false
3019 --| -----------------------------------------------------------------------+
3020 procedure can_user_view_page(p_page_name IN varchar2,
3021 x_return_status OUT NOCOPY varchar2) IS
3022 l_result boolean := false;
3023 funcName fnd_form_functions.function_name%TYPE ; --varchar2(100);
3024
3025 CURSOR l_function_csr (p_name VARCHAR2)IS
3026 SELECT function_name
3027 FROM fnd_form_functions
3028 WHERE UPPER(web_html_call) like p_name;
3029 BEGIN
3030 x_return_status := 'N';
3031
3032 IF p_page_name IS NULL THEN
3033 x_return_status := 'N';
3034 ELSE
3035 OPEN l_function_csr ((UPPER(p_page_name) || '%'));
3036 LOOP
3037 FETCH l_function_csr INTO funcName;
3038 EXIT WHEN l_function_csr%NOTFOUND;
3039
3040 l_result := FND_FUNCTION.TEST (funcName, 'Y');
3041 IF (l_result = true) THEN
3042 x_return_status := 'Y';
3043 EXIT;
3044 ELSE
3045 x_return_status := 'N';
3046 END IF;
3047 END LOOP;
3048 CLOSE l_function_csr;
3049
3050 END IF;
3051 END can_user_view_page;
3052
3053 --| ---------------------------------------------------------------------+
3054 --| Function : Is_Payee(p_salesrep_id)
3055 --| Desc : Check if passed in salesrep is a Payee
3056 --| Return 1 if the passed in salesrep_id is a Payee; otherwise return 0
3057 --| ---------------------------------------------------------------------+
3058
3059 FUNCTION Is_Payee( p_salesrep_id IN NUMBER,
3060 p_period_id IN NUMBER,
3061 p_org_id IN NUMBER) RETURN NUMBER IS
3062 l_exist NUMBER := 0;
3063 BEGIN
3064 l_exist := 0;
3065 BEGIN
3066 -- isPayee if assigned to srp in this period
3067 SELECT 1 INTO l_exist FROM dual WHERE EXISTS
3068 (SELECT 1
3069 FROM cn_srp_payee_assigns cnspay, cn_period_statuses cnps
3070 WHERE cnspay.payee_id = p_salesrep_id
3071 AND cnps.period_id = p_period_id
3072 AND cnps.org_id = p_org_id
3073 AND cnspay.org_id = p_org_id
3074 AND ((cnspay.start_date <= cnps.end_date)
3075 AND (cnps.start_date <= Nvl(cnspay.end_date,cnps.start_date)))
3076 );
3077
3078 RETURN l_exist;
3079
3080 EXCEPTION
3081 WHEN NO_DATA_FOUND THEN
3082 l_exist := 0;
3083 BEGIN
3084 -- isPayee if have Payee role in this period
3085 SELECT 1 INTO l_exist FROM dual WHERE EXISTS
3086 (SELECT 1 FROM cn_srp_roles sr, cn_period_statuses cnps
3087 WHERE sr.salesrep_id = p_salesrep_id
3088 AND sr.role_id = 54
3089 AND sr.org_id = p_org_id
3090 AND cnps.org_id = p_org_id
3091 AND cnps.period_id = p_period_id
3092 AND ((sr.start_date <= cnps.end_date)
3093 AND (cnps.start_date <= Nvl(sr.end_date,cnps.start_date)))
3094 );
3095
3096 RETURN l_exist;
3097 EXCEPTION
3098 WHEN NO_DATA_FOUND THEN
3099 RETURN 0;
3100 END;
3101 END;
3102
3103 END Is_Payee;
3104
3105 --| ---------------------------------------------------------------------+
3106 --| Function : Test_Function(p_function_name)
3107 --| Desc : Check if passed in function is allowed
3108 --| Return 1 if it is allowed; otherwise return 0
3109 --| ---------------------------------------------------------------------+
3110
3111 FUNCTION Test_Function( p_function_name IN VARCHAR2) RETURN NUMBER IS
3112 BEGIN
3113 if fnd_function.test(p_function_name) THEN
3114 return 1;
3115 else return 0;
3116 end if;
3117 END test_function;
3118
3119
3120
3121 FUNCTION get_role_name_2 (period_id NUMBER,
3122 -- payrun_id NUMBER,
3123 salesrep_id NUMBER)
3124 RETURN cn_roles.name%TYPE IS
3125 l_role_name cn_roles.name%TYPE;
3126 l_role_str VARCHAR2(1000):= '';
3127 l_start_date DATE;
3128
3129 CURSOR get_role_cursor(l_period_id NUMBER,
3130 l_salesrep_id IN NUMBER)
3131 IS
3132 SELECT DISTINCT t.role_name role_name,
3133 assign.start_date
3134 FROM cn_srp_periods srp ,
3135 cn_srp_plan_assigns assign,
3136 JTF_RS_ROLES_TL T ,
3137 JTF_RS_ROLES_B B
3138 WHERE assign.srp_plan_assign_id(+)= srp.srp_plan_assign_id
3139 AND srp.period_id = l_period_id
3140 AND assign.role_id = B.role_id(+)
3141 AND srp.ORG_ID = assign.ORG_ID
3142 AND srp.salesrep_id = l_salesrep_id
3143 AND B.ROLE_ID = T.ROLE_ID
3144 AND T.LANGUAGE = userenv('LANG')
3145 -- AND srp.credit_type_id = -1000
3146 AND srp.quota_id <> -1000
3147 ORDER BY assign.start_date;
3148
3149 /*SELECT distinct r.name role_name,assign.start_date
3150 FROM cn_srp_periods srp,
3151 cn_srp_plan_assigns assign,
3152 cn_roles r
3153 WHERE assign.srp_plan_assign_id(+)= srp.srp_plan_assign_id
3154 AND srp.period_id = l_period_id
3155 AND assign.role_id = r.role_id(+)
3156 AND srp.salesrep_id = l_salesrep_id
3157 -- AND srp.credit_type_id = -1000
3158 AND srp.quota_id <> -1000
3159 ORDER BY assign.start_date;*/
3160
3161 --variable added for bug 6685748
3162 l_html_text varchar2(2000);
3163 BEGIN
3164 --Code added for bug 6685748
3165 -- l_html_text :='<HTML>';
3166
3167 OPEN get_role_cursor(period_id,salesrep_id) ;
3168 LOOP
3169 FETCH get_role_cursor into l_role_name,l_start_date;
3170 EXIT WHEN get_role_cursor%NOTFOUND;
3171 l_role_str := l_role_str || l_role_name;
3172 --Code line added for bug 6685748
3173 l_role_str := l_role_str ||'<br>';
3174
3175 END LOOP;
3176 --Code line added for bug 6685748
3177 l_html_text :='<HTML>'||l_role_str||'<\HTML>';
3178 RETURN l_html_text;
3179
3180 EXCEPTION
3181 WHEN others THEN
3182 RETURN 'Error';
3183 END get_role_name_2;
3184
3185
3186 FUNCTION get_role_name_3 (p_period_id NUMBER DEFAULT NULL,
3187 p_salesrep_id NUMBER DEFAULT NULL,
3188 p_payrun_id NUMBER DEFAULT NULL,
3189 p_ORG_ID NUMBER DEFAULT NULL,
3190 populate NUMBER DEFAULT NULL)
3191 RETURN VARCHAR2 IS
3192
3193
3194
3195 CURSOR C_GET_SALESREP_ID
3196 IS
3197 SELECT salesrep_id
3198 FROM cn_payment_worksheets cnw
3199 WHERE cnw.payrun_id = p_payrun_id
3200 AND cnw.org_id = p_ORG_ID
3201 AND cnw.quota_id IS NULL ;
3202
3203 l_cnw_salesrep_id JTF_NUMBER_TABLE;
3204
3205 CURSOR C_SRP_ASSIGN_ID
3206 IS
3207 SELECT /*+ leading(cnw,srp) use_nl(srp) */ DISTINCT
3208 srp.salesrep_id ||'-' ||srp_plan_assign_id ||'-' ||srp.period_id
3209 FROM cn_srp_periods srp,
3210 (SELECT column_value SALESREP_ID FROM TABLE(CAST(l_cnw_salesrep_id AS JTF_NUMBER_TABLE))) cnw
3211 WHERE srp.period_id = p_period_id
3212 AND srp.quota_id <> -1000
3213 AND srp.org_id = p_ORG_ID
3214 AND srp.salesrep_id = cnw.SALESREP_ID;
3215
3216 l_cnw_srp_salrep_id JTF_VARCHAR2_TABLE_2000;
3217
3218 CURSOR get_role_cursor
3219 IS
3220 SELECT /*+ leading(cnw_srp,assign) use_nl(assign,rl) */ DISTINCT
3221 cnw_srp.SALESREP_ID, cnw_srp.PERIOD_ID, RL.ROLE_NAME, ASSIGN.START_DATE
3222 FROM CN_SRP_PLAN_ASSIGNS assign ,
3223 JTF_RS_ROLES_VL rl ,
3224 (SELECT TO_NUMBER(SUBSTR(column_value, 1, INSTR(column_value, '-', 1, 1) - 1 ) ) SALESREP_ID,
3225 TO_NUMBER(SUBSTR(column_value , INSTR(column_value, '-', 1, 1) + 1,
3226 (INSTR(column_value, '-', 1, 2) - INSTR(column_value, '-', 1, 1))-1) ) PLANASSIGNID,
3227 TO_NUMBER(SUBSTR(column_value , INSTR(column_value, '-', 1, 2) + 1 ,LENGTH(column_value) )) PERIOD_ID
3228 FROM TABLE(CAST(l_cnw_srp_salrep_id AS JTF_VARCHAR2_TABLE_2000))
3229 ) cnw_srp
3230 WHERE assign.ORG_ID = p_ORG_ID
3231 AND assign.srp_plan_assign_id(+) = cnw_srp.PLANASSIGNID
3232 AND assign.salesrep_id(+) = cnw_srp.SALESREP_ID
3233 AND rl.role_id(+) = assign.ROLE_ID
3234 AND rl.role_type_code = 'SALES_COMP'
3235 ORDER BY assign.start_date;
3236
3237
3238 l_salesrep_id_tbl JTF_NUMBER_TABLE;
3239 l_period_tbl JTF_NUMBER_TABLE;
3240 l_role_name_tbl JTF_VARCHAR2_TABLE_2000;
3241 l_start_date_tbl JTF_DATE_TABLE;
3242 l_sales_id_tbl JTF_NUMBER_TABLE;
3243
3244 l_role_name_cache VARCHAR2(2000);
3245 l_html_text VARCHAR2(2000);
3246 l_role_str VARCHAR2(2000):= '';
3247 l_sales_temp_num NUMBER;
3248 l_sales_perio_id NUMBER;
3249 l_salrep_tmp NUMBER;
3250 l_rec_count PLS_INTEGER;
3251 l_start_date DATE;
3252 l_found BOOLEAN;
3253 l_salesrep_cache_info salesrep_cache_rec_type;
3254
3255 BEGIN
3256
3257 IF populate IS NULL THEN
3258 IF g_salesrep_info_cache.EXISTS(p_salesrep_id) THEN
3259 l_salesrep_cache_info := g_salesrep_info_cache(p_salesrep_id);
3260 l_found := l_salesrep_cache_info.period_id = p_period_id;
3261 IF l_found
3262 THEN
3263 l_role_name_cache := l_salesrep_cache_info.ROLE_NAME;
3264 END IF;
3265 END IF;
3266 END IF;
3267
3268
3269 IF populate =1
3270 THEN
3271 l_salesrep_id_tbl :=JTF_NUMBER_TABLE();
3272 l_period_tbl :=JTF_NUMBER_TABLE();
3273 l_role_name_tbl :=JTF_VARCHAR2_TABLE_2000();
3274 l_start_date_tbl :=JTF_DATE_TABLE();
3275 l_sales_id_tbl :=JTF_NUMBER_TABLE();
3276 l_cnw_salesrep_id := JTF_NUMBER_TABLE();
3277 l_cnw_srp_salrep_id :=JTF_VARCHAR2_TABLE_2000();
3278
3279 OPEN C_GET_SALESREP_ID;
3280 FETCH C_GET_SALESREP_ID BULK COLLECT INTO l_cnw_salesrep_id;
3281 CLOSE C_GET_SALESREP_ID;
3282
3283
3284 OPEN C_SRP_ASSIGN_ID;
3285 FETCH C_SRP_ASSIGN_ID BULK COLLECT INTO l_cnw_srp_salrep_id;
3286 CLOSE C_SRP_ASSIGN_ID;
3287
3288 OPEN get_role_cursor;
3289 FETCH get_role_cursor BULK COLLECT
3290 INTO l_salesrep_id_tbl,
3291 l_period_tbl ,
3292 l_role_name_tbl ,
3293 l_start_date_tbl;
3294 CLOSE get_role_cursor;
3295
3296 g_salesrep_info_cache.DELETE;
3297 l_sales_temp_num := -1;
3298 l_sales_perio_id := -1;
3299 l_rec_count := l_salesrep_id_tbl.COUNT;
3300
3301 if l_rec_count > 0
3302 then
3303
3304 FOR i IN l_salesrep_id_tbl.FIRST..l_salesrep_id_tbl.LAST
3305 LOOP
3306 IF l_sales_temp_num <>l_salesrep_id_tbl(i) THEN
3307 IF l_sales_temp_num = -1 OR l_sales_perio_id = -1 THEN
3308 l_sales_temp_num :=l_salesrep_id_tbl(i);
3309 l_sales_perio_id :=l_period_tbl(i);
3310 ELSE
3311 l_salesrep_cache_info.salesrep_id :=l_salesrep_id_tbl(i-1);
3312 l_salesrep_cache_info.period_id :=l_period_tbl(i-1);
3313 l_salesrep_cache_info.role_name :=substr(l_role_str,1,length(l_role_str)-1);
3314 g_salesrep_info_cache(l_salesrep_id_tbl(i-1)) :=l_salesrep_cache_info;
3315 l_role_str :=NULL;
3316 l_sales_temp_num :=l_salesrep_id_tbl(i);
3317 l_sales_perio_id :=l_period_tbl(i);
3318 END IF;
3319 END IF;
3320 l_role_str := l_role_str || l_role_name_tbl(i)||',';
3321 IF (i= l_rec_count) THEN
3322 l_salesrep_cache_info.salesrep_id :=l_salesrep_id_tbl(i);
3323 l_salesrep_cache_info.period_id :=l_period_tbl(i);
3324 l_salesrep_cache_info.role_name :=substr(l_role_str,1,length(l_role_str)-1);
3325 g_salesrep_info_cache(l_salesrep_id_tbl(i)) :=l_salesrep_cache_info;
3326 l_role_str :=NULL;
3327 END IF;
3328 END LOOP;
3329 END IF;
3330 l_role_name_cache := l_role_str;
3331 END IF; --if populate =1
3332 RETURN l_role_name_cache;
3333 END get_role_name_3;
3334
3335
3336
3337 FUNCTION get_user(p_user_id NUMBER DEFAULT NULL,p_payrun_id NUMBER DEFAULT NULL)
3338 RETURN jtf_number_table IS
3339
3340 CURSOR C_USER(l_user_id number,l_payrun_id number)
3341 is
3342 SELECT DISTINCT re2.user_id
3343 FROM jtf_rs_group_usages u2,
3344 jtf_rs_rep_managers m2,
3345 jtf_rs_resource_extns_vl re2,
3346 (SELECT DISTINCT m1.resource_id,
3347 greatest(pr.start_date, m1.start_date_active) start_date,
3348 least(pr.end_date, nvl(m1.end_date_active, pr.end_date)) end_date
3349 FROM jtf_rs_resource_extns re1,
3350 cn_period_statuses pr,
3351 jtf_rs_group_usages u1,
3352 jtf_rs_rep_managers m1
3353 WHERE re1.user_id = l_user_id --129941
3354 AND (pr.period_id, pr.org_id) =
3355 (SELECT p.pay_period_id,
3356 p.org_id
3357 FROM cn_payruns p
3358 WHERE p.payrun_id = l_payrun_id ) -- 852986
3359 AND u1.usage = 'COMP_PAYMENT'
3360 AND ((m1.start_date_active <= pr.end_date) AND
3361 (pr.start_date <= nvl(m1.end_date_active, pr.start_date)))
3362 AND u1.group_id = m1.group_id
3363 AND m1.resource_id = re1.resource_id
3364 AND m1.parent_resource_id = m1.resource_id
3365 AND m1.hierarchy_type IN ('MGR_TO_MGR', 'REP_TO_REP')
3366 AND m1.category <> 'TBH') v3
3367 WHERE u2.usage = 'COMP_PAYMENT'
3368 AND u2.group_id = m2.group_id
3369 AND m2.parent_resource_id = v3.resource_id
3370 AND ((m2.start_date_active <= v3.end_date) AND
3371 (v3.start_date <= nvl(m2.end_date_active, v3.start_date)))
3372 AND m2.category <> 'TBH'
3373 AND m2.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP', 'REP_TO_REP')
3374 AND m2.resource_id = re2.resource_id;
3375
3376
3377 l_user_tab jtf_number_table;
3378 l_user_id number;
3379 l_payrun_id number;
3380 l_found boolean;
3381
3382 Begin
3383
3384 --G_USER_TAB := jtf_number_table();
3385
3386 l_found := FALSE;
3387
3388 IF g_user_tab.COUNT > 0 THEN
3389 --dbms_output.put_line('Returning from cache');
3390 RETURN G_USER_TAB;
3391 END IF;
3392
3393 IF NOT l_found
3394 THEN
3395 l_user_tab := jtf_number_table();
3396
3397 OPEN c_user(p_user_id,p_payrun_id);
3398 FETCH c_user BULK COLLECT INTO l_user_tab;
3399 CLOSE c_user;
3400 G_USER_TAB:=L_USER_TAB;
3401
3402 return l_user_tab;
3403 END IF;
3404
3405 End;
3406
3407 PROCEDURE get_user_info(p_user_id NUMBER DEFAULT NULL,p_payrun_id NUMBER DEFAULT NULL)
3408 is
3409 CURSOR C_USER(l_user_id number,l_payrun_id number)
3410 is
3411 SELECT DISTINCT re2.user_id
3412 FROM jtf_rs_group_usages u2,
3413 jtf_rs_rep_managers m2,
3414 jtf_rs_resource_extns_vl re2,
3415 (SELECT DISTINCT m1.resource_id,
3416 greatest(pr.start_date, m1.start_date_active) start_date,
3417 least(pr.end_date, nvl(m1.end_date_active, pr.end_date)) end_date
3418 FROM jtf_rs_resource_extns re1,
3419 cn_period_statuses pr,
3420 jtf_rs_group_usages u1,
3421 jtf_rs_rep_managers m1
3422 WHERE re1.user_id = l_user_id --129941
3423 AND (pr.period_id, pr.org_id) =
3424 (SELECT p.pay_period_id,
3425 p.org_id
3426 FROM cn_payruns p
3427 WHERE p.payrun_id = l_payrun_id ) -- 852986
3428 AND u1.usage = 'COMP_PAYMENT'
3429 AND ((m1.start_date_active <= pr.end_date) AND
3430 (pr.start_date <= nvl(m1.end_date_active, pr.start_date)))
3431 AND u1.group_id = m1.group_id
3432 AND m1.resource_id = re1.resource_id
3433 AND m1.parent_resource_id = m1.resource_id
3434 AND m1.hierarchy_type IN ('MGR_TO_MGR', 'REP_TO_REP')
3435 AND m1.category <> 'TBH') v3
3436 WHERE u2.usage = 'COMP_PAYMENT'
3437 AND u2.group_id = m2.group_id
3438 AND m2.parent_resource_id = v3.resource_id
3439 AND ((m2.start_date_active <= v3.end_date) AND
3440 (v3.start_date <= nvl(m2.end_date_active, v3.start_date)))
3441 AND m2.category <> 'TBH'
3442 AND m2.hierarchy_type IN ('MGR_TO_MGR', 'MGR_TO_REP', 'REP_TO_REP')
3443 AND m2.resource_id = re2.resource_id;
3444
3445 l_user_tab jtf_number_table;
3446
3447 begin
3448
3449 l_user_tab := jtf_number_table();
3450 G_USER_TAB := jtf_number_table();
3451
3452 OPEN c_user(p_user_id,p_payrun_id);
3453 FETCH c_user BULK COLLECT INTO l_user_tab;
3454 CLOSE c_user;
3455 G_USER_TAB:=L_USER_TAB;
3456 -- dbms_output.put_line('Putting Inside cache');
3457
3458 end;
3459
3460
3461 END CN_API;
[Home] [Help]