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