1 Package Body Pa_Rbs_Headers_Pkg AS
2 --$Header: PARBSHTB.pls 120.1.12020000.2 2013/03/27 18:11:14 bpottipa ship $
3
4 /*===============================================================================
5 This api creates RBS Header. This is a table handler for PA_RBS_HEADERS table
6 ============================================================================*/
7
8 -- Procedure : INSERT_ROW
9 -- Type : Table Handler
10 -- Purpose : This API will create new RBS headers.
11 -- : This API will be called from following package:
12 -- : 1.PA_RBS_HEADER_PVT package,Insert_Header procedure
13
14 -- Note : This API will make insert into PA_RBS_HEADERS_B and PA_RBS_HEADERS_TL table
15
16 -- Assumptions :
17
18 -- Parameters Type Required Description and Purpose
19 -- --------------------------- ------ -------- --------------------------------------------------------
20 -- P_RbsHeaderId NUMBER Yes The value will contain the Rbs Header id which is the unique identifier.
21 -- P_Name VARCHAR2 Yes The value contain the name of the Rbs header
22 -- P_Description VARCHAR2 NO The description of the Rbs header
23 -- P_EffectiveFrom DATE YES The start date of the RBS
24 -- P_EffectiveTo DATE NO The end date of the Rbs.
25 -- P_BusinessGroupId NUMBER YES The Business Group Id.
26
27
28
29 PROCEDURE Insert_Row(
30 P_RbsHeaderId IN Number,
31 P_Name IN Varchar2,
32 P_Description IN Varchar2,
33 P_EffectiveFrom IN Date,
34 P_EffectiveTo IN Date,
35 P_Use_For_Alloc_Flag IN Varchar2,
36 P_Cbs_Enabled IN Varchar2 Default NULL, --bug#15834912
37 P_ACTIVE_FLAG IN Varchar2 Default NULL, --bug#15834912
38 P_BusinessGroupId IN Number)
39 IS
40 BEGIN
41
42 Insert Into Pa_Rbs_Headers_B(
43 Rbs_Header_Id,
44 Effective_From_Date,
45 Effective_To_Date,
46 Business_Group_Id,
47 Use_For_Alloc_Flag,
48 cbs_enabled, --bug#15834912
49 active_flag, --bug#15834912
50 Creation_Date,
51 Created_By,
52 Last_Update_Date,
53 Last_Updated_By,
54 Last_Update_Login,
55 Record_Version_Number )
56 Values(
57 P_RbsHeaderId,
58 P_EffectiveFrom,
59 P_EffectiveTo,
60 P_BusinessGroupId,
61 P_Use_For_Alloc_Flag,
62 P_Cbs_Enabled, --bug#15834912
63 P_Active_Flag, --bug#15834912
64 SysDate,
65 Fnd_Global.User_Id,
66 SysDate,
67 Fnd_Global.User_Id,
68 Fnd_Global.Login_Id,
69 1);
70
71 --MLS changes incorporated.
72 Insert Into Pa_Rbs_Headers_TL(
73 Rbs_Header_Id,
74 Name,
75 Description,
76 Language,
77 Last_Update_Date,
78 Last_Updated_By,
79 Creation_Date,
80 Created_By,
81 Last_Update_Login,
82 Source_Lang )
83 Select
84 P_RbsHeaderId,
85 P_Name,
86 P_Description,
87 --UserEnv('LANG'),
88 L.Language_Code,
89 SysDate,
90 Fnd_Global.User_Id,
91 SysDate,
92 Fnd_Global.User_Id,
93 Fnd_Global.Login_Id,
94 --L.Language_Code
95 UserEnv('LANG')
96 From
97 Fnd_Languages L
98 Where L.Installed_Flag in ('I', 'B')
99 And Not Exists
100 (Select
101 Null
102 From
103 Pa_Rbs_Headers_TL T
104 WHere
105 T.Rbs_Header_Id = P_RbsHeaderId
106 And T.Language = L.Language_Code);
107
108 END Insert_Row;
109
110
111 /*==========================================================================
112 This api updates RBS Header. This is a Table Handler.
113 ============================================================================*/
114
115 -- Procedure : UPDATE_ROW
116 -- Type : Table Handler
117 -- Purpose : This API will be used to update RBS headers.
118 -- : This API will be called from following package:
119 -- : 1.PA_RBS_HEADER_PVT package,Update_Header procedure
120
121 -- Note : This API will updates PA_RBS_HEADER_B and PA_RBS_HEADERS_TL table
122
123 -- Assumptions :
124
125 -- Parameters Type Required Description and Purpose
126 -- --------------------------- ------ -------- --------------------------------------------------------
127 -- P_RbsHeaderId NUMBER Yes The value will contain the Rbs Header id which is the unique identifier.
128 -- P_Name VARCHAR2 Yes The value contain the name of the Rbs header
129 -- P_Description VARCHAR2 NO The description of the Rbs header
130 -- P_EffectiveFrom DATE Yes The start date of the RBS
131 -- P_EffectiveTo DATE NO The end date of the Rbs.
132
133 Procedure Update_Row(
134 P_RbsHeaderId IN Number,
135 P_Name IN Varchar2,
136 P_Description IN Varchar2,
137 P_EffectiveFrom IN Date,
138 P_Use_For_Alloc_Flag IN Varchar2,
139 P_EffectiveTo IN Date,
140 P_ACTIVE_FLAG IN Varchar2 Default NULL ) --bug#15834912
141
142 Is
143
144 Begin
145
146 Update Pa_Rbs_Headers_B
147 Set Effective_From_Date = Nvl(P_EffectiveFrom,Effective_From_Date),
148 Effective_To_Date = P_EffectiveTo,
149 Use_For_Alloc_Flag = P_Use_For_Alloc_Flag,
150 Last_Update_Date = SysDate,
151 Last_Updated_By = Fnd_Global.User_Id,
152 Last_Update_Login = Fnd_Global.Login_Id,
153 Record_Version_Number = Record_Version_Number + 1,
154 ACTIVE_FLAG = P_ACTIVE_FLAG --15834912
155 Where
156 Rbs_Header_Id = P_RbsHeaderId;
157
158 --MLS changes incorporated.
159 Update Pa_Rbs_Headers_TL
160 Set
161 Name = P_Name,
162 Description = P_Description,
163 Last_update_date = SysDate,
164 Last_updated_by = Fnd_Global.User_Id,
165 Last_update_login = Fnd_Global.Login_Id,
166 Source_Lang = UserEnv('LANG')
167 Where
168 Rbs_Header_Id = P_RbsHeaderId
169 And UserEnv('LANG') in (Language, Source_Lang);
170
171 Exception
172 When Others Then
173 Raise;
174
175 End Update_Row;
176
177 procedure ADD_LANGUAGE
178 is
179 begin
180 delete from Pa_Rbs_Headers_TL T
181 where not exists
182 (select NULL
183 from Pa_Rbs_Headers_b B
184 where B.Rbs_Header_Id = T.Rbs_Header_Id
185 );
186
187 update Pa_Rbs_Headers_TL T set (
188 NAME,
189 DESCRIPTION
190 ) = (select
191 B.NAME,
192 B.DESCRIPTION
193 from Pa_Rbs_Headers_TL b
194 where B.Rbs_Header_Id = T.Rbs_Header_Id
195 and B.LANGUAGE = T.SOURCE_LANG)
196 where (
197 T.Rbs_Header_Id,
198 T.LANGUAGE
199 ) in (select
200 SUBT.Rbs_Header_Id,
201 SUBT.LANGUAGE
202 from Pa_Rbs_Headers_TL SUBB, Pa_Rbs_Headers_TL SUBT
203 where SUBB.Rbs_Header_Id = SUBT.Rbs_Header_Id
204 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
205 and (SUBB.NAME <> SUBT.NAME
206 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
207 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
208 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
209 ));
210
211 insert into Pa_Rbs_Headers_TL (
212 LAST_UPDATE_LOGIN,
213 CREATION_DATE,
214 CREATED_BY,
215 LAST_UPDATE_DATE,
216 LAST_UPDATED_BY,
217 Rbs_Header_Id,
218 NAME,
219 DESCRIPTION,
220 LANGUAGE,
221 SOURCE_LANG
222 ) select
223 B.LAST_UPDATE_LOGIN,
224 B.CREATION_DATE,
225 B.CREATED_BY,
226 B.LAST_UPDATE_DATE,
227 B.LAST_UPDATED_BY,
228 B.Rbs_Header_Id,
229 B.NAME,
230 B.DESCRIPTION,
231 L.LANGUAGE_CODE,
232 B.SOURCE_LANG
233 from Pa_Rbs_Headers_TL B, FND_LANGUAGES L
234 where L.INSTALLED_FLAG in ('I', 'B')
235 and B.LANGUAGE = userenv('LANG')
236 and not exists
237 (select NULL
238 from Pa_Rbs_Headers_TL T
239 where T.Rbs_Header_Id = B.Rbs_Header_Id
240 and T.LANGUAGE = L.LANGUAGE_CODE);
241 end ADD_LANGUAGE;
242
243 END Pa_Rbs_Headers_Pkg;
[Home] [Help]