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