1 PACKAGE BODY hri_edw_dim_job AS
2 /* $Header: hriedjob.pkb 115.3 2002/06/10 01:30:48 pkm ship $ */
3
4 /******************************************************************************/
5 /* This is the function which matches jobs with job category sets. */
6 /* If a job has a job category which belongs to the given job category set, */
7 /* then the job category name is returned, else the n/a (other) name is */
8 /* returned */
9 /******************************************************************************/
10 FUNCTION find_job_category(p_job_id IN NUMBER,
11 p_sequence IN NUMBER)
12 RETURN VARCHAR2 IS
13
14 l_temp NUMBER := to_number(NULL); -- Whether a match is found
15 l_return_value VARCHAR2(80) := NULL; -- Holds the return value
16
17 /* Cursor holding all job categories (codes and lookups) */
18 /* associated with the given job */
19 CURSOR job_cat_cur IS
20 SELECT jei.jei_information1, hrl.meaning
21 FROM per_job_extra_info jei,
22 hr_lookups hrl
23 WHERE jei.job_id = p_job_id
24 AND hrl.lookup_type = 'JOB_CATEGORIES'
25 AND hrl.lookup_code = jei.jei_information1
26 AND information_type = 'Job Category';
27
28 /* Cursor matching job categories with job category sets */
29 CURSOR match_set_cur
30 (v_job_cat_code IN VARCHAR2) IS
31 SELECT 1 FROM hri_job_category_sets
32 WHERE job_category_set = p_sequence
33 AND member_lookup_code = v_job_cat_code;
34
35 /* Cursor to get other lookup code */
36 CURSOR other_cur IS
37 SELECT hrl.meaning
38 FROM hri_job_category_sets jsc,
39 hr_lookups hrl
40 WHERE jsc.job_category_set = p_sequence
41 AND jsc.member_lookup_code IS NULL
42 AND hrl.lookup_type = 'HRI_JOB_CATEGORY_SETS'
43 AND hrl.lookup_code = jsc.other_lookup_code;
44
45 BEGIN
46
47 /* The following loop goes through all the job categories held against the */
48 /* given job and compares with the relevant job category set for a match */
49 /* If matched, the matching lookup is returned */
50 FOR job_cat_rec IN job_cat_cur LOOP
51
52 /* Try and find a match */
53 OPEN match_set_cur(job_cat_rec.jei_information1);
54 FETCH match_set_cur INTO l_temp;
55 CLOSE match_set_cur;
56
57 /* Return the first match */
58 IF (l_temp = 1) THEN
59 RETURN job_cat_rec.meaning;
60 END IF;
61
62 END LOOP;
63
64 /* If no match found then return the other value */
65 OPEN other_cur;
66 FETCH other_cur INTO l_return_value;
67 CLOSE other_cur;
68
69 RETURN l_return_value;
70
71 END find_job_category;
72
73
74 /******************************************************************************/
75 /* This is a dummy procedure which call the add_job_category procedure in the */
76 /* business process layer */
77 /* */
78 /* HRI Job Categories consists of a number of member (lookups) for the set */
79 /* plus one lookup if a job does not match any of the set (other). The single */
80 /* (other) lookup is identified by the member lookup column holding NULL. */
81 /* */
82 /* Add_job_category inserts a row if it does not already exist, but updates */
83 /* the other lookup row if it exists already and is different. */
84 /******************************************************************************/
85 PROCEDURE add_job_category( p_job_cat_set IN NUMBER,
86 p_job_cat_lookup IN VARCHAR2 := null,
87 p_other_lookup IN VARCHAR2 := null )
88 IS
89
90 BEGIN
91 hri_bpl_job.add_job_category
92 ( p_job_cat_set => p_job_cat_set
93 , p_job_cat_lookup => p_job_cat_lookup
94 , p_other_lookup => p_other_lookup);
95
96 END add_job_category;
97
98
99 /******************************************************************************/
100 /* This is a dummy procedure which calls the remove_job_category procedure */
101 /* from the business process layer */
102 /* */
103 /* Removes given job category by blanket delete */
104 /******************************************************************************/
105 PROCEDURE remove_job_category( p_job_cat_set IN NUMBER,
106 p_job_cat_lookup IN VARCHAR2 := null,
107 p_other_lookup IN VARCHAR2 := null )
108 IS
109
110 BEGIN
111 hri_bpl_job.remove_job_category
112 ( p_job_cat_set => p_job_cat_set,
113 p_job_cat_lookup => p_job_cat_lookup,
114 p_other_lookup => p_other_lookup);
115
116 END remove_job_category;
117
118
119 /******************************************************************************/
120 /* Load row simply calls the update procedure */
121 /******************************************************************************/
122 PROCEDURE load_row( p_job_cat_set IN NUMBER,
123 p_job_cat_lookup IN VARCHAR2,
124 p_other_lookup IN VARCHAR2,
125 p_owner IN VARCHAR2 )
126 IS
127
128 BEGIN
129
130 hri_bpl_job.load_row
131 ( p_job_cat_set => p_job_cat_set,
132 p_job_cat_lookup => p_job_cat_lookup,
133 p_other_lookup => p_other_lookup,
134 p_owner => p_owner );
135
136 END load_row;
137
138 END hri_edw_dim_job;
[Home] [Help]