1 PACKAGE BODY hr_lookups_util_pkg AS
2 /* $Header: hrlkutil.pkb 115.0 2002/05/29 09:30:05 pkm ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2002 Oracle Corporation UK Ltd., *
7 * Reading, England. *
8 * *
9 * All rights reserved. *
10 * *
11 ******************************************************************
12 ==================================================================
13
14 Name : hr_lookups_util_pkg
15
16 Description : Contains packages that allows quick retreival of
17 data that is created in PAY_LEGISLATIVE_FIELD_INFO
18 to map local lookups to Global Lookups.
19
20 Format of the mapping in this table is as follows:
21
22 RULE_TYPE LEG_CODE VALIDATION_TYPE VALIDATION_NAME FIELD_NAME
23 ----------- -------- --------------- --------------- ----------
24 LOCAL_LOOKUP DE TITLE DR DR_DR
25 LOCAL_LOOKUP DE MAR_STATUS SP EX
26 LOCAL_LOOKUP UK TITLE DR DOCTOR
27
28 Change List
29 -----------
30
31 Version Date Author Bug No. Description of Change
32 -------+---------+----------+---------+--------------------------
33
34 115.0 22-apr-02 IHarding Created
35 =================================================================
36 */
37
38 --
39 -- ----------------------- get_legislative_lookup_code ------------
40 --
41 -- This routine returns a legislative lookup code based on the inputs
42 -- of legislation code, lookup type and the global lookup code for
43 -- which you wish to find the localized equivalent.
44 --
45 -- If there does not exists a localized equivalent, the global lookup
46 -- is returned.
47 --
48 -- In the above example, parameters of DE, TITLE and DR would result
49 -- in the value DR_DR returning.
50 --
51 -- Input parameters of UK, TITLE and MR would result in MR being
52 -- returned. And so on.
53 --
54 FUNCTION get_legislative_lookup_code (p_lookup_type VARCHAR2
55 ,p_lookup_code VARCHAR2
56 ,p_legislation_code VARCHAR2)
57 return varchar2 is
58 l_localized_lookup_code varchar2(30);
59 BEGIN
60 --
61 hr_utility.set_location('Entering get_legisative_lookup_code', 10);
62 --
63 select field_name into l_localized_lookup_code
64 from pay_legislative_field_info
65 where rule_type = 'LOCAL_LOOKUP'
66 and legislation_code = p_legislation_code
67 and validation_name = p_lookup_code
68 and validation_type = p_lookup_type;
69 --
70 hr_utility.set_location('Get_leg_lk_code Returning: '||l_localized_lookup_code, 20);
71 --
72 RETURN l_localized_lookup_code;
73
74 EXCEPTION
75 --
76 WHEN OTHERS THEN
77 l_localized_lookup_code := p_lookup_code;
78 hr_utility.set_location('Get_leg_lk_code Returning: '||l_localized_lookup_code, 30);
79 return l_localized_lookup_code;
80 --
81 END get_legislative_lookup_code;
82 --
83 -- ----------------------- get_global_lookup_code ------------------
84 --
85 -- This routine does the opposite of get_legislative_lookup_code.
86 --
87 -- Based on legislation_code, lookup_type and a localized
88 -- lookup_code, the corresponding global code is returned. If there
89 -- is no global code, then the assumption is made that the supplied
90 -- localized code is in fact a global value.
91 --
92 -- In the above example, parameters of DE, TITLE and DR_DR would
93 -- result in the value DR returning.
94 --
95 -- Input parameters of UK, TITLE and MR would result in MR being
96 -- returned.
97 --
98 FUNCTION get_global_lookup_code (p_lookup_type VARCHAR2
99 ,p_lookup_code VARCHAR2
100 ,p_legislation_code VARCHAR2)
101 return varchar2 is
102 l_global_lookup_code varchar2(30);
103 BEGIN
104 --
105 hr_utility.set_location('Entering get_global_lookup_code', 10);
106 --
107 select validation_name into l_global_lookup_code
108 from pay_legislative_field_info
109 where rule_type = 'LOCAL_LOOKUP'
110 and legislation_code = p_legislation_code
111 and field_name = p_lookup_code
112 and validation_type = p_lookup_type;
113 --
114 hr_utility.set_location('Get_global_lk_code Returning: '||l_global_lookup_code, 20);
115 --
116 RETURN l_global_lookup_code;
117 --
118 EXCEPTION
119 --
120 WHEN OTHERS THEN
121 begin
122 l_global_lookup_code := p_lookup_code;
123 hr_utility.set_location('Get_global_lk_code Returning: '||l_global_lookup_code, 30);
124 RETURN l_global_lookup_code;
125 end;
126 --
127 END get_global_lookup_code;
128 --
129 --
130 ------------------------ swap_legislative_lookup_code ---------------
131 --
132 -- This routine takes a legislative code and swaps it for the equivalent
133 -- code in a different legislation. This could be used when moving records
134 -- between business groups and the data contains one field that has a
135 -- localized lookup in the source business group as well as the target
136 -- business group.
137 --
138 -- In the above example, passing in a source legislation of DE, a target
139 -- legislation of UK, a lookup_type of TITLE and a lookup_code of DR_DR
140 -- would result in the value 'DOCTOR' being returned.
141 --
142 -- The function just basically calls the above two functions in order,
143 -- first transforming the DE lookup to global and then finding out the
144 -- UK equivalent of the global lookup.
145 --
146 FUNCTION swap_legislative_lookup_code(p_lookup_type VARCHAR2
147 ,p_lookup_code VARCHAR2
148 ,p_source_leg_code VARCHAR2
149 ,p_target_leg_code VARCHAR2)
150 return varchar2 is
151 l_swapped_lookup_code varchar2(30);
152 l_global_lookup_code varchar2(30);
153 BEGIN
154 --
155 hr_utility.set_location('Entering swap_legislative_lookup_code', 10);
156 --
157 l_global_lookup_code :=
158 get_global_lookup_code(p_lookup_type,
159 p_lookup_code,
160 p_source_leg_code);
161 --
162 hr_utility.set_location('Got global code: '||l_global_lookup_code, 20);
163 --
164 l_swapped_lookup_code :=
165 get_legislative_lookup_code(p_lookup_type,
166 l_global_lookup_code,
167 p_target_leg_code);
168 --
169 --
170 hr_utility.set_location('Swap_leg_lk_code Returning: '||l_swapped_lookup_code, 30);
171 --
172 RETURN l_swapped_lookup_code;
173
174 END swap_legislative_lookup_code;
175 --
176 --
177 END hr_lookups_util_pkg;
[Home] [Help]