1 PACKAGE BODY IGS_GE_MNT_SDTT AS
2 /* $Header: IGSGE06B.pls 120.2 2006/01/20 05:49:59 skpandey ship $ */
3
4 L_ROWID VARCHAR2(25);
5
6 -- Delete a record in the table s_disable_table_trigger.
7 PROCEDURE GENP_DEL_SDTT(
8 p_table_name IN VARCHAR2 )
9 AS
10 gv_other_detail VARCHAR2(255);
11 BEGIN
12 DECLARE
13
14 --- Delete a record from the s_disable_table_trigger
15 --- database table.
16 CURSOR CUR_DISB_TAB_TRG IS
17 SELECT ROWID , IGS_GE_S_DSB_TAB_TRG.*
18 FROM IGS_GE_S_DSB_TAB_TRG
19 WHERE table_name = p_table_name AND
20 session_id = (
21 SELECT userenv('SESSIONID')
22 FROM dual );
23 BEGIN
24 for DISB_TAB_TRG_REC IN CUR_DISB_TAB_TRG loop
25 IGS_GE_S_DSB_TAB_TRG_PKG.DELETE_ROW(X_ROWID => DISB_TAB_TRG_REC.ROWID );
26 end loop;
27 END;
28 EXCEPTION
29 WHEN OTHERS THEN
30 Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXCEPTION');
31 IGS_GE_MSG_STACK.ADD;
32 App_Exception.Raise_Exception ;
33 END genp_del_sdtt;
34
35 -- to validate whether the person is a staff member
36 FUNCTION pid_val_staff
37 (p_person_id IN NUMBER,
38 p_preferred_name OUT NOCOPY VARCHAR2
39 )
40 RETURN BOOLEAN IS
41
42 l_is_staff VARCHAR2(25);
43 l_all_person VARCHAR2(25);
44
45 CURSOR C_STAFF (cp_staff_var VARCHAR2) IS
46 SELECT 'X'
47 FROM igs_pe_per_type_map
48 WHERE system_type = cp_staff_var;
49
50 CURSOR C_ALLPERSON (cp_person_id VARCHAR2) IS
51 SELECT 'X'
52 FROM per_all_people_f
53 WHERE party_id = cp_person_id;
54
55 CURSOR C_OSSUSER (cp_staff_var VARCHAR2, cp_person_id VARCHAR2) IS
56 SELECT NVL(HZ.KNOWN_AS, SUBSTR (HZ.PERSON_FIRST_NAME, 1, DECODE(INSTR(HZ.PERSON_FIRST_NAME, ' '),
57 0, LENGTH(HZ.PERSON_FIRST_NAME), (INSTR(HZ.PERSON_FIRST_NAME, ' ')-1)))) || ' ' || HZ.PERSON_LAST_NAME
58 PREFERRED_NAME
59 FROM IGS_PE_PERSON_TYPES PT,IGS_PE_TYP_INSTANCES_ALL PTI,HZ_PARTIES HZ
60 WHERE HZ.PARTY_ID = PTI.PERSON_ID
61 AND HZ.PARTY_ID = cp_person_id
62 AND PTI.PERSON_TYPE_CODE = PT.PERSON_TYPE_CODE
63 AND PT.SYSTEM_TYPE = cp_staff_var
64 AND TRUNC(SYSDATE) BETWEEN TRUNC(PTI.START_DATE) AND TRUNC(NVL(PTI.END_DATE,SYSDATE))
65 AND HZ.STATUS = 'A';
66
67 --skpandey, Bug#4937960: Changed C_HRUSER cursor definition to optimize query
68 CURSOR C_HRUSER (cp_staff_var VARCHAR2, cp_person_id VARCHAR2) IS
69 SELECT NVL(HZ.KNOWN_AS, SUBSTR (HZ.PERSON_FIRST_NAME, 1, DECODE(INSTR(HZ.PERSON_FIRST_NAME, ' '),
70 0, LENGTH(HZ.PERSON_FIRST_NAME), (INSTR(HZ.PERSON_FIRST_NAME, ' ')-1)))) || ' ' || HZ.PERSON_LAST_NAME
71 PREFERRED_NAME
72 FROM PER_PERSON_TYPE_USAGES_F USG,PER_ALL_PEOPLE_F PEO,IGS_PE_PER_TYPE_MAP MAP,HZ_PARTIES HZ
73 WHERE HZ.PARTY_ID = peo.party_id
74 AND USG.PERSON_ID = PEO.PERSON_ID
75 AND HZ.PARTY_ID = cp_person_id
76 AND USG.PERSON_TYPE_ID = MAP.PER_PERSON_TYPE_ID
77 AND MAP.SYSTEM_TYPE = cp_staff_var AND
78 TRUNC(SYSDATE) BETWEEN TRUNC(PEO.EFFECTIVE_START_DATE) AND TRUNC(PEO.EFFECTIVE_END_DATE)
79 AND TRUNC(SYSDATE) BETWEEN TRUNC(USG.EFFECTIVE_START_DATE) AND TRUNC(USG.EFFECTIVE_END_DATE)
80 AND HZ.STATUS = 'A';
81
82
83 BEGIN
84 OPEN c_staff ('STAFF');
85 FETCH c_staff INTO l_is_staff;
86 CLOSE c_staff;
87 OPEN c_allperson (p_person_id);
88 FETCH c_allperson INTO l_all_person;
89 CLOSE c_allperson;
90 IF l_is_staff IS NOT NULL AND l_all_person IS NOT NULL THEN
91
92 OPEN c_hruser ('STAFF', p_person_id);
93 FETCH c_hruser INTO p_preferred_name;
94 CLOSE c_hruser;
95 IF p_preferred_name IS NOT NULL THEN
96 RETURN TRUE;
97 ELSE
98 RETURN FALSE;
99 END IF;
100 ELSE
101
102 OPEN c_ossuser ('STAFF', p_person_id);
103 FETCH c_ossuser INTO p_preferred_name;
104 CLOSE c_ossuser;
105 IF p_preferred_name IS NOT NULL THEN
106 RETURN TRUE;
107 ELSE
108 RETURN FALSE;
109 END IF;
110 END IF;
111
112 END;
113
114 -- This function takes a varchar string and a format mask and checks whether the string is in the passed format or not. Bug : 2325141
115 FUNCTION check_format_mask(s IN VARCHAR2, t IN VARCHAR2) RETURN BOOLEAN IS
116 l_str_len NUMBER := LENGTH(s);
117 l_trans_s IGS_PE_PERSON_ID_TYP.FORMAT_MASK%TYPE := NULL;
118 l_chr VARCHAR2(1);
119 BEGIN
120 FOR i IN 1..l_str_len LOOP
121 l_chr := SUBSTR(s,i,1);
122 IF l_chr IN ('0','1','2','3','4','5','6','7','8','9') THEN
123 l_trans_s :=l_trans_s||'9';
124 ELSIF l_chr IN (' ','-','_','+','=',')','(','*','&','^','%','$','#','','!','`','~','/','\')
125 THEN
126 l_trans_s :=l_trans_s||l_chr;
127 ELSE
128 l_trans_s:=l_trans_s||'X';
129 END IF;
130 END LOOP;
131 IF t = l_trans_s THEN
132 RETURN TRUE;
133 ELSE
134 RETURN FALSE;
135 END IF;
136 END check_format_mask;
137
138 END IGS_GE_MNT_SDTT;
[Home] [Help]