[Home] [Help]
PACKAGE BODY: APPS.DT_COLUMN_PROMPTS_PKG
Source
1 PACKAGE body DT_COLUMN_PROMPTS_PKG AS
2 /* $Header: dtclprhi.pkb 115.5 2002/12/09 13:36:34 apholt ship $ */
3 procedure ADD_LANGUAGE
4 is
5 begin
6 update DT_COLUMN_PROMPTS_TL T set (
7 COLUMN_PROMPT
8 ) = (select
9 B.COLUMN_PROMPT
10 from DT_COLUMN_PROMPTS_TL B
11 where B.VIEW_NAME = T.VIEW_NAME
12 and B.COLUMN_NAME = T.COLUMN_NAME
13 and B.LANGUAGE = T.LANGUAGE
14 and B.LANGUAGE = T.SOURCE_LANG)
15 where (
16 T.VIEW_NAME,
17 T.COLUMN_NAME,
18 T.LANGUAGE,
19 T.LANGUAGE
20 ) in (select
21 SUBT.VIEW_NAME,
22 SUBT.COLUMN_NAME,
23 SUBT.LANGUAGE,
24 SUBT.LANGUAGE
25 from DT_COLUMN_PROMPTS_TL SUBB, DT_COLUMN_PROMPTS_TL SUBT
26 where SUBB.VIEW_NAME = SUBT.VIEW_NAME
27 and SUBB.COLUMN_NAME = SUBT.COLUMN_NAME
28 and SUBB.LANGUAGE = SUBT.LANGUAGE
29 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
30 and (SUBB.COLUMN_PROMPT <> SUBT.COLUMN_PROMPT
31 or (SUBB.COLUMN_PROMPT is null and SUBT.COLUMN_PROMPT is not null)
32 or (SUBB.COLUMN_PROMPT is not null and SUBT.COLUMN_PROMPT is null)
33 ));
34
35 insert into DT_COLUMN_PROMPTS_TL (
36 LAST_UPDATE_LOGIN,
37 CREATED_BY,
38 CREATION_DATE,
39 COLUMN_NAME,
40 VIEW_NAME,
41 COLUMN_PROMPT,
42 LAST_UPDATE_DATE,
43 LAST_UPDATED_BY,
44 LANGUAGE,
45 SOURCE_LANG
46 ) select
47 B.LAST_UPDATE_LOGIN,
48 B.CREATED_BY,
49 B.CREATION_DATE,
50 B.COLUMN_NAME,
51 B.VIEW_NAME,
52 B.COLUMN_PROMPT,
53 B.LAST_UPDATE_DATE,
54 B.LAST_UPDATED_BY,
55 L.LANGUAGE_CODE,
56 B.SOURCE_LANG
57 from DT_COLUMN_PROMPTS_TL B, FND_LANGUAGES L
58 where L.INSTALLED_FLAG in ('I', 'B')
59 and B.LANGUAGE = userenv('LANG')
60 and not exists
61 (select NULL
62 from DT_COLUMN_PROMPTS_TL T
63 where T.VIEW_NAME = B.VIEW_NAME
64 and T.COLUMN_NAME = B.COLUMN_NAME
65 and T.LANGUAGE = L.LANGUAGE_CODE);
66 end ADD_LANGUAGE;
67
68 PROCEDURE INSERT_ROW (
69 X_ROWID in out nocopy VARCHAR2,
70 X_VIEW_NAME in VARCHAR2,
71 X_COLUMN_NAME in VARCHAR2,
72 X_COLUMN_PROMPT in VARCHAR2,
73 X_CREATION_DATE in DATE,
74 X_CREATED_BY in NUMBER,
75 X_LAST_UPDATE_DATE in DATE,
76 X_LAST_UPDATED_BY in NUMBER,
77 X_LAST_UPDATE_LOGIN in NUMBER
78 ) IS
79 cursor C is select ROWID from DT_COLUMN_PROMPTS_TL
80 where VIEW_NAME = X_VIEW_NAME
81 and COLUMN_NAME = X_COLUMN_NAME
82 and LANGUAGE = userenv('LANG')
83 ;
84 BEGIN
85 insert into DT_COLUMN_PROMPTS_TL (
86 LAST_UPDATE_LOGIN,
87 CREATED_BY,
88 CREATION_DATE,
89 COLUMN_NAME,
90 VIEW_NAME,
91 COLUMN_PROMPT,
92 LAST_UPDATE_DATE,
93 LAST_UPDATED_BY,
94 LANGUAGE,
95 SOURCE_LANG
96 ) select
97 X_LAST_UPDATE_LOGIN,
98 X_CREATED_BY,
99 X_CREATION_DATE,
100 X_COLUMN_NAME,
101 X_VIEW_NAME,
102 X_COLUMN_PROMPT,
103 X_LAST_UPDATE_DATE,
104 X_LAST_UPDATED_BY,
105 L.LANGUAGE_CODE,
106 userenv('LANG')
107 from FND_LANGUAGES L
108 where L.INSTALLED_FLAG in ('I', 'B')
109 and not exists
110 (select NULL
111 from DT_COLUMN_PROMPTS_TL T
112 where T.VIEW_NAME = X_VIEW_NAME
113 and T.COLUMN_NAME = X_COLUMN_NAME
114 and T.LANGUAGE = L.LANGUAGE_CODE);
115
116 open c;
117 fetch c into X_ROWID;
118 if (c%notfound) then
119 close c;
120 raise no_data_found;
121 end if;
122 close c;
123 --
124 END INSERT_ROW;
125 --
126 procedure UPDATE_ROW (
127 X_VIEW_NAME in VARCHAR2,
128 X_COLUMN_NAME in VARCHAR2,
129 X_COLUMN_PROMPT in VARCHAR2,
130 X_LAST_UPDATE_DATE in DATE,
131 X_LAST_UPDATED_BY in NUMBER,
132 X_LAST_UPDATE_LOGIN in NUMBER
133 ) is
134 begin
135 update DT_COLUMN_PROMPTS_TL set
136 COLUMN_PROMPT = X_COLUMN_PROMPT,
137 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
138 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
139 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
140 SOURCE_LANG = userenv('LANG')
141 where VIEW_NAME = X_VIEW_NAME
142 and COLUMN_NAME = X_COLUMN_NAME
143 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
144
145 if (sql%notfound) then
146 raise no_data_found;
147 end if;
148
149 end UPDATE_ROW;
150 --
151 --
152 PROCEDURE OWNER_TO_WHO (
153 X_OWNER in VARCHAR2,
154 X_CREATION_DATE out nocopy DATE,
155 X_CREATED_BY out nocopy NUMBER,
156 X_LAST_UPDATE_DATE out nocopy DATE,
157 X_LAST_UPDATED_BY out nocopy NUMBER,
158 X_LAST_UPDATE_LOGIN out nocopy NUMBER
159 ) is
160 begin
161 if X_OWNER = 'SEED' then
162 X_CREATED_BY := 1;
163 X_LAST_UPDATED_BY := 1;
164 else
165 X_CREATED_BY := 0;
166 X_LAST_UPDATED_BY := 0;
167 end if;
168 X_CREATION_DATE := sysdate;
169 X_LAST_UPDATE_DATE := sysdate;
170 X_LAST_UPDATE_LOGIN := 0;
171 end OWNER_TO_WHO;
172 --
173 PROCEDURE LOAD_ROW (
174 X_VIEW_NAME in VARCHAR2,
175 X_COLUMN_NAME in VARCHAR2,
176 X_COLUMN_PROMPT in VARCHAR2,
177 X_OWNER in VARCHAR2
178 ) IS
179 X_ROWID ROWID;
180 X_CREATION_DATE DATE;
181 X_CREATED_BY NUMBER;
182 X_LAST_UPDATE_DATE DATE;
183 X_LAST_UPDATED_BY NUMBER;
184 X_LAST_UPDATE_LOGIN NUMBER;
185 begin
186 OWNER_TO_WHO (
187 X_OWNER,
188 X_CREATION_DATE,
189 X_CREATED_BY,
190 X_LAST_UPDATE_DATE,
191 X_LAST_UPDATED_BY,
192 X_LAST_UPDATE_LOGIN
193 );
194 begin
195 UPDATE_ROW (
196 X_VIEW_NAME,
197 X_COLUMN_NAME,
198 X_COLUMN_PROMPT,
199 X_LAST_UPDATE_DATE,
200 X_LAST_UPDATED_BY,
201 X_LAST_UPDATE_LOGIN
202 );
203 exception
204 when no_data_found then
205 INSERT_ROW (
206 X_ROWID,
207 X_VIEW_NAME,
208 X_COLUMN_NAME,
209 X_COLUMN_PROMPT,
210 X_CREATION_DATE,
211 X_CREATED_BY,
212 X_LAST_UPDATE_DATE,
213 X_LAST_UPDATED_BY,
214 X_LAST_UPDATE_LOGIN
215 );
216 end;
217 END LOAD_ROW;
218 --
219 PROCEDURE TRANSLATE_ROW (
220 X_VIEW_NAME in VARCHAR2,
221 X_COLUMN_NAME in VARCHAR2,
222 X_COLUMN_PROMPT in VARCHAR2,
223 X_OWNER in VARCHAR2
224 ) IS
225 X_CREATION_DATE DATE;
226 X_CREATED_BY NUMBER;
227 X_LAST_UPDATE_DATE DATE;
228 X_LAST_UPDATED_BY NUMBER;
229 X_LAST_UPDATE_LOGIN NUMBER;
230 begin
231 OWNER_TO_WHO (
232 X_OWNER,
233 X_CREATION_DATE,
234 X_CREATED_BY,
235 X_LAST_UPDATE_DATE,
236 X_LAST_UPDATED_BY,
237 X_LAST_UPDATE_LOGIN
238 );
239 update DT_COLUMN_PROMPTS_TL set
240 COLUMN_PROMPT = X_COLUMN_PROMPT,
241 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
242 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
243 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
244 SOURCE_LANG = userenv('LANG')
245 where VIEW_NAME = X_VIEW_NAME
246 and COLUMN_NAME = X_COLUMN_NAME
247 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
248 end TRANSLATE_ROW;
249 --
250 end DT_COLUMN_PROMPTS_PKG;