[Home] [Help]
PACKAGE BODY: APPS.HR_ORT_PKG
Source
1 package body HR_ORT_PKG as
2 /* $Header: hrortlct.pkb 120.1 2011/04/28 11:19:11 sidsaxen ship $ */
3 procedure OWNER_TO_WHO (
4 X_OWNER in VARCHAR2,
5 X_CREATION_DATE out nocopy DATE,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATE_DATE out nocopy DATE,
8 X_LAST_UPDATED_BY out nocopy NUMBER,
9 X_LAST_UPDATE_LOGIN out nocopy NUMBER
10 ) is
11 begin
12 if X_OWNER = 'SEED' then
13 X_CREATED_BY := 1;
14 X_LAST_UPDATED_BY := 1;
15 else
16 X_CREATED_BY := 0;
17 X_LAST_UPDATED_BY := 0;
18 end if;
19 X_CREATION_DATE := sysdate;
20 X_LAST_UPDATE_DATE := sysdate;
21 X_LAST_UPDATE_LOGIN := 0;
22 end OWNER_TO_WHO;
23 procedure INSERT_ROW (
24 X_ORGANIZATION_ID in NUMBER,
25 X_NAME in VARCHAR2,
26 X_CREATION_DATE in DATE,
27 X_CREATED_BY in NUMBER,
28 X_LAST_UPDATE_DATE in DATE,
29 X_LAST_UPDATED_BY in NUMBER,
30 X_LAST_UPDATE_LOGIN in NUMBER
31 ) is
32 begin
33 --
34 -- Added the following code as a part of Zero Downtime Patching Project.
35 -- Code Starts Here.
36 --
37
38 BEGIN
39 PER_RIC_PKG.chk_integrity (
40 p_entity_name => 'HR_ALL_ORGANIZATION_UNITS_TL',
41 p_ref_entity => 'HR_ALL_ORGANIZATION_UNITS',
42 p_ref_column_name => 'ORGANIZATION_ID',
43 p_ref_col_value_number => X_ORGANIZATION_ID,
44 p_ref_col_value_varchar => NULL,
45 p_ref_col_value_date => NULL,
46 p_ref_type => 'INS');
47
48 END;
49 --
50 -- Code Ends Here.
51 --
52 insert into HR_ALL_ORGANIZATION_UNITS_TL (
53 ORGANIZATION_ID,
54 NAME,
55 LAST_UPDATE_DATE,
56 LAST_UPDATED_BY,
57 LAST_UPDATE_LOGIN,
58 CREATED_BY,
59 CREATION_DATE,
60 LANGUAGE,
61 SOURCE_LANG
62 ) values (
63 X_ORGANIZATION_ID,
64 X_NAME,
65 X_LAST_UPDATE_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_LOGIN,
68 X_CREATED_BY,
69 X_CREATION_DATE,
70 userenv('LANG'),
71 userenv('LANG'));
72
73 end INSERT_ROW;
74
75 procedure UPDATE_ROW (
76 X_ORGANIZATION_ID in NUMBER,
77 X_NAME in VARCHAR2,
78 X_LAST_UPDATE_DATE in DATE,
79 X_LAST_UPDATED_BY in NUMBER,
80 X_LAST_UPDATE_LOGIN in NUMBER
81 ) is
82 begin
83
84 update HR_ALL_ORGANIZATION_UNITS_TL set
85 NAME = X_NAME,
86 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
87 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
88 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
89 SOURCE_LANG = userenv('LANG')
90 where ORGANIZATION_ID = X_ORGANIZATION_ID
91 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
92
93 if (sql%notfound) then
94 raise no_data_found;
95 end if;
96 end UPDATE_ROW;
97
98 procedure DELETE_ROW (
99 X_ORGANIZATION_ID in NUMBER
100 ) is
101 begin
102 delete from HR_ALL_ORGANIZATION_UNITS_TL
103 where ORGANIZATION_ID = X_ORGANIZATION_ID;
104
105 if (sql%notfound) then
106 raise no_data_found;
107 end if;
108
109 end DELETE_ROW;
110
111 procedure ADD_LANGUAGE
112 is
113 begin
114 delete from HR_ALL_ORGANIZATION_UNITS_TL T
115 where not exists
116 (select NULL
117 from HR_ALL_ORGANIZATION_UNITS B
118 where B.ORGANIZATION_ID = T.ORGANIZATION_ID
119 );
120
121 update HR_ALL_ORGANIZATION_UNITS_TL T set (
122 NAME
123 ) = (select
124 B.NAME
125 from HR_ALL_ORGANIZATION_UNITS_TL B
126 where B.ORGANIZATION_ID = T.ORGANIZATION_ID
127 and B.LANGUAGE = T.SOURCE_LANG)
128 where (
129 T.ORGANIZATION_ID,
130 T.LANGUAGE
131 ) in (select
132 SUBT.ORGANIZATION_ID,
133 SUBT.LANGUAGE
134 from HR_ALL_ORGANIZATION_UNITS_TL SUBB, HR_ALL_ORGANIZATION_UNITS_TL SUBT
135 where SUBB.ORGANIZATION_ID = SUBT.ORGANIZATION_ID
136 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
137 and (SUBB.NAME <> SUBT.NAME
138 ));
139
140 insert into HR_ALL_ORGANIZATION_UNITS_TL (
141 ORGANIZATION_ID,
142 NAME,
143 LAST_UPDATE_DATE,
144 LAST_UPDATED_BY,
145 LAST_UPDATE_LOGIN,
146 CREATED_BY,
147 CREATION_DATE,
148 LANGUAGE,
149 SOURCE_LANG
150 ) select
151 B.ORGANIZATION_ID,
152 B.NAME,
153 B.LAST_UPDATE_DATE,
154 B.LAST_UPDATED_BY,
155 B.LAST_UPDATE_LOGIN,
156 B.CREATED_BY,
157 B.CREATION_DATE,
158 L.LANGUAGE_CODE,
159 B.SOURCE_LANG
160 from HR_ALL_ORGANIZATION_UNITS_TL B, FND_LANGUAGES L
161 where L.INSTALLED_FLAG in ('I', 'B')
162 and B.LANGUAGE = userenv('LANG')
163 and not exists
164 (select NULL
165 from HR_ALL_ORGANIZATION_UNITS_TL T
166 where T.ORGANIZATION_ID = B.ORGANIZATION_ID
167 and T.LANGUAGE = L.LANGUAGE_CODE);
168 end ADD_LANGUAGE;
169
170 procedure LOAD_ROW(
171 X_ORGANIZATION_ID IN VARCHAR2,
172 X_OWNER IN VARCHAR2,
173 X_NAME IN VARCHAR2) IS
174 X_ROWID ROWID;
175 X_CREATION_DATE DATE;
176 X_CREATED_BY NUMBER;
177 X_LAST_UPDATE_DATE DATE;
178 X_LAST_UPDATED_BY NUMBER;
179 X_LAST_UPDATE_LOGIN NUMBER;
180
181 begin
182
183 OWNER_TO_WHO (
184 X_OWNER,
185 X_CREATION_DATE,
186 X_CREATED_BY,
187 X_LAST_UPDATE_DATE,
188 X_LAST_UPDATED_BY,
189 X_LAST_UPDATE_LOGIN
190 );
191
192 begin
193 UPDATE_ROW (
194 TO_NUMBER(X_ORGANIZATION_ID),
195 X_NAME,
196 X_LAST_UPDATE_DATE,
197 X_LAST_UPDATED_BY,
198 X_LAST_UPDATE_LOGIN
199 );
200
201 exception
202 when no_data_found then
203
204 INSERT_ROW (
205 TO_NUMBER(X_ORGANIZATION_ID),
206 X_NAME,
207 X_CREATION_DATE,
208 X_CREATED_BY,
209 X_LAST_UPDATE_DATE,
210 X_LAST_UPDATED_BY,
211 X_LAST_UPDATE_LOGIN);
212 end;
213
214 end LOAD_ROW;
215
216 procedure TRANSLATE_ROW(
217 X_ORGANIZATION_ID IN VARCHAR2,
218 X_OWNER IN VARCHAR2,
219 X_NAME IN VARCHAR2) IS
220 X_ROWID ROWID;
221 X_CREATION_DATE DATE;
222 X_CREATED_BY NUMBER;
223 X_LAST_UPDATE_DATE DATE;
224 X_LAST_UPDATED_BY NUMBER;
225 X_LAST_UPDATE_LOGIN NUMBER;
226
227 begin
228
229 OWNER_TO_WHO (
230 X_OWNER,
231 X_CREATION_DATE,
232 X_CREATED_BY,
233 X_LAST_UPDATE_DATE,
234 X_LAST_UPDATED_BY,
235 X_LAST_UPDATE_LOGIN
236 );
237
238 update HR_ALL_ORGANIZATION_UNITS_TL set
239 NAME = X_NAME,
240 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
241 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
242 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
243 SOURCE_LANG = userenv('LANG')
244 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
245 and organization_id = TO_NUMBER(x_organization_id);
246
247 end TRANSLATE_ROW;
248 end HR_ORT_PKG;