eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HZ_ORIG_SYSTEMS_PKG

Source


1 PACKAGE BODY HZ_ORIG_SYSTEMS_PKG AS
2 /*$Header: ARHOSPTB.pls 120.5 2005/05/25 23:52:26 achung noship $ */
3 
4 procedure INSERT_ROW (
5   X_ORIG_SYSTEM_ID in out nocopy NUMBER,
6   X_ORIG_SYSTEM in VARCHAR2,
7   X_ORIG_SYSTEM_TYPE in VARCHAR2,
8   X_SST_FLAG in VARCHAR2,
9   X_STATUS in VARCHAR2,
10   X_OBJECT_VERSION_NUMBER in NUMBER,
11   X_CREATED_BY_MODULE in VARCHAR2,
12   X_ATTRIBUTE_CATEGORY in VARCHAR2,
13   X_ATTRIBUTE1 in VARCHAR2,
14   X_ATTRIBUTE2 in VARCHAR2,
15   X_ATTRIBUTE3 in VARCHAR2,
16   X_ATTRIBUTE4 in VARCHAR2,
17   X_ATTRIBUTE5 in VARCHAR2,
18   X_ATTRIBUTE6 in VARCHAR2,
19   X_ATTRIBUTE7 in VARCHAR2,
20   X_ATTRIBUTE8 in VARCHAR2,
21   X_ATTRIBUTE9 in VARCHAR2,
22   X_ATTRIBUTE10 in VARCHAR2,
23   X_ATTRIBUTE11 in VARCHAR2,
24   X_ATTRIBUTE12 in VARCHAR2,
25   X_ATTRIBUTE13 in VARCHAR2,
26   X_ATTRIBUTE14 in VARCHAR2,
27   X_ATTRIBUTE15 in VARCHAR2,
28   X_ATTRIBUTE16 in VARCHAR2,
29   X_ATTRIBUTE17 in VARCHAR2,
30   X_ATTRIBUTE18 in VARCHAR2,
31   X_ATTRIBUTE19 in VARCHAR2,
32   X_ATTRIBUTE20 in VARCHAR2,
33   X_ORIG_SYSTEM_NAME in VARCHAR2,
34   X_DESCRIPTION in VARCHAR2--,
35 /*  X_CREATION_DATE in DATE,
36   X_CREATED_BY in NUMBER,
37   X_LAST_UPDATE_DATE in DATE,
38   X_LAST_UPDATED_BY in NUMBER,
39   X_LAST_UPDATE_LOGIN in NUMBER*/
40 ) is
41 
42  CURSOR C2 IS SELECT  HZ_ORIG_SYSTEMS_S.nextval FROM sys.dual;
43  l_success VARCHAR2(1) := 'N';
44 
45 begin
46 	WHILE l_success = 'N' LOOP
47 	BEGIN
48 	IF ( X_ORIG_SYSTEM_ID IS NULL) OR (X_ORIG_SYSTEM_ID = FND_API.G_MISS_NUM) THEN
49            OPEN C2;
50            FETCH C2 INTO X_ORIG_SYSTEM_ID;
51            CLOSE C2;
52           END IF;
53 
54 	    insert into HZ_ORIG_SYSTEMS_B (
55 	    ORIG_SYSTEM_ID,
56 	    ORIG_SYSTEM,
57 	    ORIG_SYSTEM_TYPE,
58 	    SST_FLAG,
59 	    STATUS,
60 	    START_DATE_ACTIVE,
61 	    END_DATE_ACTIVE,
62 	    OBJECT_VERSION_NUMBER,
63 	    CREATED_BY_MODULE,
64 	    ATTRIBUTE_CATEGORY,
65 	    ATTRIBUTE1,
66 	    ATTRIBUTE2,
67 	    ATTRIBUTE3,
68 	    ATTRIBUTE4,
69 	    ATTRIBUTE5,
70 	    ATTRIBUTE6,
71 	    ATTRIBUTE7,
72 	    ATTRIBUTE8,
73 	    ATTRIBUTE9,
74 	    ATTRIBUTE10,
75 	    ATTRIBUTE11,
76 	    ATTRIBUTE12,
77 	    ATTRIBUTE13,
78 	    ATTRIBUTE14,
79 	    ATTRIBUTE15,
80 	    ATTRIBUTE16,
81 	    ATTRIBUTE17,
82 	    ATTRIBUTE18,
83 	    ATTRIBUTE19,
84 	    ATTRIBUTE20,
85 	    CREATION_DATE,
86 	    CREATED_BY,
87 	    LAST_UPDATE_DATE,
88 	    LAST_UPDATED_BY,
89 	    LAST_UPDATE_LOGIN
90 	  ) values (
91 	    X_ORIG_SYSTEM_ID,
92 	    X_ORIG_SYSTEM,
93 	    X_ORIG_SYSTEM_TYPE,
94 	    X_SST_FLAG,
95 	    X_STATUS,
96 	    sysdate,
97 	    decode(X_STATUS,'A',NULL,'I',sysdate),
98 	    X_OBJECT_VERSION_NUMBER,
99 	    X_CREATED_BY_MODULE,
100 	    X_ATTRIBUTE_CATEGORY,
101 	    X_ATTRIBUTE1,
102 	    X_ATTRIBUTE2,
103 	    X_ATTRIBUTE3,
104 	    X_ATTRIBUTE4,
105 	    X_ATTRIBUTE5,
106 	    X_ATTRIBUTE6,
107 	    X_ATTRIBUTE7,
108 	    X_ATTRIBUTE8,
109 	    X_ATTRIBUTE9,
110 	    X_ATTRIBUTE10,
111 	    X_ATTRIBUTE11,
112 	    X_ATTRIBUTE12,
113 	    X_ATTRIBUTE13,
114 	    X_ATTRIBUTE14,
115 	    X_ATTRIBUTE15,
116 	    X_ATTRIBUTE16,
117 	    X_ATTRIBUTE17,
118 	    X_ATTRIBUTE18,
119 	    X_ATTRIBUTE19,
120 	    X_ATTRIBUTE20,
121 	    HZ_UTILITY_V2PUB.CREATION_DATE,
122 	    HZ_UTILITY_V2PUB.CREATED_BY,
123 	    HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
124 	    HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
125 	    HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
126 	    );
127 
128 	  	l_success := 'Y';
129 
130     EXCEPTION
131 	WHEN DUP_VAL_ON_INDEX THEN
132 	     IF INSTRB( SQLERRM, 'HZ_ORIG_SYSTEMS_U1' ) <> 0 THEN
133 		  DECLARE
134 			l_count NUMBER;
135 			l_dummy VARCHAR2(1);
136 		  BEGIN
137 			l_count := 1;
138 			WHILE l_count > 0 LOOP
139 			     SELECT HZ_ORIG_SYSTEMS_S.NEXTVAL
140 			     INTO X_ORIG_SYSTEM_ID FROM dual;
141 			     BEGIN
142 				SELECT 'Y' INTO l_dummy
143 				FROM HZ_ORIG_SYSTEMS_B
144 				WHERE ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID;
145 				l_count := 1;
146 			     EXCEPTION
147 				WHEN NO_DATA_FOUND THEN
148 				     l_count := 0;
149 			     END;
150 			END LOOP;
151 		  END;
152 	     END IF;
153     END;
154     END LOOP;
155 
156 
157   insert into HZ_ORIG_SYSTEMS_TL (
158     ORIG_SYSTEM_ID,
159     ORIG_SYSTEM_NAME,
160     DESCRIPTION,
161     CREATED_BY,
162     CREATION_DATE,
163     LAST_UPDATED_BY,
164     LAST_UPDATE_DATE,
165     LAST_UPDATE_LOGIN,
166     LANGUAGE,
167     SOURCE_LANG
168   ) select
169     X_ORIG_SYSTEM_ID,
170     X_ORIG_SYSTEM_NAME,
171     X_DESCRIPTION,
172     HZ_UTILITY_V2PUB.CREATED_BY,
173     HZ_UTILITY_V2PUB.CREATION_DATE,
174     HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
175     HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
176     HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
177     L.LANGUAGE_CODE,
178     userenv('LANG')
179   from FND_LANGUAGES L
180   where L.INSTALLED_FLAG in ('I', 'B')
181   and not exists
182     (select NULL
183     from HZ_ORIG_SYSTEMS_TL T
184     where T.ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID
185     and T.LANGUAGE = L.LANGUAGE_CODE);
186 
187 end INSERT_ROW;
188 
189 procedure LOCK_ROW (
190   X_ORIG_SYSTEM_ID in NUMBER,
191   X_OBJECT_VERSION_NUMBER in NUMBER
192  ) is
193   cursor c is select
194       OBJECT_VERSION_NUMBER
195     from HZ_ORIG_SYSTEMS_B
196     where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID
197     for update of ORIG_SYSTEM_ID nowait;
198   recinfo c%rowtype;
199 
200   cursor c1 is select
201       ORIG_SYSTEM_NAME,
202       DESCRIPTION,
203       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
204     from HZ_ORIG_SYSTEMS_TL
205     where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID
206     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
207     for update of ORIG_SYSTEM_ID nowait;
208 begin
209   open c;
210   fetch c into recinfo;
211   if (c%notfound) then
212     close c;
213     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
214     app_exception.raise_exception;
215   end if;
216   close c;
217   if (    ( recinfo.OBJECT_VERSION_NUMBER IS NULL AND X_object_version_number IS NULL )
218 		OR ( recinfo.OBJECT_VERSION_NUMBER IS NOT NULL AND
219 		X_object_version_number IS NOT NULL AND
220 		recinfo.OBJECT_VERSION_NUMBER = X_object_version_number )
221   ) then
222     null;
223   else
224     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
225     app_exception.raise_exception;
226   end if;
227   return;
228 end LOCK_ROW;
229 
230 procedure UPDATE_ROW (
231   X_ORIG_SYSTEM_ID in NUMBER,
232   X_ORIG_SYSTEM in VARCHAR2,
233   X_ORIG_SYSTEM_TYPE in VARCHAR2,
234   X_SST_FLAG in VARCHAR2,
235   X_STATUS in VARCHAR2,
236   X_OBJECT_VERSION_NUMBER in NUMBER,
237   X_CREATED_BY_MODULE in VARCHAR2,
238   X_ATTRIBUTE_CATEGORY in VARCHAR2,
239   X_ATTRIBUTE1 in VARCHAR2,
240   X_ATTRIBUTE2 in VARCHAR2,
241   X_ATTRIBUTE3 in VARCHAR2,
242   X_ATTRIBUTE4 in VARCHAR2,
243   X_ATTRIBUTE5 in VARCHAR2,
244   X_ATTRIBUTE6 in VARCHAR2,
245   X_ATTRIBUTE7 in VARCHAR2,
246   X_ATTRIBUTE8 in VARCHAR2,
247   X_ATTRIBUTE9 in VARCHAR2,
248   X_ATTRIBUTE10 in VARCHAR2,
249   X_ATTRIBUTE11 in VARCHAR2,
250   X_ATTRIBUTE12 in VARCHAR2,
251   X_ATTRIBUTE13 in VARCHAR2,
252   X_ATTRIBUTE14 in VARCHAR2,
253   X_ATTRIBUTE15 in VARCHAR2,
254   X_ATTRIBUTE16 in VARCHAR2,
255   X_ATTRIBUTE17 in VARCHAR2,
256   X_ATTRIBUTE18 in VARCHAR2,
257   X_ATTRIBUTE19 in VARCHAR2,
258   X_ATTRIBUTE20 in VARCHAR2,
259   X_ORIG_SYSTEM_NAME in VARCHAR2,
260   X_DESCRIPTION in VARCHAR2--,
261 /*  X_LAST_UPDATE_DATE in DATE,
262   X_LAST_UPDATED_BY in NUMBER,
263   X_LAST_UPDATE_LOGIN in NUMBER*/
264 ) is
265 --p_object_version_number number;
266 
267 begin
268 
269 --p_object_version_number := NVL(X_OBJECT_VERSION_NUMBER, 1) + 1;
270 
271   update HZ_ORIG_SYSTEMS_B set
272     ORIG_SYSTEM = DECODE(X_ORIG_SYSTEM, FND_API.G_MISS_CHAR, NULL,NULL,ORIG_SYSTEM, X_ORIG_SYSTEM),
273     ORIG_SYSTEM_TYPE = DECODE(X_ORIG_SYSTEM_TYPE, FND_API.G_MISS_CHAR, NULL,NULL,ORIG_SYSTEM_TYPE, X_ORIG_SYSTEM_TYPE),
274     SST_FLAG = DECODE(X_SST_FLAG, FND_API.G_MISS_CHAR, NULL,NULL,SST_FLAG, X_SST_FLAG),
275     STATUS = DECODE(X_STATUS, FND_API.G_MISS_CHAR, NULL,NULL,STATUS, X_STATUS),
276     END_DATE_ACTIVE = DECODE(X_STATUS,'I',DECODE(END_DATE_ACTIVE,NULL,SYSDATE,END_DATE_ACTIVE),'A',NULL,NULL,END_DATE_ACTIVE),
277     OBJECT_VERSION_NUMBER = DECODE(X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, X_OBJECT_VERSION_NUMBER),
278     CREATED_BY_MODULE = DECODE(X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL,NULL,CREATED_BY_MODULE, X_CREATED_BY_MODULE),
279     ATTRIBUTE_CATEGORY = DECODE(X_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE_CATEGORY, X_ATTRIBUTE_CATEGORY),
280     ATTRIBUTE1 = DECODE(X_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE1, X_ATTRIBUTE1),
281     ATTRIBUTE2 = DECODE(X_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE2, X_ATTRIBUTE2),
282     ATTRIBUTE3 = DECODE(X_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE3, X_ATTRIBUTE3),
283     ATTRIBUTE4 = DECODE(X_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE4, X_ATTRIBUTE4),
284     ATTRIBUTE5 = DECODE(X_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE5, X_ATTRIBUTE5),
285     ATTRIBUTE6 = DECODE(X_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE6, X_ATTRIBUTE6),
286     ATTRIBUTE7 = DECODE(X_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE7, X_ATTRIBUTE7),
287     ATTRIBUTE8 = DECODE(X_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE8, X_ATTRIBUTE8),
288     ATTRIBUTE9 = DECODE(X_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE9, X_ATTRIBUTE9),
289     ATTRIBUTE10 = DECODE(X_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE10, X_ATTRIBUTE10),
290     ATTRIBUTE11 = DECODE(X_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE11, X_ATTRIBUTE11),
291     ATTRIBUTE12 = DECODE(X_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE12, X_ATTRIBUTE12),
292     ATTRIBUTE13 = DECODE(X_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE13, X_ATTRIBUTE13),
293     ATTRIBUTE14 = DECODE(X_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE14, X_ATTRIBUTE14),
294     ATTRIBUTE15 = DECODE(X_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE15, X_ATTRIBUTE15),
295     ATTRIBUTE16 = DECODE(X_ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE16, X_ATTRIBUTE16),
296     ATTRIBUTE17 = DECODE(X_ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE17, X_ATTRIBUTE17),
297     ATTRIBUTE18 = DECODE(X_ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE18, X_ATTRIBUTE18),
298     ATTRIBUTE19 = DECODE(X_ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE19, X_ATTRIBUTE19),
299     ATTRIBUTE20 = DECODE(X_ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL,NULL,ATTRIBUTE20, X_ATTRIBUTE20),
300     LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
301     LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
302     LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
303   where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID;
304 
305   if (sql%notfound) then
306     raise no_data_found;
307   end if;
308 
309   update HZ_ORIG_SYSTEMS_TL set
310     ORIG_SYSTEM_NAME = DECODE(X_ORIG_SYSTEM_NAME, FND_API.G_MISS_CHAR, NULL,NULL,ORIG_SYSTEM_NAME, X_ORIG_SYSTEM_NAME),
311     DESCRIPTION = DECODE(X_DESCRIPTION, FND_API.G_MISS_CHAR, NULL,NULL,DESCRIPTION, X_DESCRIPTION),
312     LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
313     LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
314     LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
315     SOURCE_LANG = userenv('LANG')
316   where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID
317   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
318 
319   if (sql%notfound) then
320     raise no_data_found;
321   end if;
322 end UPDATE_ROW;
323 
324 procedure DELETE_ROW (
325   X_ORIG_SYSTEM_ID in NUMBER
326 ) is
327 begin
328   delete from HZ_ORIG_SYSTEMS_TL
329   where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID;
330 
331   if (sql%notfound) then
332     raise no_data_found;
333   end if;
334 
335   delete from HZ_ORIG_SYSTEMS_B
336   where ORIG_SYSTEM_ID = X_ORIG_SYSTEM_ID;
337 
338   if (sql%notfound) then
339     raise no_data_found;
340   end if;
341 end DELETE_ROW;
342 
343 procedure ADD_LANGUAGE
344 is
345 begin
346   delete from HZ_ORIG_SYSTEMS_TL T
347   where not exists
348     (select NULL
349     from HZ_ORIG_SYSTEMS_B B
350     where B.ORIG_SYSTEM_ID = T.ORIG_SYSTEM_ID
351     );
352 
353   update HZ_ORIG_SYSTEMS_TL T set (
354       ORIG_SYSTEM_NAME,
355       DESCRIPTION
356     ) = (select
357       B.ORIG_SYSTEM_NAME,
358       B.DESCRIPTION
359     from HZ_ORIG_SYSTEMS_TL B
360     where B.ORIG_SYSTEM_ID = T.ORIG_SYSTEM_ID
361     and B.LANGUAGE = T.SOURCE_LANG)
362   where (
363       T.ORIG_SYSTEM_ID,
364       T.LANGUAGE
365   ) in (select
366       SUBT.ORIG_SYSTEM_ID,
367       SUBT.LANGUAGE
368     from HZ_ORIG_SYSTEMS_TL SUBB, HZ_ORIG_SYSTEMS_TL SUBT
369     where SUBB.ORIG_SYSTEM_ID = SUBT.ORIG_SYSTEM_ID
370     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
371     and (SUBB.ORIG_SYSTEM_NAME <> SUBT.ORIG_SYSTEM_NAME
372       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
373       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
374       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
375   ));
376 
377   insert into HZ_ORIG_SYSTEMS_TL (
378     ORIG_SYSTEM_ID,
379     ORIG_SYSTEM_NAME,
380     DESCRIPTION,
381     CREATED_BY,
382     CREATION_DATE,
383     LAST_UPDATED_BY,
384     LAST_UPDATE_DATE,
385     LAST_UPDATE_LOGIN,
386     LANGUAGE,
387     SOURCE_LANG
388   ) select /*+ ORDERED */
389     B.ORIG_SYSTEM_ID,
390     B.ORIG_SYSTEM_NAME,
391     B.DESCRIPTION,
392     B.CREATED_BY,
393     B.CREATION_DATE,
394     B.LAST_UPDATED_BY,
395     B.LAST_UPDATE_DATE,
396     B.LAST_UPDATE_LOGIN,
397     L.LANGUAGE_CODE,
398     B.SOURCE_LANG
399   from HZ_ORIG_SYSTEMS_TL B, FND_LANGUAGES L
400   where L.INSTALLED_FLAG in ('I', 'B')
401   and B.LANGUAGE = userenv('LANG')
402   and not exists
403     (select NULL
404     from HZ_ORIG_SYSTEMS_TL T
405     where T.ORIG_SYSTEM_ID = B.ORIG_SYSTEM_ID
406     and T.LANGUAGE = L.LANGUAGE_CODE);
407 end ADD_LANGUAGE;
408 
409 end HZ_ORIG_SYSTEMS_PKG;