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