[Home] [Help]
PACKAGE BODY: APPS.PA_RBS_VERSIONS_PKG
Source
1 PACKAGE BODY PA_RBS_VERSIONS_PKG AS
2 --$Header: PARBSVTB.pls 120.2 2005/09/28 18:10:11 ramurthy noship $
3
4
5
6
7 PROCEDURE Insert_Row(
8 P_Version_Number IN Number,
9 P_Rbs_Header_Id IN Number,
10 P_Record_Version_Number IN Number,
11 P_Name IN Varchar2,
12 P_Description IN Varchar2,
13 P_Version_Start_Date IN Date,
14 P_Version_End_Date IN Date,
15 P_Job_Group_Id IN Number,
16 P_Rule_Based_Flag IN Varchar2,
17 P_Validated_Flag IN Varchar2,
18 P_Status_Code IN Varchar2,
19 P_Creation_Date IN Date,
20 P_Created_By IN Number,
21 P_Last_Update_Date IN Date,
22 P_Last_Updated_By IN Number,
23 P_Last_Update_Login IN Number,
24 X_Record_Version_Number OUT NOCOPY Number,
25 X_Rbs_Version_Id OUT NOCOPY Number,
26 X_Error_Msg_Data OUT NOCOPY Varchar2 )
27
28 Is
29
30 UNABLE_TO_CREATE_REC Exception;
31 l_RowId RowId := Null;
32
33 Cursor Return_RowId(P_Id IN Number) is
34 Select
35 RowId
36 From
37 Pa_RBS_Versions_B
38 Where
39 RBS_Version_Id = P_Id;
40
41 Cursor GetNextId is
42 Select
43 Pa_Rbs_Versions_S.NextVal
44 From
45 Dual;
46
47
48 Begin
49
50 If P_Record_Version_Number Is Not Null Then
51
52 X_Record_Version_Number := P_Record_Version_Number;
53
54 Else
55
56 X_Record_Version_Number := 1;
57
58 End If;
59
60 Open GetNextId;
61 Fetch GetNextId Into X_Rbs_Version_Id;
62 Close GetNextId;
63
64 Insert Into Pa_Rbs_Versions_B (
65 Rbs_Version_Id,
66 Version_Number,
67 Rbs_Header_Id,
68 Version_Start_Date,
69 Version_End_Date,
70 Job_Group_Id,
71 Rule_Based_Flag,
72 Validated_Flag,
73 Status_Code,
74 Last_Update_Date,
75 Last_Updated_By,
76 Creation_Date,
77 Created_By,
78 Last_Update_Login,
79 Record_Version_Number )
80 Values (
81 X_Rbs_Version_Id,
82 P_Version_Number,
83 P_Rbs_Header_Id,
84 P_Version_Start_Date,
85 P_Version_End_Date,
86 P_Job_Group_Id,
87 P_Rule_Based_Flag,
88 P_Validated_Flag,
89 P_Status_Code,
90 P_Last_Update_Date,
91 P_Last_Updated_By,
92 P_Creation_Date,
93 P_Created_By,
94 P_Last_Update_Login,
95 X_Record_Version_Number );
96
97 Insert Into Pa_Rbs_Versions_TL (
98 Rbs_Version_Id,
99 Name,
100 Description,
101 Creation_Date,
102 Created_By,
103 Last_Update_Date,
104 Last_Updated_By,
105 Last_Update_Login,
106 Language,
107 Source_Lang )
108 select
109 X_Rbs_Version_Id,
110 P_Name,
111 P_Description,
112 P_Creation_Date,
113 P_Created_By,
114 P_Last_Update_Date,
115 P_Last_Updated_By,
116 P_Last_Update_Login,
117 L.Language_Code,
118 UserEnv('LANG')
119 From
120 Fnd_Languages L
121 Where
122 L.Installed_Flag in ('I', 'B')
123 And Not Exists
124 (select
125 Null
126 From
127 Pa_Rbs_Versions_TL T
128 Where
129 T.Rbs_Version_Id = X_Rbs_Version_Id
130 and T.Language = L.Language_Code);
131
132 Open Return_RowId (P_Id => X_RBS_Version_Id);
133 Fetch Return_RowId Into l_RowId;
134
135 If Return_RowId%NotFound Then
136
137 Close Return_RowId;
138 X_Record_Version_Number := Null;
139 X_RBS_Version_Id := Null;
140 Raise UNABLE_TO_CREATE_REC;
141
142 End If;
143 Close Return_RowId;
144
145 Exception
146 When UNABLE_TO_CREATE_REC Then
147 -- System Error for sys admin information needed
148 X_Error_Msg_Data := 'PA_UNABLE_TO_CREATE_REC';
149
150 -- 4537865 RESET OUT PARAMS
151 X_Record_Version_Number := NULL ;
152 X_Rbs_Version_Id := NULL ;
153
154 When others Then
155
156 -- 4537865 RESET OUT PARAMS
157 X_Record_Version_Number := NULL ;
158 X_Rbs_Version_Id := NULL ;
159 X_Error_Msg_Data := SQLERRM ;
160
161 Raise;
162
163 End Insert_Row;
164
165 Procedure Update_Row(
166 P_RBS_Version_Id IN Number,
167 P_Name IN Varchar2,
168 P_Description IN Varchar2,
169 P_Version_Start_Date IN Date,
170 P_Job_Group_Id IN Number,
171 P_Record_Version_Number IN Number,
172 P_Last_Update_Date IN Date,
173 P_Last_Updated_By IN Number,
174 P_Last_Update_Login IN Number,
175 X_Record_Version_Number OUT NOCOPY Number, -- 4537865
176 X_Error_Msg_Data OUT NOCOPY Varchar2 )
177
178 Is
179
180 REC_VER_NUM_MISMATCH Exception;
181
182 Begin
183
184 Update Pa_RBS_Versions_B
185 Set Version_Start_Date = P_Version_Start_Date,
186 Job_Group_Id = P_Job_Group_Id,
187 Record_Version_Number = Record_Version_Number + 1,
188 Last_Update_Date = P_Last_Update_Date,
189 Last_Updated_By = P_Last_Updated_By,
190 Last_Update_Login = P_Last_Update_Login
191 Where
192 Rbs_Version_Id = P_RBS_Version_Id
193 And Record_Version_Number = P_Record_Version_Number;
194
195 If Sql%NotFound Then
196 Raise REC_VER_NUM_MISMATCH;
197 End If;
198
199 Update Pa_Rbs_Versions_TL
200 Set Name = P_Name,
201 Description = P_Description,
202 Last_Update_Date = P_Last_Update_Date,
203 Last_Updated_By = P_Last_Updated_By,
204 Last_Update_Login = P_Last_Update_Login,
205 Source_Lang = UserEnv('LANG')
206 Where
207 Rbs_Version_Id = P_Rbs_Version_Id
208 And UserEnv('LANG') in (Language, Source_Lang);
209
210 If Sql%NotFound Then
211 Raise REC_VER_NUM_MISMATCH;
212 End If;
213
214 X_Record_Version_Number := P_Record_Version_Number + 1;
215
216 Exception
217 When REC_VER_NUM_MISMATCH Then
218 X_Error_Msg_Data := 'PA_RECORD_ALREADY_UPDATED';
219
220 -- 4537865 RESET OUT PARAMS
221 X_Record_Version_Number := NULL ;
222
223 When Others Then
224 -- 4537865 RESET OUT PARAMS
225 X_Record_Version_Number := NULL ;
226 X_Error_Msg_Data := SQLERRM ;
227
228 Raise;
229
230 End Update_Row;
231
232
233 Procedure Delete_Row (
234 P_RBS_Version_Id IN Number,
235 P_Record_Version_Number IN Number,
236 X_Error_Msg_Data OUT NOCOPY Varchar2)
237
238 Is
239
240 REC_VER_NUM_MISMATCH Exception;
241
242 Begin
243
244 Delete
245 From
246 Pa_Rbs_Versions_B
247 Where
248 RBS_Version_Id = P_RBS_Version_Id
249 And Record_Version_Number = P_Record_Version_Number;
250
251 If Sql%NotFound Then
252 Raise REC_VER_NUM_MISMATCH;
253 End If;
254
255
256 Delete
257 From
258 Pa_Rbs_Versions_TL
259 Where
260 Rbs_Version_Id = P_Rbs_Version_Id;
261
262 Exception
263 When REC_VER_NUM_MISMATCH Then
264 X_Error_Msg_Data := 'PA_RECORD_ALREADY_UPDATED';
265 When Others Then
266 -- 4537865 RESET OUT PARAMS
267 X_Error_Msg_Data := SQLERRM ;
268 Raise;
269
270 End Delete_Row;
271
272 procedure ADD_LANGUAGE
273 is
274 begin
275 delete from pa_rbs_versions_tl T
276 where not exists
277 (select NULL
278 from pa_rbs_versions_b B
279 where B.RBS_VERSION_ID = T.RBS_VERSION_ID
280 );
281
282 update pa_rbs_versions_tl T set (
283 NAME,
284 DESCRIPTION
285 ) = (select
286 B.NAME,
287 B.DESCRIPTION
288 from pa_rbs_versions_tl b
289 where B.RBS_VERSION_ID = T.RBS_VERSION_ID
290 and B.LANGUAGE = T.SOURCE_LANG)
291 where (
292 T.RBS_VERSION_ID,
293 T.LANGUAGE
294 ) in (select
295 SUBT.RBS_VERSION_ID,
296 SUBT.LANGUAGE
297 from pa_rbs_versions_tl SUBB, pa_rbs_versions_tl SUBT
298 where SUBB.RBS_VERSION_ID = SUBT.RBS_VERSION_ID
299 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
300 and (SUBB.NAME <> SUBT.NAME
301 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
302 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
303 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
304 ));
305
306 insert into pa_rbs_versions_tl (
307 LAST_UPDATE_LOGIN,
308 CREATION_DATE,
309 CREATED_BY,
310 LAST_UPDATE_DATE,
311 LAST_UPDATED_BY,
312 RBS_VERSION_ID,
313 NAME,
314 DESCRIPTION,
315 LANGUAGE,
316 SOURCE_LANG
317 ) select
318 B.LAST_UPDATE_LOGIN,
319 B.CREATION_DATE,
320 B.CREATED_BY,
321 B.LAST_UPDATE_DATE,
322 B.LAST_UPDATED_BY,
323 B.RBS_VERSION_ID,
324 B.NAME,
325 B.DESCRIPTION,
326 L.LANGUAGE_CODE,
327 B.SOURCE_LANG
328 from pa_rbs_versions_tl B, FND_LANGUAGES L
329 where L.INSTALLED_FLAG in ('I', 'B')
330 and B.LANGUAGE = userenv('LANG')
331 and not exists
332 (select NULL
333 from pa_rbs_versions_tl T
334 where T.RBS_VERSION_ID = B.RBS_VERSION_ID
335 and T.LANGUAGE = L.LANGUAGE_CODE);
336 end ADD_LANGUAGE;
337
338 END PA_RBS_VERSIONS_PKG;