[Home] [Help]
PACKAGE BODY: APPS.PA_BILLING_WRKBNCH_EVENTS
Source
1 Package Body PA_Billing_Wrkbnch_Events as
2 /* $Header: PABWBCHB.pls 120.2 2005/08/19 16:17:28 mwasowic noship $ */
3
4 /*----------------- Private Procedure/Function Declarations -----------------*/
5
6 /*----------------------------------------------------------------------------+
7 | This Private Procedure Get_Next_Event_Num gets the maximum event num + 1 |
8 | for the project and task. |
9 +----------------------------------------------------------------------------*/
10 Procedure Get_Next_Event_Num ( P_Project_ID IN NUMBER,
11 P_Task_ID IN NUMBER,
12 X_Event_Num OUT NOCOPY NUMBER ) AS --File.Sql.39 bug 4440895
13
14 Cursor c_Event_Num_Project Is
15 select nvl(max(event_num), 0) + 1 from pa_events
16 where project_id = P_Project_ID
17 and task_id is null;
18
19 Cursor c_Event_Num_Task Is
20 select nvl(max(event_num), 0) + 1 from pa_events
21 where project_id = P_Project_ID
22 and task_id = P_Task_ID;
23 BEGIN
24
25 If nvl(P_Task_Id, 0) <= 0 Then
26 Open c_Event_Num_Project;
27 Fetch c_Event_Num_Project Into X_Event_Num;
28 Close c_Event_Num_Project;
29 Else
30 Open c_Event_Num_Task;
31 Fetch c_Event_Num_Task Into X_Event_Num;
32 Close c_Event_Num_Task;
33 End If;
34
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 /* ATG NOCOPY */
39 X_Event_Num := null;
40 RAISE;
41 END Get_Next_Event_Num;
42
43
44 /*----------------------------------------------------------------------------+
45 | This Private Procedure Check_Create_Update_Event validates the funding lvl |
46 | and also if the event number is unique or not. |
47 +----------------------------------------------------------------------------*/
48
49 Procedure Check_Event_Action ( P_Project_Id IN NUMBER,
50 P_Task_ID IN NUMBER,
51 P_Event_Num IN NUMBER,
52 P_Event_Id IN NUMBER,
53 P_Event_Action IN VARCHAR2,
54 P_Action_Name IN VARCHAR2,
55 P_Init_Msg_List IN VARCHAR2,
56 P_Event_Num_Chg IN VARCHAR2,
57 P_Rec_Ver_Num IN NUMBER,
58 P_Mcb_Enabled_Flag IN VARCHAR2,
59 P_Pfc_Rate_Date_Code IN VARCHAR2,
60 P_Pc_Rate_Date_Code IN VARCHAR2,
61 P_Fc_Rate_Date_Code IN VARCHAR2,
62 P_Projfunc_Curr_Code IN VARCHAR2,
63 P_Project_Curr_Code IN VARCHAR2,
64 P_Bill_Trans_Curr_Code IN VARCHAR2,
65 P_Pfc_Rate_Type IN VARCHAR2,
66 P_Pc_Rate_Type IN VARCHAR2,
67 P_Fc_Rate_Type IN VARCHAR2,
68 P_Pfc_Rate_Date IN DATE,
69 P_Pc_Rate_Date IN DATE,
70 P_Fc_Rate_Date IN DATE,
71 P_Pfc_Excg_Rate IN NUMBER,
72 P_Pc_Excg_Rate IN NUMBER,
73 P_Fc_Excg_Rate IN NUMBER,
74 P_Event_Type IN VARCHAR2,
75 P_Bill_Txn_Cur IN VARCHAR2,
76 P_Invoice_Amt IN NUMBER,
77 P_Revenue_Amt IN NUMBER,
78 P_Event_Org IN NUMBER,
79 X_Msg_Data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
80 X_Msg_Count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
81 X_Return_Status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
82 AS
83
84 Invalid_event_action exception; /* Added for bug 3850381 */
85 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
86 l_err_message varchar2(240) := null;
87 l_event_processed varchar2(1) := 'Y';
88 l_rec_ver_num pa_events.record_version_number%type;
89 /* Added for bug 3850381 */
90 l_msg_count NUMBER := 0;
91 l_data VARCHAR2(2000) := null;
92 l_msg_data VARCHAR2(2000) := null;
93 l_msg_index_out NUMBER := 0;
94
95 BEGIN
96
97 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
98
99 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
100 FND_MSG_PUB.initialize;
101 END IF;
102
103 If P_Event_Action = 'Delete'
104 Then
105 l_event_processed :=PA_EVENT_UTILS.CHECK_EVENT_PROCESSED
106 (P_event_id => P_Event_Id );
107
108 If l_event_processed <> 'Y'
109 Then
110 x_return_status := FND_API.G_RET_STS_ERROR;
111 l_err_message := FND_MESSAGE.GET_STRING('PA', 'PA_TK_EVENT_IN_USE');
112 PA_UTILS.ADD_MESSAGE
113 ( p_app_short_name => 'PA',
114 p_msg_name => 'PA_ACTION_NAME_ERR',
115 p_token1 => 'ACTION_NAME',
116 p_value1 => P_Action_Name,
117 p_token2 => 'MESSAGE',
118 p_value2 => l_Err_Message);
119 End If;
120
121 Else
122
123 /* Added for bug 3935772 */
124 If P_Event_Type is null
125 Then
126 x_return_status := FND_API.G_RET_STS_ERROR;
127 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_NO_EVENT_TYPE');
128 PA_UTILS.ADD_MESSAGE
129 ( p_app_short_name => 'PA',
130 p_msg_name => 'PA_ACTION_NAME_ERR',
131 p_token1 => 'ACTION_NAME',
132 p_value1 => P_Action_Name,
133 p_token2 => 'MESSAGE',
134 p_value2 => l_Err_Message);
135 End If;
136
137 If P_Bill_Txn_Cur is null
138 Then
139 x_return_status := FND_API.G_RET_STS_ERROR;
140 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_INVALID_BIL_TRX_CUR_AMG');
141 PA_UTILS.ADD_MESSAGE
142 ( p_app_short_name => 'PA',
143 p_msg_name => 'PA_ACTION_NAME_ERR',
144 p_token1 => 'ACTION_NAME',
145 p_value1 => P_Action_Name,
146 p_token2 => 'MESSAGE',
147 p_value2 => l_Err_Message);
148 End If;
149
150 If P_Invoice_Amt is null
151 Then
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_INVALID_INV_AMT');
154 PA_UTILS.ADD_MESSAGE
155 ( p_app_short_name => 'PA',
156 p_msg_name => 'PA_ACTION_NAME_ERR',
157 p_token1 => 'ACTION_NAME',
158 p_value1 => P_Action_Name,
159 p_token2 => 'MESSAGE',
160 p_value2 => l_Err_Message);
161 End If;
162
163 If P_Revenue_Amt is null
164 Then
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_INVALID_REV_AMT');
167 PA_UTILS.ADD_MESSAGE
168 ( p_app_short_name => 'PA',
169 p_msg_name => 'PA_ACTION_NAME_ERR',
170 p_token1 => 'ACTION_NAME',
171 p_value1 => P_Action_Name,
172 p_token2 => 'MESSAGE',
173 p_value2 => l_Err_Message);
174 End If;
175
176 If P_Event_Org is null
177 Then
178 x_return_status := FND_API.G_RET_STS_ERROR;
179 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_INVALID_EVNT_ORG_AMG');
180 PA_UTILS.ADD_MESSAGE
181 ( p_app_short_name => 'PA',
182 p_msg_name => 'PA_ACTION_NAME_ERR',
183 p_token1 => 'ACTION_NAME',
184 p_value1 => P_Action_Name,
185 p_token2 => 'MESSAGE',
186 p_value2 => l_Err_Message);
187 End If;
188
189 IF X_Return_Status <> FND_API.G_RET_STS_SUCCESS
190 THEN
191 raise Invalid_event_action;
192 END IF;
193
194 /* End of changes for bug 3935772 */
195
196 If PA_EVENT_CORE.CHECK_FUNDING ( P_Project_Id,
197 P_Task_Id ) = 'N'
198 Then
199 x_return_status := FND_API.G_RET_STS_ERROR;
200 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_TASK_FUND_NO_PROJ_EVENT_AMG');
201 PA_UTILS.ADD_MESSAGE
202 ( p_app_short_name => 'PA',
203 p_msg_name => 'PA_ACTION_NAME_ERR',
204 p_token1 => 'ACTION_NAME',
205 p_value1 => P_Action_Name,
206 p_token2 => 'MESSAGE',
207 p_value2 => l_Err_Message);
208 End If;
209
210
211 If P_Event_Num_Chg = 'Y'
212 Then
213 If PA_EVENT_CORE.CHECK_VALID_EVENT_NUM( P_Project_Id,
214 P_Task_Id,
215 P_Event_Num) = 'N'
216 Then
217 x_return_status := FND_API.G_RET_STS_ERROR;
218 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_INV_EVNT_NUM_AMG');
219 PA_UTILS.ADD_MESSAGE
220 ( p_app_short_name => 'PA',
221 p_msg_name => 'PA_ACTION_NAME_ERR',
222 p_token1 => 'ACTION_NAME',
223 p_value1 => P_Action_Name,
224 p_token2 => 'MESSAGE',
225 p_value2 => l_Err_Message);
226 End If;
227 End If;
228
229 IF (P_Mcb_Enabled_Flag = 'Y' )
230 Then
231 If p_Bill_Trans_Curr_Code <> p_Projfunc_Curr_Code
232 Then
233
234 If (p_Pfc_Rate_Date_Code = 'FIXED_DATE' and p_Pfc_Rate_Type <> 'User' and p_Pfc_Rate_Date is null)
235 Then
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_INVALID_PROJFUNC_DATE_AMG');
238 PA_UTILS.ADD_MESSAGE
239 ( p_app_short_name => 'PA',
240 p_msg_name => 'PA_ACTION_NAME_ERR',
241 p_token1 => 'ACTION_NAME',
242 p_value1 => P_Action_Name,
243 p_token2 => 'MESSAGE',
244 p_value2 => l_Err_Message);
245 End If;
246
247 If (p_Pfc_Rate_Type = 'User' and nvl(p_Pfc_Excg_Rate, 0) <= 0)
248 Then
249 x_return_status := FND_API.G_RET_STS_ERROR;
250 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_EXCH_RATE_NULL_PF_AMG');
251 PA_UTILS.ADD_MESSAGE
252 ( p_app_short_name => 'PA',
253 p_msg_name => 'PA_ACTION_NAME_ERR',
254 p_token1 => 'ACTION_NAME',
255 p_value1 => P_Action_Name,
256 p_token2 => 'MESSAGE',
257 p_value2 => l_Err_Message);
258 End If;
259
260 End If;
261
262 If (p_Bill_Trans_Curr_Code <> p_Project_Curr_Code and p_Project_Curr_Code <> p_Projfunc_Curr_Code)
263 Then
264
265 If (p_Pc_Rate_Date_Code = 'FIXED_DATE' and p_Pc_Rate_Type <> 'User' and p_Pc_Rate_Date is null)
266 Then
267 x_return_status := FND_API.G_RET_STS_ERROR;
268 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_INVALID_PROJ_DATE_AMG');
269 PA_UTILS.ADD_MESSAGE
270 ( p_app_short_name => 'PA',
271 p_msg_name => 'PA_ACTION_NAME_ERR',
272 p_token1 => 'ACTION_NAME',
273 p_value1 => P_Action_Name,
274 p_token2 => 'MESSAGE',
275 p_value2 => l_Err_Message);
276 End If;
277
278 If (p_Pc_Rate_Type = 'User' and nvl(p_Pc_Excg_Rate, 0) <= 0 )
279 Then
280 x_return_status := FND_API.G_RET_STS_ERROR;
281 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_EXCH_RATE_NULL_PC_AMG');
282 PA_UTILS.ADD_MESSAGE
283 ( p_app_short_name => 'PA',
284 p_msg_name => 'PA_ACTION_NAME_ERR',
285 p_token1 => 'ACTION_NAME',
286 p_value1 => P_Action_Name,
287 p_token2 => 'MESSAGE',
288 p_value2 => l_Err_Message);
289 End If;
290
291 End If;
292
293 If (p_Fc_Rate_Date_Code = 'FIXED_DATE' and p_Fc_Rate_Type <> 'User' and p_Fc_Rate_Date is null)
294 Then
295 x_return_status := FND_API.G_RET_STS_ERROR;
296 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_INVALID_FUND_DATE_AMG');
297 PA_UTILS.ADD_MESSAGE
298 ( p_app_short_name => 'PA',
299 p_msg_name => 'PA_ACTION_NAME_ERR',
300 p_token1 => 'ACTION_NAME',
301 p_value1 => P_Action_Name,
302 p_token2 => 'MESSAGE',
303 p_value2 => l_Err_Message);
304 End If;
305
306 If ( p_Fc_Rate_Type = 'User' and nvl(p_Fc_Excg_Rate, 0) <= 0 )
307 Then
308 x_return_status := FND_API.G_RET_STS_ERROR;
309 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_FUND_EXCG_RATE_INV_AMG');
310 PA_UTILS.ADD_MESSAGE
311 ( p_app_short_name => 'PA',
312 p_msg_name => 'PA_ACTION_NAME_ERR',
313 p_token1 => 'ACTION_NAME',
314 p_value1 => P_Action_Name,
315 p_token2 => 'MESSAGE',
316 p_value2 => l_Err_Message);
317 End If;
318
319 End If;
320
321
322 If P_Event_Action = 'Update'
323 Then
324 Begin
325 select record_version_number
326 into l_rec_ver_num
327 from pa_events
328 where event_id = p_event_id;
329
330 If l_rec_ver_num <> p_rec_ver_num
331 Then
332 x_return_status := FND_API.G_RET_STS_ERROR;
333 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_XC_RECORD_CHANGED');
334 PA_UTILS.ADD_MESSAGE
335 ( p_app_short_name => 'PA',
336 p_msg_name => 'PA_ACTION_NAME_ERR',
337 p_token1 => 'ACTION_NAME',
338 p_value1 => P_Action_Name,
339 p_token2 => 'MESSAGE',
340 p_value2 => l_Err_Message);
341 End If;
342 Exception When others then
346 End If;
343 l_return_status := FND_API.G_RET_STS_ERROR;
344 /* raise; */
345 End;
347
348 End If;
349
350 IF X_Return_Status <> FND_API.G_RET_STS_SUCCESS
351 THEN
352 raise Invalid_event_action;
353 END IF;
354
355 EXCEPTION
356 WHEN Invalid_event_action THEN
357 x_return_status := FND_API.G_RET_STS_ERROR;
358 l_msg_count := FND_MSG_PUB.count_msg;
359
360 IF l_msg_count = 1 THEN
361 PA_INTERFACE_UTILS_PUB.get_messages
362 (p_encoded => FND_API.G_TRUE,
363 p_msg_index => 1,
364 p_msg_count => l_msg_count,
365 p_msg_data => l_msg_data,
366 p_data => l_data,
367 p_msg_index_out => l_msg_index_out);
368 x_msg_data := l_data;
369 x_msg_count := l_msg_count;
370 ELSE
371 x_msg_count := l_msg_count;
372 END IF;
373 RETURN;
374
375 WHEN OTHERS THEN
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377 x_msg_count := 1;
378 x_msg_data := SQLERRM;
379
380 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_BILLING_WRKBNCH_EVENTS'
381 ,p_procedure_name => 'CHECK_EVENT_ACTION');
382
383 RETURN;
384 /* RAISE; */
385 END Check_Event_Action;
386
387 /*----------------------------------------------------------------------------+
388 | This Private Function Check_Delv_Event_Processed returns 'Y' if an event |
389 | associated with an action processed else returns 'N'.
390 +----------------------------------------------------------------------------*/
391 Function Check_Delv_Event_Processed ( P_Project_Id IN NUMBER,
392 P_Deliverable_Id IN NUMBER,
393 P_Action_Id IN NUMBER)
394 RETURN VARCHAR2
395 IS
396
397 l_return_status varchar2(1) := 'Y';
398 l_event_Id pa_events.event_id%type;
399
400 BEGIN
401
402 SELECT event_id
403 INTO l_event_id
404 FROM pa_events
405 WHERE project_id = P_Project_Id
406 AND deliverable_id = P_Deliverable_Id
407 AND action_id = P_Action_Id;
408
409 l_return_status :=PA_EVENT_UTILS.CHECK_EVENT_PROCESSED
410 (P_event_id => l_event_Id );
411
412 IF l_return_status in ('N', 'P', 'C')
413 Then
414 return 'Y';
415 else
416 return 'N';
417 End If;
418
419 EXCEPTION WHEN OTHERS
420 THEN
421 return 'N';
422 END Check_Delv_Event_Processed;
423
424 /*----------------------------------------------------------------------------+
425 | This Private Function Check_Delv_Event_Processed returns 'Y' if an event |
426 | associated with an action processed else returns 'N'.
427 +----------------------------------------------------------------------------*/
428 Procedure Delete_Delv_Event ( P_Project_Id IN NUMBER,
429 P_Deliverable_Id IN NUMBER,
430 P_Action_Id IN NUMBER,
431 P_Action_Name IN VARCHAR2,
432 X_Return_Status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
433 AS
434
435 l_tmp_return_status varchar2(1) := null;
436 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
437 l_err_message varchar2(240) := null;
438 l_tmp_rowid rowid;
439 cursor c_rowid is
440 select rowid from pa_events
441 where project_id = P_Project_Id
442 and deliverable_id = P_Deliverable_Id
443 and action_id = P_Action_Id;
444
445 BEGIN
446 l_tmp_return_status := Check_Delv_Event_Processed ( P_Project_Id
447 ,P_Deliverable_Id
448 ,P_Action_Id);
449
450 IF l_tmp_return_status = 'Y'
451 Then
452 l_err_message := FND_MESSAGE.GET_STRING('PA', 'PA_TK_EVENT_IN_USE');
453 PA_UTILS.ADD_MESSAGE
454 ( p_app_short_name => 'PA',
455 p_msg_name => 'PA_TK_EVENT_IN_USE',
456 p_token1 => 'ACTION_NAME',
457 p_value1 => P_Action_Name,
458 p_token2 => 'MESSAGE',
459 p_value2 => l_Err_Message);
460 l_return_status := FND_API.G_RET_STS_ERROR;
461 Else
462 open c_rowid;
463 fetch c_rowid into l_tmp_rowid;
464
465 if c_rowid%rowcount = 1
466 then
467 PA_EVENTS_PKG.Delete_Row (X_RowId => l_tmp_rowid);
468 end if;
469
470 close c_rowid;
471
472 End If;
473 X_Return_Status := l_return_status;
474 EXCEPTION WHEN OTHERS
475 THEN
476 raise;
477 END Delete_Delv_Event;
478
479
480 Procedure Get_Proj_Carry_Out_Org ( P_Project_ID IN NUMBER,
481 X_Org_ID OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
482 X_Org_Name OUT NOCOPY VARCHAR2 ) AS --File.Sql.39 bug 4440895
483 Begin
484
485 SELECT to_char(o.organization_id),
486 o.name
487 INTO X_Org_ID,
488 X_Org_Name
492
489 FROM pa_projects p, hr_organization_units o
490 WHERE p.carrying_out_organization_id = o.organization_id
491 AND p.project_id = P_Project_ID;
493 Exception WHEN OTHERS
494 THEN
495 NULL;
496 End Get_Proj_Carry_Out_Org;
497
498
499 Function CHECK_BILLING_EVENT_EXISTS
500 (
501 p_project_id IN pa_projects_all.project_id%TYPE,
502 p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
503 ) RETURN VARCHAR2
504 IS
505
506 l_Deliverable_Id pa_events.deliverable_id%TYPE;
507 l_Dlv_Count Number := 0;
508
509 Begin
510
511 select element_version_id
512 into l_Deliverable_Id
513 from pa_proj_element_versions
514 where proj_element_id = p_dlv_element_id
518 If nvl(l_Deliverable_Id, 0) > 0 Then
515 and object_type = 'PA_DELIVERABLES'
516 and project_id = p_project_id;
517
519
520 Select count(*)
521 into l_Dlv_Count
522 from pa_events e
523 where e.project_id = p_project_id
524 and e.deliverable_id = l_deliverable_id;
525 End If;
526
527 If nvl(l_Dlv_Count, 0) > 0 Then
528 return('Y');
529 Else
530 return('N');
531 End If;
532
533 Exception when others Then
534 return('N');
535
536 End CHECK_BILLING_EVENT_EXISTS;
537
538
539 /* Added for bug 3941159 */
540 Procedure Upd_Event_Comp_Date
541 (
542 P_Deliverable_Id IN NUMBER,
543 P_Action_Id IN NUMBER,
544 P_Event_Date IN DATE
545 )
546 IS
547 BEGIN
548 IF nvl(P_Deliverable_Id, 0) > 0 AND nvl(P_Action_Id, 0) > 0
549 Then
550 Update PA_EVENTS
551 set Completion_Date = trunc(P_Event_Date)
552 Where deliverable_id = P_Deliverable_Id
553 and action_id = P_Action_Id;
554 End If;
555 EXCEPTION WHEN OTHERS THEN
556 null;
557 END Upd_Event_Comp_Date;
558
559 /*------------- End of Public Procedure/Function Declarations ----------------*/
560
561 end PA_Billing_Wrkbnch_Events;