[Home] [Help]
PACKAGE BODY: APPS.HZ_COMMON_PUB
Source
1 PACKAGE BODY hz_common_pub AS
2 /*$Header: ARHCOMMB.pls 120.15 2006/02/13 06:33:49 vravicha 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 |
66 | |
67 | NOTES |
68 | |
69 | MODIFICATION HISTORY |
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,
209 p_column_value IN VARCHAR2,
210 x_return_status IN OUT NOCOPY VARCHAR2)
211 IS
212 CURSOR c1
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 |
288 | |
289 | ARGUMENTS : IN: |
290 | OUT: |
291 | IN/ 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 l_predicate := 'NVL(actual_content_source,content_source_type) = ''SST''';
339
340 -- SSM SST Integration and Extension
341 -- Concept of select/de-select data sources is done away with for non-profile entities.
342 /*ELSE
343 IF p_object_name NOT IN ('HZ_CODE_ASSIGNMENTS', 'HZ_ORGANIZATION_INDICATORS') THEN
344
345 -- Bug 2444678: Removed caching.
346
347 -- IF p_object_name <> NVL(G_ENTITY_NAME, 'null') THEN
351 -- END IF;
348 l_data_sources := HZ_MIXNM_UTILITY.getSelectedDataSources(p_object_name, l_entity_attr_id);
349 l_not_data_source := getNotSelectedDataSource (l_data_sources);
350 -- G_ENTITY_NAME := p_object_name;
352 */
353 /* Bug 2780113- fix for DNB hierarchy project */
354 /*
355 IF p_object_name = 'HZ_PARTY_SITES' THEN
356 l_predicate := 'actual_content_source NOT IN ('||l_not_data_source||')';
357 ELSE
358 l_predicate := 'NVL(actual_content_source,content_source_type) NOT IN ('||l_not_data_source||')';
359 END IF;
360
361 ELSE
362 l_predicate := 'content_source_type = ''USER_ENTERED''';
363 END IF;
364 */
365 END IF;
366 END IF;
367
368 /*
369 l_predicate := '((SYS_CONTEXT(''hz'', ''dnb_used'')=''Y'')' ||
370 'OR ' ||
371 '((SYS_CONTEXT(''hz'', ''dnb_used'') IS NULL OR ' ||
372 ' SYS_CONTEXT(''hz'', ''dnb_used'')=''N'') and ' ||
373 'content_source_type=''USER_ENTERED''))';
374 */
375
376 RETURN l_predicate;
377
378 END;
379
380
381 procedure enable_cont_source_security IS
382 BEGIN
383 DBMS_SESSION.SET_CONTEXT('hz', 'dnb_used', 'N');
384 END;
385
386 procedure disable_cont_source_security IS
387 BEGIN
388 DBMS_SESSION.SET_CONTEXT('hz', 'dnb_used', 'Y');
389 END;
390
391
392 function get_cust_address(v_address_id number) return varchar2 is
393
394 v_return varchar2(4000);
395
396 cursor c is
397 select
398 c.ADDRESS1 || DECODE(c.ADDRESS1, NULL, NULL, ' ') || c.ADDRESS2
399 || DECODE(c.ADDRESS2, NULL, NULL, ' ') || c.ADDRESS3 ||
400 DECODE(c.ADDRESS3, NULL, NULL, ' ') || c.ADDRESS4 ||
401 DECODE(c.ADDRESS1, NULL, NULL, ', ') || c.CITY || DECODE(c.CITY,
402 NULL, NULL, ', ') || c.STATE || DECODE(c.STATE, NULL, NULL, ' ') ||
403 c.POSTAL_CODE || DECODE(d.TERRITORY_SHORT_NAME, NULL, NULL, ', ')
404 || d.TERRITORY_SHORT_NAME
405 from hz_cust_acct_sites_all a, hz_party_sites b, hz_locations c,fnd_territories_vl d
406 where a.party_site_id =b.party_site_id
407 and b.location_id = c.location_id
408 and d.territory_code = c.country
409 and a.cust_acct_site_id = v_address_id;
410
411 begin
412 if v_address_id is not null then
413 open c;
414 fetch c into v_return;
415 close c;
416 end if;
417
418 return v_return;
419 end;
420
421 function get_cust_name(v_cust_id number) return varchar2 is
422
423 v_return hz_parties.party_name%type;
424
425 cursor c is
426 select b.party_name
427 from hz_cust_accounts a, hz_parties b
428 where a.cust_account_id = v_cust_id
429 and a.party_id = b.party_id;
430
431 begin
432 if v_cust_id is not null then
433 open c;
434 fetch c into v_return;
435 close c;
436 end if;
437
438 return v_return;
439 end;
440
441 function get_cust_contact_name(v_contact_id number) return varchar2 is
442
443 v_return hz_parties.party_name%type;
444
445 cursor c is
446 select b.person_first_name||' '||b.person_last_name
447 from hz_cust_account_roles a,
448 hz_parties b,
449 hz_relationships c
450 where a.cust_account_role_id = v_contact_id
451 and c.party_id =a.party_id
452 and c.subject_id = b.party_id
453 and c.subject_table_name = 'HZ_PARTIES'
454 and c.object_table_name = 'HZ_PARTIES'
455 and c.directional_flag = 'F';
456
457 begin
458 if v_contact_id is not null then
459 open c;
460 fetch c into v_return;
461 close c;
462 end if;
463
464 return v_return;
465 end;
466
467 function get_party_name(v_party_id number) return varchar2 is
468
469 v_return hz_parties.party_name%type;
470
471 cursor c is
472 select b.party_name
473 from hz_parties b
474 where b.party_id = v_party_id;
475
476 begin
477 if v_party_id is not null then
478 open c;
479 fetch c into v_return;
480 close c;
481 end if;
482
483 return v_return;
484 end;
485
486 procedure check_mandatory_str_col
487 -- Control mandatory column for varchar2 type
488 -- create update flag belongs to [C (creation) ,U (update)]
489 -- Column name
490 -- Column Value
491 -- Allow Null in creation mode flag
492 -- Allow Null in update mode flag
493 -- Control Status
494 ( create_update_flag IN VARCHAR2,
495 p_col_name IN VARCHAR2,
496 p_col_val IN VARCHAR2,
497 p_miss_allowed_in_c IN BOOLEAN,
498 p_miss_allowed_in_u IN BOOLEAN,
499 x_return_status IN OUT NOCOPY VARCHAR2)
500 IS
501 BEGIN
502 IF (p_col_val IS NULL) THEN
503 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
504 fnd_message.set_token('COLUMN', p_col_name);
505 fnd_msg_pub.add;
509
506 x_return_status := fnd_api.g_ret_sts_error;
507 RETURN;
508 END IF;
510 IF (create_update_flag = 'C') THEN
511 IF ((NOT p_miss_allowed_in_c) AND
512 p_col_val = fnd_api.G_MISS_CHAR )
513 THEN
514 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
515 fnd_message.set_token('COLUMN', p_col_name);
516 fnd_msg_pub.add;
517 x_return_status := fnd_api.g_ret_sts_error;
518 END IF;
519 ELSE
520 IF ((NOT p_miss_allowed_in_u) AND
521 p_col_val = fnd_api.G_MISS_CHAR )
522 THEN
523 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
524 fnd_message.set_token('COLUMN', p_col_name);
525 fnd_msg_pub.add;
526 x_return_status := fnd_api.g_ret_sts_error;
527 END IF;
528 END IF;
529 END check_mandatory_str_col;
530
531 procedure check_mandatory_date_col
532 -- Control mandatory column for date type
533 -- create update flag belongs to [C (creation) ,U (update)]
534 -- Column name
535 -- Column Value
536 -- Allow Null in creation mode flag
537 -- Allow Null in update mode flag
538 -- Control Status
539 ( create_update_flag IN VARCHAR2,
540 p_col_name IN VARCHAR2,
541 p_col_val IN DATE,
542 p_miss_allowed_in_c IN BOOLEAN,
543 p_miss_allowed_in_u IN BOOLEAN,
544 x_return_status IN OUT NOCOPY VARCHAR2)
545 IS
546 BEGIN
547 IF (p_col_val IS NULL) THEN
548 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
549 fnd_message.set_token('COLUMN', p_col_name);
550 fnd_msg_pub.add;
551 x_return_status := fnd_api.g_ret_sts_error;
552 RETURN;
553 END IF;
554
555 IF (create_update_flag = 'C') THEN
556 IF ((NOT p_miss_allowed_in_c) AND
557 p_col_val = fnd_api.G_MISS_DATE )
558 THEN
559 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
560 fnd_message.set_token('COLUMN', p_col_name);
561 fnd_msg_pub.add;
562 x_return_status := fnd_api.g_ret_sts_error;
563 END IF;
564 ELSE
565 IF ((NOT p_miss_allowed_in_u) AND
566 p_col_val = fnd_api.G_MISS_DATE )
567 THEN
568 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
569 fnd_message.set_token('COLUMN', p_col_name);
570 fnd_msg_pub.add;
571 x_return_status := fnd_api.g_ret_sts_error;
572 END IF;
573 END IF;
574 END check_mandatory_date_col;
575
576 procedure check_mandatory_num_col
577 -- Control mandatory column for number type
578 -- create update flag belongs to [C (creation) ,U (update)]
579 -- Column name
580 -- Column Value
581 -- Allow Null in creation mode flag
582 -- Allow Null in update mode flag
583 -- Control Status
584 ( create_update_flag IN VARCHAR2,
585 p_col_name IN VARCHAR2,
586 p_col_val IN NUMBER,
587 p_miss_allowed_in_c IN BOOLEAN,
588 p_miss_allowed_in_u IN BOOLEAN,
589 x_return_status IN OUT NOCOPY VARCHAR2)
590 IS
591 BEGIN
592 IF (p_col_val IS NULL) THEN
593 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
594 fnd_message.set_token('COLUMN', p_col_name);
595 fnd_msg_pub.add;
596 x_return_status := fnd_api.g_ret_sts_error;
597 RETURN;
598 END IF;
599
600 IF (create_update_flag = 'C') THEN
601 IF ((NOT p_miss_allowed_in_c) AND
602 p_col_val = fnd_api.G_MISS_NUM )
603 THEN
604 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
605 fnd_message.set_token('COLUMN', p_col_name);
606 fnd_msg_pub.add;
607 x_return_status := fnd_api.g_ret_sts_error;
608 END IF;
609 ELSE
610 IF ((NOT p_miss_allowed_in_u) AND
611 p_col_val = fnd_api.G_MISS_NUM )
612 THEN
613 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
614 fnd_message.set_token('COLUMN', p_col_name);
615 fnd_msg_pub.add;
616 x_return_status := fnd_api.g_ret_sts_error;
617 END IF;
618 END IF;
619 END check_mandatory_num_col;
620
621 FUNCTION compare(
622 date1 DATE,
623 date2 DATE) RETURN NUMBER
624 IS
625 ldate1 date;
626 ldate2 date;
627 BEGIN
628 ldate1 := trunc(date1);
629 ldate2 := trunc(date2);
633 RETURN -1;
630 IF (ldate1 IS NULL AND ldate2 IS NULL) THEN
631 RETURN 0;
632 ELSIF (ldate2 IS NULL) THEN
634 ELSIF (ldate1 IS NULL) THEN
635 RETURN 1;
636 ELSIF ( ldate1 = ldate2 ) THEN
637 RETURN 0;
638 ELSIF ( ldate1 > ldate2 ) THEN
639 RETURN 1;
640 ELSE
641 RETURN -1;
642 END IF;
643 END compare;
644
645 --
646 -- FUNCTION time_compare
647 --
648 -- DESCRIPTION
649 -- Time-sensitive version of compare
650 -- NULL indicates infinite date
651 --
652 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
653 --
654 -- ARGUMENTS
655 -- IN:
656 -- datetime1 Date 1.
657 -- datetime2 Date 2.
658 --
659 -- NOTES
660 --
661 -- MODIFICATION HISTORY
662 --
663 -- 13-DEC-2001 Joe del Callar Bug 2145637: Created.
664 --
665 FUNCTION time_compare(datetime1 IN DATE, datetime2 IN DATE) RETURN NUMBER IS
666 BEGIN
667 IF datetime1 IS NULL AND datetime2 IS NULL THEN
668 RETURN 0;
669 ELSIF datetime2 IS NULL THEN
670 RETURN -1;
671 ELSIF datetime1 IS NULL THEN
672 RETURN 1;
673 ELSIF datetime1 = datetime2 THEN
674 RETURN 0;
675 ELSIF datetime1 > datetime2 THEN
676 RETURN 1;
677 ELSE
678 RETURN -1;
679 END IF;
680 END time_compare;
681
682
683 -- NULL indicates infinite
684 FUNCTION is_between
685 ( datex DATE,
686 date1 DATE,
687 date2 DATE) RETURN BOOLEAN
688 IS
689 BEGIN
690 IF compare(datex, date1) >= 0 AND
691 compare(date2, datex) >=0 THEN
692 RETURN TRUE;
693 ELSE
694 RETURN FALSE;
695 END IF;
696 END is_between;
697
698 --
699 -- FUNCTION is_time_between
700 --
701 -- DESCRIPTION
702 -- Returns 'Y' if period datex is between datetime1 and datetime2 with time
703 -- considered.
704 -- 'N' otherwise
705 -- NULL indicates infinite date
706 --
707 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
708 --
709 -- ARGUMENTS
710 -- IN:
711 -- datetimex Date being tested for "betweenness"
712 -- datetime1 Start date/time of period.
713 -- datetime2 End date/time of period.
714 --
715 -- NOTES
716 --
717 -- MODIFICATION HISTORY
718 --
719 -- 13-DEC-2001 Joe del Callar Bug 2145637: Created.
720 --
721 FUNCTION is_time_between (
722 datetimex IN DATE,
723 datetime1 IN DATE,
724 datetime2 IN DATE
725 ) RETURN BOOLEAN IS
726 BEGIN
727 IF time_compare(datetimex, datetime1) >= 0
728 AND time_compare(datetime2, datetimex) >=0
729 THEN
730 RETURN TRUE;
731 ELSE
732 RETURN FALSE;
733 END IF;
734 END is_time_between;
735
736 -- NULL indicates infinite
737 FUNCTION is_overlap
738 -- Returns 'Y' if period [s1,e1] overlaps [s2,e2]
739 -- 'N' otherwise
740 -- NULL indicates infinite for END dates
741 (s1 DATE,
742 e1 DATE,
743 s2 DATE,
744 e2 DATE)
745 RETURN VARCHAR2
746 IS
747 BEGIN
748 IF ( is_between(s1, s2, e2) ) OR ( is_between(s2, s1, e1) ) THEN
749 RETURN 'Y';
750 ELSE
751 RETURN 'N';
752 END IF;
753 END is_overlap;
754
755 --
756 -- FUNCTION is_time_overlap
757 --
758 -- DESCRIPTION
759 -- Returns 'Y' if period [s1,e1] overlaps [s2,e2] with time factored in
760 -- 'N' otherwise
761 -- NULL indicates infinite for END dates
762 --
763 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
764 --
765 -- ARGUMENTS
766 -- IN:
767 -- s1 Start date/time of period 1.
768 -- e1 End date/time of period 1.
769 -- s2 Start date/time of period 1.
770 -- e2 End date/time of period 1.
771 --
772 -- NOTES
773 --
774 -- MODIFICATION HISTORY
775 --
776 -- 13-DEC-2001 Joe del Callar Bug 2145637: Created.
777 --
778
779 FUNCTION is_time_overlap (
780 s1 IN DATE,
781 e1 IN DATE,
782 s2 IN DATE,
783 e2 IN DATE
784 ) RETURN VARCHAR2 IS
785 BEGIN
786 IF (is_time_between(s1, s2, e2)) OR (is_time_between(s2, s1, e1)) THEN
787 RETURN 'Y';
788 ELSE
789 RETURN 'N';
790 END IF;
791 END is_time_overlap;
792
793
794 -- Return varchar2 with a set of characters non desired.
795 -- Please pass the input in uppercase as this assumes so.
796 -- This is currently called just from HZ_FUZZY_PUB, if you
797 -- intend to call it from any other program, make sure this
798 -- logic works for that.
799 FUNCTION cleanse
800 (str IN varchar2
801 )
802 RETURN varchar2
803 IS
807 BEGIN
804 str2 varchar2(400) := str;
805 str3 varchar2(400);
806
808
809 -- if the input is null, return null as the processing has no
810 -- impact on that
811 if str is null then
812 return str;
813 end if;
814
815 -- Step 1. Replace any two or more consecutive same
816 -- letters by single letter
817
818 -- get the input string in a temporary string
819 str3 := str2;
820
821 -- loop from first letter to last but one letter
822 for i in 1..length(str2)-1
823 loop
824 -- if two consecutive letters match, then replace two such letter
825 -- by one letter from the temporary string
826 if substr(str2,i,1) = substr(str2,i+1,1) then
827 str3 := replace(str3, substr(str2,i,1)||substr(str2,i+1,1), substr(str2,i,1));
828 end if;
829 end loop;
830
831 str2 := str3;
832
833 -- Step 2. Replace Vowels only that occur inside
834 -- First we should build a temporary string
835 -- which would be the original string str2
836 -- stripped off the first letter
837 str3 := substr(str2, 2);
838
839 -- Now call replace to remove all occurrences
840 -- of each vowel
841 str3 := replace(str3, 'A', '');
842 str3 := replace(str3, 'E', '');
843 str3 := replace(str3, 'I', '');
844 str3 := replace(str3, 'O', '');
845 str3 := replace(str3, 'U', '');
846
847 -- Now we have to build str2 back with
848 -- first letter of str2 and appending str3
849 str2 := substr(str2, 1, 1)||str3;
850
851 -- return str2 which is the final clean word
852 return rtrim(str2);
853
854 END cleanse;
855
856 /* SSM SST Integration And Extension.
857 * Predicates will not be attached to non-profile tables.(Concept of select / de-select data sources
858 * for these entities is done away with)
859
860 -- bug fix 2969850
861 FUNCTION getNotSelectedDataSource(
862 p_data_source IN VARCHAR2
863 ) RETURN VARCHAR2 IS
864
865 CURSOR c_content_source_type IS
866 SELECT lookup_code FROM ar_lookups
867 WHERE lookup_type = 'CONTENT_SOURCE_TYPE' and
868 ENABLED_FLAG = 'Y' AND
869 TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE));
870
871 l_not_data_source VARCHAR2(4000);
872 l_content_source_type VARCHAR2(100);
873 l_len NUMBER;
874 BEGIN
875 l_not_data_source := '';
876
877 open c_content_source_type;
878 LOOP
879 FETCH c_content_source_type INTO l_content_source_type;
880 EXIT WHEN c_content_source_type%NOTFOUND;
881
882 IF ( instrb(p_data_source, '''' || l_content_source_type || '''') = 0 ) THEN
883
884 l_not_data_source := l_not_data_source||''''||l_content_source_type||''',';
885
886 END IF;
887
888 END LOOP;
889
890 IF l_not_data_source IS NOT NULL THEN
891 l_len := LENGTHB(l_not_data_source);
892 IF l_len > 1 THEN
893 l_not_data_source := SUBSTRB(l_not_data_source,1,l_len-1);
894 END IF;
895 END IF;
896
897
898 RETURN l_not_data_source;
899 END getNotSelectedDataSource;
900 */
901
902 /*===========================================================================+
903 | PROCEDURE |
904 | enable_health_care_security |
905 | |
906 | DESCRIPTION |
907 | Enables the VPD security for healthcare module. |
908 | |
909 | SCOPE - PUBLIC |
910 | |
911 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
912 | |
913 | ARGUMENTS : IN: |
914 | OUT: |
915 | IN/ OUT: |
916 | |
917 | |
918 | |
919 | NOTES |
920 | |
921 | MODIFICATION HISTORY |
922 | Ramesh Ch 31-OCT-03 Created |
923 | |
924 +===========================================================================*/
925
926 PROCEDURE enable_health_care_security IS
927 BEGIN
928 DBMS_SESSION.SET_CONTEXT('hz','hcare_used', 'Y');
929 END enable_health_care_security;
930
931 /*===========================================================================+
932 | PROCEDURE |
933 | disable_health_care_security |
937 | |
934 | |
935 | DESCRIPTION |
936 | Disables the VPD security for healthcare module. |
938 | SCOPE - PUBLIC |
939 | |
940 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
941 | |
942 | ARGUMENTS : IN: |
943 | OUT: |
944 | IN/ OUT: |
945 | |
946 | |
947 | |
948 | NOTES |
949 | |
950 | MODIFICATION HISTORY |
951 | Ramesh Ch 31-OCT-03 Created |
952 | |
953 +===========================================================================*/
954
955 PROCEDURE disable_health_care_security IS
956 BEGIN
957 DBMS_SESSION.SET_CONTEXT('hz', 'hcare_used', 'N');
958 END disable_health_care_security;
959
960 /*===========================================================================+
961 | PROCEDURE |
962 | add_hcare_policy_function |
963 | |
964 | DESCRIPTION |
965 | Adds the hcare_created_by_module_sec policy function to |
966 | HZ_PARTIES,HZ_PERSON_PROFILES,HZ_LOCATIONS,HZ_PARTY_SITES, |
967 | HZ_PARTY_SITE_USES,HZ_CITIZENSHIP,HZ_PERSON_LANGUAGE and |
968 | HZ_CONTACT_POINTS. |
969 | |
970 | SCOPE - PUBLIC |
971 | |
972 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
973 | |
974 | ARGUMENTS : IN: |
975 | OUT: |
976 | IN/ OUT: |
977 | |
978 | |
979 | |
980 | NOTES |
981 | |
982 | MODIFICATION HISTORY |
983 | Ramesh Ch 31-OCT-03 Created |
984 | Ramesh Ch 08-NOV-03 Commented the policy function for |
985 | HZ_CITIZENSHIP entity. |
986 +===========================================================================*/
987
988 PROCEDURE add_hcare_policy_function
989 IS
990
991 l_ar_schema VARCHAR2(30);
992 l_apps_schema VARCHAR2(30);
993 l_aol_schema VARCHAR2(30);
994 l_apps_mls_schema VARCHAR2(30);
995
996 l_status VARCHAR2(30);
997 l_industry VARCHAR2(30);
998 l_return_value BOOLEAN;
999
1000 BEGIN
1001
1002 arp_util.debug('add_hcare_policy_function (+) ');
1003
1004 --Get ar and apps schema name
1005 l_return_value := fnd_installation.get_app_info(
1006 'AR', l_status, l_industry, l_ar_schema);
1007
1008 IF NOT l_return_value THEN
1009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1010 END IF;
1011
1012 l_return_value := fnd_installation.get_app_info(
1013 'FND', l_status, l_industry, l_aol_schema);
1014
1015 IF NOT l_return_value THEN
1016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1017 END IF;
1018
1019 system.ad_apps_private.get_apps_schema_name(
1020 1, l_aol_schema, l_apps_schema, l_apps_mls_schema);
1021
1022 --Add policy functions
1023 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');
1024 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');
1025 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');
1029 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');
1026 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');
1027 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');
1028 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');
1030 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');
1031
1032 arp_util.debug('add_hcare_policy_function (-) ');
1033
1034 END add_hcare_policy_function;
1035
1036 /*===========================================================================+
1037 | PROCEDURE |
1038 | drop_hcare_policy_function |
1039 | |
1040 | DESCRIPTION |
1041 | Drops the hcare_created_by_module_sec policy function which |
1042 | was already added to HZ_PARTIES,HZ_PERSON_PROFILES, |
1043 | HZ_LOCATIONS,HZ_PARTY_SITES,HZ_PARTY_SITE_USES,HZ_CITIZENSHIP, |
1044 | HZ_PERSON_LANGUAGE and HZ_CONTACT_POINTS. |
1045 | |
1046 | |
1047 | SCOPE - PUBLIC |
1048 | |
1049 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1050 | |
1051 | ARGUMENTS : IN: |
1052 | OUT: |
1053 | IN/ OUT: |
1054 | |
1055 | |
1056 | |
1057 | NOTES |
1058 | |
1059 | MODIFICATION HISTORY |
1060 | Ramesh Ch 31-OCT-03 Created |
1061 | Ramesh Ch 08-NOV-03 Commented the policy function for |
1062 | HZ_CITIZENSHIP entity. |
1063 | |
1064 +===========================================================================*/
1065
1066 PROCEDURE drop_hcare_policy_function
1067 IS
1068
1069 l_ar_schema VARCHAR2(30);
1070 l_apps_schema VARCHAR2(30);
1071 l_aol_schema VARCHAR2(30);
1072 l_apps_mls_schema VARCHAR2(30);
1073
1074 l_status VARCHAR2(30);
1075 l_industry VARCHAR2(30);
1076 l_return_value BOOLEAN;
1077
1078 BEGIN
1079
1080 arp_util.debug('drop_hcare_policy_function (+) ');
1081
1082 --Get ar and apps schema name
1083 l_return_value := fnd_installation.get_app_info(
1084 'AR', l_status, l_industry, l_ar_schema);
1085
1086 IF NOT l_return_value THEN
1087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1088 END IF;
1089
1090 l_return_value := fnd_installation.get_app_info(
1091 'FND', l_status, l_industry, l_aol_schema);
1092
1093 IF NOT l_return_value THEN
1094 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1095 END IF;
1096
1097 system.ad_apps_private.get_apps_schema_name(
1098 1, l_aol_schema, l_apps_schema, l_apps_mls_schema);
1099
1100 --Drop policy functions
1101 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_PARTIES', 'hcare_created_by_module_sec');
1102 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_PERSON_PROFILES', 'hcare_created_by_module_sec');
1103 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_LOCATIONS', 'hcare_created_by_module_sec');
1104 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_PARTY_SITES', 'hcare_created_by_module_sec');
1105 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_PARTY_SITE_USES', 'hcare_created_by_module_sec');
1106 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_CITIZENSHIP', 'hcare_created_by_module_sec');
1107 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_PERSON_LANGUAGE','hcare_created_by_module_sec');
1108 FND_ACCESS_CONTROL_UTIL.DROP_POLICY(l_ar_schema, 'HZ_CONTACT_POINTS', 'hcare_created_by_module_sec');
1109
1110 arp_util.debug('drop_hcare_policy_function (-) ');
1111
1112 END drop_hcare_policy_function;
1113
1114 /*===========================================================================+
1115 | FUNCTION |
1116 | hcare_created_by_module_sec |
1117 | |
1118 | DESCRIPTION |
1119 | Policy Function |
1120 | |
1121 | SCOPE - PUBLIC |
1122 | |
1123 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1124 | |
1125 | ARGUMENTS : IN: |
1126 | p_object_schema: Entity Schema Name. |
1127 | p_object_name : Entity Name. |
1128 | OUT: |
1129 | IN/ OUT: |
1130 | |
1131 | RETURNS : Dynamic where clause based on the context value. |
1132 | |
1133 | NOTES |
1134 | |
1135 | MODIFICATION HISTORY |
1136 | Ramesh Ch 31-OCT-03 Created |
1137 | |
1138 +===========================================================================*/
1139
1140 FUNCTION hcare_created_by_module_sec(p_object_schema IN VARCHAR2,p_object_name IN VARCHAR2)
1141 RETURN VARCHAR2
1142 IS
1146
1143 l_context VARCHAR2(10);
1144 BEGIN
1145 l_context := NVL(SYS_CONTEXT('hz', 'hcare_used'),'Y');
1147 IF l_context <> 'N' THEN
1148 return 'CREATED_BY_MODULE<>''HTBPERSON''';
1149 ELSE RETURN NULL;
1150 END IF;
1151 END hcare_created_by_module_sec;
1152
1153
1154 END;