[Home] [Help]
PACKAGE BODY: APPS.OKE_DELIVERABLE_BILLING_PVT
Source
1 PACKAGE BODY OKE_DELIVERABLE_BILLING_PVT AS
2 /* $Header: OKEVDVBB.pls 120.6.12010000.2 2008/09/16 05:38:31 serukull ship $ */
3
4 --
5 -- Private Global Variables
6 --
7 G_Pkg_Name VARCHAR2(30) := 'OKE_DELIVERABLE_BILLING_PVT';
8 g_module CONSTANT VARCHAR2(250) := 'oke.plsql.oke_deliverable_billing_pvt.';
9
10 --
11 -- Private Global Cursors
12 --
13 -- Cursor to get Billing Information from deliverable
14 --
15 CURSOR BillInfo ( C_Event_ID NUMBER ) IS
16 SELECT b.billing_event_id
17 , b.deliverable_id
18 , b.pa_event_id
19 , b.bill_project_id
20 , b.bill_task_id
21 , b.bill_event_type
22 , b.bill_event_date
23 , b.bill_description
24 , b.bill_unit_price
25 , b.bill_quantity
26 , d.uom_code
27 , b.bill_currency_code
28 , (b.bill_unit_price * b.bill_quantity) bill_amount
29 , b.revenue_amount
30 , b.k_header_id
31 , b.bill_line_id
32 , b.bill_chg_req_id
33 , b.bill_organization_id
34 , b.bill_item_id
35 , d.inventory_org_id
36 , b.bill_fund_ref1
37 , b.bill_fund_ref2
38 , b.bill_fund_ref3
39 , b.bill_bill_of_lading
40 , b.bill_serial_num
41 FROM oke_k_deliverables_b d, oke_k_billing_events b
42 WHERE b.billing_event_id = C_Event_ID
43 AND d.deliverable_id = b.deliverable_id;
44
45 --
46 -- Cursor to get Event Number
47 --
48 CURSOR NextEventNum ( C_Proj_ID NUMBER , C_Task_ID NUMBER ) IS
49 SELECT NVL(MAX(Event_Num) , 0) + 1
50 FROM pa_events
51 WHERE Project_ID = C_Proj_ID
52 AND Nvl(Task_ID,-1)= NVL(C_Task_ID,-1);
53
54 --
55 -- Cursor to get Contract Number and Order Number
56 --
57 CURSOR ContractNum ( C_Header_ID NUMBER ) IS
58 SELECT CH2.Contract_Number
59 , DECODE( CH2.ID , CH.ID , NULL , CH.Contract_Number )
60 FROM okc_k_headers_b ch
61 , okc_k_headers_b ch2
62 , oke_k_headers eh
63 WHERE ch.id = C_Header_ID
64 AND ch.id = eh.k_header_id
65 AND ch2.id = nvl( eh.boa_id , eh.k_header_id );
66
67 --
68 -- Cursor to get Contract Line Number
69 --
70 CURSOR LineNum ( C_Line_ID NUMBER ) IS
71 SELECT Line_Number
72 FROM okc_k_lines_b
73 WHERE id = C_Line_ID;
74
75 --
76 -- Cursor to get Change Request Number
77 --
78 CURSOR ChgReqNum ( C_ChgReq_ID NUMBER ) IS
79 SELECT Chg_Request_Num
80 FROM oke_chg_requests
81 WHERE chg_request_id = C_ChgReq_ID;
82
83 --
84 -- Private Procedures and Functions
85 --
86
87 --
88 -- Function to return event level of the current project
89 --
90 FUNCTION Event_Level
91 ( P_Project_ID IN NUMBER
92 , P_Event_ID IN NUMBER
93 ) RETURN VARCHAR2 IS
94
95 CURSOR c IS
96 SELECT DECODE(task_id , NULL , 'PROJECT' , 'TASK')
97 FROM pa_events
98 WHERE project_id = P_Project_ID
99 AND event_id <> P_Event_ID;
100 Dummy VARCHAR2(10) := NULL;
101
102 BEGIN
103 OPEN c;
104 FETCH c INTO Dummy;
105 IF ( c%notfound ) THEN
106 CLOSE c;
107 RETURN ( 'ANY' );
108 END IF;
109 CLOSE c;
110 RETURN ( Dummy );
111 END Event_Level;
112
113
114 --
115 -- Procedure to update event references in PA
116 --
117 PROCEDURE Update_Event_References
118 ( P_Event_ID IN NUMBER
119 , P_Contract_Num IN VARCHAR2
120 , P_Order_Num IN VARCHAR2
121 , P_Line_Num IN VARCHAR2
122 , P_Chg_Req_Num IN VARCHAR2
123 , P_Item_ID IN NUMBER
124 , P_Org_ID IN NUMBER
125 , P_Unit_Price IN NUMBER
126 , P_UOM IN VARCHAR2
127 , P_Bill_Quantity IN NUMBER
128 , P_Bill_Of_Lading IN VARCHAR2
129 , P_Serial_Num IN VARCHAR2
130 , P_Fund_Ref1 IN VARCHAR2
131 , P_Fund_Ref2 IN VARCHAR2
132 , P_Fund_Ref3 IN VARCHAR2
133 ) IS
134 l_api_name CONSTANT VARCHAR2(30) := ' Update_Event_References';
135 BEGIN
136
137 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
138 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Updating event reference...');
139 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Event ID = ' || P_Event_ID);
140 END IF;
141 UPDATE pa_events
142 SET quantity_billed = P_Bill_Quantity
143 , uom_code = P_UOM
144 , inventory_org_id = P_Org_ID
145 , inventory_item_id = P_Item_ID
146 , unit_price = P_Unit_Price
147 , reference1 = P_Contract_Num
148 , reference2 = P_Order_Num
149 , reference3 = P_Line_Num
150 , reference4 = P_Chg_Req_Num
151 , reference5 = P_Fund_Ref1
152 , reference6 = P_Fund_Ref2
153 , reference7 = P_Fund_Ref3
154 , reference8 = P_Bill_Of_Lading
155 , reference9 = P_Serial_Num
156 , reference10 = 'OKE'
157 WHERE event_id = P_Event_ID;
158
159 END Update_Event_References;
160
161 --
162 -- Procedure to revert billed event in PA
163 --
164 PROCEDURE Revert_Billing_Event
165 ( P_Event_ID IN NUMBER
166 , P_Event_Date IN DATE
167 ) IS
168 l_api_name CONSTANT VARCHAR2(30) := 'Revert_Billing_Event';
169 L_Event_ID NUMBER;
170 L_Event_Num NUMBER;
171 L_RowID VARCHAR2(18);
172 L_UserID NUMBER := FND_GLOBAL.User_ID;
173 L_LoginID NUMBER := FND_GLOBAL.Login_ID;
174
175 CURSOR EventInfo ( C_Event_ID NUMBER ) IS
176 SELECT project_id
177 , task_id
178 , organization_id
179 , event_num
180 , event_type
181 , description
182 , bill_amount
183 , revenue_amount
184 , quantity_billed
185 , uom_code
186 , inventory_org_id
187 , inventory_item_id
188 , unit_price
189 , reference1
190 , reference2
191 , reference3
192 , reference4
193 , reference5
194 , reference6
195 , reference7
196 , reference8
197 , reference9
198 , Bill_Trans_Currency_Code
199 , Bill_Trans_Bill_Amount
200 , Bill_Trans_rev_Amount
201 , Project_Currency_Code
202 , Project_Rate_Type
203 , Project_Rate_Date
204 , Project_Exchange_Rate
205 , Project_Inv_Rate_Date
206 , Project_Inv_Exchange_Rate
207 , Project_Bill_Amount
208 , Project_Rev_Rate_Date
209 , Project_Rev_Exchange_Rate
210 , Project_Revenue_Amount
211 , ProjFunc_Currency_Code
212 , ProjFunc_Rate_Type
213 , ProjFunc_Rate_Date
214 , ProjFunc_Exchange_Rate
215 , ProjFunc_Inv_Rate_Date
216 , ProjFunc_Inv_Exchange_Rate
217 , ProjFunc_Bill_Amount
218 , ProjFunc_Rev_Rate_Date
219 , Projfunc_Rev_Exchange_Rate
220 , ProjFunc_Revenue_Amount
221 , Funding_Rate_Type
222 , Funding_Rate_Date
223 , Funding_Exchange_Rate
224 , Invproc_Currency_Code
225 , Invproc_Rate_Type
226 , Invproc_Rate_Date
227 , Invproc_Exchange_Rate
228 , Revproc_Currency_Code
229 , Revproc_Rate_Type
230 , Revproc_Rate_Date
231 , Revproc_Exchange_Rate
232 , Inv_Gen_Rejection_Code
233 FROM pa_events
234 WHERE event_id = C_Event_ID;
235 EvInfoRec EventInfo%rowtype;
236
237 CURSOR EventID IS
238 SELECT pa_events_s.nextval
239 FROM dual;
240
241 BEGIN
242
243 OPEN EventInfo ( P_Event_ID );
244 FETCH EventInfo INTO EvInfoRec;
245 CLOSE EventInfo;
246
247 OPEN NextEventNum ( EvInfoRec.Project_ID
248 , EvInfoRec.Task_ID );
249 FETCH NextEventNum INTO L_Event_Num;
250 CLOSE NextEventNum;
251
252 OPEN EventID;
253 FETCH EventID INTO L_Event_ID;
254 CLOSE EventID;
255
256 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
257 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Reversal Event ID = ' || L_Event_ID);
258 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Reversal Event Num = ' || L_Event_Num);
259 END IF;
260
261 PA_EVENTS_PKG.Insert_Row
262 ( X_ROWID => L_RowID
263 , X_EVENT_ID => L_Event_ID
264 , X_TASK_ID => EvInfoRec.Task_ID
265 , X_EVENT_NUM => L_Event_Num
266 , X_LAST_UPDATE_DATE => sysdate
267 , X_LAST_UPDATED_BY => L_UserID
268 , X_CREATION_DATE => sysdate
269 , X_CREATED_BY => L_UserID
270 , X_LAST_UPDATE_LOGIN => L_LoginID
271 , X_EVENT_TYPE => EvInfoRec.Event_Type
272 , X_DESCRIPTION => EvInfoRec.Description
273 , X_BILL_AMOUNT => (-1) * EvInfoRec.Bill_Amount
274 , X_REVENUE_AMOUNT => (-1) * EvInfoRec.Revenue_Amount
275 , X_REVENUE_DISTRIBUTED_FLAG => 'N'
276 , X_BILL_HOLD_FLAG => 'N'
277 , X_COMPLETION_DATE => P_Event_Date
278 , X_REV_DIST_REJECTION_CODE => NULL
279 , X_ATTRIBUTE_CATEGORY => NULL
280 , X_ATTRIBUTE1 => NULL
281 , X_ATTRIBUTE2 => NULL
282 , X_ATTRIBUTE3 => NULL
283 , X_ATTRIBUTE4 => NULL
284 , X_ATTRIBUTE5 => NULL
285 , X_ATTRIBUTE6 => NULL
286 , X_ATTRIBUTE7 => NULL
287 , X_ATTRIBUTE8 => NULL
288 , X_ATTRIBUTE9 => NULL
289 , X_ATTRIBUTE10 => NULL
290 , X_PROJECT_ID => EvInfoRec.Project_ID
291 , X_ORGANIZATION_ID => EvInfoRec.Organization_ID
292 , X_BILLING_ASSIGNMENT_ID => NULL
293 , X_EVENT_NUM_REVERSED => EvInfoRec.Event_Num
294 , X_CALLING_PLACE => NULL
295 , X_CALLING_PROCESS => NULL
296 , X_Bill_Trans_Currency_Code => EvInfoRec.Bill_Trans_Currency_Code
297 , X_Bill_Trans_Bill_Amount => EvInfoRec.Bill_Trans_Bill_Amount
298 , X_Bill_Trans_rev_Amount => EvInfoRec.Bill_Trans_rev_Amount
299 , X_Project_Currency_Code => EvInfoRec.Project_Currency_Code
300 , X_Project_Rate_Type => EvInfoRec.Project_Rate_Type
301 , X_Project_Rate_Date => EvInfoRec.Project_Rate_Date
302 , X_Project_Exchange_Rate => EvInfoRec.Project_Exchange_Rate
303 , X_Project_Inv_Rate_Date => EvInfoRec.Project_Inv_Rate_Date
304 , X_Project_Inv_Exchange_Rate => EvInfoRec.Project_Inv_Exchange_Rate
305 , X_Project_Bill_Amount => EvInfoRec.Project_Bill_Amount
306 , X_Project_Rev_Rate_Date => EvInfoRec.Project_Rev_Rate_Date
307 , X_Project_Rev_Exchange_Rate => EvInfoRec.Project_Rev_Exchange_Rate
308 , X_Project_Revenue_Amount => EvInfoRec.Project_Revenue_Amount
309 , X_ProjFunc_Currency_Code => EvInfoRec.ProjFunc_Currency_Code
310 , X_ProjFunc_Rate_Type => EvInfoRec.ProjFunc_Rate_Type
311 , X_ProjFunc_Rate_Date => EvInfoRec.ProjFunc_Rate_Date
312 , X_ProjFunc_Exchange_Rate => EvInfoRec.ProjFunc_Exchange_Rate
313 , X_ProjFunc_Inv_Rate_Date => EvInfoRec.ProjFunc_Inv_Rate_Date
314 , X_ProjFunc_Inv_Exchange_Rate => EvInfoRec.ProjFunc_Inv_Exchange_Rate
315 , X_ProjFunc_Bill_Amount => EvInfoRec.ProjFunc_Bill_Amount
316 , X_ProjFunc_Rev_Rate_Date => EvInfoRec.ProjFunc_Rev_Rate_Date
317 , X_Projfunc_Rev_Exchange_Rate => EvInfoRec.Projfunc_Rev_Exchange_Rate
318 , X_ProjFunc_Revenue_Amount => EvInfoRec.ProjFunc_Revenue_Amount
319 , X_Funding_Rate_Type => EvInfoRec.Funding_Rate_Type
320 , X_Funding_Rate_Date => EvInfoRec.Funding_Rate_Date
321 , X_Funding_Exchange_Rate => EvInfoRec.Funding_Exchange_Rate
322 , X_Invproc_Currency_Code => EvInfoRec.Invproc_Currency_Code
323 , X_Invproc_Rate_Type => EvInfoRec.Invproc_Rate_Type
324 , X_Invproc_Rate_Date => EvInfoRec.Invproc_Rate_Date
325 , X_Invproc_Exchange_Rate => EvInfoRec.Invproc_Exchange_Rate
326 , X_Revproc_Currency_Code => EvInfoRec.Revproc_Currency_Code
327 , X_Revproc_Rate_Type => EvInfoRec.Revproc_Rate_Type
328 , X_Revproc_Rate_Date => EvInfoRec.Revproc_Rate_Date
329 , X_Revproc_Exchange_Rate => EvInfoRec.Revproc_Exchange_Rate
330 , X_Inv_Gen_Rejection_Code => EvInfoRec.Inv_Gen_Rejection_Code
331 , X_Product_code => 'OKE'
332 , X_Event_reference => L_Event_ID
333 );
334
335 Update_Event_References
336 ( P_Event_ID => L_Event_ID
337 , P_Contract_Num => EvInfoRec.reference1
338 , P_Order_Num => EvInfoRec.reference2
339 , P_Line_Num => EvInfoRec.reference3
340 , P_Chg_Req_Num => EvInfoRec.reference4
341 , P_Item_ID => EvInfoRec.Inventory_Item_ID
342 , P_Org_ID => EvInfoRec.Inventory_Org_ID
343 , P_Unit_Price => EvInfoRec.Unit_Price
344 , P_UOM => EvInfoRec.UOM_Code
345 , P_Bill_Quantity => (-1) * EvInfoRec.Quantity_Billed
346 , P_Bill_Of_Lading => EvInfoRec.reference8
347 , P_Serial_Num => EvInfoRec.reference9
348 , P_Fund_Ref1 => EvInfoRec.reference5
349 , P_Fund_Ref2 => EvInfoRec.reference6
350 , P_Fund_Ref3 => EvInfoRec.reference7
351 );
352
353 END Revert_Billing_Event;
354
355 --
356 -- Public Functions and Procedures
357 --
358
359 --
360 -- Name : Create_Billing_Event
361 -- Pre-reqs : None
362 -- Function : This procedure creates a billing event in PA
363 --
364 --
365 -- Parameters :
366 -- IN : P_Commit
367 -- P_Event_ID
368 -- OUT : X_Event_ID
369 -- X_Event_Num
370 -- X_Return_Status
371 -- X_Msg_Count
372 -- X_Msg_Data
373 --
374 -- Returns : None
375 --
376
377 PROCEDURE Create_Billing_Event
378 ( P_Commit IN VARCHAR2
379 , P_Event_ID IN NUMBER
380 , X_Event_ID OUT NOCOPY NUMBER
381 , X_Event_Num OUT NOCOPY NUMBER
382 , X_Return_Status OUT NOCOPY VARCHAR2
383 , X_Msg_Count OUT NOCOPY NUMBER
384 , X_Msg_Data OUT NOCOPY VARCHAR2
385 ) IS
386
387 BillInfoRec BillInfo%rowtype;
388 L_Event_ID NUMBER;
389 L_Contract_Num VARCHAR2(120);
390 L_Order_Num VARCHAR2(120);
391 L_Line_Num VARCHAR2(150);
392 L_ChgReq_Num VARCHAR2(30);
393 L_Result VARCHAR2(1);
394
395
396 CURSOR C(P_ID NUMBER) IS
397 SELECT 'X'FROM PA_EVENTS
398 WHERE EVENT_ID = P_ID;
399
400 L_Bill_Trans_Currency_Code VARCHAR2(15);
401 L_Bill_Trans_Bill_Amount NUMBER;
402 L_Bill_Trans_rev_Amount NUMBER;
403 L_Project_Currency_Code VARCHAR2(15);
404 L_Project_Rate_Type VARCHAR2(30);
405 L_Project_Rate_Date DATE;
406 L_Project_Exchange_Rate NUMBER;
407 L_Project_Inv_Rate_Date DATE;
408 L_Project_Inv_Exchange_Rate NUMBER;
409 L_Project_Bill_Amount NUMBER;
410 L_Project_Rev_Rate_Date DATE;
411 L_Project_Rev_Exchange_Rate NUMBER;
412 L_Project_Revenue_Amount NUMBER;
413 L_ProjFunc_Currency_Code VARCHAR2(15);
414 L_ProjFunc_Rate_Type VARCHAR2(30);
415 L_ProjFunc_Rate_Date DATE;
416 L_ProjFunc_Exchange_Rate NUMBER;
417 L_ProjFunc_Inv_Rate_Date DATE;
418 L_ProjFunc_Inv_Exchange_Rate NUMBER;
419 L_ProjFunc_Bill_Amount NUMBER;
420 L_ProjFunc_Rev_Rate_Date DATE;
421 L_Projfunc_Rev_Exchange_Rate NUMBER;
422 L_ProjFunc_Revenue_Amount NUMBER;
423 L_Funding_Rate_Type VARCHAR2(30);
424 L_Funding_Rate_Date DATE;
425 L_Funding_Exchange_Rate NUMBER;
426 L_Invproc_Currency_Code VARCHAR2(15);
427 L_Invproc_Rate_Type VARCHAR2(30);
428 L_Invproc_Rate_Date DATE;
429 L_Invproc_Exchange_Rate NUMBER;
430 L_Revproc_Currency_Code VARCHAR2(15);
431 L_Revproc_Rate_Type VARCHAR2(30);
432 L_Revproc_Rate_Date DATE;
433 L_Revproc_Exchange_Rate NUMBER;
434 L_Inv_Gen_Rejection_Code VARCHAR2(30);
435
436
437 l_api_name CONSTANT VARCHAR2(30) := 'Create_Billing_Event';
438 BEGIN
439 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
440 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Creating Billing Event ...');
441 END IF;
442 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
443
444 --
445 -- Standard Start of API savepoint
446 --
447 SAVEPOINT create_billing_event;
448
449 --
450 -- First fetch billing information from deliverable
451 --
452 OPEN BillInfo ( P_Event_ID );
453 FETCH BillInfo INTO BillInfoRec;
454 CLOSE BillInfo;
455
456 --
457 -- Next fetch various reference information
458 --
459 OPEN ContractNum ( BillInfoRec.K_Header_ID );
460 FETCH ContractNum INTO L_Contract_Num , L_Order_Num;
461 CLOSE ContractNum;
462
463 OPEN LineNum ( BillInfoRec.Bill_Line_ID );
464 FETCH LineNum INTO L_Line_Num;
465 CLOSE LineNum;
466
467 OPEN ChgReqNum ( BillInfoRec.Bill_Chg_Req_ID );
468 FETCH ChgReqNum INTO L_ChgReq_Num;
469 CLOSE ChgReqNum;
470
471 --
472 -- Validations
473 --
474 -- 1. Make sure billing event level is consistent with previous
475 -- events
476 --
477 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
478 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Check Previous Event Level');
479 END IF;
480
481 IF ( Event_Level( BillInfoRec.Bill_Project_ID
482 , BillInfoRec.Pa_Event_ID ) = 'PROJECT'
483 AND BillInfoRec.Bill_Task_ID IS NOT NULL ) THEN
484 FND_MESSAGE.set_name('PA' , 'PA_PR_EPR_EVENTS_AT_PROJ_LVL');
485 FND_MSG_PUB.add;
486 X_Return_Status := FND_API.G_RET_STS_ERROR;
487 END IF;
488
489 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
490 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Event Type = ' || BillInfoRec.Bill_Event_Type);
491 END IF;
492
493 IF ( BillInfoRec.Bill_Event_Type IS NULL ) THEN
494 fnd_message.set_name('OKE' , 'OKE_API_MISSING_VALUE');
495 fnd_message.set_token('VALUE' , 'BILL_EVENT_TYPE');
496 FND_MSG_PUB.add;
497 X_Return_Status := FND_API.G_RET_STS_ERROR;
498 END IF;
499
500 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
501 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Description = ' || BillInfoRec.Bill_Description);
502 END IF;
503
504 IF ( BillInfoRec.Bill_Description IS NULL ) THEN
505 fnd_message.set_name('OKE' , 'OKE_API_MISSING_VALUE');
506 fnd_message.set_token('VALUE' , 'BILL_DESCRIPTION');
507 FND_MSG_PUB.add;
508 X_Return_Status := FND_API.G_RET_STS_ERROR;
509 END IF;
510
511 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
512 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Bill Amount = ' || BillInfoRec.Bill_Amount);
513 END IF;
514
515 IF ( BillInfoRec.Bill_Amount IS NULL ) THEN
516 fnd_message.set_name('OKE' , 'OKE_API_MISSING_VALUE');
517 fnd_message.set_token('VALUE' , 'BILL_AMOUNT');
518 FND_MSG_PUB.add;
519 X_Return_Status := FND_API.G_RET_STS_ERROR;
520 END IF;
521
522 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
523 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Revenue Amount = ' || BillInfoRec.Revenue_Amount);
524 END IF;
525
526 IF ( BillInfoRec.Revenue_Amount IS NULL ) THEN
527 fnd_message.set_name('OKE' , 'OKE_API_MISSING_VALUE');
528 fnd_message.set_token('VALUE' , 'REVENUE_AMOUNT');
529 FND_MSG_PUB.add;
530 X_Return_Status := FND_API.G_RET_STS_ERROR;
531 END IF;
532
533 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
534 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Organization ID = ' || BillInfoRec.Bill_Organization_ID);
535 END IF;
536 IF ( BillInfoRec.Bill_Organization_ID IS NULL ) THEN
537 fnd_message.set_name('OKE' , 'OKE_API_MISSING_VALUE');
538 fnd_message.set_token('VALUE' , 'BILL_ORGANIZATION_ID');
539 FND_MSG_PUB.add;
540 X_Return_Status := FND_API.G_RET_STS_ERROR;
541 END IF;
542
543 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
544 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Project ID = ' || BillInfoRec.Bill_Project_ID);
545 END IF;
546
547 IF ( BillInfoRec.Bill_Project_ID IS NULL ) THEN
548 fnd_message.set_name('OKE' , 'OKE_API_MISSING_VALUE');
549 fnd_message.set_token('VALUE' , 'BILL_PROJECT_ID');
550 FND_MSG_PUB.add;
551 X_Return_Status := FND_API.G_RET_STS_ERROR;
552 END IF;
553
554 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
555 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Task ID = ' || BillInfoRec.Bill_Task_ID);
556 END IF;
557 IF ( X_Return_Status <> FND_API.G_RET_STS_SUCCESS ) THEN
558 RAISE FND_API.G_EXC_ERROR;
559 END IF;
560
561 --
562 -- Addtional check to ensure the record hasn't been deleted from PA
563 --
564 OPEN C(BillInfoRec.Pa_Event_ID);
565 FETCH C INTO L_Result;
566 CLOSE C;
567
568 -- Get Mc columns
569 Populate_Mc_Columns ( P_Event_ID => P_Event_ID
570 , X_Bill_Trans_Currency_Code => L_Bill_Trans_Currency_Code
571 , X_Bill_Trans_Bill_Amount => L_Bill_Trans_Bill_Amount
572 , X_Bill_Trans_rev_Amount => L_Bill_Trans_rev_Amount
573 , X_Project_Currency_Code => L_Project_Currency_Code
574 , X_Project_Rate_Type => L_Project_Rate_Type
575 , X_Project_Rate_Date => L_Project_Rate_Date
576 , X_Project_Exchange_Rate => L_Project_Exchange_Rate
577 , X_Project_Inv_Rate_Date => L_Project_Inv_Rate_Date
578 , X_Project_Inv_Exchange_Rate => L_Project_Inv_Exchange_Rate
579 , X_Project_Bill_Amount => L_Project_Bill_Amount
580 , X_Project_Rev_Rate_Date => L_Project_Rev_Rate_Date
581 , X_Project_Rev_Exchange_Rate => L_Project_Rev_Exchange_Rate
582 , X_Project_Revenue_Amount => L_Project_Revenue_Amount
583 , X_ProjFunc_Currency_Code => L_ProjFunc_Currency_Code
584 , X_ProjFunc_Rate_Type => L_ProjFunc_Rate_Type
585 , X_ProjFunc_Rate_Date => L_ProjFunc_Rate_Date
586 , X_ProjFunc_Exchange_Rate => L_ProjFunc_Exchange_Rate
587 , X_ProjFunc_Inv_Rate_Date => L_ProjFunc_Inv_Rate_Date
588 , X_ProjFunc_Inv_Exchange_Rate => L_ProjFunc_Inv_Exchange_Rate
589 , X_ProjFunc_Bill_Amount => L_ProjFunc_Bill_Amount
590 , X_ProjFunc_Rev_Rate_Date => L_ProjFunc_Rev_Rate_Date
591 , X_Projfunc_Rev_Exchange_Rate => L_Projfunc_Rev_Exchange_Rate
592 , X_ProjFunc_Revenue_Amount => L_ProjFunc_Revenue_Amount
593 , X_Funding_Rate_Type => L_Funding_Rate_Type
594 , X_Funding_Rate_Date => L_Funding_Rate_Date
595 , X_Funding_Exchange_Rate => L_Funding_Exchange_Rate
596 , X_Invproc_Currency_Code => L_Invproc_Currency_Code
597 , X_Invproc_Rate_Type => L_Invproc_Rate_Type
598 , X_Invproc_Rate_Date => L_Invproc_Rate_Date
599 , X_Invproc_Exchange_Rate => L_Invproc_Exchange_Rate
600 , X_Revproc_Currency_Code => L_Revproc_Currency_Code
601 , X_Revproc_Rate_Type => L_Revproc_Rate_Type
602 , X_Revproc_Rate_Date => L_Revproc_Rate_Date
603 , X_Revproc_Exchange_Rate => L_Revproc_Exchange_Rate
604 , X_Inv_Gen_Rejection_Code => L_Inv_Gen_Rejection_Code );
605
606
607 IF ( BillInfoRec.Pa_Event_ID > 0 AND L_Result = 'X') THEN
608
609 --
610 -- PA Event has previously been created
611 --
612 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
613 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Event Previously Created ...');
614 END IF;
615
616 Update_Billing_Event
617 ( P_Commit => FND_API.G_FALSE
618 , P_Deliverable_ID => BillInfoRec.Deliverable_ID
619 , P_Event_ID => BillInfoRec.Pa_Event_ID
620 , P_Event_Type => BillInfoRec.Bill_Event_Type
621 , P_Event_Date => BillInfoRec.Bill_Event_Date
622 , P_Project_ID => BillInfoRec.Bill_Project_ID
623 , P_Task_ID => BillInfoRec.Bill_Task_ID
624 , P_Organization_ID => BillInfoRec.Bill_Organization_ID
625 , P_Description => BillInfoRec.Bill_Description
626 , P_Unit_Price => BillInfoRec.Bill_Unit_Price
627 , P_Bill_Quantity => BillInfoRec.Bill_Quantity
628 , P_UOM_Code => BillInfoRec.UOM_Code
629 , P_Bill_Amount => BillInfoRec.Bill_Amount
630 , P_Revenue_Amount => BillInfoRec.Revenue_Amount
631 , P_Item_ID => BillInfoRec.Bill_Item_ID
632 , P_Inventory_Org_ID => BillInfoRec.Inventory_Org_ID
633 , P_Contract_Num => L_Contract_Num
634 , P_Order_Num => L_Order_Num
635 , P_Line_Num => L_Line_Num
636 , P_Chg_Request_Num => L_ChgReq_Num
637 , P_Bill_Of_Lading => BillInfoRec.Bill_Bill_Of_Lading
638 , P_Serial_Num => BillInfoRec.Bill_Serial_Num
639 , P_Fund_Ref1 => BillInfoRec.Bill_Fund_Ref1
640 , P_Fund_Ref2 => BillInfoRec.Bill_Fund_Ref2
641 , P_Fund_Ref3 => BillInfoRec.Bill_Fund_Ref3
642 , P_Bill_Trans_Currency_Code => L_Bill_Trans_Currency_Code
643 , P_Bill_Trans_Bill_Amount => L_Bill_Trans_Bill_Amount
644 , P_Bill_Trans_rev_Amount => L_Bill_Trans_rev_Amount
645 , P_Project_Currency_Code => L_Project_Currency_Code
646 , P_Project_Rate_Type => L_Project_Rate_Type
647 , P_Project_Rate_Date => L_Project_Rate_Date
648 , P_Project_Exchange_Rate => L_Project_Exchange_Rate
649 , P_Project_Inv_Rate_Date => L_Project_Inv_Rate_Date
650 , P_Project_Inv_Exchange_Rate => L_Project_Inv_Exchange_Rate
651 , P_Project_Bill_Amount => L_Project_Bill_Amount
652 , P_Project_Rev_Rate_Date => L_Project_Rev_Rate_Date
653 , P_Project_Rev_Exchange_Rate => L_Project_Rev_Exchange_Rate
654 , P_Project_Revenue_Amount => L_Project_Revenue_Amount
655 , P_ProjFunc_Currency_Code => L_ProjFunc_Currency_Code
656 , P_ProjFunc_Rate_Type => L_ProjFunc_Rate_Type
657 , P_ProjFunc_Rate_Date => L_ProjFunc_Rate_Date
658 , P_ProjFunc_Exchange_Rate => L_ProjFunc_Exchange_Rate
659 , P_ProjFunc_Inv_Rate_Date => L_ProjFunc_Inv_Rate_Date
660 , P_ProjFunc_Inv_Exchange_Rate => L_ProjFunc_Inv_Exchange_Rate
661 , P_ProjFunc_Bill_Amount => L_ProjFunc_Bill_Amount
662 , P_ProjFunc_Rev_Rate_Date => L_ProjFunc_Rev_Rate_Date
663 , P_Projfunc_Rev_Exchange_Rate => L_Projfunc_Rev_Exchange_Rate
664 , P_ProjFunc_Revenue_Amount => L_ProjFunc_Revenue_Amount
665 , P_Funding_Rate_Type => L_Funding_Rate_Type
666 , P_Funding_Rate_Date => L_Funding_Rate_Date
667 , P_Funding_Exchange_Rate => L_Funding_Exchange_Rate
668 , P_Invproc_Currency_Code => L_Invproc_Currency_Code
669 , P_Invproc_Rate_Type => L_Invproc_Rate_Type
670 , P_Invproc_Rate_Date => L_Invproc_Rate_Date
671 , P_Invproc_Exchange_Rate => L_Invproc_Exchange_Rate
672 , P_Revproc_Currency_Code => L_Revproc_Currency_Code
673 , P_Revproc_Rate_Type => L_Revproc_Rate_Type
674 , P_Revproc_Rate_Date => L_Revproc_Rate_Date
675 , P_Revproc_Exchange_Rate => L_Revproc_Exchange_Rate
676 , P_Inv_Gen_Rejection_Code => L_Inv_Gen_Rejection_Code
677 , X_Return_Status => X_Return_Status
678 , X_Msg_Count => X_Msg_Count
679 , X_Msg_Data => X_Msg_Data
680 );
681
682 IF ( X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
683 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
684 ELSIF ( X_return_status = FND_API.G_RET_STS_ERROR ) THEN
685 RAISE FND_API.G_EXC_ERROR;
686 END IF;
687
688 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
689 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Updating deliverable with event info ...');
690 END IF;
691
692 UPDATE oke_k_billing_events
693 SET initiated_flag = 'Y'
694 WHERE billing_event_id = P_Event_ID;
695
696
697
698 ELSE
699 --
700 -- PA Event has not yet been created
701 --
702 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
703 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Event Not Previously Created ...');
704 END IF;
705 Create_Billing_Event
706 ( P_Commit => FND_API.G_FALSE
707 , P_Event_Type => BillInfoRec.Bill_Event_Type
708 , P_Event_Date => BillInfoRec.Bill_Event_Date
709 , P_Project_ID => BillInfoRec.Bill_Project_ID
710 , P_Task_ID => BillInfoRec.Bill_Task_ID
711 , P_Organization_ID => BillInfoRec.Bill_Organization_ID
712 , P_Description => BillInfoRec.Bill_Description
713 , P_Unit_Price => BillInfoRec.Bill_Unit_Price
714 , P_Bill_Quantity => BillInfoRec.Bill_Quantity
715 , P_UOM_Code => BillInfoRec.UOM_Code
716 , P_Bill_Amount => BillInfoRec.Bill_Amount
717 , P_Revenue_Amount => BillInfoRec.Revenue_Amount
718 , P_Item_ID => BillInfoRec.Bill_Item_ID
719 , P_Inventory_Org_ID => BillInfoRec.Inventory_Org_ID
720 , P_Contract_Num => L_Contract_Num
721 , P_Order_Num => L_Order_Num
722 , P_Line_Num => L_Line_Num
723 , P_Chg_Request_Num => L_ChgReq_Num
724 , P_Bill_Of_Lading => BillInfoRec.Bill_Bill_Of_Lading
725 , P_Serial_Num => BillInfoRec.Bill_Serial_Num
726 , P_Fund_Ref1 => BillInfoRec.Bill_Fund_Ref1
727 , P_Fund_Ref2 => BillInfoRec.Bill_Fund_Ref2
728 , P_Fund_Ref3 => BillInfoRec.Bill_Fund_Ref3
729 , P_Event_Num_Reversed => NULL
730 , P_Bill_Trans_Currency_Code => L_Bill_Trans_Currency_Code
731 , P_Bill_Trans_Bill_Amount => L_Bill_Trans_Bill_Amount
732 , P_Bill_Trans_rev_Amount => L_Bill_Trans_rev_Amount
733 , P_Project_Currency_Code => L_Project_Currency_Code
734 , P_Project_Rate_Type => L_Project_Rate_Type
735 , P_Project_Rate_Date => L_Project_Rate_Date
736 , P_Project_Exchange_Rate => L_Project_Exchange_Rate
737 , P_Project_Inv_Rate_Date => L_Project_Inv_Rate_Date
738 , P_Project_Inv_Exchange_Rate => L_Project_Inv_Exchange_Rate
739 , P_Project_Bill_Amount => L_Project_Bill_Amount
740 , P_Project_Rev_Rate_Date => L_Project_Rev_Rate_Date
741 , P_Project_Rev_Exchange_Rate => L_Project_Rev_Exchange_Rate
742 , P_Project_Revenue_Amount => L_Project_Revenue_Amount
743 , P_ProjFunc_Currency_Code => L_ProjFunc_Currency_Code
744 , P_ProjFunc_Rate_Type => L_ProjFunc_Rate_Type
745 , P_ProjFunc_Rate_Date => L_ProjFunc_Rate_Date
746 , P_ProjFunc_Exchange_Rate => L_ProjFunc_Exchange_Rate
747 , P_ProjFunc_Inv_Rate_Date => L_ProjFunc_Inv_Rate_Date
748 , P_ProjFunc_Inv_Exchange_Rate => L_ProjFunc_Inv_Exchange_Rate
749 , P_ProjFunc_Bill_Amount => L_ProjFunc_Bill_Amount
750 , P_ProjFunc_Rev_Rate_Date => L_ProjFunc_Rev_Rate_Date
751 , P_Projfunc_Rev_Exchange_Rate => L_Projfunc_Rev_Exchange_Rate
752 , P_ProjFunc_Revenue_Amount => L_ProjFunc_Revenue_Amount
753 , P_Funding_Rate_Type => L_Funding_Rate_Type
754 , P_Funding_Rate_Date => L_Funding_Rate_Date
755 , P_Funding_Exchange_Rate => L_Funding_Exchange_Rate
756 , P_Invproc_Currency_Code => L_Invproc_Currency_Code
757 , P_Invproc_Rate_Type => L_Invproc_Rate_Type
758 , P_Invproc_Rate_Date => L_Invproc_Rate_Date
759 , P_Invproc_Exchange_Rate => L_Invproc_Exchange_Rate
760 , P_Revproc_Currency_Code => L_Revproc_Currency_Code
761 , P_Revproc_Rate_Type => L_Revproc_Rate_Type
762 , P_Revproc_Rate_Date => L_Revproc_Rate_Date
763 , P_Revproc_Exchange_Rate => L_Revproc_Exchange_Rate
764 , P_Inv_Gen_Rejection_Code => L_Inv_Gen_Rejection_Code
765 , X_Event_ID => L_Event_ID
766 , X_Event_Num => X_Event_Num
767 , X_Return_Status => X_Return_Status
768 , X_Msg_Count => X_Msg_Count
769 , X_Msg_Data => X_Msg_Data
770 );
771
772 IF ( X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
774 ELSIF ( X_return_status = FND_API.G_RET_STS_ERROR ) THEN
775 RAISE FND_API.G_EXC_ERROR;
776 END IF;
777
778 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
779 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Updating deliverable with event info ...');
780 END IF;
781
782 UPDATE oke_k_billing_events
783 SET pa_event_id = L_Event_ID
784 ,initiated_flag = 'Y'
785 WHERE billing_event_id = P_Event_ID;
786
787 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
788 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Successful completion ...');
789 END IF;
790 X_Event_ID := L_Event_ID;
791
792 END IF;
793
794 --
795 -- Standard commit check
796 --
797 IF FND_API.To_Boolean( p_commit ) THEN
798 COMMIT WORK;
799 END IF;
800
801 --
802 -- Standard call to get message count and if count is 1, get message
803 -- info
804 --
805 FND_MSG_PUB.Count_And_Get( P_Count => X_Msg_Count
806 , P_Data => X_Msg_Data );
807
808 EXCEPTION
809 WHEN FND_API.G_EXC_ERROR THEN
810 ROLLBACK TO create_billing_event;
811 X_Return_Status := FND_API.G_RET_STS_ERROR;
812 FND_MSG_PUB.Count_And_Get( P_Count => X_Msg_Count
813 , P_Data => X_Msg_Data );
814
815 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
816 ROLLBACK TO create_billing_event;
817 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
818 FND_MSG_PUB.Count_And_Get( P_Count => X_Msg_Count
819 , P_Data => X_Msg_Data );
820
821 WHEN OTHERS THEN
822 ROLLBACK TO create_billing_event;
823 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
824 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
825 FND_MSG_PUB.add_exc_msg
826 ( p_pkg_name => G_Pkg_Name
827 , p_procedure_name => 'CREATE_BILLING_EVENT' );
828 END IF;
829 FND_MSG_PUB.Count_And_Get( P_Count => X_Msg_Count
830 , P_Data => X_Msg_Data );
831 END Create_Billing_Event;
832
833
834 PROCEDURE Create_Billing_Event
835 ( P_Commit IN VARCHAR2
836 , P_Event_Type IN VARCHAR2
837 , P_Event_Date IN DATE
838 , P_Project_ID IN NUMBER
839 , P_Task_ID IN NUMBER
840 , P_Organization_ID IN NUMBER
841 , P_Description IN VARCHAR2
842 , P_Unit_Price IN NUMBER
843 , P_Bill_Quantity IN NUMBER
844 , P_UOM_Code IN VARCHAR2
845 , P_Bill_Amount IN NUMBER
846 , P_Revenue_Amount IN NUMBER
847 , P_Item_ID IN NUMBER
848 , P_Inventory_Org_ID IN NUMBER
849 , P_Contract_Num IN VARCHAR2
850 , P_Order_Num IN VARCHAR2
851 , P_Line_Num IN VARCHAR2
852 , P_Chg_Request_Num IN VARCHAR2
853 , P_Bill_Of_Lading IN VARCHAR2
854 , P_Serial_Num IN VARCHAR2
855 , P_Fund_Ref1 IN VARCHAR2
856 , P_Fund_Ref2 IN VARCHAR2
857 , P_Fund_Ref3 IN VARCHAR2
858 , P_Event_Num_Reversed IN NUMBER
859 , P_Bill_Trans_Currency_Code IN VARCHAR2 DEFAULT NULL
860 , P_Bill_Trans_Bill_Amount IN NUMBER DEFAULT NULL
861 , P_Bill_Trans_rev_Amount IN NUMBER DEFAULT NULL
862 , P_Project_Currency_Code IN VARCHAR2 DEFAULT NULL
863 , P_Project_Rate_Type IN VARCHAR2 DEFAULT NULL
864 , P_Project_Rate_Date IN DATE DEFAULT NULL
865 , P_Project_Exchange_Rate IN NUMBER DEFAULT NULL
866 , P_Project_Inv_Rate_Date IN DATE DEFAULT NULL
867 , P_Project_Inv_Exchange_Rate IN NUMBER DEFAULT NULL
868 , P_Project_Bill_Amount IN NUMBER DEFAULT NULL
869 , P_Project_Rev_Rate_Date IN DATE DEFAULT NULL
870 , P_Project_Rev_Exchange_Rate IN NUMBER DEFAULT NULL
871 , P_Project_Revenue_Amount IN NUMBER DEFAULT NULL
872 , P_ProjFunc_Currency_Code IN VARCHAR2 DEFAULT NULL
873 , P_ProjFunc_Rate_Type IN VARCHAR2 DEFAULT NULL
874 , P_ProjFunc_Rate_Date IN DATE DEFAULT NULL
875 , P_ProjFunc_Exchange_Rate IN NUMBER DEFAULT NULL
876 , P_ProjFunc_Inv_Rate_Date IN DATE DEFAULT NULL
877 , P_ProjFunc_Inv_Exchange_Rate IN NUMBER DEFAULT NULL
878 , P_ProjFunc_Bill_Amount IN NUMBER DEFAULT NULL
879 , P_ProjFunc_Rev_Rate_Date IN DATE DEFAULT NULL
880 , P_Projfunc_Rev_Exchange_Rate IN NUMBER DEFAULT NULL
881 , P_ProjFunc_Revenue_Amount IN NUMBER DEFAULT NULL
882 , P_Funding_Rate_Type IN VARCHAR2 DEFAULT NULL
883 , P_Funding_Rate_Date IN DATE DEFAULT NULL
884 , P_Funding_Exchange_Rate IN NUMBER DEFAULT NULL
885 , P_Invproc_Currency_Code IN VARCHAR2 DEFAULT NULL
886 , P_Invproc_Rate_Type IN VARCHAR2 DEFAULT NULL
887 , P_Invproc_Rate_Date IN DATE DEFAULT NULL
888 , P_Invproc_Exchange_Rate IN NUMBER DEFAULT NULL
889 , P_Revproc_Currency_Code IN VARCHAR2 DEFAULT NULL
890 , P_Revproc_Rate_Type IN VARCHAR2 DEFAULT NULL
891 , P_Revproc_Rate_Date IN DATE DEFAULT NULL
892 , P_Revproc_Exchange_Rate IN NUMBER DEFAULT NULL
893 , P_Inv_Gen_Rejection_Code IN VARCHAR2 DEFAULT NULL
894 , X_Event_ID OUT NOCOPY NUMBER
895 , X_Event_Num OUT NOCOPY NUMBER
896 , X_Return_Status OUT NOCOPY VARCHAR2
897 , X_Msg_Count OUT NOCOPY NUMBER
898 , X_Msg_Data OUT NOCOPY VARCHAR2
899 ) IS
900
901 L_RowID VARCHAR2(18);
902 L_UserID NUMBER := FND_GLOBAL.User_ID;
903 L_LoginID NUMBER := FND_GLOBAL.Login_ID;
904 l_api_name CONSTANT VARCHAR2(30) := 'Update_Billing_Event';
905 CURSOR EventID IS
906 SELECT pa_events_s.nextval
907 FROM dual;
908
909 BEGIN
910
911 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
912
913 --
914 -- Standard Start of API savepoint
915 --
916 SAVEPOINT create_billing_event_pvt;
917
918 --
919 -- Getting Event Num if not already specified
920 --
921 IF ( X_Event_Num IS NULL ) THEN
922 OPEN NextEventNum ( P_Project_ID
923 , P_Task_ID );
924 FETCH NextEventNum INTO X_Event_Num;
925 CLOSE NextEventNum;
926 END IF;
927
928 OPEN EventID;
929 FETCH EventID INTO X_Event_ID;
930 CLOSE EventID;
931
932 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
933 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Creating PA billing event ...');
934 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Event ID = ' || X_Event_ID);
935 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Event Num = ' || X_Event_Num);
936 END IF;
937
938 PA_EVENTS_PKG.Insert_Row
939 ( X_ROWID => L_RowID
940 , X_EVENT_ID => X_Event_ID
941 , X_TASK_ID => P_Task_ID
942 , X_EVENT_NUM => X_Event_Num
943 , X_LAST_UPDATE_DATE => sysdate
944 , X_LAST_UPDATED_BY => L_UserID
945 , X_CREATION_DATE => sysdate
946 , X_CREATED_BY => L_UserID
947 , X_LAST_UPDATE_LOGIN => L_LoginID
948 , X_EVENT_TYPE => P_Event_Type
949 , X_DESCRIPTION => P_Description
950 , X_BILL_AMOUNT => P_Bill_Amount
951 , X_REVENUE_AMOUNT => P_Revenue_Amount
952 , X_REVENUE_DISTRIBUTED_FLAG => 'N'
953 , X_BILL_HOLD_FLAG => 'N'
954 , X_COMPLETION_DATE => P_Event_Date
955 , X_REV_DIST_REJECTION_CODE => NULL
956 , X_ATTRIBUTE_CATEGORY => NULL
957 , X_ATTRIBUTE1 => NULL
958 , X_ATTRIBUTE2 => NULL
959 , X_ATTRIBUTE3 => NULL
960 , X_ATTRIBUTE4 => NULL
961 , X_ATTRIBUTE5 => NULL
962 , X_ATTRIBUTE6 => NULL
963 , X_ATTRIBUTE7 => NULL
964 , X_ATTRIBUTE8 => NULL
965 , X_ATTRIBUTE9 => NULL
966 , X_ATTRIBUTE10 => NULL
967 , X_PROJECT_ID => P_Project_ID
968 , X_ORGANIZATION_ID => P_Organization_ID
969 , X_BILLING_ASSIGNMENT_ID => NULL
970 , X_EVENT_NUM_REVERSED => P_Event_Num_Reversed
971 , X_CALLING_PLACE => NULL
972 , X_CALLING_PROCESS => NULL
973 , X_Bill_Trans_Currency_Code => P_Bill_Trans_Currency_Code
974 , X_Bill_Trans_Bill_Amount => P_Bill_Trans_Bill_Amount
975 , X_Bill_Trans_rev_Amount => P_Bill_Trans_rev_Amount
976 , X_Project_Currency_Code => P_Project_Currency_Code
977 , X_Project_Rate_Type => P_Project_Rate_Type
978 , X_Project_Rate_Date => P_Project_Rate_Date
979 , X_Project_Exchange_Rate => P_Project_Exchange_Rate
980 , X_Project_Inv_Rate_Date => P_Project_Inv_Rate_Date
981 , X_Project_Inv_Exchange_Rate => P_Project_Inv_Exchange_Rate
982 , X_Project_Bill_Amount => P_Project_Bill_Amount
983 , X_Project_Rev_Rate_Date => P_Project_Rev_Rate_Date
984 , X_Project_Rev_Exchange_Rate => P_Project_Rev_Exchange_Rate
985 , X_Project_Revenue_Amount => P_Project_Revenue_Amount
986 , X_ProjFunc_Currency_Code => P_ProjFunc_Currency_Code
987 , X_ProjFunc_Rate_Type => P_ProjFunc_Rate_Type
988 , X_ProjFunc_Rate_Date => P_ProjFunc_Rate_Date
989 , X_ProjFunc_Exchange_Rate => P_ProjFunc_Exchange_Rate
990 , X_ProjFunc_Inv_Rate_Date => P_ProjFunc_Inv_Rate_Date
991 , X_ProjFunc_Inv_Exchange_Rate => P_ProjFunc_Inv_Exchange_Rate
992 , X_ProjFunc_Bill_Amount => P_ProjFunc_Bill_Amount
993 , X_ProjFunc_Rev_Rate_Date => P_ProjFunc_Rev_Rate_Date
994 , X_Projfunc_Rev_Exchange_Rate => P_Projfunc_Rev_Exchange_Rate
995 , X_ProjFunc_Revenue_Amount => P_ProjFunc_Revenue_Amount
996 , X_Funding_Rate_Type => P_Funding_Rate_Type
997 , X_Funding_Rate_Date => P_Funding_Rate_Date
998 , X_Funding_Exchange_Rate => P_Funding_Exchange_Rate
999 , X_Invproc_Currency_Code => P_Invproc_Currency_Code
1000 , X_Invproc_Rate_Type => P_Invproc_Rate_Type
1001 , X_Invproc_Rate_Date => P_Invproc_Rate_Date
1002 , X_Invproc_Exchange_Rate => P_Invproc_Exchange_Rate
1003 , X_Revproc_Currency_Code => P_Revproc_Currency_Code
1004 , X_Revproc_Rate_Type => P_Revproc_Rate_Type
1005 , X_Revproc_Rate_Date => P_Revproc_Rate_Date
1006 , X_Revproc_Exchange_Rate => P_Revproc_Exchange_Rate
1007 , X_Inv_Gen_Rejection_Code => P_Inv_Gen_Rejection_Code
1008 , X_Product_Code => 'OKE'
1009 , X_event_reference => X_Event_Id
1010 );
1011
1012 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1013 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Updating additional billing info ...');
1014 END IF;
1015 Update_Event_References
1016 ( P_Event_ID => X_Event_ID
1017 , P_Contract_Num => P_Contract_Num
1018 , P_Order_Num => P_Order_Num
1019 , P_Line_Num => P_Line_Num
1020 , P_Chg_Req_Num => P_Chg_Request_Num
1021 , P_Item_ID => P_Item_ID
1022 , P_Org_ID => P_Inventory_Org_ID
1023 , P_Unit_Price => P_Unit_Price
1024 , P_UOM => P_UOM_Code
1025 , P_Bill_Quantity => P_Bill_Quantity
1026 , P_Bill_Of_Lading => P_Bill_Of_Lading
1027 , P_Serial_Num => P_Serial_Num
1028 , P_Fund_Ref1 => P_Fund_Ref1
1029 , P_Fund_Ref2 => P_Fund_Ref2
1030 , P_Fund_Ref3 => P_Fund_Ref3
1031 );
1032
1033 --
1034 -- Standard commit check
1035 --
1036 IF FND_API.To_Boolean( p_commit ) THEN
1037 COMMIT WORK;
1038 END IF;
1039
1040 --
1041 -- Standard call to get message count and if count is 1, get message
1042 -- info
1043 --
1044 FND_MSG_PUB.Count_And_Get( P_Count => X_Msg_Count
1045 , P_Data => X_Msg_Data );
1046
1047 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1048 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Billing event created ...');
1049 END IF;
1050
1051 EXCEPTION
1052 WHEN FND_API.G_EXC_ERROR THEN
1053 ROLLBACK TO create_billing_event_pvt;
1054 X_Return_Status := FND_API.G_RET_STS_ERROR;
1055 FND_MSG_PUB.Count_And_Get( P_Count => X_Msg_Count
1056 , P_Data => X_Msg_Data );
1057
1058 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1059 ROLLBACK TO create_billing_event_pvt;
1060 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1061 FND_MSG_PUB.Count_And_Get( P_Count => X_Msg_Count
1062 , P_Data => X_Msg_Data );
1063
1064 WHEN OTHERS THEN
1065 ROLLBACK TO create_billing_event_pvt;
1066 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1067 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1068 FND_MSG_PUB.add_exc_msg
1069 ( p_pkg_name => G_Pkg_Name
1070 , p_procedure_name => 'CREATE_BILLING_EVENT' );
1071 END IF;
1072 FND_MSG_PUB.Count_And_Get( P_Count => X_Msg_Count
1073 , P_Data => X_Msg_Data );
1074 END Create_Billing_Event;
1075
1076
1077 PROCEDURE Update_Billing_Event
1078 ( P_Commit IN VARCHAR2
1079 , P_Deliverable_ID IN NUMBER
1080 , P_Event_ID IN NUMBER
1081 , P_Event_Type IN VARCHAR2
1082 , P_Event_Date IN DATE
1083 , P_Project_ID IN NUMBER
1084 , P_Task_ID IN NUMBER
1085 , P_Organization_ID IN NUMBER
1086 , P_Description IN VARCHAR2
1087 , P_Unit_Price IN NUMBER
1088 , P_Bill_Quantity IN NUMBER
1089 , P_UOM_Code IN VARCHAR2
1090 , P_Bill_Amount IN NUMBER
1091 , P_Revenue_Amount IN NUMBER
1092 , P_Item_ID IN NUMBER
1093 , P_Inventory_Org_ID IN NUMBER
1094 , P_Contract_Num IN VARCHAR2
1095 , P_Order_Num IN VARCHAR2
1096 , P_Line_Num IN VARCHAR2
1097 , P_Chg_Request_Num IN VARCHAR2
1098 , P_Bill_Of_Lading IN VARCHAR2
1099 , P_Serial_Num IN VARCHAR2
1100 , P_Fund_Ref1 IN VARCHAR2
1101 , P_Fund_Ref2 IN VARCHAR2
1102 , P_Fund_Ref3 IN VARCHAR2
1103 , P_Bill_Trans_Currency_Code IN VARCHAR2 DEFAULT NULL
1104 , P_Bill_Trans_Bill_Amount IN NUMBER DEFAULT NULL
1105 , P_Bill_Trans_rev_Amount IN NUMBER DEFAULT NULL
1106 , P_Project_Currency_Code IN VARCHAR2 DEFAULT NULL
1107 , P_Project_Rate_Type IN VARCHAR2 DEFAULT NULL
1108 , P_Project_Rate_Date IN DATE DEFAULT NULL
1109 , P_Project_Exchange_Rate IN NUMBER DEFAULT NULL
1110 , P_Project_Inv_Rate_Date IN DATE DEFAULT NULL
1111 , P_Project_Inv_Exchange_Rate IN NUMBER DEFAULT NULL
1112 , P_Project_Bill_Amount IN NUMBER DEFAULT NULL
1113 , P_Project_Rev_Rate_Date IN DATE DEFAULT NULL
1114 , P_Project_Rev_Exchange_Rate IN NUMBER DEFAULT NULL
1115 , P_Project_Revenue_Amount IN NUMBER DEFAULT NULL
1116 , P_ProjFunc_Currency_Code IN VARCHAR2 DEFAULT NULL
1117 , P_ProjFunc_Rate_Type IN VARCHAR2 DEFAULT NULL
1118 , P_ProjFunc_Rate_Date IN DATE DEFAULT NULL
1119 , P_ProjFunc_Exchange_Rate IN NUMBER DEFAULT NULL
1120 , P_ProjFunc_Inv_Rate_Date IN DATE DEFAULT NULL
1121 , P_ProjFunc_Inv_Exchange_Rate IN NUMBER DEFAULT NULL
1122 , P_ProjFunc_Bill_Amount IN NUMBER DEFAULT NULL
1123 , P_ProjFunc_Rev_Rate_Date IN DATE DEFAULT NULL
1124 , P_Projfunc_Rev_Exchange_Rate IN NUMBER DEFAULT NULL
1125 , P_ProjFunc_Revenue_Amount IN NUMBER DEFAULT NULL
1126 , P_Funding_Rate_Type IN VARCHAR2 DEFAULT NULL
1127 , P_Funding_Rate_Date IN DATE DEFAULT NULL
1128 , P_Funding_Exchange_Rate IN NUMBER DEFAULT NULL
1129 , P_Invproc_Currency_Code IN VARCHAR2 DEFAULT NULL
1130 , P_Invproc_Rate_Type IN VARCHAR2 DEFAULT NULL
1131 , P_Invproc_Rate_Date IN DATE DEFAULT NULL
1132 , P_Invproc_Exchange_Rate IN NUMBER DEFAULT NULL
1133 , P_Revproc_Currency_Code IN VARCHAR2 DEFAULT NULL
1134 , P_Revproc_Rate_Type IN VARCHAR2 DEFAULT NULL
1135 , P_Revproc_Rate_Date IN DATE DEFAULT NULL
1136 , P_Revproc_Exchange_Rate IN NUMBER DEFAULT NULL
1137 , P_Inv_Gen_Rejection_Code IN VARCHAR2 DEFAULT NULL
1138 , X_Return_Status OUT NOCOPY VARCHAR2
1139 , X_Msg_Count OUT NOCOPY NUMBER
1140 , X_Msg_Data OUT NOCOPY VARCHAR2
1141 ) IS
1142
1143 CURSOR EventNum IS
1144 SELECT event_num
1145 , project_id
1146 , revenue_distributed_flag
1147 , rowid
1148 , bill_amount
1149 FROM pa_events
1150 WHERE event_id = P_Event_ID;
1151
1152
1153
1154 L_Event_ID NUMBER;
1155 L_Event_Num NUMBER;
1156 L_Project_ID NUMBER;
1157 L_Rev_Dist VARCHAR2(80);
1158 L_RowID VARCHAR2(18);
1159 L_Bill_Amount NUMBER;
1160 L_UserID NUMBER := FND_GLOBAL.User_ID;
1161 L_LoginID NUMBER := FND_GLOBAL.Login_ID;
1162 L_Result VARCHAR2(1);
1163 l_api_name CONSTANT VARCHAR2(30) := 'Update_Billing_Event';
1164
1165 BEGIN
1166
1167 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1168 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Updating Billing Event ...');
1169 END IF;
1170
1171 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
1172
1173 --
1174 -- Standard Start of API savepoint
1175 --
1176 SAVEPOINT update_billing_event_pvt;
1177
1178 --
1179 -- Get Event Num
1180 --
1181 OPEN EventNum;
1182 FETCH EventNum INTO L_Event_Num , L_Project_ID,L_Rev_Dist , L_RowID,L_Bill_Amount;
1183 CLOSE EventNum;
1184
1185
1186
1187 IF ( PA_EVENTS_PKG.Is_Event_Billed
1188 ( L_Project_ID
1189 , P_Task_ID
1190 , L_Event_Num
1191 , L_Bill_Amount ) = 'Y')THEN
1192
1193 -- Event has already been billed, need to cancel previous entry
1194 -- and create a new one.
1195 --
1196 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1197 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Event already processed in PA:');
1198 END IF;
1199
1200 --
1201 -- A Law 05/15/2001
1202 --
1203 -- Temporarily raised an exception when event has been processed.
1204 -- Current design logic does not work as a non-updateable event
1205 -- maybe revenue distributed but not billed. In such scenario,
1206 -- The credit memo event will cause draft invoice creation to fail.
1207 --
1208 FND_MESSAGE.set_name('OKE' , 'OKE_BILL_EVENT_PROCESSED');
1209 FND_MESSAGE.set_token('EVENT' , L_Event_Num);
1210 FND_MSG_PUB.add;
1211 RAISE FND_API.G_EXC_ERROR;
1212
1213 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1214 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Reversing billed entry ...');
1215 END IF;
1216
1217 Revert_Billing_Event
1218 ( P_Event_ID => P_Event_ID
1219 , P_Event_Date => P_Event_Date
1220 );
1221
1222 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1223 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Creating revised entry ...');
1224 END IF;
1225
1226 Create_Billing_Event
1227 ( P_Commit => FND_API.G_FALSE
1228 , P_Event_Type => P_Event_Type
1229 , P_Event_Date => P_Event_Date
1230 , P_Project_ID => P_Project_ID
1231 , P_Task_ID => P_Task_ID
1232 , P_Organization_ID => P_Organization_ID
1233 , P_Description => P_Description
1234 , P_Unit_Price => P_Unit_Price
1235 , P_Bill_Quantity => P_Bill_Quantity
1236 , P_UOM_Code => P_UOM_Code
1237 , P_Bill_Amount => P_Bill_Amount
1238 , P_Revenue_Amount => P_Revenue_Amount
1239 , P_Item_ID => P_Item_ID
1240 , P_Inventory_Org_ID => P_Inventory_Org_ID
1241 , P_Contract_Num => P_Contract_Num
1242 , P_Order_Num => P_Order_Num
1243 , P_Line_Num => P_Line_Num
1244 , P_Chg_Request_Num => P_Chg_Request_Num
1245 , P_Bill_Of_Lading => P_Bill_Of_Lading
1246 , P_Serial_Num => P_Serial_Num
1247 , P_Fund_Ref1 => P_Fund_Ref1
1248 , P_Fund_Ref2 => P_Fund_Ref2
1249 , P_Fund_Ref3 => P_Fund_Ref3
1250 , P_Event_Num_Reversed => NULL
1251 , P_Bill_Trans_Currency_Code => P_Bill_Trans_Currency_Code
1252 , P_Bill_Trans_Bill_Amount => P_Bill_Trans_Bill_Amount
1253 , P_Bill_Trans_rev_Amount => P_Bill_Trans_rev_Amount
1254 , P_Project_Currency_Code => P_Project_Currency_Code
1255 , P_Project_Rate_Type => P_Project_Rate_Type
1256 , P_Project_Rate_Date => P_Project_Rate_Date
1257 , P_Project_Exchange_Rate => P_Project_Exchange_Rate
1258 , P_Project_Inv_Rate_Date => P_Project_Inv_Rate_Date
1259 , P_Project_Inv_Exchange_Rate => P_Project_Inv_Exchange_Rate
1260 , P_Project_Bill_Amount => P_Project_Bill_Amount
1261 , P_Project_Rev_Rate_Date => P_Project_Rev_Rate_Date
1262 , P_Project_Rev_Exchange_Rate => P_Project_Rev_Exchange_Rate
1263 , P_Project_Revenue_Amount => P_Project_Revenue_Amount
1264 , P_ProjFunc_Currency_Code => P_ProjFunc_Currency_Code
1265 , P_ProjFunc_Rate_Type => P_ProjFunc_Rate_Type
1266 , P_ProjFunc_Rate_Date => P_ProjFunc_Rate_Date
1267 , P_ProjFunc_Exchange_Rate => P_ProjFunc_Exchange_Rate
1268 , P_ProjFunc_Inv_Rate_Date => P_ProjFunc_Inv_Rate_Date
1269 , P_ProjFunc_Inv_Exchange_Rate => P_ProjFunc_Inv_Exchange_Rate
1270 , P_ProjFunc_Bill_Amount => P_ProjFunc_Bill_Amount
1271 , P_ProjFunc_Rev_Rate_Date => P_ProjFunc_Rev_Rate_Date
1272 , P_Projfunc_Rev_Exchange_Rate => P_Projfunc_Rev_Exchange_Rate
1273 , P_ProjFunc_Revenue_Amount => P_ProjFunc_Revenue_Amount
1274 , P_Funding_Rate_Type => P_Funding_Rate_Type
1275 , P_Funding_Rate_Date => P_Funding_Rate_Date
1276 , P_Funding_Exchange_Rate => P_Funding_Exchange_Rate
1277 , P_Invproc_Currency_Code => P_Invproc_Currency_Code
1278 , P_Invproc_Rate_Type => P_Invproc_Rate_Type
1279 , P_Invproc_Rate_Date => P_Invproc_Rate_Date
1280 , P_Invproc_Exchange_Rate => P_Invproc_Exchange_Rate
1281 , P_Revproc_Currency_Code => P_Revproc_Currency_Code
1282 , P_Revproc_Rate_Type => P_Revproc_Rate_Type
1283 , P_Revproc_Rate_Date => P_Revproc_Rate_Date
1284 , P_Revproc_Exchange_Rate => P_Revproc_Exchange_Rate
1285 , P_Inv_Gen_Rejection_Code => P_Inv_Gen_Rejection_Code
1286 , X_Event_ID => L_Event_ID
1287 , X_Event_Num => L_Event_Num
1288 , X_Return_Status => X_Return_Status
1289 , X_Msg_Count => X_Msg_Count
1290 , X_Msg_Data => X_Msg_Data
1291 );
1292
1293 IF ( X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1295 ELSIF ( X_return_status = FND_API.G_RET_STS_ERROR ) THEN
1296 RAISE FND_API.G_EXC_ERROR;
1297 END IF;
1298
1299 UPDATE oke_k_billing_events
1300 SET pa_event_id = L_Event_ID
1301 WHERE billing_event_id = P_Event_ID;
1302
1303 ELSE
1304
1305
1306 -- Event has not been billed, only need to update event
1307 -- information
1308 --
1309
1310 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1311 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Event not yet processed in PA; update existing ...');
1312 END IF;
1313
1314
1315 UPDATE pa_events
1316 SET last_update_date = sysdate
1317 , last_updated_by = FND_GLOBAL.User_ID
1318 , last_update_login = FND_GLOBAL.Login_ID
1319 , event_type = nvl( P_Event_Type , event_type )
1320 , description = nvl( P_Description , description )
1321 , bill_amount = nvl( P_Bill_Amount , bill_amount )
1322 , revenue_amount = nvl( P_Revenue_Amount , revenue_amount )
1323 , completion_date = nvl( P_Event_Date , completion_date )
1324 , project_id = nvl( P_Project_ID , project_id )
1325 , event_num = L_Event_Num
1326 , task_id = nvl( P_Task_ID , task_id )
1327 , quantity_billed = P_Bill_Quantity
1328 , uom_code = P_UOM_Code
1329 , inventory_org_id = P_Inventory_Org_ID
1330 , inventory_item_id = P_Item_ID
1331 , unit_price = P_Unit_Price
1332 , reference1 = P_Contract_Num
1333 , reference2 = P_Order_Num
1334 , reference3 = P_Line_Num
1335 , reference4 = P_Chg_Request_Num
1336 , reference5 = P_Fund_Ref1
1337 , reference6 = P_Fund_Ref2
1338 , reference7 = P_Fund_Ref3
1339 , reference8 = P_Bill_Of_Lading
1340 , reference9 = P_Serial_Num
1341 , reference10 = 'OKE'
1342 , organization_id = P_Organization_ID /* jxtang for bug 2219338 */
1343 , Bill_Trans_Currency_Code = P_Bill_Trans_Currency_Code
1344 , Bill_Trans_Bill_Amount = P_Bill_Trans_Bill_Amount
1345 , Bill_Trans_rev_Amount = P_Bill_Trans_rev_Amount
1346 , Project_Currency_Code = P_Project_Currency_Code
1347 , Project_Rate_Type = P_Project_Rate_Type
1348 , Project_Rate_Date = P_Project_Rate_Date
1349 , Project_Exchange_Rate = P_Project_Exchange_Rate
1350 , Project_Inv_Rate_Date = P_Project_Inv_Rate_Date
1351 , Project_Inv_Exchange_Rate = P_Project_Inv_Exchange_Rate
1352 , Project_Bill_Amount = P_Project_Bill_Amount
1353 , Project_Rev_Rate_Date = P_Project_Rev_Rate_Date
1354 , Project_Rev_Exchange_Rate = P_Project_Rev_Exchange_Rate
1355 , Project_Revenue_Amount = P_Project_Revenue_Amount
1356 , ProjFunc_Currency_Code = P_ProjFunc_Currency_Code
1357 , ProjFunc_Rate_Type = P_ProjFunc_Rate_Type
1358 , ProjFunc_Rate_Date = P_ProjFunc_Rate_Date
1359 , ProjFunc_Exchange_Rate = P_ProjFunc_Exchange_Rate
1360 , ProjFunc_Inv_Rate_Date = P_ProjFunc_Inv_Rate_Date
1361 , ProjFunc_Inv_Exchange_Rate = P_ProjFunc_Inv_Exchange_Rate
1362 , ProjFunc_Bill_Amount = P_ProjFunc_Bill_Amount
1363 , ProjFunc_Rev_Rate_Date = P_ProjFunc_Rev_Rate_Date
1364 , Projfunc_Rev_Exchange_Rate = P_Projfunc_Rev_Exchange_Rate
1365 , ProjFunc_Revenue_Amount = P_ProjFunc_Revenue_Amount
1366 , Funding_Rate_Type = P_Funding_Rate_Type
1367 , Funding_Rate_Date = P_Funding_Rate_Date
1368 , Funding_Exchange_Rate = P_Funding_Exchange_Rate
1369 , Invproc_Currency_Code = P_Invproc_Currency_Code
1370 , Invproc_Rate_Type = P_Invproc_Rate_Type
1371 , Invproc_Rate_Date = P_Invproc_Rate_Date
1372 , Invproc_Exchange_Rate = P_Invproc_Exchange_Rate
1373 , Revproc_Currency_Code = P_Revproc_Currency_Code
1374 , Revproc_Rate_Type = P_Revproc_Rate_Type
1375 , Revproc_Rate_Date = P_Revproc_Rate_Date
1376 , Revproc_Exchange_Rate = P_Revproc_Exchange_Rate
1377 , Inv_Gen_Rejection_Code = P_Inv_Gen_Rejection_Code
1378 WHERE event_id = P_Event_ID;
1379
1380 Update_Event_References
1381 ( P_Event_ID => P_Event_ID
1382 , P_Contract_Num => P_Contract_Num
1383 , P_Order_Num => P_Order_Num
1384 , P_Line_Num => P_Line_Num
1385 , P_Chg_Req_Num => P_Chg_Request_Num
1386 , P_Item_ID => P_Item_ID
1387 , P_Org_ID => P_Inventory_Org_ID
1388 , P_Unit_Price => P_Unit_Price
1389 , P_UOM => P_UOM_Code
1390 , P_Bill_Quantity => P_Bill_Quantity
1391 , P_Bill_Of_Lading => P_Bill_Of_Lading
1392 , P_Serial_Num => P_Serial_Num
1393 , P_Fund_Ref1 => P_Fund_Ref1
1394 , P_Fund_Ref2 => P_Fund_Ref2
1395 , P_Fund_Ref3 => P_Fund_Ref3
1396 );
1397
1398
1399
1400 END IF;
1401
1402 --
1403 -- Standard commit check
1404 --
1405 IF FND_API.To_Boolean( p_commit ) THEN
1406 COMMIT WORK;
1407 END IF;
1408
1409 --
1410 -- Standard call to get message count and if count is 1, get message
1411 -- info
1412 --
1413 FND_MSG_PUB.Count_And_Get( P_Count => X_Msg_Count
1414 , P_Data => X_Msg_Data );
1415
1416 EXCEPTION
1417 WHEN FND_API.G_EXC_ERROR THEN
1418 ROLLBACK TO update_billing_event_pvt;
1419 X_Return_Status := FND_API.G_RET_STS_ERROR;
1420 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1421 , p_data => x_msg_data );
1422
1423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1424 ROLLBACK TO update_billing_event_pvt;
1425 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1426 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1427 , p_data => x_msg_data );
1428
1429 WHEN OTHERS THEN
1430 ROLLBACK TO update_billing_event_pvt;
1431 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1432 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1433 FND_MSG_PUB.add_exc_msg
1434 ( p_pkg_name => G_Pkg_Name
1435 , p_procedure_name => 'UPDATE_BILLING_EVENT' );
1436 END IF;
1437 END Update_Billing_Event;
1438
1439 PROCEDURE Insert_Billing_Info
1440 ( P_Deliverable_Id IN NUMBER
1441 , P_Billing_Event_Id IN NUMBER
1442 , P_Pa_Event_Id IN NUMBER
1443 , P_K_Header_Id IN NUMBER
1444 , P_K_Line_Id IN NUMBER
1445 , P_Bill_Event_Type IN VARCHAR2
1446 , P_Bill_Event_Date IN DATE
1447 , P_Bill_Item_Id IN NUMBER
1448 , P_Bill_Line_Id IN NUMBER
1449 , P_Bill_Chg_Req_Id IN NUMBER
1450 , P_Bill_Project_Id IN NUMBER
1451 , P_Bill_Task_Id IN NUMBER
1452 , P_Bill_Organization_Id IN NUMBER
1453 , P_Bill_Fund_Ref1 IN VARCHAR2
1454 , P_Bill_Fund_Ref2 IN VARCHAR2
1455 , P_Bill_Fund_Ref3 IN VARCHAR2
1456 , P_Bill_Bill_Of_Lading IN VARCHAR2
1457 , P_Bill_Serial_Num IN VARCHAR2
1458 , P_Bill_Currency_Code IN VARCHAR2
1459 , P_Bill_Rate_Type IN VARCHAR2
1460 , P_Bill_Rate_Date IN DATE
1461 , P_Bill_Exchange_Rate IN NUMBER
1462 , P_Bill_Description IN VARCHAR2
1463 , P_Bill_Quantity IN NUMBER
1464 , P_Bill_Unit_Price IN NUMBER
1465 , P_Revenue_Amount IN NUMBER
1466 , P_Created_By IN NUMBER
1467 , P_Creation_Date IN DATE
1468 , P_LAST_UPDATED_BY IN NUMBER
1469 , P_LAST_UPDATE_LOGIN IN NUMBER
1470 , P_LAST_UPDATE_DATE IN DATE
1471 ) Is
1472
1473 Begin
1474
1475 Insert Into oke_k_billing_events
1476 ( BILLING_EVENT_ID
1477 , PA_EVENT_ID
1478 , K_HEADER_ID
1479 , K_LINE_ID
1480 , DELIVERABLE_ID
1481 , BILL_EVENT_TYPE
1482 , BILL_EVENT_DATE
1483 , BILL_ITEM_ID
1484 , BILL_LINE_ID
1485 , BILL_CHG_REQ_ID
1486 , BILL_PROJECT_ID
1487 , BILL_TASK_ID
1488 , BILL_ORGANIZATION_ID
1489 , BILL_FUND_REF1
1490 , BILL_FUND_REF2
1491 , BILL_FUND_REF3
1492 , BILL_BILL_OF_LADING
1493 , BILL_SERIAL_NUM
1494 , BILL_CURRENCY_CODE
1495 , BILL_RATE_TYPE
1496 , BILL_RATE_DATE
1497 , BILL_EXCHANGE_RATE
1498 , BILL_DESCRIPTION
1499 , BILL_QUANTITY
1500 , BILL_UNIT_PRICE
1501 , REVENUE_AMOUNT
1502 , CREATED_BY
1503 , CREATION_DATE
1504 , LAST_UPDATED_BY
1505 , LAST_UPDATE_LOGIN
1506 , LAST_UPDATE_DATE
1507 , INITIATED_FLAG)
1508 VALUES
1509 ( P_BILLING_EVENT_ID
1510 , P_PA_EVENT_ID
1511 , P_K_HEADER_ID
1512 , P_K_LINE_ID
1513 , P_DELIVERABLE_ID
1514 , P_BILL_EVENT_TYPE
1515 , P_BILL_EVENT_DATE
1516 , P_BILL_ITEM_ID
1517 , P_BILL_LINE_ID
1518 , P_BILL_CHG_REQ_ID
1519 , P_BILL_PROJECT_ID
1520 , P_BILL_TASK_ID
1521 , P_BILL_ORGANIZATION_ID
1522 , P_BILL_FUND_REF1
1523 , P_BILL_FUND_REF2
1524 , P_BILL_FUND_REF3
1525 , P_BILL_BILL_OF_LADING
1526 , P_BILL_SERIAL_NUM
1527 , P_BILL_CURRENCY_CODE
1528 , P_BILL_RATE_TYPE
1529 , P_BILL_RATE_DATE
1530 , P_BILL_EXCHANGE_RATE
1531 , P_BILL_DESCRIPTION
1532 , P_BILL_QUANTITY
1533 , P_BILL_UNIT_PRICE
1534 , P_REVENUE_AMOUNT
1535 , P_CREATED_BY
1536 , P_CREATION_DATE
1537 , P_LAST_UPDATED_BY
1538 , P_LAST_UPDATE_LOGIN
1539 , P_LAST_UPDATE_DATE
1540 , 'N');
1541
1542 EXCEPTION
1543 WHEN OTHERS THEN
1544 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1545 FND_MSG_PUB.add_exc_msg
1546 ( p_pkg_name => G_Pkg_Name
1547 , p_procedure_name => 'INSERT_BILLING_INFO' );
1548 END IF;
1549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1550
1551 END INSERT_BILLING_INFO;
1552
1553
1554
1555
1556 PROCEDURE Update_Billing_Info
1557 ( P_Deliverable_ID IN NUMBER
1558 , P_Billing_Event_ID IN NUMBER
1559 , P_Bill_Event_Type IN VARCHAR2
1560 , P_Bill_Event_Date IN DATE
1561 , P_Bill_Project_ID IN NUMBER
1562 , P_Bill_Task_ID IN NUMBER
1563 , P_Bill_Org_ID IN NUMBER
1564 , P_Bill_Line_ID IN NUMBER
1565 , P_Bill_Chg_Req_ID IN NUMBER
1566 , P_Bill_Item_ID IN NUMBER
1567 , P_Bill_Description IN VARCHAR2
1568 , P_Bill_Unit_Price IN NUMBER
1569 , P_Bill_Quantity IN NUMBER
1570 , P_Bill_Currency_Code IN VARCHAR2
1571 , P_Bill_Rate_Type IN VARCHAR2
1572 , P_Bill_Rate_Date IN DATE
1573 , P_Bill_Exchange_Rate IN NUMBER
1574 , P_Revenue_Amount IN NUMBER
1575 , P_Bill_Of_Lading IN VARCHAR2
1576 , P_Bill_Serial_Num IN VARCHAR2
1577 , P_Bill_Fund_Ref1 IN VARCHAR2
1578 , P_Bill_Fund_Ref2 IN VARCHAR2
1579 , P_Bill_Fund_Ref3 IN VARCHAR2
1580 , P_LAST_UPDATED_BY IN NUMBER
1581 , P_LAST_UPDATE_LOGIN IN NUMBER
1582 , P_LAST_UPDATE_DATE IN DATE
1583 ) IS
1584 BEGIN
1585
1586 UPDATE oke_k_billing_events
1587 SET bill_event_type = P_Bill_Event_Type
1588 , bill_event_date = P_Bill_Event_Date
1589 /* , billing_event_id =
1590 DECODE( SIGN( nvl(billing_event_id,0) - P_Billing_Event_ID )
1591 , 1 , billing_event_id , P_Billing_Event_ID ) */
1592 , bill_project_id = P_Bill_Project_ID
1593 , bill_task_id = P_Bill_Task_ID
1594 , bill_organization_id = P_Bill_Org_ID
1595 , bill_line_id = P_Bill_Line_ID
1596 , bill_chg_req_id = P_Bill_Chg_Req_ID
1597 , bill_item_id = P_Bill_Item_ID
1598 , bill_description = P_Bill_Description
1599 -- , unit_price = P_Unit_Price
1600 , bill_unit_price = P_Bill_Unit_Price
1601 , bill_quantity = P_Bill_Quantity
1602 , revenue_amount = P_Revenue_Amount
1603 , bill_currency_code = P_Bill_Currency_Code
1604 , bill_rate_type = P_Bill_Rate_Type
1605 , bill_rate_date = P_Bill_Rate_Date
1606 , bill_exchange_rate = P_Bill_Exchange_Rate
1607 , bill_bill_of_lading = P_Bill_Of_Lading
1608 , bill_serial_num = P_Bill_Serial_Num
1609 , bill_fund_ref1 = P_Bill_Fund_Ref1
1610 , bill_fund_ref2 = P_Bill_Fund_Ref2
1611 , bill_fund_ref3 = P_Bill_Fund_Ref3
1612 , last_updated_by = P_LAST_UPDATED_BY
1613 , last_update_login = P_LAST_UPDATE_LOGIN
1614 , last_update_date = P_LAST_UPDATE_DATE
1615 , initiated_flag = 'N'
1616 WHERE billing_event_id = P_Billing_Event_ID;
1617
1618 EXCEPTION
1619 WHEN OTHERS THEN
1620 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1621 FND_MSG_PUB.add_exc_msg
1622 ( p_pkg_name => G_Pkg_Name
1623 , p_procedure_name => 'UPDATE_BILLING_INFO' );
1624 END IF;
1625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1626 END Update_Billing_Info;
1627
1628 PROCEDURE Delete_Billing_Info (
1629 P_Billing_Event_ID IN NUMBER
1630 ) IS
1631 L_task_ID NUMBER;
1632 L_Event_Num NUMBER;
1633 l_pa_event_id NUMBER;
1634 L_Project_ID NUMBER;
1635 L_RowID VARCHAR2(18);
1636 L_oke_RowID ROWID;
1637 L_Bill_Amount NUMBER;
1638
1639 CURSOR EventNum IS
1640 SELECT event_num
1641 , project_id
1642 , task_id
1643 , rowid
1644 , bill_amount
1645 FROM pa_events
1646 WHERE event_id = l_pa_event_id;
1647
1648 BEGIN
1649
1650 SELECT pa_event_id, ROWID
1651 INTO l_pa_event_id, L_oke_RowID
1652 FROM oke_k_billing_events
1653 WHERE billing_event_id = P_Billing_Event_ID;
1654
1655 IF l_pa_event_id IS NOT NULL THEN
1656
1657 OPEN EventNum;
1658 FETCH EventNum INTO L_Event_Num, L_Project_ID, L_task_ID, L_RowID, L_Bill_Amount;
1659 CLOSE EventNum;
1660
1661 IF L_RowID IS NOT NULL THEN
1662
1663 IF ( PA_EVENTS_PKG.Is_Event_Billed
1664 ( L_Project_ID
1665 , L_Task_ID
1666 , L_Event_Num
1667 , L_Bill_Amount ) = 'Y')
1668 THEN
1669 FND_MESSAGE.set_name('OKE' , 'OKE_BILL_EVENT_PROCESSED');
1670 FND_MESSAGE.set_token('EVENT' , L_Event_Num);
1671 FND_MSG_PUB.add;
1672 RAISE FND_API.G_EXC_ERROR;
1673 ELSE
1674 PA_EVENTS_PKG.Delete_Row(X_Rowid => l_rowid);
1675 END IF;
1676 END IF;
1677 END IF;
1678
1679 DELETE FROM oke_k_billing_events
1680 WHERE ROWID = L_oke_RowID;
1681
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1685 FND_MSG_PUB.add_exc_msg
1686 ( p_pkg_name => G_Pkg_Name
1687 , p_procedure_name => 'DELETE_BILLING_INFO' );
1688 END IF;
1689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1690 END Delete_Billing_Info;
1691
1692 PROCEDURE Lock_Billing_Info
1693 ( P_Deliverable_ID IN NUMBER
1694 , P_Billing_Event_ID IN NUMBER
1695 , P_Bill_Event_Type IN VARCHAR2
1696 , P_Bill_Event_Date IN DATE
1697 , P_Bill_Project_ID IN NUMBER
1698 , P_Bill_Task_ID IN NUMBER
1699 , P_Bill_Org_ID IN NUMBER
1700 , P_Bill_Line_ID IN NUMBER
1701 , P_Bill_Chg_Req_ID IN NUMBER
1702 , P_Bill_Item_ID IN NUMBER
1703 , P_Bill_Description IN VARCHAR2
1704 , P_Bill_Unit_Price IN NUMBER
1705 , P_Bill_Quantity IN NUMBER
1706 , P_Bill_Currency_Code IN VARCHAR2
1707 , P_Bill_Rate_Type IN VARCHAR2
1708 , P_Bill_Rate_Date IN DATE
1709 , P_Bill_Exchange_Rate IN NUMBER
1710 , P_Revenue_Amount IN NUMBER
1711 , P_Bill_Of_Lading IN VARCHAR2
1712 , P_Bill_Serial_Num IN VARCHAR2
1713 , P_Bill_Fund_Ref1 IN VARCHAR2
1714 , P_Bill_Fund_Ref2 IN VARCHAR2
1715 , P_Bill_Fund_Ref3 IN VARCHAR2
1716 ) IS
1717
1718 CURSOR c IS
1719 SELECT bill_event_type
1720 , bill_event_date
1721 , billing_event_id
1722 , pa_event_id
1723 , k_header_id
1724 , k_line_id
1725 , deliverable_id
1726 , bill_project_id
1727 , bill_task_id
1728 , bill_organization_id
1729 , bill_line_id
1730 , bill_chg_req_id
1731 , bill_item_id
1732 , bill_description
1733 , bill_unit_price
1734 , bill_quantity
1735 , revenue_amount
1736 , bill_currency_code
1737 , bill_rate_type
1738 , bill_rate_date
1739 , bill_exchange_rate
1740 , bill_bill_of_lading
1741 , bill_serial_num
1742 , bill_fund_ref1
1743 , bill_fund_ref2
1744 , bill_fund_ref3
1745 FROM oke_k_billing_events
1746 WHERE billing_event_id = P_Billing_Event_ID
1747 FOR UPDATE OF Billing_Event_ID NOWAIT;
1748
1749 RecInfo c%rowtype;
1750
1751 BEGIN
1752
1753 OPEN c;
1754 FETCH c INTO RecInfo;
1755 IF ( c%notfound ) THEN
1756 CLOSE c;
1757 FND_MESSAGE.Set_Name('FND' , 'FORM_RECORD_DELETED');
1758 APP_EXCEPTION.Raise_Exception;
1759 END IF;
1760 CLOSE c;
1761
1762 IF ( ((RecInfo.bill_event_type = P_Bill_Event_Type)
1763 OR ((RecInfo.bill_event_type is null) AND (P_Bill_Event_Type is null)))
1764 AND ((RecInfo.bill_event_date = P_Bill_Event_Date)
1765 OR ((RecInfo.bill_event_date is null) AND (P_Bill_Event_Date is null)))
1766 AND ((RecInfo.billing_event_id = P_Billing_Event_ID)
1767 OR ((RecInfo.billing_event_id is null) AND (P_Billing_Event_ID is null)))
1768 AND ((RecInfo.bill_project_id = P_Bill_Project_ID)
1769 OR ((RecInfo.bill_project_id is null) AND (P_Bill_Project_ID is null)))
1770 AND ((RecInfo.bill_task_id = P_Bill_Task_ID)
1771 OR ((RecInfo.bill_task_id is null) AND (P_Bill_Task_ID is null)))
1772 AND ((RecInfo.bill_organization_id = P_Bill_Org_ID)
1773 OR ((RecInfo.bill_organization_id is null) AND (P_Bill_Org_ID is null)))
1774 AND ((RecInfo.bill_line_id = P_Bill_Line_ID)
1775 OR ((RecInfo.bill_line_id is null) AND (P_Bill_Line_ID is null)))
1776 AND ((RecInfo.bill_chg_req_id = P_Bill_Chg_Req_ID)
1777 OR ((RecInfo.bill_chg_req_id is null) AND (P_Bill_Chg_Req_ID is null)))
1778 AND ((RecInfo.bill_item_id = P_Bill_Item_ID)
1779 OR ((RecInfo.bill_item_id is null) AND (P_Bill_Item_ID is null)))
1780 AND ((RecInfo.bill_description = P_Bill_Description)
1781 OR ((RecInfo.bill_description is null) AND (P_Bill_Description is null)))
1782 -- AND ((RecInfo.unit_price = P_Unit_Price)
1783 -- OR ((RecInfo.unit_price is null) AND (P_Unit_Price is null)))
1784 AND ((RecInfo.bill_unit_price = P_Bill_Unit_Price)
1785 OR ((RecInfo.bill_unit_price is null) AND (P_Bill_Unit_Price is null)))
1786 AND ((RecInfo.bill_quantity = P_Bill_Quantity)
1787 OR ((RecInfo.bill_quantity is null) AND (P_Bill_Quantity is null)))
1788 AND ((RecInfo.revenue_amount = P_Revenue_Amount)
1789 OR ((RecInfo.revenue_amount is null) AND (P_Revenue_Amount is null)))
1790 AND ((RecInfo.bill_currency_code = P_Bill_Currency_Code)
1791 OR ((RecInfo.bill_currency_code is null) AND (P_Bill_Currency_Code is null)))
1792 AND ((RecInfo.bill_rate_type = P_Bill_Rate_Type)
1793 OR ((RecInfo.bill_rate_type is null) AND (P_Bill_Rate_Type is null)))
1794 AND ((RecInfo.bill_rate_date = P_Bill_Rate_Date)
1795 OR ((RecInfo.bill_rate_date is null) AND (P_Bill_Rate_Date is null)))
1796 AND ((RecInfo.bill_exchange_rate = P_Bill_Exchange_Rate)
1797 OR ((RecInfo.bill_exchange_rate is null) AND (P_Bill_Exchange_Rate is null)))
1798 AND ((RecInfo.bill_bill_of_lading = P_Bill_Of_Lading)
1799 OR ((RecInfo.bill_bill_of_lading is null) AND (P_Bill_Of_Lading is null)))
1800 AND ((RecInfo.bill_serial_num = P_Bill_Serial_Num)
1801 OR ((RecInfo.bill_serial_num is null) AND (P_Bill_Serial_Num is null)))
1802 AND ((RecInfo.bill_fund_ref1 = P_Bill_Fund_Ref1)
1803 OR ((RecInfo.bill_fund_ref1 is null) AND (P_Bill_Fund_Ref1 is null)))
1804 AND ((RecInfo.bill_fund_ref2 = P_Bill_Fund_Ref2)
1805 OR ((RecInfo.bill_fund_ref2 is null) AND (P_Bill_Fund_Ref2 is null)))
1806 AND ((RecInfo.bill_fund_ref3 = P_Bill_Fund_Ref3)
1807 OR ((RecInfo.bill_fund_ref3 is null) AND (P_Bill_Fund_Ref3 is null)))
1808 ) THEN
1809 NULL;
1810 ELSE
1811 FND_MESSAGE.Set_NAme('FND' , 'FORM_RECORD_CHANGED');
1812 APP_EXCEPTION.Raise_Exception;
1813 END IF;
1814
1815 RETURN;
1816
1817 END Lock_Billing_Info;
1818
1819
1820 PROCEDURE Populate_MC_Columns
1821 ( P_Event_ID IN NUMBER
1822 , x_Bill_Trans_Currency_Code OUT NOCOPY VARCHAR2
1823 , x_Bill_Trans_Bill_Amount OUT NOCOPY NUMBER
1824 , x_Bill_Trans_rev_Amount OUT NOCOPY NUMBER
1825 , x_Project_Currency_Code OUT NOCOPY VARCHAR2
1826 , x_Project_Rate_Type OUT NOCOPY VARCHAR2
1827 , x_Project_Rate_Date OUT NOCOPY DATE
1828 , x_Project_Exchange_Rate OUT NOCOPY NUMBER
1829 , x_Project_inv_Rate_Date OUT NOCOPY DATE
1830 , x_Project_Inv_Exchange_Rate OUT NOCOPY NUMBER
1831 , x_Project_Bill_Amount OUT NOCOPY NUMBER
1832 , x_Project_Rev_Rate_Date OUT NOCOPY DATE
1833 , x_Project_Rev_Exchange_Rate OUT NOCOPY NUMBER
1834 , x_Project_Revenue_Amount OUT NOCOPY NUMBER
1835 , x_ProjFunc_Currency_Code OUT NOCOPY VARCHAR2
1836 , x_ProjFunc_Rate_Type OUT NOCOPY VARCHAR2
1837 , x_ProjFunc_Rate_Date OUT NOCOPY DATE
1838 , x_ProjFunc_Exchange_Rate OUT NOCOPY NUMBER
1839 , x_ProjFunc_Inv_Rate_Date OUT NOCOPY DATE
1840 , x_ProjFunc_Inv_Exchange_Rate OUT NOCOPY NUMBER
1841 , x_ProjFunc_Bill_Amount OUT NOCOPY NUMBER
1842 , x_ProjFunc_Rev_Rate_Date OUT NOCOPY DATE
1843 , x_Projfunc_Rev_Exchange_Rate OUT NOCOPY NUMBER
1844 , x_ProjFunc_Revenue_Amount OUT NOCOPY NUMBER
1845 , x_Funding_Rate_Type OUT NOCOPY VARCHAR2
1846 , x_Funding_Rate_Date OUT NOCOPY DATE
1847 , x_Funding_Exchange_Rate OUT NOCOPY NUMBER
1848 , x_Invproc_Currency_Code OUT NOCOPY VARCHAR2
1849 , x_Invproc_Rate_Type OUT NOCOPY VARCHAR2
1850 , x_Invproc_Rate_Date OUT NOCOPY DATE
1851 , x_Invproc_Exchange_Rate OUT NOCOPY NUMBER
1852 , x_Revproc_Currency_Code OUT NOCOPY VARCHAR2
1853 , x_Revproc_Rate_Type OUT NOCOPY VARCHAR2
1854 , x_Revproc_Rate_Date OUT NOCOPY DATE
1855 , x_Revproc_Exchange_Rate OUT NOCOPY NUMBER
1856 , x_Inv_Gen_Rejection_Code OUT NOCOPY VARCHAR2 ) IS
1857
1858 BillInfoRec BillInfo%rowtype;
1859
1860 l_api_name CONSTANT VARCHAR2(30) := 'Populate_MC_Columns';
1861 l_multi_currency_billing_flag VARCHAR2(15);
1862 l_baseline_funding_flag VARCHAR2(15);
1863 l_revproc_currency_code VARCHAR2(15);
1864 l_invproc_currency_code VARCHAR2(30);
1865 l_project_currency_code VARCHAR2(15);
1866 l_project_bil_rate_date_code VARCHAR2(30);
1867 l_project_bil_rate_type VARCHAR2(30);
1868 l_project_bil_rate_date DATE;
1869 l_project_bil_exchange_rate NUMBER;
1870 l_projfunc_currency_code VARCHAR2(15);
1871 l_projfunc_bil_rate_date_code VARCHAR2(30);
1872 l_projfunc_bil_rate_type VARCHAR2(30);
1873 l_invproc_currency_type VARCHAR2(30);
1874 l_projfunc_bil_rate_date DATE;
1875 l_projfunc_bil_exchange_rate NUMBER;
1876 l_funding_rate_date_code VARCHAR2(30);
1877 l_funding_rate_type VARCHAR2(30);
1878 l_funding_rate_date DATE;
1879 l_funding_exchange_rate NUMBER;
1880 l_return_status VARCHAR2(30);
1881 l_msg_count NUMBER;
1882 l_msg_data VARCHAR2(30);
1883
1884 BEGIN
1885
1886 OPEN BillInfo(P_Event_ID);
1887 FETCH BillInfo INTO BillInfoRec;
1888 CLOSE BillInfo;
1889
1890 --
1891 -- populate currency information based on similar logic in the PA
1892 -- Events form
1893 --
1894 PA_MULTI_CURRENCY_BILLING.get_project_defaults
1895 ( P_project_id => BillInfoRec.Bill_Project_ID
1896 , X_multi_currency_billing_flag => l_multi_currency_billing_flag
1897 , X_baseline_funding_flag => l_baseline_funding_flag
1898 , X_revproc_currency_code => l_revproc_currency_code
1899 , X_invproc_currency_type => l_invproc_currency_type
1900 , X_invproc_currency_code => l_invproc_currency_code
1901 , X_project_currency_code => l_project_currency_code
1902 , X_project_bil_rate_date_code => l_project_bil_rate_date_code
1903 , X_project_bil_rate_type => l_project_bil_rate_type
1904 , X_project_bil_rate_date => l_project_bil_rate_date
1905 , X_project_bil_exchange_rate => l_project_bil_exchange_rate
1906 , X_projfunc_currency_code => l_projfunc_currency_code
1907 , X_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code
1908 , X_projfunc_bil_rate_type => l_projfunc_bil_rate_type
1909 , X_projfunc_bil_rate_date => l_projfunc_bil_rate_date
1910 , X_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate
1911 , X_funding_rate_date_code => l_funding_rate_date_code
1912 , X_funding_rate_type => l_funding_rate_type
1913 , X_funding_rate_date => l_funding_rate_date
1914 , X_funding_exchange_rate => l_funding_exchange_rate
1915 , X_return_status => l_return_status
1916 , X_msg_count => l_msg_count
1917 , X_msg_data => l_msg_data);
1918
1919 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1921 END IF;
1922
1923 x_Bill_Trans_Currency_Code := BillInfoRec.bill_currency_code;
1924 x_Bill_Trans_Bill_Amount := BillInfoRec.Bill_Amount;
1925 x_Bill_Trans_rev_Amount := BillInfoRec.Revenue_Amount;
1926
1927
1928 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1929 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Project Currency = ' || l_project_currency_code);
1930 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Project Rate Type = ' || l_project_bil_rate_type);
1931 END IF;
1932 x_Project_Currency_Code := l_project_currency_code;
1933 IF ( BillInfoRec.Bill_Currency_Code <> l_project_currency_code ) THEN
1934 x_Project_Rate_Type := l_project_bil_rate_type;
1935 END IF;
1936 x_Project_Rate_Date := NULL;
1937 x_Project_Exchange_Rate := NULL;
1938 x_Project_inv_Rate_Date := NULL;
1939 x_Project_Inv_Exchange_Rate := NULL;
1940 x_Project_Bill_Amount := NULL;
1941 x_Project_Rev_Rate_Date := NULL;
1942 x_Project_Rev_Exchange_Rate := NULL;
1943 x_Project_Revenue_Amount := NULL;
1944
1945
1946 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1947 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Project Func Currency = ' || l_projfunc_currency_code);
1948 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Project Func Rate Type = ' || l_projfunc_bil_rate_type);
1949 END IF;
1950 x_ProjFunc_Currency_Code := l_projfunc_currency_code;
1951 IF ( BillInfoRec.Bill_Currency_Code <> l_projfunc_currency_code ) THEN
1952 x_ProjFunc_Rate_Type := l_projfunc_bil_rate_type;
1953 END IF;
1954 x_ProjFunc_Rate_Date := NULL;
1955 x_ProjFunc_Exchange_Rate := NULL;
1956 x_ProjFunc_Inv_Rate_Date := NULL;
1957 x_ProjFunc_Inv_Exchange_Rate := NULL;
1958 x_ProjFunc_Bill_Amount := NULL;
1959 x_ProjFunc_Rev_Rate_Date := NULL;
1960 x_Projfunc_Rev_Exchange_Rate := NULL;
1961 x_ProjFunc_Revenue_Amount := NULL;
1962
1963 x_Funding_Rate_Type := l_funding_rate_type;
1964 x_Funding_Rate_Date := NULL;
1965 x_Funding_Exchange_Rate := NULL;
1966
1967 IF ( BillInfoRec.Bill_Currency_Code <> l_invproc_currency_code ) THEN
1968 IF ( l_invproc_currency_type = 'PROJECT_CURRENCY' ) THEN
1969 x_Invproc_Currency_Code := l_invproc_currency_code;
1970 x_Invproc_Rate_Type := l_project_bil_rate_type;
1971 ELSIF ( l_invproc_currency_type = 'PROJFUNC_CURRENCY' ) THEN
1972 x_Invproc_Currency_Code := l_invproc_currency_code;
1973 x_Invproc_Rate_Type := l_projfunc_bil_rate_type;
1974 ELSIF ( l_invproc_currency_type = 'FUNDING_CURRENCY' ) THEN
1975 x_Invproc_Currency_Code := NULL;
1976 x_Invproc_Rate_Type := l_funding_rate_type;
1977 END IF;
1978 ELSE
1979 x_Invproc_Currency_Code := l_invproc_currency_code;
1980 x_Invproc_Rate_Type := NULL;
1981 END IF;
1982 x_Invproc_Rate_Date := NULL;
1983 x_Invproc_Exchange_Rate := NULL;
1984
1985 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1986 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Inv Proc Currency = ' || x_invproc_currency_code);
1987 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Inv Proc Rate Type = ' || x_invproc_rate_type);
1988 END IF;
1989 x_Revproc_Currency_Code := l_revproc_currency_code;
1990 x_Revproc_Rate_Type := x_projfunc_rate_type;
1991 x_Revproc_Rate_Date := NULL;
1992 x_Revproc_Exchange_Rate := NULL;
1993
1994 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1995 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Rev Proc Currency = ' || x_revproc_currency_code);
1996 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Rev Proc Rate Type = ' || x_revproc_rate_type);
1997 END IF;
1998 x_Inv_Gen_Rejection_Code := NULL;
1999
2000 EXCEPTION
2001 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2002 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2003 FND_MSG_PUB.add_exc_msg
2004 ( p_pkg_name => G_Pkg_Name
2005 , p_procedure_name => 'POPULATE_MC_COLUMNS' );
2006 END IF;
2007
2008 WHEN OTHERS THEN
2009 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2010 FND_MSG_PUB.add_exc_msg
2011 ( p_pkg_name => G_Pkg_Name
2012 , p_procedure_name => 'POPULATE_MC_COLUMNS' );
2013 END IF;
2014
2015 END Populate_Mc_Columns;
2016
2017
2018
2019 END OKE_DELIVERABLE_BILLING_PVT;