1 PACKAGE BODY PJI_PMV_PREDICATE AS
2 /* $Header: PJIRX05B.pls 120.4 2005/11/17 16:19:48 appldev noship $ */
3
4 FUNCTION Show_Class_Code(p_Class_Code IN VARCHAR2
5 , p_Class_Category IN VARCHAR2
6 , p_Org_ID IN VARCHAR2 DEFAULT NULL)
7 RETURN NUMBER
8 IS
9 l_Return_Value NUMBER;
10 l_Security_Profile_ID NUMBER;
11 l_Not_Secured_Flag VARCHAR2(30);
12 BEGIN
13 IF NOT (UPPER(p_Class_Category) = 'ALL' OR p_Class_Category IS NULL) THEN
14 BEGIN
15 IF p_Class_Category = '$PROJECT_TYPE$ALL' AND (UPPER(p_Org_ID) <> 'ALL' AND p_Org_ID IS NOT NULL) THEN
16 SELECT 1
17 INTO l_Return_Value
18 FROM pji_class_codes pjicc
19 , pa_project_types_all papta
20 WHERE
21 pjicc.class_id = p_Class_Code
22 AND pjicc.record_type = 'T'
23 AND papta.project_type= pjicc.class_code
24 AND papta.org_id = p_Org_ID;
25 ELSIF SUBSTR(p_Class_Category, 1, 14) = '$PROJECT_TYPE$' AND (UPPER(p_Org_ID) <> 'ALL' AND p_Org_ID IS NOT NULL) THEN
26 SELECT 1
27 INTO l_Return_Value
28 FROM pji_class_codes pjicc
29 , pa_project_types_all papta
30 WHERE
31 pjicc.class_id = p_Class_Code
32 AND pjicc.class_category = p_Class_Category
33 AND pjicc.record_type = 'T'
34 AND papta.project_type_class_code= SUBSTR(p_Class_Category,15)
35 AND papta.project_type= pjicc.class_code
36 AND papta.org_id = p_Org_ID;
37 ELSIF p_Class_Category = '$PROJECT_TYPE$ALL' THEN
38 BEGIN
39 l_Security_Profile_ID:=fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL');
40 SELECT view_all_organizations_flag
41 INTO l_Not_Secured_Flag
42 FROM per_security_profiles prof
43 WHERE security_profile_id = l_Security_Profile_ID;
44 END;
45
46 IF l_Not_Secured_Flag = 'Y' THEN
47 SELECT 1
48 INTO l_Return_Value
49 FROM pji_class_codes pjicc
50 , pa_project_types_all papta
51 , pa_implementations_all paimp
52 WHERE
53 pjicc.class_id = p_Class_Code
54 AND pjicc.record_type = 'T'
55 AND papta.project_type= pjicc.class_code
56 AND paimp.org_id = papta.org_id;
57 ELSE
58 SELECT 1
59 INTO l_Return_Value
60 FROM pji_class_codes pjicc
61 , pa_project_types_all papta
62 , per_organization_list sec
63 , pa_implementations_all paimp
64 WHERE
65 pjicc.class_id = p_Class_Code
66 AND pjicc.record_type = 'T'
67 AND papta.project_type= pjicc.class_code
68 AND papta.org_id = sec.organization_id
69 AND paimp.org_id = sec.organization_id
70 AND sec.security_profile_id = l_Security_Profile_ID;
71 END IF;
72 ELSE
73 SELECT 1
74 INTO l_Return_Value
75 FROM pji_class_codes
76 WHERE
77 class_id = p_Class_Code
78 AND class_category = p_Class_Category;
79 END IF;
80 EXCEPTION
81 WHEN OTHERS THEN
82 NULL;
83 END;
84 END IF;
85 RETURN l_Return_Value;
86 END;
87
88 FUNCTION Show_Currency_Type(p_Currency_Code IN VARCHAR2
89 , p_Org_ID IN VARCHAR2 DEFAULT NULL)
90 RETURN NUMBER
91 IS
92 l_Return_Value NUMBER;
93 BEGIN
94 IF p_Currency_Code = 'FII_GLOBAL1' THEN
95 RETURN 1;
96 ELSIF p_Currency_Code = 'FII_GLOBAL2' THEN
97 RETURN 2;
98 ELSIF p_Currency_Code = 'FII_GLOBAL1' THEN
99 RETURN NULL;
100 ELSE
101 IF NOT (UPPER(p_Org_ID) = 'ALL' OR p_Org_ID IS NULL) THEN
102 BEGIN
103 SELECT 1
104 INTO l_Return_Value
105 FROM
106 pa_implementations_all paimp
107 , gl_sets_of_books glsob
108 WHERE
109 paimp.set_of_books_id = glsob.set_of_books_id
110 and paimp.org_id = p_Org_ID
111 and glsob.currency_code = p_Currency_Code;
112
113 RETURN l_Return_Value;
114 EXCEPTION
115 WHEN NO_DATA_FOUND THEN
116 RETURN NULL;
117 WHEN OTHERS THEN
118 RETURN NULL;
119 END;
120 ELSE
121 RETURN NULL;
122 END IF;
123 END IF;
124 END Show_Currency_Type;
125
126 FUNCTION Show_Project(p_Project_ID IN NUMBER
127 , p_Organization_ID IN VARCHAR2
128 , p_Org_ID IN VARCHAR2 DEFAULT NULL)
129 RETURN NUMBER
130 IS
131 l_Return_Value NUMBER;
132 BEGIN
133 IF p_Organization_ID IS NOT NULL THEN
134 IF NOT (UPPER(p_Org_ID) = 'ALL' OR p_Org_ID IS NULL) THEN
135 SELECT 1
136 INTO l_Return_Value
137 FROM pa_projects_all prj
138 , pji_org_denorm pjd
139 WHERE
140 1=1
141 AND project_id = p_Project_ID
142 AND prj.carrying_out_organization_id = pjd.sub_organization_id
143 AND pjd.organization_id = p_Organization_ID
144 AND prj.org_id = p_Org_ID;
145 ELSE
146 SELECT 1
147 INTO l_Return_Value
148 FROM pa_projects_all prj
149 , pji_org_denorm pjd
150 WHERE
151 1=1
152 AND project_id = p_Project_ID
153 AND prj.carrying_out_organization_id = pjd.sub_organization_id
154 AND pjd.organization_id = p_Organization_ID;
155 END IF;
156 RETURN l_Return_Value;
157 ELSE
158 RETURN NULL;
159 END IF;
160 END;
161
162
163 FUNCTION Show_Project_Type (p_Project_ID IN NUMBER
164 , p_Organization_ID IN VARCHAR2
165 , p_Project_Type IN VARCHAR2
166 , p_Org_ID IN VARCHAR2 DEFAULT NULL)
167
168 RETURN NUMBER
169 IS
170 l_Return_Value NUMBER;
171 BEGIN
172
173 IF p_Organization_ID IS NOT NULL THEN
174 IF NOT (UPPER(p_Org_ID) = 'ALL' OR p_Org_ID IS NULL) THEN
175
176 SELECT 1 into l_Return_Value
177 FROM
178 pa_projects_all prj
179 , pji_org_denorm pjd
180 , (SELECT project_id
181 FROM pji_project_classes
182 WHERE class_category = '$PROJECT_TYPE$'||p_Project_Type) PJC
183 WHERE
184 1=1
185 and prj.project_id = pjc.project_id
186 and prj.carrying_out_organization_id = pjd.sub_organization_id
187 and prj.project_id = p_Project_ID
188 and pjd.organization_id = p_Organization_ID
189 and prj.org_id = p_Org_ID;
190
191 ELSE
192
193 SELECT 1 into l_Return_Value
194 FROM
195 pa_projects_all prj
196 ,pji_org_denorm pjd
197 , (SELECT project_id
198 FROM pji_project_classes
199 WHERE class_category = '$PROJECT_TYPE$'||p_Project_type) PJC
200 WHERE
201 1=1
202 and prj.project_id = pjc.project_id
203 and prj.carrying_out_organization_id = pjd.sub_organization_id
204 and prj.project_id = p_Project_ID
205 and pjd.organization_id = p_Organization_ID;
206 END IF;
207 RETURN l_Return_Value;
208 ELSE
209 RETURN NULL;
210 END IF;
211 END;
212
213
214 FUNCTION Show_Operating_Unit(p_Org_ID IN VARCHAR2)
215 RETURN NUMBER
216 IS
217 l_Return_Value NUMBER;
218 l_Security_Profile_ID NUMBER;
219 l_Not_Secured_Flag VARCHAR2(30);
220 BEGIN
221 l_Security_Profile_ID:=fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL');
222
223 IF l_Security_Profile_ID IS NOT NULL THEN
224 BEGIN
225 SELECT view_all_organizations_flag
226 INTO l_Not_Secured_Flag
227 FROM per_security_profiles prof
228 WHERE security_profile_id = l_Security_Profile_ID;
229 IF l_Not_Secured_Flag = 'Y' THEN
230 SELECT 1
231 INTO l_Return_Value
232 FROM pa_implementations_all imp
233 WHERE org_id = p_Org_ID;
234 ELSE
235 SELECT 1
236 INTO l_Return_Value
237 FROM
238 pa_implementations_all imp
239 , per_organization_list list
240 WHERE
241 list.security_profile_id = l_Security_Profile_ID
242 AND list.organization_id = p_Org_ID
243 AND imp.org_id = list.organization_id;
244 END IF;
245 EXCEPTION
246 WHEN NO_DATA_FOUND THEN
247 NULL;
248 END;
249 END IF;
250 RETURN l_Return_Value;
251 END;
252
253 END PJI_PMV_PREDICATE;
[Home] [Help]