[Home] [Help]
PACKAGE BODY: APPS.EGO_IMPORT_BATCHES_PKG
Source
1 PACKAGE BODY EGO_IMPORT_BATCHES_PKG AS
2 /* $Header: EGOVBATB.pls 120.0 2005/10/27 02:08:44 dsakalle noship $ */
3
4 PROCEDURE INSERT_ROW (
5 X_ROWID IN OUT NOCOPY VARCHAR2,
6 X_BATCH_ID IN NUMBER,
7 X_ORGANIZATION_ID IN NUMBER,
8 X_SOURCE_SYSTEM_ID IN NUMBER,
9 X_BATCH_TYPE IN VARCHAR2,
10 X_ASSIGNEE IN NUMBER,
11 X_BATCH_STATUS IN VARCHAR2,
12 X_OBJECT_VERSION_NUMBER IN NUMBER,
13 X_NAME IN VARCHAR2,
14 X_DESCRIPTION IN VARCHAR2,
15 X_CREATION_DATE IN DATE,
16 X_CREATED_BY IN NUMBER,
17 X_LAST_UPDATE_DATE IN DATE,
18 X_LAST_UPDATED_BY IN NUMBER,
19 X_LAST_UPDATE_LOGIN IN NUMBER)
20 IS
21 CURSOR C IS
22 SELECT ROWID FROM EGO_IMPORT_BATCHES_B
23 WHERE BATCH_ID = X_BATCH_ID;
24 BEGIN
25 INSERT into EGO_IMPORT_BATCHES_B (
26 ORGANIZATION_ID,
27 BATCH_ID,
28 SOURCE_SYSTEM_ID,
29 BATCH_TYPE,
30 ASSIGNEE,
31 BATCH_STATUS,
32 OBJECT_VERSION_NUMBER,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN
38 ) values (
39 X_ORGANIZATION_ID,
40 X_BATCH_ID,
41 X_SOURCE_SYSTEM_ID,
42 X_BATCH_TYPE,
43 X_ASSIGNEE,
44 X_BATCH_STATUS,
45 X_OBJECT_VERSION_NUMBER,
46 X_CREATION_DATE,
47 X_CREATED_BY,
48 X_LAST_UPDATE_DATE,
49 X_LAST_UPDATED_BY,
50 X_LAST_UPDATE_LOGIN
51 );
52
53 INSERT into EGO_IMPORT_BATCHES_TL (
54 BATCH_ID,
55 NAME,
56 DESCRIPTION,
57 OBJECT_VERSION_NUMBER,
58 CREATED_BY,
59 CREATION_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATE_LOGIN,
63 LANGUAGE,
64 SOURCE_LANG
65 ) SELECT
66 X_BATCH_ID,
67 X_NAME,
68 X_DESCRIPTION,
69 X_OBJECT_VERSION_NUMBER,
70 X_CREATED_BY,
71 X_CREATION_DATE,
72 X_LAST_UPDATED_BY,
73 X_LAST_UPDATE_DATE,
74 X_LAST_UPDATE_LOGIN,
75 L.LANGUAGE_CODE,
76 USERENV('LANG')
77 FROM FND_LANGUAGES L
78 WHERE L.INSTALLED_FLAG IN ('I', 'B')
79 AND NOT EXISTS
80 (SELECT NULL
81 FROM EGO_IMPORT_BATCHES_TL T
82 WHERE T.BATCH_ID = X_BATCH_ID
83 AND T.LANGUAGE = L.LANGUAGE_CODE);
84
85 OPEN C;
86 FETCH C INTO X_ROWID;
87 IF (C%NOTFOUND) THEN
88 CLOSE C;
89 RAISE NO_DATA_FOUND;
90 END IF;
91 CLOSE C;
92 END INSERT_ROW;
93
94 PROCEDURE LOCK_ROW (
95 X_BATCH_ID IN NUMBER,
96 X_ORGANIZATION_ID IN NUMBER,
97 X_SOURCE_SYSTEM_ID IN NUMBER,
98 X_BATCH_TYPE IN VARCHAR2,
99 X_ASSIGNEE IN NUMBER,
100 X_BATCH_STATUS IN VARCHAR2,
101 X_OBJECT_VERSION_NUMBER IN NUMBER,
102 X_NAME IN VARCHAR2,
103 X_DESCRIPTION IN VARCHAR2
104 ) IS
105 CURSOR C IS
106 SELECT
107 ORGANIZATION_ID,
108 SOURCE_SYSTEM_ID,
109 BATCH_TYPE,
110 ASSIGNEE,
111 BATCH_STATUS,
112 OBJECT_VERSION_NUMBER
113 FROM EGO_IMPORT_BATCHES_B
114 WHERE BATCH_ID = X_BATCH_ID
115 FOR UPDATE OF BATCH_ID NOWAIT;
116
117 RECINFO C%ROWTYPE;
118
119 CURSOR C1 IS
120 SELECT
121 NAME,
122 DESCRIPTION,
123 DECODE(LANGUAGE, USERENV('LANG'), 'Y', 'N') BASELANG
124 FROM EGO_IMPORT_BATCHES_TL
125 WHERE BATCH_ID = X_BATCH_ID
126 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
127 FOR UPDATE OF BATCH_ID NOWAIT;
128 BEGIN
129 OPEN C;
130 FETCH C INTO recinfo;
131 IF (C%NOTFOUND) THEN
132 CLOSE C;
133 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
134 APP_EXCEPTION.RAISE_EXCEPTION;
135 END IF;
136 CLOSE C;
137 IF ( (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
138 AND (recinfo.SOURCE_SYSTEM_ID = X_SOURCE_SYSTEM_ID)
139 AND (recinfo.BATCH_TYPE = X_BATCH_TYPE)
140 AND ((recinfo.ASSIGNEE = X_ASSIGNEE)
141 OR ((recinfo.ASSIGNEE IS NULL) AND (X_ASSIGNEE IS NULL)))
142 AND (recinfo.BATCH_STATUS = X_BATCH_STATUS)
143 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
144 ) THEN
145 NULL;
146 ELSE
147 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
148 APP_EXCEPTION.RAISE_EXCEPTION;
149 END IF;
150
151 FOR tlinfo IN c1 LOOP
152 IF (tlinfo.BASELANG = 'Y') THEN
153 IF ( (tlinfo.NAME = X_NAME)
154 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
155 OR ((tlinfo.DESCRIPTION IS NULL) AND (X_DESCRIPTION IS NULL)))
156 ) THEN
157 NULL;
158 ELSE
159 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
160 APP_EXCEPTION.RAISE_EXCEPTION;
161 END IF;
162 END IF;
163 END LOOP;
164 RETURN;
165 END LOCK_ROW;
166
167 PROCEDURE UPDATE_ROW (
168 X_BATCH_ID IN NUMBER,
169 X_ORGANIZATION_ID IN NUMBER,
170 X_SOURCE_SYSTEM_ID IN NUMBER,
171 X_BATCH_TYPE IN VARCHAR2,
172 X_ASSIGNEE IN NUMBER,
173 X_BATCH_STATUS IN VARCHAR2,
174 X_OBJECT_VERSION_NUMBER IN NUMBER,
175 X_NAME IN VARCHAR2,
176 X_DESCRIPTION IN VARCHAR2,
177 X_LAST_UPDATE_DATE IN DATE,
178 X_LAST_UPDATED_BY IN NUMBER,
179 X_LAST_UPDATE_LOGIN IN NUMBER
180 ) IS
181 BEGIN
182 UPDATE EGO_IMPORT_BATCHES_B SET
183 ORGANIZATION_ID = X_ORGANIZATION_ID,
184 SOURCE_SYSTEM_ID = X_SOURCE_SYSTEM_ID,
185 BATCH_TYPE = X_BATCH_TYPE,
186 ASSIGNEE = X_ASSIGNEE,
187 BATCH_STATUS = X_BATCH_STATUS,
188 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
189 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
190 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
191 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
192 WHERE BATCH_ID = X_BATCH_ID;
193
194 IF (SQL%NOTFOUND) THEN
195 RAISE NO_DATA_FOUND;
196 END IF;
197
198 UPDATE EGO_IMPORT_BATCHES_TL SET
199 NAME = X_NAME,
200 DESCRIPTION = X_DESCRIPTION,
201 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
202 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
203 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
204 SOURCE_LANG = USERENV('LANG')
205 WHERE BATCH_ID = X_BATCH_ID
206 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
207
208 IF (SQL%NOTFOUND) THEN
209 RAISE NO_DATA_FOUND;
210 END IF;
211 END UPDATE_ROW;
212
213 PROCEDURE DELETE_ROW (
214 X_BATCH_ID IN NUMBER
215 ) IS
216 BEGIN
217 DELETE FROM EGO_IMPORT_BATCHES_TL
218 WHERE BATCH_ID = X_BATCH_ID;
219
220 IF (SQL%NOTFOUND) THEN
221 RAISE NO_DATA_FOUND;
222 END IF;
223
224 DELETE FROM EGO_IMPORT_BATCHES_B
225 WHERE BATCH_ID = X_BATCH_ID;
226
227 IF (SQL%NOTFOUND) THEN
228 RAISE NO_DATA_FOUND;
229 END IF;
230 END DELETE_ROW;
231
232
233 PROCEDURE ADD_LANGUAGE
234 IS
235 BEGIN
236 DELETE FROM EGO_IMPORT_BATCHES_TL T
237 WHERE NOT EXISTS
238 (SELECT NULL
239 FROM EGO_IMPORT_BATCHES_B B
240 WHERE B.BATCH_ID = T.BATCH_ID
241 );
242
243 UPDATE EGO_IMPORT_BATCHES_TL T SET (
244 NAME,
245 DESCRIPTION
246 ) = (SELECT
247 B.NAME,
248 B.DESCRIPTION
249 FROM EGO_IMPORT_BATCHES_TL B
250 WHERE B.BATCH_ID = T.BATCH_ID
251 AND B.LANGUAGE = T.SOURCE_LANG)
252 WHERE (
253 T.BATCH_ID,
254 T.LANGUAGE
255 ) IN (SELECT
256 SUBT.BATCH_ID,
257 SUBT.LANGUAGE
258 FROM EGO_IMPORT_BATCHES_TL SUBB, EGO_IMPORT_BATCHES_TL SUBT
259 WHERE SUBB.BATCH_ID = SUBT.BATCH_ID
260 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
261 AND (SUBB.NAME <> SUBT.NAME
262 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
263 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
264 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
265 ));
266
267 INSERT INTO EGO_IMPORT_BATCHES_TL (
268 BATCH_ID,
269 NAME,
270 DESCRIPTION,
271 OBJECT_VERSION_NUMBER,
272 CREATED_BY,
273 CREATION_DATE,
274 LAST_UPDATED_BY,
275 LAST_UPDATE_DATE,
276 LAST_UPDATE_LOGIN,
277 LANGUAGE,
278 SOURCE_LANG
279 ) SELECT /*+ ORDERED */
280 B.BATCH_ID,
281 B.NAME,
282 B.DESCRIPTION,
283 B.OBJECT_VERSION_NUMBER,
284 B.CREATED_BY,
285 B.CREATION_DATE,
286 B.LAST_UPDATED_BY,
287 B.LAST_UPDATE_DATE,
288 B.LAST_UPDATE_LOGIN,
289 L.LANGUAGE_CODE,
290 B.SOURCE_LANG
291 FROM EGO_IMPORT_BATCHES_TL B, FND_LANGUAGES L
292 WHERE L.INSTALLED_FLAG IN ('I', 'B')
293 AND B.LANGUAGE = USERENV('LANG')
294 AND NOT EXISTS
295 (SELECT NULL
296 FROM EGO_IMPORT_BATCHES_TL T
297 WHERE T.BATCH_ID = B.BATCH_ID
298 AND T.LANGUAGE = L.LANGUAGE_CODE);
299 END ADD_LANGUAGE;
300
301 END EGO_IMPORT_BATCHES_PKG;