1 PACKAGE BODY hrbisofo AS
2 /* $Header: hrbisofo.pkb 115.0 99/07/15 19:38:50 porting shi $ */
3
4
5 -----------------------------------------------------------------------------
6 -- --
7 -- FUNCTION --
8 -- primary_sales_job --
9 -- --
10 -- PARAMETERS: --
11 -- p_person_id sales persons id --
12 -- --
13 -- RETURNS --
14 -- Name of primary sales job ID. If no job is found then null is --
15 -- returned. --
16 -- --
17 -- DESCRIPTION --
18 -- This function returns a persons current primary sales job. All --
19 -- sales jobs must be defined as part of the pre-defined Revenue --
20 -- generating job category. If the person has more than one current --
21 -- sales assignment then the primary assignment will be used in --
22 -- preference to any other. If the person has more than one sales job --
23 -- where none are the primary assignment then the assignment with the --
24 -- earliest starting date will be used. --
25 -- --
26 -- --
27 -----------------------------------------------------------------------------
28 --
29 FUNCTION primary_sales_job
30 (p_person_id in NUMBER) return NUMBER is
31
32 v_job_id per_jobs.job_id%TYPE; -- job ID
33
34 cursor c_job is
35 select job.job_id
36 from per_people_x peo
37 , per_jobs job
38 , per_assignments_x asg
39 , per_job_extra_info inf
40 , hr_lookups lkp
41 where lkp.lookup_type = 'JOB_CATEGORIES'
42 and lkp.lookup_code = inf.jei_information1
43 and inf.jei_information1 = 'RG'
44 and inf.information_type = 'Job Category'
45 and job.job_id = inf.job_id
46 and job.job_id = asg.job_id
47 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
48 and peo.person_id = asg.person_id
49 and peo.person_id = p_person_id
50 order by 1,decode(asg.primary_flag,'Y',1,2),asg.effective_start_date;
51
52
53 BEGIN
54 --
55 -- get the primary sales job for this person
56 --
57 open c_job;
58 fetch c_job into v_job_id;
59 if c_job%notfound then
60 v_job_id := '';
61 end if;
62 close c_job;
63 return v_job_id;
64
65 EXCEPTION
66
67 when others then
68 return null;
69
70 END;
71
72 -----------------------------------------------------------------------------
73 -- End of package
74 -----------------------------------------------------------------------------
75
76 END hrBisOfo;
[Home] [Help]