1 PACKAGE BODY AP_WEB_PROJECT_PKG AS
2 /* $Header: apwprojb.pls 120.10.12000000.2 2007/09/28 13:57:28 rveliche ship $ */
3
4 PROCEDURE IsSessionProjectEnabled(
5 P_EmployeeID IN FND_USER.employee_id%TYPE,
6 P_FNDUserID IN FND_USER.user_id%TYPE,
7 P_Result OUT NOCOPY VARCHAR2)
8
9 -- Determines whether the session is project enabled. Checks (1) PA is
10 -- installed, (2) project accounting profile option is enabled, and
11 -- (3) the web user can enter projects information. If all of the above
12 -- is true, then returns 'Y', otherwise 'N'.
13 -- For contingent workers, an additional check is made whether
14 -- projects in cwk enabled.
15
16 IS
17
18 l_Status VARCHAR2(10);
19 l_Industry VARCHAR2(10);
20 l_PAEnable VARCHAR2(30);
21 l_PAEnableProfile VARCHAR2(30) := NULL;
22 l_PAEnablePreviousVal VARCHAR2(30);
23 l_Mesg VARCHAR2(100);
24 l_FNDUserID AP_WEB_DB_HR_INT_PKG.fndUser_userID;
25 l_FNDUsersEmployeeID AP_WEB_DB_HR_INT_PKG.fndUser_employeeID;
26 l_userIdCursor AP_WEB_DB_HR_INT_PKG.UserIdRefCursor;
27
28 BEGIN
29
30 -- Assume that the session is not project enabled
31 P_Result := 'N';
32
33 -- Check projects installed
34 if (FND_INSTALLATION.GET(275, 275, l_Status, l_Industry)) then
35 if (l_Status <> 'I') then
36 return;
37 end if;
38 end if;
39
40 -- Be sure that both employee ID and FND User ID is not null. Otherwise
41 -- return not projects enabled
42 IF (P_FNDUserID IS NULL) OR (P_EmployeeID IS NULL) THEN
43 RETURN;
44 END IF;
45
46 -- For Contingent workers, call PA API to determine whether
47 -- PA.FP.M is installed.
48 IF (AP_WEB_DB_HR_INT_PKG.IsPersonCwk(P_EmployeeID) = 'Y' AND
49 PA_PO_INTEGRATION.is_pjc_po_cwk_intg_enab <> 'Y') THEN
50 RETURN;
51 END IF;
52
53 -- Check whether third party is being used since different logic is used
54 -- to handle the case where an employee may correspond to multiple
55 -- FND users.
56 IF ( NOT AP_WEB_DB_HR_INT_PKG.GetEmpIdForUser(
57 P_FNDUserID,
58 l_FNDUsersEmployeeID) ) THEN
59 l_FNDUsersEmployeeID := NULL;
60 END IF;
61
62 if (l_FNDUsersEmployeeID = P_EmployeeID) then
63
64 -- Third party not being used.
65
66 -- Check profile option
67 l_PAEnableProfile := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
68 p_name => 'AP_WEB_ENABLE_PROJECT_ACCOUNTING',
69 p_user_id => P_FNDUserID,
70 p_resp_id => null,
71 p_apps_id => null);
72 if (l_PAEnableProfile = 'N') then
73 return;
74 end if;
75
76 -- Call PA API to determine whether user is projects enabled.
77 PA_CLIENT_EXTN_PTE.check_time_exp_proj_user(l_FNDUsersEmployeeID,
78 P_FNDUserID,
79 l_PAEnable, l_Mesg);
80 if (l_PAEnable = 'Y') then
81 P_Result := 'Y';
82 end if;
83
84 else
85
86 -- Third party being used.
87 -- For each FNDUSER corresponding to the employee, verify that the
88 -- Project enabled profile option is the same for all fnd users
89 -- If value is the same for all FND user then return the value, otherwise
90 -- return not projects enabled.
91 l_PAEnablePreviousVal := NULL;
92
93 IF ( AP_WEB_DB_HR_INT_PKG.GetUserIdForEmpCursor(
94 p_EmployeeID,
95 l_userIdCursor) = TRUE ) THEN
96 LOOP
97 EXIT WHEN l_userIdCursor%NOTFOUND OR
98 l_PAEnableProfile = 'N' OR
99 ((l_PAEnablePreviousVal <> l_PAEnable) AND
100 (l_PAEnablePreviousVal IS NOT NULL));
101
102 FETCH l_userIdCursor INTO l_FNDUserID;
103
104 -- Check profile option
105 l_PAEnableProfile := AP_WEB_UTILITIES_PKG.VALUE_SPECIFIC(
106 p_name => 'AP_WEB_ENABLE_PROJECT_ACCOUNTING',
107 p_user_id => l_FNDUserID,
108 p_resp_id => null,
109 p_apps_id => null);
110
111 PA_CLIENT_EXTN_PTE.check_time_exp_proj_user(P_EmployeeID,
112 l_FNDUserID,
113 l_PAEnable,
114 l_Mesg);
115 l_PAEnablePreviousVal := l_PAEnable;
116 END LOOP;
117 CLOSE l_userIdCursor;
118 END IF;
119
120 if (l_PAEnableProfile <> 'N') AND
121 (l_PAEnable = 'Y') AND
122 (l_PAEnablePreviousVal = l_PAEnable) THEN
123 P_Result := 'Y';
124 end if;
125 end if;
126 EXCEPTION
127 WHEN OTHERS THEN
128 BEGIN
129 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
130 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
131 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'IsSessionProjectEnabled');
132 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
133 END;
134
135
136 END;
137
138 PROCEDURE GetExpenditureTypeMapping(
139 P_ExpenseType IN AP_EXPENSE_REPORT_PARAMS.parameter_id%TYPE,
140 P_PAExpenditureType OUT NOCOPY AP_EXPENSE_REPORT_PARAMS.pa_expenditure_type%TYPE
141 )
142 -- Returns the expenditure type mapped to the expense type
143 -- Exception generated if cannot find P_ExpenseType
144
145 IS
146 l_expTypeInfoRec AP_WEB_DB_EXPTEMPLATE_PKG.ExpTypeInfoRec;
147 BEGIN
148
149 IF ( AP_WEB_DB_EXPTEMPLATE_PKG.GetExpTypeInfo(
150 P_ExpenseType,
151 l_expTypeInfoRec) ) THEN
152 P_PAExpenditureType := l_expTypeInfoRec.pa_exp_type;
153 END IF;
154 EXCEPTION
155 WHEN OTHERS THEN
156 BEGIN
157 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
158 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
159 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'GetExpenditureTypeMapping');
160 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
161 END;
162
163 END GetExpenditureTypeMapping;
164
165 PROCEDURE DerivePAInfoFromDatabase(
166 P_IsProjectEnabled IN VARCHAR2,
167 P_PAProjectNumber IN OUT NOCOPY PA_PROJECTS_EXPEND_V.project_number%TYPE,
168 P_PAProjectID IN OUT NOCOPY PA_PROJECTS_EXPEND_V.project_id%TYPE,
169 P_PATaskNumber IN OUT NOCOPY PA_TASKS_EXPEND_V.task_number%TYPE,
170 P_PATaskID IN OUT NOCOPY PA_TASKS_EXPEND_V.task_id%TYPE,
171 P_PAExpenditureType IN OUT NOCOPY AP_EXPENSE_REPORT_PARAMS.pa_expenditure_type%TYPE )
172
173 -- Populates the ProjectNumber, TaskNumber
174 -- Assumes that Project and Task Number are filled in already
175 -- Reports errors that would not be caught by core validation
176 -- Will clear out project info, if invalid
177
178 IS
179 l_Field1 INTEGER; -- Temporary
180 l_Field2 INTEGER; -- Temporary
181 BEGIN
182
183 if P_IsProjectEnabled = 'Y' then
184
185 if (P_PAExpenditureType is not NULL) then
186
187 -- Get ProjectNumber from ProjectID
188 if P_PAProjectID is not NULL then
189 begin
190 IF ( NOT AP_WEB_DB_PA_INT_PKG.GetProjectNumber(
191 P_PAProjectID,
192 P_PAProjectNumber) ) THEN
193 P_PAProjectNumber := NULL;
194 END IF;
195 exception
196 when OTHERS then
197 NULL; -- do not report errors here, will be caught in validation
198 end;
199 end if;
200
201 -- GetTaskID from TaskNumber
202 if P_PATaskID is not NULL then
203 if P_PAProjectID is not NULL then
204 IF ( NOT AP_WEB_DB_PA_INT_PKG.GetTaskIDByProjID(
205 P_PAProjectID,
206 P_PATaskID) ) THEN
207 P_PATaskID := NULL;
208 END IF;
209 end if;
210 end if;
211
212 -- Clear out expenditure type if not project-enabled receipt
213 if (P_PAProjectNumber = NULL) and (P_PATaskNumber = NULL) then
214 P_PAExpenditureType := NULL;
215 P_PAProjectID := NULL;
216 P_PATaskID := NULL;
217 end if;
218
219 return; -- No more additional errors to catch
220
221 end if; -- (P_PAExpenditureType is not NULL)
222 end if; -- P_IsProjectEnabled = 'Y'
223 EXCEPTION
224 WHEN OTHERS THEN
225 BEGIN
226 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
227 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
228 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'DerivePAInfoFromDatabase');
229 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
230 END;
231
232 END DerivePAInfoFromDatabase;
233
234 PROCEDURE DerivePAInfoFromUserInput(
235 P_IsProjectEnabled IN VARCHAR2,
236 P_PAProjectNumber IN OUT NOCOPY PA_PROJECTS_EXPEND_V.project_number%TYPE,
237 P_PAProjectID OUT NOCOPY PA_PROJECTS_EXPEND_V.project_id%TYPE,
238 P_PAProjectName OUT NOCOPY PA_PROJECTS_EXPEND_V.project_name%TYPE,
239 P_PATaskNumber IN OUT NOCOPY PA_TASKS_EXPEND_V.task_number%TYPE,
240 P_PATaskID OUT NOCOPY PA_TASKS_EXPEND_V.task_id%TYPE,
241 P_PATaskName OUT NOCOPY PA_TASKS_EXPEND_V.task_name%TYPE,
242 P_PAExpenditureType OUT NOCOPY AP_EXPENSE_REPORT_PARAMS.pa_expenditure_type%TYPE,
243 P_ExpenseType IN AP_EXPENSE_REPORT_PARAMS.parameter_id%TYPE
244 )
245
246 -- Populates the ProjectID, TaskID, and Expenditure Type
247 -- Assumes that Project and Task Number are filled in already
248 -- Reports errors that would not be caught by core validation
249
250 IS
251 P_PAExpenditureTypeTemp AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paExpendituretype;
252 BEGIN
253
254 -- Initialize out variables
255 P_PAProjectID := NULL;
256 P_PATaskID := NULL;
257 P_PAExpenditureType := NULL;
258
259 if P_IsProjectEnabled = 'Y' then
260
261 -- Determine whether expense type project enabled
262 GetExpenditureTypeMapping(P_ExpenseType, P_PAExpenditureTypeTemp);
263 P_PAExpenditureType := P_PAExpenditureTypeTemp;
264
265 if (P_PAExpenditureTypeTemp IS NOT NULL) then
266
267 -- Get ProjectID from ProjectNumber
268 if (NOT P_PAProjectNumber IS NULL) then
269 IF ( NOT AP_WEB_DB_PA_INT_PKG.GetProjectInfo(
270 P_PAProjectNumber,
271 p_PAProjectID,
272 P_PAProjectName) ) THEN
273 P_PAProjectID := NULL;
274 END IF;
275
276 end if;
277
278 -- GetTaskID from TaskNumber
279 if (NOT P_PATaskNumber IS NULL) then
280 if (NOT P_PAProjectID IS NULL) then
281 IF ( NOT AP_WEB_DB_PA_INT_PKG.GetTaskInfo(
282 P_PATaskNumber,
283 P_PAProjectID,
284 P_PATaskID,
285 P_PATaskName) ) THEN
286 P_PATaskID := NULL;
287 END IF;
288 end if;
289 end if;
290
291 if (P_PAProjectNumber IS NULL) and (P_PATaskNumber IS NULL) then
292 P_PAExpenditureType := NULL;
293 end if;
294
295 end if; -- (P_PAExpenditureType is not NULL)
296
297 end if;
298 EXCEPTION
299 WHEN OTHERS THEN
300 BEGIN
301 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
302 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
303 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'DerivePAInfoFromUserInput');
304 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
305 END;
306 END DerivePAInfoFromUserInput;
307
308 PROCEDURE ValidatePATransaction(
309 P_project_id IN NUMBER,
310 P_task_id IN NUMBER,
311 P_ei_date IN DATE,
312 P_expenditure_type IN VARCHAR2,
313 P_non_labor_resource IN VARCHAR2,
314 P_person_id IN NUMBER,
315 P_quantity IN NUMBER DEFAULT NULL,
316 P_denom_currency_code IN VARCHAR2 DEFAULT NULL,
317 P_acct_currency_code IN VARCHAR2 DEFAULT NULL,
318 P_denom_raw_cost IN NUMBER DEFAULT NULL,
319 P_acct_raw_cost IN NUMBER DEFAULT NULL,
320 P_acct_rate_type IN VARCHAR2 DEFAULT NULL,
321 P_acct_rate_date IN DATE DEFAULT NULL,
322 P_acct_exchange_rate IN NUMBER DEFAULT NULL ,
323 P_transfer_ei IN NUMBER DEFAULT NULL,
324 P_incurred_by_org_id IN NUMBER DEFAULT NULL,
325 P_nl_resource_org_id IN NUMBER DEFAULT NULL,
326 P_transaction_source IN VARCHAR2 DEFAULT NULL ,
327 P_calling_module IN VARCHAR2 DEFAULT NULL,
328 P_vendor_id IN NUMBER DEFAULT NULL,
329 P_entered_by_user_id IN NUMBER DEFAULT NULL,
330 P_attribute_category IN VARCHAR2 DEFAULT NULL,
331 P_attribute1 IN VARCHAR2 DEFAULT NULL,
332 P_attribute2 IN VARCHAR2 DEFAULT NULL,
333 P_attribute3 IN VARCHAR2 DEFAULT NULL,
334 P_attribute4 IN VARCHAR2 DEFAULT NULL,
335 P_attribute5 IN VARCHAR2 DEFAULT NULL,
336 P_attribute6 IN VARCHAR2 DEFAULT NULL,
337 P_attribute7 IN VARCHAR2 DEFAULT NULL,
338 P_attribute8 IN VARCHAR2 DEFAULT NULL,
339 P_attribute9 IN VARCHAR2 DEFAULT NULL,
340 P_attribute10 IN VARCHAR2 DEFAULT NULL,
341 P_attribute11 IN VARCHAR2 DEFAULT NULL,
342 P_attribute12 IN VARCHAR2 DEFAULT NULL,
343 P_attribute13 IN VARCHAR2 DEFAULT NULL,
344 P_attribute14 IN VARCHAR2 DEFAULT NULL,
345 P_attribute15 IN VARCHAR2 DEFAULT NULL,
346 P_msg_type OUT NOCOPY VARCHAR2,
347 P_msg_data OUT NOCOPY VARCHAR2,
348 P_billable_flag OUT NOCOPY VARCHAR2)
349 IS
350
351 --
352 -- This is a wrapper function to PATC.GET_STATUS. Since the
353 -- PA group is modifying the API for 11.5, but not in 11.0,
354 -- we would like to isolate the rest of the source code from
355 -- these changes.
356 --
357 l_MsgApplication VARCHAR2(10);
358 l_MsgType VARCHAR2(10);
359 l_MsgName VARCHAR2(100);
360 l_MsgToken1 VARCHAR2(1000);
361 l_MsgToken2 VARCHAR2(1000);
362 l_MsgToken3 VARCHAR2(1000);
363 l_MsgCount NUMBER;
364
365 BEGIN
366
367 AP_WEB_WRAPPER_PKG.ValidatePATransaction(
368 P_project_id,
369 P_task_id,
370 P_ei_date,
371 P_expenditure_type,
372 P_non_labor_resource,
373 P_person_id,
374 P_quantity,
375 P_denom_currency_code,
376 P_acct_currency_code,
377 P_denom_raw_cost,
378 P_acct_raw_cost,
379 P_acct_rate_type,
380 P_acct_rate_date,
381 P_acct_exchange_rate,
382 P_transfer_ei,
383 P_incurred_by_org_id,
384 P_nl_resource_org_id,
385 P_transaction_source,
386 P_calling_module,
387 P_vendor_id,
388 P_entered_by_user_id,
389 P_attribute_category,
390 P_attribute1,
391 P_attribute2,
392 P_attribute3,
393 P_attribute4,
394 P_attribute5,
395 P_attribute6,
396 P_attribute7,
397 P_attribute8,
398 P_attribute9,
399 P_attribute10,
400 P_attribute11,
401 P_attribute12,
402 P_attribute13,
403 P_attribute14,
404 P_attribute15,
405 l_MsgApplication,
406 l_MsgType,
407 l_MsgToken1,
408 l_MsgToken2,
409 l_MsgToken3,
410 l_MsgCount,
411 l_MsgName, -- P_msg_data will contain the error msg
412 P_Msg_Data,
413 P_billable_flag);
414
415 -- If errors occurred, then get the message text and return in P_msg_data
416 P_Msg_Data := NULL;
417 IF (l_MsgName IS NOT NULL) THEN
418 FND_MESSAGE.SET_NAME( l_MsgApplication, l_MsgName);
419
420 -- Bug: 6347674, donot set the tokens when there are none comming from the PA client extension
421 IF (l_MsgToken1 IS NOT NULL) THEN
422 FND_MESSAGE.SET_TOKEN('PATC_MSG_TOKEN1', l_MsgToken1);
423 END IF;
424
425 IF (l_MsgToken2 IS NOT NULL) THEN
426 FND_MESSAGE.SET_TOKEN('PATC_MSG_TOKEN2', l_MsgToken2);
427 END IF;
428
429 IF (l_MsgToken3 IS NOT NULL) THEN
430 FND_MESSAGE.SET_TOKEN('PATC_MSG_TOKEN3', l_MsgToken3);
431 END IF;
432
433 P_Msg_Data := FND_MESSAGE.GET;
434 END IF;
435
436 -- Set the message type to one we use in Web Expenses
437 P_Msg_Type := NULL;
438 IF (l_MsgType = 'E') THEN
439 P_Msg_Type := AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeError;
440 ELSIF (l_MsgType = 'W') THEN
441 P_Msg_Type := AP_WEB_DFLEX_PKG.C_CustValidResMsgTypeWarning;
442 END IF;
443 EXCEPTION
444 WHEN OTHERS THEN
445 BEGIN
446 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
447 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
448 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'PROJECT.ValidatePATransaction');
449 AP_WEB_UTILITIES_PKG.DisplayException(fnd_message.get);
450 END;
451 END ValidatePATransaction;
452
453 ----------------------------------------------------------------------
454
455 FUNCTION IsGrantsEnabled RETURN VARCHAR2
456 IS
457 l_is_grants_enabled VARCHAR2(1);
458 BEGIN
459 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
460 'start IsGrantsEnabled');
461
462 IF (GMS_OIE_INT_PKG.IsGrantsEnabled()) THEN
463 l_is_grants_enabled := 'Y';
464 ELSE
465 l_is_grants_enabled := 'N';
466 END IF;
467
468 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_OA_MAINFLOW_PKG',
469 'end IsGrantsEnabled');
470 RETURN l_is_grants_enabled;
471 END IsGrantsEnabled;
472 ----------------------------------------------------------------------
473
474 END AP_WEB_PROJECT_PKG;
[Home] [Help]