1 package body HXT_HOLIDAY_DAYS_PKG as
2 /* $Header: hxthddml.pkb 120.1 2005/10/07 02:29:49 nissharm noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_ID in NUMBER,
6 X_HCL_ID in NUMBER,
7 X_HOLIDAY_DATE in DATE,
8 X_HOURS in NUMBER,
9 X_NAME in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from HXT_HOLIDAY_DAYS
17 where ID = X_ID
18 ;
19 begin
20 insert into HXT_HOLIDAY_DAYS (
21 ID,
22 HCL_ID,
23 HOLIDAY_DATE,
24 HOURS,
25 NAME,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_ID,
33 X_HCL_ID,
34 X_HOLIDAY_DATE,
35 X_HOURS,
36 X_NAME,
37 X_CREATION_DATE,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_LOGIN
42 );
43
44 insert into HXT_HOLIDAY_DAYS_TL (
45 ID,
46 NAME,
47 LAST_UPDATE_DATE,
48 LAST_UPDATED_BY,
49 LAST_UPDATE_LOGIN,
50 CREATED_BY,
51 CREATION_DATE,
52 LANGUAGE,
53 SOURCE_LANG
54 ) select
55 X_ID,
56 X_NAME,
57 X_LAST_UPDATE_DATE,
58 X_LAST_UPDATED_BY,
59 X_LAST_UPDATE_LOGIN,
60 X_CREATED_BY,
61 X_CREATION_DATE,
62 L.LANGUAGE_CODE,
63 userenv('LANG')
64 from FND_LANGUAGES L
65 where L.INSTALLED_FLAG in ('I', 'B')
66 and not exists
67 (select NULL
68 from HXT_HOLIDAY_DAYS_TL T
69 where T.ID = X_ID
70 and T.LANGUAGE = L.LANGUAGE_CODE);
71
72 open c;
73 fetch c into X_ROWID;
74 if (c%notfound) then
75 close c;
76 raise no_data_found;
77 end if;
78 close c;
79
80 end INSERT_ROW;
81
82 procedure LOCK_ROW (
83 X_ID in NUMBER,
84 X_HCL_ID in NUMBER,
85 X_HOLIDAY_DATE in DATE,
86 X_HOURS in NUMBER,
87 X_NAME in VARCHAR2
88 ) is
89 cursor c is select
90 HCL_ID,
91 HOLIDAY_DATE,
92 HOURS
93 from HXT_HOLIDAY_DAYS
94 where ID = X_ID
95 for update of ID nowait;
96 recinfo c%rowtype;
97
98 cursor c1 is select
99 NAME,
100 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
101 from HXT_HOLIDAY_DAYS_TL
102 where ID = X_ID
103 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
104 for update of ID nowait;
105 begin
106 open c;
107 fetch c into recinfo;
108 if (c%notfound) then
109 close c;
110 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
111 app_exception.raise_exception;
112 end if;
113 close c;
114 if ( (recinfo.HCL_ID = X_HCL_ID)
115 AND (recinfo.HOLIDAY_DATE = X_HOLIDAY_DATE)
116 AND ((recinfo.HOURS = X_HOURS)
117 OR ((recinfo.HOURS is null) AND (X_HOURS is null)))
118 ) then
119 null;
120 else
121 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
122 app_exception.raise_exception;
123 end if;
124
125 for tlinfo in c1 loop
126 if (tlinfo.BASELANG = 'Y') then
127 if ( (tlinfo.NAME = X_NAME)
128 ) then
129 null;
130 else
131 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
132 app_exception.raise_exception;
133 end if;
134 end if;
135 end loop;
136 return;
137 end LOCK_ROW;
138
139 procedure UPDATE_ROW (
140 X_ID in NUMBER,
141 X_HCL_ID in NUMBER,
142 X_HOLIDAY_DATE in DATE,
143 X_HOURS in NUMBER,
144 X_NAME in VARCHAR2,
145 X_LAST_UPDATE_DATE in DATE,
146 X_LAST_UPDATED_BY in NUMBER,
147 X_LAST_UPDATE_LOGIN in NUMBER
148 ) is
149 begin
150 update HXT_HOLIDAY_DAYS set
151 HCL_ID = X_HCL_ID,
152 HOLIDAY_DATE = X_HOLIDAY_DATE,
153 HOURS = X_HOURS,
154 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
155 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
156 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
157 where ID = X_ID;
158
159 if (sql%notfound) then
160 raise no_data_found;
161 end if;
162
163 update HXT_HOLIDAY_DAYS_TL set
164 NAME = X_NAME,
165 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
166 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
167 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
168 SOURCE_LANG = userenv('LANG')
169 where ID = X_ID
170 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
171
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175 end UPDATE_ROW;
176
177 procedure DELETE_ROW (
178 X_ID in NUMBER
179 ) is
180 begin
181 delete from HXT_HOLIDAY_DAYS_TL
182 where ID = X_ID;
183
184 if (sql%notfound) then
185 raise no_data_found;
186 end if;
187
188 delete from HXT_HOLIDAY_DAYS
189 where ID = X_ID;
190
191 if (sql%notfound) then
192 raise no_data_found;
193 end if;
194 end DELETE_ROW;
195
196 procedure ADD_LANGUAGE
197 is
198 begin
199 delete from HXT_HOLIDAY_DAYS_TL T
200 where not exists
201 (select NULL
202 from HXT_HOLIDAY_DAYS B
203 where B.ID = T.ID
204 );
205
206 update HXT_HOLIDAY_DAYS_TL T set (
207 NAME
208 ) = (select
209 B.NAME
210 from HXT_HOLIDAY_DAYS_TL B
211 where B.ID = T.ID
212 and B.LANGUAGE = T.SOURCE_LANG)
213 where (
214 T.ID,
215 T.LANGUAGE
216 ) in (select
217 SUBT.ID,
218 SUBT.LANGUAGE
219 from HXT_HOLIDAY_DAYS_TL SUBB, HXT_HOLIDAY_DAYS_TL SUBT
220 where SUBB.ID = SUBT.ID
221 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
222 and (SUBB.NAME <> SUBT.NAME
223 ));
224
225 insert into HXT_HOLIDAY_DAYS_TL (
226 ID,
227 NAME,
228 LAST_UPDATE_DATE,
229 LAST_UPDATED_BY,
230 LAST_UPDATE_LOGIN,
231 CREATED_BY,
232 CREATION_DATE,
233 LANGUAGE,
234 SOURCE_LANG
235 ) select
236 B.ID,
237 B.NAME,
238 B.LAST_UPDATE_DATE,
239 B.LAST_UPDATED_BY,
240 B.LAST_UPDATE_LOGIN,
241 B.CREATED_BY,
242 B.CREATION_DATE,
243 L.LANGUAGE_CODE,
244 B.SOURCE_LANG
245 from HXT_HOLIDAY_DAYS_TL B, FND_LANGUAGES L
246 where L.INSTALLED_FLAG in ('I', 'B')
247 and B.LANGUAGE = userenv('LANG')
248 and not exists
249 (select NULL
250 from HXT_HOLIDAY_DAYS_TL T
251 where T.ID = B.ID
252 and T.LANGUAGE = L.LANGUAGE_CODE);
253 end ADD_LANGUAGE;
254
255 end HXT_HOLIDAY_DAYS_PKG;
[Home] [Help]