[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_USAGES_PKG
Source
1 PACKAGE BODY HZ_PARTY_USAGES_PKG AS
2 /*$Header: ARHPUCTB.pls 120.3 2005/05/24 23:22:47 jhuang noship $ */
3
4 PROCEDURE insert_row (
5 x_party_usage_code IN VARCHAR2,
6 x_party_usage_type IN VARCHAR2,
7 x_restrict_manual_assign_flag IN VARCHAR2,
8 x_restrict_manual_update_flag IN VARCHAR2,
9 x_publish_to_wfds_flag IN VARCHAR2,
10 x_status_flag IN VARCHAR2,
11 x_object_version_number IN NUMBER,
12 x_party_usage_name IN VARCHAR2,
13 x_description IN VARCHAR2,
14 x_creation_date IN DATE,
15 x_created_by IN NUMBER,
16 x_last_update_date IN DATE,
17 x_last_updated_by IN NUMBER,
18 x_last_update_login IN NUMBER
19 ) IS
20
21 BEGIN
22
23 INSERT INTO hz_party_usages_b (
24 party_usage_code,
25 party_usage_type,
26 restrict_manual_assign_flag,
27 restrict_manual_update_flag,
28 publish_to_wfds_flag,
29 status_flag,
30 object_version_number,
31 created_by,
32 creation_date,
33 last_update_login,
34 last_update_date,
35 last_updated_by
36 )
37 VALUES (
38 x_party_usage_code,
39 x_party_usage_type,
40 DECODE(x_restrict_manual_assign_flag,
41 NULL, 'N',
42 FND_API.G_MISS_CHAR, 'N',
43 x_restrict_manual_assign_flag),
44 DECODE(x_restrict_manual_update_flag,
45 NULL, 'N',
46 FND_API.G_MISS_CHAR, 'N',
47 x_restrict_manual_update_flag),
48 DECODE(x_publish_to_wfds_flag,
49 NULL, 'N',
50 FND_API.G_MISS_CHAR, 'N',
51 x_publish_to_wfds_flag),
52 DECODE(x_status_flag,
53 NULL, 'A',
54 FND_API.G_MISS_CHAR, 'A',
55 x_status_flag),
56 x_object_version_number,
57 x_created_by,
58 x_creation_date,
59 x_last_update_login,
60 x_last_update_date,
61 x_last_updated_by
62 );
63
64 INSERT INTO hz_party_usages_tl (
65 party_usage_code,
66 language,
67 source_lang,
68 party_usage_name,
69 description,
70 created_by,
71 creation_date,
72 last_update_login,
73 last_update_date,
74 last_updated_by
75 )
76 SELECT
77 x_party_usage_code,
78 l.language_code,
79 USERENV('LANG'),
80 x_party_usage_name,
81 x_description,
82 x_created_by,
83 x_creation_date,
84 x_last_update_login,
85 x_last_update_date,
86 x_last_updated_by
87 FROM fnd_languages l
88 WHERE l.installed_flag IN ('I', 'B')
89 AND NOT EXISTS (
90 SELECT NULL
91 FROM hz_party_usages_tl t
92 WHERE t.party_usage_code = x_party_usage_code
93 AND t.language = l.language_code
94 );
95
96 END insert_row;
97
98
99 PROCEDURE update_row (
100 x_party_usage_code IN VARCHAR2,
101 x_party_usage_type IN VARCHAR2,
102 x_restrict_manual_assign_flag IN VARCHAR2,
103 x_restrict_manual_update_flag IN VARCHAR2,
104 x_publish_to_wfds_flag IN VARCHAR2,
105 x_status_flag IN VARCHAR2,
106 x_object_version_number IN NUMBER,
107 x_party_usage_name IN VARCHAR2,
108 x_description IN VARCHAR2,
109 x_last_update_date IN DATE,
110 x_last_updated_by IN NUMBER,
111 x_last_update_login IN NUMBER
112 ) IS
113
114 BEGIN
115
116 UPDATE hz_party_usages_b
117 SET
118 party_usage_type = x_party_usage_type,
119 restrict_manual_assign_flag =
120 DECODE(x_restrict_manual_assign_flag,
121 NULL, restrict_manual_assign_flag,
122 FND_API.G_MISS_CHAR, NULL,
123 x_restrict_manual_assign_flag),
124 restrict_manual_update_flag =
125 DECODE(x_restrict_manual_update_flag,
126 NULL, restrict_manual_update_flag,
127 FND_API.G_MISS_CHAR, NULL,
128 x_restrict_manual_update_flag),
129 publish_to_wfds_flag =
130 DECODE(x_publish_to_wfds_flag,
131 NULL, publish_to_wfds_flag,
132 FND_API.G_MISS_CHAR, NULL,
133 x_publish_to_wfds_flag),
134 status_flag =
135 DECODE(x_status_flag,
136 NULL, status_flag,
137 x_status_flag),
138 object_version_number =
139 DECODE(x_object_version_number,
140 NULL, object_version_number,
141 x_object_version_number),
142 last_update_login = x_last_update_login,
143 last_update_date = x_last_update_date,
144 last_updated_by = x_last_updated_by
145 WHERE party_usage_code = x_party_usage_code;
146
147 IF ( SQL%NOTFOUND ) THEN
148 RAISE NO_DATA_FOUND;
149 END IF;
150
151 UPDATE hz_party_usages_tl
152 SET
153 party_usage_name =
154 DECODE(x_party_usage_name,
155 NULL, party_usage_name,
156 x_party_usage_name),
157 description =
158 DECODE(x_description,
159 NULL, description,
160 x_description),
161 last_update_login = x_last_update_login,
162 last_update_date = x_last_update_date,
163 last_updated_by = x_last_updated_by,
164 source_lang = USERENV('LANG')
165 WHERE party_usage_code = x_party_usage_code
166 AND USERENV('LANG') IN (language, source_lang);
167
168 IF ( SQL%NOTFOUND ) THEN
169 RAISE NO_DATA_FOUND;
170 END IF;
171
172 END update_row;
173
174
175 PROCEDURE add_language IS
176
177 BEGIN
178
179 DELETE FROM hz_party_usages_tl t
180 WHERE NOT EXISTS (
181 SELECT NULL
182 FROM hz_party_usages_b b
183 WHERE b.party_usage_code = t.party_usage_code
184 );
185
186 UPDATE hz_party_usages_tl t
187 SET (
188 party_usage_name,
189 description
190 ) = (
191 SELECT
192 b.party_usage_name,
193 b.description
194 FROM hz_party_usages_tl b
195 WHERE b.party_usage_code = t.party_usage_code
196 AND b.language = t.source_lang)
197 WHERE (
198 t.party_usage_code,
199 t.language
200 ) IN (
201 SELECT
202 subt.party_usage_code,
203 subt.language
204 FROM hz_party_usages_tl subb, hz_party_usages_tl subt
205 WHERE subb.party_usage_code = subt.party_usage_code
206 AND subb.language = subt.source_lang
207 AND (subb.party_usage_name <> subt.party_usage_name
208 OR subb.description <> subt.description
209 OR (subb.description IS NULL AND subt.description IS NOT NULL)
210 OR (subb.description IS NOT NULL and subt.description IS NULL)
211 ));
212
213 INSERT INTO hz_party_usages_tl (
214 party_usage_code,
215 party_usage_name,
216 description,
217 created_by,
218 creation_date,
219 last_update_login,
220 last_update_date,
221 last_updated_by,
222 language,
223 source_lang
224 )
225 SELECT /*+ ORDERED */
226 b.party_usage_code,
227 b.party_usage_name,
228 b.description,
229 b.created_by,
230 b.creation_date,
231 b.last_update_login,
232 b.last_update_date,
233 b.last_updated_by,
234 l.language_code,
235 b.source_lang
236 FROM hz_party_usages_tl b, fnd_languages l
237 WHERE l.installed_flag IN ('I', 'B')
238 AND b.language = USERENV('LANG')
239 AND NOT EXISTS (
240 SELECT NULL
241 FROM hz_party_usages_tl t
242 WHERE t.party_usage_code = b.party_usage_code
243 AND t.language = l.language_code
244 );
245
246 END add_language;
247
248
249 PROCEDURE translate_row (
250 x_party_usage_code IN VARCHAR2,
251 x_owner IN VARCHAR2,
252 x_party_usage_name IN VARCHAR2,
253 x_description IN VARCHAR2
254 ) IS
255
256 BEGIN
257
258 translate_row (
259 x_party_usage_code => x_party_usage_code,
260 x_owner => x_owner,
261 x_party_usage_name => x_party_usage_name,
262 x_description => x_description,
263 x_last_update_date => NULL,
264 x_custom_mode => NULL
265 );
266
267 END translate_row;
268
269
270 /**
271 * The following procedure will be called only from lct file.
272 * We don't need to check last update date because:
273 *
274 * - Customer can't update seeded party usage codes and rules in Admin UI.
275 * Seeded party usage codes and rules can only be updated through FDNLOAD
276 * through patching process.
277 * - Party usage codes created by customer through Admin UI must follow some
278 * naming convention. For instance, party usage code must end with _CUS.
279 * This will make sure that seeded party usage codes later added won't
280 * conflict with ones customer created.
281 * - Same party usage name can be shared by different party usage codes
282 * (i.e. party usage codes customer created can have the same name as
283 * party usage codes seeded by us.)
284 */
285
286 PROCEDURE translate_row (
287 x_party_usage_code IN VARCHAR2,
288 x_owner IN VARCHAR2,
289 x_party_usage_name IN VARCHAR2,
290 x_description IN VARCHAR2,
291 x_last_update_date IN VARCHAR2,
292 x_custom_mode IN VARCHAR2
293 ) IS
294
295 f_luby NUMBER; -- entity owner in file
296 f_ludate DATE; -- entity update date in file
297
298 BEGIN
299
300 -- Translate owner to file_last_updated_by
301 f_luby := fnd_load_util.owner_id(x_owner);
302
303 -- Translate char last_update_date to date
304 f_ludate := NVL(TO_DATE(x_last_update_date, 'YYYY/MM/DD'), sysdate);
305
306 UPDATE hz_party_usages_tl
307 SET
308 party_usage_name =
309 DECODE(x_party_usage_name,
310 NULL, party_usage_name,
311 x_party_usage_name),
312 description =
313 DECODE(x_description,
314 NULL, description,
315 x_description),
316 last_update_date = f_ludate,
317 last_updated_by = f_luby,
318 last_update_login = 0,
319 source_lang = USERENV('LANG')
320 WHERE party_usage_code = x_party_usage_code
321 AND USERENV('LANG') IN (language, source_lang);
322
323 END translate_row;
324
325
326 PROCEDURE load_row (
327 x_party_usage_code IN VARCHAR2,
328 x_owner IN VARCHAR2,
329 x_party_usage_type IN VARCHAR2,
330 x_restrict_manual_assign_flag IN VARCHAR2,
331 x_restrict_manual_update_flag IN VARCHAR2,
332 x_publish_to_wfds_flag IN VARCHAR2,
333 x_status_flag IN VARCHAR2,
334 x_party_usage_name IN VARCHAR2,
335 x_description IN VARCHAR2,
336 x_last_update_date IN VARCHAR2,
337 x_custom_mode IN VARCHAR2
338 ) IS
339
340 f_luby NUMBER; -- entity owner in file
341 f_ludate DATE; -- entity update date in file
342
343 CURSOR c_party_usage IS
344 SELECT object_version_number
345 FROM hz_party_usages_b
346 WHERE party_usage_code = x_party_usage_code;
347
348 db_object_version_number NUMBER;
349
350 BEGIN
351
352 -- Translate owner to file_last_updated_by
353 f_luby := fnd_load_util.owner_id(x_owner);
354
355 -- Translate char last_update_date to date
356 f_ludate := NVL(TO_DATE(x_last_update_date, 'YYYY/MM/DD'), sysdate);
357
358 OPEN c_party_usage;
359 FETCH c_party_usage INTO db_object_version_number;
360
361 -- create party usage if not found.
362 -- otherwise, update
363
364 IF (c_party_usage%NOTFOUND) THEN
365 insert_row (
366 x_party_usage_code => x_party_usage_code,
367 x_party_usage_type => x_party_usage_type,
368 x_restrict_manual_assign_flag => x_restrict_manual_assign_flag,
369 x_restrict_manual_update_flag => x_restrict_manual_update_flag,
370 x_publish_to_wfds_flag => x_publish_to_wfds_flag,
371 x_status_flag => x_status_flag,
372 x_object_version_number => 1,
373 x_party_usage_name => x_party_usage_name,
374 x_description => x_description,
375 x_creation_date => f_ludate,
376 x_created_by => f_luby,
377 x_last_update_date => f_ludate,
378 x_last_updated_by => f_luby,
379 x_last_update_login => 0
380 );
381 ELSE
382 update_row (
383 x_party_usage_code => x_party_usage_code,
384 x_party_usage_type => x_party_usage_type,
385 x_restrict_manual_assign_flag => x_restrict_manual_assign_flag,
386 x_restrict_manual_update_flag => x_restrict_manual_update_flag,
387 x_publish_to_wfds_flag => x_publish_to_wfds_flag,
388 x_status_flag => x_status_flag,
389 x_object_version_number => db_object_version_number,
390 x_party_usage_name => x_party_usage_name,
391 x_description => x_description,
392 x_last_update_date => f_ludate,
393 x_last_updated_by => f_luby,
394 x_last_update_login => 0
395 );
396 END IF;
397
398 close c_party_usage;
399
400 END load_row;
401
402
403 END HZ_PARTY_USAGES_PKG;