[Home] [Help]
PACKAGE BODY: APPS.PA_EVENT_PVT
Source
1 PACKAGE BODY PA_EVENT_PVT AS
2 /* $Header: PAEVAPVB.pls 120.6.12010000.3 2009/05/26 10:50:26 nkapling ship $ */
3 g_inv_evt_fun_allowed VARCHAR2(1) DEFAULT NULL;
4 g_rev_evt_fun_allowed VARCHAR2(1) DEFAULT NULL;
5
6 -- ============================================================================
7 --
8 --Name: check_mdty_params1
9 --Type: Procedure
10 --Description: This function validates the mandatory parameters1 which
11 -- includes
12 -- 1.product code,
13 -- 2.function security ,
14 -- 3.api compatibility.
15 --
16 --Called subprograms:FND_API.Compatible_API_Call
17 -- PA_PM_FUNCTION_SECURITY_PUB.check_function_security
18 --
19 --
20 --
21 --History:
22
23 -- ============================================================================
24 PROCEDURE CHECK_MDTY_PARAMS1
25 ( p_api_version_number IN NUMBER
26 ,p_api_name IN VARCHAR2
27 ,p_pm_product_code IN VARCHAR2
28 ,p_function_name IN VARCHAR2
29 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
30 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
31 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
32
33 AS
34
35 Cursor ProductCode_cur(P_pm_product_code VARCHAR2)
36 Is
37 select 1
38 from pa_lookups
39 where lookup_type ='PM_PRODUCT_CODE'
40 and lookup_code=P_pm_product_code;
41
42 l_product_code NUMBER:=NULL;
43 l_resp_id NUMBER := 0;
44 l_function_allowed VARCHAR2(1);
45 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
46
47 BEGIN
48
49
50 --Log Message
51 IF l_debug_mode = 'Y' THEN
52 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS1.begin'
53 ,x_msg => 'Beginning of Check Mandatory Parameters1 '
54 ,x_log_level => 5);
55 END IF;
56
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58
59
60 -- Standard call to check for call compatibility.
61
62 --Log Message
63 IF l_debug_mode = 'Y' THEN
64 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS1.begin'
65 ,x_msg => 'Beginning of api compatibility check '
66 ,x_log_level => 5);
67 End If;
68
69 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
70 p_api_version_number ,
71 p_api_name ,
72 G_PKG_PVT )
73 THEN
74
75 x_return_status := FND_API.G_RET_STS_ERROR;
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77
78 END IF;
79
80 --Log Message
81 IF l_debug_mode = 'Y' THEN
82 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS1.begin'
83 ,x_msg => 'Beginning of Function security Check'
84 ,x_log_level => 5);
85 End If;
86
87 l_resp_id := FND_GLOBAL.Resp_id;
88
89 -- Actions performed using the APIs would be subject to
90 -- function security. If the responsibility does not allow
91 -- such functions to be executed, the API should not proceed further
92 -- since the user does not have access to such functions
93
94 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
95 ( p_api_version_number => p_api_version_number
96 ,p_responsibility_id => l_resp_id
97 ,p_function_name => p_function_name
98 ,p_msg_count => x_msg_count
99 ,p_msg_data => x_msg_data
100 ,p_return_status => x_return_status
101 ,p_function_allowed => l_function_allowed );
102
103 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
104 THEN
105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
106
107 ELSIF x_return_status = FND_API.G_RET_STS_ERROR
108 THEN
109 RAISE FND_API.G_EXC_ERROR;
110 END IF;
111
112 IF l_function_allowed = 'N' THEN
113 pa_interface_utils_pub.map_new_amg_msg
114 ( p_old_message_code => 'PA_FUNC_SECURITY_ENFORCED'
115 ,p_msg_attribute => 'CHANGE'
116 ,p_resize_flag => 'Y'
117 ,p_msg_context => 'GENERAL'
118 ,p_attribute1 => ''
119 ,p_attribute2 => ''
120 ,p_attribute3 => ''
121 ,p_attribute4 => ''
122 ,p_attribute5 => '');
123 x_return_status := FND_API.G_RET_STS_ERROR;
124 RAISE FND_API.G_EXC_ERROR;
125 --For Bug 3619483
126 END IF;
127
128 IF fnd_function.test('PA_PAXINEVT_MAINT_INV_EVENTS') THEN
129 g_inv_evt_fun_allowed := 'Y';
130 ELSE
131 g_inv_evt_fun_allowed := 'N';
132 END IF;
133
134 IF fnd_function.test('PA_PAXINEVT_MAINT_REV_EVENTS') THEN
135 g_rev_evt_fun_allowed := 'Y';
136 ELSE
137 g_rev_evt_fun_allowed := 'N';
138 END IF;
139 IF (g_inv_evt_fun_allowed = 'N') AND (g_rev_evt_fun_allowed = 'N') THEN
140 pa_interface_utils_pub.map_new_amg_msg
141 ( p_old_message_code => 'PA_EV_NO_PRV_MAINT_AMG'
142 ,p_msg_attribute => 'CHANGE'
143 ,p_resize_flag => 'Y'
144 ,p_msg_context => 'GENERAL'
145 ,p_attribute1 => ''
146 ,p_attribute2 => ''
147 ,p_attribute3 => ''
148 ,p_attribute4 => ''
149 ,p_attribute5 => '');
150 x_return_status := FND_API.G_RET_STS_ERROR;
151 RAISE FND_API.G_EXC_ERROR;
152 END IF;
153
154 --END OF BUG FIX 3619483
155 --Log Message
156 IF l_debug_mode = 'Y' THEN
157 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS1.begin'
158 ,x_msg => 'Validating Product Code'
159 ,x_log_level => 5);
160 End If;
161
162 -- CHECK WHETHER MANDATORY INCOMING PARAMETER PRODUCT CODE EXIST
163 IF (p_pm_product_code IS NULL)
164 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
165 THEN
166 IF (p_pm_product_code IS NOT NULL AND p_function_name <> 'PA_EV_UPDATE_EVENT') /* Added for bug 5056969 */
167 THEN
168 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
169 THEN
170 pa_interface_utils_pub.map_new_amg_msg
171 ( p_old_message_code => 'PA_PRODUCT_CODE_IS_MISS'
172 ,p_msg_attribute => 'CHANGE'
173 ,p_resize_flag => 'N'
174 ,p_msg_context => 'GENERAL'
175 ,p_attribute1 => ''
176 ,p_attribute2 => ''
177 ,p_attribute3 => ''
178 ,p_attribute4 => ''
179 ,p_attribute5 => '');
180 END IF;
181 x_return_status := FND_API.G_RET_STS_ERROR;
182 RAISE FND_API.G_EXC_ERROR;
183 End IF; /* End of 'If' Added for bug 5056969 */
184 ELSE
185 Open ProductCode_cur(P_pm_product_code);
186 Fetch ProductCode_cur Into l_product_code;
187 Close ProductCode_cur;
188
189 If l_product_code Is NULL
190 Then
191 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
192 THEN
193 pa_interface_utils_pub.map_new_amg_msg
194 ( p_old_message_code => 'PA_INVALID_PRODUCT_CODE'
195 ,p_msg_attribute => 'CHANGE'
196 ,p_resize_flag => 'N'
197 ,p_msg_context => 'GENERAL'
198 ,p_attribute1 => ''
199 ,p_attribute2 => ''
200 ,p_attribute3 => ''
201 ,p_attribute4 => ''
202 ,p_attribute5 => '');
203 END IF;
204 x_return_status := FND_API.G_RET_STS_ERROR;
205 RAISE FND_API.G_EXC_ERROR;
206 End If;
207 END IF;
208
209 IF l_debug_mode = 'Y' THEN
210 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS1.begin'
211 ,x_msg => 'End of Check Mandatory Parameters1 '
212 ,x_log_level => 5);
213 END IF;
214
215 --handling exceptions
216 Exception
217 WHEN FND_API.G_EXC_ERROR
218 THEN
219 RAISE FND_API.G_EXC_ERROR;
220
221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
222 THEN
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224
225 When pa_event_utils.pvt_excp
226 then
227 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
228 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'check_mdty_params1->';
229 Raise pub_excp;--raising exception to be handled in public package
230
231
232 When others
233 then
234 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
235 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'check_mdty_params1->';
236 Raise pub_excp;--raising exception to be handled in public package
237
238 END check_mdty_params1;
239
240 -- ============================================================================
241 --
242 --Name:
243 --Type:
244 --Description: This API checks mandatory input parameters for null or '^'.
245 -- The mandatory parameters are
246 -- 1.event reference,
247 -- 2.project number,
248 -- 3.event type ,
249 -- 4.event organization
250 -- during creation of new event.
251 -- And event reference for update and delete of an event.
252 --
253 --Called subprograms:PA_EVENT_PVT.CHECK_EVENT_REF_UNQ
254 --
255 -- ============================================================================
256 PROCEDURE CHECK_MDTY_PARAMS2
257 ( p_pm_event_reference IN VARCHAR2
258 ,P_pm_product_code IN VARCHAR2
259 ,p_project_number IN VARCHAR2
260 ,p_event_type IN VARCHAR2
261 ,p_organization_name IN VARCHAR2
262 ,p_calling_place IN VARCHAR2
263 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
264
265 AS
266
267 l_debug_mode VARCHAR2(1):= NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
268
269 BEGIN
270
271 --Log Message
272 IF l_debug_mode = 'Y' THEN
273 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS2.begin'
274 ,x_msg => 'Beginning of Check Mandatory Parameters2 '
275 ,x_log_level => 5);
276 END IF;
277
278 --Initialising return status
279 x_return_status :=FND_API.G_RET_STS_SUCCESS;
280
281 --Log Message
282 IF l_debug_mode = 'Y' THEN
283 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS2.begin'
284 ,x_msg => 'Checking event reference for null or ^ '
285 ,x_log_level => 5);
286 END IF;
287
288 IF (p_pm_event_reference IS NULL
289 OR p_pm_event_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
290 THEN
291 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
292 THEN
293 pa_interface_utils_pub.map_new_amg_msg
294 ( p_old_message_code =>'PA_EVENT_REF_IS_MISS'
295 ,p_msg_attribute => 'CHANGE'
296 ,p_resize_flag => 'N'
297 ,p_msg_context => 'EVENT'
298 ,p_attribute1 => null
299 ,p_attribute2 => ''
300 ,p_attribute3 => ''
301 ,p_attribute4 => ''
302 ,p_attribute5 => '');
303 END IF;
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 RAISE FND_API.G_EXC_ERROR;
306 End If;
307
308 --Log Message
309 IF l_debug_mode = 'Y' THEN
310 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS2.begin'
311 ,x_msg => 'Start of Validations for Create Event '
312 ,x_log_level => 5);
313 END IF;
314
315 --Start of validations required for creating events
316 IF (p_calling_place='CREATE_EVENT') then
317
318 --Log Message
319 IF l_debug_mode = 'Y' THEN
320 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS2.begin'
321 ,x_msg => 'Validating Uniqueness of event reference '
322 ,x_log_level => 5);
323 END IF;
324
325 --Calls PA_EVENT_PVT.CHECK_EVENT_REF_UNQ to check the event reference is unique
326 --and not present in Oracle Projects DB for the given pm_product_code
327 If PA_EVENT_PVT.CHECK_EVENT_REF_UNQ
328 (P_pm_product_code =>P_pm_product_code
329 ,P_pm_event_reference =>P_pm_event_reference)='N'
330 Then
331 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
332 THEN
333 pa_interface_utils_pub.map_new_amg_msg
334 ( p_old_message_code => 'PA_EVENT_REF_IS_NOT_UNQ'
335 ,p_msg_attribute => 'CHANGE'
336 ,p_resize_flag => 'N'
337 ,p_msg_context => 'EVENT'
338 ,p_attribute1 => p_pm_event_reference
339 ,p_attribute2 => ''
340 ,p_attribute3 => ''
341 ,p_attribute4 => ''
342 ,p_attribute5 => '');
343 END IF;
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 RAISE FND_API.G_EXC_ERROR;
346 End If;
347
348 --Log Message
349 IF l_debug_mode = 'Y' THEN
350 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS2.begin'
351 ,x_msg => 'Checking project number for null or ^ '
352 ,x_log_level => 5);
353 END IF;
354
355 IF (p_project_number IS NULL
356 OR p_project_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
357 THEN
358 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
359 THEN
360 pa_interface_utils_pub.map_new_amg_msg
361 ( p_old_message_code => 'PA_PROJ_NUM_IS_MISS'
362 ,p_msg_attribute => 'CHANGE'
363 ,p_resize_flag => 'N'
364 ,p_msg_context => 'EVENT'
365 ,p_attribute1 => p_pm_event_reference
366 ,p_attribute2 => ''
367 ,p_attribute3 => ''
368 ,p_attribute4 => ''
369 ,p_attribute5 => '');
370 END IF;
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 END IF;
373
374 --Log Message
375 IF l_debug_mode = 'Y' THEN
376 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS2.begin'
377 ,x_msg => 'Checking Event type for null or ^ '
378 ,x_log_level => 5);
379 END IF;
380
381 IF (p_event_type IS NULL
382 OR p_event_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
383 THEN
384 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
385 THEN
386 pa_interface_utils_pub.map_new_amg_msg
387 ( p_old_message_code => 'PA_EVNT_TYPE_IS_MISS'
388 ,p_msg_attribute => 'CHANGE'
389 ,p_resize_flag => 'N'
390 ,p_msg_context => 'EVENT'
391 ,p_attribute1 => p_pm_event_reference
392 ,p_attribute2 => ''
393 ,p_attribute3 => ''
394 ,p_attribute4 => ''
395 ,p_attribute5 => '');
396 END IF;
397 x_return_status := FND_API.G_RET_STS_ERROR;
398 END IF;
399
400 --Log Message
401 IF l_debug_mode = 'Y' THEN
402 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS2.begin'
403 ,x_msg => 'Checking organization name for null or ^ '
404 ,x_log_level => 5);
405 END IF;
406
407 IF (p_organization_name IS NULL
408 OR p_organization_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
409 THEN
410 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
411 THEN
412 pa_interface_utils_pub.map_new_amg_msg
413 ( p_old_message_code => 'PA_EVNT_ORG_IS_MISS'
414 ,p_msg_attribute => 'CHANGE'
415 ,p_resize_flag => 'N'
416 ,p_msg_context => 'EVENT'
417 ,p_attribute1 => p_pm_event_reference
418 ,p_attribute2 => ''
419 ,p_attribute3 => ''
420 ,p_attribute4 => ''
421 ,p_attribute5 => '');
422 END IF;
423 x_return_status := FND_API.G_RET_STS_ERROR;
424 END IF;
425
426 --Log Message
427 IF l_debug_mode = 'Y' THEN
428 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS2.begin'
429 ,x_msg => 'End of Validations for Create Event '
430 ,x_log_level => 5);
431 END IF;
432
433 END IF; /* end of validations for create_event */
434
435 IF l_debug_mode = 'Y' THEN
436 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_MDTY_PARAMS1.begin'
437 ,x_msg => 'End of Check Mandatory Parameters2'
438 ,x_log_level => 5);
439 END IF;
440
441 --handling exceptions
442 Exception
443 WHEN FND_API.G_EXC_ERROR
444 THEN
445 RAISE FND_API.G_EXC_ERROR;
446
447 When pa_event_utils.pvt_excp
448 then
449 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
450 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'CHECK_MDTY_PARAMS2->';
451 Raise pub_excp;--raising exception to be handled in public package
452
453 When others
454 then
455 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
456 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CHECK_MDTY_PARAMS2->';
457 Raise pub_excp;--raising exception to be handled in public package
458
459
460 END CHECK_MDTY_PARAMS2;
461
462 -- ============================================================================
463 --
464 --Name: CHECK_CREATE_EVENT_OK
465 --Type: function
466 --Description: This function validates if the event is valid and can be created.
467 --
468 --Called subprograms:
469 -- PA_EVENT_UTILS.CHECK_VALID_TASK
470 -- PA_EVENT_UTILS.CHECK_VALID_EVENT_TYPE
471 -- PA_EVENT_UTILS.CHECK_VALID_EVENT_ORG
472 -- PA_EVENT_UTILS.CHECK_VALID_REV_AMT
473 -- PA_EVENT_UTILS.CHECK_VALID_BILL_AMT
474 -- PA_EVENT_UTILS.CHECK_VALID_EVENT_NUM
475 -- PA_EVENT_UTILS.CHECK_VALID_INV_ORG
476 -- PA_EVENT_UTILS.CHECK_VALID_INV_ITEM
477 -- PA_EVENT_UTILS.CHECK_VALID_CURR
478 -- PA_EVENT_UTILS.CHECK_VALID_FUND_RATE_TYPE
479 -- PA_EVENT_UTILS.CHECK_VALID_PROJ_RATE_TYPE
480 -- PA_EVENT_UTILS.CHECK_VALID_PFC_RATE_TYPE
481 -- PA_EVENT_UTILS.CHECK_VALID_AGREEMENT
482 -- PA_EVENT_UTILS.CHECK_VALID_EVENT_DATE
483 -- ============================================================================
484 FUNCTION CHECK_CREATE_EVENT_OK
485 (P_pm_product_code IN VARCHAR2
486 ,P_event_in_rec IN pa_event_pub.Event_rec_in_type
487 ,P_project_currency_code IN VARCHAR2
488 ,P_proj_func_currency_code IN VARCHAR2
489 ,P_project_bil_rate_date_code IN VARCHAR2
490 ,P_project_rate_type IN VARCHAR2
491 ,p_project_bil_rate_date IN VARCHAR2
492 ,p_projfunc_bil_rate_date_code IN VARCHAR2
493 ,P_projfunc_rate_type IN VARCHAR2
494 ,p_projfunc_bil_rate_date IN VARCHAR2
495 ,P_funding_rate_type IN VARCHAR2
496 ,P_multi_currency_billing_flag IN VARCHAR2
497 ,p_project_id IN NUMBER
498 ,p_projfunc_bil_exchange_rate IN NUMBER -- Added for bug 3013137
499 ,p_funding_bil_rate_date_code IN VARCHAR2 --Added for bug 3053190
500 ,x_task_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
501 ,x_organization_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
502 ,x_inv_org_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
503 ,x_agreement_id OUT NOCOPY NUMBER -- Federal Uptake
504 ,P_event_type_classification OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
505 RETURN VARCHAR2
506 IS
507
508 l_projfunc_rate_date pa_projects_all.projfunc_bil_rate_date%type;
509 l_project_rate_date pa_projects_all.project_bil_rate_date%type;
510 p_api_name VARCHAR2(100):='CHECK_CREATE_EVENT_OK';
511 l_funding_rate_type PA_EVENTS.funding_rate_type%TYPE;
512 l_proj_func_currency_code PA_EVENTS.projfunc_currency_code%TYPE;
513 l_return_status VARCHAR2(1):='Y';
514 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
515 l_ret_status VARCHAR2(2000):=NULL;
516 l_project_rate_type PA_EVENTS.project_rate_type%TYPE; -- Added for bug 3013137 and 3009307
517 l_projfunc_rate_type PA_EVENTS.projfunc_rate_type%TYPE; -- Added for bug 3013137 and 3009307
518
519 BEGIN
520 --Log Message
521 IF l_debug_mode = 'Y' THEN
522 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
523 ,x_msg => 'Beginning of Check Create Event Ok'
524 ,x_log_level => 5);
525 END IF;
526
527 --Log Message
528 IF l_debug_mode = 'Y' THEN
529 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
530 ,x_msg => 'Validating task for the given project'
531 ,x_log_level => 5);
532 END IF;
533
534 --validating the task
535 If (P_event_in_rec.P_task_number Is NOT NULL
536 AND P_event_in_rec.P_task_number <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
537 Then
538 If PA_EVENT_UTILS.check_valid_task
539 (P_project_id =>P_project_id
540 ,P_task_id =>x_task_id
541 ,P_task_num =>P_event_in_rec.P_task_number)='N'
542 Then
543 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
544 THEN
545 pa_interface_utils_pub.map_new_amg_msg
546 ( p_old_message_code => 'PA_INVALID_TOP_TASK'
547 ,p_msg_attribute => 'CHANGE'
548 ,p_resize_flag => 'N'
549 ,p_msg_context => 'EVENT'
550 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
551 ,p_attribute2 => ''
552 ,p_attribute3 => ''
553 ,p_attribute4 => ''
554 ,p_attribute5 => '');
555 END IF;
556 l_return_status:='N';
557 Return(l_return_status); --If task_id is invalid then terminate further validation
558 End If;
559 --Log Message
560 IF l_debug_mode = 'Y' THEN
561 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
562 ,x_msg => 'Validating funding level of the project'
563 ,x_log_level => 5);
564 END IF;
565 Else
566 x_task_id:=NULL;
567 End If;
568
569 --validating the funding level of the project.
570 If PA_EVENT_UTILS.check_funding
571 (P_project_id =>P_project_id
572 ,P_task_id =>x_task_id)='N'
573 Then
574 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
575 THEN
576 pa_interface_utils_pub.map_new_amg_msg
577 ( p_old_message_code => 'PA_TASK_FUND_NO_PROJ_EVENT'
578 ,p_msg_attribute => 'CHANGE'
579 ,p_resize_flag => 'N'
580 ,p_msg_context => 'EVENT'
581 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
582 ,p_attribute2 => ''
583 ,p_attribute3 => ''
584 ,p_attribute4 => ''
585 ,p_attribute5 => '');
586 END IF;
587 l_return_status:='N';
588 Return(l_return_status); --If task_id is invalid then terminate further validation
589 End If;
590
591 -- log Message -- Start of Federal Uptake
592 IF l_debug_mode = 'Y' THEN
593 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
594 ,x_msg => 'Validating agreement for the given project'
595 ,x_log_level => 5);
596 END IF;
597
598 --validating the agreement
599 If ( ( P_event_in_rec.P_agreement_number Is NOT NULL OR
600 P_event_in_rec.P_agreement_type IS NOT NULL OR
601 P_event_in_rec.P_customer_number IS NOT NULL )
602 AND( P_event_in_rec.P_agreement_number <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
603 Then
604 If PA_EVENT_UTILS.CHECK_VALID_AGREEMENT (
605 P_project_id => P_project_id
606 ,P_task_id => x_task_id
607 ,P_agreement_number => P_event_in_rec.P_agreement_number
608 ,P_agreement_type => P_event_in_rec.P_agreement_type
609 ,P_customer_number => P_event_in_rec.P_customer_number
610 ,P_agreement_id => x_agreement_id ) ='N'
611 Then
612 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
613 THEN
614 pa_interface_utils_pub.map_new_amg_msg
615 ( p_old_message_code => 'PA_INVALID_AGMT_NUM'
616 ,p_msg_attribute => 'CHANGE'
617 ,p_resize_flag => 'N'
618 ,p_msg_context => 'EVENT'
619 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
620 ,p_attribute2 => ''
621 ,p_attribute3 => ''
622 ,p_attribute4 => ''
623 ,p_attribute5 => '');
624 END IF;
625 l_return_status:='N';
626 Return(l_return_status); --If agreement_id is invalid then terminate further validation
627 End If;
628 End IF;
629
630 --Log Message
631 IF l_debug_mode = 'Y' THEN
632 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
633 ,x_msg => 'Validating event date is between agreement date'
634 ,x_log_level => 5);
635 END IF;
636
637 --validating event date
638 If ( P_event_in_rec.P_completion_date Is NOT NULL
639 AND P_event_in_rec.P_completion_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE )
640 Then
641 If PA_EVENT_UTILS.CHECK_VALID_EVENT_DATE (
642 P_event_date => P_event_in_rec.P_completion_date
643 ,P_agreement_id => x_agreement_id ) ='N'
644 Then
645 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
646 THEN
647 pa_interface_utils_pub.map_new_amg_msg
648 ( p_old_message_code => 'PA_INVALID_EVENT_DATE'
649 ,p_msg_attribute => 'CHANGE'
650 ,p_resize_flag => 'N'
651 ,p_msg_context => 'EVENT'
652 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
653 ,p_attribute2 => ''
654 ,p_attribute3 => ''
655 ,p_attribute4 => ''
656 ,p_attribute5 => '');
657 END IF;
658 l_return_status:='N';
659 Return(l_return_status); --If event date is not between agreement date
660 End If;
661 End IF;
662 -- End of Federal Uptake
663
664 --Log Message
665 IF l_debug_mode = 'Y' THEN
666 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
667 ,x_msg => 'Validating the event_type'
668 ,x_log_level => 5);
669 END IF;
670
671 If ( P_event_in_rec.P_context = 'D' )
672 Then
673 IF (P_event_in_rec.P_deliverable_id IS NULL OR
674 P_event_in_rec.P_deliverable_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
675 Then
676 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
677 THEN
678 pa_interface_utils_pub.map_new_amg_msg
679 ( p_old_message_code => 'PA_EV_DLV_ID_MISS'
680 ,p_msg_attribute => 'CHANGE'
681 ,p_resize_flag => 'N'
682 ,p_msg_context => 'EVENT'
683 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
684 ,p_attribute2 => ''
685 ,p_attribute3 => ''
686 ,p_attribute4 => ''
687 ,p_attribute5 => '');
688 END IF;
689 l_return_status:='N';
690 END If;
691
692 IF (P_event_in_rec.P_action_id IS NULL OR
693 P_event_in_rec.P_action_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
694 Then
695 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
696 THEN
697 pa_interface_utils_pub.map_new_amg_msg
698 ( p_old_message_code => 'PA_EV_ACT_ID_MISS'
699 ,p_msg_attribute => 'CHANGE'
700 ,p_resize_flag => 'N'
701 ,p_msg_context => 'EVENT'
702 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
703 ,p_attribute2 => ''
704 ,p_attribute3 => ''
705 ,p_attribute4 => ''
706 ,p_attribute5 => '');
707 END IF;
708 l_return_status:='N';
709 END If;
710 End If;
711
712 --validating the event_type
713 If PA_EVENT_UTILS.check_valid_event_type
714 (P_event_type =>P_event_in_rec.P_event_type
715 ,P_context =>P_event_in_rec.P_context
716 ,X_event_type_classification =>p_event_type_classification)='N'
717 Then
718 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
719 THEN
720 pa_interface_utils_pub.map_new_amg_msg
721 ( p_old_message_code => 'PA_INVALID_EVNT_TYPE'
722 ,p_msg_attribute => 'CHANGE'
723 ,p_resize_flag => 'N'
724 ,p_msg_context => 'EVENT'
725 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
726 ,p_attribute2 => ''
727 ,p_attribute3 => ''
728 ,p_attribute4 => ''
729 ,p_attribute5 => '');
730 END IF;
731 l_return_status:='N';
732 End If;
733 --For Bug 3619483
734 IF ((g_rev_evt_fun_allowed = 'N' ) AND
735 (p_event_type_classification IN('WRITE ON','WRITE OFF') )) THEN
736 pa_interface_utils_pub.map_new_amg_msg
737 ( p_old_message_code => 'PA_EV_NO_REV_MAINT_AMG'
738 ,p_msg_attribute => 'CHANGE'
739 ,p_resize_flag => 'Y'
740 ,p_msg_context => 'GENERAL'
741 ,p_attribute1 => ''
742 ,p_attribute2 => ''
743 ,p_attribute3 => ''
744 ,p_attribute4 => ''
745 ,p_attribute5 => '');
746 RAISE FND_API.G_EXC_ERROR;
747 END IF;
748
749 IF ((g_inv_evt_fun_allowed = 'N' ) AND
750 (p_event_type_classification IN('DEFERRED REVENUE','INVOICE REDUCTION','SCHEDULED PAYMENTS'))) THEN
751 pa_interface_utils_pub.map_new_amg_msg
752 ( p_old_message_code => 'PA_EV_NO_INV_MAINT_AMG'
753 ,p_msg_attribute => 'CHANGE'
754 ,p_resize_flag => 'Y'
755 ,p_msg_context => 'GENERAL'
756 ,p_attribute1 => ''
757 ,p_attribute2 => ''
758 ,p_attribute3 => ''
759 ,p_attribute4 => ''
760 ,p_attribute5 => '');
761 RAISE FND_API.G_EXC_ERROR;
762 END IF;
763
764 IF ((p_event_type_classification = 'MANUAL') AND
765 (g_inv_evt_fun_allowed = 'Y' ) AND
766 (g_rev_evt_fun_allowed = 'N' ) AND
767 (NVL(p_event_in_rec.P_bill_trans_rev_amount,0)<> 0)) THEN
768 pa_interface_utils_pub.map_new_amg_msg
769 ( p_old_message_code => 'PA_EV_NO_REV_MAINT_AMG'
770 ,p_msg_attribute => 'CHANGE'
771 ,p_resize_flag => 'Y'
772 ,p_msg_context => 'GENERAL'
773 ,p_attribute1 => ''
774 ,p_attribute2 => ''
775 ,p_attribute3 => ''
776 ,p_attribute4 => ''
777 ,p_attribute5 => '');
778 pa_interface_utils_pub.map_new_amg_msg
779 ( p_old_message_code => 'PA_INVALID_REV_AMT_AMG'
780 ,p_msg_attribute => 'CHANGE'
781 ,p_resize_flag => 'Y'
782 ,p_msg_context => 'GENERAL'
783 ,p_attribute1 => ''
784 ,p_attribute2 => ''
785 ,p_attribute3 => ''
786 ,p_attribute4 => ''
787 ,p_attribute5 => '');
788 RAISE FND_API.G_EXC_ERROR;
789 END IF;
790
791 IF ((p_event_type_classification = 'MANUAL') AND
792 (g_inv_evt_fun_allowed = 'N' ) AND
793 (g_rev_evt_fun_allowed = 'Y' ) AND
794 (NVL(p_event_in_rec.P_bill_trans_bill_amount,0) <> 0 )) THEN
795 pa_interface_utils_pub.map_new_amg_msg
796 ( p_old_message_code => 'PA_EV_NO_INV_MAINT_AMG'
797 ,p_msg_attribute => 'CHANGE'
798 ,p_resize_flag => 'Y'
799 ,p_msg_context => 'GENERAL'
800 ,p_attribute1 => ''
801 ,p_attribute2 => ''
802 ,p_attribute3 => ''
803 ,p_attribute4 => ''
804 ,p_attribute5 => '');
805 pa_interface_utils_pub.map_new_amg_msg
806 ( p_old_message_code => 'PA_INVALID_BILL_AMT_AMG'
807 ,p_msg_attribute => 'CHANGE'
808 ,p_resize_flag => 'Y'
809 ,p_msg_context => 'GENERAL'
810 ,p_attribute1 => ''
811 ,p_attribute2 => ''
812 ,p_attribute3 => ''
813 ,p_attribute4 => ''
814 ,p_attribute5 => '');
815 RAISE FND_API.G_EXC_ERROR;
816 END IF;
817
818 --End of Bug fix3619483
819
820 --Log Message
821 IF l_debug_mode = 'Y' THEN
822 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
823 ,x_msg => 'Validating organisation name and deriving organisation_id'
824 ,x_log_level => 5);
825 END IF;
826 --calls PA_EVENT_UTILS.CHECK_VALID_EVENT_ORG to validate and
827 --derive the organisation_id from organisation name
828 If PA_EVENT_UTILS.check_valid_event_org
829 (P_event_org_name =>p_event_in_rec.P_organization_name
830 ,P_event_org_id =>x_Organization_Id)='N'
831 Then
832 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
833 THEN
834 pa_interface_utils_pub.map_new_amg_msg
835 ( p_old_message_code => 'PA_INVALID_EVNT_ORG'
836 ,p_msg_attribute => 'CHANGE'
837 ,p_resize_flag => 'N'
838 ,p_msg_context => 'EVENT'
839 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
840 ,p_attribute2 => ''
841 ,p_attribute3 => ''
842 ,p_attribute4 => ''
843 ,p_attribute5 => '');
844 END IF;
845 l_return_status:='N';
846 End If;
847
848
849 --validating the currency fields if mcb is enabled
850 If (P_multi_currency_billing_flag ='Y')
851 Then
852
853 --Log Message
854 IF l_debug_mode = 'Y' THEN
855 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
856 ,x_msg => 'Start of MCB Validations '
857 ,x_log_level => 5);
858 END IF;
859
860 --Log Message
861 IF l_debug_mode = 'Y' THEN
862 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
863 ,x_msg => 'Validating bill trans currency code'
864 ,x_log_level => 5);
865 END IF;
866
867 --If bill trans currency code is null
868 --then the default value is used for further validation.
869 If (p_event_in_rec.P_bill_trans_currency_code Is NULL)
870 Then
871 l_proj_func_currency_code:=P_proj_func_currency_code;
872 ElsIf PA_EVENT_UTILS.check_valid_curr
873 (P_bill_trans_curr =>p_event_in_rec.P_bill_trans_currency_code)='N'
874 Then
875 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
876 THEN
877 pa_interface_utils_pub.map_new_amg_msg
878 (p_old_message_code => 'PA_INVALID_BIL_TRX_CUR'
879 ,p_msg_attribute => 'CHANGE'
880 ,p_resize_flag => 'N'
881 ,p_msg_context => 'EVENT'
882 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
883 ,p_attribute2 => ''
884 ,p_attribute3 => ''
885 ,p_attribute4 => ''
886 ,p_attribute5 => '');
887 END IF;
888 l_return_status:='N';
889 l_proj_func_currency_code:=P_proj_func_currency_code;
890 Else
891 l_proj_func_currency_code:=p_event_in_rec.P_bill_trans_currency_code;
892 End If;
893
894 --Log Message
895 IF l_debug_mode = 'Y' THEN
896 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
897 ,x_msg => 'Validating funding rate type'
898 ,x_log_level => 5);
899 END IF;
900
901 --If funding rate type is null or '^' then the default value is used for other validations.
902 If (P_event_in_rec.P_funding_rate_type Is NOT NULL
903 AND P_event_in_rec.P_funding_rate_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
904 Then
905 If PA_EVENT_UTILS.check_valid_fund_rate_type
906 (P_fund_rate_type =>P_event_in_rec.P_funding_rate_type,
907 x_fund_rate_type =>l_funding_rate_type)='N'
908 Then
909 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
910 THEN
911 pa_interface_utils_pub.map_new_amg_msg
912 ( p_old_message_code => 'PA_FUND_RATE_TYPE_INV'
913 ,p_msg_attribute => 'CHANGE'
914 ,p_resize_flag => 'N'
915 ,p_msg_context => 'EVENT'
916 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
917 ,p_attribute2 => ''
918 ,p_attribute3 => ''
919 ,p_attribute4 => ''
920 ,p_attribute5 => '');
921 END IF;
922 l_return_status:='N';
923 End If;
924 ELSE l_funding_rate_type := P_funding_rate_type; -- Added for bug 3013137 and 3009307
925
926 End If;
927
928 -- When fuding rate type is 'User' at event level and not 'User' at the project level
929 -- and exchange rate is not a valid positive number then error is raised.
930 --For bug 3045302
931 -- If exchange rate has been passed by the AMG is 0 or -ve then
932 -- irrespective of the Rate Type we should raise an error.
933
934 If ((UPPER(P_event_in_rec.P_funding_rate_type) = 'USER'
935 AND p_event_in_rec.P_funding_rate_type <> P_funding_rate_type
936 AND ( p_event_in_rec.P_funding_exchange_rate = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
937 OR p_event_in_rec.P_funding_exchange_rate Is NULL ))
938 OR p_event_in_rec.P_funding_exchange_rate <= 0)
939 Then
940 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
941 THEN
942 pa_interface_utils_pub.map_new_amg_msg
943 ( p_old_message_code => 'PA_FUND_EXCG_RATE_INV'
944 ,p_msg_attribute => 'CHANGE'
945 ,p_resize_flag => 'N'
946 ,p_msg_context => 'EVENT'
947 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
948 ,p_attribute2 => ''
949 ,p_attribute3 => ''
950 ,p_attribute4 => ''
951 ,p_attribute5 => '');
952 END IF;
953 l_return_status:='N';
954 End If;
955
956 /* Added for bug 3053190 */
957 If ( p_funding_bil_rate_date_code ='FIXED_DATE'
958 AND UPPER(P_funding_rate_type) ='USER'
959 AND NVL(UPPER(P_event_in_rec.P_funding_rate_type),'USER') <>'USER'
960 AND (P_event_in_rec.P_funding_rate_date IS NULL
961 OR P_event_in_rec.P_funding_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE))
962 THEN
963 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
964 THEN
965 pa_interface_utils_pub.map_new_amg_msg
966 ( p_old_message_code => 'PA_INVALID_FUND_DATE'
967 ,p_msg_attribute => 'CHANGE'
968 ,p_resize_flag => 'N'
969 ,p_msg_context => 'EVENT'
970 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
971 ,p_attribute2 => ''
972 ,p_attribute3 => ''
973 ,p_attribute4 => ''
974 ,p_attribute5 => '');
975 END IF;
976 l_return_status:='N';
977 End If;
978 /*End of change for bug 3053190 */
979 --Log Message
980 IF l_debug_mode = 'Y' THEN
981 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
982 ,x_msg => 'Validating project rate type.'
983 ,x_log_level => 5);
984 END IF;
985 /*We validate the PC attributes only when PC<>PFC for Bug 3045302*/
986 IF (P_project_currency_code <> P_proj_func_currency_code )
987 THEN
988 If(p_event_in_rec.P_project_rate_date Is NULL
989 or p_event_in_rec.P_project_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
990 Then
991 l_project_rate_date := p_project_bil_rate_date;
992 Else
993 l_project_rate_date := p_event_in_rec.P_project_rate_date;
994 End If;
995
996 If (p_event_in_rec.P_project_rate_type Is NOT NULL
997 AND p_event_in_rec.P_project_rate_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
998 Then
999 If PA_EVENT_UTILS.check_valid_proj_rate_type
1000 (
1001 P_proj_rate_type =>p_event_in_rec.P_project_rate_type
1002 ,P_bill_trans_currency_code =>l_proj_func_currency_code
1003 ,P_project_currency_code =>P_project_currency_code
1004 ,P_proj_level_rt_dt_code =>P_project_bil_rate_date_code
1005 ,P_project_rate_date =>l_project_rate_date -- Modified
1006 --Commented for bug 3013137 and 3009307 ,P_event_date =>SYSDATE) ='N'
1007 ,P_event_date =>NVL(P_event_in_rec.p_completion_date,SYSDATE)
1008 ,x_proj_rate_type =>l_project_rate_type
1009 ) ='N'
1010 Then
1011 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1012 THEN
1013 pa_interface_utils_pub.map_new_amg_msg
1014 ( p_old_message_code => 'PA_PROJ_RATE_TYPE_INV'
1015 ,p_msg_attribute => 'CHANGE'
1016 ,p_resize_flag => 'N'
1017 ,p_msg_context => 'EVENT'
1018 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1019 ,p_attribute2 => ''
1020 ,p_attribute3 => ''
1021 ,p_attribute4 => ''
1022 ,p_attribute5 => '');
1023 END IF;
1024 l_return_status:='N';
1025 End If;
1026 Else l_project_rate_type := P_project_rate_type; -- Added for bug 3013137 and 3009307
1027 End If;
1028
1029 --If bill transaction currency is not same as the project currency
1030 --and if the project exchange rate type is USER, project exchange
1031 --rate details are defaulted from project details in PUBLIC package.
1032 --If at event level project rate type(User) is different from rate type
1033 --at project level then validating project exchange rate .
1034 --Setting error message in case of rate is not a valid positive number.
1035 --For bug 3045302
1036 -- If exchange rate has been passed by the AMG is 0 or -ve then
1037 -- irrespective of the Rate Type we should raise an error.
1038
1039 If ((l_proj_func_currency_code <> P_project_currency_code
1040 AND UPPER(p_event_in_rec.P_project_rate_type) = 'USER'
1041 AND p_event_in_rec.P_project_rate_type <> P_project_rate_type
1042 AND ( p_event_in_rec.P_project_exchange_rate = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1043 OR p_event_in_rec.P_project_exchange_rate Is NULL))
1044 OR p_event_in_rec.P_project_exchange_rate <= 0)
1045 Then
1046 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1047 THEN
1048 pa_interface_utils_pub.map_new_amg_msg
1049 ( p_old_message_code => 'PA_PROJ_EXCG_RATE_INV'
1050 ,p_msg_attribute => 'CHANGE'
1051 ,p_resize_flag => 'N'
1052 ,p_msg_context => 'EVENT'
1053 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1054 ,p_attribute2 => ''
1055 ,p_attribute3 => ''
1056 ,p_attribute4 => ''
1057 ,p_attribute5 => '');
1058 END IF;
1059 l_return_status:='N';
1060 End If;
1061
1062 /* Added for bug 3053190 */
1063 If ( P_project_bil_rate_date_code ='FIXED_DATE'
1064 AND UPPER(P_project_rate_type) ='USER'
1065 AND NVL(UPPER(P_event_in_rec.P_project_rate_type),'USER') <>'USER'
1066 AND (P_event_in_rec.P_project_rate_date IS NULL
1067 OR P_event_in_rec.P_project_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE))
1068 THEN
1069 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1070 THEN
1071 pa_interface_utils_pub.map_new_amg_msg
1072 ( p_old_message_code => 'PA_INVALID_PROJ_DATE'
1073 ,p_msg_attribute => 'CHANGE'
1074 ,p_resize_flag => 'N'
1075 ,p_msg_context => 'EVENT'
1076 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1077 ,p_attribute2 => ''
1078 ,p_attribute3 => ''
1079 ,p_attribute4 => ''
1080 ,p_attribute5 => '');
1081 END IF;
1082 l_return_status:='N';
1083 End If;
1084 /*End of change for bug 3053190 */
1085 END IF;/*P_project_currency_code <> P_proj_func_currency_code */
1086 --Log Message
1087 IF l_debug_mode = 'Y' THEN
1088 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
1089 ,x_msg => 'Validating project functional rate type.'
1090 ,x_log_level => 5);
1091 END IF;
1092
1093 If (P_event_in_rec.P_projfunc_rate_date Is NULL
1094 OR P_event_in_rec.P_projfunc_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1095 Then
1096 l_projfunc_rate_date:=p_projfunc_bil_rate_date;
1097 Else
1098 l_projfunc_rate_date :=P_event_in_rec.P_projfunc_rate_date;
1099 End If;
1100
1101 If (p_event_in_rec.P_projfunc_rate_type Is NOT NULL
1102 AND p_event_in_rec.P_projfunc_rate_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1103 Then
1104 If PA_EVENT_UTILS.check_valid_pfc_rate_type
1105 (P_pfc_rate_type =>P_event_in_rec.P_projfunc_rate_type
1106 ,P_bill_trans_currency_code =>l_proj_func_currency_code
1107 ,P_proj_func_currency_code =>P_proj_func_currency_code
1108 ,P_proj_level_func_rt_dt_code =>P_projfunc_bil_rate_date_code
1109 ,P_projfunc_rate_date =>l_projfunc_rate_date
1110 -- Commented for bug 3013137 and 3009307 ,P_event_date =>SYSDATE
1111 ,P_event_date =>NVL(P_event_in_rec.p_completion_date,SYSDATE)
1112 ,x_pfc_rate_type =>l_projfunc_rate_type
1113 )
1114 ='N'
1115 Then
1116 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1117 THEN
1118 pa_interface_utils_pub.map_new_amg_msg
1119 ( p_old_message_code =>'PA_PFC_RATE_TYPE_INV'
1120 ,p_msg_attribute => 'CHANGE'
1121 ,p_resize_flag => 'N'
1122 ,p_msg_context => 'EVENT'
1123 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1124 ,p_attribute2 => ''
1125 ,p_attribute3 => ''
1126 ,p_attribute4 => ''
1127 ,p_attribute5 => '');
1128 END IF;
1129 l_return_status:='N';
1130 End If;
1131 Else l_projfunc_rate_type := P_projfunc_rate_type; -- Added for bug 3013137 and 3009307
1132 End If;
1133
1134 --If bill transaction currency is not same as the projfunc currency
1135 --and if the projfunc exchange rate type is USER, projfunc exchange
1136 --rate details are defaulted from project details in PUBLIC package.
1137 --If at event level projfunc rate type(User) is different from rate type
1138 --at project level then validating projfunc exchange rate .
1139 --Setting error message in case of rate is not a valid positive number.
1140 --For bug 3045302
1141 -- If exchange rate has been passed by the AMG is 0 or -ve then
1142 -- irrespective of the Rate Type we should raise an error.
1143
1144 If ((P_proj_func_currency_code <> l_proj_func_currency_code
1145 AND UPPER(p_event_in_rec.P_projfunc_rate_type) = 'USER'
1146 AND p_event_in_rec.P_projfunc_rate_type <> P_projfunc_rate_type
1147 AND (p_event_in_rec.P_projfunc_exchange_rate = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1148 OR p_event_in_rec.P_projfunc_exchange_rate Is NULL ))
1149 OR p_event_in_rec.P_projfunc_exchange_rate <= 0)
1150 Then
1151 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1152 THEN
1153 pa_interface_utils_pub.map_new_amg_msg
1154 ( p_old_message_code => 'PA_PFC_EXCG_RATE_INV'
1155 ,p_msg_attribute => 'CHANGE'
1156 ,p_resize_flag => 'N'
1157 ,p_msg_context => 'EVENT'
1158 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1159 ,p_attribute2 => ''
1160 ,p_attribute3 => ''
1161 ,p_attribute4 => ''
1162 ,p_attribute5 => '');
1163 END IF;
1164 l_return_status:='N';
1165 End If;
1166
1167 /* Added for bug 3053190 */
1168 If ( p_projfunc_bil_rate_date_code ='FIXED_DATE'
1169 AND UPPER(P_projfunc_rate_type) ='USER'
1170 AND NVL(UPPER(P_event_in_rec.P_projfunc_rate_type),'USER') <>'USER'
1171 AND (P_event_in_rec.P_projfunc_rate_date IS NULL
1172 OR P_event_in_rec.P_projfunc_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE))
1173 THEN
1174 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1175 THEN
1176 pa_interface_utils_pub.map_new_amg_msg
1177 ( p_old_message_code => 'PA_INVALID_PROJFUNC_DATE'
1178 ,p_msg_attribute => 'CHANGE'
1179 ,p_resize_flag => 'N'
1180 ,p_msg_context => 'EVENT'
1181 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1182 ,p_attribute2 => ''
1183 ,p_attribute3 => ''
1184 ,p_attribute4 => ''
1185 ,p_attribute5 => '');
1186 END IF;
1187 l_return_status:='N';
1188 End If;
1189 /*End of change for bug 3053190 */
1190 Else --if mcb is not enabled
1191 If (p_event_in_rec.P_bill_trans_currency_code Is NULL)
1192 Then
1193 l_proj_func_currency_code:=P_proj_func_currency_code;
1194
1195 ElsIf (p_event_in_rec.P_bill_trans_currency_code <> P_proj_func_currency_code)
1196 Then
1197 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1198 THEN
1199 pa_interface_utils_pub.map_new_amg_msg
1200 -- (p_old_message_code => 'PA_INVALID_BIL_TRX_CUR' -- Commented for Bug#3013172
1201 (p_old_message_code => 'PA_EVENT_NON_MCB_OPTION' -- Modified the message code for Bug#3013172
1202 ,p_msg_attribute => 'CHANGE'
1203 ,p_resize_flag => 'N'
1204 ,p_msg_context => 'EVENT'
1205 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1206 ,p_attribute2 => ''
1207 ,p_attribute3 => ''
1208 ,p_attribute4 => ''
1209 ,p_attribute5 => '');
1210 END IF;
1211 l_return_status:='N';
1212 l_proj_func_currency_code:=P_proj_func_currency_code;
1213 Else
1214 l_proj_func_currency_code:=p_event_in_rec.P_bill_trans_currency_code;
1215 End If;
1216 End If;
1217
1218 --Log Message
1219 IF l_debug_mode = 'Y' THEN
1220 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
1221 ,x_msg => 'Validating the Adjusting Revenue flag.'
1222 ,x_log_level => 5);
1223 END IF;
1224
1225 --validating the Adjusting Revenue flag
1226 If(p_event_in_rec.P_adjusting_revenue_flag Is NOT NULL
1227 AND p_event_in_rec.P_adjusting_revenue_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1228 AND PA_EVENT_PVT.CHECK_YES_NO
1229 (P_flag =>P_event_in_rec.P_adjusting_revenue_flag)='N')
1230 Then
1231 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1232 THEN
1233 pa_interface_utils_pub.map_new_amg_msg
1234 ( p_old_message_code =>'PA_INVALID_ADJ_REV_FLG'
1235 ,p_msg_attribute => 'CHANGE'
1236 ,p_resize_flag => 'N'
1237 ,p_msg_context => 'EVENT'
1238 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1239 ,p_attribute2 => ''
1240 ,p_attribute3 => ''
1241 ,p_attribute4 => ''
1242 ,p_attribute5 => '');
1243 END IF;
1244 l_return_status:='N';
1245 ElsIf (P_event_in_rec.P_adjusting_revenue_flag in ('Y','y'))
1246 Then
1247 If (p_event_type_classification Is NULL
1248 OR p_event_type_classification <> 'MANUAL')
1249 Then
1250 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1251 THEN
1252 pa_interface_utils_pub.map_new_amg_msg
1253 ( p_old_message_code =>'PA_INV_EV_TYP_ADJ_REV'
1254 ,p_msg_attribute => 'CHANGE'
1255 ,p_resize_flag => 'N'
1256 ,p_msg_context => 'EVENT'
1257 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1258 ,p_attribute2 => ''
1259 ,p_attribute3 => ''
1260 ,p_attribute4 => ''
1261 ,p_attribute5 => '');
1262 END IF;
1263 l_return_status:='N';
1264 End If;
1265 End If;
1266
1267 --Log Message
1268 IF l_debug_mode = 'Y' THEN
1269 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
1270 ,x_msg => 'Validating the revenue amount.'
1271 ,x_log_level => 5);
1272 END IF;
1273
1274 --validating the revenue amount
1275 --For revenue events like Write-on And Write-off events,
1276 --revenue amount is mandatory and a +ve value.
1277 If (p_event_type_classification In('WRITE ON','WRITE OFF'))
1278 Then
1279 If PA_EVENT_UTILS.CHECK_VALID_REV_AMT
1280 (P_event_type_classification =>P_event_type_classification
1281 ,P_rev_amt =>p_event_in_rec.P_bill_trans_rev_amount)='N'
1282 Then
1283 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1284 THEN
1285 pa_interface_utils_pub.map_new_amg_msg
1286 ( p_old_message_code => 'PA_PR_EPR_REV_GT_ZERO'
1287 ,p_msg_attribute => 'CHANGE'
1288 ,p_resize_flag => 'N'
1289 ,p_msg_context => 'EVENT'
1290 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
1291 ,p_attribute2 => ''
1292 ,p_attribute3 => ''
1293 ,p_attribute4 => ''
1294 ,p_attribute5 => '');
1295 END IF;
1296 l_return_status:='N';
1297 --call to check revenue amount for write-off events .The check should be done only if the p_completion_date
1298 -- is not NULL.
1299 /*For bug 3053669 */
1300 ElsIf (p_event_type_classification = 'WRITE OFF'AND P_event_in_rec.p_completion_date is not NULL
1301 AND P_event_in_rec.p_completion_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE )
1302 Then
1303
1304 l_ret_status:=PA_EVENT_UTILS.check_write_off_amt
1305 (P_project_id =>P_project_id
1306 ,P_task_id =>x_task_id
1307 ,P_event_id =>p_event_in_rec.P_event_id
1308 ,P_rev_amt =>p_event_in_rec.P_bill_trans_rev_amount
1309 ,P_bill_trans_currency =>l_proj_func_currency_code
1310 ,P_proj_func_currency =>P_proj_func_currency_code
1311 /* Commented for bug 3013137 and 3009307 ,P_proj_func_rate_type =>p_event_in_rec.P_project_rate_type
1312 ,P_proj_func_rate =>p_event_in_rec.P_project_exchange_rate,
1313 ,P_proj_func_rate_date =>p_event_in_rec.P_project_rate_date
1314 ,P_event_date =>sysdate); */
1315 ,P_proj_func_rate_type =>l_projfunc_rate_type
1316 ,P_proj_func_rate =>NVL(p_event_in_rec.P_project_exchange_rate,
1317 p_projfunc_bil_exchange_rate)
1318 ,P_proj_func_rate_date =>l_projfunc_rate_date
1319 ,P_event_date =>P_event_in_rec.p_completion_date);
1320 If l_ret_status <> 'Y'
1321 Then
1322 If l_ret_status = 'N'
1323 Then
1324 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1325 THEN
1326 pa_interface_utils_pub.map_new_amg_msg
1327 ( p_old_message_code =>'PA_TK_EXCESS_REV'
1328 ,p_msg_attribute => 'CHANGE'
1329 ,p_resize_flag => 'N'
1330 ,p_msg_context => 'EVENT'
1331 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
1332 ,p_attribute2 => ''
1333 ,p_attribute3 => ''
1334 ,p_attribute4 => ''
1335 ,p_attribute5 => '');
1336 END IF;
1337 l_return_status:='N';
1338 Else
1339 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1340 THEN
1341 pa_interface_utils_pub.map_new_amg_msg
1342 ( p_old_message_code =>l_ret_status
1343 ,p_msg_attribute => 'CHANGE'
1344 ,p_resize_flag => 'N'
1345 ,p_msg_context => 'EVENT'
1346 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
1347 ,p_attribute2 => ''
1348 ,p_attribute3 => ''
1349 ,p_attribute4 => ''
1350 ,p_attribute5 => '');
1351 END IF;
1352 l_return_status:='N';
1353 End If; /*If l_ret_status = 'N'*/
1354 End If; /*If l_ret_status <> 'Y'*/
1355 End If;
1356 ElsIf (p_event_type_classification In('MANUAL','WRITE ON','WRITE OFF')
1357 AND (p_event_in_rec.P_bill_trans_rev_amount Is NULL
1358 OR p_event_in_rec.P_bill_trans_rev_amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
1359 Then
1360 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1361 THEN
1362 pa_interface_utils_pub.map_new_amg_msg
1363 ( p_old_message_code => 'PA_INVALID_REV_AMT'
1364 ,p_msg_attribute => 'CHANGE'
1365 ,p_resize_flag => 'N'
1366 ,p_msg_context => 'EVENT'
1367 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
1368 ,p_attribute2 => ''
1369 ,p_attribute3 => ''
1370 ,p_attribute4 => ''
1371 ,p_attribute5 => '');
1372 END IF;
1373 l_return_status:='N';
1374 End If;
1375
1376 --Log Message
1377 IF l_debug_mode = 'Y' THEN
1378 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
1379 ,x_msg => 'Validating the bill amount.'
1380 ,x_log_level => 5);
1381 END IF;
1382
1383 --validating the bill amount
1384 If PA_EVENT_UTILS.CHECK_VALID_BILL_AMT
1385 (P_event_type_classification =>P_event_type_classification
1386 ,P_bill_amt =>p_event_in_rec.P_bill_trans_bill_amount)='N'
1387 Then
1388 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1389 THEN
1390 pa_interface_utils_pub.map_new_amg_msg
1391 ( p_old_message_code => 'PA_PR_EPR_BILL_GT_ZERO'
1392 ,p_msg_attribute => 'CHANGE'
1393 ,p_resize_flag => 'N'
1394 ,p_msg_context => 'EVENT'
1395 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1396 ,p_attribute2 => ''
1397 ,p_attribute3 => ''
1398 ,p_attribute4 => ''
1399 ,p_attribute5 => '');
1400 END IF;
1401 l_return_status:='N';
1402 ElsIf (p_event_type_classification In('MANUAL','DEFERRED REVENUE','INVOICE REDUCTION','SCHEDULED PAYMENTS')
1403 AND (p_event_in_rec.P_bill_trans_bill_amount Is NULL
1404 OR p_event_in_rec.P_bill_trans_bill_amount = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
1405 Then
1406 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1407 THEN
1408 pa_interface_utils_pub.map_new_amg_msg
1409 ( p_old_message_code => 'PA_INVALID_BILL_AMT'
1410 ,p_msg_attribute => 'CHANGE'
1411 ,p_resize_flag => 'N'
1412 ,p_msg_context => 'EVENT'
1413 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
1414 ,p_attribute2 => ''
1415 ,p_attribute3 => ''
1416 ,p_attribute4 => ''
1417 ,p_attribute5 => '');
1418 END IF;
1419 l_return_status:='N';
1420 End If;
1421
1422 --Log Message
1423 IF l_debug_mode = 'Y' THEN
1424 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
1425 ,x_msg => 'Validating the Bill hold flag.'
1426 ,x_log_level => 5);
1427 END IF;
1428
1429
1430 --validating the Bill hold flag
1431 If (p_event_in_rec.P_bill_hold_flag Is NOT NULL
1432 AND p_event_in_rec.P_bill_hold_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1433 Then
1434 If (upper(p_event_in_rec.P_bill_hold_flag) NOT In('Y','N','O'))
1435 Then
1436 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1437 THEN
1438 pa_interface_utils_pub.map_new_amg_msg
1439 ( p_old_message_code => 'PA_INVALID_BIL_HLD_FLG'
1440 ,p_msg_attribute => 'CHANGE'
1441 ,p_resize_flag => 'N'
1442 ,p_msg_context => 'EVENT'
1443 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1444 ,p_attribute2 => ''
1445 ,p_attribute3 => ''
1446 ,p_attribute4 => ''
1447 ,p_attribute5 => '');
1448 END IF;
1449 l_return_status:='N';
1450 End If;
1451 End If;
1452
1453 --Log Message
1454 IF l_debug_mode = 'Y' THEN
1455 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
1456 ,x_msg => 'Validating event number.'
1457 ,x_log_level => 5);
1458 END IF;
1459
1460 --validating the event number
1461 If (p_event_in_rec.P_event_number Is NOT NULL
1462 AND p_event_in_rec.P_event_number <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1463 AND PA_EVENT_UTILS.CHECK_VALID_EVENT_NUM
1464 (P_project_id =>P_project_id
1465 ,P_task_id =>x_task_id
1466 ,P_event_num =>p_event_in_rec.P_event_number)='N')
1467 Then
1468 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1469 THEN
1470 pa_interface_utils_pub.map_new_amg_msg
1471 ( p_old_message_code => 'PA_INV_EVNT_NUM'
1472 ,p_msg_attribute => 'CHANGE'
1473 ,p_resize_flag => 'N'
1474 ,p_msg_context => 'EVENT'
1475 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1476 ,p_attribute2 => ''
1477 ,p_attribute3 => ''
1478 ,p_attribute4 => ''
1479 ,p_attribute5 => '');
1480 END IF;
1481 l_return_status:='N';
1482 End If;
1483
1484 --Log Message
1485 IF l_debug_mode = 'Y' THEN
1486 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
1487 ,x_msg => 'Validating inventory organization name and deriving inventory organization id.'
1488 ,x_log_level => 5);
1489 END IF;
1490
1491
1492 --validating the inventory organization name should be valid and active
1493 If (p_event_in_rec.P_inventory_org_name Is NOT NULL
1494 AND p_event_in_rec.P_inventory_org_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1495 AND PA_EVENT_UTILS.CHECK_VALID_INV_ORG
1496 (P_inv_org_name =>p_event_in_rec.P_inventory_org_name
1497 ,P_inv_org_id =>x_inv_org_id)='N')
1498 Then
1499 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1500 THEN
1501 pa_interface_utils_pub.map_new_amg_msg
1502 ( p_old_message_code => 'PA_INV_INVT_ORG_NAME'
1503 ,p_msg_attribute => 'CHANGE'
1504 ,p_resize_flag => 'N'
1505 ,p_msg_context => 'EVENT'
1506 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1507 ,p_attribute2 => ''
1508 ,p_attribute3 => ''
1509 ,p_attribute4 => ''
1510 ,p_attribute5 => '');
1511 END IF;
1512 l_return_status:='N';
1513 End If;
1514
1515 --Log Message
1516 IF l_debug_mode = 'Y' THEN
1517 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
1518 ,x_msg => 'Validating inventory item id.'
1519 ,x_log_level => 5);
1520 END IF;
1521
1522 --validating the inventory item_id
1523 If (p_event_in_rec.P_inventory_item_id Is NOT NULL
1524 AND p_event_in_rec.P_inventory_item_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1525 AND PA_EVENT_UTILS.CHECK_VALID_INV_ITEM
1526 (P_inv_item_id =>p_event_in_rec.P_inventory_item_id)='N')
1527 Then
1528 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1529 THEN
1530 pa_interface_utils_pub.map_new_amg_msg
1531 ( p_old_message_code => 'PA_INV_INVT_ITEM'
1532 ,p_msg_attribute => 'CHANGE'
1533 ,p_resize_flag => 'N'
1534 ,p_msg_context => 'EVENT'
1535 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1536 ,p_attribute2 => ''
1537 ,p_attribute3 => ''
1538 ,p_attribute4 => ''
1539 ,p_attribute5 => '');
1540 END IF;
1541 l_return_status:='N';
1542 End If;
1543
1544 --Log Message
1545 IF l_debug_mode = 'Y' THEN
1546 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_EVENT_OK.begin'
1547 ,x_msg => 'End of Check Create Event Ok'
1548 ,x_log_level => 5);
1549 END IF;
1550
1551 RETURN(l_return_status);
1552
1553 --handling exceptions
1554 Exception
1555 When pa_event_utils.pvt_excp
1556 then
1557 x_task_id := NULL; -- NOCOPY
1558 x_organization_id := NULL; --NOCOPY
1559 x_inv_org_id := NULL; --NOCOPY
1560 P_event_type_classification := NULL; --NOCOPY
1561 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
1562 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'CHECK_CREATE_EVENT_OK->';
1563 Raise pub_excp;--raising exception to be handled in public package
1564
1565 When others
1566 then
1567 x_task_id := NULL; --NOCOPY
1568 x_organization_id := NULL; --NOCOPY
1569 x_inv_org_id := NULL; --NOCOPY
1570 P_event_type_classification := NULL; --NOCOPY
1571 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
1572 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CHECK_CREATE_EVENT_OK->';
1573 Raise pub_excp;--raising exception to be handled in public package
1574
1575 END CHECK_CREATE_EVENT_OK;
1576
1577 -- ============================================================================
1578 --
1579 --Name: CHECK_UPDATE_EVENT_OK
1580 --Type: function
1581 --Description: This function validates if the event is updateable.
1582 --
1583 --Called subprograms:
1584 -- PA_EVENT_UTILS.CHECK_VALID_TASK
1585 -- PA_EVENT_UTILS.CHECK_VALID_EVENT_TYPE
1586 -- PA_EVENT_UTILS.CHECK_VALID_EVENT_ORG
1587 -- PA_EVENT_UTILS.CHECK_VALID_REV_AMT
1588 -- PA_EVENT_UTILS.CHECK_VALID_BILL_AMT
1589 -- PA_EVENT_UTILS.CHECK_VALID_EVENT_NUM
1590 -- PA_EVENT_UTILS.CHECK_VALID_INV_ORG
1591 -- PA_EVENT_UTILS.CHECK_VALID_INV_ITEM
1592 -- PA_EVENT_UTILS.CHECK_VALID_CURR
1593 -- PA_EVENT_UTILS.CHECK_VALID_FUND_RATE_TYPE
1594 -- PA_EVENT_UTILS.CHECK_VALID_PROJ_RATE_TYPE
1595 -- PA_EVENT_UTILS.CHECK_VALID_PFC_RATE_TYPE
1596 -- PA_EVENT_UTILS.CHECK_VALID_AGREEMENT
1597 -- PA_EVENT_UTILS.CHECK_VALID_EVENT_DATE
1598 -- ============================================================================
1599 FUNCTION CHECK_UPDATE_EVENT_OK
1600 (P_pm_product_code IN VARCHAR2
1601 ,P_event_in_rec IN pa_event_pub.Event_rec_in_type
1602 ,P_project_currency_code IN VARCHAR2
1603 ,P_proj_func_currency_code IN VARCHAR2
1604 ,P_project_bil_rate_date_code IN VARCHAR2
1605 ,P_project_rate_type IN VARCHAR2
1606 ,p_project_bil_rate_date IN VARCHAR2
1607 ,p_projfunc_bil_rate_date_code IN VARCHAR2
1608 ,P_projfunc_rate_type IN VARCHAR2
1609 ,p_projfunc_bil_rate_date IN VARCHAR2
1610 ,P_funding_rate_type IN VARCHAR2
1611 ,P_multi_currency_billing_flag IN VARCHAR2
1612 ,p_project_id IN NUMBER
1613 ,p_projfunc_bil_exchange_rate IN NUMBER -- Added bug 3013137
1614 ,p_funding_bill_rate_date_code IN VARCHAR2 --Added for bug 3053190
1615 ,x_task_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1616 ,x_organization_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1617 ,x_inv_org_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1618 ,x_agreement_id OUT NOCOPY NUMBER -- Federal Uptake
1619 ,p_event_type_classification OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1620 ,P_event_processed OUT NOCOPY VARCHAR2) /* Added for bug 7110782 */
1621 RETURN VARCHAR2
1622 AS
1623
1624 l_proj_func_currency_code PA_EVENTS.projfunc_currency_code%TYPE;
1625 l_bill_trans_currency_code PA_EVENTS.bill_trans_currency_code%TYPE; -- Added bug 3013137
1626 l_projfunc_rate_date PA_PROJECTS_ALL.projfunc_bil_rate_date%TYPE;
1627 l_project_rate_date PA_PROJECTS_ALL.project_bil_rate_date%TYPE;
1628 l_funding_rate_type PA_EVENTS.funding_rate_type%TYPE;
1629 l_return_status VARCHAR2(1):='Y';
1630 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1631 l_ret_status VARCHAR2(2000):=NULL;
1632
1633 l_project_rate_type PA_EVENTS.project_rate_type%TYPE; -- Added bug 3013137 and 3009307
1634 l_projfunc_rate_type PA_EVENTS.projfunc_rate_type%TYPE; -- Added bug 3013137 and 3009307
1635 l_completion_date PA_EVENTS.completion_date%TYPE; --Added for bug 3053669
1636 l_revenue_amount PA_EVENTS.bill_trans_rev_amount%TYPE; --Added for bug 3053669;Chgd the type for 4027500
1637 l_adjusting_revenue_flag PA_EVENTS.adjusting_revenue_flag%TYPE; --Added for bug 3053669
1638 l_bill_amount PA_EVENTS.bill_trans_bill_amount%TYPE; --Added for bug 3053669
1639 /*The following variable are added for bug 3053190.The varables are to used only for the validation
1640 of dates and should not be used for any other puprpose. */
1641 l_check_projfunc_rate_date PA_EVENTS.PROJFUNC_RATE_DATE %TYPE;
1642 l_check_project_rate_date PA_EVENTS.PROJECT_RATE_DATE % TYPE;
1643 l_check_funding_rate_date PA_EVENTS.FUNDING_RATE_DATE % TYPE;
1644 l_event_processed VARCHAR2(1);
1645 --For Bug 3619483 :Added following 2 variables
1646 l_old_rev_amount PA_EVENTS.bill_trans_rev_amount%TYPE;
1647 l_old_bill_amount PA_EVENTS.bill_trans_bill_amount%TYPE;
1648 l_event_date PA_EVENTS.completion_date%TYPE;
1649
1650 BEGIN
1651
1652 --Log Message
1653 IF l_debug_mode = 'Y' THEN
1654 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
1655 ,x_msg => 'Beginning of Check Update Event Ok'
1656 ,x_log_level => 5);
1657 END IF;
1658
1659 --Log Message
1660 IF l_debug_mode = 'Y' THEN
1661 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
1662 ,x_msg => 'Validating if the event is processed.'
1663 ,x_log_level => 5);
1664 END IF;
1665 /*We are getting the processed status of the event and store it in the variable l_event_processed for future
1666 processing for bug 3205120 */
1667
1668 P_event_processed := 'N'; /* Added for bug 7110782 */
1669 l_event_processed :=PA_EVENT_UTILS.CHECK_EVENT_PROCESSED
1670 (P_event_id =>P_event_in_rec.P_event_id);
1671 --Check if the event is processed.If processed then it cannot be updated.
1672 --The folowing code is added to supoort events created in Deliverables in amg .
1673 If l_event_processed = 'Y' AND nvl(p_event_in_rec.p_context,'Z') <> 'D'
1674 Then
1675 DECLARE
1676
1677 l_deliverable_id NUMBER ;
1678
1679 BEGIN
1680
1681 SELECT deliverable_id
1682 INTO l_deliverable_id
1683 FROM PA_EVENTS
1684 WHERE EVENT_ID=P_event_in_rec.P_event_id
1685 AND deliverable_id IS NULL;
1686
1687 EXCEPTION
1688
1689 WHEN NO_DATA_FOUND THEN
1690
1691 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1692 THEN
1693 pa_interface_utils_pub.map_new_amg_msg
1694 ( p_old_message_code => 'PA_EVENT_IN_DELV_UPD'
1695 ,p_msg_attribute => 'CHANGE'
1696 ,p_resize_flag => 'N'
1697 ,p_msg_context => 'EVENT'
1698 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1699 ,p_attribute2 => ''
1700 ,p_attribute3 => ''
1701 ,p_attribute4 => ''
1702 ,p_attribute5 => '');
1703 END IF;
1704 l_return_status:='N';
1705 Return(l_return_status);
1706 END;
1707 ElsIf l_event_processed = 'N'
1708 Then
1709 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1710 THEN
1711 pa_interface_utils_pub.map_new_amg_msg
1712 ( p_old_message_code => 'PA_TK_EVENT_IN_USE'
1713 ,p_msg_attribute => 'CHANGE'
1714 ,p_resize_flag => 'N'
1715 ,p_msg_context => 'EVENT'
1716 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1717 ,p_attribute2 => ''
1718 ,p_attribute3 => ''
1719 ,p_attribute4 => ''
1720 ,p_attribute5 => '');
1721 END IF;
1722 l_return_status:='N';
1723 Return(l_return_status); --If event is processed then terminating further validation
1724
1725 /* Code added for bug 7110782 - starts */
1726
1727 /* Retunr I if event is only invoiced */
1728 ELSIF l_event_processed = 'I' THEN
1729 P_event_processed := 'I';
1730
1731 /* Retunr R if event is only revenue distributed */
1732 ELSIF l_event_processed = 'R' THEN
1733 P_event_processed := 'R';
1734
1735 /* Code added for bug 7110782 - ends */
1736
1737 /*The following code is added for bug 3205120 */
1738 ELSIF l_event_processed = 'P'
1739 THEN
1740 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1741 THEN
1742 pa_interface_utils_pub.map_new_amg_msg
1743 ( p_old_message_code => 'PA_EVENT_PARTIAL_BILL'
1744 ,p_msg_attribute => 'CHANGE'
1745 ,p_resize_flag => 'N'
1746 ,p_msg_context => 'EVENT'
1747 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1748 ,p_attribute2 => ''
1749 ,p_attribute3 => ''
1750 ,p_attribute4 => ''
1751 ,p_attribute5 => '');
1752 END IF;
1753 l_return_status:='N';
1754 Return(l_return_status);
1755
1756 ELSIF l_event_processed = 'C'
1757 THEN
1758 /*The invoice for this project has been cancelled .None of the fields other than Description,Organization,Bill Hold flag and date
1759 can be updated .However the DFF will be updateable. */
1760 if (
1761 P_event_in_rec.P_Task_Number IS NOT NULL OR
1762 P_event_in_rec.p_event_number IS NOT NULL OR
1763 P_event_in_rec.P_event_type IS NOT NULL OR
1764 P_event_in_rec.P_project_number IS NOT NULL OR
1765 P_event_in_rec.P_agreement_number IS NOT NULL OR
1766 P_event_in_rec.P_inventory_org_name IS NOT NULL OR
1767 P_event_in_rec.P_inventory_item_id IS NOT NULL OR
1768 P_event_in_rec.P_quantity_billed IS NOT NULL OR
1769 P_event_in_rec.P_uom_code IS NOT NULL OR
1770 P_event_in_rec.P_unit_price IS NOT NULL OR
1771 P_event_in_rec.P_reference1 IS NOT NULL OR
1772 P_event_in_rec.P_reference2 IS NOT NULL OR
1773 P_event_in_rec.P_reference3 IS NOT NULL OR
1774 P_event_in_rec.P_reference4 IS NOT NULL OR
1775 P_event_in_rec.P_reference5 IS NOT NULL OR
1776 P_event_in_rec.P_reference6 IS NOT NULL OR
1777 P_event_in_rec.P_reference7 IS NOT NULL OR
1778 P_event_in_rec.P_reference8 IS NOT NULL OR
1779 P_event_in_rec.P_reference9 IS NOT NULL OR
1780 P_event_in_rec.P_reference10 IS NOT NULL OR
1781 P_event_in_rec.P_bill_trans_currency_code IS NOT NULL OR
1782 /* P_event_in_rec.P_bill_trans_bill_amount IS NOT NULL OR commented for bug 8485535*/
1783 (P_event_in_rec.P_bill_trans_rev_amount IS NOT NULL and l_event_processed='C') OR /* Modified for bug 8485535*/
1784 P_event_in_rec.P_project_rate_type IS NOT NULL OR
1785 P_event_in_rec.P_project_rate_date IS NOT NULL OR
1786 P_event_in_rec.P_project_exchange_rate IS NOT NULL OR
1787 P_event_in_rec.P_projfunc_rate_type IS NOT NULL OR
1788 P_event_in_rec.P_projfunc_rate_date IS NOT NULL OR
1789 P_event_in_rec.P_projfunc_exchange_rate IS NOT NULL OR
1790 P_event_in_rec.P_funding_rate_type IS NOT NULL OR
1791 P_event_in_rec.P_funding_rate_date IS NOT NULL OR
1792 P_event_in_rec.P_funding_exchange_rate IS NOT NULL OR
1793 P_event_in_rec.P_adjusting_revenue_flag IS NOT NULL ) THEN
1794
1795 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1796 THEN
1797 pa_interface_utils_pub.map_new_amg_msg
1798 ( p_old_message_code => 'PA_EVENT_CANCEL_UPD'
1799 ,p_msg_attribute => 'CHANGE'
1800 ,p_resize_flag => 'N'
1801 ,p_msg_context => 'EVENT'
1802 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1803 ,p_attribute2 => ''
1804 ,p_attribute3 => ''
1805 ,p_attribute4 => ''
1806 ,p_attribute5 => '');
1807 END IF;
1808 l_return_status:='N';
1809 Return(l_return_status);
1810 END IF;
1811
1812 /*End of chsnge for bug 3205120 */
1813 End If;
1814
1815 --Log Message
1816 IF l_debug_mode = 'Y' THEN
1817 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
1818 ,x_msg => 'Validating task for the given project'
1819 ,x_log_level => 5);
1820 END IF;
1821
1822 --validating the task
1823 If (P_event_in_rec.P_task_number Is NOT NULL
1824 AND P_event_in_rec.P_task_number <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1825 Then
1826 If PA_EVENT_UTILS.CHECK_VALID_TASK
1827 (P_project_id =>P_project_id
1828 ,P_task_id =>x_task_id
1829 ,P_task_num =>P_event_in_rec.P_task_number)='N'
1830 Then
1831 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1832 THEN
1833 pa_interface_utils_pub.map_new_amg_msg
1834 ( p_old_message_code => 'PA_INVALID_TOP_TASK'
1835 ,p_msg_attribute => 'CHANGE'
1836 ,p_resize_flag => 'N'
1837 ,p_msg_context => 'EVENT'
1838 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1839 ,p_attribute2 => ''
1840 ,p_attribute3 => ''
1841 ,p_attribute4 => ''
1842 ,p_attribute5 => '');
1843 END IF;
1844 l_return_status:='N';
1845 --If task_id is invalid then terminating further validation
1846 Return(l_return_status);
1847 End If;
1848 Else
1849 /* x_task_id:=NULL; */
1850 select task_id
1851 into x_task_id
1852 from pa_events
1853 where event_id = P_event_in_rec.P_event_id;
1854 End If;
1855 --Log Message
1856 IF l_debug_mode = 'Y' THEN
1857 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
1858 ,x_msg => 'Validating funding level of the project'
1859 ,x_log_level => 5);
1860 END IF;
1861
1862 --validating the funding level of the project.
1863 If PA_EVENT_UTILS.CHECK_FUNDING
1864 (P_project_id =>P_project_id
1865 ,P_task_id =>x_task_id)='N'
1866 Then
1867 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1868 THEN
1869 pa_interface_utils_pub.map_new_amg_msg
1870 ( p_old_message_code => 'PA_TASK_FUND_NO_PROJ_EVENT'
1871 ,p_msg_attribute => 'CHANGE'
1872 ,p_resize_flag => 'N'
1873 ,p_msg_context => 'EVENT'
1874 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1875 ,p_attribute2 => ''
1876 ,p_attribute3 => ''
1877 ,p_attribute4 => ''
1878 ,p_attribute5 => '');
1879 END IF;
1880 l_return_status:='N';
1881 --If task_id is invalid then terminating further validation
1882 Return(l_return_status);
1883 End If;
1884
1885 -- log Message -- Start Federal Uptake
1886 IF l_debug_mode = 'Y' THEN
1887 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
1888 ,x_msg => 'Validating agreement for the given project'
1889 ,x_log_level => 5);
1890 END IF;
1891
1892 --validating the agreement
1893
1894 If (P_event_in_rec.P_agreement_number Is NULL AND
1895 P_event_in_rec.P_agreement_type IS NULL AND
1896 P_event_in_rec.P_customer_number IS NULL )
1897 Then
1898 select agreement_id
1899 into x_agreement_id
1900 from pa_events
1901 where event_id = P_event_in_rec.P_event_id;
1902 else
1903 if
1904 (P_event_in_rec.P_agreement_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
1905 (P_event_in_rec.P_agreement_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
1906 (P_event_in_rec.P_customer_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1907 Then
1908 x_agreement_id := NULL;
1909 else
1910 if PA_EVENT_UTILS.CHECK_VALID_AGREEMENT (
1911 P_project_id => P_project_id
1912 ,P_task_id => x_task_id
1913 ,P_agreement_number => P_event_in_rec.P_agreement_number
1914 ,P_agreement_type => P_event_in_rec.P_agreement_type
1915 ,P_customer_number => P_event_in_rec.P_customer_number
1916 ,P_agreement_id => x_agreement_id ) ='N'
1917 Then
1918 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1919 THEN
1920 pa_interface_utils_pub.map_new_amg_msg
1921 ( p_old_message_code => 'PA_INVALID_AGMT_NUM'
1922 ,p_msg_attribute => 'CHANGE'
1923 ,p_resize_flag => 'N'
1924 ,p_msg_context => 'EVENT'
1925 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1926 ,p_attribute2 => ''
1927 ,p_attribute3 => ''
1928 ,p_attribute4 => ''
1929 ,p_attribute5 => '');
1930 END IF;
1931 l_return_status:='N';
1932 Return(l_return_status); --If agreement_id is invalid then terminate further validation
1933 End If;
1934 End IF;
1935 END IF;
1936
1937 --Log Message
1938 IF l_debug_mode = 'Y' THEN
1939 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_CREATE_UPDATE_OK.begin'
1940 ,x_msg => 'Validating event date is between agreement date'
1941 ,x_log_level => 5);
1942 END IF;
1943
1944 --validating event date
1945
1946 IF ( P_event_in_rec.P_completion_date is NULL )
1947 Then
1948 select completion_date
1949 into l_event_date
1950 from pa_events
1951 where event_id = P_event_in_rec.P_event_id;
1952 ELSE
1953 l_event_date := P_event_in_rec.P_completion_date;
1954 END IF;
1955
1956 IF l_event_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1957 Then
1958 If PA_EVENT_UTILS.CHECK_VALID_EVENT_DATE (
1959 P_event_date => l_event_date
1960 ,P_agreement_id => x_agreement_id ) ='N'
1961 Then
1962 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1963 THEN
1964 pa_interface_utils_pub.map_new_amg_msg
1965 ( p_old_message_code => 'PA_INVALID_EVENT_DATE'
1966 ,p_msg_attribute => 'CHANGE'
1967 ,p_resize_flag => 'N'
1968 ,p_msg_context => 'EVENT'
1969 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1970 ,p_attribute2 => ''
1971 ,p_attribute3 => ''
1972 ,p_attribute4 => ''
1973 ,p_attribute5 => '');
1974 END IF;
1975 l_return_status:='N';
1976 Return(l_return_status); --If event date is not between agreement date
1977 End If;
1978 End IF;
1979
1980 -- End Federal Uptake
1981
1982 --Log Message
1983 IF l_debug_mode = 'Y' THEN
1984 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
1985 ,x_msg => 'Validating the event_type'
1986 ,x_log_level => 5);
1987 END IF;
1988
1989 --validating the event_type
1990 If (P_event_in_rec.P_event_type Is NOT NULL
1991 AND P_event_in_rec.P_event_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1992 Then
1993 If PA_EVENT_UTILS.CHECK_VALID_EVENT_TYPE
1994 (P_event_type =>P_event_in_rec.P_event_type
1995 ,P_context =>P_event_in_rec.P_context
1996 ,X_event_type_classification =>p_event_type_classification)='N'
1997 Then
1998 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1999 THEN
2000 pa_interface_utils_pub.map_new_amg_msg
2001 (p_old_message_code => 'PA_INVALID_EVNT_TYPE'
2002 ,p_msg_attribute => 'CHANGE'
2003 ,p_resize_flag => 'N'
2004 ,p_msg_context => 'EVENT'
2005 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2006 ,p_attribute2 => ''
2007 ,p_attribute3 => ''
2008 ,p_attribute4 => ''
2009 ,p_attribute5 => '');
2010 END IF;
2011 l_return_status:='N';
2012 End If;
2013 Else
2014 --Defaulting event_type_classification for other validations
2015 SELECT t.event_type_classification
2016 INTO p_event_type_classification
2017 FROM pa_event_types t,pa_events v
2018 WHERE v.event_id=P_event_in_rec.P_event_id
2019 AND t.event_type=v.event_type ;
2020 End If;
2021 --For Bug 3619483
2022 IF ( (g_rev_evt_fun_allowed = 'N' ) AND
2023 (p_event_type_classification IN('WRITE ON','WRITE OFF')) ) THEN
2024 pa_interface_utils_pub.map_new_amg_msg
2025 ( p_old_message_code => 'PA_EV_NO_REV_MAINT_AMG'
2026 ,p_msg_attribute => 'CHANGE'
2027 ,p_resize_flag => 'Y'
2028 ,p_msg_context => 'GENERAL'
2029 ,p_attribute1 => ''
2030 ,p_attribute2 => ''
2031 ,p_attribute3 => ''
2032 ,p_attribute4 => ''
2033 ,p_attribute5 => '');
2034 RAISE FND_API.G_EXC_ERROR;
2035 END IF;
2036
2037 IF ( (g_inv_evt_fun_allowed = 'N' ) AND
2038 (p_event_type_classification IN('DEFERRED REVENUE','INVOICE REDUCTION','SCHEDULED PAYMENTS'))) THEN
2039 pa_interface_utils_pub.map_new_amg_msg
2040 ( p_old_message_code => 'PA_EV_NO_INV_MAINT_AMG'
2041 ,p_msg_attribute => 'CHANGE'
2042 ,p_resize_flag => 'Y'
2043 ,p_msg_context => 'GENERAL'
2044 ,p_attribute1 => ''
2045 ,p_attribute2 => ''
2046 ,p_attribute3 => ''
2047 ,p_attribute4 => ''
2048 ,p_attribute5 => '');
2049 RAISE FND_API.G_EXC_ERROR;
2050 END IF;
2051
2052 SELECT bill_trans_rev_amount,
2053 bill_trans_bill_amount
2054 INTO l_old_rev_amount,
2055 l_old_bill_amount
2056 FROM pa_events
2057 WHERE event_id = p_event_in_rec.P_event_id;
2058
2059 IF (p_event_type_classification = 'MANUAL') THEN
2060 IF ( (g_inv_evt_fun_allowed = 'Y' ) AND
2061 (g_rev_evt_fun_allowed = 'N' ) AND
2062 (l_old_rev_amount <> NVL(p_event_in_rec.p_bill_trans_rev_amount,0)) ) THEN
2063 pa_interface_utils_pub.map_new_amg_msg
2064 ( p_old_message_code => 'PA_EV_NO_REV_MAINT_AMG'
2065 ,p_msg_attribute => 'CHANGE'
2066 ,p_resize_flag => 'Y'
2067 ,p_msg_context => 'GENERAL'
2068 ,p_attribute1 => ''
2069 ,p_attribute2 => ''
2070 ,p_attribute3 => ''
2071 ,p_attribute4 => ''
2072 ,p_attribute5 => '');
2073 pa_interface_utils_pub.map_new_amg_msg
2074 ( p_old_message_code => 'PA_INVALID_REV_AMT_AMG'
2075 ,p_msg_attribute => 'CHANGE'
2076 ,p_resize_flag => 'Y'
2077 ,p_msg_context => 'GENERAL'
2078 ,p_attribute1 => ''
2079 ,p_attribute2 => ''
2080 ,p_attribute3 => ''
2081 ,p_attribute4 => ''
2082 ,p_attribute5 => '');
2083 RAISE FND_API.G_EXC_ERROR;
2084
2085 ELSIF ( (g_inv_evt_fun_allowed = 'N' ) AND
2086 (g_rev_evt_fun_allowed = 'Y' ) AND
2087 (l_old_bill_amount <> NVL(p_event_in_rec.p_bill_trans_bill_amount,0))) THEN
2088 pa_interface_utils_pub.map_new_amg_msg
2089 ( p_old_message_code => 'PA_EV_NO_INV_MAINT_AMG'
2090 ,p_msg_attribute => 'CHANGE'
2091 ,p_resize_flag => 'Y'
2092 ,p_msg_context => 'GENERAL'
2093 ,p_attribute1 => ''
2094 ,p_attribute2 => ''
2095 ,p_attribute3 => ''
2096 ,p_attribute4 => ''
2097 ,p_attribute5 => '');
2098 pa_interface_utils_pub.map_new_amg_msg
2099 ( p_old_message_code => 'PA_INVALID_BILL_AMT_AMG'
2100 ,p_msg_attribute => 'CHANGE'
2101 ,p_resize_flag => 'Y'
2102 ,p_msg_context => 'GENERAL'
2103 ,p_attribute1 => ''
2104 ,p_attribute2 => ''
2105 ,p_attribute3 => ''
2106 ,p_attribute4 => ''
2107 ,p_attribute5 => '');
2108 RAISE FND_API.G_EXC_ERROR;
2109 END IF;
2110
2111 END IF;
2112 --End of Bug fix 3619483
2113 --Log Message
2114 IF l_debug_mode = 'Y' THEN
2115 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2116 ,x_msg => 'Validating organisation name and deriving organisation_id'
2117 ,x_log_level => 5);
2118 END IF;
2119
2120 --calls PA_EVENT_UTILS.CHECK_VALID_EVENT_ORG to validate and
2121 --derive the organisation_id from organisation name
2122 If (p_event_in_rec.P_organization_name Is NOT NULL
2123 AND p_event_in_rec.P_organization_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2124 AND PA_EVENT_UTILS.CHECK_VALID_EVENT_ORG
2125 (P_event_org_name =>p_event_in_rec.P_organization_name
2126 ,P_event_org_id =>x_Organization_Id)='N')
2127 Then
2128 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2129 THEN
2130 pa_interface_utils_pub.map_new_amg_msg
2131 ( p_old_message_code => 'PA_INVALID_EVNT_ORG'
2132 ,p_msg_attribute => 'CHANGE'
2133 ,p_resize_flag => 'N'
2134 ,p_msg_context => 'EVENT'
2135 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2136 ,p_attribute2 => ''
2137 ,p_attribute3 => ''
2138 ,p_attribute4 => ''
2139 ,p_attribute5 => '');
2140 END IF;
2141 l_return_status:='N';
2142 End If;
2143
2144 /* For bug 3053190.Moved the following select from below and added the columns
2145 PROJECT_RATE_DATE,PROJFUNC_RATE_DATE,FUNDING_RATE_DATE to the select */
2146
2147 /*Added for bug 3053669 The validation should happen if the revenue amount passed is not null
2148 OR the revenue amount present in the database is not NULL .Selecting the completion date of the
2149 event for validation of Write Off events */
2150
2151 SELECT decode(p_event_in_rec.P_bill_trans_rev_amount,NULL,bill_trans_rev_amount,
2152 p_event_in_rec.P_bill_trans_rev_amount),
2153 decode(p_event_in_rec.P_bill_trans_bill_amount,NULL,bill_trans_bill_amount,
2154 p_event_in_rec.P_bill_trans_bill_amount),
2155 decode(p_event_in_rec.p_completion_date,NULL,ev.completion_date,P_event_in_rec.p_completion_date),
2156 decode(p_event_in_rec.P_adjusting_revenue_flag,NULL,ev.adjusting_revenue_flag,
2157 p_event_in_rec.P_adjusting_revenue_flag),
2158 decode(p_event_in_rec.P_projfunc_rate_date,NULL,ev.projfunc_rate_date,
2159 PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,ev.projfunc_rate_date,
2160 p_event_in_rec.P_projfunc_rate_date),
2161 decode(p_event_in_rec.P_project_rate_date,NULL,ev.project_rate_date,
2162 PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,ev.project_rate_date,
2163 p_event_in_rec.P_project_rate_date),
2164 decode(p_event_in_rec.P_funding_rate_date,NULL,ev.funding_rate_date,
2165 PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,ev.funding_rate_date,
2166 p_event_in_rec.P_funding_rate_date)
2167 into l_revenue_amount,l_bill_amount,l_completion_date,l_adjusting_revenue_flag,
2168 l_check_projfunc_rate_date,l_check_project_rate_date,l_check_funding_rate_date
2169 from pa_events ev
2170 where ev.event_id = p_event_in_rec.P_event_id;
2171
2172
2173 --validating the currency fields if mcb is enabled
2174 If (P_multi_currency_billing_flag ='Y')
2175 Then
2176
2177 --Log Message
2178 IF l_debug_mode = 'Y' THEN
2179 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2180 ,x_msg => 'Start of MCB Validations '
2181 ,x_log_level => 5);
2182 END IF;
2183
2184 --Log Message
2185 IF l_debug_mode = 'Y' THEN
2186 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2187 ,x_msg => 'Validating bill trans currency code'
2188 ,x_log_level => 5);
2189 END IF;
2190
2191
2192 --If bill trans currency code is null or '^' then the default value
2193 --is used for further validation.
2194 /* If (p_event_in_rec.P_bill_trans_currency_code Is NULL) 4027500 */
2195 If (p_event_in_rec.P_bill_trans_currency_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2196 Then
2197 /* l_proj_func_currency_code:=P_proj_func_currency_code; commented for bug 3049100 */
2198
2199 SELECT bill_trans_currency_code
2200 INTO l_proj_func_currency_code
2201 FROM pa_events
2202 WHERE event_id = P_event_in_rec.P_event_id;
2203
2204 ElsIf PA_EVENT_UTILS.CHECK_VALID_CURR
2205 (P_bill_trans_curr =>p_event_in_rec.P_bill_trans_currency_code)='N'
2206 Then
2207 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2208 THEN
2209 pa_interface_utils_pub.map_new_amg_msg
2210 (p_old_message_code => 'PA_INVALID_BIL_TRX_CUR'
2211 ,p_msg_attribute => 'CHANGE'
2212 ,p_resize_flag => 'N'
2213 ,p_msg_context => 'EVENT'
2214 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2215 ,p_attribute2 => ''
2216 ,p_attribute3 => ''
2217 ,p_attribute4 => ''
2218 ,p_attribute5 => '');
2219 END IF;
2220 l_return_status:='N';
2221 l_proj_func_currency_code:=P_proj_func_currency_code;
2222 Else
2223 l_proj_func_currency_code:=p_event_in_rec.P_bill_trans_currency_code;
2224 End If;
2225
2226 --Log Message
2227 IF l_debug_mode = 'Y' THEN
2228 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2229 ,x_msg => 'Validating funding rate type'
2230 ,x_log_level => 5);
2231 END IF;
2232
2233 --If funding rate type is null or '^' then the default value is used for
2234 --other validations.
2235 If (P_event_in_rec.P_funding_rate_type Is NOT NULL
2236 AND P_event_in_rec.P_funding_rate_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2237 Then
2238 If PA_EVENT_UTILS.CHECK_VALID_FUND_RATE_TYPE
2239 (P_fund_rate_type =>P_event_in_rec.P_funding_rate_type
2240 ,x_fund_rate_type =>l_funding_rate_type)='N'
2241
2242 Then
2243 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2244 THEN
2245 pa_interface_utils_pub.map_new_amg_msg
2246 ( p_old_message_code => 'PA_FUND_RATE_TYPE_INV'
2247 ,p_msg_attribute => 'CHANGE'
2248 ,p_resize_flag => 'N'
2249 ,p_msg_context => 'EVENT'
2250 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2251 ,p_attribute2 => ''
2252 ,p_attribute3 => ''
2253 ,p_attribute4 => ''
2254 ,p_attribute5 => '');
2255 END IF;
2256 l_return_status:='N';
2257 End If;
2258
2259 -- When fuding rate type is 'User' at event level and not 'User' at the project level
2260 -- and exchange rate is not a valid positive number then error is raised.
2261 --For bug 3045302
2262 -- If exchange rate has been passed by the AMG is 0 or -ve then
2263 -- irrespective of the Rate Type we should raise an error.
2264 If ((UPPER(P_event_in_rec.P_funding_rate_type) = 'USER'
2265 AND p_event_in_rec.P_funding_rate_type <> P_funding_rate_type
2266 AND ( p_event_in_rec.P_funding_exchange_rate = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2267 OR p_event_in_rec.P_funding_exchange_rate Is NULL ))
2268 OR p_event_in_rec.P_funding_exchange_rate <=0 )
2269 Then
2270 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2271 THEN
2272 pa_interface_utils_pub.map_new_amg_msg
2273 ( p_old_message_code => 'PA_FUND_EXCG_RATE_INV'
2274 ,p_msg_attribute => 'CHANGE'
2275 ,p_resize_flag => 'N'
2276 ,p_msg_context => 'EVENT'
2277 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2278 ,p_attribute2 => ''
2279 ,p_attribute3 => ''
2280 ,p_attribute4 => ''
2281 ,p_attribute5 => '');
2282 END IF;
2283 l_return_status:='N';
2284 End If;
2285 Else l_funding_rate_type := P_funding_rate_type; -- Added bug 3013137 and 3009307
2286 End If;
2287 /* Added for bug 3053190 */
2288 If ( p_funding_bill_rate_date_code ='FIXED_DATE'
2289 AND UPPER(P_funding_rate_type) ='USER'
2290 AND NVL(UPPER(P_event_in_rec.P_funding_rate_type),'USER') <>'USER'
2291 AND l_check_funding_rate_date IS NULL )
2292 THEN
2293 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2294 THEN
2295 pa_interface_utils_pub.map_new_amg_msg
2296 ( p_old_message_code => 'PA_INVALID_FUND_DATE'
2297 ,p_msg_attribute => 'CHANGE'
2298 ,p_resize_flag => 'N'
2299 ,p_msg_context => 'EVENT'
2300 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2301 ,p_attribute2 => ''
2302 ,p_attribute3 => ''
2303 ,p_attribute4 => ''
2304 ,p_attribute5 => '');
2305 END IF;
2306 l_return_status:='N';
2307 End If;
2308 /*End of change for bug 3053190 */
2309 --Log Message
2310 IF l_debug_mode = 'Y' THEN
2311 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2312 ,x_msg => 'Validating project rate type.'
2313 ,x_log_level => 5);
2314 END IF;
2315 /*The Project Currency Attributes should not be validated when PC=PFC.
2316 This is because we will be using the PFC attributes and not the PC attributes
2317 for bug 3045302*/
2318
2319 IF (P_project_currency_code <> P_proj_func_currency_code )
2320 THEN
2321 If(p_event_in_rec.P_project_rate_date Is NULL
2322 or p_event_in_rec.P_project_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
2323 Then
2324 l_project_rate_date := p_project_bil_rate_date;
2325 Else
2326 l_project_rate_date := p_event_in_rec.P_project_rate_date;
2327 End If;
2328
2329 If (p_event_in_rec.P_project_rate_type Is NOT NULL
2330 AND p_event_in_rec.P_project_rate_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2331 Then
2332 If PA_EVENT_UTILS.check_valid_proj_rate_type
2333 (P_proj_rate_type =>p_event_in_rec.P_project_rate_type
2334 ,P_bill_trans_currency_code =>l_proj_func_currency_code
2335 ,P_project_currency_code =>P_project_currency_code
2336 ,P_proj_level_rt_dt_code =>P_project_bil_rate_date_code
2337 ,P_project_rate_date =>l_project_rate_date -- Modified
2338 --Commented bug 3013137 and 3009307 ,P_event_date =>SYSDATE) ='N'
2339 ,P_event_date =>NVL(P_event_in_rec.p_completion_date,SYSDATE)
2340 ,x_proj_rate_type =>l_project_rate_type
2341 ) ='N'
2342
2343 Then
2344 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2345 THEN
2346 pa_interface_utils_pub.map_new_amg_msg
2347 ( p_old_message_code => 'PA_PROJ_RATE_TYPE_INV'
2348 ,p_msg_attribute => 'CHANGE'
2349 ,p_resize_flag => 'N'
2350 ,p_msg_context => 'EVENT'
2351 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2352 ,p_attribute2 => ''
2353 ,p_attribute3 => ''
2354 ,p_attribute4 => ''
2355 ,p_attribute5 => '');
2356 END IF;
2357 l_return_status:='N';
2358 End If;
2359 Else l_project_rate_type := P_project_rate_type; -- Added bug 3013137 and 3009307
2360 End If;
2361
2362 --If bill transaction currency is not same as the project currency
2363 --and if the project exchange rate type is USER, project exchange
2364 --rate details are defaulted from project details in PUBLIC package.
2365 --If at event level project rate type(User) is different from rate type
2366 --at project level then validating project exchange rate .
2367 --Setting error message in case of rate is not a valid positive number.
2368 --For bug 3045302
2369 -- If exchange rate has been passed by the AMG is 0 or -ve then
2370 -- irrespective of the Rate Type we should raise an error.
2371
2372 If (( l_proj_func_currency_code <> P_project_currency_code
2373 AND UPPER(p_event_in_rec.P_project_rate_type) = 'USER'
2374 AND p_event_in_rec.P_project_rate_type <> P_project_rate_type
2375 AND ( p_event_in_rec.P_project_exchange_rate = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2376 OR p_event_in_rec.P_project_exchange_rate Is NULL))
2377 OR p_event_in_rec.P_project_exchange_rate <= 0)
2378 Then
2379 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2380 THEN
2381 pa_interface_utils_pub.map_new_amg_msg
2382 ( p_old_message_code => 'PA_PROJ_EXCG_RATE_INV'
2383 ,p_msg_attribute => 'CHANGE'
2384 ,p_resize_flag => 'N'
2385 ,p_msg_context => 'EVENT'
2386 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2387 ,p_attribute2 => ''
2388 ,p_attribute3 => ''
2389 ,p_attribute4 => ''
2390 ,p_attribute5 => '');
2391 END IF;
2392 l_return_status:='N';
2393 End If;
2394
2395 /* Added for bug 3053190 */
2396 If ( P_project_bil_rate_date_code ='FIXED_DATE'
2397 AND UPPER(P_project_rate_type) ='USER'
2398 AND NVL(UPPER(P_event_in_rec.P_project_rate_type),'USER') <>'USER'
2399 AND l_check_project_rate_date IS NULL)
2400 THEN
2401 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2402 THEN
2403 pa_interface_utils_pub.map_new_amg_msg
2404 ( p_old_message_code => 'PA_INVALID_PROJ_DATE'
2405 ,p_msg_attribute => 'CHANGE'
2406 ,p_resize_flag => 'N'
2407 ,p_msg_context => 'EVENT'
2408 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2409 ,p_attribute2 => ''
2410 ,p_attribute3 => ''
2411 ,p_attribute4 => ''
2412 ,p_attribute5 => '');
2413 END IF;
2414 l_return_status:='N';
2415 End If;
2416 /*End of change for bug 3053190 */
2417
2418 END IF; /*End of P_project_currency_code <> P_proj_func_currency_code */
2419
2420 --Log Message
2421 IF l_debug_mode = 'Y' THEN
2422 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2423 ,x_msg => 'Validating project functional rate type.'
2424 ,x_log_level => 5);
2425 END IF;
2426
2427 If (P_event_in_rec.P_projfunc_rate_date Is NULL
2428 OR P_event_in_rec.P_projfunc_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
2429 Then
2430 l_projfunc_rate_date:=p_projfunc_bil_rate_date;
2431 Else
2432 l_projfunc_rate_date :=P_event_in_rec.P_projfunc_rate_date;
2433 End If;
2434
2435 If (p_event_in_rec.P_projfunc_rate_type Is NOT NULL
2436 AND p_event_in_rec.P_projfunc_rate_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2437 Then
2438 If PA_EVENT_UTILS.check_valid_pfc_rate_type
2439 (P_pfc_rate_type =>P_event_in_rec.P_projfunc_rate_type
2440 ,P_bill_trans_currency_code =>l_proj_func_currency_code
2441 ,P_proj_func_currency_code =>P_proj_func_currency_code -- Modified
2442 ,P_proj_level_func_rt_dt_code =>P_projfunc_bil_rate_date_code
2443 ,P_projfunc_rate_date =>l_projfunc_rate_date
2444 -- Commented bug 3013137 and 3009307 ,P_event_date =>SYSDATE
2445 ,P_event_date =>NVL(P_event_in_rec.p_completion_date,SYSDATE)
2446 ,x_pfc_rate_type =>l_projfunc_rate_type
2447 )
2448 ='N'
2449
2450 Then
2451 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2452 THEN
2453 pa_interface_utils_pub.map_new_amg_msg
2454 ( p_old_message_code =>'PA_PFC_RATE_TYPE_INV'
2455 ,p_msg_attribute => 'CHANGE'
2456 ,p_resize_flag => 'N'
2457 ,p_msg_context => 'EVENT'
2458 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2459 ,p_attribute2 => ''
2460 ,p_attribute3 => ''
2461 ,p_attribute4 => ''
2462 ,p_attribute5 => '');
2463 END IF;
2464 l_return_status:='N';
2465 End If;
2466 Else l_projfunc_rate_type := P_projfunc_rate_type; -- Added bug 3013137 and 3009307
2467 End If;
2468
2469 --If bill transaction currency is not same as the projfunc currency
2470 --and if the projfunc exchange rate type is USER, projfunc exchange
2471 --rate details are defaulted from project details in PUBLIC package.
2472 --If at event level projfunc rate type(User) is different from rate type
2473 --at project level then validating projfunc exchange rate .
2474 --Setting error message in case of rate is not a valid positive number.
2475 --For bug 3045302
2476 -- If exchange rate has been passed by the AMG is 0 or -ve then
2477 -- irrespective of the Rate Type we should raise an error.
2478
2479 If ((P_proj_func_currency_code <> l_proj_func_currency_code
2480 AND UPPER(p_event_in_rec.P_projfunc_rate_type) = 'USER'
2481 AND p_event_in_rec.P_projfunc_rate_type <> P_projfunc_rate_type
2482 AND (p_event_in_rec.P_projfunc_exchange_rate = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2483 OR p_event_in_rec.P_projfunc_exchange_rate Is NULL ))
2484 OR p_event_in_rec.P_projfunc_exchange_rate <= 0)
2485 Then
2486 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2487 THEN
2488 pa_interface_utils_pub.map_new_amg_msg
2489 ( p_old_message_code => 'PA_PFC_EXCG_RATE_INV'
2490 ,p_msg_attribute => 'CHANGE'
2491 ,p_resize_flag => 'N'
2492 ,p_msg_context => 'EVENT'
2493 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2494 ,p_attribute2 => ''
2495 ,p_attribute3 => ''
2496 ,p_attribute4 => ''
2497 ,p_attribute5 => '');
2498 END IF;
2499 l_return_status:='N';
2500 End If;
2501
2502 /* Added for bug 3053190 */
2503 If ( p_projfunc_bil_rate_date_code ='FIXED_DATE'
2504 AND UPPER(P_projfunc_rate_type) ='USER'
2505 AND NVL(UPPER(P_event_in_rec.P_projfunc_rate_type),'USER') <>'USER'
2506 AND l_check_projfunc_rate_date IS NULL )
2507 THEN
2508 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2509 THEN
2510 pa_interface_utils_pub.map_new_amg_msg
2511 ( p_old_message_code => 'PA_INVALID_PROJFUNC_DATE'
2512 ,p_msg_attribute => 'CHANGE'
2513 ,p_resize_flag => 'N'
2514 ,p_msg_context => 'EVENT'
2515 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2516 ,p_attribute2 => ''
2517 ,p_attribute3 => ''
2518 ,p_attribute4 => ''
2519 ,p_attribute5 => '');
2520 END IF;
2521 l_return_status:='N';
2522 End If;
2523 /*End of change for bug 3053190 */
2524
2525 --If mcb is not enabled then validating bill trans currency code
2526 --is null then the default value is used for further validation.
2527 Else
2528 If (p_event_in_rec.P_bill_trans_currency_code Is NULL)
2529 Then
2530 l_proj_func_currency_code:=P_proj_func_currency_code;
2531
2532 ElsIf (p_event_in_rec.P_bill_trans_currency_code <> P_proj_func_currency_code)
2533 Then
2534 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2535 THEN
2536 pa_interface_utils_pub.map_new_amg_msg
2537 -- (p_old_message_code => 'PA_INVALID_BIL_TRX_CUR' --Commented for Bug#3013172
2538 (p_old_message_code => 'PA_EVENT_NON_MCB_OPTION' --Modified for Bug#3013172
2539 ,p_msg_attribute => 'CHANGE'
2540 ,p_resize_flag => 'N'
2541 ,p_msg_context => 'EVENT'
2542 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2543 ,p_attribute2 => ''
2544 ,p_attribute3 => ''
2545 ,p_attribute4 => ''
2546 ,p_attribute5 => '');
2547 END IF;
2548 l_return_status:='N';
2549 l_proj_func_currency_code:=P_proj_func_currency_code;
2550 Else
2551 l_proj_func_currency_code:=p_event_in_rec.P_bill_trans_currency_code;
2552 End If;
2553 End If;
2554
2555 --Log Message
2556 IF l_debug_mode = 'Y' THEN
2557 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2558 ,x_msg => 'Validating the Adjusting Revenue flag.'
2559 ,x_log_level => 5);
2560 END IF;
2561
2562 --validating the Adjusting Revenue flag
2563 /*Added for bug 3053669 The validation should happen if the revenue amount passed is not null
2564 OR the revenue amount present in the database is not NULL .Selecting the completion date of the
2565 event for validation of Write Off events */
2566
2567 /* SELECT decode(p_event_in_rec.P_bill_trans_rev_amount,NULL,bill_trans_rev_amount,
2568 p_event_in_rec.P_bill_trans_rev_amount),
2569 decode(p_event_in_rec.P_bill_trans_bill_amount,NULL,bill_trans_bill_amount,
2570 p_event_in_rec.P_bill_trans_bill_amount),
2571 decode(p_event_in_rec.p_completion_date,NULL,ev.completion_date,P_event_in_rec.p_completion_date),
2572 decode(p_event_in_rec.P_adjusting_revenue_flag,NULL,ev.adjusting_revenue_flag,
2573 p_event_in_rec.P_adjusting_revenue_flag)
2574 into l_revenue_amount,l_bill_amount,l_completion_date,l_adjusting_revenue_flag
2575 from pa_events ev
2576 where ev.event_id = p_event_in_rec.P_event_id; */
2577
2578 If(l_adjusting_revenue_flag Is NOT NULL
2579 AND l_adjusting_revenue_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2580 AND PA_EVENT_PVT.CHECK_YES_NO
2581 (P_flag =>l_adjusting_revenue_flag)='N')
2582 Then
2583 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2584 THEN
2585 pa_interface_utils_pub.map_new_amg_msg
2586 ( p_old_message_code =>'PA_INVALID_ADJ_REV_FLG'
2587 ,p_msg_attribute => 'CHANGE'
2588 ,p_resize_flag => 'N'
2589 ,p_msg_context => 'EVENT'
2590 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2591 ,p_attribute2 => ''
2592 ,p_attribute3 => ''
2593 ,p_attribute4 => ''
2594 ,p_attribute5 => '');
2595 END IF;
2596 l_return_status:='N';
2597 ElsIf (l_adjusting_revenue_flag in ('Y','y'))
2598 Then
2599 If (p_event_type_classification <> 'MANUAL')
2600 Then
2601 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2602 THEN
2603 pa_interface_utils_pub.map_new_amg_msg
2604 ( p_old_message_code =>'PA_INV_EV_TYP_ADJ_REV'
2605 ,p_msg_attribute => 'CHANGE'
2606 ,p_resize_flag => 'N'
2607 ,p_msg_context => 'EVENT'
2608 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2609 ,p_attribute2 => ''
2610 ,p_attribute3 => ''
2611 ,p_attribute4 => ''
2612 ,p_attribute5 => '');
2613 END IF;
2614 l_return_status:='N';
2615 End If;
2616 End If;
2617
2618 --Log Message
2619 IF l_debug_mode = 'Y' THEN
2620 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2621 ,x_msg => 'Validating the revenue amount.'
2622 ,x_log_level => 5);
2623 END IF;
2624
2625 --validating the revenue amount
2626 --For revenue events like Write-on And Write-off events,
2627 --revenue amount is mandatory and a +ve value.
2628
2629 If (l_revenue_amount Is NOT NULL
2630 AND l_revenue_amount <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
2631 Then
2632 If (p_event_type_classification In('WRITE ON','WRITE OFF'))
2633 Then
2634 If PA_EVENT_UTILS.check_valid_rev_amt
2635 (P_event_type_classification =>P_event_type_classification
2636 ,P_rev_amt =>l_revenue_amount)='N'
2637 Then
2638 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2639 THEN
2640 pa_interface_utils_pub.map_new_amg_msg
2641 ( p_old_message_code => 'PA_PR_EPR_REV_GT_ZERO'
2642 ,p_msg_attribute => 'CHANGE'
2643 ,p_resize_flag => 'N'
2644 ,p_msg_context => 'EVENT'
2645 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
2646 ,p_attribute2 => ''
2647 ,p_attribute3 => ''
2648 ,p_attribute4 => ''
2649 ,p_attribute5 => '');
2650 END IF;
2651 l_return_status:='N';
2652 --call to check revenue amount for write-on events .
2653 ElsIf (p_event_type_classification = 'WRITE OFF')
2654 Then
2655 /* Added for bug 3053669 */
2656 IF (l_completion_date IS NOT NULL AND l_completion_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE )
2657 THEN
2658 l_ret_status:=PA_EVENT_UTILS.check_write_off_amt
2659 (P_project_id =>P_project_id
2660 ,P_task_id =>x_task_id
2661 ,P_event_id =>p_event_in_rec.P_event_id
2662 ,P_rev_amt =>l_revenue_amount
2663 ,P_bill_trans_currency =>l_proj_func_currency_code
2664 ,P_proj_func_currency =>P_proj_func_currency_code
2665 /* Commented bug 3013137 and 3009307 ,P_proj_func_rate_type =>p_event_in_rec.P_project_rate_type
2666 ,P_proj_func_rate =>p_event_in_rec.P_project_exchange_rate,
2667 ,P_proj_func_rate_date =>p_event_in_rec.P_project_rate_date
2668 ,P_event_date =>sysdate); */
2669 ,P_proj_func_rate_type =>l_projfunc_rate_type
2670 ,P_proj_func_rate =>NVL(p_event_in_rec.P_project_exchange_rate,
2671 p_projfunc_bil_exchange_rate)
2672 ,P_proj_func_rate_date =>l_projfunc_rate_date
2673 ,P_event_date =>l_completion_date);
2674 If l_ret_status <> 'Y'
2675 Then
2676 If l_ret_status = 'N'
2677 Then
2678 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2679 THEN
2680 pa_interface_utils_pub.map_new_amg_msg
2681 ( p_old_message_code =>'PA_TK_EXCESS_REV'
2682 ,p_msg_attribute => 'CHANGE'
2683 ,p_resize_flag => 'N'
2684 ,p_msg_context => 'EVENT'
2685 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
2686 ,p_attribute2 => ''
2687 ,p_attribute3 => ''
2688 ,p_attribute4 => ''
2689 ,p_attribute5 => '');
2690 END IF;
2691 l_return_status:='N';
2692 Else
2693 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2694 THEN
2695 pa_interface_utils_pub.map_new_amg_msg
2696 ( p_old_message_code =>l_ret_status
2697 ,p_msg_attribute => 'CHANGE'
2698 ,p_resize_flag => 'N'
2699 ,p_msg_context => 'EVENT'
2700 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
2701 ,p_attribute2 => ''
2702 ,p_attribute3 => ''
2703 ,p_attribute4 => ''
2704 ,p_attribute5 => '');
2705 END IF;
2706 l_return_status:='N';
2707 End If;
2708 End If;
2709 End If;/* P_event_in_rec.p_completion_date IS NOT NULL */
2710 End If;
2711 End If;
2712 ElsIf (p_event_type_classification In('MANUAL','WRITE ON','WRITE OFF')
2713 /* AND l_bill_amount= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) 4027500 */ AND l_revenue_amount is null )
2714 Then
2715 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2716 THEN
2717 pa_interface_utils_pub.map_new_amg_msg
2718 ( p_old_message_code => 'PA_INVALID_REV_AMT'
2719 ,p_msg_attribute => 'CHANGE'
2720 ,p_resize_flag => 'N'
2721 ,p_msg_context => 'EVENT'
2722 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
2723 ,p_attribute2 => ''
2724 ,p_attribute3 => ''
2725 ,p_attribute4 => ''
2726 ,p_attribute5 => '');
2727 END IF;
2728 l_return_status:='N';
2729
2730 End If;
2731
2732 --Log Message
2733 IF l_debug_mode = 'Y' THEN
2734 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2735 ,x_msg => 'Validating the bill amount.'
2736 ,x_log_level => 5);
2737 END IF;
2738
2739 --validating the bill amount. Changed the variable name for bug 3013226.
2740 If (l_bill_amount Is NOT NULL
2741 AND l_bill_amount <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2742 AND PA_EVENT_UTILS.CHECK_VALID_BILL_AMT
2743 (P_event_type_classification =>P_event_type_classification
2744 ,P_bill_amt =>l_bill_amount)='N')
2745 Then
2746 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2747 THEN
2748 pa_interface_utils_pub.map_new_amg_msg
2749 ( p_old_message_code => 'PA_PR_EPR_BILL_GT_ZERO'
2750 ,p_msg_attribute => 'CHANGE'
2751 ,p_resize_flag => 'N'
2752 ,p_msg_context => 'EVENT'
2753 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2754 ,p_attribute2 => ''
2755 ,p_attribute3 => ''
2756 ,p_attribute4 => ''
2757 ,p_attribute5 => '');
2758 END IF;
2759 l_return_status:='N';
2760 ElsIf (p_event_type_classification In('MANUAL','DEFERRED REVENUE','INVOICE REDUCTION','SCHEDULED PAYMENTS')
2761 /* AND l_bill_amount= PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ) 4027500 */ AND l_bill_amount is null )
2762 Then
2763 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2764 THEN
2765 pa_interface_utils_pub.map_new_amg_msg
2766 ( p_old_message_code => 'PA_INVALID_BILL_AMT'
2767 ,p_msg_attribute => 'CHANGE'
2768 ,p_resize_flag => 'N'
2769 ,p_msg_context => 'EVENT'
2770 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
2771 ,p_attribute2 => ''
2772 ,p_attribute3 => ''
2773 ,p_attribute4 => ''
2774 ,p_attribute5 => '');
2775 END IF;
2776 l_return_status:='N';
2777 End If;
2778
2779 --Log Message
2780 IF l_debug_mode = 'Y' THEN
2781 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2782 ,x_msg => 'Validating the Bill hold flag.'
2783 ,x_log_level => 5);
2784 END IF;
2785
2786 --validating the Bill hold flag
2787 If (p_event_in_rec.P_bill_hold_flag Is NOT NULL
2788 AND p_event_in_rec.P_bill_hold_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2789 Then
2790 If (upper(p_event_in_rec.P_bill_hold_flag) NOT In('Y','N','O'))
2791 Then
2792 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2793 THEN
2794 pa_interface_utils_pub.map_new_amg_msg
2795 ( p_old_message_code => 'PA_INVALID_BIL_HLD_FLG'
2796 ,p_msg_attribute => 'CHANGE'
2797 ,p_resize_flag => 'N'
2798 ,p_msg_context => 'EVENT'
2799 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2800 ,p_attribute2 => ''
2801 ,p_attribute3 => ''
2802 ,p_attribute4 => ''
2803 ,p_attribute5 => '');
2804 END IF;
2805 l_return_status:='N';
2806 End If;
2807 End If;
2808
2809 --Log Message
2810 IF l_debug_mode = 'Y' THEN
2811 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2812 ,x_msg => 'Validating event number.'
2813 ,x_log_level => 5);
2814 END IF;
2815
2816 --validating the event number
2817 If ((p_event_in_rec.P_event_number Is NOT NULL
2818 AND p_event_in_rec.P_event_number <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2819 AND PA_EVENT_UTILS.CHECK_VALID_EVENT_NUM
2820 (P_project_id =>P_project_id
2821 ,P_task_id =>x_task_id
2822 ,P_event_num =>p_event_in_rec.P_event_number)='Y')
2823 OR p_event_in_rec.P_event_number <=0 ) -- Added the OR condition for bug 5697448
2824 Then
2825 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2826 THEN
2827 pa_interface_utils_pub.map_new_amg_msg
2828 ( p_old_message_code => 'PA_INV_EVNT_NUM'
2829 ,p_msg_attribute => 'CHANGE'
2830 ,p_resize_flag => 'N'
2831 ,p_msg_context => 'EVENT'
2832 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2833 ,p_attribute2 => ''
2834 ,p_attribute3 => ''
2835 ,p_attribute4 => ''
2836 ,p_attribute5 => '');
2837 END IF;
2838 l_return_status:='N';
2839 End If;
2840
2841 --Log Message
2842 IF l_debug_mode = 'Y' THEN
2843 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2844 ,x_msg => 'Validating inventory organization name and deriving inventory organization id.'
2845 ,x_log_level => 5);
2846 END IF;
2847
2848 --validating the inventory organization name should be valid and active
2849 If (p_event_in_rec.P_inventory_org_name Is NOT NULL
2850 AND p_event_in_rec.P_inventory_org_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2851 AND PA_EVENT_UTILS.CHECK_VALID_INV_ORG
2852 (P_inv_org_name =>p_event_in_rec.P_inventory_org_name
2853 ,P_inv_org_id =>x_inv_org_id)='N')
2854 Then
2855 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2856 THEN
2857 pa_interface_utils_pub.map_new_amg_msg
2858 ( p_old_message_code => 'PA_INV_INVT_ORG_NAME'
2859 ,p_msg_attribute => 'CHANGE'
2860 ,p_resize_flag => 'N'
2861 ,p_msg_context => 'EVENT'
2862 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2863 ,p_attribute2 => ''
2864 ,p_attribute3 => ''
2865 ,p_attribute4 => ''
2866 ,p_attribute5 => '');
2867 END IF;
2868 l_return_status:='N';
2869 End If;
2870
2871 --Log Message
2872 IF l_debug_mode = 'Y' THEN
2873 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2874 ,x_msg => 'Validating inventory item id.'
2875 ,x_log_level => 5);
2876 END IF;
2877
2878 --validating the inventory item_id
2879 If (p_event_in_rec.P_inventory_item_id Is NOT NULL
2880 AND p_event_in_rec.P_inventory_item_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2881 AND PA_EVENT_UTILS.CHECK_VALID_INV_ITEM
2882 (P_inv_item_id =>p_event_in_rec.P_inventory_item_id)='N')
2883 Then
2884 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2885 THEN
2886 pa_interface_utils_pub.map_new_amg_msg
2887 ( p_old_message_code => 'PA_INV_INVT_ITEM'
2888 ,p_msg_attribute => 'CHANGE'
2889 ,p_resize_flag => 'N'
2890 ,p_msg_context => 'EVENT'
2891 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2892 ,p_attribute2 => ''
2893 ,p_attribute3 => ''
2894 ,p_attribute4 => ''
2895 ,p_attribute5 => '');
2896 END IF;
2897 l_return_status:='N';
2898 End If;
2899
2900 --Log Message
2901 IF l_debug_mode = 'Y' THEN
2902 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PVT.CHECK_UPDATE_EVENT_OK.begin'
2903 ,x_msg => 'End of Check Update Event Ok'
2904 ,x_log_level => 5);
2905 END IF;
2906
2907 RETURN(l_return_status);
2908
2909 --handling exceptions
2910 Exception
2911 When pa_event_utils.pvt_excp
2912 then
2913 x_task_id := NULL; --NOCOPY
2914 x_organization_id := NULL; --NOCOPY
2915 x_inv_org_id := NULL; --NOCOPY
2916 P_event_type_classification:= NULL; --NOCOPY
2917 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
2918 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'CHECK_UPDATE_EVENT_OK->';
2919 Raise pub_excp;--raising exception to be handled in public package
2920
2921 When others
2922 then
2923 x_task_id := NULL; --NOCOPY
2924 x_organization_id := NULL; --NOCOPY
2925 x_inv_org_id := NULL; --NOCOPY
2926 P_event_type_classification:= NULL; --NOCOPY
2927 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
2928 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CHECK_UPDATE_EVENT_OK->';
2929 Raise pub_excp;--raising exception to be handled in public package
2930
2931
2932 END CHECK_UPDATE_EVENT_OK;
2933
2934 -- ============================================================================
2935 --
2936 --Name: validate_flex_fields
2937 --Type: Procedure
2938 --Description: This procedure can be used to validate flexfields.
2939 --
2940 --Called subprograms:
2941 -- None
2942 --
2943 -- ============================================================================
2944
2945 PROCEDURE VALIDATE_FLEXFIELD
2946 ( P_desc_flex_name IN VARCHAR2
2947 ,P_attribute_category IN VARCHAR2
2948 ,P_attribute1 IN VARCHAR2
2949 ,P_attribute2 IN VARCHAR2
2950 ,P_attribute3 IN VARCHAR2
2951 ,P_attribute4 IN VARCHAR2
2952 ,P_attribute5 IN VARCHAR2
2953 ,P_attribute6 IN VARCHAR2
2954 ,P_attribute7 IN VARCHAR2
2955 ,P_attribute8 IN VARCHAR2
2956 ,P_attribute9 IN VARCHAR2
2957 ,P_attribute10 IN VARCHAR2
2958 ,P_return_msg OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2959 ,P_valid_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2960 AS
2961 l_dummy VARCHAR2(1);
2962 l_r VARCHAR2(2000);
2963 BEGIN
2964
2965 -- DEFINE ID COLUMNS
2966 fnd_flex_descval.set_context_value(p_attribute_category);
2967 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1);
2968 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attribute2);
2969 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attribute3);
2970 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attribute4);
2971 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attribute5);
2972 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attribute6);
2973 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attribute7);
2974 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attribute8);
2975 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attribute9);
2976 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attribute10);
2977
2978 -- VALIDATE
2979 IF (fnd_flex_descval.validate_desccols( 'PA',p_desc_flex_name)) then
2980 p_RETURN_msg := 'VALID: ' || fnd_flex_descval.concatenated_ids;
2981 p_valid_status := 'Y';
2982 ELSE
2983 p_RETURN_msg := 'INVALID: ' || fnd_flex_descval.error_message;
2984 p_valid_status := 'N';
2985 END IF;
2986
2987 --handling exceptions
2988 Exception
2989 When pa_event_utils.pvt_excp
2990 then
2991 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
2992 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'VALIDATE_FLEXFIELD->';
2993 Raise pub_excp;--raising exception to be handled in public package
2994
2995 When others
2996 then
2997 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
2998 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'VALIDATE_FLEXFIELD->';
2999 Raise pub_excp;--raising exception to be handled in public package
3000
3001 END VALIDATE_FLEXFIELD;
3002 -- ============================================================================
3003 --
3004 --Name: CONV_EVENT_REF_TO_ID
3005 --Type: Procedure
3006 --Description: This procedure can be used to validate event_id if provide
3007 -- OR
3008 -- If event_id is not provided then validate the event reference
3009 -- and convert the event reference to an event id.
3010 --
3011 --Called subprograms:
3012 -- PA_EVENT_PVT.FETCH_EVENT_ID
3013 --
3014 -- ============================================================================
3015 FUNCTION CONV_EVENT_REF_TO_ID
3016 (P_pm_product_code IN VARCHAR2
3017 ,P_pm_event_reference IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3018 ,P_event_id IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
3019 RETURN VARCHAR2
3020 AS
3021
3022 Cursor check_event_id
3023 Is
3024 Select 'x'
3025 From pa_events
3026 Where event_id=P_event_id;
3027 -- And pm_product_code =P_pm_product_code; /* Commented for bug 5056969 */
3028
3029 event_id_found VARCHAR2(1);
3030 l_return_status VARCHAR2(1) := 'Y';
3031 l_event_id pa_events.event_id%type;
3032
3033 l_pm_event_reference varchar2(30) := p_pm_event_reference;
3034 l1_event_id pa_events.event_id%type := p_event_id;
3035
3036 BEGIN
3037 --validating that either the event reference or the event id is passed.
3038 If ( ( P_pm_event_reference IS NULL
3039 OR P_pm_event_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3040 AND ( P_event_id IS NULL
3041 OR P_event_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
3042 )
3043
3044 Then
3045 If FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3046 Then
3047 pa_interface_utils_pub.map_new_amg_msg
3048 ( p_old_message_code => 'PA_INV_EVNT_ID_REF'
3049 ,p_msg_attribute => 'CHANGE'
3050 ,p_resize_flag => 'N'
3051 ,p_msg_context => 'EVENT'
3052 ,p_attribute1 => p_pm_event_reference
3053 ,p_attribute2 => ''
3054 ,p_attribute3 => ''
3055 ,p_attribute4 => ''
3056 ,p_attribute5 => '');
3057 End If;
3058 l_return_status := 'N';
3059 End If;
3060
3061 --validating event id when provided.
3062 If (P_event_id is not null)
3063 And (P_event_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
3064 Then
3065 Open check_event_id;
3066 Fetch check_event_id into event_id_found;
3067
3068 If check_event_id%NOTFOUND
3069 Then
3070 If FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3071 Then
3072 pa_interface_utils_pub.map_new_amg_msg
3073 ( p_old_message_code => 'PA_INV_EVNT_ID'
3074 ,p_msg_attribute => 'CHANGE'
3075 ,p_resize_flag => 'N'
3076 ,p_msg_context => 'EVENT'
3077 ,p_attribute1 => p_pm_event_reference
3078 ,p_attribute2 => ''
3079 ,p_attribute3 => ''
3080 ,p_attribute4 => ''
3081 ,p_attribute5 => '');
3082 End If;
3083 l_return_status := 'N';
3084 End If;
3085 Close check_event_id;
3086 End If;
3087
3088 --Validating event_reference.
3089 --Derive event_id from the event_reference number when not provided.
3090
3091 If ( P_pm_event_reference IS NOT NULL
3092 AND P_pm_event_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3093
3094 Then
3095
3096 l_event_id := PA_EVENT_PVT.FETCH_EVENT_ID
3097 (P_pm_product_code =>P_pm_product_code
3098 ,P_pm_event_reference =>p_pm_event_reference);
3099
3100 --If event_id is null then return false.
3101 If (l_event_id Is NULL)
3102 Then
3103 If FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3104 Then
3105 pa_interface_utils_pub.map_new_amg_msg
3106 (p_old_message_code => 'PA_INVALID_EVENT_REF'
3107 ,p_msg_attribute => 'CHANGE'
3108 ,p_resize_flag => 'N'
3109 ,p_msg_context => 'EVENT'
3110 ,p_attribute1 => p_pm_event_reference
3111 ,p_attribute2 => ''
3112 ,p_attribute3 => ''
3113 ,p_attribute4 => ''
3114 ,p_attribute5 => '');
3115 End If;
3116 l_return_status := 'N';
3117 End If;
3118
3119 End If; /*P_pm_event_reference */
3120
3121 --Check if the event_id is same in both the above cases.
3122
3123 If (P_event_id is not null)
3124 And (P_event_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
3125 And P_pm_event_reference IS NOT NULL
3126 And P_pm_event_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
3127 Then
3128
3129 If (l_event_id <> p_event_id )
3130 Then
3131 If FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3132 Then
3133 pa_interface_utils_pub.map_new_amg_msg
3134 (p_old_message_code => 'PA_INV_EVENT_REF_ID'
3135 ,p_msg_attribute => 'CHANGE'
3136 ,p_resize_flag => 'N'
3137 ,p_msg_context => 'EVENT'
3138 ,p_attribute1 => p_pm_event_reference
3139 ,p_attribute2 => ''
3140 ,p_attribute3 => ''
3141 ,p_attribute4 => ''
3142 ,p_attribute5 => '');
3143 End If;
3144 l_return_status := 'N';
3145 End If;
3146 elsif (P_event_id IS NULL
3147 OR P_event_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
3148 Then
3149 p_event_id := l_event_id;
3150 End If;
3151
3152 --If the event reference is not given then derive then derive it from the id.
3153
3154 If ( P_pm_event_reference IS NULL
3155 OR P_pm_event_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3156
3157 Then
3158
3159 BEGIN
3160
3161 SELECT pm_event_reference
3162 INTO P_pm_event_reference
3163 FROM PA_EVENTS
3164 WHERE event_id=p_event_id;
3165 Exception
3166
3167 When others
3168 then
3169 NULL;
3170
3171 END;
3172
3173 End If;
3174
3175 Return(l_return_status);
3176
3177 --handling exceptions
3178 Exception
3179 When pa_event_utils.pvt_excp
3180 then
3181 p_pm_event_reference := l_pm_event_reference ; -- NOCOPY
3182 p_event_id := l1_event_id; -- NOCOPY
3183 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
3184 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'CONV_EVENT_REF_TO_ID->';
3185 Raise pub_excp;--raising exception to be handled in public package
3186
3187 When others
3188 then
3189 p_pm_event_reference := l_pm_event_reference ; -- NOCOPY
3190 p_event_id := l1_event_id; -- NOCOPY
3191 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
3192 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CONV_EVENT_REF_TO_ID->';
3193 Raise pub_excp;--raising exception to be handled in public package
3194
3195
3196 END CONV_EVENT_REF_TO_ID;
3197 -- ============================================================================
3198 --
3199 --Name: CHECK_DELETE_EVENT_OK
3200 --Type: procedure
3201 --Description: This API checks if an existing event or a set of existing events
3202 -- can be deleted or not.If the validation is successful then it
3203 -- it deletes these events.
3204 --Called subprograms:
3205 --
3206 --
3207 --
3208 --History:
3209
3210 -- ============================================================================
3211
3212 FUNCTION CHECK_DELETE_EVENT_OK
3213 (P_pm_event_reference IN VARCHAR2
3214 ,P_event_id IN NUMBER)
3215 RETURN VARCHAR2
3216 AS
3217
3218 P_event_id_out NUMBER:=NULL;
3219 P_return_status VARCHAR2(1):='Y';
3220 l_event_processed VARCHAR2(1);
3221 p_event_type_classification VARCHAR2(30); --For Bug 3619483
3222 l_rev_amount NUMBER; --For Bug 3619483
3223 l_bill_amount NUMBER; --For Bug 3619483
3224 BEGIN
3225
3226
3227 --Checking if the event is processed.
3228 --If the event is billed or revenue distributed then it cannot be deleted.
3229 -- Now the function can return more than two statuses and
3230 -- only if the return status='Y' we should continue with furthure processing
3231 -- For bug 3205120.We get the processed status of the event and store it in a local variable
3232 l_event_processed := PA_EVENT_UTILS.CHECK_EVENT_PROCESSED
3233 (P_event_id =>P_event_id);
3234
3235 --The folowing code is added to supoort events created in Deliverables in amg .
3236 If l_event_processed = 'Y'
3237 Then
3238 DECLARE
3239
3240 l_deliverable_id NUMBER ;
3241
3242 BEGIN
3243
3244 SELECT deliverable_id
3245 INTO l_deliverable_id
3246 FROM PA_EVENTS
3247 WHERE EVENT_ID=P_event_id
3248 AND deliverable_id IS NULL;
3249
3250 EXCEPTION
3251
3252 WHEN NO_DATA_FOUND THEN
3253
3254 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3255 THEN
3256 pa_interface_utils_pub.map_new_amg_msg
3257 ( p_old_message_code => 'PA_EVENT_IN_DELV_DEL'
3258 ,p_msg_attribute => 'CHANGE'
3259 ,p_resize_flag => 'N'
3260 ,p_msg_context => 'EVENT'
3261 ,p_attribute1 => p_pm_event_reference
3262 ,p_attribute2 => ''
3263 ,p_attribute3 => ''
3264 ,p_attribute4 => ''
3265 ,p_attribute5 => '');
3266 END IF;
3267 p_return_status:='N';
3268 Return(p_return_status);
3269 END;
3270 ElsIf l_event_processed IN ('N','I','R')/*For Bug 7305416*/
3271 Then
3272 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3273 THEN
3274 pa_interface_utils_pub.map_new_amg_msg
3275 ( p_old_message_code => 'PA_TK_EVENT_IN_USE'
3276 ,p_msg_attribute => 'CHANGE'
3277 ,p_resize_flag => 'N'
3278 ,p_msg_context => 'EVENT'
3279 ,p_attribute1 => p_pm_event_reference
3280 ,p_attribute2 => ''
3281 ,p_attribute3 => ''
3282 ,p_attribute4 => ''
3283 ,p_attribute5 => '');
3284 END IF;
3285 p_return_status:='N';
3286 Return(p_return_status);
3287 /* The following code has been added for bug 3205120 */
3288 Elsif l_event_processed ='P'
3289 Then
3290 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3291 THEN
3292 pa_interface_utils_pub.map_new_amg_msg
3293 ( p_old_message_code => 'PA_EVENT_PARTIAL_BILL'
3294 ,p_msg_attribute => 'CHANGE'
3295 ,p_resize_flag => 'N'
3296 ,p_msg_context => 'EVENT'
3297 ,p_attribute1 => p_pm_event_reference
3298 ,p_attribute2 => ''
3299 ,p_attribute3 => ''
3300 ,p_attribute4 => ''
3301 ,p_attribute5 => '');
3302 END IF;
3303 p_return_status:='N';
3304 Return(p_return_status);
3305 Elsif l_event_processed = 'C'
3306 Then
3307 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3308 THEN
3309 pa_interface_utils_pub.map_new_amg_msg
3310 ( p_old_message_code => 'PA_EVENT_CANCEL_DEL'
3311 ,p_msg_attribute => 'CHANGE'
3312 ,p_resize_flag => 'N'
3313 ,p_msg_context => 'EVENT'
3314 ,p_attribute1 => p_pm_event_reference
3315 ,p_attribute2 => ''
3316 ,p_attribute3 => ''
3317 ,p_attribute4 => ''
3318 ,p_attribute5 => '');
3319 END IF;
3320 p_return_status:='N';
3321 Return(p_return_status);
3322 /*End of change for bug 3205120 */
3323 End If;
3324 --For Bug 3619483
3325 SELECT t.event_type_classification
3326 INTO p_event_type_classification
3327 FROM pa_event_types t,pa_events v
3328 WHERE v.event_id=P_event_id
3329 AND t.event_type=v.event_type ;
3330
3331 IF ( (g_rev_evt_fun_allowed = 'N' ) AND
3332 (p_event_type_classification IN('WRITE ON','WRITE OFF')) ) THEN
3333 pa_interface_utils_pub.map_new_amg_msg
3334 ( p_old_message_code => 'PA_EV_NO_REV_MAINT_AMG'
3335 ,p_msg_attribute => 'CHANGE'
3336 ,p_resize_flag => 'Y'
3337 ,p_msg_context => 'GENERAL'
3338 ,p_attribute1 => ''
3339 ,p_attribute2 => ''
3340 ,p_attribute3 => ''
3341 ,p_attribute4 => ''
3342 ,p_attribute5 => '');
3343 RAISE FND_API.G_EXC_ERROR;
3344 END IF;
3345
3346 IF ( (g_inv_evt_fun_allowed = 'N' ) AND
3347 (p_event_type_classification IN('DEFERRED REVENUE','INVOICE REDUCTION','SCHEDULED PAYMENTS'))) THEN
3348 pa_interface_utils_pub.map_new_amg_msg
3349 ( p_old_message_code => 'PA_EV_NO_INV_MAINT_AMG'
3350 ,p_msg_attribute => 'CHANGE'
3351 ,p_resize_flag => 'Y'
3352 ,p_msg_context => 'GENERAL'
3353 ,p_attribute1 => ''
3354 ,p_attribute2 => ''
3355 ,p_attribute3 => ''
3356 ,p_attribute4 => ''
3357 ,p_attribute5 => '');
3358 RAISE FND_API.G_EXC_ERROR;
3359 END IF;
3360 SELECT bill_trans_rev_amount,
3361 bill_trans_bill_amount
3362 INTO l_rev_amount,
3363 l_bill_amount
3364 FROM pa_events
3365 WHERE event_id = P_event_id;
3366
3367 IF (p_event_type_classification = 'MANUAL') THEN
3368 IF ( (g_inv_evt_fun_allowed = 'Y' ) AND
3369 (g_rev_evt_fun_allowed = 'N' ) AND
3370 (l_rev_amount <> 0) ) THEN
3371 pa_interface_utils_pub.map_new_amg_msg
3372 ( p_old_message_code => 'PA_EV_NO_REV_MAINT_AMG'
3373 ,p_msg_attribute => 'CHANGE'
3374 ,p_resize_flag => 'Y'
3375 ,p_msg_context => 'GENERAL'
3376 ,p_attribute1 => ''
3377 ,p_attribute2 => ''
3378 ,p_attribute3 => ''
3379 ,p_attribute4 => ''
3380 ,p_attribute5 => '');
3381 RAISE FND_API.G_EXC_ERROR;
3382
3383 ELSIF ( (g_inv_evt_fun_allowed = 'N' ) AND
3384 (g_rev_evt_fun_allowed = 'Y' ) AND
3385 (l_bill_amount <> 0)) THEN
3386 pa_interface_utils_pub.map_new_amg_msg
3387 ( p_old_message_code => 'PA_EV_NO_INV_MAINT_AMG'
3388 ,p_msg_attribute => 'CHANGE'
3389 ,p_resize_flag => 'Y'
3390 ,p_msg_context => 'GENERAL'
3391 ,p_attribute1 => ''
3392 ,p_attribute2 => ''
3393 ,p_attribute3 => ''
3394 ,p_attribute4 => ''
3395 ,p_attribute5 => '');
3396 RAISE FND_API.G_EXC_ERROR;
3397 END IF;
3398
3399 END IF;
3400
3401 --End of Bug 3619483
3402 Return(p_return_status);
3403
3404 --handling exceptions
3405 Exception
3406 When pa_event_utils.pvt_excp
3407 then
3408 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
3409 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'CHECK_DELETE_EVENT_OK->';
3410 Raise pub_excp;--raising exception to be handled in public package
3411
3412 When others
3413 then
3414 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
3415 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CHECK_DELETE_EVENT_OK->';
3416 Raise pub_excp;--raising exception to be handled in public package
3417
3418 END CHECK_DELETE_EVENT_OK;
3419
3420 -- ============================================================================
3421 --
3422 --Name:
3423 --Type:
3424 --Description: This function validates the uniqueness of the event_reference
3425 -- and product_code combination during creation of new events.
3426 --
3427 --Called subprograms: None
3428 --
3429 -- ============================================================================
3430 FUNCTION CHECK_EVENT_REF_UNQ
3431 (P_pm_product_code IN VARCHAR2
3432 ,P_pm_event_reference IN VARCHAR2)
3433 RETURN VARCHAR2
3434 AS
3435
3436 Cursor ProductCode_cur(P_pm_product_code VARCHAR2)
3437 Is
3438 select 1
3439 from pa_lookups
3440 where lookup_type ='PM_PRODUCT_CODE'
3441 and lookup_code=P_pm_product_code;
3442
3443 Cursor unique_evnt_ref_curs(P_pm_product_code VARCHAR2
3444 ,P_pm_event_reference VARCHAR2)
3445 Is
3446 Select 1
3447 From pa_events
3448 Where pm_product_code=P_pm_product_code
3449 And pm_event_reference =P_pm_event_reference;
3450
3451 l_product_code NUMBER:=NULL;
3452 l_event_reference NUMBER:=NULL;
3453 BEGIN
3454
3455 Open ProductCode_cur(P_pm_product_code);
3456 Fetch ProductCode_cur Into l_product_code;
3457 Close ProductCode_cur;
3458
3459 If l_product_code Is NULL
3460 Then
3461 RETURN('N');
3462 Else
3463 Open unique_evnt_ref_curs(P_pm_product_code,P_pm_event_reference);
3464 Fetch unique_evnt_ref_curs Into l_event_reference;
3465 Close unique_evnt_ref_curs;
3466 If l_event_reference Is NOT NULL
3467 Then
3468 RETURN('N');
3469 Else
3470 RETURN('Y');
3471 End If;
3472 End If;
3473
3474 --handling exceptions
3475 Exception
3476 When pa_event_utils.pvt_excp
3477 then
3478 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
3479 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'CHECK_EVENT_REF_UNQ->';
3480 Raise pub_excp;--raising exception to be handled in public package
3481
3482 When others
3483 then
3484 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
3485 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CHECK_EVENT_REF_UNQ->';
3486 Raise pub_excp;--raising exception to be handled in public package
3487
3488 END CHECK_EVENT_REF_UNQ;
3489 -- ============================================================================
3490 --
3491 --Name:
3492 --Type:
3493 --Description: This function validates and returns 'T' if the given value is
3494 -- in ('Y','y','N','n')
3495 --
3496 --Called subprograms: None
3497 --
3498 -- ============================================================================
3499 FUNCTION CHECK_YES_NO
3500 (P_flag IN VARCHAR2)
3501 RETURN VARCHAR2
3502 AS
3503 l_return_status VARCHAR2(1):='Y';
3504
3505 BEGIN
3506
3507 If (P_flag In('Y','y','N','n')
3508 OR P_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3509 Then
3510 RETURN(l_return_status);
3511 Else
3512 l_return_status := 'N';
3513 RETURN(l_return_status);
3514 End If;
3515
3516 --handling exceptions
3517 Exception
3518 When pa_event_utils.pvt_excp
3519 then
3520 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
3521 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'CHECK_YES_NO->';
3522 Raise pub_excp;--raising exception to be handled in public package
3523
3524 When others
3525 then
3526 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
3527 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CHECK_YES_NO->';
3528 Raise pub_excp;--raising exception to be handled in public package
3529
3530 END CHECK_YES_NO;
3531
3532 -- ============================================================================
3533 --
3534 --Name:
3535 --Type:
3536 --Description: This function fetches the event_id for the given event_reference
3537 -- and product_code combination.
3538 --
3539 --Called subprograms: None
3540 --
3541 -- ============================================================================
3542 FUNCTION FETCH_EVENT_ID
3543 (P_pm_product_code IN VARCHAR2
3544 ,P_pm_event_reference IN VARCHAR2)
3545 RETURN NUMBER
3546 AS
3547
3548 cursor fetch_event_id_curs
3549 Is
3550 Select event_id
3551 From pa_events
3552 Where pm_event_reference = p_pm_event_reference
3553 And pm_product_code = p_pm_product_code;
3554
3555 P_event_out_id NUMBER:=NULL;
3556
3557 BEGIN
3558
3559 Open fetch_event_id_curs;
3560 Fetch fetch_event_id_curs Into P_event_out_id;
3561 Close fetch_event_id_curs;
3562
3563 If P_event_out_id is not NULL
3564 Then
3565 RETURN(P_event_out_id);
3566 else
3567 RETURN(NULL);
3568 End If;
3569
3570 --handling exceptions
3571 Exception
3572 When pa_event_utils.pvt_excp
3573 then
3574 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
3575 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'FETCH_EVENT_ID->';
3576 Raise pub_excp;--raising exception to be handled in public package
3577
3578 When others
3579 then
3580 PA_EVENT_PUB.PACKAGE_NAME:=PA_EVENT_PUB.PACKAGE_NAME||'PRIVATE->';
3581 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'FETCH_EVENT_ID->';
3582 Raise pub_excp;--raising exception to be handled in public package
3583
3584 END FETCH_EVENT_ID;
3585
3586 END PA_EVENT_PVT;
3587