[Home] [Help]
PACKAGE BODY: APPS.CS_PARTY_ROLES_PKG
Source
1 package body CS_PARTY_ROLES_PKG as
2 /* $Header: csxptyrb.pls 120.0 2005/08/18 19:25 aneemuch noship $ */
3 procedure INSERT_ROW (
4 PX_PARTY_ROLE_CODE in out NOCOPY VARCHAR2,
5 P_START_DATE_ACTIVE in DATE,
6 P_END_DATE_ACTIVE in DATE,
7 P_SEEDED_FLAG in VARCHAR2,
8 P_SORT_ORDER in NUMBER,
9 P_NAME in VARCHAR2,
10 P_DESCRIPTION in VARCHAR2,
11 P_CREATION_DATE in DATE,
12 P_CREATED_BY in NUMBER,
13 P_LAST_UPDATE_DATE in DATE,
14 P_LAST_UPDATED_BY in NUMBER,
15 P_LAST_UPDATE_LOGIN in NUMBER,
16 X_OBJECT_VERSION_NUMBER out NOCOPY NUMBER
17 ) is
18 l_object_version_number NUMBER := 1;
19 begin
20 insert into CS_PARTY_ROLES_B (
21 PARTY_ROLE_CODE,
22 START_DATE_ACTIVE,
23 END_DATE_ACTIVE,
24 SEEDED_FLAG,
25 SORT_ORDER,
26 OBJECT_VERSION_NUMBER,
27 CREATION_DATE,
28 CREATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_LOGIN
32 ) values (
33 PX_PARTY_ROLE_CODE,
34 P_START_DATE_ACTIVE,
35 P_END_DATE_ACTIVE,
36 P_SEEDED_FLAG,
37 P_SORT_ORDER,
38 l_object_version_number,
39 Decode(P_CREATION_DATE,NULL,SYSDATE,P_CREATION_DATE),
40 P_CREATED_BY,
41 Decode(P_LAST_UPDATE_DATE,NULL,SYSDATE,P_LAST_UPDATE_DATE),
42 P_LAST_UPDATED_BY,
43 P_LAST_UPDATE_LOGIN
44 );
45
46 insert into CS_PARTY_ROLES_TL (
47 CREATION_DATE,
48 CREATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_LOGIN,
52 PARTY_ROLE_CODE,
53 NAME,
54 DESCRIPTION,
55 LANGUAGE,
56 SOURCE_LANG
57 ) select
58 P_CREATION_DATE,
59 P_CREATED_BY,
60 P_LAST_UPDATE_DATE,
61 P_LAST_UPDATED_BY,
62 P_LAST_UPDATE_LOGIN,
63 PX_PARTY_ROLE_CODE,
64 P_NAME,
65 P_DESCRIPTION,
66 L.LANGUAGE_CODE,
67 userenv('LANG')
68 from FND_LANGUAGES L
69 where L.INSTALLED_FLAG in ('I', 'B')
70 and not exists
71 (select NULL
72 from CS_PARTY_ROLES_TL T
73 where T.PARTY_ROLE_CODE = PX_PARTY_ROLE_CODE
74 and T.LANGUAGE = L.LANGUAGE_CODE);
75
76 x_object_version_number := l_object_version_number;
77 end INSERT_ROW;
78
79 procedure UPDATE_ROW (
80 P_PARTY_ROLE_CODE in VARCHAR2,
81 P_START_DATE_ACTIVE in DATE,
82 P_END_DATE_ACTIVE in DATE,
83 P_SEEDED_FLAG in VARCHAR2,
84 P_SORT_ORDER in NUMBER ,
85 P_NAME in VARCHAR2,
86 P_DESCRIPTION in VARCHAR2,
87 P_LAST_UPDATE_DATE in DATE,
88 P_LAST_UPDATED_BY in NUMBER,
89 P_LAST_UPDATE_LOGIN in NUMBER,
90 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER
91 ) is
92 l_object_Version_number number;
93 begin
94 update CS_PARTY_ROLES_B set
95 START_DATE_ACTIVE = P_START_DATE_ACTIVE,
96 END_DATE_ACTIVE = P_END_DATE_ACTIVE,
97 SEEDED_FLAG = P_SEEDED_FLAG,
98 SORT_ORDER = P_SORT_ORDER,
99 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
100 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
101 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
102 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
103 where PARTY_ROLE_CODE = P_PARTY_ROLE_CODE
104 RETURNING OBJECT_VERSION_NUMBER INTO L_OBJECT_VERSION_NUMBER;
105
106 X_OBJECT_VERSION_NUMBER := l_object_version_number;
107
108 if (sql%notfound) then
109 raise no_data_found;
110 end if;
111
112 update CS_PARTY_ROLES_TL set
113 NAME = P_NAME,
114 DESCRIPTION = P_DESCRIPTION,
115 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
116 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
117 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
118 SOURCE_LANG = userenv('LANG')
119 where PARTY_ROLE_CODE = P_PARTY_ROLE_CODE
120 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
121
122 if (sql%notfound) then
123 raise no_data_found;
124 end if;
125 end UPDATE_ROW;
126
127 procedure DELETE_ROW (
128 P_PARTY_ROLE_CODE in VARCHAR2
129 ) is
130 begin
131 delete from CS_PARTY_ROLES_TL
132 where PARTY_ROLE_CODE = P_PARTY_ROLE_CODE;
133
134 if (sql%notfound) then
135 raise no_data_found;
136 end if;
137
138 delete from CS_PARTY_ROLES_B
139 where PARTY_ROLE_CODE = P_PARTY_ROLE_CODE;
140
141 if (sql%notfound) then
142 raise no_data_found;
143 end if;
144 end DELETE_ROW;
145
146 procedure ADD_LANGUAGE
147 is
148 begin
149 delete from CS_PARTY_ROLES_TL T
150 where not exists
151 (select NULL
152 from CS_PARTY_ROLES_B B
153 where B.PARTY_ROLE_CODE = T.PARTY_ROLE_CODE
154 );
155
156 update CS_PARTY_ROLES_TL T set (
157 NAME,
158 DESCRIPTION
159 ) = (select
160 B.NAME,
161 B.DESCRIPTION
162 from CS_PARTY_ROLES_TL B
163 where B.PARTY_ROLE_CODE = T.PARTY_ROLE_CODE
164 and B.LANGUAGE = T.SOURCE_LANG)
165 where (
166 T.PARTY_ROLE_CODE,
167 T.LANGUAGE
168 ) in (select
169 SUBT.PARTY_ROLE_CODE,
170 SUBT.LANGUAGE
171 from CS_PARTY_ROLES_TL SUBB, CS_PARTY_ROLES_TL SUBT
172 where SUBB.PARTY_ROLE_CODE = SUBT.PARTY_ROLE_CODE
173 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
174 and (SUBB.NAME <> SUBT.NAME
175 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
176 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
177 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
178 ));
179
180 insert into CS_PARTY_ROLES_TL (
181 CREATION_DATE,
182 CREATED_BY,
183 LAST_UPDATE_DATE,
184 LAST_UPDATED_BY,
185 LAST_UPDATE_LOGIN,
186 PARTY_ROLE_CODE,
187 NAME,
188 DESCRIPTION,
189 LANGUAGE,
190 SOURCE_LANG
191 ) select
192 B.CREATION_DATE,
193 B.CREATED_BY,
194 B.LAST_UPDATE_DATE,
195 B.LAST_UPDATED_BY,
196 B.LAST_UPDATE_LOGIN,
197 B.PARTY_ROLE_CODE,
198 B.NAME,
199 B.DESCRIPTION,
200 L.LANGUAGE_CODE,
201 B.SOURCE_LANG
202 from CS_PARTY_ROLES_TL B, FND_LANGUAGES L
203 where L.INSTALLED_FLAG in ('I', 'B')
204 and B.LANGUAGE = userenv('LANG')
205 and not exists
206 (select NULL
207 from CS_PARTY_ROLES_TL T
208 where T.PARTY_ROLE_CODE = B.PARTY_ROLE_CODE
209 and T.LANGUAGE = L.LANGUAGE_CODE);
210 end ADD_LANGUAGE;
211
212 PROCEDURE LOAD_ROW (
213 P_PARTY_ROLE_CODE IN VARCHAR2,
214 P_START_DATE_ACTIVE IN VARCHAR2,
215 P_END_DATE_ACTIVE IN VARCHAR2,
216 P_SEEDED_FLAG IN VARCHAR2,
217 P_SORT_ORDER IN VARCHAR2,
218 P_NAME IN VARCHAR2,
219 P_DESCRIPTION IN VARCHAR2,
220 P_OWNER IN VARCHAR2,
221 P_CREATION_DATE IN VARCHAR2,
222 P_CREATED_BY IN NUMBER,
223 P_LAST_UPDATE_DATE IN VARCHAR2,
224 P_LAST_UPDATED_BY IN NUMBER,
225 P_LAST_UPDATE_LOGIN IN NUMBER,
226 P_OBJECT_VERSION_NUMBER IN NUMBER )
227
228 IS
229
230 -- Out local variables for the update / insert row procedures.
231 lx_object_version_number NUMBER := 0;
232 l_user_id NUMBER := 0;
233
234 -- needed to be passed as the parameter value for the insert's in/out
235 -- parameter.
236 l_PARTY_ROLE_CODE VARCHAR2(30);
237
238 BEGIN
239
240 if ( p_owner = 'SEED' ) then
241 l_user_id := 1;
242 end if;
243
244 l_PARTY_ROLE_CODE := p_PARTY_ROLE_CODE;
245
246 UPDATE_ROW (
247 P_PARTY_ROLE_CODE =>l_PARTY_ROLE_CODE,
248 P_START_DATE_ACTIVE =>to_date(p_start_date_active,'DD-MM-YYYY'),
249 P_END_DATE_ACTIVE =>to_date(p_end_date_active,'DD-MM-YYYY'),
250 P_SEEDED_FLAG =>p_seeded_flag,
251 P_SORT_ORDER =>p_sort_order ,
252 P_NAME =>p_name,
253 P_DESCRIPTION =>p_description,
254 P_LAST_UPDATE_DATE =>nvl(to_date(p_last_update_date,
255 'DD-MM-YYYY'),sysdate),
256 P_LAST_UPDATED_BY =>l_user_id,
257 P_LAST_UPDATE_LOGIN =>0,
258 X_OBJECT_VERSION_NUMBER =>lx_object_version_number
259 );
260
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 INSERT_ROW (
264 PX_PARTY_ROLE_CODE =>l_PARTY_ROLE_CODE,
265 P_START_DATE_ACTIVE =>to_date(p_start_date_active,'DD-MM-YYYY'),
266 P_END_DATE_ACTIVE =>to_date(p_end_date_active,'DD-MM-YYYY'),
267 P_SEEDED_FLAG =>p_seeded_flag,
268 P_SORT_ORDER =>p_sort_order ,
269 P_NAME =>p_name,
270 P_DESCRIPTION =>p_description,
271 P_CREATION_DATE =>nvl(to_date( p_creation_date,
272 'DD-MM-YYYY'),sysdate),
273 P_CREATED_BY =>l_user_id,
274 P_LAST_UPDATE_DATE =>nvl(to_date( p_last_update_date,
275 'DD-MM-YYYY'),sysdate),
276 P_LAST_UPDATED_BY =>l_user_id,
277 P_LAST_UPDATE_LOGIN =>0,
278 X_OBJECT_VERSION_NUMBER =>lx_object_version_number
279 );
280
281 END LOAD_ROW;
282
283 procedure TRANSLATE_ROW ( X_PARTY_ROLE_CODE in varchar2,
284 X_NAME in varchar2,
285 X_DESCRIPTION in varchar2,
286 X_LAST_UPDATE_DATE in date,
287 X_LAST_UPDATE_LOGIN in number,
288 X_OWNER in varchar2)
289 is
290
291 l_user_id number;
292
293 begin
294
295 if X_OWNER = 'SEED' then
296 l_user_id := 1;
297 else
298 l_user_id := 0;
299 end if;
300
301 update CS_PARTY_ROLES_TL set
302 name = nvl(x_name,name),
306 last_update_login = 0,
303 description = nvl(x_description,name),
304 last_update_date = nvl(x_last_update_date,sysdate),
305 last_updated_by = l_user_id,
307 source_lang = userenv('LANG')
308 where PARTY_ROLE_CODE = x_PARTY_ROLE_CODE
309 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
310
311 end TRANSLATE_ROW;
312
313 end CS_PARTY_ROLES_PKG;