[Home] [Help]
PACKAGE BODY: APPS.HZ_COMMON_PUB
Source
1 PACKAGE BODY hz_common_pub AS
2 /*$Header: ARHCOMMB.pls 120.15.12010000.2 2010/03/04 11:36:43 rgokavar ship $ */
3
4 -- Bug 2444678: Removed caching.
5
6 -- added for mix-n-match.
7 -- G_ENTITY_NAME VARCHAR2(30);
8 -- G_ENTITY_ATTR_ID NUMBER;
9 --G_DATA_SOURCES VARCHAR2(200);
10
11
12 /* SSM SST Integration and Extension.
13 |
14 | -- private function for bug fix 2969850
15 |
16 | FUNCTION getNotSelectedDataSource(
17 | p_data_source IN VARCHAR2
18 | ) RETURN VARCHAR2;
19 */
20
21 /*===========================================================================+
22 | PROCEDURE |
23 | commit_transaction |
24 | |
25 | DESCRIPTION |
26 | Commits transaction. |
27 | |
28 | SCOPE - PUBLIC |
29 | |
30 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
31 | |
32 | ARGUMENTS : IN: |
33 | OUT: |
34 | IN/ OUT: |
35 | |
36 | RETURNS : NONE |
37 | |
38 | NOTES |
39 | |
40 | MODIFICATION HISTORY |
41 | Rashmi Goyal 08-OCT-99 Created |
42 | |
43 +===========================================================================*/
44
45 procedure commit_transaction IS
46 BEGIN
47 commit;
48 END;
49
50 /*===========================================================================+
51 | PROCEDURE |
52 | rollback_transaction |
53 | |
54 | DESCRIPTION |
55 | Rollbacks transaction. |
56 | |
57 | SCOPE - PUBLIC |
58 | |
59 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
60 | |
61 | ARGUMENTS : IN: |
62 | OUT: |
63 | IN/ OUT: |
64 | |
65 | RETURNS : NONE |
69 | MODIFICATION HISTORY |
66 | |
67 | NOTES |
68 | |
70 | Rashmi Goyal 08-OCT-99 Created |
71 | |
72 +===========================================================================*/
73
74 procedure rollback_transaction IS
75 BEGIN
76 rollback;
77 END;
78
79 /*===========================================================================+
80 | FUNCTION |
81 | is_TCA_installed |
82 | |
83 | DESCRIPTION |
84 | Checks if TCA is installed. |
85 | |
86 | SCOPE - PUBLIC |
87 | |
88 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
89 | |
90 | ARGUMENTS : IN: |
91 | OUT: |
92 | IN/ OUT: |
93 | |
94 | RETURNS : NONE |
95 | |
96 | NOTES |
97 | |
98 | MODIFICATION HISTORY |
99 | Rashmi Goyal 08-OCT-99 Created |
100 | |
101 +===========================================================================*/
102
103 function is_TCA_installed RETURN BOOLEAN IS
104 l_installed NUMBER;
105 l_user_schema VARCHAR2(32) := USER;
106 BEGIN
107 -- Bug 4956173
108 SELECT 1
109 INTO l_installed
110 FROM sys.all_source
111 WHERE name = 'HZ_PARTY_V2PUB'
112 AND type = 'PACKAGE BODY'
113 AND owner = l_user_schema
114 AND rownum=1;
115
116 IF l_installed = 1 THEN
117 RETURN TRUE;
118 ELSE
119 RETURN FALSE;
120 END IF;
121 EXCEPTION WHEN NO_DATA_FOUND THEN
122 RETURN FALSE;
123 END;
124
125 /*===========================================================================+
126 | PROCEDURE
127 | validate_lookup
128 |
129 | DESCRIPTION
130 | Validate lookup code.
131 |
132 | SCOPE - PUBLIC
133 |
134 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
135 |
136 | ARGUMENTS : IN:
137 | p_lookup_type
138 | p_column
139 | p_column_value
140 | OUT:
141 | IN/ OUT:
142 | x_return_status
143 |
144 | RETURNS : NONE
145 |
146 | NOTES
147 |
148 | MODIFICATION HISTORY
149 | Rashmi Goyal 08-OCT-99 Created
150 | Jianying Huang 31-JAN-01 Bug 1621845: Replace count(*)
151 | with 'select 'Y'..and rownum=1
152 | Jianying Huang 12-FEB-01 Bug 1639630: Check 'YES/NO' lookup code by comparision
153 | instead of query.
154 |
155 +===========================================================================*/
156
157 procedure validate_lookup(
158 p_lookup_type IN VARCHAR2,
159 p_column IN VARCHAR2,
160 p_column_value IN VARCHAR2,
161 x_return_status IN OUT NOCOPY VARCHAR2
162 ) IS
163
164 CURSOR c_exist IS
165 SELECT 'Y'
166 FROM ar_lookups
167 WHERE lookup_type = p_lookup_type
168 AND lookup_code = p_column_value
169 AND ROWNUM = 1;
170
171 l_exist VARCHAR2(1);
172 l_error BOOLEAN;
173
174 BEGIN
175
176 IF p_column_value IS NOT NULL AND
177 p_column_value <> FND_API.G_MISS_CHAR THEN
178
179 IF p_lookup_type = 'YES/NO' THEN
180 IF p_column_value NOT IN ('Y', 'N') THEN
181 l_error := TRUE;
182 END IF;
183 ELSE
184 OPEN c_exist;
185 FETCH c_exist INTO l_exist;
186
187 IF c_exist%NOTFOUND THEN
188 l_error := TRUE;
189 END IF;
190
191 CLOSE c_exist;
192 END IF;
193
194 IF l_error THEN
195 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_LOOKUP');
196 FND_MESSAGE.SET_TOKEN('COLUMN', p_column);
197 FND_MESSAGE.SET_TOKEN('LOOKUP_TYPE', p_lookup_type);
198 FND_MSG_PUB.ADD;
199 x_return_status := FND_API.G_RET_STS_ERROR;
200 END IF;
201
202 END IF;
203
204 END;
205
206 PROCEDURE validate_fnd_lookup
207 ( p_lookup_type IN VARCHAR2,
208 p_column IN VARCHAR2,
212 CURSOR c1
209 p_column_value IN VARCHAR2,
210 x_return_status IN OUT NOCOPY VARCHAR2)
211 IS
213 IS
214 SELECT 'Y'
215 FROM fnd_lookup_values
216 WHERE lookup_type = p_lookup_type
217 AND lookup_code = p_column_value
218 AND ROWNUM = 1;
219 l_exist VARCHAR2(1);
220 BEGIN
221 IF ( p_column_value IS NOT NULL
222 AND p_column_value <> fnd_api.g_miss_char ) THEN
223 OPEN c1;
224 FETCH c1 INTO l_exist;
225 IF c1%NOTFOUND THEN
226 fnd_message.set_name('AR','HZ_API_INVALID_LOOKUP');
227 fnd_message.set_token('COLUMN',p_column);
228 fnd_message.set_token('LOOKUP_TYPE',p_lookup_type);
229 fnd_msg_pub.add;
230 x_return_status := fnd_api.g_ret_sts_error;
231 END IF;
232 CLOSE c1;
233 END IF;
234 END validate_fnd_lookup;
235
236 /*===========================================================================+
237 | FUNCTION |
238 | get_account_number |
239 | |
240 | DESCRIPTION |
241 | Gets unique account_number. |
242 | |
243 | SCOPE - PUBLIC |
244 | |
245 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
246 | |
247 | ARGUMENTS : IN: |
248 | OUT: |
249 | IN/ OUT: |
250 | |
251 | RETURNS : NONE |
252 | |
253 | NOTES |
254 | |
255 | MODIFICATION HISTORY |
256 | Chirag Mehta 17-AUG-00 Created |
257 | |
258 +===========================================================================*/
259
260
261 function get_account_number RETURN NUMBER IS
262
263 l_count number;
264 l_account_number varchar2(30);
265 BEGIN
266 l_count :=1;
267 WHILE l_count > 0 LOOP
268 SELECT to_char(hz_account_num_s.nextval)
269 INTO l_account_number FROM dual;
270 SELECT COUNT(*) INTO l_count
271 FROM hz_cust_accounts
272 WHERE account_number = l_account_number;
273
274 END LOOP;
275 RETURN l_account_number;
276 END get_account_number;
277
278 /*===========================================================================+
279 | FUNCTION |
280 | get_party_site_number |
281 | |
282 | DESCRIPTION |
283 | Gets unique party_site_number. |
284 | |
285 | SCOPE - PUBLIC |
286 | |
287 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
291 | IN/ OUT: |
288 | |
289 | ARGUMENTS : IN: |
290 | OUT: |
292 | |
293 | RETURNS : NONE |
294 | |
295 | NOTES |
296 | |
297 | MODIFICATION HISTORY |
298 | Chirag Mehta 17-AUG-00 Created |
299 | |
300 +===========================================================================*/
301
302
303 function get_party_site_number RETURN NUMBER IS
304
305 l_count number;
306 l_party_site_number varchar2(30);
307 BEGIN
308 l_count :=1;
309 WHILE l_count > 0 LOOP
310 SELECT to_char(hz_party_site_number_s.nextval)
311 INTO l_party_site_number FROM dual;
312 SELECT COUNT(*) INTO l_count
313 FROM hz_party_sites
314 WHERE party_site_number = l_party_site_number;
315
316 END LOOP;
317 RETURN l_party_site_number;
318 END get_party_site_number ;
319
320
321 FUNCTION content_source_type_security(
322 p_object_schema IN VARCHAR2,
323 p_object_name IN VARCHAR2
324 ) RETURN VARCHAR2 IS
325 l_predicate VARCHAR2(4000);
326 l_context VARCHAR2(10);
327 l_entity_attr_id NUMBER := null;
328 l_data_sources VARCHAR2(400);
329 l_not_data_source VARCHAR2(4000);
330 BEGIN
331
332 l_context := NVL(SYS_CONTEXT('hz', 'dnb_used'),'N');
333
334 IF l_context <> 'Y' THEN
335 IF p_object_name IN (
336 'HZ_ORGANIZATION_PROFILES', 'HZ_PERSON_PROFILES')
337 THEN
338 --Bug9063717 - Removed NVL Condition.
339 --l_predicate := 'NVL(actual_content_source,content_source_type) = ''SST''';
340 l_predicate := 'actual_content_source = ''SST''';
341
342 -- SSM SST Integration and Extension
343 -- Concept of select/de-select data sources is done away with for non-profile entities.
344 /*ELSE
345 IF p_object_name NOT IN ('HZ_CODE_ASSIGNMENTS', 'HZ_ORGANIZATION_INDICATORS') THEN
346
347 -- Bug 2444678: Removed caching.
348
349 -- IF p_object_name <> NVL(G_ENTITY_NAME, 'null') THEN
350 l_data_sources := HZ_MIXNM_UTILITY.getSelectedDataSources(p_object_name, l_entity_attr_id);
351 l_not_data_source := getNotSelectedDataSource (l_data_sources);
352 -- G_ENTITY_NAME := p_object_name;
353 -- END IF;
354 */
355 /* Bug 2780113- fix for DNB hierarchy project */
356 /*
357 IF p_object_name = 'HZ_PARTY_SITES' THEN
358 l_predicate := 'actual_content_source NOT IN ('||l_not_data_source||')';
359 ELSE
360 l_predicate := 'NVL(actual_content_source,content_source_type) NOT IN ('||l_not_data_source||')';
361 END IF;
362
363 ELSE
364 l_predicate := 'content_source_type = ''USER_ENTERED''';
365 END IF;
366 */
367 END IF;
368 END IF;
369
370 /*
371 l_predicate := '((SYS_CONTEXT(''hz'', ''dnb_used'')=''Y'')' ||
372 'OR ' ||
373 '((SYS_CONTEXT(''hz'', ''dnb_used'') IS NULL OR ' ||
374 ' SYS_CONTEXT(''hz'', ''dnb_used'')=''N'') and ' ||
375 'content_source_type=''USER_ENTERED''))';
376 */
377
378 RETURN l_predicate;
379
380 END;
381
382
383 procedure enable_cont_source_security IS
384 BEGIN
385 DBMS_SESSION.SET_CONTEXT('hz', 'dnb_used', 'N');
386 END;
387
388 procedure disable_cont_source_security IS
389 BEGIN
390 DBMS_SESSION.SET_CONTEXT('hz', 'dnb_used', 'Y');
391 END;
392
393
394 function get_cust_address(v_address_id number) return varchar2 is
395
396 v_return varchar2(4000);
397
398 cursor c is
399 select
400 c.ADDRESS1 || DECODE(c.ADDRESS1, NULL, NULL, ' ') || c.ADDRESS2
401 || DECODE(c.ADDRESS2, NULL, NULL, ' ') || c.ADDRESS3 ||
402 DECODE(c.ADDRESS3, NULL, NULL, ' ') || c.ADDRESS4 ||
403 DECODE(c.ADDRESS1, NULL, NULL, ', ') || c.CITY || DECODE(c.CITY,
404 NULL, NULL, ', ') || c.STATE || DECODE(c.STATE, NULL, NULL, ' ') ||
405 c.POSTAL_CODE || DECODE(d.TERRITORY_SHORT_NAME, NULL, NULL, ', ')
406 || d.TERRITORY_SHORT_NAME
407 from hz_cust_acct_sites_all a, hz_party_sites b, hz_locations c,fnd_territories_vl d
408 where a.party_site_id =b.party_site_id
409 and b.location_id = c.location_id
410 and d.territory_code = c.country
411 and a.cust_acct_site_id = v_address_id;
412
413 begin
414 if v_address_id is not null then
415 open c;
416 fetch c into v_return;
417 close c;
418 end if;
419
420 return v_return;
421 end;
422
423 function get_cust_name(v_cust_id number) return varchar2 is
424
425 v_return hz_parties.party_name%type;
426
427 cursor c is
428 select b.party_name
429 from hz_cust_accounts a, hz_parties b
430 where a.cust_account_id = v_cust_id
431 and a.party_id = b.party_id;
432
433 begin
434 if v_cust_id is not null then
435 open c;
436 fetch c into v_return;
437 close c;
438 end if;
439
440 return v_return;
441 end;
442
443 function get_cust_contact_name(v_contact_id number) return varchar2 is
444
445 v_return hz_parties.party_name%type;
446
447 cursor c is
448 select b.person_first_name||' '||b.person_last_name
449 from hz_cust_account_roles a,
450 hz_parties b,
451 hz_relationships c
452 where a.cust_account_role_id = v_contact_id
453 and c.party_id =a.party_id
454 and c.subject_id = b.party_id
455 and c.subject_table_name = 'HZ_PARTIES'
456 and c.object_table_name = 'HZ_PARTIES'
457 and c.directional_flag = 'F';
458
459 begin
460 if v_contact_id is not null then
461 open c;
462 fetch c into v_return;
463 close c;
464 end if;
465
466 return v_return;
467 end;
468
469 function get_party_name(v_party_id number) return varchar2 is
470
471 v_return hz_parties.party_name%type;
472
473 cursor c is
474 select b.party_name
475 from hz_parties b
476 where b.party_id = v_party_id;
477
478 begin
479 if v_party_id is not null then
480 open c;
481 fetch c into v_return;
482 close c;
483 end if;
484
485 return v_return;
486 end;
487
488 procedure check_mandatory_str_col
489 -- Control mandatory column for varchar2 type
490 -- create update flag belongs to [C (creation) ,U (update)]
491 -- Column name
492 -- Column Value
493 -- Allow Null in creation mode flag
494 -- Allow Null in update mode flag
495 -- Control Status
496 ( create_update_flag IN VARCHAR2,
497 p_col_name IN VARCHAR2,
498 p_col_val IN VARCHAR2,
499 p_miss_allowed_in_c IN BOOLEAN,
500 p_miss_allowed_in_u IN BOOLEAN,
501 x_return_status IN OUT NOCOPY VARCHAR2)
502 IS
503 BEGIN
504 IF (p_col_val IS NULL) THEN
505 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
506 fnd_message.set_token('COLUMN', p_col_name);
507 fnd_msg_pub.add;
508 x_return_status := fnd_api.g_ret_sts_error;
509 RETURN;
510 END IF;
511
512 IF (create_update_flag = 'C') THEN
513 IF ((NOT p_miss_allowed_in_c) AND
514 p_col_val = fnd_api.G_MISS_CHAR )
515 THEN
516 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
517 fnd_message.set_token('COLUMN', p_col_name);
518 fnd_msg_pub.add;
519 x_return_status := fnd_api.g_ret_sts_error;
520 END IF;
521 ELSE
522 IF ((NOT p_miss_allowed_in_u) AND
523 p_col_val = fnd_api.G_MISS_CHAR )
524 THEN
525 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
526 fnd_message.set_token('COLUMN', p_col_name);
527 fnd_msg_pub.add;
528 x_return_status := fnd_api.g_ret_sts_error;
529 END IF;
530 END IF;
531 END check_mandatory_str_col;
532
533 procedure check_mandatory_date_col
534 -- Control mandatory column for date type
535 -- create update flag belongs to [C (creation) ,U (update)]
536 -- Column name
537 -- Column Value
538 -- Allow Null in creation mode flag
539 -- Allow Null in update mode flag
540 -- Control Status
541 ( create_update_flag IN VARCHAR2,
542 p_col_name IN VARCHAR2,
543 p_col_val IN DATE,
544 p_miss_allowed_in_c IN BOOLEAN,
545 p_miss_allowed_in_u IN BOOLEAN,
546 x_return_status IN OUT NOCOPY VARCHAR2)
547 IS
548 BEGIN
549 IF (p_col_val IS NULL) THEN
550 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
551 fnd_message.set_token('COLUMN', p_col_name);
552 fnd_msg_pub.add;
553 x_return_status := fnd_api.g_ret_sts_error;
554 RETURN;
555 END IF;
556
557 IF (create_update_flag = 'C') THEN
558 IF ((NOT p_miss_allowed_in_c) AND
559 p_col_val = fnd_api.G_MISS_DATE )
560 THEN
561 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
562 fnd_message.set_token('COLUMN', p_col_name);
563 fnd_msg_pub.add;
564 x_return_status := fnd_api.g_ret_sts_error;
565 END IF;
566 ELSE
567 IF ((NOT p_miss_allowed_in_u) AND
568 p_col_val = fnd_api.G_MISS_DATE )
569 THEN
570 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
571 fnd_message.set_token('COLUMN', p_col_name);
572 fnd_msg_pub.add;
573 x_return_status := fnd_api.g_ret_sts_error;
574 END IF;
575 END IF;
576 END check_mandatory_date_col;
577
578 procedure check_mandatory_num_col
579 -- Control mandatory column for number type
580 -- create update flag belongs to [C (creation) ,U (update)]
581 -- Column name
582 -- Column Value
583 -- Allow Null in creation mode flag
584 -- Allow Null in update mode flag
585 -- Control Status
586 ( create_update_flag IN VARCHAR2,
587 p_col_name IN VARCHAR2,
588 p_col_val IN NUMBER,
589 p_miss_allowed_in_c IN BOOLEAN,
590 p_miss_allowed_in_u IN BOOLEAN,
591 x_return_status IN OUT NOCOPY VARCHAR2)
592 IS
593 BEGIN
594 IF (p_col_val IS NULL) THEN
595 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
596 fnd_message.set_token('COLUMN', p_col_name);
597 fnd_msg_pub.add;
598 x_return_status := fnd_api.g_ret_sts_error;
599 RETURN;
600 END IF;
601
602 IF (create_update_flag = 'C') THEN
603 IF ((NOT p_miss_allowed_in_c) AND
604 p_col_val = fnd_api.G_MISS_NUM )
605 THEN
606 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
607 fnd_message.set_token('COLUMN', p_col_name);
608 fnd_msg_pub.add;
609 x_return_status := fnd_api.g_ret_sts_error;
610 END IF;
611 ELSE
612 IF ((NOT p_miss_allowed_in_u) AND
613 p_col_val = fnd_api.G_MISS_NUM )
614 THEN
615 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
616 fnd_message.set_token('COLUMN', p_col_name);
617 fnd_msg_pub.add;
618 x_return_status := fnd_api.g_ret_sts_error;
619 END IF;
620 END IF;
621 END check_mandatory_num_col;
622
623 FUNCTION compare(
624 date1 DATE,
625 date2 DATE) RETURN NUMBER
626 IS
627 ldate1 date;
628 ldate2 date;
629 BEGIN
630 ldate1 := trunc(date1);
631 ldate2 := trunc(date2);
632 IF (ldate1 IS NULL AND ldate2 IS NULL) THEN
633 RETURN 0;
634 ELSIF (ldate2 IS NULL) THEN
635 RETURN -1;
636 ELSIF (ldate1 IS NULL) THEN
637 RETURN 1;
638 ELSIF ( ldate1 = ldate2 ) THEN
639 RETURN 0;
640 ELSIF ( ldate1 > ldate2 ) THEN
641 RETURN 1;
642 ELSE
643 RETURN -1;
644 END IF;
645 END compare;
646
647 --
648 -- FUNCTION time_compare
649 --
650 -- DESCRIPTION
651 -- Time-sensitive version of compare
655 --
652 -- NULL indicates infinite date
653 --
654 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
656 -- ARGUMENTS
657 -- IN:
658 -- datetime1 Date 1.
659 -- datetime2 Date 2.
660 --
661 -- NOTES
662 --
663 -- MODIFICATION HISTORY
664 --
665 -- 13-DEC-2001 Joe del Callar Bug 2145637: Created.
666 --
667 FUNCTION time_compare(datetime1 IN DATE, datetime2 IN DATE) RETURN NUMBER IS
668 BEGIN
669 IF datetime1 IS NULL AND datetime2 IS NULL THEN
670 RETURN 0;
671 ELSIF datetime2 IS NULL THEN
672 RETURN -1;
673 ELSIF datetime1 IS NULL THEN
674 RETURN 1;
675 ELSIF datetime1 = datetime2 THEN
676 RETURN 0;
677 ELSIF datetime1 > datetime2 THEN
678 RETURN 1;
679 ELSE
680 RETURN -1;
681 END IF;
682 END time_compare;
683
684
685 -- NULL indicates infinite
686 FUNCTION is_between
687 ( datex DATE,
688 date1 DATE,
689 date2 DATE) RETURN BOOLEAN
690 IS
691 BEGIN
692 IF compare(datex, date1) >= 0 AND
693 compare(date2, datex) >=0 THEN
694 RETURN TRUE;
695 ELSE
696 RETURN FALSE;
697 END IF;
698 END is_between;
699
700 --
701 -- FUNCTION is_time_between
702 --
703 -- DESCRIPTION
704 -- Returns 'Y' if period datex is between datetime1 and datetime2 with time
705 -- considered.
706 -- 'N' otherwise
707 -- NULL indicates infinite date
708 --
709 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
710 --
711 -- ARGUMENTS
712 -- IN:
713 -- datetimex Date being tested for "betweenness"
714 -- datetime1 Start date/time of period.
715 -- datetime2 End date/time of period.
716 --
717 -- NOTES
718 --
719 -- MODIFICATION HISTORY
720 --
721 -- 13-DEC-2001 Joe del Callar Bug 2145637: Created.
722 --
723 FUNCTION is_time_between (
724 datetimex IN DATE,
725 datetime1 IN DATE,
726 datetime2 IN DATE
727 ) RETURN BOOLEAN IS
728 BEGIN
729 IF time_compare(datetimex, datetime1) >= 0
730 AND time_compare(datetime2, datetimex) >=0
731 THEN
732 RETURN TRUE;
733 ELSE
734 RETURN FALSE;
735 END IF;
736 END is_time_between;
737
738 -- NULL indicates infinite
739 FUNCTION is_overlap
740 -- Returns 'Y' if period [s1,e1] overlaps [s2,e2]
741 -- 'N' otherwise
742 -- NULL indicates infinite for END dates
743 (s1 DATE,
744 e1 DATE,
745 s2 DATE,
746 e2 DATE)
747 RETURN VARCHAR2
748 IS
749 BEGIN
750 IF ( is_between(s1, s2, e2) ) OR ( is_between(s2, s1, e1) ) THEN
751 RETURN 'Y';
752 ELSE
753 RETURN 'N';
754 END IF;
755 END is_overlap;
756
757 --
758 -- FUNCTION is_time_overlap
759 --
760 -- DESCRIPTION
761 -- Returns 'Y' if period [s1,e1] overlaps [s2,e2] with time factored in
762 -- 'N' otherwise
763 -- NULL indicates infinite for END dates
764 --
765 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
766 --
767 -- ARGUMENTS
768 -- IN:
769 -- s1 Start date/time of period 1.
770 -- e1 End date/time of period 1.
771 -- s2 Start date/time of period 1.
772 -- e2 End date/time of period 1.
773 --
774 -- NOTES
775 --
776 -- MODIFICATION HISTORY
777 --
778 -- 13-DEC-2001 Joe del Callar Bug 2145637: Created.
779 --
780
781 FUNCTION is_time_overlap (
782 s1 IN DATE,
783 e1 IN DATE,
784 s2 IN DATE,
785 e2 IN DATE
786 ) RETURN VARCHAR2 IS
787 BEGIN
788 IF (is_time_between(s1, s2, e2)) OR (is_time_between(s2, s1, e1)) THEN
789 RETURN 'Y';
790 ELSE
791 RETURN 'N';
792 END IF;
793 END is_time_overlap;
794
795
796 -- Return varchar2 with a set of characters non desired.
797 -- Please pass the input in uppercase as this assumes so.
798 -- This is currently called just from HZ_FUZZY_PUB, if you
799 -- intend to call it from any other program, make sure this
800 -- logic works for that.
801 FUNCTION cleanse
802 (str IN varchar2
803 )
804 RETURN varchar2
805 IS
806 str2 varchar2(400) := str;
807 str3 varchar2(400);
808
809 BEGIN
810
811 -- if the input is null, return null as the processing has no
812 -- impact on that
813 if str is null then
814 return str;
815 end if;
816
817 -- Step 1. Replace any two or more consecutive same
818 -- letters by single letter
819
820 -- get the input string in a temporary string
821 str3 := str2;
822
823 -- loop from first letter to last but one letter
824 for i in 1..length(str2)-1
825 loop
826 -- if two consecutive letters match, then replace two such letter
827 -- by one letter from the temporary string
828 if substr(str2,i,1) = substr(str2,i+1,1) then
829 str3 := replace(str3, substr(str2,i,1)||substr(str2,i+1,1), substr(str2,i,1));
830 end if;
831 end loop;
832
833 str2 := str3;
834
835 -- Step 2. Replace Vowels only that occur inside
836 -- First we should build a temporary string
837 -- which would be the original string str2
838 -- stripped off the first letter
839 str3 := substr(str2, 2);
840
841 -- Now call replace to remove all occurrences
842 -- of each vowel
843 str3 := replace(str3, 'A', '');
844 str3 := replace(str3, 'E', '');
845 str3 := replace(str3, 'I', '');
846 str3 := replace(str3, 'O', '');
847 str3 := replace(str3, 'U', '');
848
849 -- Now we have to build str2 back with
850 -- first letter of str2 and appending str3
851 str2 := substr(str2, 1, 1)||str3;
852
853 -- return str2 which is the final clean word
854 return rtrim(str2);
855
856 END cleanse;
857
858 /* SSM SST Integration And Extension.
859 * Predicates will not be attached to non-profile tables.(Concept of select / de-select data sources
860 * for these entities is done away with)
861
862 -- bug fix 2969850
863 FUNCTION getNotSelectedDataSource(
864 p_data_source IN VARCHAR2
865 ) RETURN VARCHAR2 IS
866
867 CURSOR c_content_source_type IS
868 SELECT lookup_code FROM ar_lookups
869 WHERE lookup_type = 'CONTENT_SOURCE_TYPE' and
870 ENABLED_FLAG = 'Y' AND
871 TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE));
872
873 l_not_data_source VARCHAR2(4000);
874 l_content_source_type VARCHAR2(100);
875 l_len NUMBER;
876 BEGIN
877 l_not_data_source := '';
878
879 open c_content_source_type;
880 LOOP
881 FETCH c_content_source_type INTO l_content_source_type;
882 EXIT WHEN c_content_source_type%NOTFOUND;
883
884 IF ( instrb(p_data_source, '''' || l_content_source_type || '''') = 0 ) THEN
885
886 l_not_data_source := l_not_data_source||''''||l_content_source_type||''',';
887
888 END IF;
889
890 END LOOP;
891
892 IF l_not_data_source IS NOT NULL THEN
893 l_len := LENGTHB(l_not_data_source);
894 IF l_len > 1 THEN
895 l_not_data_source := SUBSTRB(l_not_data_source,1,l_len-1);
896 END IF;
897 END IF;
898
899
900 RETURN l_not_data_source;
901 END getNotSelectedDataSource;
902 */
903
904 /*===========================================================================+
905 | PROCEDURE |
906 | enable_health_care_security |
907 | |
908 | DESCRIPTION |
909 | Enables the VPD security for healthcare module. |
910 | |
911 | SCOPE - PUBLIC |
915 | ARGUMENTS : IN: |
912 | |
913 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
914 | |
916 | OUT: |
917 | IN/ OUT: |
918 | |
919 | |
920 | |
921 | NOTES |
922 | |
923 | MODIFICATION HISTORY |
924 | Ramesh Ch 31-OCT-03 Created |
925 | |
926 +===========================================================================*/
927
928 PROCEDURE enable_health_care_security IS
929 BEGIN
930 DBMS_SESSION.SET_CONTEXT('hz','hcare_used', 'Y');
931 END enable_health_care_security;
932
933 /*===========================================================================+
934 | PROCEDURE |
935 | disable_health_care_security |
936 | |
937 | DESCRIPTION |
938 | Disables the VPD security for healthcare module. |
939 | |
940 | SCOPE - PUBLIC |
941 | |
942 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
943 | |
944 | ARGUMENTS : IN: |
945 | OUT: |
946 | IN/ OUT: |
947 | |
948 | |
949 | |
950 | NOTES |
951 | |
952 | MODIFICATION HISTORY |
953 | Ramesh Ch 31-OCT-03 Created |
954 | |
955 +===========================================================================*/
956
957 PROCEDURE disable_health_care_security IS
958 BEGIN
959 DBMS_SESSION.SET_CONTEXT('hz', 'hcare_used', 'N');
960 END disable_health_care_security;
961
962 /*===========================================================================+
963 | PROCEDURE |
964 | add_hcare_policy_function |
965 | |
966 | DESCRIPTION |
967 | Adds the hcare_created_by_module_sec policy function to |
968 | HZ_PARTIES,HZ_PERSON_PROFILES,HZ_LOCATIONS,HZ_PARTY_SITES, |
969 | HZ_PARTY_SITE_USES,HZ_CITIZENSHIP,HZ_PERSON_LANGUAGE and |
970 | HZ_CONTACT_POINTS. |
971 | |
972 | SCOPE - PUBLIC |
973 | |
974 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
975 | |
976 | ARGUMENTS : IN: |
977 | OUT: |
978 | IN/ OUT: |
979 | |
980 | |
981 | |
982 | NOTES |
983 | |
984 | MODIFICATION HISTORY |
985 | Ramesh Ch 31-OCT-03 Created |
986 | Ramesh Ch 08-NOV-03 Commented the policy function for |
987 | HZ_CITIZENSHIP entity. |
988 +===========================================================================*/
989
990 PROCEDURE add_hcare_policy_function
991 IS
992
993 l_ar_schema VARCHAR2(30);
994 l_apps_schema VARCHAR2(30);
995 l_aol_schema VARCHAR2(30);
996 l_apps_mls_schema VARCHAR2(30);
997
998 l_status VARCHAR2(30);
999 l_industry VARCHAR2(30);
1000 l_return_value BOOLEAN;
1001
1002 BEGIN
1003
1004 arp_util.debug('add_hcare_policy_function (+) ');
1005
1006 --Get ar and apps schema name
1010 IF NOT l_return_value THEN
1007 l_return_value := fnd_installation.get_app_info(
1008 'AR', l_status, l_industry, l_ar_schema);
1009
1011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1012 END IF;
1013
1014 l_return_value := fnd_installation.get_app_info(
1015 'FND', l_status, l_industry, l_aol_schema);
1016
1017 IF NOT l_return_value THEN
1018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1019 END IF;
1020
1021 system.ad_apps_private.get_apps_schema_name(
1022 1, l_aol_schema, l_apps_schema, l_apps_mls_schema);
1023
1024 --Add policy functions
1025 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_PARTIES', 'hcare_created_by_module_sec', l_apps_schema, 'hz_common_pub.hcare_created_by_module_sec');
1026 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_PERSON_PROFILES', 'hcare_created_by_module_sec', l_apps_schema, 'hz_common_pub.hcare_created_by_module_sec');
1027 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_LOCATIONS', 'hcare_created_by_module_sec', l_apps_schema, 'hz_common_pub.hcare_created_by_module_sec');
1028 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_PARTY_SITES', 'hcare_created_by_module_sec', l_apps_schema, 'hz_common_pub.hcare_created_by_module_sec');
1029 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_PARTY_SITE_USES', 'hcare_created_by_module_sec', l_apps_schema, 'hz_common_pub.hcare_created_by_module_sec');
1030 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_CITIZENSHIP', 'hcare_created_by_module_sec', l_apps_schema, 'hz_common_pub.hcare_created_by_module_sec');
1031 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_PERSON_LANGUAGE','hcare_created_by_module_sec', l_apps_schema, 'hz_common_pub.hcare_created_by_module_sec');
1032 FND_ACCESS_CONTROL_UTIL.ADD_POLICY(l_ar_schema, 'HZ_CONTACT_POINTS', 'hcare_created_by_module_sec', l_apps_schema, 'hz_common_pub.hcare_created_by_module_sec');
1033
1034 arp_util.debug('add_hcare_policy_function (-) ');
1035
1036 END add_hcare_policy_function;
1037
1038 /*===========================================================================+
1039 | PROCEDURE |
1040 | drop_hcare_policy_function |
1041 | |
1042 | DESCRIPTION |
1043 | Drops the hcare_created_by_module_sec policy function which |
1044 | was already added to HZ_PARTIES,HZ_PERSON_PROFILES, |
1045 | HZ_LOCATIONS,HZ_PARTY_SITES,HZ_PARTY_SITE_USES,HZ_CITIZENSHIP, |
1046 | HZ_PERSON_LANGUAGE and HZ_CONTACT_POINTS. |
1047 | |
1048 | |
1049 | SCOPE - PUBLIC |
1050 | |
1051 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1052 | |
1053 | ARGUMENTS : IN: |
1054 | OUT: |
1055 | IN/ OUT: |
1056 | |
1057 | |
1058 | |
1059 | NOTES |
1060 | |
1061 | MODIFICATION HISTORY |
1062 | Ramesh Ch 31-OCT-03 Created |
1063 | Ramesh Ch 08-NOV-03 Commented the policy function for |
1064 | HZ_CITIZENSHIP entity. |
1065 | |
1066 +===========================================================================*/
1067
1068 PROCEDURE drop_hcare_policy_function
1069 IS
1070
1071 l_ar_schema VARCHAR2(30);
1072 l_apps_schema VARCHAR2(30);
1073 l_aol_schema VARCHAR2(30);
1074 l_apps_mls_schema VARCHAR2(30);
1075
1076 l_status VARCHAR2(30);
1077 l_industry VARCHAR2(30);
1078 l_return_value BOOLEAN;
1079
1080 BEGIN
1081
1082 arp_util.debug('drop_hcare_policy_function (+) ');
1083
1084 --Get ar and apps schema name
1085 l_return_value := fnd_installation.get_app_info(
1086 'AR', l_status, l_industry, l_ar_schema);
1087
1088 IF NOT l_return_value THEN
1089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1090 END IF;
1091
1092 l_return_value := fnd_installation.get_app_info(
1093 'FND', l_status, l_industry, l_aol_schema);
1094
1095 IF NOT l_return_value THEN
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 END IF;
1098
1099 system.ad_apps_private.get_apps_schema_name(
1100 1, l_aol_schema, l_apps_schema, l_apps_mls_schema);
1101
1102 --Drop policy functions
1103 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_PARTIES', 'hcare_created_by_module_sec');
1104 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_PERSON_PROFILES', 'hcare_created_by_module_sec');
1105 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_LOCATIONS', 'hcare_created_by_module_sec');
1106 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_PARTY_SITES', 'hcare_created_by_module_sec');
1107 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_PARTY_SITE_USES', 'hcare_created_by_module_sec');
1108 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_CITIZENSHIP', 'hcare_created_by_module_sec');
1109 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_PERSON_LANGUAGE','hcare_created_by_module_sec');
1110 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_CONTACT_POINTS', 'hcare_created_by_module_sec');
1111
1112 arp_util.debug('drop_hcare_policy_function (-) ');
1113
1114 END drop_hcare_policy_function;
1115
1116 /*===========================================================================+
1117 | FUNCTION |
1118 | hcare_created_by_module_sec |
1119 | |
1120 | DESCRIPTION |
1121 | Policy Function |
1122 | |
1123 | SCOPE - PUBLIC |
1124 | |
1125 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1126 | |
1127 | ARGUMENTS : IN: |
1128 | p_object_schema: Entity Schema Name. |
1129 | p_object_name : Entity Name. |
1130 | OUT: |
1131 | IN/ OUT: |
1132 | |
1133 | RETURNS : Dynamic where clause based on the context value. |
1134 | |
1135 | NOTES |
1136 | |
1137 | MODIFICATION HISTORY |
1138 | Ramesh Ch 31-OCT-03 Created |
1139 | |
1140 +===========================================================================*/
1141
1142 FUNCTION hcare_created_by_module_sec(p_object_schema IN VARCHAR2,p_object_name IN VARCHAR2)
1143 RETURN VARCHAR2
1144 IS
1145 l_context VARCHAR2(10);
1146 BEGIN
1147 l_context := NVL(SYS_CONTEXT('hz', 'hcare_used'),'Y');
1148
1149 IF l_context <> 'N' THEN
1150 return 'CREATED_BY_MODULE<>''HTBPERSON''';
1151 ELSE RETURN NULL;
1152 END IF;
1153 END hcare_created_by_module_sec;
1154
1155
1156 END;