1 package body BSC_SYS_IMAGES_MAP_PKG as
2 /* $Header: BSCSSIMB.pls 115.7 2004/03/04 16:23:36 meastmon ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SOURCE_TYPE in NUMBER,
6 X_SOURCE_CODE in NUMBER,
7 X_TYPE in NUMBER,
8 X_IMAGE_ID in NUMBER,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER
14 ) is
15 cursor C is select ROWID from BSC_SYS_IMAGES_MAP_TL
16 where SOURCE_TYPE = X_SOURCE_TYPE
17 and SOURCE_CODE = X_SOURCE_CODE
18 and TYPE = X_TYPE
19 and LANGUAGE = userenv('LANG')
20 ;
21 begin
22 insert into BSC_SYS_IMAGES_MAP_TL (
23 SOURCE_TYPE,
24 SOURCE_CODE,
25 TYPE,
26 IMAGE_ID,
27 CREATION_DATE ,
28 CREATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY ,
31 LAST_UPDATE_LOGIN,
32 LANGUAGE,
33 SOURCE_LANG
34 ) select
35 X_SOURCE_TYPE,
36 X_SOURCE_CODE,
37 X_TYPE,
38 X_IMAGE_ID,
39 X_CREATION_DATE ,
40 X_CREATED_BY,
41 X_LAST_UPDATE_DATE,
42 X_LAST_UPDATED_BY ,
43 X_LAST_UPDATE_LOGIN,
44 L.LANGUAGE_CODE,
45 userenv('LANG')
46 from FND_LANGUAGES L
47 where L.INSTALLED_FLAG in ('I', 'B')
48 and not exists
49 (select NULL
50 from BSC_SYS_IMAGES_MAP_TL T
51 where T.SOURCE_TYPE = X_SOURCE_TYPE
52 and T.SOURCE_CODE = X_SOURCE_CODE
53 and T.TYPE = X_TYPE
54 and T.LANGUAGE = L.LANGUAGE_CODE);
55
56 open c;
57 fetch c into X_ROWID;
58 if (c%notfound) then
59 close c;
60 raise no_data_found;
61 end if;
62 close c;
63
64 end INSERT_ROW;
65
66 procedure LOCK_ROW (
67 X_SOURCE_TYPE in NUMBER,
68 X_SOURCE_CODE in NUMBER,
69 X_TYPE in NUMBER,
70 X_IMAGE_ID in NUMBER
71 ) is
72 cursor c1 is select
73 IMAGE_ID,
74 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
75 from BSC_SYS_IMAGES_MAP_TL
76 where SOURCE_TYPE = X_SOURCE_TYPE
77 and SOURCE_CODE = X_SOURCE_CODE
78 and TYPE = X_TYPE
79 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
80 for update of SOURCE_TYPE nowait;
81 begin
82 for tlinfo in c1 loop
83 if (tlinfo.BASELANG = 'Y') then
84 if ( (tlinfo.IMAGE_ID = X_IMAGE_ID)
85 ) then
86 null;
87 else
88 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
89 app_exception.raise_exception;
90 end if;
91 end if;
92 end loop;
93 return;
94 end LOCK_ROW;
95
96 procedure UPDATE_ROW (
97 X_SOURCE_TYPE in NUMBER,
98 X_SOURCE_CODE in NUMBER,
99 X_TYPE in NUMBER,
100 X_IMAGE_ID in NUMBER,
101 X_CREATION_DATE in DATE,
102 X_CREATED_BY in NUMBER,
103 X_LAST_UPDATE_DATE in DATE,
104 X_LAST_UPDATED_BY in NUMBER,
105 X_LAST_UPDATE_LOGIN in NUMBER
106 ) is
107 begin
108 update BSC_SYS_IMAGES_MAP_TL set
109 IMAGE_ID = X_IMAGE_ID,
110 LAST_UPDATE_DATE = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_DATE,LAST_UPDATE_DATE),
111 LAST_UPDATED_BY = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATED_BY,LAST_UPDATED_BY),
112 LAST_UPDATE_LOGIN = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_LOGIN,LAST_UPDATE_LOGIN),
113 SOURCE_LANG = userenv('LANG')
114 where SOURCE_TYPE = X_SOURCE_TYPE
115 and SOURCE_CODE = X_SOURCE_CODE
116 and TYPE = X_TYPE
117 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
118
119 if (sql%notfound) then
120 raise no_data_found;
121 end if;
122 end UPDATE_ROW;
123
124 procedure DELETE_ROW (
125 X_SOURCE_TYPE in NUMBER,
126 X_SOURCE_CODE in NUMBER,
127 X_TYPE in NUMBER
128 ) is
129 begin
130 delete from BSC_SYS_IMAGES_MAP_TL
131 where SOURCE_TYPE = X_SOURCE_TYPE
132 and SOURCE_CODE = X_SOURCE_CODE
133 and TYPE = X_TYPE;
134
135 if (sql%notfound) then
136 raise no_data_found;
137 end if;
138
139 end DELETE_ROW;
140
141 procedure ADD_LANGUAGE
142 is
143 l_user NUMBER;
144 begin
145
146 -- Ref: bug#3482442 In corner cases this query can return more than one
147 -- row and it will fail. AUDSID is not PK. After meeting with
148 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
149 l_user := BSC_APPS.fnd_global_user_id;
150
151 update BSC_SYS_IMAGES_MAP_TL T set (
152 IMAGE_ID
153 ) = (select
154 B.IMAGE_ID
155 from BSC_SYS_IMAGES_MAP_TL B
156 where B.SOURCE_TYPE = T.SOURCE_TYPE
157 and B.SOURCE_CODE = T.SOURCE_CODE
158 and B.TYPE = T.TYPE
159 and B.LANGUAGE = T.SOURCE_LANG)
160 where (
161 T.SOURCE_TYPE,
162 T.SOURCE_CODE,
163 T.TYPE,
164 T.LANGUAGE
165 ) in (select
166 SUBT.SOURCE_TYPE,
167 SUBT.SOURCE_CODE,
168 SUBT.TYPE,
169 SUBT.LANGUAGE
170 from BSC_SYS_IMAGES_MAP_TL SUBB, BSC_SYS_IMAGES_MAP_TL SUBT
171 where SUBB.SOURCE_TYPE = SUBT.SOURCE_TYPE
172 and SUBB.SOURCE_CODE = SUBT.SOURCE_CODE
173 and SUBB.TYPE = SUBT.TYPE
174 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
175 and (SUBB.IMAGE_ID <> SUBT.IMAGE_ID
176 ));
177
178 insert into BSC_SYS_IMAGES_MAP_TL (
179 SOURCE_TYPE,
180 SOURCE_CODE,
181 TYPE,
182 IMAGE_ID,
183 CREATION_DATE ,
184 CREATED_BY,
185 LAST_UPDATE_DATE,
186 LAST_UPDATED_BY ,
187 LAST_UPDATE_LOGIN,
188 LANGUAGE,
189 SOURCE_LANG
190 ) select
191 B.SOURCE_TYPE,
192 B.SOURCE_CODE,
193 B.TYPE,
194 B.IMAGE_ID,
195 SYSDATE,
196 l_user,
197 SYSDATE,
198 l_user,
199 l_user,
200 L.LANGUAGE_CODE,
201 B.SOURCE_LANG
202 from BSC_SYS_IMAGES_MAP_TL B, FND_LANGUAGES L
203 where L.INSTALLED_FLAG in ('I', 'B')
204 and B.LANGUAGE = userenv('LANG')
205 and not exists
206 (select NULL
207 from BSC_SYS_IMAGES_MAP_TL T
208 where T.SOURCE_TYPE = B.SOURCE_TYPE
209 and T.SOURCE_CODE = B.SOURCE_CODE
210 and T.TYPE = B.TYPE
211 and T.LANGUAGE = L.LANGUAGE_CODE);
212 end ADD_LANGUAGE;
213
214 end BSC_SYS_IMAGES_MAP_PKG;
[Home] [Help]