1 PACKAGE CN_API AUTHID CURRENT_USER as
2 /* $Header: cnputils.pls 120.6.12010000.1 2008/07/24 10:51:19 appldev ship $ */
3
4 G_RET_STS_WARNING CONSTANT VARCHAR2(1) := 'W';
5
6 TYPE code_combination_rec IS RECORD
7 ( ccid gl_code_combinations.code_combination_id%TYPE,
8 code_combination VARCHAR2(2000));
9
10 TYPE code_combination_tbl IS TABLE OF code_combination_rec INDEX BY BINARY_INTEGER;
11
12 --| ---------------------------------------------------------------------+
13 --| Procedure Name : get_fnd_message
14 --| Desc : Read from FND Message Stack and put into CN Message stack
15 --| Will initialize FND message stack and flush CN Message stack
16 --| at the end.
17 --| ** This procedure will do a 'COMMIT' inside the code. **
18 --| ---------------------------------------------------------------------+
19 PROCEDURE get_fnd_message( p_msg_count NUMBER,
20 p_msg_data VARCHAR2);
21
22 --| ---------------------------------------------------------------------+
23 --| Function Name : get_rate_table_name
24 --| Desc : Pass in rate table id then return rate table name
25 --| ---------------------------------------------------------------------+
26 FUNCTION get_rate_table_name( p_rate_table_id NUMBER)
27 RETURN cn_rate_schedules.name%TYPE;
28
29 --PRAGMA RESTRICT_REFERENCES (get_rate_table_name,WNDS,WNPS);
30
31 --| ---------------------------------------------------------------------+
32 --| Function Name : get_rate_table_id
33 --| Desc : Pass in rate table name then return rate table id
34 --| ---------------------------------------------------------------------+
35 FUNCTION get_rate_table_id( p_rate_table_name VARCHAR2, p_org_id NUMBER)
36 RETURN cn_rate_schedules.rate_schedule_id%TYPE;
37
38 --PRAGMA RESTRICT_REFERENCES (get_rate_table_id,WNDS,WNPS);
39 --| ---------------------------------------------------------------------+
40 --| Function Name : get_period_name
41 --| Desc : Pass in period id then return period name only if the period
42 --| is 'opened' or 'future entry' status
43 --| ---------------------------------------------------------------------+
44 FUNCTION get_period_name( p_period_id NUMBER, p_org_id NUMBER)
45 RETURN cn_periods.period_name%TYPE;
46
47 --PRAGMA RESTRICT_REFERENCES (get_period_name,WNDS,WNPS);
48 --| ---------------------------------------------------------------------+
49 --| Function Name : get_period_id
50 --| Desc : Pass in period name then return period id only if the period
51 --| is 'opened' or 'future entry' status
52 --| ---------------------------------------------------------------------+
53 FUNCTION get_period_id( p_period_name VARCHAR2, p_org_id NUMBER)
54 RETURN cn_periods.period_id%TYPE;
55
56 --PRAGMA RESTRICT_REFERENCES (get_period_id,WNDS,WNPS);
57 --| ---------------------------------------------------------------------+
58 --| Function Name : get_rev_class_id
59 --| Desc : Pass in revenue class name then return revenue class id
60 --| ---------------------------------------------------------------------+
61 FUNCTION get_rev_class_id( p_rev_class_name VARCHAR2, p_org_id NUMBER)
62 RETURN cn_revenue_classes.revenue_class_id%TYPE;
63
64 --PRAGMA RESTRICT_REFERENCES (get_rev_class_id,WNDS,WNPS);
65 --| ---------------------------------------------------------------------+
66 --| Function Name : get_rev_class_name
67 --| Desc : Pass in revenue class id then return revenue class name
68 --| ---------------------------------------------------------------------+
69 FUNCTION get_rev_class_name( p_rev_class_id number)
70 RETURN cn_revenue_classes.name%TYPE;
71
72 --PRAGMA RESTRICT_REFERENCES (get_rev_class_name,WNDS,WNPS);
73 --| ---------------------------------------------------------------------+
74 --| Function Name : get_lkup_meaning
75 --| Desc : Pass in lookup code and lookup type then return lookup meaning
76 --| ---------------------------------------------------------------------+
77 FUNCTION get_lkup_meaning( p_lkup_code VARCHAR2,
78 p_lkup_type VARCHAR2 )
79 RETURN cn_lookups.meaning%TYPE;
80
81 --PRAGMA RESTRICT_REFERENCES (get_lkup_meaning,WNDS,WNPS);
82 --| ---------------------------------------------------------------------+
83 --| Function Name : chk_miss_char_para
84 --| Desc : Check if the passed in char type parameter is missing
85 --| If so, add error message onto FND message stack and update error
86 --| x_loading_status
87 --| ---------------------------------------------------------------------+
88 FUNCTION chk_miss_char_para ( p_char_para IN VARCHAR2 ,
89 p_para_name IN VARCHAR2 ,
90 p_loading_status IN VARCHAR2 ,
91 x_loading_status OUT NOCOPY VARCHAR2 )
92 RETURN VARCHAR2 ;
93 --| ---------------------------------------------------------------------+
94 --| Function Name : chk_miss_num_para
95 --| Desc : Check if the passed in number type parameter is missing
96 --| If so, add error message onto FND message stack and update error
97 --| x_loading_status
98 --| ---------------------------------------------------------------------+
99 FUNCTION chk_miss_num_para ( p_num_para IN NUMBER ,
100 p_para_name IN VARCHAR2 ,
101 p_loading_status IN VARCHAR2 ,
102 x_loading_status OUT NOCOPY VARCHAR2 )
103 RETURN VARCHAR2 ;
104 --| ---------------------------------------------------------------------+
105 --| Function Name : chk_miss_date_para
106 --| Desc : Check if the passed in date type parameter is missing
107 --| If so, add error message onto FND message stack and update error
108 --| x_loading_status
109 --| ---------------------------------------------------------------------+
110 FUNCTION chk_miss_date_para ( p_date_para IN DATE ,
111 p_para_name IN VARCHAR2 ,
112 p_loading_status IN VARCHAR2 ,
113 x_loading_status OUT NOCOPY VARCHAR2 )
114 RETURN VARCHAR2 ;
115
116 --| ---------------------------------------------------------------------+
117 --| Function Name : chk_null_num_para
118 --| Desc : Check if the passed in number type parameter is null
119 --| If so, add error message onto FND message stack and update error
120 --| x_loading_status
121 --| ---------------------------------------------------------------------+
122 FUNCTION chk_null_num_para ( p_num_para IN NUMBER ,
123 p_obj_name IN VARCHAR2 ,
124 p_loading_status IN VARCHAR2 ,
125 x_loading_status OUT NOCOPY VARCHAR2 )
126 RETURN VARCHAR2 ;
127
128 --| ---------------------------------------------------------------------+
129 --| Function Name : chk_null_char_para
130 --| Desc : Check if the passed in char type parameter is null
131 --| If so, add error message onto FND message stack and update error
132 --| x_loading_status
133 --| ---------------------------------------------------------------------+
134 FUNCTION chk_null_char_para ( p_char_para IN VARCHAR2 ,
135 p_obj_name IN VARCHAR2 ,
136 p_loading_status IN VARCHAR2 ,
137 x_loading_status OUT NOCOPY VARCHAR2 )
138 RETURN VARCHAR2 ;
139 --| ---------------------------------------------------------------------+
140 --| Function Name : chk_miss_null_date_para
141 --| Desc : Check if the passed in date type parameter is null
142 --| If so, add error message onto FND message stack and update error
143 --| x_loading_status
144 --| ---------------------------------------------------------------------+
145 FUNCTION chk_null_date_para ( p_date_para IN DATE ,
146 p_obj_name IN VARCHAR2 ,
147 p_loading_status IN VARCHAR2 ,
148 x_loading_status OUT NOCOPY VARCHAR2 )
149 RETURN VARCHAR2 ;
150
151 --| ---------------------------------------------------------------------+
152 --| Function Name : chk_miss_null_num_para
153 --| Desc : Check if the passed in number type parameter is missing or null
154 --| If so, add error message onto FND message stack and update error
155 --| x_loading_status
156 --| ---------------------------------------------------------------------+
157 FUNCTION chk_miss_null_num_para ( p_num_para IN NUMBER ,
158 p_obj_name IN VARCHAR2 ,
159 p_loading_status IN VARCHAR2 ,
160 x_loading_status OUT NOCOPY VARCHAR2 )
161 RETURN VARCHAR2;
162
163 --| ---------------------------------------------------------------------+
164 --| Function Name : chk_miss_null_char_para
165 --| Desc : Check if the passed in char type parameter is missing or null
166 --| If so, add error message onto FND message stack and update error
167 --| x_loading_status
168 --| ---------------------------------------------------------------------+
169 FUNCTION chk_miss_null_char_para ( p_char_para IN VARCHAR2 ,
170 p_obj_name IN VARCHAR2 ,
171 p_loading_status IN VARCHAR2 ,
172 x_loading_status OUT NOCOPY VARCHAR2 )
173 RETURN VARCHAR2;
174 --| ---------------------------------------------------------------------+
175 --| Function Name : chk_miss_null_date_para
176 --| Desc : Check if the passed in date type parameter is missing or null
177 --| If so, add error message onto FND message stack and update error
178 --| x_loading_status
179 --| ---------------------------------------------------------------------+
180 FUNCTION chk_miss_null_date_para ( p_date_para IN DATE ,
181 p_obj_name IN VARCHAR2 ,
182 p_loading_status IN VARCHAR2 ,
183 x_loading_status OUT NOCOPY VARCHAR2 )
184 RETURN VARCHAR2;
185
186 --| ---------------------------------------------------------------------+
187 --| Function Name : pe_num_field_must_null
188 --| Desc : Check if the Number type field is null
189 --| If NOT,add error message onto FND message stack and update error
190 --| x_loading_status
191 --| ---------------------------------------------------------------------+
192 FUNCTION pe_num_field_must_null ( p_num_field IN NUMBER ,
193 p_pe_type IN VARCHAR2 ,
194 p_obj_name IN VARCHAR2 ,
195 p_token1 IN VARCHAR2 ,
196 p_token2 IN VARCHAR2 ,
197 p_token3 IN VARCHAR2 ,
198 p_loading_status IN VARCHAR2 ,
199 x_loading_status OUT NOCOPY VARCHAR2 )
200 RETURN VARCHAR2 ;
201
202 --| ---------------------------------------------------------------------+
203 --| Function Name : pe_char_field_must_null
204 --| Desc : Check if the char type field is null
205 --| If NOT,add error message onto FND message stack and update error
206 --| x_loading_status
207 --| ---------------------------------------------------------------------+
208 FUNCTION pe_char_field_must_null ( p_char_field IN VARCHAR2 ,
209 p_pe_type IN VARCHAR2 ,
210 p_obj_name IN VARCHAR2 ,
211 p_token1 IN VARCHAR2 ,
212 p_token2 IN VARCHAR2 ,
213 p_token3 IN VARCHAR2 ,
214 p_loading_status IN VARCHAR2 ,
215 x_loading_status OUT NOCOPY VARCHAR2 )
216 RETURN VARCHAR2 ;
217
218 --| ---------------------------------------------------------------------+
219 --| Function Name : pe_num_field_cannot_null
220 --| Desc : Check the numbeer type field can not be null
221 --| If NULL,add error message onto FND message stack and update error
222 --| x_loading_status
223 --| ---------------------------------------------------------------------+
224 FUNCTION pe_num_field_cannot_null( p_num_field IN NUMBER ,
225 p_pe_type IN VARCHAR2 ,
226 p_obj_name IN VARCHAR2 ,
227 p_token1 IN VARCHAR2 ,
228 p_token2 IN VARCHAR2 ,
229 p_token3 IN VARCHAR2 ,
230 p_loading_status IN VARCHAR2 ,
231 x_loading_status OUT NOCOPY VARCHAR2 )
232 RETURN VARCHAR2 ;
233
234 --| ---------------------------------------------------------------------+
235 --| Function Name : pe_char_field_cannot_null
236 --| Desc : Check the char type field can not be null
237 --| If NULL,add error message onto FND message stack and update error
238 --| x_loading_status
239 --| ---------------------------------------------------------------------+
240 FUNCTION pe_char_field_cannot_null( p_char_field IN VARCHAR2 ,
241 p_pe_type IN VARCHAR2 ,
242 p_obj_name IN VARCHAR2 ,
243 p_token1 IN VARCHAR2 ,
244 p_token2 IN VARCHAR2 ,
245 p_token3 IN VARCHAR2 ,
246 p_loading_status IN VARCHAR2 ,
247 x_loading_status OUT NOCOPY VARCHAR2 )
248 RETURN VARCHAR2 ;
249 --| ---------------------------------------------------------------------+
250 --| Function Name : get_cp_name
251 --| Desc : Pass in comp plan id then return comp plan name
252 --| ---------------------------------------------------------------------+
253 FUNCTION get_cp_name( p_comp_plan_id NUMBER)
254 RETURN cn_comp_plans.name%TYPE;
255
256 --PRAGMA RESTRICT_REFERENCES (get_cp_name,WNDS,WNPS);
257 --| ---------------------------------------------------------------------+
258 --| Function Name : get_cp_id
259 --| Desc : Pass in comp plan name then return comp plan id
260 --| ---------------------------------------------------------------------+
261 FUNCTION get_cp_id( p_comp_plan_name VARCHAR2, p_org_id NUMBER)
262 RETURN cn_comp_plans.comp_plan_id%TYPE;
263
264 --PRAGMA RESTRICT_REFERENCES (get_cp_id,WNDS,WNPS);
265
266 --| ---------------------------------------------------------------------+
267 --| Function Name : get_pp_name
268 --| Desc : Pass in payment plan id then return payment plan name
269 --| ---------------------------------------------------------------------+
270 FUNCTION get_pp_name( p_pmt_plan_id NUMBER)
271 RETURN cn_pmt_plans.name%TYPE;
272
273 --PRAGMA RESTRICT_REFERENCES (get_pp_name,WNDS,WNPS);
274 --| ---------------------------------------------------------------------+
275 --| Function Name : get_pp_id
276 --| Desc : Pass in payment plan name then return payment plan id
277 --| ---------------------------------------------------------------------+
278 FUNCTION get_pp_id( p_pmt_plan_name VARCHAR2, p_org_id NUMBER)
279 RETURN cn_pmt_plans.pmt_plan_id%TYPE;
280
281 --PRAGMA RESTRICT_REFERENCES (get_pp_id,WNDS,WNPS);
282
283 --| ---------------------------------------------------------------------+
284 --| Function Name : get_salesrep_name
285 --| Desc : Pass in salesrep id then return salesrep name
286 --| ---------------------------------------------------------------------+
287 FUNCTION get_salesrep_name( p_salesrep_id NUMBER, p_org_id NUMBER)
288 RETURN cn_salesreps.name%TYPE;
289
290 ----PRAGMA RESTRICT_REFERENCES (get_salesrep_name,WNDS,WNPS);
291
292 --| ---------------------------------------------------------------------+
293 --| Function Name : get_salesrep_id
294 --| Desc : Pass in salesrep name and employee number then return salesrep id
295 --| ---------------------------------------------------------------------+
296 FUNCTION get_salesrep_id( p_salesrep_name VARCHAR2, p_emp_num VARCHAR2, p_org_id NUMBER)
297 RETURN cn_salesreps.salesrep_id%TYPE;
298
299 --| ---------------------------------------------------------------------+
300 --| Function Name : chk_and_get_salesrep_id
301 --| Desc : Pass in employee number and salesrep type,
302 --| Check if retrieve only one record, if yes get the salesrep_id
303 --| ---------------------------------------------------------------------+
304 PROCEDURE chk_and_get_salesrep_id( p_emp_num IN VARCHAR2,
305 p_type IN VARCHAR2,
306 p_org_id IN NUMBER,
307 x_salesrep_id OUT NOCOPY cn_salesreps.salesrep_id%TYPE,
308 x_return_status OUT NOCOPY VARCHAR2,
309 x_loading_status OUT NOCOPY VARCHAR2,
310 p_show_message IN VARCHAR2 := FND_API.G_TRUE);
311
312
313 -- --------------------------------------------------------------------------+
314 -- Function : date_range_overlap
315 -- Desc : Check if two set of dates (a_start_date,a_end_date) and
316 -- (b_start_date, b_end_date) are overlap or not.
317 -- Assuming
318 -- 1. a_start_date is not null and a_start_date > a_end_date
319 -- 2. b_start_date is not null and b_start_date > b_end_date
320 -- 3. both end_date can be open (null)
321 -- -------------------------------------------------------------------------+
322 FUNCTION date_range_overlap
323 (
324 a_start_date DATE,
325 a_end_date DATE,
326 b_start_date DATE,
327 b_end_date DATE
328 ) RETURN BOOLEAN;
329
330 --PRAGMA RESTRICT_REFERENCES (date_range_overlap,WNDS,WNPS);
331
335 -- Assuming
332 -- --------------------------------------------------------------------------+
333 -- Function : date_range_within
334 -- Desc : Check if (a_start_date,a_end_date) is within (b_start_date, b_end_date)
336 -- 1. a_start_date is not null and a_start_date > a_end_date
337 -- 2. b_start_date is not null and b_start_date > b_end_date
338 -- 3. both end_date can be open (null)
339 -- -------------------------------------------------------------------------+
340 FUNCTION date_range_within
341 (
342 a_start_date DATE,
343 a_end_date DATE,
344 b_start_date DATE,
345 b_end_date DATE
346 ) RETURN BOOLEAN;
347
348 --PRAGMA RESTRICT_REFERENCES (date_range_within,WNDS,WNPS);
349
350 --| ---------------------------------------------------------------------+
351 --| Function Name : invalid_date_range
352 --| Desc : Check if start date exist, if start_date > end_date
353 --| If so, add error message onto FND message stack and update error
354 --| x_loading_status
355 --| Input : p_end_date_nullable : end_date is nullable only if
356 --| p_end_date_nullable = FND_API.G_TRUE
357 --| ---------------------------------------------------------------------+
358 FUNCTION invalid_date_range
359 ( p_start_date IN DATE ,
360 p_end_date IN DATE ,
361 p_end_date_nullable IN VARCHAR2 := FND_API.G_TRUE,
362 p_loading_status IN VARCHAR2 := NULL ,
363 x_loading_status OUT NOCOPY VARCHAR2,
364 p_show_message IN VARCHAR2 := fnd_api.G_TRUE )
365 RETURN VARCHAR2 ;
366
367
368 --| ---------------------------------------------------------------------+
369 --| Function Name : get_role_id
370 --| Desc : Get the role id using the role name
371 --| ---------------------------------------------------------------------+
372 FUNCTION get_role_id ( p_role_name VARCHAR2 )
373 RETURN cn_roles.role_id%TYPE;
374
375 --PRAGMA RESTRICT_REFERENCES (get_role_id,WNDS,WNPS);
376 --| ---------------------------------------------------------------------+
377 --| Function Name : get_role_name
378 --| Desc : Get the role name using the role ID
379 --| ---------------------------------------------------------------------+
380 FUNCTION get_role_name ( p_role_id VARCHAR2 )
381 RETURN cn_roles.name%TYPE;
382
383 --PRAGMA RESTRICT_REFERENCES (get_role_name,WNDS,WNPS);
384 -- --------------------------------------------------------------------------+
385 -- Function : get_role_plan_id
386 -- Desc : get the role_plan_id if it exists in cn_role_plans
387 -- --------------------------------------------------------------------------+
388 FUNCTION get_role_plan_id
389 (
390 p_role_name IN VARCHAR2,
391 p_comp_plan_name IN VARCHAR2,
392 p_start_date IN DATE,
393 p_end_date IN DATE,
394 p_org_id IN NUMBER
395 ) RETURN cn_role_plans.role_plan_id%TYPE;
396 -- --------------------------------------------------------------------------+
397 -- Function : get_role_pmt_plan_id
398 -- Desc : get the role_pmt_plan_id if it exists in cn_role_pmt_plans
399 -- --------------------------------------------------------------------------+
400 FUNCTION get_role_pmt_plan_id
401 (
402 p_role_name IN VARCHAR2,
403 p_pmt_plan_name IN VARCHAR2,
404 p_start_date IN DATE,
405 p_end_date IN DATE,
406 p_org_id IN NUMBER
407 ) RETURN cn_role_pmt_plans.role_pmt_plan_id%TYPE;
408 -- --------------------------------------------------------------------------+
409 -- Function : get_srp_role_plan_id
410 -- Desc : get the srp_role_plan_id if it exists in cn_srp_roles
411 -- --------------------------------------------------------------------------+
412 FUNCTION get_srp_role_id
413 (p_emp_num IN cn_salesreps.employee_number%type,
414 p_type IN cn_salesreps.TYPE%type,
415 p_role_name IN cn_roles.name%type,
416 p_start_date IN cn_srp_roles.start_date%type,
417 p_end_date IN cn_srp_roles.end_date%TYPE,
418 p_org_id IN cn_salesreps.org_id%type
419 ) RETURN cn_srp_roles.srp_role_id%TYPE;
420 --| ---------------------------------------------------------------------+
421 --| Function Name : get_srp_payee_assign_id
422 --| Desc : Get the srp_payee_assign_id using the
423 --| payee_id, salesrep_id, quota_id, start_date, end_date
424 --| ---------------------------------------------------------------------+
425 FUNCTION get_srp_payee_assign_id ( p_payee_id NUMBER,
426 p_salesrep_id NUMBER,
427 p_quota_id NUMBER,
428 p_start_date DATE,
429 p_end_date DATE,
430 p_org_id NUMBER)
431 RETURN cn_srp_payee_assigns.srp_payee_assign_id%TYPE;
432
433 --PRAGMA RESTRICT_REFERENCES (get_srp_payee_assign_id,WNDS,WNPS);
434
435 --| ---------------------------------------------------------------------+
436 --| Function Name : next_period
437 --| ---------------------------------------------------------------------+
438 FUNCTION next_period (p_end_date DATE, p_org_id NUMBER)
439 RETURN cn_acc_period_statuses_v.end_date%TYPE ;
440
441 --PRAGMA RESTRICT_REFERENCES (next_period,WNDS,WNPS);
442
443 FUNCTION get_pay_period(p_salesrep_id NUMBER,
444 p_date DATE,
445 p_org_id NUMBER )
446 RETURN cn_commission_lines.pay_period_id%TYPE;
447
451 RETURN cn_calc_formulas.itd_flag%TYPE;
448 --PRAGMA RESTRICT_REFERENCES (get_pay_period,WNDS,WNPS);
449
450 FUNCTION get_itd_flag(p_calc_formula_id NUMBER)
452
453 --PRAGMA RESTRICT_REFERENCES (get_itd_flag,WNDS,WNPS);
454 --| ---------------------------------------------------------------------+
455 --| Function Name : get_acc_period_id
456 --| Desc : Pass in period name then return period id only if the period
457 --| is 'opened' or 'future entry' status
458 --| ---------------------------------------------------------------------+
459 FUNCTION get_acc_period_id( p_period_name VARCHAR2, p_org_id NUMBER)
460 RETURN cn_periods.period_id%TYPE;
461
462 --PRAGMA RESTRICT_REFERENCES (get_acc_period_id,WNDS,WNPS);
463 --| ---------------------------------------------------------------------+
464 --| Function Name : get_acc_period_name
465 --| Desc : Pass in period id then return period name only if the period
466 --| is 'opened' or 'future entry' status
467 --| ---------------------------------------------------------------------+
468 FUNCTION get_acc_period_name( p_period_id NUMBER, p_org_id NUMBER)
469 RETURN cn_periods.period_name%TYPE;
470
471 --PRAGMA RESTRICT_REFERENCES (get_acc_period_name,WNDS,WNPS);
472 --| ---------------------------------------------------------------------+
473 --| Function Name : get_quota_assign_id
474 --| Desc : Pass in quota id , comp_plan_id then return quota_assing_id
475 --| ---------------------------------------------------------------------+
476 FUNCTION get_quota_assign_id( p_quota_id NUMBER,
477 p_comp_plan_id NUMBER)
478 RETURN cn_quota_assigns.quota_assign_id%TYPE;
479
480 --PRAGMA RESTRICT_REFERENCES (get_quota_assign_id,WNDS,WNPS);
481
482
483 TYPE date_range_rec_type IS RECORD
484 (start_date DATE,
485 end_date DATE );
486
487 TYPE date_range_tbl_type IS TABLE OF date_range_rec_type
488 INDEX BY BINARY_INTEGER;
489
490 -- --------------------------------------------------------------------------+
491 -- PROCEDURE : get_date_range_overlap
492 -- Desc : get the overlap portion of the two date ranges
493 -- Assuming
494 -- 1. a_start_date is not null and a_start_date < a_end_date
495 -- 2. b_start_date is not null and b_start_date < b_end_date
496 -- 3. both end_date can be open (null)
497 -- -------------------------------------------------------------------------+
498 PROCEDURE get_date_range_overlap
499 (
500 a_start_date DATE,
501 a_end_date DATE,
502 b_start_date DATE,
503 b_end_date DATE,
504 p_org_id NUMBER,
505 x_date_range_tbl OUT NOCOPY date_range_tbl_type );
506
507 --PRAGMA RESTRICT_REFERENCES (get_date_range_overlap,WNDS,WNPS);
508
509 -- -------------------------------------------------------------------------+
510 -- PROCEDURE : get_date_range_diff
511 -- Desc : get the difference portion of the two date ranges
512 -- Assuming
513 -- 1. a_start_date is not null and a_start_date < a_end_date
514 -- 2. b_start_date is not null and b_start_date < b_end_date
515 -- 3. both end_date can be open (null)
516 -- -------------------------------------------------------------------------+
517 PROCEDURE get_date_range_diff
518 (
519 a_start_date DATE,
520 a_end_date DATE,
521 b_start_date DATE,
522 b_end_date DATE,
523 x_date_range_tbl OUT NOCOPY date_range_tbl_type );
524
525 --PRAGMA RESTRICT_REFERENCES (get_date_range_overlap,WNDS,WNPS);
526
527 -- -------------------------------------------------------------------------+
528 -- PROCEDURE : get_date_range_intersect
529 -- Desc : get the intersection of two date ranges
530 -- Assuming
531 -- 1. a_start_date is not null and a_start_date < a_end_date
532 -- 2. b_start_date is not null and b_start_date < b_end_date
533 -- 3. both end_date can be open (null)
534 -- 4. the two date ranges overlap
535 -- -------------------------------------------------------------------------+
536 PROCEDURE get_date_range_intersect
537 (
538 a_start_date DATE,
539 a_end_date DATE,
540 b_start_date DATE,
541 b_end_date DATE,
542 x_start_date OUT NOCOPY DATE,
543 x_end_date OUT NOCOPY DATE);
544
545 --PRAGMA RESTRICT_REFERENCES (get_date_range_diff,WNDS,WNPS);
546
547 TYPE date_range_action_rec_type IS RECORD
548 (start_date DATE,
549 end_date DATE,
550 action_flag VARCHAR2(1));
551
552 TYPE date_range_action_tbl_type IS TABLE OF date_range_action_rec_type
553 INDEX BY BINARY_INTEGER;
554
555 -- -------------------------------------------------------------------------+
556 -- PROCEDURE : get_date_range_diff_action
557 -- Desc : get the difference portion of the two date ranges
558 -- Assuming
559 -- 1. start_date_new is not null and start_date_new < end_date_new
560 -- 2. start_date_old is not null and start_date_old < end_date_old
561 -- 3. both end_date can be open (null)
562 -- -------------------------------------------------------------------------+
563 PROCEDURE get_date_range_diff_action
564 (
565 start_date_new DATE,
566 end_date_new DATE,
567 start_date_old DATE,
568 end_date_old DATE,
569 x_date_range_action_tbl OUT NOCOPY date_range_action_tbl_type );
570
574 -- FUNCTION: get_acc_period_id
571 --PRAGMA RESTRICT_REFERENCES (get_date_range_diff_action,WNDS,WNPS);
572
573 -- -------------------------------------------------------------------------+
575 -- Desc : get the accumulation period_id given the date
576 -- If the date is null, will return the latest accumulation period
577 -- with period_status = 'O'
578 -- -------------------------------------------------------------------------+
579 FUNCTION get_acc_period_id (p_date DATE, p_org_id NUMBER) RETURN NUMBER;
580
581 -- -------------------------------------------------------------------------+
582 -- FUNCTION: get_acc_period_id_fo
583 -- Desc : get the accumulation period_id given the date
584 -- If the date is null, will return the first accumulation period
585 -- with period_status = 'O'
586 -- -------------------------------------------------------------------------+
587 FUNCTION get_acc_period_id_fo (p_date DATE, p_org_id NUMBER) RETURN NUMBER;
588
589 --PRAGMA RESTRICT_REFERENCES (get_acc_period_id,WNDS,WNPS);
590 --| ---------------------------------------------------------------------+
591 --| Procedure Name : check_revenue_class_overlap
592 --| Desc : Pass in Comp Plan ID
593 --| pass in Comp Plan Name
594 --| pass in p_loading_status
595 --| out x_loading_status
596 --| out x_return_status
597 --| ---------------------------------------------------------------------+
598 PROCEDURE check_revenue_class_overlap
599 (
600 p_comp_plan_id IN NUMBER,
601 p_rc_overlap IN VARCHAR2,
602 p_loading_status IN VARCHAR2,
603 x_loading_status OUT NOCOPY VARCHAR2,
604 x_return_status OUT NOCOPY VARCHAR2 );
605
606 --| ---------------------------------------------------------------------+
607 --| Function Name : get_comp_group_name
608 --| Desc : Pass in comp_group id then return comp_group_name
609 --| ---------------------------------------------------------------------+
610 FUNCTION get_comp_group_name( p_comp_group_id NUMBER)
611 RETURN cn_comp_groups.name%TYPE;
612
613 --PRAGMA RESTRICT_REFERENCES (get_comp_group_name,WNDS,WNPS);
614
615 --| ---------------------------------------------------------------------+
616 --| Function Name : get_order_booked_date
617 --| Desc : Pass in order header_id then return date order was booked (or NULL)
618 --| ---------------------------------------------------------------------+
619 --FUNCTION get_order_booked_date(p_order_header_id NUMBER) RETURN DATE;
620 --PRAGMA RESTRICT_REFERENCES (get_order_booked_date,WNDS,WNPS);
621
622 --| ---------------------------------------------------------------------+
623 --| Function Name : get_site_address_id
624 --| Desc : Pass in order site_use_id then return address_id of 'use site'
625 --| (gets address_id out of RA_SITE_USES)
626 --| ---------------------------------------------------------------------+
627 FUNCTION get_site_address_id(p_site_use_id NUMBER, p_org_id NUMBER) RETURN NUMBER;
628
629 --PRAGMA RESTRICT_REFERENCES (get_site_address_id,WNDS,WNPS);
630
631 --| ---------------------------------------------------------------------+
632 --| Function Name : get_order_revenue_type
633 --| Desc : Derives the Revenue Type of an order, in the format required by
634 --| CN
635 --| ---------------------------------------------------------------------+
636 FUNCTION get_order_revenue_type(p_sales_credit_type_id NUMBER) RETURN VARCHAR2;
637
638 --PRAGMA RESTRICT_REFERENCES (get_order_revenue_type,WNDS,WNPS);
639
640 -- |------------------------------------------------------------------------+
641 -- | Function Name : get_credit_info
642 -- |
643 -- | Description : Procedure to return precision and extended precision for credit
644 -- | types
645 -- |------------------------------------------------------------------------+
646 PROCEDURE get_credit_info
647 (p_credit_type_name IN cn_credit_types.name%TYPE, /* credit type name */
648 x_precision OUT NOCOPY NUMBER, /* number of digits to right of decimal*/
649 x_ext_precision OUT NOCOPY NUMBER, /* precision where more precision is needed*/
650 p_org_id IN NUMBER
651 );
652
653 --| ---------------------------------------------------------------------+
654 --| Function Name : convert_to_repcurr
655 --| Desc : Convert from credit unit into salesrep currency amount
656 --| ---------------------------------------------------------------------+
657 FUNCTION convert_to_repcurr
658 (p_credit_unit IN NUMBER,
659 p_conv_date IN DATE ,
660 p_conv_type IN VARCHAR2,
661 p_from_credit_type_id IN NUMBER,
662 p_funcurr_code IN VARCHAR2,
663 p_repcurr_code IN VARCHAR2,
664 p_org_id IN NUMBER
665 ) RETURN NUMBER;
666
667
668 --| ---------------------------------------------------------------------+
669 --| Procedure Name : convert_to_repcurr_report
670 --| Desc : Convert from credit unit into salesrep currency amount
671 --| called by reports.
672 --| ---------------------------------------------------------------------+
673 PROCEDURE convert_to_repcurr_report
674 (p_credit_unit IN NUMBER,
675 p_conv_date IN DATE ,
676 p_conv_type IN VARCHAR2,
677 p_from_credit_type_id IN NUMBER,
678 p_funcurr_code IN VARCHAR2,
679 p_repcurr_code IN VARCHAR2,
680 x_repcurr_amount OUT NOCOPY NUMBER,
681 x_return_status OUT NOCOPY VARCHAR2,
685
682 p_org_id IN NUMBER
683 );
684
686 --| ---------------------------------------------------------------------+
687 --| Function Name : g_miss_char
688 --| Desc : function to return FND_API.g_miss_char
689 --| ---------------------------------------------------------------------+
690 FUNCTION g_miss_char RETURN VARCHAR2;
691
692 --| ---------------------------------------------------------------------+
693 --| Function Name : g_miss_date
694 --| Desc : function to return FND_API.g_miss_date
695 --| ---------------------------------------------------------------------+
696 FUNCTION g_miss_date RETURN DATE;
697
698 --| ---------------------------------------------------------------------+
699 --| Function Name : g_miss_num
700 --| Desc : function to return FND_API.g_miss_num
701 --| ---------------------------------------------------------------------+
702 FUNCTION g_miss_num RETURN NUMBER;
703
704 --| ---------------------------------------------------------------------+
705 --| Function Name : g_miss_id
706 --| Desc : function to return CN_API.g_miss_id
707 --| ---------------------------------------------------------------------+
708 FUNCTION g_miss_id RETURN NUMBER;
709
710 --| ---------------------------------------------------------------------+
711 --| Function Name : g_false
712 --| Desc : function to return FND_API.g_false
713 --| ---------------------------------------------------------------------+
714 FUNCTION g_false RETURN VARCHAR2;
715
716 --| ---------------------------------------------------------------------+
717 --| Function Name : g_true
718 --| Desc : function to return FND_API.g_true
719 --| ---------------------------------------------------------------------+
720 FUNCTION g_true RETURN VARCHAR2;
721
722 --| ---------------------------------------------------------------------+
723 --| Function Name : g_valid_level_none
724 --| Desc : function to return FND_API.G_VALID_LEVEL_NONE
725 --| ---------------------------------------------------------------------+
726 FUNCTION g_valid_level_none RETURN NUMBER;
727
728 --| ---------------------------------------------------------------------+
729 --| Function Name : g_valid_level_full
730 --| Desc : function to return FND_API.G_VALID_LEVEL_FULL
731 --| ---------------------------------------------------------------------+
732 FUNCTION g_valid_level_full RETURN NUMBER;
733
734 --| ---------------------------------------------------------------------+
735 --| Function Name : generate code combinations
736 --| Desc :
737 --| ---------------------------------------------------------------------+
738 PROCEDURE get_ccids
739 (p_account_type IN varchar2,
740 p_org_id IN NUMBER,
741 x_account_structure OUT NOCOPY varchar2,
742 x_code_combinations OUT NOCOPY code_combination_tbl);
743
744 --| ---------------------------------------------------------------------+
745 --| Function Name : get code combination in display format
746 --| Desc :
747 --| ---------------------------------------------------------------------+
748 PROCEDURE get_ccid_disp
749 (p_ccid IN varchar2,
750 p_org_id IN NUMBER,
751 x_code_combination OUT NOCOPY varchar2);
752 --| ---------------------------------------------------------------------+
753 --| Function Name : get code combination in display format
754 --| Desc :
755 --| ---------------------------------------------------------------------+
756 function get_ccid_disp_func
757 (p_ccid IN varchar2, p_org_id IN NUMBER )
758 RETURN varchar2;
759 --| ---------------------------------------------------------------------+
760 --| Function Name : attribute_desc
761 --| Desc : Pass in rule_id, rule_attribute_id
762 --| ---------------------------------------------------------------------+
763 FUNCTION get_attribute_desc( p_rule_id NUMBER,
764 p_attribute_id NUMBER )
765 RETURN VARCHAR2 ;
766
767 --| ---------------------------------------------------------------------+
768 --| Function Name : rule_count
769 --| Desc : Pass in rule_id
770 --| ---------------------------------------------------------------------+
771 FUNCTION get_rule_count( p_rule_id NUMBER)
772 RETURN NUMBER ;
773
774 PRAGMA RESTRICT_REFERENCES (get_attribute_desc,WNDS,WNPS);
775 --| ---------------------------------------------------------------------+
776 -- Procedure : chk_Payrun_status_paid
777 -- Description : Check for valid payrun_id, Status must be unpaid
778 --| ---------------------------------------------------------------------+
779 FUNCTION chk_payrun_status_paid
780 ( p_payrun_id IN NUMBER,
781 p_loading_status IN VARCHAR2,
782 x_loading_status OUT NOCOPY VARCHAR2
783 ) RETURN VARCHAR2 ;
784 --| ---------------------------------------------------------------------+
785 -- Procedure : Chk_hold_status
786 -- Description : This procedure is used to check if the salesrep is on
787 -- hold and valid salesrep ID is passed
788 --| ---------------------------------------------------------------------+
789 FUNCTION chk_hold_status
790 (
791 p_salesrep_id IN NUMBER,
792 p_org_id IN NUMBER,
793 p_loading_status IN VARCHAR2,
794 x_loading_status OUT NOCOPY VARCHAR2
795 ) RETURN VARCHAR2;
796 --| ---------------------------------------------------------------------+
797 -- Procedure : Chk_Srp_hold_status
798 -- Description : This procedure is used to check if the salesrep is on
802 (
799 -- hold and valid salesrep ID is passed
800 --| ---------------------------------------------------------------------+
801 FUNCTION chk_srp_hold_status
803 p_salesrep_id IN NUMBER,
804 p_org_id IN NUMBER,
805 p_loading_status IN VARCHAR2,
806 x_loading_status OUT NOCOPY VARCHAR2
807 ) RETURN VARCHAR2;
808 --| ---------------------------------------------------------------------+
809 -- Function : Get_pay_Element_id(P_quota_id, p_salesrep_id, p_date)
810 --| ---------------------------------------------------------------------+
811 FUNCTION Get_pay_Element_ID
812 (
813 p_quota_id IN NUMBER,
814 p_Salesrep_id IN cn_rs_salesreps.salesrep_id%TYPE,
815 p_org_id IN NUMBER,
816 p_date IN DATE
817 ) RETURN NUMBER;
818
819 -- ===========================================================================
820 -- Procedure : check_duplicate_worksheet
821 -- Description : Check Duplicate Work sheet for salesrep and role in payrun
822 -- ===========================================================================
823 FUNCTION chk_duplicate_worksheet
824 ( p_payrun_id IN NUMBER,
825 p_salesrep_id IN NUMBER,
826 p_org_id IN NUMBER,
827 p_loading_status IN VARCHAR2,
828 x_loading_status OUT NOCOPY VARCHAR2
829 ) RETURN VARCHAR2 ;
830
831 -- ===========================================================================
832 -- Procedure : check_worksheet_status
833 -- Description : Check Worksheet Status
834 -- ===========================================================================
835 FUNCTION chk_worksheet_status
836 ( p_payrun_id IN NUMBER,
837 p_salesrep_id IN NUMBER,
838 p_org_id IN NUMBER,
839 p_loading_status IN VARCHAR2,
840 x_loading_status OUT NOCOPY VARCHAR2
841 ) RETURN VARCHAR2 ;
842 --| ---------------------------------------------------------------------+
843 -- Function : Get_pay_Element_Name(P_element_type_id)
844 --| ---------------------------------------------------------------------+
845 FUNCTION Get_pay_Element_Name
846 (
847 p_element_type_id IN NUMBER
848 ) RETURN VARCHAR2;
849
850 --| -----------------------------------------------------------------------+
851 --| Function Name : can_user_view_page()
852 --| Desc : procedure to test if a HTML page is accessible to a user
853 --| Return true if yes, else return false
854 --| ---------------------------------------------------------------------+
855 procedure can_user_view_page
856 (
857 p_page_name IN varchar2,
858 x_return_status OUT NOCOPY varchar2
859 );
860
861 --| ---------------------------------------------------------------------+
862 --| Function : Is_Payee(p_salesrep_id)
863 --| Desc : Check if passed in salesrep is a Payee
864 --| Return 1 if the passed in salesrep_id is a Payee; otherwise return 0
865 --| ---------------------------------------------------------------------+
866
867 FUNCTION Is_Payee( p_salesrep_id IN NUMBER,
868 p_period_id IN NUMBER,
869 p_org_id IN NUMBER) RETURN NUMBER;
870
871
872 --| ---------------------------------------------------------------------+
873 --| Function : Test_Function(p_function_name)
874 --| Desc : Check if passed in function is allowed
875 --| Return 1 if it is allowed; otherwise return 0
876 --| ---------------------------------------------------------------------+
877
878 FUNCTION Test_Function( p_function_name IN VARCHAR2) RETURN NUMBER;
879
880 FUNCTION get_role_name_2 (period_id NUMBER,
881 salesrep_id NUMBER)
882 RETURN cn_roles.name%TYPE;
883
884 END CN_API;
[Home] [Help]