[Home] [Help]
PACKAGE BODY: APPS.OKE_K_ACCESS_PVT
Source
1 PACKAGE BODY OKE_K_ACCESS_PVT AS
2 /* $Header: OKEVKASB.pls 115.10 2002/11/20 20:44:25 who ship $ */
3
4 WF_Type VARCHAR2(80);
5 WF_Item_Type VARCHAR2(8);
6 WF_Process VARCHAR2(30);
7
8 --
9 -- Private Procedures
10 --
11 PROCEDURE ASSIGNMENT_EXISTS
12 ( P_PROJECT_PARTY_ID IN NUMBER
13 , P_OBJECT_TYPE IN VARCHAR2
14 , P_OBJECT_ID IN NUMBER
15 , P_ROLE_ID IN NUMBER
16 , P_PERSON_ID IN NUMBER
17 , P_START_DATE_ACTIVE IN DATE
18 , P_END_DATE_ACTIVE IN DATE
19 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
20 ) IS
21
22 CURSOR c IS
23 SELECT project_role_id
24 FROM pa_project_parties
25 WHERE project_party_id <> nvl( P_Project_Party_ID , -9999 )
26 AND object_type = P_Object_Type
27 AND object_id = P_Object_ID
28 AND resource_type_id = 101
29 AND resource_source_id = P_Person_ID
30 AND ( --
31 -- Two date ranges overlap
32 --
33 GREATEST( trunc(P_Start_Date_Active) , trunc(start_date_active) ) <=
34 LEAST( nvl( trunc(end_date_active) , trunc(P_End_Date_Active) )
35 , nvl( trunc(P_End_Date_Active) , trunc(end_date_active) ) )
36 OR --
37 -- Two open ended assignments
38 --
39 ( P_End_Date_Active is NULL AND end_date_active is NULL )
40 );
41
42 L_Role_ID NUMBER;
43
44 BEGIN
45
46 OPEN c;
47 FETCH c INTO L_Role_ID;
48
49 IF ( c%notfound ) THEN
50 CLOSE c;
51 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
52 RETURN;
53 END IF;
54
55 CLOSE c;
56
57 IF ( L_Role_ID <> P_Role_ID ) THEN
58 FND_MESSAGE.Set_Name('OKE' , 'OKE_SEC_MULTI_ROLE_ASSIGNED');
59 ELSE
60 FND_MESSAGE.Set_Name('OKE' , 'OKE_SEC_OVERLAP_ROLE_ASSIGNED');
61 END IF;
62 FND_MESSAGE.Set_Token('EMP' , pa_utils.GetEmpName(P_Person_ID));
63 FND_MSG_PUB.Add;
64 X_Return_Status := FND_API.G_RET_STS_ERROR;
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 FND_MSG_PUB.ADD_EXC_MSG( p_pkg_name => 'OKE_K_ACCESS_PVT'
69 , p_procedure_name => 'ASSIGNMENT_EXISTS'
70 , p_error_text => substr(sqlerrm , 1 , 240) );
71 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
72
73 END ASSIGNMENT_EXISTS;
74
75
76 PROCEDURE VALIDATE_START_END_DATES
77 ( P_START_DATE_ACTIVE IN DATE
78 , P_END_DATE_ACTIVE IN DATE
79 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
80 ) IS
81
82 BEGIN
83
84 IF ( P_End_Date_Active IS NOT NULL
85 AND P_End_Date_Active < P_Start_Date_Active ) THEN
86 FND_MESSAGE.Set_Name('OKE' , 'OKE_INVALID_EFFDATE_PAIR');
87 FND_MSG_PUB.Add;
88 X_Return_Status := FND_API.G_RET_STS_ERROR;
89 ELSE
90 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
91 RETURN;
92 END IF;
93
94 END VALIDATE_START_END_DATES;
95
96 --
97 -- Public Procedures
98 --
99 PROCEDURE CREATE_CONTRACT_ACCESS
100 ( P_COMMIT IN VARCHAR2
101 , P_OBJECT_TYPE IN VARCHAR2
102 , P_OBJECT_ID IN NUMBER
103 , P_ROLE_ID IN NUMBER
104 , P_PERSON_ID IN NUMBER
105 , P_START_DATE_ACTIVE IN DATE
106 , P_END_DATE_ACTIVE IN OUT NOCOPY DATE
107 , X_PROJECT_PARTY_ID OUT NOCOPY NUMBER
108 , X_RESOURCE_ID OUT NOCOPY NUMBER
109 , X_ASSIGNMENT_ID OUT NOCOPY NUMBER
110 , X_RECORD_VERSION_NUMBER OUT NOCOPY NUMBER
111 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
112 , X_MSG_COUNT OUT NOCOPY NUMBER
113 , X_MSG_DATA OUT NOCOPY VARCHAR2
114 ) IS
115
116 CURSOR c IS
117 SELECT Record_Version_Number
118 FROM PA_Project_Parties PPP
119 WHERE Project_Party_ID = X_Project_Party_ID;
120
121 BEGIN
122
123 FND_MSG_PUB.initialize;
124
125 Validate_Start_End_Dates
126 ( P_Start_Date_Active => P_Start_Date_Active
127 , P_End_Date_Active => P_End_Date_Active
128 , X_Return_Status => X_Return_Status
129 );
130
131 IF ( X_Return_Status <> FND_API.G_RET_STS_SUCCESS ) THEN
132 RETURN;
133 END IF;
134
135 Assignment_Exists
136 ( P_Project_Party_ID => NULL
137 , P_Object_Type => P_Object_Type
138 , P_Object_ID => P_Object_ID
139 , P_Role_ID => P_Role_ID
140 , P_Person_ID => P_Person_ID
141 , P_Start_Date_Active => P_Start_Date_Active
142 , P_End_Date_Active => P_End_Date_Active
143 , X_Return_Status => X_Return_Status
144 );
145
146 IF ( X_Return_Status <> FND_API.G_RET_STS_SUCCESS ) THEN
147 RETURN;
148 END IF;
149
150 PA_PROJECT_PARTIES_PVT.Create_Project_Party
151 ( P_Commit => P_Commit
152 , P_Validate_Only => FND_API.G_FALSE
153 , P_Validation_Level => FND_API.G_VALID_LEVEL_FULL
154 , P_Debug_Mode => 'N'
155 , P_Object_ID => P_Object_ID
156 , P_Object_Type => P_Object_Type
157 , P_Resource_Type_ID => 101
158 , P_Project_Role_ID => P_Role_ID
159 , P_Resource_Source_ID => P_Person_ID
160 , P_Start_Date_Active => P_Start_Date_Active
161 , P_Scheduled_Flag => 'N'
162 , P_Calling_Module => 'FORM'
163 , P_Project_ID => NULL
164 , P_Project_End_Date => NULL
165 , P_End_Date_Active => P_End_Date_Active
166 , X_Project_Party_ID => X_Project_Party_ID
167 , X_Resource_ID => X_Resource_ID
168 , X_Assignment_ID => X_Assignment_ID
169 , X_WF_Type => WF_Type
170 , X_WF_Item_Type => WF_Item_Type
171 , X_WF_Process => WF_Process
172 , X_Return_Status => X_Return_Status
173 , X_Msg_Count => X_Msg_Count
174 , X_Msg_Data => X_Msg_Data
175 );
176
177 IF ( X_Return_Status <> FND_API.G_RET_STS_SUCCESS ) THEN
178 RETURN;
179 END IF;
180
181 OPEN c;
182 FETCH c INTO X_Record_Version_Number;
183 IF ( c%notfound ) THEN
184 CLOSE c;
185 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
186 END IF;
187 CLOSE c;
188
189 END CREATE_CONTRACT_ACCESS;
190
191
192 PROCEDURE LOCK_ROW
193 ( P_OBJECT_TYPE IN VARCHAR2
194 , P_OBJECT_ID IN NUMBER
195 , P_ROLE_ID IN NUMBER
196 , P_PERSON_ID IN NUMBER
197 , P_START_DATE_ACTIVE IN DATE
198 , P_END_DATE_ACTIVE IN DATE
199 , P_PROJECT_PARTY_ID IN NUMBER
200 , P_RESOURCE_ID IN NUMBER
201 ) IS
202
203 BEGIN
204
205 PA_PROJECT_PARTIES_PKG.Lock_Row
206 ( X_Project_Party_ID => P_Project_Party_ID
207 , X_Object_ID => P_Object_ID
208 , X_Object_Type => P_Object_Type
209 , X_Project_ID => NULL
210 , X_Resource_ID => P_Resource_ID
211 , X_Resource_Type_ID => 101
212 , X_Resource_Source_ID => P_Person_ID
213 , X_Project_Role_ID => P_Role_ID
214 , X_Start_Date_Active => P_Start_Date_Active
215 , X_Scheduled_Flag => 'N'
216 , X_End_Date_Active => P_End_Date_Active
217 );
218
219 END LOCK_ROW;
220
221
222 PROCEDURE UPDATE_CONTRACT_ACCESS
223 ( P_COMMIT IN VARCHAR2
224 , P_OBJECT_TYPE IN VARCHAR2
225 , P_OBJECT_ID IN NUMBER
226 , P_ROLE_ID IN NUMBER
227 , P_PERSON_ID IN NUMBER
228 , P_START_DATE_ACTIVE IN DATE
229 , P_END_DATE_ACTIVE IN OUT NOCOPY DATE
230 , P_PROJECT_PARTY_ID IN NUMBER
231 , P_RECORD_VERSION_NUMBER IN NUMBER
232 , P_RESOURCE_ID IN NUMBER
233 , P_ASSIGNMENT_ID IN NUMBER
234 , X_ASSIGNMENT_ID OUT NOCOPY NUMBER
235 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
236 , X_MSG_COUNT OUT NOCOPY NUMBER
237 , X_MSG_DATA OUT NOCOPY VARCHAR2
238 ) IS
239
240 BEGIN
241
242 FND_MSG_PUB.initialize;
243
244 Validate_Start_End_Dates
245 ( P_Start_Date_Active => P_Start_Date_Active
246 , P_End_Date_Active => P_End_Date_Active
247 , X_Return_Status => X_Return_Status
248 );
249
250 IF ( X_Return_Status <> FND_API.G_RET_STS_SUCCESS ) THEN
251 RETURN;
252 END IF;
253
254 Assignment_Exists
255 ( P_Project_Party_ID => P_Project_Party_ID
256 , P_Object_Type => P_Object_Type
257 , P_Object_ID => P_Object_ID
258 , P_Role_ID => P_Role_ID
259 , P_Person_ID => P_Person_ID
260 , P_Start_Date_Active => P_Start_Date_Active
261 , P_End_Date_Active => P_End_Date_Active
262 , X_Return_Status => X_Return_Status
263 );
264
265 IF ( X_Return_Status <> FND_API.G_RET_STS_SUCCESS ) THEN
266 RETURN;
267 END IF;
268
269 PA_PROJECT_PARTIES_PVT.Update_Project_Party
270 ( P_Commit => P_Commit
271 , P_Validate_Only => FND_API.G_FALSE
272 , P_Validation_Level => FND_API.G_VALID_LEVEL_FULL
273 , P_Debug_Mode => 'N'
274 , P_Object_ID => P_Object_ID
275 , P_Object_Type => P_Object_Type
276 , P_Project_Role_ID => P_Role_ID
277 , P_Resource_Type_ID => 101
278 , P_Resource_Source_ID => P_Person_ID
279 , P_Resource_ID => P_Resource_ID
280 , P_Start_Date_Active => P_Start_Date_Active
281 , P_Scheduled_Flag => 'N'
282 , P_Record_Version_Number => P_Record_Version_Number
283 , P_Calling_Module => 'FORM'
284 , P_Project_ID => NULL
285 , P_Project_End_Date => NULL
286 , P_Project_Party_ID => P_Project_Party_ID
287 , P_Assignment_ID => FND_API.G_MISS_NUM
288 , P_Assign_Record_Version_Number => FND_API.G_MISS_NUM
289 , P_End_Date_Active => P_End_Date_Active
290 , X_Assignment_ID => X_Assignment_ID
291 , X_WF_Type => WF_Type
292 , X_WF_Item_Type => WF_Item_Type
293 , X_WF_Process => WF_Process
294 , X_Return_Status => X_Return_Status
295 , X_Msg_Count => X_Msg_Count
296 , X_Msg_Data => X_Msg_Data
297 );
298
299 END UPDATE_CONTRACT_ACCESS;
300
301 END OKE_K_ACCESS_PVT;