1 package body PAY_REPORT_FORMAT_MAPPINGS_PKG as
2 /* $Header: pyrfm01t.pkb 115.0 2004/04/06 04:15:05 mkataria noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_REPORT_FORMAT_MAPPING_ID in NUMBER,
6 X_TEMPORARY_ACTION_FLAG in VARCHAR2,
7 X_BUSINESS_GROUP_ID in NUMBER,
8 X_LEGISLATION_CODE in VARCHAR2,
9 X_REPORT_TYPE in VARCHAR2,
10 X_REPORT_QUALIFIER in VARCHAR2,
11 X_REPORT_FORMAT in VARCHAR2,
12 X_EFFECTIVE_START_DATE in DATE,
13 X_EFFECTIVE_END_DATE in DATE,
14 X_RANGE_CODE in VARCHAR2,
15 X_ASSIGNMENT_ACTION_CODE in VARCHAR2,
16 X_INITIALIZATION_CODE in VARCHAR2,
17 X_ARCHIVE_CODE in VARCHAR2,
18 X_MAGNETIC_CODE in VARCHAR2,
19 X_REPORT_CATEGORY in VARCHAR2,
20 X_REPORT_NAME in VARCHAR2,
21 X_SORT_CODE in VARCHAR2,
22 X_UPDATABLE_FLAG in VARCHAR2,
23 X_DEINITIALIZATION_CODE in VARCHAR2,
24 X_DISPLAY_NAME in VARCHAR2 ) is
25 cursor C is select ROWID from PAY_REPORT_FORMAT_MAPPINGS_F
26 where REPORT_FORMAT_MAPPING_ID = X_REPORT_FORMAT_MAPPING_ID
27 ;
28 begin insert into PAY_REPORT_FORMAT_MAPPINGS_F (
29 TEMPORARY_ACTION_FLAG,
30 REPORT_FORMAT_MAPPING_ID,
31 BUSINESS_GROUP_ID,
32 LEGISLATION_CODE,
33 REPORT_TYPE,
34 REPORT_QUALIFIER,
35 REPORT_FORMAT,
36 EFFECTIVE_START_DATE,
37 EFFECTIVE_END_DATE,
38 RANGE_CODE,
39 ASSIGNMENT_ACTION_CODE,
40 INITIALIZATION_CODE,
41 ARCHIVE_CODE,
42 MAGNETIC_CODE,
43 REPORT_CATEGORY,
44 REPORT_NAME,
45 SORT_CODE,
46 UPDATABLE_FLAG,
47 DEINITIALIZATION_CODE
48 ) values (
49 X_TEMPORARY_ACTION_FLAG,
50 X_REPORT_FORMAT_MAPPING_ID,
51 X_BUSINESS_GROUP_ID,
52 X_LEGISLATION_CODE,
53 X_REPORT_TYPE,
54 X_REPORT_QUALIFIER,
55 X_REPORT_FORMAT,
56 X_EFFECTIVE_START_DATE,
57 X_EFFECTIVE_END_DATE,
58 X_RANGE_CODE,
59 X_ASSIGNMENT_ACTION_CODE,
60 X_INITIALIZATION_CODE,
61 X_ARCHIVE_CODE,
62 X_MAGNETIC_CODE,
63 X_REPORT_CATEGORY,
64 X_REPORT_NAME,
65 X_SORT_CODE,
66 X_UPDATABLE_FLAG,
67 X_DEINITIALIZATION_CODE
68 );
69
70 insert into PAY_REPORT_FORMAT_MAPPINGS_TL (
71 REPORT_FORMAT_MAPPING_ID,
72 DISPLAY_NAME,
73 LANGUAGE,
74 SOURCE_LANG
75 ) select
76 X_REPORT_FORMAT_MAPPING_ID,
77 X_DISPLAY_NAME,
78 L.LANGUAGE_CODE,
79 userenv('LANG')
80 from FND_LANGUAGES L
81 where L.INSTALLED_FLAG in ('I', 'B')
82 and not exists
83 (select NULL from PAY_REPORT_FORMAT_MAPPINGS_TL T
84 where T.REPORT_FORMAT_MAPPING_ID = X_REPORT_FORMAT_MAPPING_ID
85 and T.LANGUAGE = L.LANGUAGE_CODE);
86
87 open c;
88 fetch c into X_ROWID;
89 if (c%notfound) then
90 close c;
91 raise no_data_found;
92 end if;
93 close c;
94
95 end INSERT_ROW;
96
97 procedure LOCK_ROW (
98 X_REPORT_FORMAT_MAPPING_ID in NUMBER,
99 X_TEMPORARY_ACTION_FLAG in VARCHAR2,
100 X_BUSINESS_GROUP_ID in NUMBER,
101 X_LEGISLATION_CODE in VARCHAR2,
102 X_REPORT_TYPE in VARCHAR2,
103 X_REPORT_QUALIFIER in VARCHAR2,
104 X_REPORT_FORMAT in VARCHAR2,
105 X_EFFECTIVE_START_DATE in DATE,
106 X_EFFECTIVE_END_DATE in DATE,
107 X_RANGE_CODE in VARCHAR2,
108 X_ASSIGNMENT_ACTION_CODE in VARCHAR2,
109 X_INITIALIZATION_CODE in VARCHAR2,
110 X_ARCHIVE_CODE in VARCHAR2,
111 X_MAGNETIC_CODE in VARCHAR2,
112 X_REPORT_CATEGORY in VARCHAR2,
113 X_REPORT_NAME in VARCHAR2,
114 X_SORT_CODE in VARCHAR2,
115 X_UPDATABLE_FLAG in VARCHAR2,
116 X_DEINITIALIZATION_CODE in VARCHAR2,
117 X_DISPLAY_NAME in VARCHAR2
118 ) is
119 cursor c is select
120 TEMPORARY_ACTION_FLAG,
121 BUSINESS_GROUP_ID,
122 LEGISLATION_CODE,
123 REPORT_TYPE,
124 REPORT_QUALIFIER,
125 REPORT_FORMAT,
126 EFFECTIVE_START_DATE,
127 EFFECTIVE_END_DATE,
128 RANGE_CODE,
129 ASSIGNMENT_ACTION_CODE,
130 INITIALIZATION_CODE,
131 ARCHIVE_CODE,
132 MAGNETIC_CODE,
133 REPORT_CATEGORY,
134 REPORT_NAME,
135 SORT_CODE,
136 UPDATABLE_FLAG,
137 DEINITIALIZATION_CODE
138 from PAY_REPORT_FORMAT_MAPPINGS_F
139 where REPORT_FORMAT_MAPPING_ID = X_REPORT_FORMAT_MAPPING_ID
140 for update of REPORT_FORMAT_MAPPING_ID nowait; recinfo c%rowtype;
141
142 cursor c1 is select DISPLAY_NAME, decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
143 from PAY_REPORT_FORMAT_MAPPINGS_TL
144 where REPORT_FORMAT_MAPPING_ID = X_REPORT_FORMAT_MAPPING_ID and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
145 for update of REPORT_FORMAT_MAPPING_ID nowait;
146 begin
147 open c;
148 fetch c into recinfo;
149 if (c%notfound) then close c;
150 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
151 app_exception.raise_exception;
152 end if;
153 close c;
154 if ( ((recinfo.TEMPORARY_ACTION_FLAG = X_TEMPORARY_ACTION_FLAG)
155 OR ((recinfo.TEMPORARY_ACTION_FLAG is null) AND (X_TEMPORARY_ACTION_FLAG is null)))
156 AND ((recinfo.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
157 OR ((recinfo.BUSINESS_GROUP_ID is null) AND (X_BUSINESS_GROUP_ID is null)))
158 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
159 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
160 AND (recinfo.REPORT_TYPE = X_REPORT_TYPE)
161 AND (recinfo.REPORT_QUALIFIER = X_REPORT_QUALIFIER)
162 AND (recinfo.REPORT_FORMAT = X_REPORT_FORMAT)
163 AND (recinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE)
164 AND (recinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE)
165 AND ((recinfo.RANGE_CODE = X_RANGE_CODE)
166 OR ((recinfo.RANGE_CODE is null) AND (X_RANGE_CODE is null)))
167 AND ((recinfo.ASSIGNMENT_ACTION_CODE = X_ASSIGNMENT_ACTION_CODE)
168 OR ((recinfo.ASSIGNMENT_ACTION_CODE is null) AND (X_ASSIGNMENT_ACTION_CODE is null)))
169 AND ((recinfo.INITIALIZATION_CODE = X_INITIALIZATION_CODE)
170 OR ((recinfo.INITIALIZATION_CODE is null) AND (X_INITIALIZATION_CODE is null)))
171 AND ((recinfo.ARCHIVE_CODE = X_ARCHIVE_CODE)
172 OR ((recinfo.ARCHIVE_CODE is null) AND (X_ARCHIVE_CODE is null)))
173 AND ((recinfo.MAGNETIC_CODE = X_MAGNETIC_CODE)
174 OR ((recinfo.MAGNETIC_CODE is null) AND (X_MAGNETIC_CODE is null)))
175 AND (recinfo.REPORT_CATEGORY = X_REPORT_CATEGORY)
176 AND ((recinfo.REPORT_NAME = X_REPORT_NAME)
177 OR ((recinfo.REPORT_NAME is null) AND (X_REPORT_NAME is null)))
178 AND ((recinfo.SORT_CODE = X_SORT_CODE)
179 OR ((recinfo.SORT_CODE is null) AND (X_SORT_CODE is null)))
180 AND ((recinfo.UPDATABLE_FLAG = X_UPDATABLE_FLAG)
181 OR ((recinfo.UPDATABLE_FLAG is null) AND (X_UPDATABLE_FLAG is null)))
182 AND ((recinfo.DEINITIALIZATION_CODE = X_DEINITIALIZATION_CODE)
183 OR ((recinfo.DEINITIALIZATION_CODE is null) AND (X_DEINITIALIZATION_CODE is null)))
184 ) then
185 null;
186 else fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
187 app_exception.raise_exception;
188 end if;
189
190 for tlinfo in c1 loop
191 if (tlinfo.BASELANG = 'Y') then
192 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME))
193 then
194 null;
195 else fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
196 app_exception.raise_exception;
197 end if;
198 end if;
199 end loop;
200 return;
201 end LOCK_ROW;
202
203 procedure UPDATE_ROW (
204 X_REPORT_FORMAT_MAPPING_ID in NUMBER,
205 X_TEMPORARY_ACTION_FLAG in VARCHAR2,
206 X_BUSINESS_GROUP_ID in NUMBER,
207 X_LEGISLATION_CODE in VARCHAR2,
208 X_REPORT_TYPE in VARCHAR2,
209 X_REPORT_QUALIFIER in VARCHAR2,
210 X_REPORT_FORMAT in VARCHAR2,
211 X_EFFECTIVE_START_DATE in DATE,
212 X_EFFECTIVE_END_DATE in DATE,
213 X_RANGE_CODE in VARCHAR2,
214 X_ASSIGNMENT_ACTION_CODE in VARCHAR2,
215 X_INITIALIZATION_CODE in VARCHAR2,
216 X_ARCHIVE_CODE in VARCHAR2,
217 X_MAGNETIC_CODE in VARCHAR2,
218 X_REPORT_CATEGORY in VARCHAR2,
219 X_REPORT_NAME in VARCHAR2,
220 X_SORT_CODE in VARCHAR2,
221 X_UPDATABLE_FLAG in VARCHAR2,
222 X_DEINITIALIZATION_CODE in VARCHAR2,
223 X_DISPLAY_NAME in VARCHAR2
224 ) is
225 begin
226 update PAY_REPORT_FORMAT_MAPPINGS_F set
227 TEMPORARY_ACTION_FLAG = X_TEMPORARY_ACTION_FLAG,
228 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
229 LEGISLATION_CODE = X_LEGISLATION_CODE,
230 REPORT_TYPE = X_REPORT_TYPE,
231 REPORT_QUALIFIER = X_REPORT_QUALIFIER,
232 REPORT_FORMAT = X_REPORT_FORMAT,
233 EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE,
234 EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
235 RANGE_CODE = X_RANGE_CODE,
236 ASSIGNMENT_ACTION_CODE = X_ASSIGNMENT_ACTION_CODE,
237 INITIALIZATION_CODE = X_INITIALIZATION_CODE,
238 ARCHIVE_CODE = X_ARCHIVE_CODE,
239 MAGNETIC_CODE = X_MAGNETIC_CODE,
240 REPORT_CATEGORY = X_REPORT_CATEGORY,
241 REPORT_NAME = X_REPORT_NAME,
242 SORT_CODE = X_SORT_CODE,
243 UPDATABLE_FLAG = X_UPDATABLE_FLAG,
244 DEINITIALIZATION_CODE = X_DEINITIALIZATION_CODE
245 where REPORT_FORMAT_MAPPING_ID = X_REPORT_FORMAT_MAPPING_ID;
246
247 if (sql%notfound) then
248 raise no_data_found;
249 end if;
250
251 update PAY_REPORT_FORMAT_MAPPINGS_TL set
252 DISPLAY_NAME = X_DISPLAY_NAME,
253 SOURCE_LANG = userenv('LANG')
254 where REPORT_FORMAT_MAPPING_ID = X_REPORT_FORMAT_MAPPING_ID
255 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
256
257 if (sql%notfound) then
258 raise no_data_found;
259 end if;
260 end UPDATE_ROW;
261
262 procedure DELETE_ROW (
263 X_REPORT_FORMAT_MAPPING_ID in NUMBER
264 ) is
265 begin
266 delete from PAY_REPORT_FORMAT_MAPPINGS_TL
267 where REPORT_FORMAT_MAPPING_ID = X_REPORT_FORMAT_MAPPING_ID;
268
269 if (sql%notfound) then
270 raise no_data_found;
271 end if;
272
273 delete from PAY_REPORT_FORMAT_MAPPINGS_F
274 where REPORT_FORMAT_MAPPING_ID = X_REPORT_FORMAT_MAPPING_ID;
275
276 if (sql%notfound) then
277 raise no_data_found;
278 end if;
279 end DELETE_ROW;
280
281 procedure ADD_LANGUAGE
282 is
283 begin
284 delete from PAY_REPORT_FORMAT_MAPPINGS_TL T
285 where not exists
286 (select NULL from PAY_REPORT_FORMAT_MAPPINGS_F B
287 where B.REPORT_FORMAT_MAPPING_ID = T.REPORT_FORMAT_MAPPING_ID );
288
289 update PAY_REPORT_FORMAT_MAPPINGS_TL T set (
290 DISPLAY_NAME
291 ) = (select B.DISPLAY_NAME from PAY_REPORT_FORMAT_MAPPINGS_TL B
292 where B.REPORT_FORMAT_MAPPING_ID = T.REPORT_FORMAT_MAPPING_ID
293 and B.LANGUAGE = T.SOURCE_LANG)
294 where (
295 T.REPORT_FORMAT_MAPPING_ID,
296 T.LANGUAGE
297 ) in (select
298 SUBT.REPORT_FORMAT_MAPPING_ID, SUBT.LANGUAGE
299 from PAY_REPORT_FORMAT_MAPPINGS_TL SUBB, PAY_REPORT_FORMAT_MAPPINGS_TL SUBT
300 where SUBB.REPORT_FORMAT_MAPPING_ID = SUBT.REPORT_FORMAT_MAPPING_ID
301 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
302 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME ));
303
304 insert into PAY_REPORT_FORMAT_MAPPINGS_TL (
305 REPORT_FORMAT_MAPPING_ID,
306 DISPLAY_NAME,
307 LANGUAGE,
308 SOURCE_LANG
309 ) select /*+ ORDERED */
310 B.REPORT_FORMAT_MAPPING_ID,
311 B.DISPLAY_NAME,
312 L.LANGUAGE_CODE, B.SOURCE_LANG from PAY_REPORT_FORMAT_MAPPINGS_TL B, FND_LANGUAGES L
313 where L.INSTALLED_FLAG in ('I', 'B')
314 and B.LANGUAGE = userenv('LANG')
315 and not exists
316 (select NULL from PAY_REPORT_FORMAT_MAPPINGS_TL T
317 where T.REPORT_FORMAT_MAPPING_ID = B.REPORT_FORMAT_MAPPING_ID
318 and T.LANGUAGE = L.LANGUAGE_CODE);
319 end ADD_LANGUAGE;
320
321 end PAY_REPORT_FORMAT_MAPPINGS_PKG;
[Home] [Help]