[Home] [Help]
PACKAGE BODY: APPS.MSC_SD_RP_MASS_PKG
Source
1 package body MSC_SD_RP_MASS_PKG as
2 /* $Header: MSCRPSDB.pls 120.1 2010/04/23 21:29:04 hulu noship $ */
3
4
5 X_LANG VARCHAR2(2);
6
7 procedure INSERT_ROW (
8 X_TABLE_NAME in VARCHAR2,
9 X_COLUMN_NAME in VARCHAR2,
10 X_VALUE_TYPE in VARCHAR2,
11 X_OPERATION_LOV_SQL in VARCHAR2,
12 X_VALUE_LOV_NAME in VARCHAR2,
13 X_FIELD_NAME IN VARCHAR2,
14 X_DEPENDENT_COLUMN 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
22 begin
23 insert into MSC_ORP_MASS_UPDATE_COLUMNS_B (
24 TABLE_NAME,
25 COLUMN_NAME,
26 VALUE_TYPE,
27 OPERATION_LOV_SQL,
28 VALUE_LOV_NAME,
29 DEPENDENT_COLUMN,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_TABLE_NAME,
37 X_COLUMN_NAME,
38 X_VALUE_TYPE,
39 X_OPERATION_LOV_SQL,
40 X_VALUE_LOV_NAME,
41 X_DEPENDENT_COLUMN,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATED_BY,
46 X_LAST_UPDATE_LOGIN
47 );
48
49 insert into MSC_ORP_MASS_UPDATE_COLUMNS_TL (
50 TABLE_NAME,
51 COLUMN_NAME,
52 FIELD_NAME,
53 CREATED_BY,
54 CREATION_DATE,
55 LAST_UPDATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATE_LOGIN,
58 LANGUAGE,
59 SOURCE_LANG
60 ) select
61 X_TABLE_NAME,
62 X_COLUMN_NAME,
63 X_FIELD_NAME,
64 X_CREATED_BY,
65 X_CREATION_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATE_LOGIN,
69 L.LANGUAGE_CODE,
70 userenv('LANG')
71 from FND_LANGUAGES L
72 where L.INSTALLED_FLAG in ('I', 'B')
73 and not exists
74 (select NULL
75 from MSC_ORP_MASS_UPDATE_COLUMNS_TL T
76 where T.TABLE_NAME = X_TABLE_NAME
77 AND T.COLUMN_NAME= X_COLUMN_NAME
78 and T.LANGUAGE = L.LANGUAGE_CODE);
79
80
81 end INSERT_ROW;
82
83
84
85 procedure TRANSLATE_ROW (
86 X_TABLE_NAME in VARCHAR2,
87 X_COLUMN_NAME in VARCHAR2,
88 X_FIELD_NAME IN VARCHAR2 ,
89 X_OWNER in VARCHAR2,
90 X_LAST_UPDATE_DATE in VARCHAR2,
91 X_CUSTOM_MODE in VARCHAR2)
92 is
93 f_luby number; -- entity owner in file
94 f_ludate date; -- entity update date in file
95 db_luby number; -- entity owner in db
96 db_ludate date; -- entity update date in db
97
98
99 begin
100 -- Translate owner to file_last_updated_by
101 f_luby := fnd_load_util.owner_id(x_owner);
102
103 -- Translate char last_update_date to date
104 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
105
106 select last_updated_by, last_update_date
107 into db_luby, db_ludate
108 from MSC_ORP_MASS_UPDATE_COLUMNS_TL
109 where TABLE_NAME = X_TABLE_NAME
110 and COLUMN_NAME = X_COLUMN_NAME
111 and language = userenv('LANG');
112
113 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
114 db_ludate, X_CUSTOM_MODE)) then
115 update MSC_ORP_MASS_UPDATE_COLUMNS_TL set
116 FIELD_NAME = X_FIELD_NAME,
117 last_update_date = f_ludate,
118 last_updated_by = f_luby,
119 last_update_login = 0,
120 source_lang = userenv('LANG')
121 where TABLE_NAME = X_TABLE_NAME
122 and COLUMN_NAME = X_COLUMN_NAME
123 and userenv('LANG') in (language, source_lang);
124
125 END IF;
126
127 end TRANSLATE_ROW;
128
129
130
131 procedure UPDATE_ROW (
132 X_TABLE_NAME IN VARCHAR2,
133 X_COLUMN_NAME IN VARCHAR2,
134 X_VALUE_TYPE IN VARCHAR2,
135 X_OPERATION_LOV_SQL IN VARCHAR2,
136 X_VALUE_LOV_NAME IN VARCHAR2,
137 X_DEPENDENT_COLUMN IN VARCHAR2,
138 X_FIELD_NAME IN VARCHAR2,
139 X_LAST_UPDATE_DATE IN DATE,
140 X_LAST_UPDATED_BY IN NUMBER,
141 X_LAST_UPDATE_LOGIN IN NUMBER
142 ) is
143
144
145 begin
146 update MSC_ORP_MASS_UPDATE_COLUMNS_B set
147 OPERATION_LOV_SQL = X_OPERATION_LOV_SQL,
148 VALUE_TYPE = X_VALUE_TYPE,
149 VALUE_LOV_NAME = X_VALUE_LOV_NAME,
150 DEPENDENT_COLUMN = X_DEPENDENT_COLUMN,
151 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
152 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
153 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
154 where TABLE_NAME = X_TABLE_NAME
155 and COLUMN_NAME = X_COLUMN_NAME;
156
157
158 if (sql%notfound) then
159 raise no_data_found;
160 end if;
161
162 update MSC_ORP_MASS_UPDATE_COLUMNS_TL set
163 FIELD_NAME = X_FIELD_NAME,
164 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
165 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
166 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
167 SOURCE_LANG = userenv('LANG')
168 where TABLE_NAME = X_TABLE_NAME
169 and COLUMN_NAME = X_COLUMN_NAME
170 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
171
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175
176 end UPDATE_ROW;
177
178
179
180 procedure LOAD_ROW (
181 X_TABLE_NAME IN VARCHAR2,
182 X_COLUMN_NAME IN VARCHAR2,
183 X_VALUE_TYPE IN VARCHAR2,
184 X_OPERATION_LOV_SQL IN VARCHAR2,
185 X_VALUE_LOV_NAME IN VARCHAR2,
186 X_FIELD_NAME IN VARCHAR2,
187 X_DEPENDENT_COLUMN IN VARCHAR2,
188 X_OWNER in VARCHAR2,
189 X_LAST_UPDATE_DATE in VARCHAR2,
190 X_CUSTOM_MODE in VARCHAR2
191 )
192 is
193 L_COLUMN_NAME VARCHAR2(50);
194 f_luby number; -- entity owner in file
195 f_ludate date; -- entity update date in file
196 db_luby number; -- entity owner in db
197 db_ludate date; -- entity update date in db
198
199 cursor EXISTING_ROW is
200 select T.COLUMN_NAME
201 from MSC_ORP_MASS_UPDATE_COLUMNS_B T
202 where T.TABLE_NAME = X_TABLE_NAME
203 AND T.COLUMN_NAME =X_COLUMN_NAME;
204
205
206 begin
207
208 -- Translate owner to file_last_updated_by
209 f_luby := fnd_load_util.owner_id(x_owner);
210
211 -- Translate char last_update_date to date
212 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
213
214 open EXISTING_ROW;
215 fetch EXISTING_ROW into L_COLUMN_NAME;
216
217 if (EXISTING_ROW%notfound) then
218
219 Insert_Row(
220 X_TABLE_NAME => X_TABLE_NAME,
221 x_COLUMN_NAME => x_COLUMN_NAME,
222 x_OPERATION_LOV_SQL => X_OPERATION_LOV_SQL,
223 x_VALUE_TYPE => X_VALUE_TYPE,
224 x_VALUE_LOV_NAME => X_VALUE_LOV_NAME,
225 X_FIELD_NAME => x_FIELD_NAME,
226 X_DEPENDENT_COLUMN => X_DEPENDENT_COLUMN,
227 x_creation_date => f_ludate,
228 x_created_by => f_luby,
229 x_last_update_date => f_ludate,
230 x_last_updated_by => f_luby,
231 x_last_update_login => 0);
232 else
233
234 select LAST_UPDATED_BY, LAST_UPDATE_DATE
235 into db_luby, db_ludate
236 from MSC_ORP_MASS_UPDATE_COLUMNS_B
237 where TABLE_NAME = x_TABLE_NAME
238 and COLUMN_NAME = X_COLUMN_NAME;
239
240
241 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
242 db_ludate, X_CUSTOM_MODE)) then
243
244 Update_Row(
245 x_TABLE_NAME => x_TABLE_NAME,
246 x_COLUMN_NAME => X_COLUMN_NAME,
247 x_VALUE_TYPE => X_VALUE_TYPE,
248 x_OPERATION_LOV_SQL => X_OPERATION_LOV_SQL,
249 x_VALUE_LOV_NAME => X_VALUE_LOV_NAME,
250 x_FIELD_NAME => x_FIELD_NAME,
251 X_DEPENDENT_COLUMN => X_DEPENDENT_COLUMN,
252 x_last_update_date => f_ludate,
253 x_last_updated_by => f_luby,
254 x_last_update_login => 0);
255 end if;
256
257
258
259 end if;
260
261 close EXISTING_ROW;
262
263 end LOAD_ROW;
264
265 procedure ADD_LANGUAGE
266 is
267 begin
268
269 insert into msc_orp_mass_update_columns_tl (
270 TABLE_NAME ,
271 COLUMN_NAME ,
272 LANGUAGE ,
273 SOURCE_LANG ,
274 FIELD_NAME ,
275 LAST_UPDATE_DATE ,
276 LAST_UPDATED_BY ,
277 CREATION_DATE ,
278 CREATED_BY ,
279 LAST_UPDATE_LOGIN
280 ) select
281 B.table_name,
282 B.column_name,
283 L.LANGUAGE_CODE,
284 B.SOURCE_LANG,
285 B.field_name,
286 B.LAST_UPDATE_DATE,
287 B.LAST_UPDATED_BY,
288 B.CREATION_DATE,
289 B.CREATED_BY,
290 B.LAST_UPDATE_LOGIN
291 from msc_orp_mass_update_columns_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 msc_orp_mass_update_columns_tl T
297 where T.table_name = B.table_name
298 and T.column_name = B.column_name
299 and T.LANGUAGE = L.LANGUAGE_CODE);
300 end ADD_LANGUAGE;
301
302 end MSC_SD_RP_MASS_PKG;