1 PACKAGE BODY hr_ae_utility AS
2 /* $Header: hraeutil.pkb 120.0.12020000.2 2012/07/04 21:58:28 amnaraya ship $ */
3 --
4 --
5 -- Formats the full name for the UAE legislation.
6 --
7 FUNCTION per_ae_full_name
8 (p_first_name IN VARCHAR2
9 ,p_middle_names IN VARCHAR2
10 ,p_last_name IN VARCHAR2
11 ,p_known_as IN VARCHAR2
12 ,p_title IN VARCHAR2
13 ,p_suffix IN VARCHAR2
14 ,p_pre_name_adjunct IN VARCHAR2
15 ,p_per_information1 IN VARCHAR2
16 ,p_per_information2 IN VARCHAR2
17 ,p_per_information3 IN VARCHAR2
18 ,p_per_information4 IN VARCHAR2
19 ,p_per_information5 IN VARCHAR2
20 ,p_per_information6 IN VARCHAR2
21 ,p_per_information7 IN VARCHAR2
22 ,p_per_information8 IN VARCHAR2
23 ,p_per_information9 IN VARCHAR2
24 ,p_per_information10 IN VARCHAR2
25 ,p_per_information11 IN VARCHAR2
26 ,p_per_information12 IN VARCHAR2
27 ,p_per_information13 IN VARCHAR2
28 ,p_per_information14 IN VARCHAR2
29 ,p_per_information15 IN VARCHAR2
30 ,p_per_information16 IN VARCHAR2
31 ,p_per_information17 IN VARCHAR2
32 ,p_per_information18 IN VARCHAR2
33 ,p_per_information19 IN VARCHAR2
34 ,p_per_information20 IN VARCHAR2
35 ,p_per_information21 IN VARCHAR2
36 ,p_per_information22 IN VARCHAR2
37 ,p_per_information23 IN VARCHAR2
38 ,p_per_information24 IN VARCHAR2
39 ,p_per_information25 IN VARCHAR2
40 ,p_per_information26 IN VARCHAR2
41 ,p_per_information27 IN VARCHAR2
42 ,p_per_information28 IN VARCHAR2
43 ,p_per_information29 IN VARCHAR2
44 ,p_per_information30 in VARCHAR2) RETURN VARCHAR2 IS
45 --
46 --
47 -- Local variables
48 --
49 l_full_name varchar2(2000);
50 BEGIN
51 --
52 --
53 -- Construct the full name which has the following format:
54 --
55 -- <First>, <Father>, <Grandfather>, <Last>
56 --
57 -- NB. <Father> is held in PER_INFORMATION1 and
58 -- <Grandfather> is held in PER_INFORMATION2.
59 --
60 SELECT SUBSTR(LTRIM(RTRIM (DECODE(p_first_name, null,'',' ' || p_first_name)
61 ||DECODE(p_per_information1, null, '', ' ' || p_per_information1) -- Father
62 ||DECODE(p_per_information2, null, '', ' ' || p_per_information2) -- Grandfather
63 ||DECODE(p_last_name, null, '', ' '||p_last_name)
64 )), 1, 240)
65 INTO l_full_name
66 FROM dual;
67
68 --
69 --
70 -- Return the full name.
71 --
72 RETURN (l_full_name);
73 END per_ae_full_name;
74
75 --Added procedure for Bug 14026965
76 PROCEDURE DERIVE_HR_LOC_ADDRESS
77 (p_tax_name in varchar2,
78 p_style in varchar2,
79 p_address_line_1 in varchar2,
80 p_address_line_2 in varchar2,
81 p_address_line_3 in varchar2,
82 p_town_or_city in varchar2,
83 p_country in varchar2,
84 p_postal_code in varchar2,
85 p_region_1 in varchar2,
86 p_region_2 in varchar2,
87 p_region_3 in varchar2,
88 p_telephone_number_1 in varchar2,
89 p_telephone_number_2 in varchar2,
90 p_telephone_number_3 in varchar2,
91 p_loc_information13 in varchar2,
92 p_loc_information14 in varchar2,
93 p_loc_information15 in varchar2,
94 p_loc_information16 in varchar2,
95 p_loc_information17 in varchar2,
96 p_attribute_category in varchar2,
100 p_attribute4 in varchar2,
97 p_attribute1 in varchar2,
98 p_attribute2 in varchar2,
99 p_attribute3 in varchar2,
101 p_attribute5 in varchar2,
102 p_attribute6 in varchar2,
103 p_attribute7 in varchar2,
104 p_attribute8 in varchar2,
105 p_attribute9 in varchar2,
106 p_attribute10 in varchar2,
107 p_attribute11 in varchar2,
108 p_attribute12 in varchar2,
109 p_attribute13 in varchar2,
110 p_attribute14 in varchar2,
111 p_attribute15 in varchar2,
112 p_attribute16 in varchar2,
113 p_attribute17 in varchar2,
114 p_attribute18 in varchar2,
115 p_attribute19 in varchar2,
116 p_attribute20 in varchar2,
117 p_global_attribute_category in varchar2,
118 p_global_attribute1 in varchar2,
119 p_global_attribute2 in varchar2,
120 p_global_attribute3 in varchar2,
121 p_global_attribute4 in varchar2,
122 p_global_attribute5 in varchar2,
123 p_global_attribute6 in varchar2,
124 p_global_attribute7 in varchar2,
125 p_global_attribute8 in varchar2,
126 p_global_attribute9 in varchar2,
127 p_global_attribute10 in varchar2,
128 p_global_attribute11 in varchar2,
129 p_global_attribute12 in varchar2,
130 p_global_attribute13 in varchar2,
131 p_global_attribute14 in varchar2,
132 p_global_attribute15 in varchar2,
133 p_global_attribute16 in varchar2,
134 p_global_attribute17 in varchar2,
135 p_global_attribute18 in varchar2,
136 p_global_attribute19 in varchar2,
137 p_global_attribute20 in varchar2,
138 p_loc_information18 in varchar2,
139 p_loc_information19 in varchar2,
143 v_city_name varchar2(80);
140 p_loc_information20 in varchar2,
141 p_derived_locale out nocopy varchar2
142 ) is
144 BEGIN
145
146 --Get City Name from HR Lookup
147
148 SELECT DISTINCT MEANING
149 INTO v_city_name
150 FROM HR_LOOKUPS H
151 WHERE H.LOOKUP_TYPE='AE_CITY_CODES'
152 AND H.LOOKUP_CODE = p_town_or_city
153 AND H.ENABLED_FLAG = 'Y'
154 AND H.APPLICATION_ID = 800;
155
156 -- If the above query fetches exactly one row then concatinate
157 p_derived_locale := v_city_name || ', ' || p_country;
158
159 EXCEPTION
160 WHEN NO_DATA_FOUND THEN
161 p_derived_locale := p_country;
162 WHEN TOO_MANY_ROWS THEN
163 p_derived_locale := p_country;
164 END DERIVE_HR_LOC_ADDRESS;
165 --
166
167
168 END hr_ae_utility;