[Home] [Help]
PACKAGE BODY: APPS.PA_EVENT_PUB
Source
1 PACKAGE BODY PA_EVENT_PUB AS
2 /* $Header: PAEVAPBB.pls 120.6.12010000.3 2008/11/30 19:32:25 arbandyo ship $ */
3
4 /* Global Constants */
5 G_PKG_NAME VARCHAR2(30) := 'PA_EVENT_PUB';
6
7 --PACKAGE GLOBAL to be used during updates ---------------------------
8 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.USER_id;
9 G_LOGIN_ID CONSTANT NUMBER := FND_GLOBAL.login_id;
10
11 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG.MODE'),'Y');
12
13 /*==================================================================
14 --
15 --Name: create_event
16 --Type: procedure
17 --Description: This API creates an event or a set of events.
18 --
19 --Called subprograms: PA_EVENT_PVT.CHECK_MDTY_PARAMS1
20 -- PA_EVENT_PVT.CHECK_MDTY_PARAMS2
21 -- PA_EVENT_UTILS.CHECK_VALID_PROJECT
22 -- PA_EVENT_PVT.CHECK_CREATE_EVENT_OK
23 -- PA_EVENT_PVT.VALIDATE_FLEXFIELD
24 -- PA_EVENTS_PKG.INSERT_ROW
25 --History:
26
27 -- ============================================================================*/
28
29 PROCEDURE create_event
30 ( p_api_version_number IN NUMBER
31 ,p_commit IN VARCHAR2
32 ,p_init_msg_list IN VARCHAR2
33 ,p_pm_product_code IN VARCHAR2
34 ,p_event_in_tbl IN Event_In_Tbl_Type
35 ,p_event_out_tbl OUT NOCOPY Event_Out_Tbl_Type --File.Sql.39 bug 4440895
36 ,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
37 ,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
38 ,p_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
39 IS
40
41
42 rowid varchar2(18);
43 l_event_type_classification pa_event_types.event_type_classification%type;
44 P_project_id pa_events.project_id%type;
45 P_task_id pa_events.task_id%type;
46 P_Organization_Id pa_projects_all.org_id%type;
47 p_inv_org_id pa_events.inventory_item_id%type;
48 p_agreement_id pa_events.agreement_id%type; -- Federal Uptake
49
50 p_event_in_rec Event_Rec_In_Type;
51 p_event_out_rec Event_Rec_Out_Type;
52 tot_in_rec NUMBER:=0;
53 tot_out_rec NUMBER:=0;
54 p_api_name VARCHAR2(100):='CREATE_EVENT';
55 p_return_msg VARCHAR2(2000);
56 p_validate_status VARCHAR2(1):='Y';
57
58 /* Declaring varible for MCB2 */
59 l_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%type;
60 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
61 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
62 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
63 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
64 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
65 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
66 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
67 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
68 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
69 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
70 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
71 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
72 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
73 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
74 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
75 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
76 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
77 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
78 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
79 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
80 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
81 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
82 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
83 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
84
85 l_event_number pa_events.event_num%type;
86 l_encoded varchar2(1):='F';
87 l_return_status varchar2(1):= FND_API.G_RET_STS_SUCCESS;
88 BEGIN
89 -- Initialize the Error Stack
90 pa_debug.set_err_stack('PA_EVENT_PUB.CREATE_EVENT');
91
92 --Log Message
93 IF l_debug_mode = 'Y' THEN
94 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CREATE_EVENT.begin'
95 ,x_msg => 'Beginning of Create Event'
96 ,x_log_level => 5);
97 END IF;
98
99 p_msg_count := 0;
100 p_return_status := FND_API.G_RET_STS_SUCCESS;
101
102 --Log Message
103 IF l_debug_mode = 'Y' THEN
104 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CREATE_EVENT.begin'
105 ,x_msg => 'Calling mandatory input parameters-1'
106 ,x_log_level => 5);
107 END IF;
108
109 ---Validating mandatory input parameters-
110
111
112 PA_EVENT_PVT.CHECK_MDTY_PARAMS1
113 ( p_api_version_number =>p_api_version_number
114 ,p_api_name =>p_api_name
115 ,p_pm_product_code =>p_pm_product_code
116 ,p_function_name =>'PA_EV_CREATE_EVENT'
117 ,x_return_status =>p_return_status
118 ,x_msg_count =>p_msg_count
119 ,x_msg_data =>p_msg_data );
120
121
122 If p_return_status <> FND_API.G_RET_STS_SUCCESS
123 Then
124
125 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
126 THEN
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128
129 ELSIF p_return_status = FND_API.G_RET_STS_ERROR
130 THEN
131 RAISE FND_API.G_EXC_ERROR;
132 END IF;
133 End If;
134 tot_in_rec := p_event_in_tbl.first;
135 while tot_in_rec is not null loop -- loop begins
136 -- For all the date variables using TRUNC instead of ltrim(rtrim) for bug 3053669
137 P_event_in_rec.P_pm_event_reference :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_pm_event_reference));
138 P_event_in_rec.P_task_number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_task_number));
139 P_event_in_rec.P_event_number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_event_number));
140 P_event_in_rec.P_event_type :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_event_type));
141 -- Added the below three line for federal Uptake
142 P_event_in_rec.P_agreement_number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_agreement_number));
143 P_event_in_rec.P_agreement_type :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_agreement_type));
144 P_event_in_rec.P_customer_number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_customer_number));
145 P_event_in_rec.P_description :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_description));
146 P_event_in_rec.P_bill_hold_flag :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_bill_hold_flag));
147 P_event_in_rec.P_completion_date :=trunc(p_event_in_tbl(tot_in_rec).P_completion_date);
148 P_event_in_rec.P_desc_flex_name :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_desc_flex_name));
149 P_event_in_rec.P_attribute_category :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute_category));
150 P_event_in_rec.P_attribute1 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute1));
151 P_event_in_rec.P_attribute2 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute2));
152 P_event_in_rec.P_attribute3 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute3));
153 P_event_in_rec.P_attribute4 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute4));
154 P_event_in_rec.P_attribute5 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute5));
155 P_event_in_rec.P_attribute6 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute6));
156 P_event_in_rec.P_attribute7 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute7));
157 P_event_in_rec.P_attribute8 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute8));
158 P_event_in_rec.P_attribute9 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute9));
159 P_event_in_rec.P_attribute10 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute10));
160 P_event_in_rec.P_project_number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_project_number));
161 P_event_in_rec.P_organization_name :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_organization_name));
162 P_event_in_rec.P_inventory_org_name :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_inventory_org_name));
163 P_event_in_rec.P_inventory_item_id :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_inventory_item_id));
164 P_event_in_rec.P_quantity_billed :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_quantity_billed));
165 P_event_in_rec.P_uom_code :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_uom_code));
166 P_event_in_rec.P_unit_price :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_unit_price));
167 P_event_in_rec.P_reference1 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference1));
168 P_event_in_rec.P_reference2 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference2));
169 P_event_in_rec.P_reference3 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference3));
170 P_event_in_rec.P_reference4 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference4));
171 P_event_in_rec.P_reference5 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference5));
172 P_event_in_rec.P_reference6 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference6));
173 P_event_in_rec.P_reference7 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference7));
174 P_event_in_rec.P_reference8 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference8));
175 P_event_in_rec.P_reference9 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference9));
176 P_event_in_rec.P_reference10 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference10));
177 P_event_in_rec.P_bill_trans_currency_code :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_bill_trans_currency_code));
178 P_event_in_rec.P_bill_trans_bill_amount :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_bill_trans_bill_amount));
179 P_event_in_rec.P_bill_trans_rev_amount :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_bill_trans_rev_amount));
180 P_event_in_rec.P_project_rate_type :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_project_rate_type));
181 P_event_in_rec.P_project_rate_date :=trunc(p_event_in_tbl(tot_in_rec).P_project_rate_date);
182 P_event_in_rec.P_project_exchange_rate :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_project_exchange_rate));
183 P_event_in_rec.P_projfunc_rate_type :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_projfunc_rate_type));
184 P_event_in_rec.P_projfunc_rate_date :=trunc(p_event_in_tbl(tot_in_rec).P_projfunc_rate_date);
185 P_event_in_rec.P_projfunc_exchange_rate :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_projfunc_exchange_rate));
186 P_event_in_rec.P_funding_rate_type :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_funding_rate_type));
187 P_event_in_rec.P_funding_rate_date :=trunc(p_event_in_tbl(tot_in_rec).P_funding_rate_date);
188 P_event_in_rec.P_funding_exchange_rate :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_funding_exchange_rate));
189 P_event_in_rec.P_adjusting_revenue_flag :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_adjusting_revenue_flag));
190 P_event_in_rec.P_event_id :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_event_id));
191 P_event_in_rec.P_deliverable_id :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_deliverable_id));
192 P_event_in_rec.P_action_id :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_action_id));
193 P_event_in_rec.P_context :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_context));
194
195
196 BEGIN --Start of Inner Block
197 --Seting savepoint
198 savepoint create_event;
199
200 --Log Message
201 IF l_debug_mode = 'Y' THEN
202 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CREATE_EVENT.begin'
203 ,x_msg => ' Calling mandatory input parameters-2'
204 ,x_log_level => 5);
205 END IF;
206
207 -- Validating mandatory input parameters-2
208
209 PA_EVENT_PVT.CHECK_MDTY_PARAMS2
210 (p_pm_event_reference => p_event_in_rec.p_pm_event_reference
211 ,p_pm_product_code => p_pm_product_code
212 ,p_project_number => p_event_in_rec.p_project_number
213 ,p_event_type => p_event_in_rec.p_event_type
214 ,p_organization_name => p_event_in_rec.p_organization_name
215 ,p_calling_place => 'CREATE_EVENT'
216 ,x_return_status => p_return_status );
217
218 If p_return_status <> FND_API.G_RET_STS_SUCCESS
219 Then
220 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
221 THEN
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
223
224 ELSIF p_return_status = FND_API.G_RET_STS_ERROR
225 THEN
226 RAISE FND_API.G_EXC_ERROR;
227 END IF;
228 End If;
229
230 --Log Message
231 IF l_debug_mode = 'Y' THEN
232 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CREATE_EVENT.begin'
233 ,x_msg => 'Calling check_valid_project '
234 ,x_log_level => 5);
235 END IF;
236
237 --validating the project number and derive the project_id.
238 If PA_EVENT_UTILS.CHECK_VALID_PROJECT
239 (P_project_id =>P_project_id
240 ,P_project_num =>P_event_in_rec.P_project_number) = 'N'
241 Then
242 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
243 THEN
244 pa_interface_utils_pub.map_new_amg_msg
245 ( p_old_message_code => 'PA_INVALID_PROJECT'
246 ,p_msg_attribute => 'CHANGE'
247 ,p_resize_flag => 'N'
248 ,p_msg_context => 'EVENT'
249 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
250 ,p_attribute2 => ''
251 ,p_attribute3 => ''
252 ,p_attribute4 => ''
253 ,p_attribute5 => '');
254 END IF;
255 p_return_status := FND_API.G_RET_STS_ERROR;
256 --If project_id is invalid then terminate further validation by raising error.
257 RAISE FND_API.G_EXC_ERROR;
258 End If;
259
260 --Log Message
261 IF l_debug_mode = 'Y' THEN
262 pa_debug.write_log (x_module =>'pa.plsql.PA_EVENT_PUB.CREATE_EVENT.begin'
263 ,x_msg =>'Calling get_project_defaults to get defaulting currencies'
264 ,x_log_level =>5);
265 END IF;
266
267 -- Before the check_create_event_ok function is called the mcb related
268 -- fields are defaulted
269 -- Defaulting currency fields for the given project_id.
270
271 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
272 p_project_id => P_project_id,
273 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
274 x_baseline_funding_flag => l_baseline_funding_flag,
275 x_revproc_currency_code => l_revproc_currency_code,
276 x_invproc_currency_type => l_invproc_currency_type,
277 x_invproc_currency_code => l_invproc_currency_code,
278 x_project_currency_code => l_project_currency_code,
279 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
280 x_project_bil_rate_type => l_project_bil_rate_type,
281 x_project_bil_rate_date => l_project_bil_rate_date,
282 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
283 x_projfunc_currency_code => l_projfunc_currency_code,
284 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
285 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
286 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
287 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
291 x_funding_exchange_rate => l_funding_exchange_rate,
288 x_funding_rate_date_code => l_funding_rate_date_code,
289 x_funding_rate_type => l_funding_rate_type,
290 x_funding_rate_date => l_funding_rate_date,
292 x_return_status => p_return_status,
293 x_msg_count => p_msg_count,
294 x_msg_data => p_msg_data);
295
296 If p_return_status <> FND_API.G_RET_STS_SUCCESS
297 Then
298 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
299 THEN
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301
302 ELSIF p_return_status = FND_API.G_RET_STS_ERROR
303 THEN
304 RAISE FND_API.G_EXC_ERROR;
305 END IF;
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_PUB.CREATE_EVENT.begin'
311 ,x_msg => 'Calling check_create_event_ok'
312 ,x_log_level => 5);
313 END IF;
314
315 -- Calls check_create_event_ok function
316
317 If PA_EVENT_PVT.check_create_event_ok
318 (P_pm_product_code =>p_pm_product_code
319 ,P_event_in_rec =>p_event_in_rec
320 ,P_project_currency_code =>l_project_currency_code
321 ,P_proj_func_currency_code =>l_projfunc_currency_code
322 ,P_project_bil_rate_date_code =>l_project_bil_rate_date_code
323 ,P_project_rate_type =>l_project_bil_rate_type
324 ,p_project_bil_rate_date =>l_project_bil_rate_date
325 ,p_projfunc_bil_rate_date_code =>l_projfunc_bil_rate_date_code
326 ,P_projfunc_rate_type =>l_projfunc_bil_rate_type
327 ,p_projfunc_bil_rate_date =>l_projfunc_bil_rate_date
328 ,P_funding_rate_type =>l_funding_rate_type
329 ,P_event_type_classification =>l_event_type_classification
330 ,P_multi_currency_billing_flag =>l_multi_currency_billing_flag
331 ,p_project_id =>p_project_id
332 ,p_projfunc_bil_exchange_rate =>l_projfunc_bil_exchange_rate -- Added for bug 3009307
333 ,p_funding_bil_rate_date_code => l_funding_rate_date_code --Added for bug 3053190
334 ,x_task_id =>p_task_id
335 ,x_organization_id =>p_organization_id
336 ,x_inv_org_id =>p_inv_org_id
337 ,x_agreement_id =>p_agreement_id -- Federal Uptake
338 ) = 'N'
339 Then
340 p_return_status := FND_API.G_RET_STS_ERROR;
341 RAISE FND_API.G_EXC_ERROR;
342 End If;
343
344 --Log Message
345 IF l_debug_mode = 'Y' THEN
346 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CREATE_EVENT.begin'
347 ,x_msg => 'Beginning Validate Flexfields'
348 ,x_log_level => 5);
349 END IF;
350
351
352 --Validating Flexfields
353 IF (p_event_in_rec.p_desc_flex_name IS NOT NULL)
354 AND (p_event_in_rec.p_desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
355 THEN
356 PA_EVENT_PVT.VALIDATE_FLEXFIELD
357 ( p_desc_flex_name => p_event_in_rec.p_desc_flex_name
358 ,p_attribute_category => p_event_in_rec.p_attribute_category
359 ,p_attribute1 => p_event_in_rec.p_attribute1
360 ,p_attribute2 => p_event_in_rec.p_attribute2
361 ,p_attribute3 => p_event_in_rec.p_attribute3
362 ,p_attribute4 => p_event_in_rec.p_attribute4
363 ,p_attribute5 => p_event_in_rec.p_attribute5
364 ,p_attribute6 => p_event_in_rec.p_attribute6
365 ,p_attribute7 => p_event_in_rec.p_attribute7
366 ,p_attribute8 => p_event_in_rec.p_attribute8
367 ,p_attribute9 => p_event_in_rec.p_attribute9
368 ,p_attribute10 => p_event_in_rec.p_attribute10
369 ,p_return_msg => p_return_msg
370 ,p_valid_status => p_validate_status);
371 IF p_validate_status = 'N'
372 THEN
373 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
374 THEN
375 pa_interface_utils_pub.map_new_amg_msg
376 ( p_old_message_code => 'PA_INVALID_FF_VALUES'
377 ,p_msg_attribute => 'CHANGE'
378 ,p_resize_flag => 'N'
379 ,p_msg_context => 'EVENT'
380 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
381 ,p_attribute2 => ''
382 ,p_attribute3 => ''
383 ,p_attribute4 => ''
384 ,p_attribute5 => '');
385 END IF;
386 p_return_status := FND_API.G_RET_STS_ERROR;
387 RAISE FND_API.G_EXC_ERROR;
388 END IF;
389 Else
390 p_event_in_rec.P_desc_flex_name :=NULL;
391 p_event_in_rec.P_attribute_category :=NULL;
392 p_event_in_rec.P_attribute1 :=NULL;
393 p_event_in_rec.P_attribute2 :=NULL;
394 p_event_in_rec.P_attribute3 :=NULL;
395 p_event_in_rec.P_attribute4 :=NULL;
396 p_event_in_rec.P_attribute5 :=NULL;
397 p_event_in_rec.P_attribute6 :=NULL;
398 p_event_in_rec.P_attribute7 :=NULL;
399 p_event_in_rec.P_attribute8 :=NULL;
400 p_event_in_rec.P_attribute9 :=NULL;
401 p_event_in_rec.P_attribute10 :=NULL;
402 END IF;
403
404 --Log Message
408 ,x_log_level => 5);
405 IF l_debug_mode = 'Y' THEN
406 pa_debug.write_log ( x_module => 'pa.plsql.PA_EVENT_PUB.CREATE_EVENT.begin'
407 ,x_msg => 'Validating or generating event number'
409 END IF;
410
411 -- Validating the event num.If found NULL it is populated before
412 -- inserting the record into pa_events.
413 If (p_event_in_rec.P_event_number Is NULL
414 OR p_event_in_rec.P_event_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
415 Then
416 --generating event number for project level events
417 If (P_task_id Is NULL)
418 Then
419 SELECT NVL(max(event_num),0)+1
420 INTO P_event_in_rec.p_event_number
421 FROM pa_events e
422 WHERE e.project_id = P_project_id
423 AND e.task_id IS NULL;
424 Else
425 --generating event number for task level events
426 SELECT NVL(max(event_num),0)+1
427 INTO P_event_in_rec.p_event_number
428 FROM pa_events e
429 WHERE e.project_id =P_project_id
430 AND e.task_id = P_task_id;
431 End If;
432 End If;
433
434 --Validating the adjust revenue flag
435 If upper(p_event_in_rec.P_adjusting_revenue_flag) = 'Y'
436 Then
437 p_event_in_rec.P_bill_trans_bill_amount := 0;
438 p_event_in_rec.P_bill_hold_flag := 'N';
439 p_event_in_rec.P_adjusting_revenue_flag :='Y';
440 End If;
441
442 --If event type is of revenue type(Write-Off), set bill trans bill amt = 0.
443
444 If l_event_type_classification In('WRITE OFF')
445 Then
446 p_event_in_rec.P_bill_trans_bill_amount:=0;
447 End If;
448
449 --If event type is of invoice type, set bill trans rev amt = 0
450
451 If l_event_type_classification In('DEFERRED REVENUE','INVOICE REDUCTION','SCHEDULED PAYMENTS')
452 Then
453 p_event_in_rec.P_bill_trans_rev_amount:=0;
454 End If;
455
456 --If event type = 'Write-On' bill trans bill amt = rev amt.
457
458 If (l_event_type_classification = 'WRITE ON')
459 Then
460 p_event_in_rec.P_bill_trans_bill_amount := p_event_in_rec.P_bill_trans_rev_amount;
461 End If;
462
463 --If P_description is NULL then set default as event_type
464
465 If(p_event_in_rec.P_description Is NULL)
466 Then
467 p_event_in_rec.P_description:=p_event_in_rec.P_event_type;
468 End If;
469
470 --Defaulting adjusting revenue flag
471 If(p_event_in_rec.P_adjusting_revenue_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
472 Then
473 p_event_in_rec.P_adjusting_revenue_flag :=NULL;
474 End If;
475
476 --Defaulting bill hold flag
477 If(p_event_in_rec.P_bill_hold_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
478 Then
479 p_event_in_rec.P_bill_hold_flag :=NULL;
480 End If;
481
482
483 --Log Message
484 IF l_debug_mode = 'Y' THEN
485 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CREATE_EVENT.begin'
486 ,x_msg => 'Beginning defaulting mcb parameters'
487 ,x_log_level => 5);
488 END IF;
489
490 --Before defaulting the mcb related paramaters validating
491 --if the User has overwritten any of the fields.
492 If (l_multi_currency_billing_flag = 'Y')
493 Then
494 /* Moved the following code here from a later point for code merge of bug 5458861 */
495 --Get the conversion_type for funding_rate_type, projfunc_rate_type and project_rate_type for bug3009239
496
497 IF p_event_in_rec.p_funding_rate_type is not null
498 THEN
499 SELECT conversion_type
500 INTO l_funding_rate_type
501 FROM pa_conversion_types_v
502 WHERE user_conversion_type = p_event_in_rec.p_funding_rate_type
503 or conversion_type = p_event_in_rec.p_funding_rate_type;
504 p_event_in_rec.p_funding_rate_type := l_funding_rate_type;
505 END IF;
506
507 IF p_event_in_rec.p_projfunc_rate_type is not null
508 THEN
509 SELECT conversion_type
510 INTO l_projfunc_bil_rate_type
511 FROM pa_conversion_types_v
512 WHERE user_conversion_type = p_event_in_rec.p_projfunc_rate_type
513 or conversion_type = p_event_in_rec.p_projfunc_rate_type;
514 p_event_in_rec.p_projfunc_rate_type := l_projfunc_bil_rate_type;
515 END IF;
516
517 IF p_event_in_rec.p_project_rate_type is not null
518 THEN
519 SELECT conversion_type
520 INTO l_project_bil_rate_type
521 FROM pa_conversion_types_v
522 WHERE user_conversion_type = p_event_in_rec.p_project_rate_type
523 or conversion_type = p_event_in_rec.p_project_rate_type;
524 p_event_in_rec.p_project_rate_type := l_project_bil_rate_type;
525 END IF;
526
527 --till here for code merge of bug 5458861
528
529
530 --validating and defaulting bill transaction currency code
531 If (p_event_in_rec.P_bill_trans_currency_code Is NULL
532 OR p_event_in_rec.P_bill_trans_currency_code=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
533 Then
537 --funding rate,date and type validations
534 p_event_in_rec.P_bill_trans_currency_code:=l_projfunc_currency_code;
535 End If;
536
538 If(p_event_in_rec.P_funding_rate_type Is NULL
539 OR p_event_in_rec.P_funding_rate_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
540 Then
541 p_event_in_rec.P_funding_rate_type:=l_funding_rate_type;
542 End If;
543 If (l_funding_rate_date_code = 'FIXED_DATE')
544 Then
545 If(p_event_in_rec.P_funding_rate_date Is NULL
546 OR p_event_in_rec.P_funding_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
547 Then
548 p_event_in_rec.P_funding_rate_date:=l_funding_rate_date;
549 End If;
550 Else
551 --Commented for Bug3009239
552 -- p_event_in_rec.P_funding_rate_date:=l_funding_rate_date;
553 --Added for Bug3009239
554 If(p_event_in_rec.P_funding_rate_date Is NULL
555 OR p_event_in_rec.P_funding_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
556 Then
557 p_event_in_rec.P_funding_rate_date:=l_funding_rate_date;
558 End If;
559 --till here for Bug3009239
560 End If;
561 --If fund rate type is User then only take the exchange rate from User
562 --else default it from the project level.
563 /* The following block of code has been commented for bug 3045302
564 If (p_event_in_rec.P_funding_rate_type <>l_funding_rate_type)
565 Then
566 If ( p_event_in_rec.P_funding_rate_type <> 'User' )
567 Then
568 p_event_in_rec.P_funding_exchange_rate :=NULL;
569 Else
570 If(p_event_in_rec.P_funding_exchange_rate Is NULL
571 OR p_event_in_rec.P_funding_exchange_rate =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
572 Then
573 p_event_in_rec.P_funding_exchange_rate:=l_funding_exchange_rate;
574 End If;
575 p_event_in_rec.P_funding_rate_date := null; --Added for Bug3013256
576 End If;
577
578 Else
579 If(p_event_in_rec.P_funding_rate_type = 'User'
580 AND (p_event_in_rec.P_funding_exchange_rate Is NULL
581 OR p_event_in_rec.P_funding_exchange_rate =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
582 Then
583 p_event_in_rec.P_funding_exchange_rate :=l_funding_exchange_rate;
584 p_event_in_rec.P_funding_rate_date := null; --Added for Bug3010927
585 --Added for Bug3013256
586 Else
587 If ( p_event_in_rec.P_funding_rate_type <> 'User' ) THEN
588 p_event_in_rec.p_funding_exchange_rate := null;
589 End If;
590 --till here for Bug3013256
591 End If;
592 End If;
593 End of comment for bug 3045302 */
594
595 /*Code added for bug 3045302 */
596 If ( p_event_in_rec.P_funding_rate_type <> 'User' )
597 Then
598 p_event_in_rec.P_funding_exchange_rate :=NULL;
599 Else
600 If(p_event_in_rec.P_funding_exchange_rate Is NULL
601 OR p_event_in_rec.P_funding_exchange_rate =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
602 Then
603 p_event_in_rec.P_funding_exchange_rate:=l_funding_exchange_rate;
604 End If;
605 p_event_in_rec.P_funding_rate_date := null; --Added for Bug3013256
606 End If;
607 /*End of code change for Bug 3045302 */
608 /*The code for validation of project currency attributes has been moved from here
609 to below the validation of project functional currency attributes for
610 bug 3045302*/
611 --project functional rate,date,type validaions
612 If (p_event_in_rec.P_bill_trans_currency_code = l_projfunc_currency_code )
613 Then
614 p_event_in_rec.P_projfunc_rate_type :=NULL;
615 p_event_in_rec.P_projfunc_rate_date :=NULL;
616 p_event_in_rec.P_projfunc_exchange_rate :=NULL;
617
618 --start of validtions when project functional currency and bill trans currency are different.
619 Else
620 If(p_event_in_rec.P_projfunc_rate_type Is NULL
621 OR p_event_in_rec.P_projfunc_rate_type=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
622 Then
623 p_event_in_rec.P_projfunc_rate_type:=l_projfunc_bil_rate_type;
624 End If;
625
626 If (l_projfunc_bil_rate_date_code= 'FIXED_DATE')
627 Then
628 If(p_event_in_rec.P_projfunc_rate_date Is NULL
629 OR p_event_in_rec.P_projfunc_rate_date=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
630 Then
631 p_event_in_rec.P_projfunc_rate_date:=l_projfunc_bil_rate_date;
632 End If;
633 Else
634 --Commented for Bug3009239
635 -- p_event_in_rec.P_projfunc_rate_date:=l_projfunc_bil_rate_date;
636 --Added for Bug3009239
637 If(p_event_in_rec.P_projfunc_rate_date Is NULL
641 End If;
638 OR p_event_in_rec.P_projfunc_rate_date=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
639 Then
640 p_event_in_rec.P_projfunc_rate_date:=l_projfunc_bil_rate_date;
642 --till here for Bug3009239
643
644 End If;
645
646 If(p_event_in_rec.P_projfunc_rate_type <> l_projfunc_bil_rate_type)
647 Then
648 If ( p_event_in_rec.P_projfunc_rate_type <> 'User' )
649 Then
650 p_event_in_rec.P_projfunc_exchange_rate:=NULL;
651 ELSE
652 If (p_event_in_rec.P_projfunc_exchange_rate Is NULL
653 OR p_event_in_rec.P_projfunc_exchange_rate=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
654 Then
655 p_event_in_rec.P_projfunc_exchange_rate:=l_projfunc_bil_exchange_rate;
656 End If;
657 p_event_in_rec.P_projfunc_rate_date := null; --Added for Bug3010927
658 End If;
659 Else
660 If(p_event_in_rec.P_projfunc_rate_type = 'User')
661 Then
662 If (p_event_in_rec.P_projfunc_exchange_rate Is NULL
663 OR p_event_in_rec.P_projfunc_exchange_rate=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
664 Then
665 p_event_in_rec.P_projfunc_exchange_rate:=l_projfunc_bil_exchange_rate;
666 End If;
667 p_event_in_rec.P_projfunc_rate_date := null; --Added for Bug3010927
668 Else
669 --Commented for Bug3013256
670 --p_event_in_rec.P_projfunc_exchange_rate:=l_projfunc_bil_exchange_rate;
671 p_event_in_rec.P_projfunc_exchange_rate:=null; --Added for Bug3013256
672 End If;
673 End If;
674 End If;
675
676 --project rate,date and type validations
677 If (p_event_in_rec.P_bill_trans_currency_code = l_project_currency_code)
678 Then
679 p_event_in_rec.P_project_rate_type :=NULL;
680 p_event_in_rec.P_project_rate_date :=NULL;
681 p_event_in_rec.P_project_exchange_rate :=NULL;
682
683 --start of validations when project currency and bill trans currency are different.
684 --If project currency code is same as projfunc currency code default the
685 --attributes of project currency from project functional currency attributes.
686 --Change for bug 3045302
687 Elsif (l_project_currency_code= l_projfunc_currency_code)
688 Then
689 p_event_in_rec.P_project_rate_type := p_event_in_rec.P_projfunc_rate_type;
690 p_event_in_rec.P_project_rate_date := p_event_in_rec.P_projfunc_rate_date;
691 p_event_in_rec.P_project_exchange_rate := p_event_in_rec.P_projfunc_exchange_rate;
692 Else
693 If(p_event_in_rec.P_project_rate_type Is NULL
694 OR p_event_in_rec.P_project_rate_type =PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
695 Then
696 p_event_in_rec.P_project_rate_type:=l_project_bil_rate_type;
697 End If;
698
699 If (l_project_bil_rate_date_code = 'FIXED_DATE')
700 Then
701 If(p_event_in_rec.P_project_rate_date Is NULL
702 OR p_event_in_rec.P_project_rate_date =PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
703 Then
704 p_event_in_rec.P_project_rate_date:=l_project_bil_rate_date;
705 End If;
706 Else
707 --Commented for Bug3009239
708 -- p_event_in_rec.P_project_rate_date:=l_project_bil_rate_date;
709 --Added for Bug3009239
710 If(p_event_in_rec.P_project_rate_date Is NULL
711 OR p_event_in_rec.P_project_rate_date =PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
712 Then
713 p_event_in_rec.P_project_rate_date:=l_project_bil_rate_date;
714 End If;
715 --till here for Bug3009239
716 End If;
717
718 If (p_event_in_rec.P_project_rate_type<>l_project_bil_rate_type)
719 Then
720 If ( p_event_in_rec.P_project_rate_type <> 'User' )
721 Then
722 p_event_in_rec.P_project_exchange_rate:=NULL;
723 ELSE
724 If(p_event_in_rec.P_project_exchange_rate Is NULL
725 OR p_event_in_rec.P_project_exchange_rate=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
726 Then
727 p_event_in_rec.P_project_exchange_rate:=l_project_bil_exchange_rate;
728 End If;
729 p_event_in_rec.P_project_rate_date := null; --Added for Bug3045302
730 End If;
731 Else
732 If(p_event_in_rec.P_project_rate_type= 'User')
733 Then
734 If(p_event_in_rec.P_project_exchange_rate Is NULL
735 OR p_event_in_rec.P_project_exchange_rate=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
736 Then
737 p_event_in_rec.P_project_exchange_rate:=l_project_bil_exchange_rate;
738 End If;
739 p_event_in_rec.P_project_rate_date := null; --Added for Bug3010927
740 Else
741 --Commented for Bug 3013256
742 --p_event_in_rec.P_project_exchange_rate:=l_project_bil_exchange_rate;
746 End If;
743 p_event_in_rec.P_project_exchange_rate := null; --Added for Bug3013256
744 End If;
745 End If;
747
748 Else
749 --Defaulting the mcb columns when mcb is not enabled
750 p_event_in_rec.P_project_rate_type:=l_project_bil_rate_type;
751 p_event_in_rec.P_project_rate_date:=l_project_bil_rate_date;
752 p_event_in_rec.P_project_exchange_rate:=l_project_bil_exchange_rate;
753
754 p_event_in_rec.P_projfunc_rate_type:=l_projfunc_bil_rate_type;
755 p_event_in_rec.P_projfunc_rate_date:=l_projfunc_bil_rate_date;
756 p_event_in_rec.P_projfunc_exchange_rate:=l_projfunc_bil_exchange_rate;
757
758 p_event_in_rec.P_funding_rate_type:=l_funding_rate_type;
759 p_event_in_rec.P_funding_rate_date:=l_funding_rate_date;
760 p_event_in_rec.P_funding_exchange_rate:=l_funding_exchange_rate;
761
762 p_event_in_rec.P_bill_trans_currency_code:=l_projfunc_currency_code;
763 End If;
764
765 --Assigning null to event_id
766 p_event_in_rec.p_event_id:=NULL;
767
768 --Log Message
769 IF l_debug_mode = 'Y' THEN
770 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CREATE_EVENT.begin'
771 ,x_msg => 'Beginning Insert event. '
772 ,x_log_level => 5);
773 END IF;
774
775 /* Moving the following code block to an earlier part of this procedure for code merge of bug 5458861 */
776
777 --Get the conversion_type for funding_rate_type, projfunc_rate_type and project_rate_type for bug3009239
778 /* IF p_event_in_rec.p_funding_rate_type is not null
779 THEN
780 SELECT conversion_type
781 INTO l_funding_rate_type
782 FROM pa_conversion_types_v
783 WHERE user_conversion_type = p_event_in_rec.p_funding_rate_type
784 or conversion_type = p_event_in_rec.p_funding_rate_type;
785 p_event_in_rec.p_funding_rate_type := l_funding_rate_type;
786 END IF;
787
788 IF p_event_in_rec.p_projfunc_rate_type is not null
789 THEN
790 SELECT conversion_type
791 INTO l_projfunc_bil_rate_type
792 FROM pa_conversion_types_v
793 WHERE user_conversion_type = p_event_in_rec.p_projfunc_rate_type
794 or conversion_type = p_event_in_rec.p_projfunc_rate_type;
795 p_event_in_rec.p_projfunc_rate_type := l_projfunc_bil_rate_type;
796 END IF;
797
798 IF p_event_in_rec.p_project_rate_type is not null
799 THEN
800 SELECT conversion_type
801 INTO l_project_bil_rate_type
802 FROM pa_conversion_types_v
803 WHERE user_conversion_type = p_event_in_rec.p_project_rate_type
804 or conversion_type = p_event_in_rec.p_project_rate_type;
805 p_event_in_rec.p_project_rate_type := l_project_bil_rate_type;
806 END IF;
807 --till here for bug3009239
808 Commented for code merge of bug 5458861 */
809
810 -- Adding code to populate invoice and revenue attributes
811
812 IF l_invproc_currency_type = 'PROJECT_CURRENCY' THEN
813 l_invproc_currency_code := l_project_currency_code;
814 l_invproc_rate_type := p_event_in_rec.P_project_rate_type;
815 l_invproc_rate_date := p_event_in_rec.P_project_rate_date;
816 l_invproc_exchange_rate := p_event_in_rec.P_project_exchange_rate;
817 ELSIF l_invproc_currency_type = 'PROJFUNC_CURRENCY' THEN
818 l_invproc_currency_code := l_projfunc_currency_code;
819 l_invproc_rate_type := p_event_in_rec.P_projfunc_rate_type;
820 l_invproc_rate_date := p_event_in_rec.P_projfunc_rate_date;
821 l_invproc_exchange_rate := p_event_in_rec.P_projfunc_exchange_rate;
822 ELSIF l_invproc_currency_type = 'FUNDING_CURRENCY' THEN
823 l_invproc_currency_code := '';
824 l_invproc_rate_type := p_event_in_rec.P_funding_rate_type;
825 l_invproc_rate_date := p_event_in_rec.P_funding_rate_date;
826 l_invproc_exchange_rate := p_event_in_rec.P_funding_exchange_rate;
827
828 END IF;
829
830 IF l_revproc_currency_code = l_projfunc_currency_code THEN
831 l_revproc_currency_code := l_projfunc_currency_code;
832 l_revproc_rate_type := p_event_in_rec.P_projfunc_rate_type;
833 l_revproc_rate_date := p_event_in_rec.P_projfunc_rate_date;
834 l_revproc_exchange_rate := p_event_in_rec.P_projfunc_exchange_rate;
835 END IF;
836
837 -- Till here
838
839 --Call to table handler to insert the events into the database
840 --Calling PA_EVENTS_PKG.
841
842 PA_EVENTS_PKG.Insert_Row
843 (X_Rowid =>rowid
844 ,X_Event_Id =>p_event_in_rec.p_event_id
845 ,X_product_code =>p_pm_product_code
846 ,X_event_reference =>p_event_in_rec.p_pm_event_reference
847 ,X_Task_Id =>P_task_id
848 ,X_Event_Num =>p_event_in_rec.p_event_number
849 ,X_Last_Update_Date =>SYSDATE
850 ,X_Last_Updated_By =>G_USER_ID
854 ,X_Event_Type =>p_event_in_rec.P_event_type
851 ,X_Creation_Date =>SYSDATE
852 ,X_Created_By =>G_USER_ID
853 ,X_Last_Update_Login =>G_LOGIN_ID
855 ,X_Description =>p_event_in_rec.P_description
856 ,X_Bill_Amount =>0
857 ,X_Revenue_Amount =>0
858 ,X_Revenue_Distributed_Flag =>'N'
859 ,X_Bill_Hold_Flag =>p_event_in_rec.P_bill_hold_flag
860 ,X_Completion_date =>p_event_in_rec.P_completion_date
861 ,X_Rev_Dist_Rejection_Code =>NULL
862 ,X_Attribute_Category =>p_event_in_rec.P_attribute_category
863 ,X_Attribute1 =>p_event_in_rec.P_attribute1
864 ,X_Attribute2 =>p_event_in_rec.P_attribute2
865 ,X_Attribute3 =>p_event_in_rec.P_attribute3
866 ,X_Attribute4 =>p_event_in_rec.P_attribute4
867 ,X_Attribute5 =>p_event_in_rec.P_attribute5
868 ,X_Attribute6 =>p_event_in_rec.P_attribute6
869 ,X_Attribute7 =>p_event_in_rec.P_attribute7
870 ,X_Attribute8 =>p_event_in_rec.P_attribute8
871 ,X_Attribute9 =>p_event_in_rec.P_attribute9
872 ,X_Attribute10 =>p_event_in_rec.P_attribute10
873 ,X_Project_Id =>P_project_id
874 ,X_Organization_Id =>P_Organization_Id
875 ,X_Billing_Assignment_Id =>NULL
876 ,X_Event_Num_Reversed =>NULL
877 ,X_Calling_Place =>NULL
878 ,X_Calling_Process =>NULL
879 ,X_Bill_Trans_Currency_Code =>p_event_in_rec.P_bill_trans_currency_code
880 ,X_Bill_Trans_Bill_Amount =>p_event_in_rec.P_bill_trans_bill_amount
881 ,X_Bill_Trans_rev_Amount =>p_event_in_rec.P_bill_trans_rev_amount
882 ,X_Project_Currency_Code =>l_project_currency_code
883 ,X_Project_Rate_Type =>p_event_in_rec.P_project_rate_type
884 ,X_Project_Rate_Date =>p_event_in_rec.P_project_rate_date
885 ,X_Project_Exchange_Rate =>p_event_in_rec.P_project_exchange_rate
886 ,X_Project_Inv_Rate_Date =>NULL
887 ,X_Project_Inv_Exchange_Rate =>NULL
888 ,X_Project_Bill_Amount =>NULL
889 ,X_Project_Rev_Rate_date =>NULL
890 ,X_Project_Rev_Exchange_Rate =>NULL
891 ,X_Project_Revenue_Amount =>NULL
892 ,X_ProjFunc_Currency_Code =>l_projfunc_currency_code
893 ,X_ProjFunc_Rate_Type =>p_event_in_rec.P_projfunc_rate_type
894 ,X_ProjFunc_Rate_date =>p_event_in_rec.P_projfunc_rate_date
895 ,X_ProjFunc_Exchange_Rate =>p_event_in_rec.P_projfunc_exchange_rate
896 ,X_ProjFunc_Inv_Rate_date =>NULL
897 ,X_ProjFunc_Inv_Exchange_Rate =>NULL
898 ,X_ProjFunc_Bill_Amount =>NULL
899 ,X_ProjFunc_Rev_Rate_date =>NULL
900 ,X_Projfunc_Rev_Exchange_Rate =>NULL
901 ,X_ProjFunc_Revenue_Amount =>NULL
902 ,X_Funding_Rate_Type =>p_event_in_rec.P_funding_rate_type
903 ,X_Funding_Rate_date =>p_event_in_rec.P_funding_rate_date
904 ,X_Funding_Exchange_Rate =>p_event_in_rec.P_funding_exchange_rate
905 ,X_Invproc_Currency_Code =>l_invproc_currency_code
906 ,X_Invproc_Rate_Type =>l_invproc_rate_type
907 ,X_Invproc_Rate_date =>l_invproc_rate_date
908 ,X_Invproc_Exchange_Rate =>l_invproc_exchange_rate
909 ,X_Revproc_Currency_Code =>l_revproc_currency_code
910 ,X_Revproc_Rate_Type =>l_revproc_rate_type
911 ,X_Revproc_Rate_date =>l_revproc_rate_date
912 ,X_Revproc_Exchange_Rate =>l_revproc_exchange_rate
913 ,X_Inv_Gen_Rejection_Code =>NULL
914 ,X_Adjusting_Revenue_Flag =>p_event_in_rec.P_adjusting_revenue_flag
915 ,X_inventory_org_id =>p_inv_org_id
916 ,X_inventory_item_id =>p_event_in_rec.P_inventory_item_id
917 ,X_quantity_billed =>p_event_in_rec.P_quantity_billed
918 ,X_uom_code =>p_event_in_rec.P_uom_code
919 ,X_unit_price =>p_event_in_rec.P_unit_price
920 ,X_reference1 =>p_event_in_rec.P_reference1
921 ,X_reference2 =>p_event_in_rec.P_reference2
922 ,X_reference3 =>p_event_in_rec.P_reference3
923 ,X_reference4 =>p_event_in_rec.P_reference4
924 ,X_reference5 =>p_event_in_rec.P_reference5
925 ,X_reference6 =>p_event_in_rec.P_reference6
926 ,X_reference7 =>p_event_in_rec.P_reference7
927 ,X_reference8 =>p_event_in_rec.P_reference8
928 ,X_reference9 =>p_event_in_rec.P_reference9
932 ,X_Record_Version_Number => 1
929 ,X_reference10 =>p_event_in_rec.P_reference10
930 ,X_Deliverable_Id =>p_event_in_rec.P_deliverable_id
931 ,X_Action_Id =>p_event_in_rec.P_action_id
933 ,X_Agreement_Id =>p_agreement_id); -- Fedral Uptake
934
935 --If commit is set to true then commit to database.
936 IF FND_API.to_boolean( p_commit )
937 THEN
938 COMMIT;
939 END IF;
940
941 EXCEPTION
942 WHEN FND_API.G_EXC_ERROR
943 THEN
944 ROLLBACK TO create_event;
945 l_return_status := FND_API.G_RET_STS_ERROR;
946
947 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
948 THEN
949 ROLLBACK TO create_event;
950 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
951
952 WHEN pa_event_pvt.pub_excp
953 THEN
954 ROLLBACK TO create_event;
955 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
956 PA_EVENT_PUB.PACKAGE_NAME
957 :='(Event Reference='||p_event_in_rec.p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
958 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'CREATE_EVENT';
959
960 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
961 THEN
962 FND_MSG_PUB.add_exc_msg
963 ( p_pkg_name => PACKAGE_NAME
964 , p_procedure_name => PROCEDURE_NAME );
965 PACKAGE_NAME:=NULL;
966 PROCEDURE_NAME:=NULL;
967 END IF;
968
969 WHEN OTHERS
970 THEN
971 ROLLBACK TO create_event;
972 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973 PA_EVENT_PUB.PACKAGE_NAME
974 :='(Event Reference='||p_event_in_rec.p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
975 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CREATE_EVENT';
976
977 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
978 THEN
979 FND_MSG_PUB.add_exc_msg
980 ( p_pkg_name => PACKAGE_NAME
981 , p_procedure_name => PROCEDURE_NAME );
982 PACKAGE_NAME:=NULL;
983 PROCEDURE_NAME:=NULL;
984 END IF;
985
986 END; --end of Inner Block
987
988 --Populating the output table
989 p_event_out_tbl(tot_out_rec).pm_event_reference := p_event_in_rec.p_pm_event_reference;
990 p_event_out_tbl(tot_out_rec).Event_Id := p_event_in_rec.P_event_id;
991 p_event_out_tbl(tot_out_rec).Return_status := P_return_status;
992 tot_out_rec := tot_out_rec + 1;
993 tot_in_rec := p_event_in_tbl.next(tot_in_rec);
994
995 pa_debug.reset_err_stack; -- Reset error stack
996 END LOOP; -- End of loop
997
998
999 --Setting the return status to false even if one record fails the validation.
1000 p_return_status := l_return_status;
1001
1002 --Extracting and returning error message if message count is 1.
1003 FND_MSG_PUB.Count_And_Get
1004 ( p_encoded => l_encoded
1005 ,p_count => p_msg_count
1006 ,p_data => p_msg_data );
1007
1008
1009 EXCEPTION
1010 WHEN FND_API.G_EXC_ERROR
1011 THEN
1012 p_return_status := FND_API.G_RET_STS_ERROR;
1013 FND_MSG_PUB.Count_And_Get
1014 ( p_encoded => l_encoded
1015 ,p_count => p_msg_count
1016 ,p_data => p_msg_data );
1017
1018 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1019 THEN
1020 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1021 FND_MSG_PUB.Count_And_Get
1022 ( p_encoded => l_encoded
1023 ,p_count => p_msg_count
1024 ,p_data => p_msg_data );
1025
1026 WHEN pa_event_pvt.pub_excp
1027 THEN
1028 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1029 PA_EVENT_PUB.PACKAGE_NAME
1030 :='(event_reference='||p_event_in_rec.p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
1031 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'CREATE_EVENT';
1032
1033 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1034 THEN
1035 FND_MSG_PUB.add_exc_msg
1036 ( p_pkg_name => PACKAGE_NAME
1037 , p_procedure_name => PROCEDURE_NAME );
1038 PACKAGE_NAME:=NULL;
1039 PROCEDURE_NAME:=NULL;
1040 END IF;
1041
1042 FND_MSG_PUB.Count_And_Get
1043 ( p_encoded => l_encoded
1044 ,p_count => p_msg_count
1045 ,p_data => p_msg_data );
1046
1047 WHEN OTHERS
1048 THEN
1049 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1050 PA_EVENT_PUB.PACKAGE_NAME
1051 :='(event_reference='||p_event_in_rec.p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
1055 THEN
1052 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CREATE_EVENT';
1053
1054 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1056 FND_MSG_PUB.add_exc_msg
1057 ( p_pkg_name => PACKAGE_NAME
1058 , p_procedure_name => PROCEDURE_NAME );
1059 PACKAGE_NAME:=NULL;
1060 PROCEDURE_NAME:=NULL;
1061 END IF;
1062
1063 FND_MSG_PUB.Count_And_Get
1064 ( p_encoded => l_encoded
1065 ,p_count => p_msg_count
1066 ,p_data => p_msg_data );
1067
1068 END create_event;
1069 -- ============================================================================
1070 --
1071 --Name: update_event
1072 --Type: procedure
1073 --Description: This API updates an existing event or a set of existing events
1074 --
1075 --Called subprograms: PA_EVENT_PVT.CHECK_MDTY_PARAMS1
1076 -- PA_EVENT_PVT.CHECK_MDTY_PARAMS2
1077 -- PA_EVENT_PVT.check_update_event_ok
1078 -- PA_EVENT_PVT.VALIDATE_FLEXFIELD
1079 -- pa_events_pkg.update_row
1080 --
1081 --
1082 --
1083 --History:
1084
1085 -- ============================================================================
1086
1087 PROCEDURE UPDATE_EVENT
1088 ( p_api_version_number IN NUMBER
1089 ,p_commit IN VARCHAR2
1090 ,p_init_msg_list IN VARCHAR2
1091 ,p_pm_product_code IN VARCHAR2
1092 ,p_event_in_tbl IN Event_In_Tbl_Type
1093 ,p_event_out_tbl OUT NOCOPY Event_Out_Tbl_Type --File.Sql.39 bug 4440895
1094 ,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1095 ,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1096 ,p_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1097 AS
1098
1099 Cursor chk_proj_curs(P_project_number VARCHAR2)
1100 Is
1101 Select project_id
1102 From pa_projects_all
1103 Where segment1 = P_project_number;
1104
1105 p_inv_org_id pa_events.inventory_item_id%type;
1106 P_project_id pa_events.project_id%type;
1107 l_project_id pa_projects_all.project_id%type;
1108 P_task_id pa_events.task_id%type;
1109 l_task_id pa_events.task_id%type;
1110 P_Organization_Id pa_projects_all.org_id%type;
1111 l_event_type_classification pa_event_types.event_type_classification%type;
1112 P_agreement_id pa_events.agreement_id%type; -- Federal Uptake
1113
1114 P_event_id_out pa_events.event_id%type;
1115 Rowid VARCHAR2(18);
1116 p_event_in_rec Event_Rec_In_Type;
1117 p_event_out_rec Event_Rec_Out_Type;
1118 tot_in_rec NUMBER:=0;
1119 tot_out_rec NUMBER:=0;
1120 p_api_name VARCHAR2(100):='UPDATE_EVENT';
1121 p_return_msg VARCHAR2(2000);
1122 p_validate_status VARCHAR2(1):='Y';
1123 l_bill_trans_curr_code pa_events.Bill_Trans_Currency_Code%type;
1124 l_project_currency_code pa_events.Project_currency_code%type;
1125 l_Project_Inv_Exchange_Rate pa_events.Project_Inv_Exchange_Rate%type;
1126 l_Project_Bill_Amount pa_events.Project_Bill_Amount%type;
1127 l_Project_Rev_Rate_date pa_events.Project_Rev_Rate_date%type;
1128 l_Project_Rev_Exchange_Rate pa_events.Project_Rev_Exchange_Rate%type;
1129 l_Project_Revenue_Amount pa_events.Project_Revenue_Amount%type;
1130 l_ProjFunc_Currency_Code pa_events.ProjFunc_Currency_Code%type;
1131 l_ProjFunc_Inv_Rate_date pa_events.ProjFunc_Inv_Rate_date%type;
1132 l_ProjFunc_Inv_Exchange_Rate pa_events.ProjFunc_Inv_Exchange_Rate%type;
1133 l_ProjFunc_Bill_Amount pa_events.ProjFunc_Bill_Amount%type;
1134 l_ProjFunc_Rev_Rate_date pa_events.ProjFunc_Rev_Rate_date%type;
1135 l_Projfunc_Rev_Exchange_Rate pa_events.Projfunc_Rev_Exchange_Rate%type;
1136 l_ProjFunc_Revenue_Amount pa_events.ProjFunc_Revenue_Amount%type;
1137 l_Invproc_Currency_Code pa_events.Invproc_Currency_Code%type;
1138 l_Invproc_Rate_Type pa_events.Invproc_Rate_Type%type;
1139 l_Invproc_Rate_date pa_events.Invproc_Rate_date%type;
1140 l_Invproc_Exchange_Rate pa_events.Invproc_Exchange_Rate%type;
1141 l_Revproc_Currency_Code pa_events.Revproc_Currency_Code%type;
1142 l_Revproc_Rate_Type pa_events.Revproc_Rate_Type%type;
1143 l_Revproc_Rate_date pa_events.Revproc_Rate_date%type;
1144 l_revproc_exchange_rate pa_events.revproc_exchange_rate%type;
1145 l_Inv_Gen_Rejection_Code pa_events.Inv_Gen_Rejection_Code%type;
1146 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%type;
1147 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%type;
1148 l_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%type;
1149 l_project_bil_rate_type pa_events.project_rate_type%type;
1150 l_project_bil_rate_date pa_events.project_rate_date%type;
1151 l_project_bil_exchange_rate pa_events.project_exchange_rate%type;
1152 l_projfunc_bil_rate_type pa_events.projfunc_rate_type%type;
1153 l_projfunc_bil_rate_date pa_events.projfunc_rate_date%type;
1154 L_PROJFUNC_BIL_EXCHANGE_RATE pa_events.PROJFUNC_EXCHANGE_RATE%type;
1155 L_FUNDING_RATE_TYPE pa_events.FUNDING_RATE_TYPE%type;
1156 L_FUNDING_RATE_DATE pa_events.FUNDING_RATE_DATE%type;
1157 L_FUNDING_EXCHANGE_RATE pa_events.FUNDING_EXCHANGE_RATE%type;
1158 l_bill_trans_rev_amt pa_events.bill_trans_rev_amount%type;
1162 L_adjusting_revenue_flag pa_events.adjusting_revenue_flag%type;
1159 l_bill_trans_bill_amt pa_events.bill_trans_bill_amount%type;
1160 l_description pa_events.description%type;
1161 l_bill_hold_flag pa_events.bill_hold_flag%type;
1163 l_inventory_org_id pa_events.inventory_org_id%type;
1164 l_inventory_item_id pa_events.inventory_item_id%type;
1165 l_organization_id pa_events.organization_id%type;
1166 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
1167 l_encoded varchar2(1):='F';
1168 l_return_status varchar2(1):= FND_API.G_RET_STS_SUCCESS;
1169 l_record_version_number pa_events.record_version_number%TYPE;
1170
1171 /* Added for bug 7110782 */
1172 l_event_processed varchar2(1) := 'N';
1173 l_bill_amount pa_events.bill_amount%type;
1174 l_revenue_amount pa_events.revenue_amount%type;
1175 l_revenue_distributed_flag pa_events.revenue_distributed_flag%type;
1176 l_rev_dist_rejection_code pa_events.rev_dist_rejection_code%type;
1177 l_Billing_Assignment_Id pa_events.Billing_Assignment_Id%type;
1178 l_Event_Num_Reversed pa_events.Event_Num_Reversed%type;
1179 l_Calling_Place pa_events.Calling_Place%type;
1180 l_Calling_Process pa_events.Calling_Process%type;
1181 /* Added for bug 7110782 */
1182
1183 BEGIN
1184
1185 -- Initialize the Error Stack
1186 pa_debug.set_err_stack('PA_EVENT_PUB.UPDATE_EVENT');
1187
1188 --Log Message
1189 IF l_debug_mode = 'Y' THEN
1190 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.UPDATE_EVENT.begin'
1191 ,x_msg => 'Beginning of Update Event'
1192 ,x_log_level => 5);
1193 END IF;
1194
1195 p_msg_count := 0;
1196 p_return_status := FND_API.G_RET_STS_SUCCESS;
1197
1198 --Log Message
1199 IF l_debug_mode = 'Y' THEN
1200 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.UPDATE_EVENT.begin'
1201 ,x_msg => 'Calling mandatory input parameters-1'
1202 ,x_log_level => 5);
1203 END IF;
1204
1205 -- Validating mandatory input parameters-1
1206
1207 PA_EVENT_PVT.check_mdty_params1
1208 ( p_api_version_number =>p_api_version_number
1209 ,p_api_name =>p_api_name
1210 ,p_pm_product_code =>p_pm_product_code
1211 ,p_function_name =>'PA_EV_UPDATE_EVENT'
1212 ,x_return_status =>p_return_status
1213 ,x_msg_count =>p_msg_count
1214 ,x_msg_data =>p_msg_data );
1215
1216
1217 If p_return_status <> FND_API.G_RET_STS_SUCCESS
1218 Then
1219 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1220 THEN
1221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1222
1223 ELSIF p_return_status = FND_API.G_RET_STS_ERROR
1224 THEN
1225 RAISE FND_API.G_EXC_ERROR;
1226 END IF;
1227 End If;
1228
1229 tot_in_rec := p_event_in_tbl.first;
1230 while tot_in_rec is not null loop -- loop begins
1231 -- For all date variables using trunc instead of ltrim(rtrim)for bug 3053669
1232 P_event_in_rec.P_pm_event_reference :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_pm_event_reference));
1233 P_event_in_rec.P_task_number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_task_number));
1234 P_event_in_rec.P_event_number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_event_number));
1235 P_event_in_rec.P_event_type :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_event_type));
1236 -- Added the below three lines for Federal Uptake
1237 P_event_in_rec.P_agreement_number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_agreement_number));
1238 P_event_in_rec.P_agreement_type :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_agreement_type));
1239 P_event_in_rec.P_customer_number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_customer_number));
1240 P_event_in_rec.P_description :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_description));
1241 P_event_in_rec.P_bill_hold_flag :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_bill_hold_flag));
1242 P_event_in_rec.P_completion_date :=trunc(p_event_in_tbl(tot_in_rec).P_completion_date);
1243 P_event_in_rec.P_desc_flex_name :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_desc_flex_name));
1244 P_event_in_rec.P_attribute_category :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute_category));
1245 P_event_in_rec.P_attribute1 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute1));
1246 P_event_in_rec.P_attribute2 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute2));
1247 P_event_in_rec.P_attribute3 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute3));
1248 P_event_in_rec.P_attribute4 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute4));
1249 P_event_in_rec.P_attribute5 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute5));
1250 P_event_in_rec.P_attribute6 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute6));
1251 P_event_in_rec.P_attribute7 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute7));
1252 P_event_in_rec.P_attribute8 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute8));
1253 P_event_in_rec.P_attribute9 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute9));
1254 P_event_in_rec.P_attribute10 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_attribute10));
1258 P_event_in_rec.P_inventory_item_id :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_inventory_item_id));
1255 P_event_in_rec.P_project_number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_project_number));
1256 P_event_in_rec.P_organization_name :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_organization_name));
1257 P_event_in_rec.P_inventory_org_name :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_inventory_org_name));
1259 P_event_in_rec.P_quantity_billed :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_quantity_billed));
1260 P_event_in_rec.P_uom_code :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_uom_code));
1261 P_event_in_rec.P_unit_price :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_unit_price));
1262 P_event_in_rec.P_reference1 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference1));
1263 P_event_in_rec.P_reference2 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference2));
1264 P_event_in_rec.P_reference3 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference3));
1265 P_event_in_rec.P_reference4 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference4));
1266 P_event_in_rec.P_reference5 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference5));
1267 P_event_in_rec.P_reference6 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference6));
1268 P_event_in_rec.P_reference7 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference7));
1269 P_event_in_rec.P_reference8 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference8));
1270 P_event_in_rec.P_reference9 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference9));
1271 P_event_in_rec.P_reference10 :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_reference10));
1272 P_event_in_rec.P_bill_trans_currency_code :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_bill_trans_currency_code));
1273 P_event_in_rec.P_bill_trans_bill_amount :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_bill_trans_bill_amount));
1274 P_event_in_rec.P_bill_trans_rev_amount :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_bill_trans_rev_amount));
1275 P_event_in_rec.P_project_rate_type :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_project_rate_type));
1276 P_event_in_rec.P_project_rate_date :=trunc(p_event_in_tbl(tot_in_rec).P_project_rate_date);
1277 P_event_in_rec.P_project_exchange_rate :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_project_exchange_rate));
1278 P_event_in_rec.P_projfunc_rate_type :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_projfunc_rate_type));
1279 P_event_in_rec.P_projfunc_rate_date :=trunc(p_event_in_tbl(tot_in_rec).P_projfunc_rate_date);
1280 P_event_in_rec.P_projfunc_exchange_rate :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_projfunc_exchange_rate));
1281 P_event_in_rec.P_funding_rate_type :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_funding_rate_type));
1282 P_event_in_rec.P_funding_rate_date :=trunc(p_event_in_tbl(tot_in_rec).P_funding_rate_date);
1283 P_event_in_rec.P_funding_exchange_rate :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_funding_exchange_rate));
1284 P_event_in_rec.P_adjusting_revenue_flag :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_adjusting_revenue_flag));
1285 P_event_in_rec.P_event_id :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_event_id));
1286 P_event_in_rec.P_deliverable_id :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_deliverable_id));
1287 P_event_in_rec.P_action_id :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_action_id));
1288 P_event_in_rec.P_context :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_context));
1289 P_event_in_rec.P_Record_Version_Number :=ltrim(rtrim(p_event_in_tbl(tot_in_rec).P_record_version_number));
1290
1291 BEGIN --Start of Inner Block
1292
1293 --Set savepoint
1294 Savepoint Update_event;
1295
1296 --Log Message
1297 IF l_debug_mode = 'Y' THEN
1298 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.UPDATE_EVENT.begin'
1299 ,x_msg => ' Calling mandatory input parameters-2'
1300 ,x_log_level => 5);
1301 END IF;
1302
1303 -- Validating mandatory input parameters-2
1304 /*Commenting out this call as the event reference is not a mandatory parameter.bug 3118781
1305 PA_EVENT_PVT.CHECK_MDTY_PARAMS2
1306 ( p_pm_event_reference => p_event_in_rec.p_pm_event_reference
1307 ,p_pm_product_code => p_pm_product_code
1308 ,p_project_number => p_event_in_rec.p_project_number
1309 ,p_event_type => p_event_in_rec.p_event_type
1310 ,p_organization_name => p_event_in_rec.p_organization_name
1311 ,p_calling_place => 'UPDATE_EVENT'
1312 ,x_return_status => p_return_status );
1313
1314 If p_return_status <> FND_API.G_RET_STS_SUCCESS
1315 Then
1316 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1317 THEN
1318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1319
1320 ELSIF p_return_status = FND_API.G_RET_STS_ERROR
1321 THEN
1322 RAISE FND_API.G_EXC_ERROR;
1323 END IF;
1324 End If;
1325 End of comment for 3118781*/
1326 --Log Message
1327 IF l_debug_mode = 'Y' THEN
1328 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.UPDATE_EVENT.begin'
1329 ,x_msg => 'Calling conv_event_ref_to_id'
1330 ,x_log_level => 5);
1331 END IF;
1332
1333 --Call to validate the event_id or convert the event reference to event_id.
1334 --If the validation fails then terminate further validation for this record.
1335 If PA_EVENT_PVT.CONV_EVENT_REF_TO_ID
1336 (P_pm_product_code =>P_pm_product_code
1340 Then
1337 ,P_pm_event_reference =>p_event_in_rec.P_pm_event_reference
1338 ,P_event_id =>p_event_in_rec.P_event_id)
1339 ='N'
1341 p_return_status := FND_API.G_RET_STS_ERROR;
1342 RAISE FND_API.G_EXC_ERROR;
1343 End If;
1344
1345 --Deriving the project_id using the event_reference and product_code
1346 Select project_id, record_version_number
1347 Into P_project_id, p_event_in_rec.P_record_version_number
1348 From pa_events
1349 Where event_id = p_event_in_rec.P_event_id;
1350
1351
1352 --Deriving the project_id using the project_number if provided.
1353 Open chk_proj_curs(p_event_in_rec.P_project_number);
1354 Fetch chk_proj_curs Into l_project_id;
1355 Close chk_proj_curs;
1356
1357
1358 --validation to be done only if project number is provided.
1359 If (P_project_id <> nvl(l_project_id,P_project_id))
1360 Then
1361 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1362 THEN
1363 pa_interface_utils_pub.map_new_amg_msg
1364 ( p_old_message_code => 'PA_EV_REF_PROJ_MISS'
1365 ,p_msg_attribute => 'CHANGE'
1366 ,p_resize_flag => 'N'
1367 ,p_msg_context => 'EVENT'
1368 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
1369 ,p_attribute2 => ''
1370 ,p_attribute3 => ''
1371 ,p_attribute4 => ''
1372 ,p_attribute5 => '');
1373 END IF;
1374 p_return_status := FND_API.G_RET_STS_ERROR;
1375 RAISE FND_API.G_EXC_ERROR;
1376 End If;
1377
1378 --Log Message
1379 IF l_debug_mode = 'Y' THEN
1380 pa_debug.write_log (x_module =>'pa.plsql.PA_EVENT_PUB.UPDATE_EVENT.begin'
1381 ,x_msg =>'Defaulting currency from pa_events for the given event'
1382 ,x_log_level =>5);
1383 END IF;
1384
1385 --Select default currency from pa_events for the given event
1386 --And use those for validations if the currency fields are not
1387 --overwritten by the new changes.
1388 Select ev.Project_currency_code
1389 ,ev.ProjFunc_Inv_Rate_date
1390 ,ev.Project_Inv_Exchange_Rate
1391 ,ev.Project_Bill_Amount
1392 ,ev.Project_Rev_Rate_date
1393 ,ev.Project_Rev_Exchange_Rate
1394 ,ev.Project_Revenue_Amount
1395 ,ev.Project_Rate_type
1396 ,ev.Project_Rate_date
1397 ,ev.Project_Exchange_rate
1398 ,ev.ProjFunc_Currency_Code
1399 ,ev.ProjFunc_Inv_Rate_date
1400 ,ev.ProjFunc_Inv_Exchange_Rate
1401 ,ev.ProjFunc_Bill_Amount
1402 ,ev.ProjFunc_Rev_Rate_date
1403 ,ev.Projfunc_Rev_Exchange_Rate
1404 ,ev.ProjFunc_Revenue_Amount
1405 ,ev.ProjFunc_Rate_type
1406 ,ev.ProjFunc_Rate_date
1407 ,ev.ProjFunc_Exchange_rate
1408 ,ev.Invproc_Currency_Code
1409 ,ev.Invproc_Rate_Type
1410 ,ev.Invproc_Rate_date
1411 ,ev.Invproc_Exchange_Rate
1412 ,ev.Revproc_Currency_Code
1413 ,ev.Revproc_Rate_Type
1414 ,ev.Revproc_Rate_date
1415 ,ev.revproc_exchange_rate
1416 ,ev.Inv_Gen_Rejection_Code
1417 ,ev.Funding_Rate_type
1418 ,ev.Funding_Rate_date
1419 ,ev.Funding_Exchange_rate
1420 ,ev.Bill_trans_currency_code
1421 Into l_project_currency_code
1422 ,l_ProjFunc_Inv_Rate_date
1423 ,l_Project_Inv_Exchange_Rate
1424 ,l_Project_Bill_Amount
1425 ,l_Project_Rev_Rate_date
1426 ,l_Project_Rev_Exchange_Rate
1427 ,l_Project_Revenue_Amount
1428 ,l_project_bil_rate_type
1429 ,l_project_bil_rate_date
1430 ,l_project_bil_exchange_rate
1431 ,l_ProjFunc_Currency_Code
1432 ,l_ProjFunc_Inv_Rate_date
1433 ,l_ProjFunc_Inv_Exchange_Rate
1434 ,l_ProjFunc_Bill_Amount
1435 ,l_ProjFunc_Rev_Rate_date
1436 ,l_Projfunc_Rev_Exchange_Rate
1437 ,l_ProjFunc_Revenue_Amount
1438 ,l_projfunc_bil_rate_type
1439 ,l_projfunc_bil_rate_date
1440 ,l_projfunc_bil_exchange_rate
1441 ,l_Invproc_Currency_Code
1442 ,l_Invproc_Rate_Type
1443 ,l_Invproc_Rate_date
1444 ,l_Invproc_Exchange_Rate
1445 ,l_Revproc_Currency_Code
1446 ,l_Revproc_Rate_Type
1447 ,l_Revproc_Rate_date
1448 ,l_revproc_exchange_rate
1449 ,l_Inv_Gen_Rejection_Code
1450 ,l_funding_rate_type
1451 ,l_funding_rate_date
1452 ,l_funding_exchange_rate
1453 ,l_bill_trans_curr_code
1454 From pa_events ev
1455 Where event_id=p_event_in_rec.p_event_id;
1456
1457 Select multi_currency_billing_flag
1458 ,funding_rate_date_code
1459 ,project_bil_rate_date_code
1460 ,projfunc_bil_rate_date_code
1461 Into l_multi_currency_billing_flag
1462 ,l_funding_rate_date_code
1463 ,l_project_bil_rate_date_code
1464 ,l_projfunc_bil_rate_date_code
1465 From pa_projects_all
1466 Where project_id=P_project_id;
1467
1468
1469 --Log Message
1470 IF l_debug_mode = 'Y' THEN
1471 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.UPDATE_EVENT.begin'
1472 ,x_msg => 'Calling check_update_event_ok'
1473 ,x_log_level => 5);
1474 END IF;
1475
1476 -- Calls check_update_event_ok function
1477 If PA_EVENT_PVT.check_update_event_ok
1481 ,P_proj_func_currency_code =>l_projfunc_currency_code
1478 (P_pm_product_code =>p_pm_product_code
1479 ,P_event_in_rec =>p_event_in_rec
1480 ,P_project_currency_code =>l_project_currency_code
1482 ,P_project_bil_rate_date_code =>l_project_bil_rate_date_code
1483 ,P_project_rate_type =>l_project_bil_rate_type
1484 ,p_project_bil_rate_date =>l_project_bil_rate_date
1485 ,p_projfunc_bil_rate_date_code =>l_projfunc_bil_rate_date_code
1486 ,P_projfunc_rate_type =>l_projfunc_bil_rate_type
1487 ,p_projfunc_bil_rate_date =>l_projfunc_bil_rate_date
1488 ,P_funding_rate_type =>l_funding_rate_type
1489 ,P_multi_currency_billing_flag =>l_multi_currency_billing_flag
1490 ,P_event_type_classification =>l_event_type_classification
1491 ,P_event_processed =>l_event_processed /* Added for bug Bug 7110782 */
1492 ,p_project_id =>p_project_id
1493 ,p_projfunc_bil_exchange_rate =>l_projfunc_bil_exchange_rate -- Added for bug 3013137
1494 ,p_funding_bill_rate_date_code =>l_funding_rate_date_code --Added for bug 3053190
1495 ,x_task_id =>p_task_id
1496 ,x_organization_id =>p_organization_id
1497 ,x_inv_org_id =>p_inv_org_id
1498 ,x_agreement_id =>P_agreement_id -- Federal Uptake
1499 ) = 'N'
1500 Then
1501 p_return_status := FND_API.G_RET_STS_ERROR;
1502 RAISE FND_API.G_EXC_ERROR;
1503
1504 End If;
1505
1506 --Log Message
1507 IF l_debug_mode = 'Y' THEN
1508 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.UPDATE_EVENT.begin'
1509 ,x_msg => 'Beginning Validate Flexfields'
1510 ,x_log_level => 5);
1511 END IF;
1512
1513 --Validating Flexfields
1514 IF (p_event_in_rec.p_desc_flex_name IS NOT NULL)
1515 AND (p_event_in_rec.p_desc_flex_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1516 THEN
1517 PA_EVENT_PVT.VALIDATE_FLEXFIELD
1518 ( p_desc_flex_name => p_event_in_rec.p_desc_flex_name
1519 ,p_attribute_category => p_event_in_rec.p_attribute_category
1520 ,p_attribute1 => p_event_in_rec.p_attribute1
1521 ,p_attribute2 => p_event_in_rec.p_attribute2
1522 ,p_attribute3 => p_event_in_rec.p_attribute3
1523 ,p_attribute4 => p_event_in_rec.p_attribute4
1524 ,p_attribute5 => p_event_in_rec.p_attribute5
1525 ,p_attribute6 => p_event_in_rec.p_attribute6
1526 ,p_attribute7 => p_event_in_rec.p_attribute7
1527 ,p_attribute8 => p_event_in_rec.p_attribute8
1528 ,p_attribute9 => p_event_in_rec.p_attribute9
1529 ,p_attribute10 => p_event_in_rec.p_attribute10
1530 ,p_return_msg => p_return_msg
1531 ,p_valid_status => p_validate_status);
1532 IF p_validate_status = 'N'
1533 THEN
1534 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1535 THEN
1536 pa_interface_utils_pub.map_new_amg_msg
1537 ( p_old_message_code => 'PA_INVALID_FF_VALUES'
1538 ,p_msg_attribute => 'CHANGE'
1539 ,p_resize_flag => 'N'
1540 ,p_msg_context => 'EVENT'
1541 ,p_attribute1 => p_event_in_rec.p_pm_event_reference
1542 ,p_attribute2 => ''
1543 ,p_attribute3 => ''
1544 ,p_attribute4 => ''
1545 ,p_attribute5 => '');
1546 END IF;
1547 p_return_status := FND_API.G_RET_STS_ERROR;
1548 RAISE FND_API.G_EXC_ERROR;
1549 END IF;
1550 --Defaulting attribute categories.
1551 Select decode(p_event_in_rec.P_attribute_category
1552 ,NULL,attribute_category
1553 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1554 ,p_event_in_rec.P_attribute_category)
1555 ,decode(p_event_in_rec.P_attribute1
1556 ,NULL,attribute1
1557 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1558 ,p_event_in_rec.P_attribute1)
1559 ,decode(p_event_in_rec.P_attribute2
1560 ,NULL,attribute2
1561 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1562 ,p_event_in_rec.P_attribute2)
1563 ,decode(p_event_in_rec.P_attribute3
1564 ,NULL,attribute3
1565 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1566 ,p_event_in_rec.P_attribute3)
1567 ,decode(p_event_in_rec.P_attribute4
1568 ,NULL,attribute4
1569 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1570 ,p_event_in_rec.P_attribute4 )
1571 ,decode(p_event_in_rec.P_attribute5
1572 ,NULL,attribute5
1573 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1574 ,p_event_in_rec.P_attribute5 )
1575 ,decode(p_event_in_rec.P_attribute6
1576 ,NULL,attribute6
1577 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1578 ,p_event_in_rec.P_attribute6 )
1582 ,p_event_in_rec.P_attribute7 )
1579 ,decode(p_event_in_rec.P_attribute7
1580 ,NULL,attribute7
1581 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1583 ,decode(p_event_in_rec.P_attribute8
1584 ,NULL,attribute8
1585 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1586 ,p_event_in_rec.P_attribute8 )
1587 ,decode(p_event_in_rec.P_attribute9
1588 ,NULL,attribute9
1589 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1590 ,p_event_in_rec.P_attribute9 )
1591 ,decode(p_event_in_rec.P_attribute10
1592 ,NULL,attribute10
1593 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1594 ,p_event_in_rec.P_attribute10 )
1595 Into p_event_in_rec.P_attribute_category
1596 ,p_event_in_rec.P_attribute1
1597 ,p_event_in_rec.P_attribute2
1598 ,p_event_in_rec.P_attribute3
1599 ,p_event_in_rec.P_attribute4
1600 ,p_event_in_rec.P_attribute5
1601 ,p_event_in_rec.P_attribute6
1602 ,p_event_in_rec.P_attribute7
1603 ,p_event_in_rec.P_attribute8
1604 ,p_event_in_rec.P_attribute9
1605 ,p_event_in_rec.P_attribute10
1606 From pa_events
1607 Where event_id=p_event_in_rec.P_event_id;
1608
1609 Else
1610 p_event_in_rec.P_desc_flex_name :=NULL;
1611 p_event_in_rec.P_attribute_category :=NULL;
1612 p_event_in_rec.P_attribute1 :=NULL;
1613 p_event_in_rec.P_attribute2 :=NULL;
1614 p_event_in_rec.P_attribute3 :=NULL;
1615 p_event_in_rec.P_attribute4 :=NULL;
1616 p_event_in_rec.P_attribute5 :=NULL;
1617 p_event_in_rec.P_attribute6 :=NULL;
1618 p_event_in_rec.P_attribute7 :=NULL;
1619 p_event_in_rec.P_attribute8 :=NULL;
1620 p_event_in_rec.P_attribute9 :=NULL;
1621 p_event_in_rec.P_attribute10 :=NULL;
1622 END IF;
1623
1624 --Log Message
1625 IF l_debug_mode = 'Y' THEN
1626 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.UPDATE_EVENT.begin'
1627 ,x_msg => 'Beginning defaulting mcb parameters'
1628 ,x_log_level => 5);
1629 END IF;
1630
1631
1632 --Before defaulting the mcb related paramaters validating
1633 --if the User has overwritten any of the fields.
1634 If (l_multi_currency_billing_flag = 'Y')
1635 Then
1636 --validating and defaulting bill transaction currency code
1637 If (p_event_in_rec.P_bill_trans_currency_code Is NULL
1638 OR p_event_in_rec.P_bill_trans_currency_code=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1639 Then
1640 p_event_in_rec.P_bill_trans_currency_code:=l_bill_trans_curr_code; /* 3013117 */
1641 End If;
1642
1643 --funding rate,date and type validations
1644 If(p_event_in_rec.P_funding_rate_type Is NULL
1645 OR p_event_in_rec.P_funding_rate_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1646 Then
1647 p_event_in_rec.P_funding_rate_type:=l_funding_rate_type;
1648 End If;
1649
1650 If (l_funding_rate_date_code = 'FIXED_DATE')
1651 Then
1652 If(p_event_in_rec.P_funding_rate_date Is NULL
1653 OR p_event_in_rec.P_funding_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1654 Then
1655 p_event_in_rec.P_funding_rate_date:=l_funding_rate_date;
1656 End If;
1657 Else
1658 --Commented for bug3013236
1659 --p_event_in_rec.P_funding_rate_date:=l_funding_rate_date;
1660 --Added for bug3013236
1661 If(p_event_in_rec.P_funding_rate_date Is NULL
1662 OR p_event_in_rec.P_funding_rate_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1663 Then
1664 p_event_in_rec.P_funding_rate_date:=l_funding_rate_date;
1665 End If;
1666 --till here for Bug3013236
1667 End If;
1668
1669 --If fund rate type is User then only take the exchange rate from User
1670 --else default it from the project level.
1671 /* The following part of the code is commented out and rewritten at the bottom. for bug 3045302
1672
1673 If (p_event_in_rec.P_funding_rate_type <>l_funding_rate_type)
1674 Then
1675 If ( p_event_in_rec.P_funding_rate_type <> 'User' )
1676 Then
1677 p_event_in_rec.P_funding_exchange_rate :=NULL;
1681 Then
1678 Else
1679 If(p_event_in_rec.P_funding_exchange_rate Is NULL
1680 OR p_event_in_rec.P_funding_exchange_rate =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1682 p_event_in_rec.P_funding_exchange_rate:=l_funding_exchange_rate;
1683 End If;
1684 p_event_in_rec.P_funding_rate_date := null; --Added for Bug3010927
1685 End If;
1686 Else
1687 If(p_event_in_rec.P_funding_rate_type = 'User'
1688 AND (p_event_in_rec.P_funding_exchange_rate Is NULL
1689 OR p_event_in_rec.P_funding_exchange_rate =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
1690 Then
1691 p_event_in_rec.P_funding_exchange_rate :=l_funding_exchange_rate;
1692 --Added for Bug3013256
1693 Else
1694 IF ( p_event_in_rec.P_funding_rate_type <> 'User' )
1695 Then
1696 p_event_in_rec.P_funding_exchange_rate := null;
1697 End If;
1698 --till here for Bug3013256
1699 End If;
1700 p_event_in_rec.p_funding_rate_date := null; --Added for Bug3010927
1701 End If;
1702 End of commenting for Bug 3045302 */
1703 /*This is the code added for bug 3045302.*/
1704 If ( p_event_in_rec.P_funding_rate_type <> 'User' )
1705 Then
1706 p_event_in_rec.P_funding_exchange_rate :=NULL;
1707 Else
1708 If (p_event_in_rec.P_funding_exchange_rate Is NULL
1709 OR p_event_in_rec.P_funding_exchange_rate =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1710 Then
1711 p_event_in_rec.P_funding_exchange_rate:=l_funding_exchange_rate;
1712 End If;
1713 p_event_in_rec.P_funding_rate_date := null;
1714 End If;
1715 /*End of code added for bug 3045302 */
1716 /*The code for validation of project currency attributes has been moved
1717 below the code for validation of project functional attributes
1718 for bug 3045302 */
1719
1720 --project functional rate,date,type validaions
1721 If (p_event_in_rec.P_bill_trans_currency_code = l_projfunc_currency_code )
1722 Then
1723 p_event_in_rec.P_projfunc_rate_type :=NULL;
1724 p_event_in_rec.P_projfunc_rate_date :=NULL;
1725 p_event_in_rec.P_projfunc_exchange_rate :=NULL;
1726 Else ---validtions if project functional currency and bill trans currency are different.
1727 If(p_event_in_rec.P_projfunc_rate_type Is NULL
1728 OR p_event_in_rec.P_projfunc_rate_type=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1729 Then
1730 p_event_in_rec.P_projfunc_rate_type:=l_projfunc_bil_rate_type;
1731 End If;
1732
1733 If (l_projfunc_bil_rate_date_code= 'FIXED_DATE')
1734 Then
1735 If(p_event_in_rec.P_projfunc_rate_date Is NULL
1736 OR p_event_in_rec.P_projfunc_rate_date=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1737 Then
1738 p_event_in_rec.P_projfunc_rate_date:=l_projfunc_bil_rate_date;
1739 End If;
1740 Else
1741 --Commented for Bug3013236
1742 -- p_event_in_rec.P_projfunc_rate_date:=l_projfunc_bil_rate_date;
1743 --Added for Bug3013236
1744 If(p_event_in_rec.P_projfunc_rate_date Is NULL
1745 OR p_event_in_rec.P_projfunc_rate_date=PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1746 Then
1747 p_event_in_rec.P_projfunc_rate_date:=l_projfunc_bil_rate_date;
1748 End If;
1749 --till here for Bug3013236
1750 End If;
1751
1752 If(p_event_in_rec.P_projfunc_rate_type <> l_projfunc_bil_rate_type)
1753 Then
1754 If ( p_event_in_rec.P_projfunc_rate_type <> 'User' )
1755 Then
1756 p_event_in_rec.P_projfunc_exchange_rate:=NULL;
1757 Else
1758 If (p_event_in_rec.P_projfunc_exchange_rate Is NULL
1759 OR p_event_in_rec.P_projfunc_exchange_rate=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1760 Then
1761 p_event_in_rec.P_projfunc_exchange_rate:=l_projfunc_bil_exchange_rate;
1762 End If;
1763 p_event_in_rec.P_projfunc_rate_date := null; --Added for Bug3010927
1764 End If;
1765 Else
1766 If(p_event_in_rec.P_projfunc_rate_type = 'User')
1767 Then
1768 If (p_event_in_rec.P_projfunc_exchange_rate Is NULL
1772 End If;
1769 OR p_event_in_rec.P_projfunc_exchange_rate=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1770 Then
1771 p_event_in_rec.P_projfunc_exchange_rate:=l_projfunc_bil_exchange_rate;
1773 p_event_in_rec.P_projfunc_rate_date := null; --Added for Bug3010927
1774 Else
1775 --Commented for Bug3013256
1776 --p_event_in_rec.P_projfunc_exchange_rate:=l_projfunc_bil_exchange_rate;
1777 p_event_in_rec.P_projfunc_exchange_rate:=null; --Added for Bug3013256
1778 End If;
1779 End If;
1780 End If;
1781 --project rate,date and type validations
1782 --For bug 3045302.Changed the logic to default the project currency attributes
1783 --from the project functional currency if project currency is same as project functional currency.
1784 If (p_event_in_rec.P_bill_trans_currency_code = l_project_currency_code)
1785 Then
1786 p_event_in_rec.P_project_rate_type :=NULL;
1787 p_event_in_rec.P_project_rate_date :=NULL;
1788 p_event_in_rec.P_project_exchange_rate :=NULL;
1789
1790 Elsif (l_project_currency_code = l_projfunc_currency_code )
1791 Then
1792 p_event_in_rec.P_project_rate_type := p_event_in_rec.P_projfunc_rate_type;
1793 p_event_in_rec.P_project_rate_date := p_event_in_rec.P_projfunc_rate_date;
1794 p_event_in_rec.P_project_exchange_rate := p_event_in_rec.P_projfunc_exchange_rate;
1795
1796 Else
1797 ---validations if project currency and bill trans currency are different.
1798 If(p_event_in_rec.P_project_rate_type Is NULL
1799 OR p_event_in_rec.P_project_rate_type =PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1800 Then
1801 p_event_in_rec.P_project_rate_type:=l_project_bil_rate_type;
1802 End If;
1803
1804 If (l_project_bil_rate_date_code = 'FIXED_DATE')
1805 Then
1806 If(p_event_in_rec.P_project_rate_date Is NULL
1807 OR p_event_in_rec.P_project_rate_date =PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1808 Then
1809 p_event_in_rec.P_project_rate_date:=l_project_bil_rate_date;
1810 End If;
1811 Else
1812 --Commented for Bug3013236
1813 --p_event_in_rec.P_project_rate_date:=l_project_bil_rate_date;
1814 --Added for Bug3013236
1815 If(p_event_in_rec.P_project_rate_date Is NULL
1816 OR p_event_in_rec.P_project_rate_date =PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1817 Then
1818 p_event_in_rec.P_project_rate_date:=l_project_bil_rate_date;
1819 End If;
1820 --till here for Bug3013236
1821 End If;
1822
1823 If (p_event_in_rec.P_project_rate_type<>l_project_bil_rate_type)
1824 Then
1825 If ( p_event_in_rec.P_project_rate_type <> 'User' )
1826 Then
1827 p_event_in_rec.P_project_exchange_rate:=NULL;
1828 Else
1829 If(p_event_in_rec.P_project_exchange_rate Is NULL
1830 OR p_event_in_rec.P_project_exchange_rate=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1831 Then
1832 p_event_in_rec.P_project_exchange_rate:=l_project_bil_exchange_rate;
1833 End If;
1834 p_event_in_rec.P_project_rate_date := null; --Added for Bug3010927
1835 End If;
1836 Else
1837 If(p_event_in_rec.P_project_rate_type= 'User')
1838 Then
1839 If(p_event_in_rec.P_project_exchange_rate Is NULL
1840 OR p_event_in_rec.P_project_exchange_rate=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1841 Then
1842 p_event_in_rec.P_project_exchange_rate:=l_project_bil_exchange_rate;
1843 End If;
1844 p_event_in_rec.P_project_rate_date := null; --Added for Bug3010927
1845 Else
1846 --Commented for Bug3013256
1847 --p_event_in_rec.P_project_exchange_rate:=l_project_bil_exchange_rate;
1848 p_event_in_rec.P_project_exchange_rate:=null; --Added for Bug3013256
1849 End If;
1850 End If;
1851 End If;
1852
1853 Else
1854 --Defaulting the mcb columns when mcb is not enabled
1855 p_event_in_rec.P_project_rate_type:=l_project_bil_rate_type;
1856 p_event_in_rec.P_project_rate_date:=l_project_bil_rate_date;
1860 p_event_in_rec.P_projfunc_rate_date:=l_projfunc_bil_rate_date;
1857 p_event_in_rec.P_project_exchange_rate:=l_project_bil_exchange_rate;
1858
1859 p_event_in_rec.P_projfunc_rate_type:=l_projfunc_bil_rate_type;
1861 p_event_in_rec.P_projfunc_exchange_rate:=l_projfunc_bil_exchange_rate;
1862
1863 p_event_in_rec.P_funding_rate_type:=l_funding_rate_type;
1864 p_event_in_rec.P_funding_rate_date:=l_funding_rate_date;
1865 p_event_in_rec.P_funding_exchange_rate:=l_funding_exchange_rate;
1866
1867 p_event_in_rec.P_bill_trans_currency_code:=l_projfunc_currency_code;
1868 End If;
1869
1870
1871 --Log Message
1872 IF l_debug_mode = 'Y' THEN
1873 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.UPDATE_EVENT.begin'
1874 ,x_msg => 'Beginning defaulting parameters before updating table'
1875 ,x_log_level => 5);
1876 END IF;
1877
1878
1879 -- Validating the event num.
1880 --If task number has been changed then generate new event_num
1881 --before inserting the record into pa_events.
1882 If (p_event_in_rec.P_event_number Is NULL
1883 OR p_event_in_rec.P_event_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1884 Then
1885 SELECT task_id
1886 INTO l_task_id
1887 FROM pa_events e
1888 Where event_id=p_event_in_rec.P_event_id;
1889
1890 --Validating if the existing task_id matches with the updated task_id
1891 --In case of any mismatch new event_number is generated before updating the record.
1892 If p_event_in_rec.p_task_number is NOT NULL
1893 Then
1894 If (nvl(P_task_id,-1) <> nvl(l_task_id,-1))
1895 Then
1896 --generating event number for project level events
1897 If (P_task_id Is NULL )
1898 Then
1899 SELECT NVL(max(event_num),0)+1
1900 INTO P_event_in_rec.p_event_number
1901 FROM pa_events e
1902 WHERE e.project_id = P_project_id
1903 AND e.task_id IS NULL;
1904 Else
1905 --generating event number for task level events
1906 SELECT NVL(max(event_num),0)+1
1907 INTO P_event_in_rec.p_event_number
1908 FROM pa_events e
1909 WHERE e.project_id =P_project_id
1910 AND e.task_id = P_task_id;
1911 End If;
1912 --If task number is the same defaulting the event number.
1913 Else
1914 SELECT event_num
1915 INTO P_event_in_rec.p_event_number
1916 FROM pa_events e
1917 Where event_id=p_event_in_rec.P_event_id;
1918 End If;
1919 Else
1920 SELECT event_num
1921 INTO P_event_in_rec.p_event_number
1922 FROM pa_events e
1923 Where event_id=p_event_in_rec.P_event_id;
1924 End If; /* If p_event_in_rec.p_task_number is NOT NULL */
1925 End If; /* If event_num is null , '^' */
1926
1927 --Populating the rowid
1928 Select Rowid
1929 Into rowid
1930 From pa_events
1931 Where event_id=p_event_in_rec.p_event_id;
1932
1933 --Defaulting bill_hold_flag,adjusting_revenue_flag.
1934 Select decode(p_event_in_rec.P_bill_hold_flag
1935 ,NULL,BILL_HOLD_FLAG
1936 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1937 ,p_event_in_rec.P_bill_hold_flag)
1938 ,decode(p_event_in_rec.P_adjusting_revenue_flag
1939 ,NULL,ADJUSTING_REVENUE_FLAG
1940 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1941 ,p_event_in_rec.P_adjusting_revenue_flag)
1942 --Defaulting inventory org id and item ids
1943 ,decode(p_event_in_rec.P_inventory_org_name
1944 ,NULL,inventory_org_id
1945 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1946 ,p_inv_org_id)
1947 ,decode(p_event_in_rec.P_inventory_item_id
1948 ,NULL,inventory_item_id
1949 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,NULL
1950 ,p_event_in_rec.P_inventory_item_id)
1951 --Defaulting organization id.
1952 ,decode(p_event_in_rec.P_organization_name
1953 ,NULL,organization_id
1954 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,organization_id
1955 ,p_organization_id)
1956 --Defaulting OKE related fields.
1957 ,decode(p_event_in_rec.P_quantity_billed
1958 ,NULL,quantity_billed
1959 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,NULL
1960 ,p_event_in_rec.P_quantity_billed)
1961 ,decode(p_event_in_rec.P_uom_code
1962 ,NULL,uom_code
1963 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1964 ,p_event_in_rec.P_uom_code)
1965 ,decode(p_event_in_rec.P_unit_price
1969 --Defaulting references.
1966 ,NULL,unit_price
1967 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1968 ,p_event_in_rec.P_unit_price)
1970 ,decode(p_event_in_rec.P_reference1
1971 ,NULL,reference1
1972 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1973 ,p_event_in_rec.P_reference1)
1974 ,decode(p_event_in_rec.P_reference2
1975 ,NULL,reference2
1976 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1977 ,p_event_in_rec.P_reference2)
1978 ,decode(p_event_in_rec.P_reference3
1979 ,NULL,reference3
1980 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1981 ,p_event_in_rec.P_reference3)
1982 ,decode(p_event_in_rec.P_reference4
1983 ,NULL,reference4
1984 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1985 ,p_event_in_rec.P_reference4 )
1986 ,decode(p_event_in_rec.P_reference5
1987 ,NULL,reference5
1988 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1989 ,p_event_in_rec.P_reference5 )
1990 ,decode(p_event_in_rec.P_reference6
1991 ,NULL,reference6
1992 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1993 ,p_event_in_rec.P_reference6 )
1994 ,decode(p_event_in_rec.P_reference7
1995 ,NULL,reference7
1996 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
1997 ,p_event_in_rec.P_reference7 )
1998 ,decode(p_event_in_rec.P_reference8
1999 ,NULL,reference8
2000 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
2001 ,p_event_in_rec.P_reference8 )
2002 ,decode(p_event_in_rec.P_reference9
2003 ,NULL,reference9
2004 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
2005 ,p_event_in_rec.P_reference9 )
2006 ,decode(p_event_in_rec.P_reference10
2007 ,NULL,reference10
2008 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
2009 ,p_event_in_rec.P_reference10 )
2010 --defaulting completion date
2011 ,decode(p_event_in_rec.P_completion_date
2012 ,NULL,completion_date
2013 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,SYSDATE
2014 ,p_event_in_rec.P_completion_date)
2015 --Defaulting event type.
2016 ,decode(p_event_in_rec.P_event_type
2017 ,NULL,event_type
2018 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,event_type
2019 ,p_event_in_rec.P_event_type)
2020 --Defaulting bill trans revenue and bill trans bill amounts
2021 ,decode(p_event_in_rec.P_bill_trans_rev_amount
2022 ,NULL,bill_trans_rev_amount
2023 ,p_event_in_rec.P_bill_trans_rev_amount)
2024 ,decode(p_event_in_rec.P_bill_trans_bill_amount
2025 ,NULL,bill_trans_bill_amount
2026 ,p_event_in_rec.P_bill_trans_bill_amount)
2027 --Defaulting task_id.
2028 ,decode(p_event_in_rec.p_task_number
2029 ,NULL,task_id
2030 ,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,NULL
2031 ,P_task_id)
2032 ,decode(p_event_in_rec.p_description, null, description,
2033 PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, description, p_event_in_rec.p_description)
2034 ,record_version_number
2035 ,decode(ADJUSTING_REVENUE_FLAG,'N', /* Added for bug 6863270 */
2036 decode(p_event_in_rec.p_bill_trans_bill_amount, PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, bill_trans_bill_amount,
2037 null, bill_trans_bill_amount, p_event_in_rec.p_bill_trans_bill_amount),0)
2038 ,decode(p_event_in_rec.p_bill_trans_rev_amount, PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, bill_trans_rev_amount,
2039 null, bill_trans_rev_amount, p_event_in_rec.p_bill_trans_rev_amount)
2040 Into p_event_in_rec.P_bill_hold_flag
2041 ,p_event_in_rec.P_adjusting_revenue_flag
2042 ,p_inv_org_id
2043 ,p_event_in_rec.P_inventory_item_id
2044 ,P_organization_id
2045 ,p_event_in_rec.P_quantity_billed
2046 ,p_event_in_rec.P_uom_code
2047 ,p_event_in_rec.P_unit_price
2048 ,p_event_in_rec.P_reference1
2049 ,p_event_in_rec.P_reference2
2050 ,p_event_in_rec.P_reference3
2051 ,p_event_in_rec.P_reference4
2052 ,p_event_in_rec.P_reference5
2053 ,p_event_in_rec.P_reference6
2054 ,p_event_in_rec.P_reference7
2055 ,p_event_in_rec.P_reference8
2059 ,p_event_in_rec.P_event_type
2056 ,p_event_in_rec.P_reference9
2057 ,p_event_in_rec.P_reference10
2058 ,p_event_in_rec.P_completion_date
2060 ,p_event_in_rec.P_bill_trans_rev_amount
2061 ,p_event_in_rec.P_bill_trans_bill_amount
2062 ,P_task_id
2063 ,p_event_in_rec.P_description
2064 ,l_record_version_number
2065 ,l_bill_trans_bill_amt /* Added for bug 4093948 */
2066 ,l_bill_trans_rev_amt /* Added for bug 4093948 */
2067
2068 From pa_events
2069 Where event_id=p_event_in_rec.P_event_id;
2070
2071 --Unless the user updates with a valid description the
2072 --description gets defaulted with the event type.
2073 If (p_event_in_rec.P_description Is NULL
2074 OR p_event_in_rec.P_description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
2075 Then
2076 p_event_in_rec.P_description := p_event_in_rec.P_event_type;
2077 End If;
2078
2079 --If event type is of revenue type(Write-Off), setting bill trans bill amt = 0.
2080 If l_event_type_classification In('WRITE OFF')
2081 Then
2082 p_event_in_rec.P_bill_trans_bill_amount:=0;
2083 End If;
2084
2085 --If event type is of invoice type, set bill trans rev amt = 0
2086 If l_event_type_classification In('DEFERRED REVENUE','INVOICE REDUCTION','SCHEDULED PAYMENTS')
2087 Then
2088 p_event_in_rec.P_bill_trans_rev_amount:=0;
2089 End If;
2090
2091 --If event type = 'Write-On' then bill amt = rev amt.
2092 If (l_event_type_classification = 'WRITE ON')
2093 Then
2094 p_event_in_rec.P_bill_trans_bill_amount := p_event_in_rec.P_bill_trans_rev_amount;
2095 End If;
2096
2097 --Log Message
2098 IF l_debug_mode = 'Y' THEN
2099 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.UPDATE_EVENT.begin'
2100 ,x_msg => 'Begin Updating event. '
2101 ,x_log_level => 5);
2102 END IF;
2103
2104 --Get the conversion_type of the funding_rate_type, projfunc_rate_type, project_rate_type - for Bug3013236
2105 IF p_event_in_rec.p_funding_rate_type is not null
2106 THEN
2107 SELECT conversion_type
2108 INTO l_funding_rate_type
2109 FROM pa_conversion_types_v
2110 WHERE user_conversion_type = p_event_in_rec.p_funding_rate_type
2111 or conversion_type = p_event_in_rec.p_funding_rate_type;
2112 p_event_in_rec.p_funding_rate_type := l_funding_rate_type;
2113 END IF;
2114
2115 IF p_event_in_rec.p_projfunc_rate_type is not null
2116 THEN
2117 SELECT conversion_type
2118 INTO l_projfunc_bil_rate_type
2119 FROM pa_conversion_types_v
2120 WHERE user_conversion_type = p_event_in_rec.p_projfunc_rate_type
2121 or conversion_type = p_event_in_rec.p_projfunc_rate_type;
2122 p_event_in_rec.p_projfunc_rate_type := l_projfunc_bil_rate_type;
2123 END IF;
2124
2125 IF p_event_in_rec.p_project_rate_type is not null
2126 THEN
2127 SELECT conversion_type
2128 INTO l_project_bil_rate_type
2129 FROM pa_conversion_types_v
2130 WHERE user_conversion_type = p_event_in_rec.p_project_rate_type
2131 or conversion_type = p_event_in_rec.p_project_rate_type;
2132 p_event_in_rec.p_project_rate_type := l_project_bil_rate_type;
2133 END IF;
2134
2135 --till here for Bug3013236
2136
2137 -- Adding code to populate invoice and revenue attributes
2138
2139 IF l_invproc_currency_code = l_project_currency_code THEN
2140 l_invproc_currency_code := l_project_currency_code;
2141 l_invproc_rate_type := p_event_in_rec.P_project_rate_type;
2142 l_invproc_rate_date := p_event_in_rec.P_project_rate_date;
2143 l_invproc_exchange_rate := p_event_in_rec.P_project_exchange_rate;
2144 ELSIF l_invproc_currency_code = l_projfunc_currency_code THEN
2145 l_invproc_currency_code := l_projfunc_currency_code;
2146 l_invproc_rate_type := p_event_in_rec.P_projfunc_rate_type;
2147 l_invproc_rate_date := p_event_in_rec.P_projfunc_rate_date;
2148 l_invproc_exchange_rate := p_event_in_rec.P_projfunc_exchange_rate;
2149 ELSE
2150 l_invproc_currency_code := '';
2151 l_invproc_rate_type := p_event_in_rec.P_funding_rate_type;
2152 l_invproc_rate_date := p_event_in_rec.P_funding_rate_date;
2153 l_invproc_exchange_rate := p_event_in_rec.P_funding_exchange_rate;
2154
2155 END IF;
2156
2157 IF l_revproc_currency_code = l_projfunc_currency_code THEN
2158 l_revproc_currency_code := l_projfunc_currency_code;
2159 l_revproc_rate_type := p_event_in_rec.P_projfunc_rate_type;
2160 l_revproc_rate_date := p_event_in_rec.P_projfunc_rate_date;
2161 l_revproc_exchange_rate := p_event_in_rec.P_projfunc_exchange_rate;
2162 END IF;
2163
2164 -- Till here
2165
2166 IF nvl(l_record_version_number, 0) <> nvl(p_event_in_rec.P_record_version_number, 0)
2167 Then
2168 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2169 THEN
2173 ,p_resize_flag => 'N'
2170 pa_interface_utils_pub.map_new_amg_msg
2171 ( p_old_message_code => 'PA_RECORD_CHANGED'
2172 ,p_msg_attribute => 'CHANGE'
2174 ,p_msg_context => 'EVENT'
2175 ,p_attribute1 => P_event_in_rec.p_pm_event_reference
2176 ,p_attribute2 => ''
2177 ,p_attribute3 => ''
2178 ,p_attribute4 => ''
2179 ,p_attribute5 => '');
2180 END IF;
2181 p_return_status := FND_API.G_RET_STS_ERROR;
2182 RAISE FND_API.G_EXC_ERROR;
2183 End If;
2184
2185 /* Code added for Bug 7110782 - starts */
2186
2187 /* l_event_processed = 'I' means that event has been billed but not revenue distributed.
2188 In this case we are allowing to change only the bill_trans_rev_amount value */
2189 IF l_event_processed = 'I' THEN
2190
2191 SELECT ev.Task_Id
2192 ,ev.Event_Num
2193 ,ev.Event_Type
2194 ,ev.Bill_Amount
2195 ,ev.Revenue_Amount
2196 ,ev.Revenue_Distributed_Flag
2197 ,ev.Bill_Hold_Flag
2198 ,ev.Completion_date
2199 ,Rev_Dist_Rejection_Code
2200 ,ev.Attribute_Category
2201 ,ev.Attribute1
2202 ,ev.Attribute2
2203 ,ev.Attribute3
2204 ,ev.Attribute4
2205 ,ev.Attribute5
2206 ,ev.Attribute6
2207 ,ev.Attribute7
2208 ,ev.Attribute8
2209 ,ev.Attribute9
2210 ,ev.Attribute10
2211 ,ev.Project_Id
2212 ,ev.Organization_Id
2213 ,ev.Billing_Assignment_Id
2214 ,ev.Event_Num_Reversed
2215 ,ev.Calling_Place
2216 ,ev.Calling_Process
2217 ,ev.Bill_Trans_Currency_Code
2218 ,ev.Bill_Trans_Bill_Amount
2219 ,ev.Project_Currency_Code
2220 ,ev.Project_Rate_Type
2221 ,ev.Project_Rate_Date
2222 ,ev.Project_Exchange_Rate
2223 ,ev.Project_Inv_Rate_Date
2224 ,ev.Project_Inv_Exchange_Rate
2225 ,ev.Project_Bill_Amount
2226 ,ev.Project_Rev_Rate_date
2227 ,ev.Project_Rev_Exchange_Rate
2228 ,ev.Project_Revenue_Amount
2229 ,ev.ProjFunc_Currency_Code
2230 ,ev.ProjFunc_Rate_Type
2231 ,ev.ProjFunc_Rate_date
2232 ,ev.ProjFunc_Exchange_Rate
2233 ,ev.ProjFunc_Inv_Rate_date
2234 ,ev.ProjFunc_Inv_Exchange_Rate
2235 ,ev.ProjFunc_Bill_Amount
2236 ,ev.ProjFunc_Rev_Rate_date
2237 ,ev.Projfunc_Rev_Exchange_Rate
2238 ,ev.ProjFunc_Revenue_Amount
2239 ,ev.Funding_Rate_Type
2240 ,ev.Funding_Rate_date
2241 ,ev.Funding_Exchange_Rate
2242 ,ev.Invproc_Currency_Code
2243 ,ev.Invproc_Rate_Type
2244 ,ev.Invproc_Rate_date
2245 ,ev.Invproc_Exchange_Rate
2246 ,ev.Revproc_Currency_Code
2247 ,ev.Revproc_Rate_Type
2248 ,ev.Revproc_Rate_date
2249 ,ev.Revproc_Exchange_Rate
2250 ,ev.Inv_Gen_Rejection_Code
2251 ,ev.Adjusting_Revenue_Flag
2252 ,ev.inventory_org_id
2253 ,ev.inventory_item_id
2254 ,ev.quantity_billed
2255 ,ev.uom_code
2256 ,ev.unit_price
2257 ,ev.reference1
2258 ,ev.reference2
2259 ,ev.reference3
2260 ,ev.reference4
2261 ,ev.reference5
2262 ,ev.reference6
2263 ,ev.reference7
2264 ,ev.reference8
2265 ,ev.reference9
2266 ,ev.reference10
2267 ,ev.agreement_id
2268 INTO p_task_id
2269 ,p_event_in_rec.p_event_number
2270 ,p_event_in_rec.P_event_type
2271 ,l_bill_amount
2272 ,l_revenue_amount
2273 ,l_revenue_distributed_flag
2274 ,p_event_in_rec.P_bill_hold_flag
2275 ,p_event_in_rec.P_completion_date
2276 ,l_rev_dist_rejection_code
2277 ,p_event_in_rec.P_attribute_category
2278 ,p_event_in_rec.P_attribute1
2279 ,p_event_in_rec.P_attribute2
2280 ,p_event_in_rec.P_attribute3
2281 ,p_event_in_rec.P_attribute4
2282 ,p_event_in_rec.P_attribute5
2283 ,p_event_in_rec.P_attribute6
2284 ,p_event_in_rec.P_attribute7
2285 ,p_event_in_rec.P_attribute8
2286 ,p_event_in_rec.P_attribute9
2287 ,p_event_in_rec.P_attribute10
2288 ,P_project_id
2289 ,P_Organization_Id
2290 ,l_Billing_Assignment_Id
2291 ,l_Event_Num_Reversed
2292 ,l_Calling_Place
2293 ,l_Calling_Process
2294 ,p_event_in_rec.P_bill_trans_currency_code
2295 ,l_Bill_Trans_Bill_Amt
2296 ,l_project_currency_code
2297 ,p_event_in_rec.P_project_rate_type
2298 ,p_event_in_rec.P_project_rate_date
2299 ,p_event_in_rec.P_project_exchange_rate
2300 ,l_ProjFunc_Inv_Rate_date
2301 ,l_Project_Inv_Exchange_Rate
2302 ,l_Project_Bill_Amount
2303 ,l_Project_Rev_Rate_date
2304 ,l_Project_Rev_Exchange_Rate
2305 ,l_Project_Revenue_Amount
2306 ,l_ProjFunc_Currency_Code
2307 ,p_event_in_rec.P_projfunc_rate_type
2308 ,p_event_in_rec.P_projfunc_rate_date
2309 ,p_event_in_rec.P_projfunc_exchange_rate
2310 ,l_ProjFunc_Inv_Rate_date
2311 ,l_ProjFunc_Inv_Exchange_Rate
2312 ,l_ProjFunc_Bill_Amount
2313 ,l_ProjFunc_Rev_Rate_date
2314 ,l_Projfunc_Rev_Exchange_Rate
2315 ,l_ProjFunc_Revenue_Amount
2316 ,p_event_in_rec.P_funding_rate_type
2317 ,p_event_in_rec.P_funding_rate_date
2318 ,p_event_in_rec.P_funding_exchange_rate
2319 ,l_Invproc_Currency_Code
2320 ,l_Invproc_Rate_Type
2324 ,l_Revproc_Rate_Type
2321 ,l_Invproc_Rate_date
2322 ,l_Invproc_Exchange_Rate
2323 ,l_Revproc_Currency_Code
2325 ,l_Revproc_Rate_date
2326 ,l_revproc_exchange_rate
2327 ,l_Inv_Gen_Rejection_Code
2328 ,p_event_in_rec.P_adjusting_revenue_flag
2329 ,p_inv_org_id
2330 ,p_event_in_rec.P_inventory_item_id
2331 ,p_event_in_rec.P_quantity_billed
2332 ,p_event_in_rec.P_uom_code
2333 ,p_event_in_rec.P_unit_price
2334 ,p_event_in_rec.P_reference1
2335 ,p_event_in_rec.P_reference2
2336 ,p_event_in_rec.P_reference3
2337 ,p_event_in_rec.P_reference4
2338 ,p_event_in_rec.P_reference5
2339 ,p_event_in_rec.P_reference6
2340 ,p_event_in_rec.P_reference7
2341 ,p_event_in_rec.P_reference8
2342 ,p_event_in_rec.P_reference9
2343 ,p_event_in_rec.P_reference10
2344 ,P_agreement_id
2345 FROM pa_events ev
2346 WHERE event_id = p_event_in_rec.p_event_id;
2347
2348 PA_EVENTS_PKG.Update_Row
2349 (X_Rowid =>Rowid
2350 ,X_Event_Id =>p_event_in_rec.P_event_id
2351 ,X_Task_Id =>p_task_id
2352 ,X_Event_Num =>p_event_in_rec.p_event_number
2353 ,X_Last_Update_Date =>SYSDATE
2354 ,X_Last_Updated_By =>G_USER_ID
2355 ,X_Last_Update_Login =>G_LOGIN_ID
2356 ,X_Event_Type =>p_event_in_rec.P_event_type
2357 ,X_Description =>p_event_in_rec.P_description
2358 ,X_Bill_Amount =>0
2359 ,X_Revenue_Amount =>0
2360 ,X_Revenue_Distributed_Flag =>'N'
2361 ,X_Bill_Hold_Flag =>p_event_in_rec.P_bill_hold_flag
2362 ,X_Completion_date =>p_event_in_rec.P_completion_date
2363 ,X_Rev_Dist_Rejection_Code =>NULL
2364 ,X_Attribute_Category =>p_event_in_rec.P_attribute_category
2365 ,X_Attribute1 =>p_event_in_rec.P_attribute1
2366 ,X_Attribute2 =>p_event_in_rec.P_attribute2
2367 ,X_Attribute3 =>p_event_in_rec.P_attribute3
2368 ,X_Attribute4 =>p_event_in_rec.P_attribute4
2369 ,X_Attribute5 =>p_event_in_rec.P_attribute5
2370 ,X_Attribute6 =>p_event_in_rec.P_attribute6
2371 ,X_Attribute7 =>p_event_in_rec.P_attribute7
2372 ,X_Attribute8 =>p_event_in_rec.P_attribute8
2373 ,X_Attribute9 =>p_event_in_rec.P_attribute9
2374 ,X_Attribute10 =>p_event_in_rec.P_attribute10
2375 ,X_Project_Id =>P_project_id
2376 ,X_Organization_Id =>P_Organization_Id
2377 ,X_Billing_Assignment_Id =>NULL
2378 ,X_Event_Num_Reversed =>NULL
2379 ,X_Calling_Place =>NULL
2380 ,X_Calling_Process =>NULL
2381 ,X_Bill_Trans_Currency_Code =>p_event_in_rec.P_bill_trans_currency_code
2382 ,X_Bill_Trans_Bill_Amount =>l_Bill_Trans_Bill_Amt
2383 ,X_Bill_Trans_rev_Amount =>l_Bill_Trans_rev_Amt
2384 ,X_Project_Currency_Code =>l_project_currency_code
2385 ,X_Project_Rate_Type =>p_event_in_rec.P_project_rate_type
2386 ,X_Project_Rate_Date =>p_event_in_rec.P_project_rate_date
2387 ,X_Project_Exchange_Rate =>p_event_in_rec.P_project_exchange_rate
2388 ,X_Project_Inv_Rate_Date =>l_ProjFunc_Inv_Rate_date
2389 ,X_Project_Inv_Exchange_Rate =>l_Project_Inv_Exchange_Rate
2390 ,X_Project_Bill_Amount =>l_Project_Bill_Amount
2391 ,X_Project_Rev_Rate_date =>l_Project_Rev_Rate_date
2392 ,X_Project_Rev_Exchange_Rate =>l_Project_Rev_Exchange_Rate
2393 ,X_Project_Revenue_Amount =>l_Project_Revenue_Amount
2394 ,X_ProjFunc_Currency_Code =>l_ProjFunc_Currency_Code
2395 ,X_ProjFunc_Rate_Type =>p_event_in_rec.P_projfunc_rate_type
2396 ,X_ProjFunc_Rate_date =>p_event_in_rec.P_projfunc_rate_date
2397 ,X_ProjFunc_Exchange_Rate =>p_event_in_rec.P_projfunc_exchange_rate
2398 ,X_ProjFunc_Inv_Rate_date =>l_ProjFunc_Inv_Rate_date
2399 ,X_ProjFunc_Inv_Exchange_Rate =>l_ProjFunc_Inv_Exchange_Rate
2400 ,X_ProjFunc_Bill_Amount =>l_ProjFunc_Bill_Amount
2401 ,X_ProjFunc_Rev_Rate_date =>l_ProjFunc_Rev_Rate_date
2402 ,X_Projfunc_Rev_Exchange_Rate =>l_Projfunc_Rev_Exchange_Rate
2403 ,X_ProjFunc_Revenue_Amount =>l_ProjFunc_Revenue_Amount
2404 ,X_Funding_Rate_Type =>p_event_in_rec.P_funding_rate_type
2405 ,X_Funding_Rate_date =>p_event_in_rec.P_funding_rate_date
2406 ,X_Funding_Exchange_Rate =>p_event_in_rec.P_funding_exchange_rate
2407 ,X_Invproc_Currency_Code =>l_Invproc_Currency_Code
2408 ,X_Invproc_Rate_Type =>l_Invproc_Rate_Type
2409 ,X_Invproc_Rate_date =>l_Invproc_Rate_date
2413 ,X_Revproc_Rate_date =>l_Revproc_Rate_date
2410 ,X_Invproc_Exchange_Rate =>l_Invproc_Exchange_Rate
2411 ,X_Revproc_Currency_Code =>l_Revproc_Currency_Code
2412 ,X_Revproc_Rate_Type =>l_Revproc_Rate_Type
2414 ,X_Revproc_Exchange_Rate =>l_revproc_exchange_rate
2415 ,X_Inv_Gen_Rejection_Code =>l_Inv_Gen_Rejection_Code
2416 ,X_Adjusting_Revenue_Flag =>p_event_in_rec.P_adjusting_revenue_flag
2417 ,X_inventory_org_id =>p_inv_org_id
2418 ,X_inventory_item_id =>p_event_in_rec.P_inventory_item_id
2419 ,X_quantity_billed =>p_event_in_rec.P_quantity_billed
2420 ,X_uom_code =>p_event_in_rec.P_uom_code
2421 ,X_unit_price =>p_event_in_rec.P_unit_price
2422 ,X_reference1 =>p_event_in_rec.P_reference1
2423 ,X_reference2 =>p_event_in_rec.P_reference2
2424 ,X_reference3 =>p_event_in_rec.P_reference3
2425 ,X_reference4 =>p_event_in_rec.P_reference4
2426 ,X_reference5 =>p_event_in_rec.P_reference5
2427 ,X_reference6 =>p_event_in_rec.P_reference6
2428 ,X_reference7 =>p_event_in_rec.P_reference7
2429 ,X_reference8 =>p_event_in_rec.P_reference8
2430 ,X_reference9 =>p_event_in_rec.P_reference9
2431 ,X_reference10 =>p_event_in_rec.P_reference10
2432 ,X_agreement_id =>P_agreement_id );
2433
2434 /* l_event_processed = 'R' means that event has been revenue distributed but not billed.
2435 In this case we are allowing to change only the bill_trans_bill_amount and bill_hold_flga values */
2436 ELSIF l_event_processed = 'R' THEN
2437
2438 SELECT ev.Task_Id
2439 ,ev.Event_Num
2440 ,ev.Event_Type
2441 ,ev.Bill_Amount
2442 ,ev.Revenue_Amount
2443 ,ev.Revenue_Distributed_Flag
2444 ,ev.Completion_date
2445 ,Rev_Dist_Rejection_Code
2446 ,ev.Attribute_Category
2447 ,ev.Attribute1
2448 ,ev.Attribute2
2449 ,ev.Attribute3
2450 ,ev.Attribute4
2451 ,ev.Attribute5
2452 ,ev.Attribute6
2453 ,ev.Attribute7
2454 ,ev.Attribute8
2455 ,ev.Attribute9
2456 ,ev.Attribute10
2457 ,ev.Project_Id
2458 ,ev.Organization_Id
2459 ,ev.Billing_Assignment_Id
2460 ,ev.Event_Num_Reversed
2461 ,ev.Calling_Place
2462 ,ev.Calling_Process
2463 ,ev.Bill_Trans_Currency_Code
2464 ,ev.Bill_Trans_rev_Amount
2465 ,ev.Project_Currency_Code
2466 ,ev.Project_Rate_Type
2467 ,ev.Project_Rate_Date
2468 ,ev.Project_Exchange_Rate
2469 ,ev.Project_Inv_Rate_Date
2470 ,ev.Project_Inv_Exchange_Rate
2471 ,ev.Project_Bill_Amount
2472 ,ev.Project_Rev_Rate_date
2473 ,ev.Project_Rev_Exchange_Rate
2474 ,ev.Project_Revenue_Amount
2475 ,ev.ProjFunc_Currency_Code
2476 ,ev.ProjFunc_Rate_Type
2477 ,ev.ProjFunc_Rate_date
2478 ,ev.ProjFunc_Exchange_Rate
2479 ,ev.ProjFunc_Inv_Rate_date
2480 ,ev.ProjFunc_Inv_Exchange_Rate
2481 ,ev.ProjFunc_Bill_Amount
2482 ,ev.ProjFunc_Rev_Rate_date
2483 ,ev.Projfunc_Rev_Exchange_Rate
2484 ,ev.ProjFunc_Revenue_Amount
2485 ,ev.Funding_Rate_Type
2486 ,ev.Funding_Rate_date
2487 ,ev.Funding_Exchange_Rate
2488 ,ev.Invproc_Currency_Code
2489 ,ev.Invproc_Rate_Type
2490 ,ev.Invproc_Rate_date
2491 ,ev.Invproc_Exchange_Rate
2492 ,ev.Revproc_Currency_Code
2493 ,ev.Revproc_Rate_Type
2494 ,ev.Revproc_Rate_date
2495 ,ev.Revproc_Exchange_Rate
2496 ,ev.Inv_Gen_Rejection_Code
2497 ,ev.Adjusting_Revenue_Flag
2498 ,ev.inventory_org_id
2499 ,ev.inventory_item_id
2500 ,ev.quantity_billed
2501 ,ev.uom_code
2502 ,ev.unit_price
2503 ,ev.reference1
2504 ,ev.reference2
2505 ,ev.reference3
2506 ,ev.reference4
2507 ,ev.reference5
2508 ,ev.reference6
2509 ,ev.reference7
2510 ,ev.reference8
2511 ,ev.reference9
2512 ,ev.reference10
2513 ,ev.agreement_id
2514 INTO p_task_id
2515 ,p_event_in_rec.p_event_number
2516 ,p_event_in_rec.P_event_type
2517 ,l_bill_amount
2518 ,l_revenue_amount
2519 ,l_revenue_distributed_flag
2520 ,p_event_in_rec.P_completion_date
2521 ,l_rev_dist_rejection_code
2522 ,p_event_in_rec.P_attribute_category
2523 ,p_event_in_rec.P_attribute1
2524 ,p_event_in_rec.P_attribute2
2525 ,p_event_in_rec.P_attribute3
2526 ,p_event_in_rec.P_attribute4
2527 ,p_event_in_rec.P_attribute5
2528 ,p_event_in_rec.P_attribute6
2529 ,p_event_in_rec.P_attribute7
2530 ,p_event_in_rec.P_attribute8
2531 ,p_event_in_rec.P_attribute9
2532 ,p_event_in_rec.P_attribute10
2533 ,P_project_id
2534 ,P_Organization_Id
2535 ,l_Billing_Assignment_Id
2536 ,l_Event_Num_Reversed
2537 ,l_Calling_Place
2538 ,l_Calling_Process
2539 ,p_event_in_rec.P_bill_trans_currency_code
2540 ,l_Bill_Trans_rev_Amt
2541 ,l_project_currency_code
2542 ,p_event_in_rec.P_project_rate_type
2543 ,p_event_in_rec.P_project_rate_date
2544 ,p_event_in_rec.P_project_exchange_rate
2548 ,l_Project_Rev_Rate_date
2545 ,l_ProjFunc_Inv_Rate_date
2546 ,l_Project_Inv_Exchange_Rate
2547 ,l_Project_Bill_Amount
2549 ,l_Project_Rev_Exchange_Rate
2550 ,l_Project_Revenue_Amount
2551 ,l_ProjFunc_Currency_Code
2552 ,p_event_in_rec.P_projfunc_rate_type
2553 ,p_event_in_rec.P_projfunc_rate_date
2554 ,p_event_in_rec.P_projfunc_exchange_rate
2555 ,l_ProjFunc_Inv_Rate_date
2556 ,l_ProjFunc_Inv_Exchange_Rate
2557 ,l_ProjFunc_Bill_Amount
2558 ,l_ProjFunc_Rev_Rate_date
2559 ,l_Projfunc_Rev_Exchange_Rate
2560 ,l_ProjFunc_Revenue_Amount
2561 ,p_event_in_rec.P_funding_rate_type
2562 ,p_event_in_rec.P_funding_rate_date
2563 ,p_event_in_rec.P_funding_exchange_rate
2564 ,l_Invproc_Currency_Code
2565 ,l_Invproc_Rate_Type
2566 ,l_Invproc_Rate_date
2567 ,l_Invproc_Exchange_Rate
2568 ,l_Revproc_Currency_Code
2569 ,l_Revproc_Rate_Type
2570 ,l_Revproc_Rate_date
2571 ,l_revproc_exchange_rate
2572 ,l_Inv_Gen_Rejection_Code
2573 ,p_event_in_rec.P_adjusting_revenue_flag
2574 ,p_inv_org_id
2575 ,p_event_in_rec.P_inventory_item_id
2576 ,p_event_in_rec.P_quantity_billed
2577 ,p_event_in_rec.P_uom_code
2578 ,p_event_in_rec.P_unit_price
2579 ,p_event_in_rec.P_reference1
2580 ,p_event_in_rec.P_reference2
2581 ,p_event_in_rec.P_reference3
2582 ,p_event_in_rec.P_reference4
2583 ,p_event_in_rec.P_reference5
2584 ,p_event_in_rec.P_reference6
2585 ,p_event_in_rec.P_reference7
2586 ,p_event_in_rec.P_reference8
2587 ,p_event_in_rec.P_reference9
2588 ,p_event_in_rec.P_reference10
2589 ,P_agreement_id
2590 FROM pa_events ev
2591 WHERE event_id = p_event_in_rec.p_event_id;
2592
2593 PA_EVENTS_PKG.Update_Row
2594 (X_Rowid =>Rowid
2595 ,X_Event_Id =>p_event_in_rec.P_event_id
2596 ,X_Task_Id =>p_task_id
2597 ,X_Event_Num =>p_event_in_rec.p_event_number
2598 ,X_Last_Update_Date =>SYSDATE
2599 ,X_Last_Updated_By =>G_USER_ID
2600 ,X_Last_Update_Login =>G_LOGIN_ID
2601 ,X_Event_Type =>p_event_in_rec.P_event_type
2602 ,X_Description =>p_event_in_rec.P_description
2603 ,X_Bill_Amount =>0
2604 ,X_Revenue_Amount =>0
2605 ,X_Revenue_Distributed_Flag =>'N'
2606 ,X_Bill_Hold_Flag =>p_event_in_rec.P_bill_hold_flag
2607 ,X_Completion_date =>p_event_in_rec.P_completion_date
2608 ,X_Rev_Dist_Rejection_Code =>NULL
2609 ,X_Attribute_Category =>p_event_in_rec.P_attribute_category
2610 ,X_Attribute1 =>p_event_in_rec.P_attribute1
2611 ,X_Attribute2 =>p_event_in_rec.P_attribute2
2612 ,X_Attribute3 =>p_event_in_rec.P_attribute3
2613 ,X_Attribute4 =>p_event_in_rec.P_attribute4
2614 ,X_Attribute5 =>p_event_in_rec.P_attribute5
2615 ,X_Attribute6 =>p_event_in_rec.P_attribute6
2616 ,X_Attribute7 =>p_event_in_rec.P_attribute7
2617 ,X_Attribute8 =>p_event_in_rec.P_attribute8
2618 ,X_Attribute9 =>p_event_in_rec.P_attribute9
2619 ,X_Attribute10 =>p_event_in_rec.P_attribute10
2620 ,X_Project_Id =>P_project_id
2621 ,X_Organization_Id =>P_Organization_Id
2622 ,X_Billing_Assignment_Id =>NULL
2623 ,X_Event_Num_Reversed =>NULL
2624 ,X_Calling_Place =>NULL
2625 ,X_Calling_Process =>NULL
2626 ,X_Bill_Trans_Currency_Code =>p_event_in_rec.P_bill_trans_currency_code
2627 ,X_Bill_Trans_Bill_Amount =>l_Bill_Trans_Bill_Amt
2628 ,X_Bill_Trans_rev_Amount =>l_Bill_Trans_rev_Amt
2629 ,X_Project_Currency_Code =>l_project_currency_code
2630 ,X_Project_Rate_Type =>p_event_in_rec.P_project_rate_type
2631 ,X_Project_Rate_Date =>p_event_in_rec.P_project_rate_date
2632 ,X_Project_Exchange_Rate =>p_event_in_rec.P_project_exchange_rate
2633 ,X_Project_Inv_Rate_Date =>l_ProjFunc_Inv_Rate_date
2634 ,X_Project_Inv_Exchange_Rate =>l_Project_Inv_Exchange_Rate
2635 ,X_Project_Bill_Amount =>l_Project_Bill_Amount
2636 ,X_Project_Rev_Rate_date =>l_Project_Rev_Rate_date
2637 ,X_Project_Rev_Exchange_Rate =>l_Project_Rev_Exchange_Rate
2638 ,X_Project_Revenue_Amount =>l_Project_Revenue_Amount
2639 ,X_ProjFunc_Currency_Code =>l_ProjFunc_Currency_Code
2640 ,X_ProjFunc_Rate_Type =>p_event_in_rec.P_projfunc_rate_type
2641 ,X_ProjFunc_Rate_date =>p_event_in_rec.P_projfunc_rate_date
2642 ,X_ProjFunc_Exchange_Rate =>p_event_in_rec.P_projfunc_exchange_rate
2643 ,X_ProjFunc_Inv_Rate_date =>l_ProjFunc_Inv_Rate_date
2644 ,X_ProjFunc_Inv_Exchange_Rate =>l_ProjFunc_Inv_Exchange_Rate
2648 ,X_ProjFunc_Revenue_Amount =>l_ProjFunc_Revenue_Amount
2645 ,X_ProjFunc_Bill_Amount =>l_ProjFunc_Bill_Amount
2646 ,X_ProjFunc_Rev_Rate_date =>l_ProjFunc_Rev_Rate_date
2647 ,X_Projfunc_Rev_Exchange_Rate =>l_Projfunc_Rev_Exchange_Rate
2649 ,X_Funding_Rate_Type =>p_event_in_rec.P_funding_rate_type
2650 ,X_Funding_Rate_date =>p_event_in_rec.P_funding_rate_date
2651 ,X_Funding_Exchange_Rate =>p_event_in_rec.P_funding_exchange_rate
2652 ,X_Invproc_Currency_Code =>l_Invproc_Currency_Code
2653 ,X_Invproc_Rate_Type =>l_Invproc_Rate_Type
2654 ,X_Invproc_Rate_date =>l_Invproc_Rate_date
2655 ,X_Invproc_Exchange_Rate =>l_Invproc_Exchange_Rate
2656 ,X_Revproc_Currency_Code =>l_Revproc_Currency_Code
2657 ,X_Revproc_Rate_Type =>l_Revproc_Rate_Type
2658 ,X_Revproc_Rate_date =>l_Revproc_Rate_date
2659 ,X_Revproc_Exchange_Rate =>l_revproc_exchange_rate
2660 ,X_Inv_Gen_Rejection_Code =>l_Inv_Gen_Rejection_Code
2661 ,X_Adjusting_Revenue_Flag =>p_event_in_rec.P_adjusting_revenue_flag
2662 ,X_inventory_org_id =>p_inv_org_id
2663 ,X_inventory_item_id =>p_event_in_rec.P_inventory_item_id
2664 ,X_quantity_billed =>p_event_in_rec.P_quantity_billed
2665 ,X_uom_code =>p_event_in_rec.P_uom_code
2666 ,X_unit_price =>p_event_in_rec.P_unit_price
2667 ,X_reference1 =>p_event_in_rec.P_reference1
2668 ,X_reference2 =>p_event_in_rec.P_reference2
2669 ,X_reference3 =>p_event_in_rec.P_reference3
2670 ,X_reference4 =>p_event_in_rec.P_reference4
2671 ,X_reference5 =>p_event_in_rec.P_reference5
2672 ,X_reference6 =>p_event_in_rec.P_reference6
2673 ,X_reference7 =>p_event_in_rec.P_reference7
2674 ,X_reference8 =>p_event_in_rec.P_reference8
2675 ,X_reference9 =>p_event_in_rec.P_reference9
2676 ,X_reference10 =>p_event_in_rec.P_reference10
2677 ,X_agreement_id =>P_agreement_id );
2678
2679 /* Code added for Bug 7110782 - ends */
2680
2681 ELSE
2682 --Calls table handler for updating valid events.
2683 PA_EVENTS_PKG.Update_Row
2684 (X_Rowid =>Rowid
2685 ,X_Event_Id =>p_event_in_rec.P_event_id
2686 ,X_Task_Id =>p_task_id
2687 ,X_Event_Num =>p_event_in_rec.p_event_number
2688 ,X_Last_Update_Date =>SYSDATE
2689 ,X_Last_Updated_By =>G_USER_ID
2690 ,X_Last_Update_Login =>G_LOGIN_ID
2691 ,X_Event_Type =>p_event_in_rec.P_event_type
2692 ,X_Description =>p_event_in_rec.P_description
2693 ,X_Bill_Amount =>0
2694 ,X_Revenue_Amount =>0
2695 ,X_Revenue_Distributed_Flag =>'N'
2696 ,X_Bill_Hold_Flag =>p_event_in_rec.P_bill_hold_flag
2697 ,X_Completion_date =>p_event_in_rec.P_completion_date
2698 ,X_Rev_Dist_Rejection_Code =>NULL
2699 ,X_Attribute_Category =>p_event_in_rec.P_attribute_category
2700 ,X_Attribute1 =>p_event_in_rec.P_attribute1
2701 ,X_Attribute2 =>p_event_in_rec.P_attribute2
2702 ,X_Attribute3 =>p_event_in_rec.P_attribute3
2703 ,X_Attribute4 =>p_event_in_rec.P_attribute4
2704 ,X_Attribute5 =>p_event_in_rec.P_attribute5
2705 ,X_Attribute6 =>p_event_in_rec.P_attribute6
2706 ,X_Attribute7 =>p_event_in_rec.P_attribute7
2707 ,X_Attribute8 =>p_event_in_rec.P_attribute8
2708 ,X_Attribute9 =>p_event_in_rec.P_attribute9
2709 ,X_Attribute10 =>p_event_in_rec.P_attribute10
2710 ,X_Project_Id =>P_project_id
2711 ,X_Organization_Id =>P_Organization_Id
2712 ,X_Billing_Assignment_Id =>NULL
2713 ,X_Event_Num_Reversed =>NULL
2714 ,X_Calling_Place =>NULL
2715 ,X_Calling_Process =>NULL
2716 ,X_Bill_Trans_Currency_Code =>p_event_in_rec.P_bill_trans_currency_code
2717 ,X_Bill_Trans_Bill_Amount =>l_Bill_Trans_Bill_Amt
2718 ,X_Bill_Trans_rev_Amount =>l_Bill_Trans_rev_Amt
2719 ,X_Project_Currency_Code =>l_project_currency_code
2720 ,X_Project_Rate_Type =>p_event_in_rec.P_project_rate_type
2721 ,X_Project_Rate_Date =>p_event_in_rec.P_project_rate_date
2722 ,X_Project_Exchange_Rate =>p_event_in_rec.P_project_exchange_rate
2723 ,X_Project_Inv_Rate_Date =>l_ProjFunc_Inv_Rate_date
2724 ,X_Project_Inv_Exchange_Rate =>l_Project_Inv_Exchange_Rate
2725 ,X_Project_Bill_Amount =>l_Project_Bill_Amount
2729 ,X_ProjFunc_Currency_Code =>l_ProjFunc_Currency_Code
2726 ,X_Project_Rev_Rate_date =>l_Project_Rev_Rate_date
2727 ,X_Project_Rev_Exchange_Rate =>l_Project_Rev_Exchange_Rate
2728 ,X_Project_Revenue_Amount =>l_Project_Revenue_Amount
2730 ,X_ProjFunc_Rate_Type =>p_event_in_rec.P_projfunc_rate_type
2731 ,X_ProjFunc_Rate_date =>p_event_in_rec.P_projfunc_rate_date
2732 ,X_ProjFunc_Exchange_Rate =>p_event_in_rec.P_projfunc_exchange_rate
2733 ,X_ProjFunc_Inv_Rate_date =>l_ProjFunc_Inv_Rate_date
2734 ,X_ProjFunc_Inv_Exchange_Rate =>l_ProjFunc_Inv_Exchange_Rate
2735 ,X_ProjFunc_Bill_Amount =>l_ProjFunc_Bill_Amount
2736 ,X_ProjFunc_Rev_Rate_date =>l_ProjFunc_Rev_Rate_date
2737 ,X_Projfunc_Rev_Exchange_Rate =>l_Projfunc_Rev_Exchange_Rate
2738 ,X_ProjFunc_Revenue_Amount =>l_ProjFunc_Revenue_Amount
2739 ,X_Funding_Rate_Type =>p_event_in_rec.P_funding_rate_type
2740 ,X_Funding_Rate_date =>p_event_in_rec.P_funding_rate_date
2741 ,X_Funding_Exchange_Rate =>p_event_in_rec.P_funding_exchange_rate
2742 ,X_Invproc_Currency_Code =>l_Invproc_Currency_Code
2743 ,X_Invproc_Rate_Type =>l_Invproc_Rate_Type
2744 ,X_Invproc_Rate_date =>l_Invproc_Rate_date
2745 ,X_Invproc_Exchange_Rate =>l_Invproc_Exchange_Rate
2746 ,X_Revproc_Currency_Code =>l_Revproc_Currency_Code
2747 ,X_Revproc_Rate_Type =>l_Revproc_Rate_Type
2748 ,X_Revproc_Rate_date =>l_Revproc_Rate_date
2749 ,X_Revproc_Exchange_Rate =>l_revproc_exchange_rate
2750 ,X_Inv_Gen_Rejection_Code =>l_Inv_Gen_Rejection_Code
2751 ,X_Adjusting_Revenue_Flag =>p_event_in_rec.P_adjusting_revenue_flag
2752 ,X_inventory_org_id =>p_inv_org_id
2753 ,X_inventory_item_id =>p_event_in_rec.P_inventory_item_id
2754 ,X_quantity_billed =>p_event_in_rec.P_quantity_billed
2755 ,X_uom_code =>p_event_in_rec.P_uom_code
2756 ,X_unit_price =>p_event_in_rec.P_unit_price
2757 ,X_reference1 =>p_event_in_rec.P_reference1
2758 ,X_reference2 =>p_event_in_rec.P_reference2
2759 ,X_reference3 =>p_event_in_rec.P_reference3
2760 ,X_reference4 =>p_event_in_rec.P_reference4
2761 ,X_reference5 =>p_event_in_rec.P_reference5
2762 ,X_reference6 =>p_event_in_rec.P_reference6
2763 ,X_reference7 =>p_event_in_rec.P_reference7
2764 ,X_reference8 =>p_event_in_rec.P_reference8
2765 ,X_reference9 =>p_event_in_rec.P_reference9
2766 ,X_reference10 =>p_event_in_rec.P_reference10
2767 ,X_agreement_id =>P_agreement_id ); -- Federal Uptake
2768
2769 END IF; /* Added for Bug 7110782 */
2770
2771 --If commit is set to true then commit to database.
2772 IF FND_API.to_boolean( p_commit )
2773 THEN
2774 COMMIT;
2775 END IF;
2776
2777 EXCEPTION
2778 WHEN FND_API.G_EXC_ERROR
2779 THEN
2780 ROLLBACK TO Update_event;
2781 l_return_status := FND_API.G_RET_STS_ERROR;
2782
2783 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2784 THEN
2785 ROLLBACK TO Update_event;
2786 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2787
2788 WHEN pa_event_pvt.pub_excp
2789 THEN
2790 ROLLBACK TO Update_event;
2791 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2792 PA_EVENT_PUB.PACKAGE_NAME
2793 :='(event_reference='||p_event_in_rec.p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
2794 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'UPDATE_EVENT';
2795
2796 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2797 THEN
2798 FND_MSG_PUB.add_exc_msg
2799 ( p_pkg_name => PACKAGE_NAME
2800 , p_procedure_name => PROCEDURE_NAME );
2801 PACKAGE_NAME:=NULL;
2802 PROCEDURE_NAME:=NULL;
2803 END IF;
2804
2805 WHEN OTHERS
2806 THEN
2807 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2808 ROLLBACK TO Update_event;
2809 PA_EVENT_PUB.PACKAGE_NAME
2810 :='(event_reference='||p_event_in_rec.p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
2811 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'UPDATE_EVENT';
2812
2813 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2814 THEN
2815 FND_MSG_PUB.add_exc_msg
2816 ( p_pkg_name => PACKAGE_NAME
2817 , p_procedure_name => PROCEDURE_NAME );
2818 PACKAGE_NAME:=NULL;
2819 PROCEDURE_NAME:=NULL;
2820 END IF;
2821
2822 END; --end of Inner Block
2823
2824 --Populating the output table
2825 p_event_out_tbl(tot_out_rec).pm_event_reference := p_event_in_rec.p_pm_event_reference;
2826 p_event_out_tbl(tot_out_rec).Event_Id := p_event_in_rec.P_event_id;
2827 p_event_out_tbl(tot_out_rec).Return_status := P_return_status;
2828 tot_out_rec := tot_out_rec + 1;
2832
2829 tot_in_rec := p_event_in_tbl.next(tot_in_rec);
2830
2831 pa_debug.reset_err_stack; -- Reset error stack
2833 END LOOP; --End of loop
2834
2835 --Setting the return status to false even if one record fails the validation.
2836 p_return_status := l_return_status;
2837
2838 --If there is one error message then extract the error meaasge and return it.
2839 FND_MSG_PUB.Count_And_Get
2840 ( p_encoded => l_encoded
2841 ,p_count => p_msg_count
2842 ,p_data => p_msg_data );
2843
2844 EXCEPTION
2845 WHEN FND_API.G_EXC_ERROR
2846 THEN
2847 p_return_status := FND_API.G_RET_STS_ERROR;
2848 FND_MSG_PUB.Count_And_Get
2849 ( p_encoded => l_encoded
2850 ,p_count => p_msg_count
2851 ,p_data => p_msg_data );
2852
2853 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2854 THEN
2855 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2856 FND_MSG_PUB.Count_And_Get
2857 ( p_encoded => l_encoded
2858 ,p_count => p_msg_count
2859 ,p_data => p_msg_data );
2860
2861 WHEN pa_event_pvt.pub_excp
2862 THEN
2863 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2864 PA_EVENT_PUB.PACKAGE_NAME
2865 :='(event_reference='||p_event_in_rec.p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
2866 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'UPDATE_EVENT';
2867
2868 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2869 THEN
2870 FND_MSG_PUB.add_exc_msg
2871 ( p_pkg_name => PACKAGE_NAME
2872 , p_procedure_name => PROCEDURE_NAME );
2873 PACKAGE_NAME:=NULL;
2874 PROCEDURE_NAME:=NULL;
2875 END IF;
2876 FND_MSG_PUB.Count_And_Get
2877 ( p_encoded => l_encoded
2878 ,p_count => p_msg_count
2879 ,p_data => p_msg_data );
2880
2881 WHEN OTHERS
2882 THEN
2883 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2884
2885 PA_EVENT_PUB.PACKAGE_NAME
2886 :='(event_reference='||p_event_in_rec.p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
2887 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'UPDATE_EVENT';
2888
2889 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2890 THEN
2891 FND_MSG_PUB.add_exc_msg
2892 ( p_pkg_name => PACKAGE_NAME
2893 , p_procedure_name => PROCEDURE_NAME );
2894 PACKAGE_NAME:=NULL;
2895 PROCEDURE_NAME:=NULL;
2896 END IF;
2897
2898 FND_MSG_PUB.Count_And_Get
2899 ( p_encoded => l_encoded
2900 ,p_count => p_msg_count
2901 ,p_data => p_msg_data );
2902
2903 END UPDATE_EVENT;
2904
2905 -- ============================================================================
2906 --
2907 --Name: delete_event
2908 --Type: procedure
2909 --Description: This API deletes an existing event or a set of existing events
2910 --
2911 --Called subprograms:
2912 --Called subprograms: PA_EVENT_PVT.CHECK_MDTY_PARAMS1
2913 -- PA_EVENT_PVT.CHECK_MDTY_PARAMS2
2914 -- PA_EVENT_PVT.CHECK_DELETE_EVENT_OK
2915 -- PA_EVENTS_PKG.DELETE_ROW
2916 --
2917 --
2918 --
2919 --History:
2920
2921 -- ============================================================================
2922 PROCEDURE DELETE_EVENT
2923 (p_api_version_number IN NUMBER
2924 ,p_commit IN VARCHAR2
2925 ,p_init_msg_list IN VARCHAR2
2926 ,p_pm_product_code IN VARCHAR2
2927 ,p_pm_event_reference IN VARCHAR2
2928 ,p_event_id IN NUMBER
2929 ,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2930 ,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2931 ,p_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2932 AS
2933
2934 p_api_name VARCHAR2(100):='DELETE_EVENT';
2935 P_rowid VARCHAR2(18);
2936 l_event_id_out NUMBER:=NULL;
2937 l_encoded VARCHAR2(1):='F';
2938 l_pm_event_reference VARCHAR2(25);
2939 BEGIN
2940
2941 -- Initialize the Error Stack
2942 pa_debug.set_err_stack('PA_EVENT_PUB.DELETE_EVENT');
2943
2944 --Log Message
2945 IF l_debug_mode = 'Y' THEN
2946 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.DELETE_EVENT.begin'
2947 ,x_msg => 'Beginning of Delete Event'
2948 ,x_log_level => 5);
2949 END IF;
2950
2951
2952 --Seting Savepoint
2953 Savepoint delete_event;
2957 IF l_debug_mode = 'Y' THEN
2954 p_msg_count := 0;
2955 p_return_status := FND_API.G_RET_STS_SUCCESS;
2956 --Log Message
2958 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.DELETE_EVENT.begin'
2959 ,x_msg => 'Calling mandatory input parameters-1'
2960 ,x_log_level => 5);
2961 END IF;
2962
2963 ---Validating mandatory input parameters-1
2964
2965 PA_EVENT_PVT.CHECK_MDTY_PARAMS1
2966 ( p_api_version_number =>p_api_version_number
2967 ,p_api_name =>p_api_name
2968 ,p_pm_product_code =>p_pm_product_code
2969 ,p_function_name =>'PA_EV_DELETE_EVENT'
2970 ,x_return_status =>p_return_status
2971 ,x_msg_count =>p_msg_count
2972 ,x_msg_data =>p_msg_data );
2973
2974 If p_return_status <> FND_API.G_RET_STS_SUCCESS
2975 Then
2976 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2977 THEN
2978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2979
2980 ELSIF p_return_status = FND_API.G_RET_STS_ERROR
2981 THEN
2982 RAISE FND_API.G_EXC_ERROR;
2983 END IF;
2984 End If;
2985
2986 --Log Message
2987 IF l_debug_mode = 'Y' THEN
2988 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.DELETE_EVENT.begin'
2989 ,x_msg => ' Calling mandatory input parameters-2'
2990 ,x_log_level => 5);
2991 END IF;
2992
2993 -- Validating mandatory input parameters-2
2994 /*Commenting out this call as the event reference is not a mandatory parameter.bug 3118781
2995 PA_EVENT_PVT.CHECK_MDTY_PARAMS2
2996 ( p_pm_event_reference => p_pm_event_reference
2997 ,p_pm_product_code => p_pm_product_code
2998 ,p_project_number => NULL
2999 ,p_event_type => NULL
3000 ,p_organization_name => NULL
3001 ,p_calling_place => 'DELETE_EVENT'
3002 ,x_return_status => p_return_status );
3003
3004 If p_return_status <> FND_API.G_RET_STS_SUCCESS
3005 Then
3006 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3007 THEN
3008 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3009
3010 ELSIF p_return_status = FND_API.G_RET_STS_ERROR
3011 THEN
3012 RAISE FND_API.G_EXC_ERROR;
3013 END IF;
3014 End If;
3015 End of comment for 3118781 */
3016 --Log Message
3017 IF l_debug_mode = 'Y' THEN
3018 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.DELETE_EVENT.begin'
3019 ,x_msg => 'Calling conv_event_ref_to_id'
3020 ,x_log_level => 5);
3021 END IF;
3022 l_pm_event_reference :=p_pm_event_reference;
3023 l_event_id_out:=p_event_id;
3024 --Validation of event_id or pm_event_reference and conversion to event_id
3025
3026 If PA_EVENT_PVT.CONV_EVENT_REF_TO_ID
3027 (P_pm_product_code =>P_pm_product_code
3028 ,P_pm_event_reference =>l_pm_event_reference
3029 ,P_event_id =>l_event_id_out)
3030 ='N'
3031 Then
3032 p_return_status := FND_API.G_RET_STS_ERROR;
3033 RAISE FND_API.G_EXC_ERROR;
3034 End If;
3035
3036 --Log Message
3037 IF l_debug_mode = 'Y' THEN
3038 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.DELETE_EVENT.begin'
3039 ,x_msg => 'Calling check_delete_event_ok'
3040 ,x_log_level => 5);
3041 END IF;
3042
3043 -- Calling check_delete_event_ok in pa_event_pvt package
3044
3045 If PA_EVENT_PVT.CHECK_DELETE_EVENT_OK
3046 (P_pm_event_reference =>l_pm_event_reference
3047 ,P_event_id =>l_event_id_out) ='N'
3048 Then
3049 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3050 THEN
3051 pa_interface_utils_pub.map_new_amg_msg
3052 ( p_old_message_code => 'PA_TK_EVENT_IN_USE'
3053 ,p_msg_attribute => 'CHANGE'
3054 ,p_resize_flag => 'N'
3055 ,p_msg_context => 'EVENT'
3056 ,p_attribute1 => l_pm_event_reference
3057 ,p_attribute2 => ''
3058 ,p_attribute3 => ''
3059 ,p_attribute4 => ''
3060 ,p_attribute5 => '');
3061 END IF;
3062 p_return_status := FND_API.G_RET_STS_ERROR;
3063 RAISE FND_API.G_EXC_ERROR;
3064
3065 End If;
3066
3067
3068 --If validation is successful then delete the event from PA_EVENTS
3069
3070 --Populating the rowid
3071 Select Rowid
3072 Into p_rowid
3073 From pa_events
3074 Where event_id=l_event_id_out;
3075
3076
3077 --Log Message
3078 IF l_debug_mode = 'Y' THEN
3079 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.DELETE_EVENT.begin'
3080 ,x_msg => 'Calling table handler to delete event'
3081 ,x_log_level => 5);
3082 END IF;
3083
3084 --calling PROCEDURE Delete_Row to delete the event
3085
3086
3087 PA_EVENTS_PKG.Delete_Row(X_Rowid => P_rowid);
3088
3089 --If commit is set to true then commit to database.
3090 IF FND_API.to_boolean( p_commit )
3091 THEN
3095
3092 COMMIT;
3093 END IF;
3094
3096 --If there is one error message then extract the error meaasge
3097 FND_MSG_PUB.Count_And_Get
3098 ( p_encoded => l_encoded
3099 ,p_count => p_msg_count
3100 ,p_data => p_msg_data );
3101
3102 pa_debug.reset_err_stack; -- Reset error stack
3103
3104
3105 EXCEPTION
3106 WHEN FND_API.G_EXC_ERROR
3107 THEN
3108 ROLLBACK to delete_event;
3109 p_return_status := FND_API.G_RET_STS_ERROR;
3110 FND_MSG_PUB.Count_And_Get
3111 ( p_encoded => l_encoded
3112 , p_count => p_msg_count
3113 , p_data => p_msg_data );
3114
3115 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3116 THEN
3117 ROLLBACK to delete_event;
3118 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3119 FND_MSG_PUB.Count_And_Get
3120 ( p_encoded => l_encoded
3121 , p_count => p_msg_count
3122 , p_data => p_msg_data );
3123
3124 WHEN pa_event_pvt.pub_excp
3125 THEN
3126 ROLLBACK to delete_event;
3127 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3128
3129 PA_EVENT_PUB.PACKAGE_NAME
3130 :='(event_reference='||p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
3131 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||'DELETE_EVENT';
3132
3133 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3134 THEN
3135 FND_MSG_PUB.add_exc_msg
3136 ( p_pkg_name => PACKAGE_NAME
3137 , p_procedure_name => PROCEDURE_NAME );
3138 PACKAGE_NAME:=NULL;
3139 PROCEDURE_NAME:=NULL;
3140 END IF;
3141
3142 FND_MSG_PUB.Count_And_Get
3143 ( p_encoded => l_encoded
3144 , p_count => p_msg_count
3145 , p_data => p_msg_data );
3146
3147 WHEN OTHERS
3148 THEN
3149 ROLLBACK to delete_event;
3150 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3151
3152 PA_EVENT_PUB.PACKAGE_NAME
3153 :='(event_reference='||p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
3154 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'DELETE_EVENT';
3155
3156 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3157 THEN
3158 FND_MSG_PUB.add_exc_msg
3159 ( p_pkg_name => PACKAGE_NAME
3160 , p_procedure_name => PROCEDURE_NAME );
3161 PACKAGE_NAME:=NULL;
3162 PROCEDURE_NAME:=NULL;
3163 END IF;
3164
3165 FND_MSG_PUB.Count_And_Get
3166 ( p_encoded => l_encoded
3167 , p_count => p_msg_count
3168 , p_data => p_msg_data );
3169
3170
3171 END DELETE_EVENT;
3172
3173 -- ============================================================================
3174 --
3175 --Name: init_event
3176 --Type: Procedure
3177 --Description: This procedure can be used to initialize the global PL/SQL
3178 -- tables that are used by a LOAD/EXECUTE/FETCH cycle.
3179 --
3180 --History:
3181 --
3182 -- =============================================================================
3183 PROCEDURE INIT_EVENT
3184 AS
3185 BEGIN
3186
3187 --Initialising global tables
3188 G_event_in_tbl.delete;
3189 G_event_out_tbl.delete;
3190
3191 EXCEPTION
3192 WHEN OTHERS
3193 THEN
3194
3195 PA_EVENT_PUB.PACKAGE_NAME
3196 :=PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
3197 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'INIT_EVENT';
3198
3199 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3200 THEN
3201 FND_MSG_PUB.add_exc_msg
3202 ( p_pkg_name => PACKAGE_NAME
3203 , p_procedure_name => PROCEDURE_NAME );
3204 PACKAGE_NAME:=NULL;
3205 PROCEDURE_NAME:=NULL;
3206 END IF;
3207
3208 END INIT_EVENT;
3209
3210 -- ============================================================================
3211 --
3212 --Name: load_event
3213 --Type: Procedure
3214 --Description: This procedure can be used to move the event related
3215 -- parameters from the client side to a record on the server side
3216 -- , where it will be used by a LOAD/EXECUTE/FETCH cycle.
3217 --
3218 --History:
3219 --
3223 (p_pm_product_code IN VARCHAR2
3220 -- ============================================================================
3221
3222 PROCEDURE LOAD_EVENT
3224 ,P_api_version_number IN NUMBER
3225 ,P_init_msg_list IN VARCHAR2
3226 ,P_pm_event_reference IN VARCHAR2
3227 ,P_task_number IN VARCHAR2
3228 ,P_event_number IN NUMBER
3229 ,P_event_type IN VARCHAR2
3230 ,P_agreement_number IN VARCHAR2 -- Federal Uptake
3231 ,P_agreement_type IN VARCHAR2 -- Federal Uptake
3232 ,P_customer_number IN VARCHAR2 -- Federal Uptake
3233 ,P_description IN VARCHAR2
3234 ,P_bill_hold_flag IN VARCHAR2
3235 ,P_completion_date IN DATE
3236 ,P_desc_flex_name IN VARCHAR2
3237 ,P_attribute_category IN VARCHAR2
3238 ,P_attribute1 IN VARCHAR2
3239 ,P_attribute2 IN VARCHAR2
3240 ,P_attribute3 IN VARCHAR2
3241 ,P_attribute4 IN VARCHAR2
3242 ,P_attribute5 IN VARCHAR2
3243 ,P_attribute6 IN VARCHAR2
3244 ,P_attribute7 IN VARCHAR2
3245 ,P_attribute8 IN VARCHAR2
3246 ,P_attribute9 IN VARCHAR2
3247 ,P_attribute10 IN VARCHAR2
3248 ,P_project_number IN VARCHAR2
3249 ,P_organization_name IN VARCHAR2
3250 ,P_inventory_org_name IN VARCHAR2
3251 ,P_inventory_item_id IN NUMBER
3252 ,P_quantity_billed IN NUMBER
3253 ,P_uom_code IN VARCHAR2
3254 ,P_unit_price IN NUMBER
3255 ,P_reference1 IN VARCHAR2
3256 ,P_reference2 IN VARCHAR2
3257 ,P_reference3 IN VARCHAR2
3258 ,P_reference4 IN VARCHAR2
3259 ,P_reference5 IN VARCHAR2
3260 ,P_reference6 IN VARCHAR2
3261 ,P_reference7 IN VARCHAR2
3262 ,P_reference8 IN VARCHAR2
3263 ,P_reference9 IN VARCHAR2
3264 ,P_reference10 IN VARCHAR2
3265 ,P_bill_trans_currency_code IN VARCHAR2
3266 ,P_bill_trans_bill_amount IN NUMBER
3267 ,P_bill_trans_rev_amount IN NUMBER
3268 ,P_project_rate_type IN VARCHAR2
3269 ,P_project_rate_date IN DATE
3270 ,P_project_exchange_rate IN NUMBER
3271 ,P_projfunc_rate_type IN VARCHAR2
3272 ,P_projfunc_rate_date IN DATE
3273 ,P_projfunc_exchange_rate IN NUMBER
3274 ,P_funding_rate_type IN VARCHAR2
3275 ,P_funding_rate_date IN DATE
3276 ,P_funding_exchange_rate IN NUMBER
3277 ,P_adjusting_revenue_flag IN VARCHAR2
3278 ,P_event_id IN NUMBER
3279 ,P_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3280
3281 AS
3282
3283 p_api_name VARCHAR2(100):='LOAD_EVENT';
3284 p_msg_count NUMBER:=0;
3285 p_msg_data VARCHAR2(2000);
3286 BEGIN
3287
3288 -- Initialize the Error Stack
3289 pa_debug.set_err_stack('PA_EVENT_PUB.LOAD_EVENT');
3290
3291 --Log Message
3292 IF l_debug_mode = 'Y' THEN
3293 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.LOAD_EVENT.begin'
3294 ,x_msg => 'Beginning of Load_Event'
3295 ,x_log_level => 5);
3296 End If;
3297
3298
3299 --Log Message
3300 IF l_debug_mode = 'Y' THEN
3301 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.LOAD_EVENT.begin'
3302 ,x_msg => 'Beginning of api compatibility check '
3303 ,x_log_level => 5);
3304 End If;
3305
3306 -- Standard call to check for api compatibility.
3307 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3308 p_api_version_number ,
3309 p_api_name ,
3310 G_PKG_NAME )
3311 THEN
3312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3313 END IF;
3314
3315 --Log Message
3316 IF l_debug_mode = 'Y' THEN
3317 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.LOAD_EVENT.begin'
3318 ,x_msg => 'Beginning of Loading the global tables'
3319 ,x_log_level => 5);
3320 End If;
3321
3322 --If return status is success then populating the global tables
3323
3324 G_event_in_tbl(G_event_tbl_count).P_pm_event_reference :=P_pm_event_reference;
3325 G_event_in_tbl(G_event_tbl_count).P_task_number :=P_task_number;
3326 G_event_in_tbl(G_event_tbl_count).P_event_number :=P_event_number;
3327 G_event_in_tbl(G_event_tbl_count).P_event_type :=P_event_type;
3328 -- Added the below three lines for Federal Uptake
3329 G_event_in_tbl(G_event_tbl_count).P_agreement_number :=P_agreement_number;
3330 G_event_in_tbl(G_event_tbl_count).P_agreement_type :=P_agreement_type;
3331 G_event_in_tbl(G_event_tbl_count).P_customer_number :=P_customer_number;
3332 G_event_in_tbl(G_event_tbl_count).P_description :=P_description;
3333 G_event_in_tbl(G_event_tbl_count).P_bill_hold_flag :=P_bill_hold_flag;
3337 G_event_in_tbl(G_event_tbl_count).P_attribute1 :=P_attribute1;
3334 G_event_in_tbl(G_event_tbl_count).P_completion_date :=P_completion_date;
3335 G_event_in_tbl(G_event_tbl_count).P_desc_flex_name :=P_desc_flex_name;
3336 G_event_in_tbl(G_event_tbl_count).P_attribute_category :=P_attribute_category;
3338 G_event_in_tbl(G_event_tbl_count).P_attribute2 :=P_attribute2;
3339 G_event_in_tbl(G_event_tbl_count).P_attribute3 :=P_attribute3;
3340 G_event_in_tbl(G_event_tbl_count).P_attribute4 :=P_attribute4;
3341 G_event_in_tbl(G_event_tbl_count).P_attribute5 :=P_attribute5;
3342 G_event_in_tbl(G_event_tbl_count).P_attribute6 :=P_attribute6;
3343 G_event_in_tbl(G_event_tbl_count).P_attribute7 :=P_attribute7;
3344 G_event_in_tbl(G_event_tbl_count).P_attribute8 :=P_attribute8;
3345 G_event_in_tbl(G_event_tbl_count).P_attribute9 :=P_attribute9;
3346 G_event_in_tbl(G_event_tbl_count).P_attribute10 :=P_attribute10;
3347 G_event_in_tbl(G_event_tbl_count).P_project_number :=P_project_number;
3348 G_event_in_tbl(G_event_tbl_count).P_organization_name :=P_organization_name;
3349 G_event_in_tbl(G_event_tbl_count).P_inventory_org_name :=P_inventory_org_name;
3350 G_event_in_tbl(G_event_tbl_count).P_inventory_item_id :=P_inventory_item_id;
3351 G_event_in_tbl(G_event_tbl_count).P_quantity_billed :=P_quantity_billed;
3352 G_event_in_tbl(G_event_tbl_count).P_uom_code :=P_uom_code;
3353 G_event_in_tbl(G_event_tbl_count).P_unit_price :=P_unit_price;
3354 G_event_in_tbl(G_event_tbl_count).P_reference1 :=P_reference1;
3355 G_event_in_tbl(G_event_tbl_count).P_reference2 :=P_reference2;
3356 G_event_in_tbl(G_event_tbl_count).P_reference3 :=P_reference3;
3357 G_event_in_tbl(G_event_tbl_count).P_reference4 :=P_reference4;
3358 G_event_in_tbl(G_event_tbl_count).P_reference5 :=P_reference5;
3359 G_event_in_tbl(G_event_tbl_count).P_reference6 :=P_reference6;
3360 G_event_in_tbl(G_event_tbl_count).P_reference7 :=P_reference7;
3361 G_event_in_tbl(G_event_tbl_count).P_reference8 :=P_reference8;
3362 G_event_in_tbl(G_event_tbl_count).P_reference9 :=P_reference9;
3363 G_event_in_tbl(G_event_tbl_count).P_reference10 :=P_reference10;
3364 G_event_in_tbl(G_event_tbl_count).P_bill_trans_currency_code :=P_bill_trans_currency_code;
3365 G_event_in_tbl(G_event_tbl_count).P_bill_trans_bill_amount :=P_bill_trans_bill_amount;
3366 G_event_in_tbl(G_event_tbl_count).P_bill_trans_rev_amount :=P_bill_trans_rev_amount;
3367 G_event_in_tbl(G_event_tbl_count).P_project_rate_type :=P_project_rate_type;
3368 G_event_in_tbl(G_event_tbl_count).P_project_rate_date :=P_project_rate_date;
3369 G_event_in_tbl(G_event_tbl_count).P_project_exchange_rate :=P_project_exchange_rate;
3370 G_event_in_tbl(G_event_tbl_count).P_projfunc_rate_type :=P_projfunc_rate_type;
3371 G_event_in_tbl(G_event_tbl_count).P_projfunc_rate_date :=P_projfunc_rate_date;
3372 G_event_in_tbl(G_event_tbl_count).P_projfunc_exchange_rate :=P_projfunc_exchange_rate;
3373 G_event_in_tbl(G_event_tbl_count).P_funding_rate_type :=P_funding_rate_type;
3374 G_event_in_tbl(G_event_tbl_count).P_funding_rate_date :=P_funding_rate_date;
3375 G_event_in_tbl(G_event_tbl_count).P_funding_exchange_rate :=P_funding_exchange_rate;
3376 G_event_in_tbl(G_event_tbl_count).P_adjusting_revenue_flag :=P_adjusting_revenue_flag;
3377 G_event_in_tbl(G_event_tbl_count).P_event_id :=P_event_id;
3378
3379 G_event_tbl_count := G_event_tbl_count+1;
3380
3381 pa_debug.reset_err_stack; -- Reset error stack
3382 EXCEPTION
3383 WHEN FND_API.G_EXC_ERROR
3384 THEN
3385 p_return_status := FND_API.G_RET_STS_ERROR;
3386 FND_MSG_PUB.Count_And_Get
3387 ( p_count => p_msg_count ,
3388 p_data => p_msg_data );
3389
3390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3391 THEN
3392 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3393 FND_MSG_PUB.Count_And_Get
3394 ( p_count => p_msg_count
3395 , p_data => p_msg_data );
3396
3397 WHEN OTHERS
3398 THEN
3399 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3400
3401 PA_EVENT_PUB.PACKAGE_NAME
3402 :='(event_reference='||G_event_in_tbl(G_event_tbl_count).P_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
3403 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'LOAD_EVENT';
3404
3405 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3406 THEN
3407 FND_MSG_PUB.add_exc_msg
3408 ( p_pkg_name => PACKAGE_NAME
3409 , p_procedure_name => PROCEDURE_NAME );
3410 PACKAGE_NAME:=NULL;
3411 PROCEDURE_NAME:=NULL;
3412 END IF;
3413
3414 FND_MSG_PUB.Count_And_Get
3415 ( p_count => p_msg_count ,
3416 p_data => p_msg_data );
3417
3418
3419 END LOAD_EVENT;
3420
3421 -- ============================================================================
3422 --
3423 --Name: EXECUTE_CREATE_EVENT
3424 --Type: Procedure
3425 --Description: This procedure can be used to create an event
3426 -- using global PL/SQL tables.
3427 --
3428 --Called subprograms: XXX
3429 --
3430 --History:
3431 -- ============================================================================
3432 PROCEDURE EXECUTE_CREATE_EVENT
3433 (p_api_version_number IN NUMBER
3434 ,p_commit IN VARCHAR2
3438 ,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3435 ,p_init_msg_list IN VARCHAR2
3436 ,p_pm_product_code IN VARCHAR2
3437 ,p_event_id_out OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3439 ,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3440 ,p_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3441
3442 AS
3443 p_api_name VARCHAR2(100):='EXECUTE_CREATE_EVENT';
3444 l_encoded varchar2(1):='F';
3445 BEGIN
3446 -- Initialize the Error Stack
3447 pa_debug.set_err_stack('PA_EVENT_PUB.EXECUTE_CREATE_EVENT');
3448
3449 --Log Message
3450 IF l_debug_mode = 'Y' THEN
3451 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.EXECUTE_CREATE_EVENT.begin'
3452 ,x_msg => 'Beginning of Execute_Create_Event'
3453 ,x_log_level => 5);
3454 End If;
3455
3456
3457
3458 --Log Message
3459 IF l_debug_mode = 'Y' THEN
3460 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.EXECUTE_CREATE_EVENT.begin'
3461 ,x_msg => 'Beginning of api compatibility check '
3462 ,x_log_level => 5);
3463 End If;
3464
3465 -- Standard call to check for api compatibility.
3466 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3467 p_api_version_number ,
3468 p_api_name ,
3469 G_PKG_NAME )
3470 THEN
3471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3472 END IF;
3473
3474 --Log Message
3475 IF l_debug_mode = 'Y' THEN
3476 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.EXECUTE_CREATE_EVENT.begin'
3477 ,x_msg => 'Calling Create_event '
3478 ,x_log_level => 5);
3479 End If;
3480
3481 --- Calling Create_event procedure for further processing
3482 Create_event
3483 (P_api_version_number =>p_api_version_number
3484 ,P_commit =>p_commit
3485 ,P_init_msg_list =>p_init_msg_list
3486 ,P_msg_count =>p_msg_count
3487 ,P_msg_data =>p_msg_data
3488 ,P_return_status =>p_return_status
3489 ,P_pm_product_code =>p_pm_product_code
3490 ,P_event_in_tbl =>G_event_in_tbl
3491 ,P_event_out_tbl =>G_event_out_tbl);
3492
3493
3494 If p_return_status <> FND_API.G_RET_STS_SUCCESS
3495 Then
3496 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3497 THEN
3498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3499
3500 ELSIF p_return_status = FND_API.G_RET_STS_ERROR
3501 THEN
3502 RAISE FND_API.G_EXC_ERROR;
3503 END IF;
3504 End If;
3505
3506 -- If single event is processed then return the event_id
3507 If G_event_out_tbl.count = 1
3508 Then
3509 p_event_id_out := G_event_out_tbl(0).event_id;
3510 End If;
3511
3512 -- Call fetch_event API in case of multiple events creation
3513 -- to get the event id of new events created
3514
3515 pa_debug.reset_err_stack; -- Reset error stack
3516
3517
3518 EXCEPTION
3519 WHEN FND_API.G_EXC_ERROR
3520 THEN
3521 p_event_id_out := NULL; -- NOCOPY
3522 p_return_status := FND_API.G_RET_STS_ERROR;
3523 FND_MSG_PUB.Count_And_Get
3524 ( p_encoded => l_encoded
3525 ,p_count => p_msg_count
3526 ,p_data => p_msg_data );
3527
3528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3529 THEN
3530 p_event_id_out := NULL; -- NOCOPY
3531 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3532 FND_MSG_PUB.Count_And_Get
3533 ( p_encoded => l_encoded
3534 ,p_count => p_msg_count
3535 ,p_data => p_msg_data );
3536
3537 WHEN OTHERS
3538 THEN
3539 p_event_id_out := NULL; -- NOCOPY
3540 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3541
3542 PA_EVENT_PUB.PACKAGE_NAME
3543 :=PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
3544 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'EXECUTE_CREATE_EVENT';
3545
3546 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3547 THEN
3548 FND_MSG_PUB.add_exc_msg
3549 ( p_pkg_name => PACKAGE_NAME
3550 , p_procedure_name => PROCEDURE_NAME );
3551 PACKAGE_NAME:=NULL;
3552 PROCEDURE_NAME:=NULL;
3553 END IF;
3554
3555 FND_MSG_PUB.Count_And_Get
3556 ( p_encoded => l_encoded
3557 ,p_count => p_msg_count
3558 ,p_data => p_msg_data );
3559
3560
3561 END EXECUTE_CREATE_EVENT;
3562
3563 -- ============================================================================
3564 --
3565 --Name: EXECUTE_UPDATE_EVENT
3566 --Type: Procedure
3567 --Description: This procedure can be used to update an event
3568 -- using global PL/SQL tables.
3569 --
3570 --Called subprograms: XXX
3571 --
3572 --History:
3573 --
3574 -- ============================================================================
3575
3579 ,p_init_msg_list IN VARCHAR2
3576 PROCEDURE EXECUTE_UPDATE_EVENT
3577 (p_api_version_number IN NUMBER
3578 ,p_commit IN VARCHAR2
3580 ,p_pm_product_code IN VARCHAR2
3581 ,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3582 ,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3583 ,p_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3584
3585 AS
3586
3587 p_api_name VARCHAR2(100):='EXECUTE_UPDATE_EVENT';
3588 l_encoded VARCHAR2(1):='F';
3589
3590 BEGIN
3591
3592 -- Initialize the Error Stack
3593 pa_debug.set_err_stack('PA_EVENT_PUB.EXECUTE_UPDATE_EVENT');
3594
3595 --Log Message
3596 IF l_debug_mode = 'Y' THEN
3597 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.EXECUTE_UPDATE_EVENT.begin'
3598 ,x_msg => 'Beginning of Execute_Update_Event'
3599 ,x_log_level => 5);
3600 End If;
3601
3602
3603 --Log Message
3604 IF l_debug_mode = 'Y' THEN
3605 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.EXECUTE_UPDATE_EVENT.begin'
3606 ,x_msg => 'Beginning of api compatibility check '
3607 ,x_log_level => 5);
3608 End If;
3609
3610 -- Standard call to check for api compatibility.
3611 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3612 p_api_version_number ,
3613 p_api_name ,
3614 G_PKG_NAME )
3615 THEN
3616 p_return_status:=FND_API.G_RET_STS_ERROR;
3617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3618 END IF;
3619
3620 --Log Message
3621 IF l_debug_mode = 'Y' THEN
3622 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.EXECUTE_UPDATE_EVENT.begin'
3623 ,x_msg => 'Calling Update_event '
3624 ,x_log_level => 5);
3625 End If;
3626
3627 --Calling update_event procedure for further processing
3628
3629 UPDATE_EVENT
3630 (P_api_version_number =>p_api_version_number
3631 ,P_commit =>p_commit
3632 ,P_init_msg_list =>p_init_msg_list
3633 ,P_msg_count =>p_msg_count
3634 ,P_msg_data =>p_msg_data
3635 ,P_return_status =>p_return_status
3636 ,P_pm_product_code =>p_pm_product_code
3637 ,P_event_in_tbl =>G_event_in_tbl
3638 ,P_event_out_tbl =>G_event_out_tbl);
3639
3640 If p_return_status <> FND_API.G_RET_STS_SUCCESS
3641 Then
3642 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3643 THEN
3644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3645
3646 ELSIF p_return_status = FND_API.G_RET_STS_ERROR
3647 THEN
3648 RAISE FND_API.G_EXC_ERROR;
3649 END IF;
3650 End If;
3651
3652 pa_debug.reset_err_stack; -- Reset error stack
3653
3654
3655 EXCEPTION
3656 WHEN FND_API.G_EXC_ERROR
3657 THEN
3658 p_return_status := FND_API.G_RET_STS_ERROR;
3659 FND_MSG_PUB.Count_And_Get
3660 ( p_encoded => l_encoded
3661 ,p_count => p_msg_count
3662 ,p_data => p_msg_data );
3663
3664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3665 THEN
3666 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3667 FND_MSG_PUB.Count_And_Get
3668 ( p_encoded => l_encoded
3669 ,p_count => p_msg_count
3670 ,p_data => p_msg_data );
3671
3672 WHEN OTHERS
3673 THEN
3674 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3675
3676 PA_EVENT_PUB.PACKAGE_NAME
3677 :=PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
3678 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'EXECUTE_UPDATE_EVENT';
3679
3680 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3681 THEN
3682 FND_MSG_PUB.add_exc_msg
3683 ( p_pkg_name => PACKAGE_NAME
3684 , p_procedure_name => PROCEDURE_NAME );
3685 PACKAGE_NAME:=NULL;
3686 PROCEDURE_NAME:=NULL;
3687 END IF;
3688
3689 FND_MSG_PUB.Count_And_Get
3690 ( p_encoded => l_encoded
3691 ,p_count => p_msg_count
3692 ,p_data => p_msg_data );
3693
3694 END EXECUTE_UPDATE_EVENT;
3695
3696 -- ============================================================================
3697 --
3698 --Name: clear_event
3699 --Type: Procedure
3700 --Description: This procedure can be used to clear the global PL/SQL
3701 -- tables that are used by a LOAD/EXECUTE/FETCH cycle.
3702 --
3703 --Called subprograms: init_event
3704 --History:
3705 --
3706 -- =======================================================================
3707 PROCEDURE CLEAR_EVENT
3708 AS
3709
3710 BEGIN
3711 -- Call init_event procedure
3712 init_event;
3713
3714 EXCEPTION
3715 WHEN OTHERS
3716 THEN
3717
3718 PA_EVENT_PUB.PACKAGE_NAME
3719 :=PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
3723 THEN
3720 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CLEAR_EVENT';
3721
3722 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3724 FND_MSG_PUB.add_exc_msg
3725 ( p_pkg_name => PACKAGE_NAME
3726 , p_procedure_name => PROCEDURE_NAME );
3727 PACKAGE_NAME:=NULL;
3728 PROCEDURE_NAME:=NULL;
3729 END IF;
3730
3731 END CLEAR_EVENT;
3732
3733
3734 -- ============================================================================
3735 --
3736 --Name: fetch_event
3737 --Type: Procedure
3738 --Description: This procedure can be used to return the event_ids to the
3739 -- external system .
3740 --
3741 --Called subprograms: XXX
3742 --
3743 --
3744 --
3745 --History:
3746 --
3747 -- ============================================================================
3748 PROCEDURE FETCH_EVENT
3749 (p_api_version_number IN NUMBER
3750 ,P_pm_product_code IN VARCHAR2
3751 ,P_pm_event_reference IN VARCHAR2
3752 ,P_event_id_out OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3753 ,p_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3754 IS
3755 tot_recs NUMBER:=0;
3756 p_api_name VARCHAR2(100):='FETCH_EVENT';
3757 p_msg_count NUMBER;
3758 p_msg_data VARCHAR2(2000);
3759 l_encoded VARCHAR2(1):='F';
3760 BEGIN
3761
3762 -- Initialize the Error Stack
3763 pa_debug.set_err_stack('PA_EVENT_PUB.FETCH_EVENT');
3764
3765 --Log Message
3766 IF l_debug_mode = 'Y' THEN
3767 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.FETCH_EVENT.begin'
3768 ,x_msg => 'Beginning of Fetch_Event'
3769 ,x_log_level => 5);
3770 End If;
3771
3772
3773 --Set savepoint
3774 Savepoint fetch_event;
3775 p_msg_count := 0;
3776 p_return_status := FND_API.G_RET_STS_SUCCESS;
3777
3778 --Log Message
3779 IF l_debug_mode = 'Y' THEN
3780 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.FETCH_EVENT.begin'
3781 ,x_msg => 'Beginning of api compatibility check '
3782 ,x_log_level => 5);
3783 End If;
3784
3785 -- Standard call to check for api compatibility.
3786 IF NOT FND_API.Compatible_API_Call ( g_api_version_number ,
3787 p_api_version_number ,
3788 p_api_name ,
3789 G_PKG_NAME )
3790 THEN
3791 p_return_status:=FND_API.G_RET_STS_ERROR;
3792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3793 END IF;
3794 -- CHECK WHETHER MANDATORY INCOMING PARAMETER PRODUCT CODE EXIST
3795
3796 IF (p_pm_product_code IS NULL)
3797 OR (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3798 THEN
3799 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3800 THEN
3801 pa_interface_utils_pub.map_new_amg_msg
3802 ( p_old_message_code => 'PA_PRODUCT_CODE_IS_MISS'
3803 ,p_msg_attribute => 'CHANGE'
3804 ,p_resize_flag => 'N'
3805 ,p_msg_context => 'EVENT'
3806 ,p_attribute1 => ''
3807 ,p_attribute2 => ''
3808 ,p_attribute3 => ''
3809 ,p_attribute4 => ''
3810 ,p_attribute5 => '');
3811 END IF;
3812 p_return_status := FND_API.G_RET_STS_ERROR;
3813 RAISE FND_API.G_EXC_ERROR;
3814 END IF;
3815
3816 --Log Message
3817 IF l_debug_mode = 'Y' THEN
3818 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.FETCH_EVENT.begin'
3819 ,x_msg => 'Begin Fetching event_id from event_reference'
3820 ,x_log_level => 5);
3821 End If;
3822
3823
3824 --Deriving event_id from p_pm_event_reference.
3825 If(p_pm_event_reference Is NOT NULL
3826 And p_pm_event_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
3827 Then
3828 While tot_recs <= G_event_tbl_count
3829 loop
3830 If (G_event_out_tbl(tot_recs).pm_event_reference = p_pm_event_reference )
3831 Then
3832 P_event_id_out:=G_event_out_tbl(tot_recs).event_id;
3833 Exit;
3834 End If;
3835 tot_recs :=tot_recs + 1;
3836 End Loop;
3837 Else
3838 If FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3839 Then
3840 FND_MESSAGE.SET_NAME('PA','PA_API_CONV_ERROR_AMG'); -- Bug 2257612
3841 FND_MESSAGE.SET_TOKEN('ATTR_NAME','p_pm_event_reference');
3842 FND_MESSAGE.SET_TOKEN('ATTR_VALUE', p_pm_event_reference);
3843 FND_MSG_PUB.ADD;
3844 End If;
3845 p_return_status := FND_API.G_RET_STS_ERROR;
3846 RAISE FND_API.G_EXC_ERROR;
3847 END IF;
3848
3849 FND_MSG_PUB.Count_And_Get
3850 ( p_encoded => l_encoded
3851 , p_count => p_msg_count
3852 , p_data => p_msg_data );
3853
3854
3855 --Log Message
3856 IF l_debug_mode = 'Y' THEN
3857 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.FETCH_EVENT.begin'
3858 ,x_msg => 'End of Fetch_Event'
3859 ,x_log_level => 5);
3860 End If;
3861
3862 pa_debug.reset_err_stack; -- Reset error stack
3863
3864 EXCEPTION
3868 p_event_id_out := NULL; -- NOCOPY
3865 WHEN FND_API.G_EXC_ERROR
3866 THEN
3867 ROLLBACK to fetch_event;
3869 p_return_status := FND_API.G_RET_STS_ERROR;
3870 FND_MSG_PUB.Count_And_Get
3871 ( p_encoded => l_encoded
3872 , p_count => p_msg_count
3873 , p_data => p_msg_data );
3874
3875 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3876 THEN
3877 ROLLBACK to fetch_event;
3878 p_event_id_out := NULL; -- NOCOPY
3879 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3880 FND_MSG_PUB.Count_And_Get
3881 ( p_encoded => l_encoded
3882 , p_count => p_msg_count
3883 , p_data => p_msg_data );
3884
3885 WHEN OTHERS
3886 THEN
3887 ROLLBACK to fetch_event;
3888 p_event_id_out := NULL; -- NOCOPY
3889 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3890
3891 PA_EVENT_PUB.PACKAGE_NAME
3892 :='(event_reference='||p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
3893 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'FETCH_EVENT';
3894
3895 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3896 THEN
3897 FND_MSG_PUB.add_exc_msg
3898 ( p_pkg_name => PACKAGE_NAME
3899 , p_procedure_name => PROCEDURE_NAME );
3900 PACKAGE_NAME:=NULL;
3901 PROCEDURE_NAME:=NULL;
3902 END IF;
3903
3904 FND_MSG_PUB.Count_And_Get
3905 ( p_encoded => l_encoded
3906 , p_count => p_msg_count
3907 , p_data => p_msg_data );
3908
3909
3910 END FETCH_EVENT;
3911
3912 -- ============================================================================
3913 --
3914 --Name: CHECK_DELETE_EVENT_OK
3915 --Type: procedure
3916 --Description: This API ONLY checks if an existing event or a set of existing events
3917 -- can be deleted or not.
3918 --Called subprograms:
3919 --
3920 --
3921 --
3922 --History:
3923
3924 -- ============================================================================
3925 PROCEDURE CHECK_DELETE_EVENT_OK
3926 (P_api_version_number IN NUMBER
3927 ,P_commit IN VARCHAR2
3928 ,P_init_msg_list IN VARCHAR2
3929 ,P_pm_product_code IN VARCHAR2
3930 ,P_pm_event_reference IN VARCHAR2
3931 ,P_event_id IN NUMBER
3932 ,P_del_event_ok_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3933 ,P_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3934 ,P_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3935 ,P_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3936
3937
3938 AS
3939 p_api_name VARCHAR2(100):='CHECK_DELETE_EVENT_OK';
3940 l_event_id NUMBER:=NULL;
3941 l_encoded VARCHAR2(1):='F';
3942 l_pm_event_reference VARCHAR2(25);
3943 BEGIN
3944 -- Initialize the Error Stack
3945 pa_debug.set_err_stack('PA_EVENT_PUB.CHECK_DELETE_EVENT_OK');
3946
3947 --Log Message
3948 IF l_debug_mode = 'Y' THEN
3949 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CHECK_DELETE_EVENT_OK.begin'
3950 ,x_msg => 'Beginning of Check_Delete_Event_Ok'
3951 ,x_log_level => 5);
3952 END IF;
3953
3954 --Set savepoint
3955 Savepoint check_delete_event_ok;
3956 p_msg_count := 0;
3957 p_return_status := FND_API.G_RET_STS_SUCCESS;
3958
3959 --Log Message
3960 IF l_debug_mode = 'Y' THEN
3961 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CHECK_DELETE_EVENT_OK.begin'
3962 ,x_msg => 'Calling mandatory input parameters-1'
3963 ,x_log_level => 5);
3964 END IF;
3965
3966 --Validating mandatory input parameters-1
3967
3968 PA_EVENT_PVT.CHECK_MDTY_PARAMS1
3969 ( p_api_version_number =>p_api_version_number
3970 ,p_api_name =>p_api_name
3971 ,p_pm_product_code =>p_pm_product_code
3972 ,p_function_name =>'PA_EV_DEL_EVENT_OK'
3973 ,x_return_status =>p_return_status
3974 ,x_msg_count =>p_msg_count
3975 ,x_msg_data =>p_msg_data );
3976
3977 If p_return_status <> FND_API.G_RET_STS_SUCCESS
3978 Then
3979 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3980 THEN
3981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3982
3983 ELSIF p_return_status = FND_API.G_RET_STS_ERROR
3984 THEN
3985 RAISE FND_API.G_EXC_ERROR;
3986 END IF;
3987 End If;
3988
3989 --Log Message
3990 IF l_debug_mode = 'Y' THEN
3991 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CHECK_DELETE_EVENT_OK.begin'
3992 ,x_msg => 'Calling conv_event_ref_to_id'
3993 ,x_log_level => 5);
3994 END IF;
3995
3996 l_event_id:=p_event_id;
3997 --Validation of event_id or pm_event_reference and conversion to event_id
3998 If PA_EVENT_PVT.CONV_EVENT_REF_TO_ID
3999 (P_pm_product_code =>P_pm_product_code
4000 ,P_pm_event_reference =>l_pm_event_reference
4001 ,P_event_id =>l_event_id)
4002 ='N'
4003 Then
4004 p_return_status := FND_API.G_RET_STS_ERROR;
4005 RAISE FND_API.G_EXC_ERROR;
4006 End If;
4007
4008 --Log Message
4009 IF l_debug_mode = 'Y' THEN
4010 pa_debug.write_log (x_module => 'pa.plsql.PA_EVENT_PUB.CHECK_DELETE_EVENT_OK.begin'
4011 ,x_msg => 'Calling Check_Event_Processed'
4012 ,x_log_level => 5);
4013 END IF;
4014
4015
4016 --Validating if the event is billed or revenue distributed
4017 --Calls PA_EVENT_UTILS.CHECK_EVENT_PROCESSED
4018
4019 If PA_EVENT_UTILS.CHECK_EVENT_PROCESSED
4020 (P_event_id =>l_event_id) IN ('N','I','R')/*For Bug 7305416*/
4021 Then
4022 pa_interface_utils_pub.map_new_amg_msg
4023 ( p_old_message_code => 'PA_TK_EVENT_IN_USE'
4024 ,p_msg_attribute => 'CHANGE'
4025 ,p_resize_flag => 'N'
4026 ,p_msg_context => 'EVENT'
4027 ,p_attribute1 => l_pm_event_reference
4028 ,p_attribute2 => ''
4029 ,p_attribute3 => ''
4030 ,p_attribute4 => ''
4031 ,p_attribute5 => '');
4032 p_return_status := FND_API.G_RET_STS_ERROR;
4033 RAISE FND_API.G_EXC_ERROR;
4034 END IF;
4035
4036 FND_MSG_PUB.Count_And_Get
4037 ( p_encoded => l_encoded
4038 , p_count => p_msg_count
4039 , p_data => p_msg_data );
4040
4041 pa_debug.reset_err_stack; -- Reset error stack
4042
4043
4044 EXCEPTION
4045 WHEN FND_API.G_EXC_ERROR
4046 THEN
4047 ROLLBACK to check_delete_event_ok;
4048 p_return_status := FND_API.G_RET_STS_ERROR;
4049 FND_MSG_PUB.Count_And_Get
4050 ( p_encoded => l_encoded
4051 , p_count => p_msg_count
4052 , p_data => p_msg_data );
4053
4054 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
4055 THEN
4056 ROLLBACK to check_delete_event_ok;
4057 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4058 FND_MSG_PUB.Count_And_Get
4059 ( p_encoded => l_encoded
4060 , p_count => p_msg_count
4061 , p_data => p_msg_data );
4062
4063 WHEN OTHERS
4064 THEN
4065 ROLLBACK to check_delete_event_ok;
4066 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4067
4068 PA_EVENT_PUB.PACKAGE_NAME
4069 :='(event_reference='||p_pm_event_reference||')'||PA_EVENT_PUB.PACKAGE_NAME||'PUBLIC';
4070 PA_EVENT_PUB.PROCEDURE_NAME:=PA_EVENT_PUB.PROCEDURE_NAME||substr(sqlerrm,1,80)||'CHECK_DELETE_EVENT_OK';
4071
4072 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4073 THEN
4074 FND_MSG_PUB.add_exc_msg
4075 ( p_pkg_name => PACKAGE_NAME
4076 , p_procedure_name => PROCEDURE_NAME );
4077 PACKAGE_NAME:=NULL;
4078 PROCEDURE_NAME:=NULL;
4079 END IF;
4080
4081 FND_MSG_PUB.Count_And_Get
4082 ( p_encoded => l_encoded
4083 , p_count => p_msg_count
4084 , p_data => p_msg_data );
4085
4086
4087 END CHECK_DELETE_EVENT_OK;
4088
4089 END PA_EVENT_PUB;