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