1 PACKAGE BODY HZ_UI_UTIL_PKG AS
2 /* $Header: ARHPUISB.pls 120.19 2006/05/24 13:28:17 idali noship $ */
3
4 -------------------------------------
5 -- CHECK_ENTITY_CREATION - Signature
6 -------------------------------------
7
8 PROCEDURE check_entity_creation (
9 p_entity_name IN VARCHAR2, -- table name
10 p_data_source IN VARCHAR2, -- if applicable
11 p_party_id IN NUMBER, -- only pass if available
12 p_parent_entity_name IN VARCHAR2, -- if applicable
13 p_parent_entity_pk1 IN VARCHAR2, -- if applicable
14 p_parent_entity_pk2 IN VARCHAR2, -- if applicable
15 p_function_name IN VARCHAR2, -- FND function name
16 x_create_flag OUT NOCOPY VARCHAR2 -- can we create?
17 ) IS
18 l_entity_attr_id NUMBER;
19 l_return_status VARCHAR2(1);
20 BEGIN
21
22 /*
23 * Call the Third Party Data Integration routine to see if the user can
24 * create instances of the entity.
25 */
26
27 IF p_entity_name IN ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES') THEN
28 x_create_flag := 'Y';
29 ELSE
30 HZ_MIXNM_UTILITY.CheckUserCreationPrivilege(
31 p_entity_name => p_entity_name,
32 p_entity_attr_id => l_entity_attr_id,
33 p_mixnmatch_enabled => NULL,
34 p_actual_content_source => 'USER_ENTERED',
35 x_return_status => l_return_status
36 );
37
38 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
39 x_create_flag := 'N';
40 ELSE
41 x_create_flag := 'Y';
42 END IF;
43 END IF;
44
45 EXCEPTION
46 WHEN OTHERS THEN
47 x_create_flag := 'N';
48 END check_entity_creation;
49
50 -------------------------------------
51 -- GET_VIEW_PREDICATE
52 -------------------------------------
53
54 FUNCTION get_view_predicate (
55 p_entity_name IN VARCHAR2, -- entity/table you wish to filter
56 p_entity_alias IN VARCHAR2, -- alias for entity as used in SELECT
57 p_function_name IN VARCHAR2 -- FND function name
58 ) RETURN VARCHAR2
59 IS
60 l_alias VARCHAR2(30);
61 BEGIN
62
63 IF p_entity_name = 'HZ_PARTIES' THEN
64 IF p_entity_alias IS NOT NULL THEN
65 l_alias := p_entity_alias;
66 ELSE
67 l_alias := p_entity_name;
68 END IF;
69
70 RETURN ' (' || l_alias || '.' || 'ORIG_SYSTEM_REF NOT LIKE ''PER%'') ';
71
72 ELSE
73 RETURN ' (1=1) ';
74 END IF;
75 END get_view_predicate;
76
77
78 -------------------------------------
79 -- CHECK_ROW_ACCESS
80 -------------------------------------
81
82 /*
83 PROCEDURE check_row_access (
84 p_entity_name IN VARCHAR2, -- table name
85 p_data_source IN VARCHAR2 DEFAULT NULL, -- if applicable
86 p_entity_pk1 IN VARCHAR2, -- primary key
87 p_entity_pk2 IN VARCHAR2 DEFAULT NULL, -- primary key pt. 2
88 p_party_id IN NUMBER DEFAULT NULL, -- only pass if available
89 x_viewable_flag OUT NOCOPY VARCHAR2, -- can we see it?
90 x_updateable_flag OUT NOCOPY VARCHAR2, -- can we mess with it?
91 x_deleteable_flag OUT NOCOPY VARCHAR2 -- can we get rid of it?
92 ) IS
93 BEGIN
94
95 --
96 -- This is for those cases where the caller wants to check all the access
97 -- at once. Merely a wrapper for the individual operation-level checks.
98 --
99
100 -- Check view access
101
102 x_viewable_flag := check_row_viewable (
103 p_entity_name => p_entity_name,
104 p_data_source => p_data_source,
105 p_entity_pk1 => p_entity_pk1,
106 p_entity_pk2 => p_entity_pk2,
107 p_party_id => p_party_id
108 );
109
110 -- Check update access
111
112 x_updateable_flag := check_row_updateable (
113 p_entity_name => p_entity_name,
114 p_data_source => p_data_source,
115 p_entity_pk1 => p_entity_pk1,
116 p_entity_pk2 => p_entity_pk2,
117 p_party_id => p_party_id
118 );
119
120 -- Check delete access
121
122 x_deleteable_flag := check_row_deleteable (
123 p_entity_name => p_entity_name,
124 p_data_source => p_data_source,
125 p_entity_pk1 => p_entity_pk1,
126 p_entity_pk2 => p_entity_pk2,
127 p_party_id => p_party_id
128 );
129
130 END check_row_access;
131 */
132
133 -- "Pure" Function versions, that can be used directly in SELECT statements
134
135 FUNCTION check_row_viewable (
136 p_entity_name IN VARCHAR2, -- table name
137 p_data_source IN VARCHAR2, -- if applicable
138 p_entity_pk1 IN VARCHAR2, -- primary key
139 p_entity_pk2 IN VARCHAR2, -- primary key pt. 2
140 p_party_id IN NUMBER, -- only pass if available
141 p_function_name IN VARCHAR2 -- FND function name
142 ) RETURN VARCHAR2 -- "Y" or "N" if we can view the row
143 IS
144 l_viewable_flag VARCHAR2(1);
145 l_return_status VARCHAR2(1);
146 l_msg_count NUMBER;
147 l_msg_data VARCHAR2(2000);
148 BEGIN
149 /*
150 * Call the Data Sharing and Security API to check DSS security rules.
151 * The DSS function returns "T" or "F".
152 */
153
154 l_viewable_flag := HZ_DSS_UTIL_PUB.TEST_INSTANCE (
155 p_operation_code => 'SELECT',
156 p_db_object_name => p_entity_name,
157 p_instance_pk1_value => p_entity_pk1,
158 p_instance_pk2_value => p_entity_pk2,
159 x_return_status => l_return_status,
160 x_msg_count => l_msg_count,
161 x_msg_data => l_msg_data
162 );
163
164 -- Default security to N if API fails
165 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
166 l_viewable_flag := 'N';
167 ELSIF l_viewable_flag = 'T' THEN -- Will return FND_API.G_TRUE from HZ_DSS_UTIL_PUB
168 l_viewable_flag := 'Y';
169 ELSE
170 l_viewable_flag := 'N';
171 END IF;
172
173 RETURN l_viewable_flag;
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 RETURN 'N';
178 END check_row_viewable;
179
180
181
182
183 FUNCTION check_row_updateable (
184 p_entity_name IN VARCHAR2, -- table name
185 p_data_source IN VARCHAR2, -- if applicable
186 p_entity_pk1 IN VARCHAR2, -- primary key
187 p_entity_pk2 IN VARCHAR2, -- primary key pt. 2
188 p_party_id IN NUMBER, -- only pass if available
189 p_function_name IN VARCHAR2 -- FND function name
190 ) RETURN VARCHAR2 -- "Y" or "N" if we can update the row
191 IS
192 l_updateable_flag VARCHAR2(1) := 'N';
193 l_return_status VARCHAR2(1);
194 l_msg_count NUMBER;
195 l_msg_data VARCHAR2(2000);
196 BEGIN
197 /*
198 * Special code added to support MOSR update functionality BASED ON PROFILE
199 */
200 IF p_entity_name = 'HZ_ORIG_SYS_REFERENCES' THEN
201 IF HZ_UTILITY_V2PUB.is_purchased_content_source(p_data_source) = 'Y' THEN
202 l_updateable_flag := 'N';
203 ELSE
204 IF NVL(FND_PROFILE.value('HZ_SSM_VIEW_UPDATE_STATE'), 'VIEW_ONLY') = 'CREATE_AND_UPDATE' THEN
205 l_updateable_flag := 'Y';
206 END IF;
207 END IF;
208 return l_updateable_flag;
209 END IF;
210
211 /*
212 * Call the Data Sharing and Security API to check DSS security rules.
213 * The DSS function returns "T" or "F".
214 */
215
216 l_updateable_flag := HZ_DSS_UTIL_PUB.TEST_INSTANCE (
217 p_operation_code => 'UPDATE',
218 p_db_object_name => p_entity_name,
219 p_instance_pk1_value => p_entity_pk1,
220 p_instance_pk2_value => p_entity_pk2,
221 p_user_name => FND_GLOBAL.User_Name,
222 x_return_status => l_return_status,
223 x_msg_count => l_msg_count,
224 x_msg_data => l_msg_data
225 );
226
227 -- Default security to N if API fails
228 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
229 l_updateable_flag := 'N';
230 ELSIF l_updateable_flag = 'T' THEN -- Will return FND_API.G_TRUE from HZ_DSS_UTIL_PUB
231 l_updateable_flag := 'Y';
232 ELSE
233 l_updateable_flag := 'N';
234 END IF;
235
236 -- If DSS check fails,then no need to check any further security.
237
238 IF l_updateable_flag = 'N' THEN
239 RETURN l_updateable_flag;
240 END IF;
241
242 /*
243 * Call the Third Party Data Integration security rules.
244 */
245 -- Bug 4203937 : check Mix-N-Match security only for
246 -- other entities supported by Mix-N-Match
247 /* Bug 4693719 : Do not call CheckUserUpdatePrivilege from CPUI
248 * CPUI should display update enabled icon in the UI so that
249 * primary_flag, start_date, status etc columns can be updated.
250 * Error will be raised from API if the rules are violated
251 *
252 If p_entity_name in ('HZ_RELATIONSHIPS', 'HZ_CODE_ASSIGNMENTS',
253 'HZ_CONTACT_POINTS', 'HZ_CREDIT_RATINGS', 'HZ_FINANCIAL_REPORTS',
254 'HZ_LOCATIONS', 'HZ_PARTY_SITES', 'HZ_FINANCIAL_NUMBERS') then
255
256 HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege (
257 p_actual_content_source => p_data_source,
258 p_new_actual_content_source => 'USER_ENTERED',
259 p_entity_name => p_entity_name,
260 x_return_status => l_return_status
261 );
262
263 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
264 l_updateable_flag := 'N';
265 ELSE
266 l_updateable_flag := 'Y';
267 END IF;
268 end if;
269 */
270 RETURN l_updateable_flag;
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 RETURN 'N';
275 END check_row_updateable;
276
277
278
279 FUNCTION check_row_deleteable (
280 p_entity_name IN VARCHAR2, -- table name
281 p_data_source IN VARCHAR2, -- if applicable
282 p_entity_pk1 IN VARCHAR2, -- primary key
283 p_entity_pk2 IN VARCHAR2, -- primary key pt. 2
284 p_party_id IN NUMBER, -- only pass if available
285 p_function_name IN VARCHAR2 -- FND function name
286 ) RETURN VARCHAR2 -- "Y" or "N" if we can delete the row
287 IS
288 l_deleteable_flag VARCHAR2(1) := 'N';
289 l_return_status VARCHAR2(1);
290 l_msg_count NUMBER;
291 l_msg_data VARCHAR2(2000);
292 BEGIN
293 /*
294 * Special code added to support MOSR update functionality BASED ON PROFILE
295 */
296 IF p_entity_name = 'HZ_ORIG_SYS_REFERENCES' THEN
297 IF NVL(FND_PROFILE.value('HZ_SSM_VIEW_UPDATE_STATE'), 'VIEW_ONLY') = 'CREATE_AND_UPDATE' THEN
298 l_deleteable_flag := 'Y';
299 END IF;
300 return l_deleteable_flag;
301 END IF;
302
303 /*
304 * Call the Data Sharing and Security API to check DSS security rules.
305 * The DSS function returns "T" or "F".
306 */
307
308 l_deleteable_flag := HZ_DSS_UTIL_PUB.TEST_INSTANCE (
309 p_operation_code => 'DELETE',
310 p_db_object_name => p_entity_name,
311 p_instance_pk1_value => p_entity_pk1,
312 p_instance_pk2_value => p_entity_pk2,
313 p_user_name => FND_GLOBAL.User_Name,
314 x_return_status => l_return_status,
315 x_msg_count => l_msg_count,
316 x_msg_data => l_msg_data
317 );
318
319 -- Default security to N if API fails
320 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
321 l_deleteable_flag := 'N';
322 ELSIF l_deleteable_flag = 'T' THEN -- Will return FND_API.G_TRUE from HZ_DSS_UTIL_PUB
323 l_deleteable_flag := 'Y';
324 ELSE
325 l_deleteable_flag := 'N';
326 END IF;
327
328 RETURN l_deleteable_flag;
329
330 EXCEPTION
331 WHEN OTHERS THEN
332 RETURN 'N';
333 END check_row_deleteable;
334
335 -------------------------------------
336 -- CHECK_COLUMNS
337 -------------------------------------
338
339
340 PROCEDURE check_columns(
341 p_entity_name IN VARCHAR2, -- table name
342 p_data_source IN VARCHAR2, -- if applicable
343 p_entity_pk1 IN VARCHAR2, -- primary key
344 p_entity_pk2 IN VARCHAR2, -- primary key pt. 2
345 p_party_id IN NUMBER , -- only pass if available
346 p_function_name IN VARCHAR2, -- function name
347 p_attribute_list IN HZ_MIXNM_UTILITY.INDEXVARCHAR30List, -- pl/sql table of attribute names
348 p_value_is_null_list IN HZ_MIXNM_UTILITY.INDEXVARCHAR1List, -- pl/sql table of flags
349 x_viewable_list OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR1List, -- pl/sql table of flags
350 x_updateable_list OUT NOCOPY HZ_MIXNM_UTILITY.INDEXVARCHAR1List -- pl/sql table of flags
351 ) IS
352 l_return_status VARCHAR2(1);
353 i NUMBER;
354 BEGIN
355
356 /*
357 * Call the Third Party Data Integration column checking routine.
358 */
359
360 HZ_MIXNM_UTILITY.areSSTColumnsUpdeable (
361 p_party_id => p_party_id,
362 p_entity_name => p_entity_name,
363 p_attribute_name_list => p_attribute_list,
364 p_value_is_null_list => p_value_is_null_list,
365 p_data_source_type => p_data_source,
366 x_updatable_flag_list => x_updateable_list,
367 x_return_status => l_return_status
368 );
369
370 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
371 FOR i IN p_attribute_list.FIRST .. p_attribute_list.LAST
372 LOOP
373 x_updateable_list(i) := 'N';
374 END LOOP;
375 END IF;
376
377 /*
378 * We do not currently have view security at the attribute level, so
379 * all attributes are viewable.
380 */
381
382 FOR i IN p_attribute_list.FIRST .. p_attribute_list.LAST
383 LOOP
384 x_viewable_list(i) := 'Y';
385 END LOOP;
386
387 END check_columns;
388
389 /**
390 * PROCEDURE get_value
391 *
392 * DESCRIPTION
393 * used by common party UI .
394 * will return various value
395 * ARGUMENTS
396 * IN:
397 * p_org_contact_id org contact id. If passed in, will return
398 * org contact roles.
399 * p_phone_country_code phone country code
400 * p_phone_area_code phone area_code
401 * p_phone_number phone number. If passed in, will return
402 * formatted phone number.
403 * p_phone_extension phone extension.
404 * p_phone_line_type phone_line_type
405 * p_location_id location id. If passed in, will return
406 * formatted address.
407 * p_cust_acct_id Cust account ID, returns the formatted bill_to address
408 * p_cust_acct_site_id Cust account site ID, returns the formatted bill_to address
409 */
410
411 PROCEDURE get_value (
412 p_org_contact_id IN VARCHAR2,
413 p_phone_country_code IN VARCHAR2,
414 p_phone_area_code IN VARCHAR2,
415 p_phone_number IN VARCHAR2,
416 p_phone_extension IN VARCHAR2,
417 p_phone_line_type IN VARCHAR2,
418 p_location_id IN VARCHAR2,
419 x_org_contact_roles OUT NOCOPY VARCHAR2,
420 x_formatted_phone OUT NOCOPY VARCHAR2,
421 x_formatted_address OUT NOCOPY VARCHAR2,
422 p_act_cont_role_id IN VARCHAR2,
423 x_act_contact_roles OUT NOCOPY VARCHAR2,
424 p_primary_phone_contact_pt_id IN NUMBER,
425 x_has_contact_restriction OUT NOCOPY VARCHAR2,
426 p_relationship_type_id IN NUMBER,
427 p_relationship_group_code IN VARCHAR2,
428 x_is_in_relationship_group OUT NOCOPY VARCHAR2,
429 p_cust_acct_id IN VARCHAR2,
430 x_billto_address OUT NOCOPY VARCHAR2,
431 p_cust_acct_site_id IN VARCHAR2
432 ) IS
433
434 BEGIN
435
436 IF p_cust_acct_id IS NOT NULL THEN
437 BEGIN
438 SELECT hz_format_pub.format_address ( hps.LOCATION_ID , null , null ,' , ' , null , null , null , null )
439 INTO x_billto_address
440 FROM HZ_PARTY_SITES hps , HZ_CUST_ACCT_SITES_ALL hcas
441 WHERE hcas.BILL_TO_FLAG='P'
442 AND hcas.CUST_ACCOUNT_ID = p_cust_acct_id
443 AND hcas.PARTY_SITE_ID = hps.PARTY_SITE_ID;
444 EXCEPTION
445 WHEN OTHERS THEN
446 x_billto_address:=NULL;
447 END;
448 END IF;
449
450 IF p_org_contact_id IS NOT NULL THEN
451 x_org_contact_roles := hz_utility_v2pub.get_org_contact_role(p_org_contact_id);
452 END IF;
453
454 IF p_phone_number IS NOT NULL THEN
455 x_formatted_phone :=
456 hz_format_phone_v2pub.get_formatted_phone (
457 p_phone_country_code, p_phone_area_code, p_phone_number,
458 p_phone_extension, p_phone_line_type);
459 END IF;
460
461 IF p_location_id IS NOT NULL THEN
462 x_formatted_address :=
463 hz_format_pub.format_address(p_location_id, null, null, ', ');
464 END IF;
465
466 IF p_act_cont_role_id IS NOT NULL THEN
467 x_act_contact_roles := hz_act_util_pub.get_act_contact_roles(p_act_cont_role_id);
468 END IF;
469
470 IF p_primary_phone_contact_pt_id IS NOT NULL THEN
471 x_has_contact_restriction :=
472 hz_utility_v2pub.is_restriction_exist(
473 'HZ_CONTACT_POINTS',
474 p_primary_phone_contact_pt_id,
475 'DO_NOT');
476 END IF;
477
478 IF p_relationship_type_id IS NOT NULL AND
479 p_relationship_group_code IS NOT NULL
480 THEN
481 x_is_in_relationship_group :=
482 hz_utility_v2pub.is_role_in_relationship_group(
483 p_relationship_type_id,
484 p_relationship_group_code);
485 END IF;
486
487 IF p_cust_acct_site_id IS NOT NULL
488 THEN
489 BEGIN
490 SELECT HZ_FORMAT_PUB.format_address(party_site.location_id, null, null, ', ')
491 ||decode(acct.PARTY_ID,
492 party_site.PARTY_ID,'',
493 ' ('||(select party.PARTY_NAME
494 from HZ_PARTIES party, HZ_RELATIONSHIPS reln
495 where party_site.PARTY_ID = reln.PARTY_ID
496 AND reln.SUBJECT_TYPE = 'PERSON'
497 AND reln.SUBJECT_ID = party.PARTY_ID)||')')
498 INTO x_formatted_address
499 FROM HZ_CUST_ACCT_SITES_ALL site,
500 HZ_CUST_ACCOUNTS acct,
501 HZ_PARTY_SITES party_site
502 WHERE site.CUST_ACCT_SITE_ID = p_cust_acct_site_id
503 AND party_site.PARTY_SITE_ID = site.PARTY_SITE_ID
504 AND acct.CUST_ACCOUNT_ID = site.CUST_ACCOUNT_ID;
505 EXCEPTION
506 WHEN OTHERS THEN
507 x_formatted_address:=NULL;
508 END;
509 END IF;
510
511
512 END get_value;
513
514 END HZ_UI_UTIL_PKG;
[Home] [Help]