[Home] [Help]
PACKAGE BODY: APPS.EAM_WORKORDERBILLING_PVT
Source
1 PACKAGE BODY EAM_WorkOrderBilling_PVT AS
2 /* $Header: EAMVWOBB.pls 120.4 2006/09/01 22:03:51 anjgupta noship $ */
3
4
5 -- Start of comments
6 -- API name : insert_AR_Interface
7 -- Type : Private.
8 -- Function :
9 -- Pre-reqs : None.
10 -- Parameters :
11 -- IN : p_api_version IN NUMBER Required
12 -- p_init_msg_list IN VARCHAR2 Optional
13 -- Default = FND_API.G_FALSE
14 -- p_commit IN VARCHAR2 Optional
15 -- Default = FND_API.G_FALSE
16 -- p_validation_level IN NUMBER Optional
17 -- Default = FND_API.G_VALID_LEVEL_FULL
18 -- parameter1
19 -- parameter2
20 -- .
21 -- .
22 -- OUT : x_return_status OUT VARCHAR2(1)
23 -- x_msg_count OUT NUMBER
24 -- x_msg_data OUT VARCHAR2(2000)
25 -- parameter1
26 -- parameter2
27 -- .
28 -- .
29 -- Version : Current version x.x
30 -- Changed....
31 -- previous version y.y
32 -- Changed....
33 -- .
34 -- .
35 -- previous version 2.0
36 -- Changed....
37 -- Initial version 1.0
38 --
39 -- Notes : Note text
40 --
41 -- End of comments
42
43 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_WorkOrderBilling_PVT';
44
45 PROCEDURE insert_AR_Interface
46 ( p_api_version IN NUMBER ,
47 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
48 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
49 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
50 x_return_status OUT NOCOPY VARCHAR2 ,
51 x_msg_count OUT NOCOPY NUMBER ,
52 x_msg_data OUT NOCOPY VARCHAR2 ,
53 p_ra_line IN WO_Billing_RA_Rec_Type
54 )
55
56 IS
57
58 l_api_name CONSTANT VARCHAR2(30) := 'insert_AR_Interface';
59 l_api_version CONSTANT NUMBER := 1.0;
60 l_invoice_num NUMBER;
61 l_set_of_books NUMBER;
62 l_uom_name VARCHAR2(25);
63 l_desc_of_invoice VARCHAR2(240);
64 l_count NUMBER;
65 l_term_id NUMBER;
66 l_business_group_id NUMBER;
67 l_rounded_amount NUMBER;
68 l_rounded_unit_price NUMBER;
69 l_rounded_conv_rate NUMBER;
70 l_batch_source_name VARCHAR2(50);
71 l_stmt number :=0;
72 l_a_count number;
73 l_ou_id number;
74
75 BEGIN
76
77 l_stmt := 10;
78 -- Standard Start of API savepoint
79 SAVEPOINT insert_AR_Interface_PVT;
80 -- Standard call to check for call compatibility.
81 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
82 p_api_version ,
83 l_api_name ,
84 G_PKG_NAME )
85 THEN
86 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
87 END IF;
88 -- Initialize message list if p_init_msg_list is set to TRUE.
89 IF FND_API.to_Boolean( p_init_msg_list ) THEN
90 FND_MSG_PUB.initialize;
91 END IF;
92 -- Initialize API return status to success
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 -- API body
96
97 -- validation 0: All required fields have to be present.
98 if (p_ra_line.wip_entity_id is null) or
99 (p_ra_line.wip_entity_name is null) or
100 (p_ra_line.currency_code is null) or
101 (p_ra_line.invoice_num is null) or
102 (p_ra_line.line_num is null)
103 then
104 FND_MESSAGE.SET_NAME('EAM', 'EAM_NOT_ENOUGH_PARAMS');
105 FND_MSG_PUB.Add;
106 RAISE FND_API.G_EXC_ERROR;
107 end if;
108
109 l_stmt := 20;
110
111 /*CBOPPANA - FOR BUG 3050249 .
112
113 COMMENTED THE FOLLOWING CODE AND ADDED ANOTHER COLUMN 'PRIMARY_SALESREP_NUMBER' IN THE INSERT STATEMENT TO ENTER ALWAYS -3 INTO AR INTERFACE TABLES
114
115 EFFECT : EAM WILL NOT CHECK IF THE 'REQUIRE SALESREP 'OPTION IS SET TO YES/NO
116 */
117
118
119 -- validation 2: customer chosen should exist in the table 'hz_cust_accounts'.
120 select count(*) into l_count
121 from hz_cust_accounts
122 where cust_account_id=p_ra_line.customer_id;
123
124 if (l_count=0) then
125 FND_MESSAGE.SET_NAME('EAM', 'EAM_CUSTOMER_NOT_EXIST');
126 FND_MSG_PUB.Add;
127 RAISE FND_API.G_EXC_ERROR;
128 end if;
129
130 -- validation 3: unit selling price * quantity = amount
131 if not (p_ra_line.unit_selling_price * p_ra_line.quantity = p_ra_line.billed_amount)
132 then
133 FND_MESSAGE.SET_NAME('EAM', 'EAM_RA_WRONG_AMOUNT');
134 FND_MSG_PUB.Add;
135 RAISE FND_API.G_EXC_ERROR;
136 end if;
137
138 l_stmt := 30;
139
140 -- find the necessary data before inserting into the table
141
142 begin
143
144 SELECT org_information1, to_number(ORG_INFORMATION3)
145 INTO l_set_of_books, l_ou_id
146 FROM hr_organization_information
147 WHERE org_information_context = 'Accounting Information'
148 AND organization_id = p_ra_line.org_id;
149
150
151
152
153 exception
154 when no_data_found then
155 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_ORG_ID');
156 FND_MSG_PUB.Add;
157 RAISE FND_API.G_EXC_ERROR;
158 end;
159
160 -- get desc. of invoice
161 l_desc_of_invoice:='Invoice for work order #' || p_ra_line.wip_entity_id || ', ' || p_ra_line.wip_entity_name;
162
163 -- get uom name
164 begin
165 select unit_of_measure into l_uom_name
166 from mtl_units_of_measure
167 where uom_code = p_ra_line.uom_code;
168 exception
169 when no_data_found then
170 FND_MESSAGE.SET_NAME ('EAM', 'EAM_INVALID_UOM_CODE');
171 FND_MSG_PUB.Add;
172 RAISE FND_API.G_EXC_ERROR;
173 end;
174
175 l_stmt := 40;
176 -- get term id
177 begin
178 select nvl(payment_term_id, 4)
179 into l_term_id
180 from hz_cust_accounts
181 where cust_account_id = p_ra_line.customer_id;
182
183 exception
184 when others then
185 l_term_id :=4;
186 end;
187
188
189
190 -- get rounded amount
191 select decode(c1.minimum_accountable_unit,
192 NULL, round(p_ra_line.billed_amount, c1.precision),
193 round(p_ra_line.billed_amount/c1.minimum_accountable_unit)
194 * c1.minimum_accountable_unit )
195 into l_rounded_amount
196 from
197 fnd_currencies c1
198 where
199 c1.currency_code = p_ra_line.currency_code;
200
201 -- get rounded unit price
202 select decode(c1.minimum_accountable_unit,
203 NULL, round(p_ra_line.unit_selling_price, c1.precision),
204 round(p_ra_line.unit_selling_price/c1.minimum_accountable_unit)
205 * c1.minimum_accountable_unit )
206 into l_rounded_unit_price
207 from
208 fnd_currencies c1
209 where
210 c1.currency_code = p_ra_line.currency_code;
211
212 -- get rounded conversion rate
213 select decode(c1.minimum_accountable_unit,
214 NULL, round(nvl(p_ra_line.conversion_rate, 1), c1.precision),
215 round(nvl(p_ra_line.conversion_rate,1)/c1.minimum_accountable_unit)
216 * c1.minimum_accountable_unit )
217 into l_rounded_conv_rate
218 from
219 fnd_currencies c1
220 where
221 c1.currency_code = p_ra_line.currency_code;
222
223 l_stmt := 45;
224
225 /* Added Code for bug # 3680865 - Start */
226 -- get the BATCH SOURCE NAME from the ra_interfaces_batches corresponging to batch_source_id=25 for work order billing
227
228
229
230
231 SELECT NAME
232 INTO l_batch_source_name
233 FROM RA_BATCH_SOURCES_all
234 WHERE BATCH_SOURCE_ID=25
235 and org_id = l_ou_id; --For Work Order Billing
236
237
238
239 l_stmt := 50;
240 -- insert into the table.
241 /*CBOPPANA 3050249 ALWAYS INSERT VALUE -3 FOR COLUMN 'PRIMARY_SALESREP_NUMBER' */
242
243 insert into ra_interface_lines_all
244 (interface_line_context,
245 interface_line_Attribute1,
246 interface_line_Attribute2,
247 interface_line_Attribute3,
248 interface_line_Attribute4,
249 interface_line_Attribute5,
250 interface_line_Attribute6,
251 interface_line_Attribute7,
252 interface_line_Attribute8,
253 batch_source_name,
254 set_of_books_id,
255 line_type,
256 description,
257 currency_code,
258 amount,
259 cust_trx_type_name,
260 cust_trx_type_id,
261 term_id,
262 orig_system_bill_customer_id,
263 orig_system_bill_address_id,
264 conversion_type,
265 conversion_date,
266 conversion_rate,
267 inventory_item_id,
268 uom_code,
269 uom_name,
270 tax_exempt_flag,
271 org_id,
272 quantity,
273 unit_selling_price,
274 created_by,
275 creation_date,
276 last_updated_by,
277 last_update_date,
278 last_update_login,
279 primary_salesrep_number)
280 values
281 ('Work Order Billing',
282 p_ra_line.wip_entity_id,
283 p_ra_line.wip_entity_name,
284 p_ra_line.invoice_num,
285 p_ra_line.line_num,
286 p_ra_line.work_request,
287 null,
288 p_ra_line.project_id,
289 p_ra_line.task_id,
290 l_batch_source_name, --bug 3680865
291 l_set_of_books,
292 'LINE',
293 l_desc_of_invoice,
294 p_ra_line.currency_code,
295 --p_ra_line.billed_amount,
296 l_rounded_amount,
297 'INVOICE',
298 1,
299 l_term_id,
300 p_ra_line.customer_id,
301 p_ra_line.bill_to_address,
302 nvl(p_ra_line.conversion_type, 'User'),
303 p_ra_line.conversion_date,
304 --nvl(p_ra_line.conversion_rate, 1),
305 l_rounded_conv_rate,
306 p_ra_line.billed_inventory_item_id,
307 p_ra_line.uom_code,
308 l_uom_name,
309 'S',
310 l_ou_id,
311 p_ra_line.quantity,
312 l_rounded_unit_price,
313 fnd_global.user_id,
314 sysdate,
315 fnd_global.user_id,
316 sysdate ,
317 fnd_global.login_id,
318 TO_CHAR(-3)
319 );
320
321
322 l_stmt := 60;
323 -- Bug 3050249: DGUPTA: Need to enter sales credit information for -3
324 insert into RA_INTERFACE_SALESCREDITS_ALL
325 (interface_line_context,
326 interface_line_Attribute1,
327 interface_line_Attribute2,
328 interface_line_Attribute3,
329 interface_line_Attribute4,
330 interface_line_Attribute5,
331 interface_line_Attribute6,
332 interface_line_Attribute7,
333 interface_line_Attribute8,
334 salesrep_number,
335 sales_credit_type_name,
336 sales_credit_percent_split)
337 values(
338 'Work Order Billing' ,
339 p_ra_line.wip_entity_id,
340 p_ra_line.wip_entity_name,
341 p_ra_line.invoice_num,
342 p_ra_line.line_num,
343 p_ra_line.work_request,
344 null,
345 p_ra_line.project_id,
346 p_ra_line.task_id,
347 '-3',
348 'Quota Sales Credit',
349 100);
350
351
352 l_stmt := 70;
353
354 -- End of API body.
355
356 -- Standard check of p_commit.
357 IF FND_API.To_Boolean( p_commit ) THEN
358 COMMIT WORK;
359 END IF;
360 -- Standard call to get message count and if count is 1, get message info.
361 FND_MSG_PUB.Count_And_Get
362 ( p_count => x_msg_count ,
363 p_data => x_msg_data
364 );
365 EXCEPTION
366 WHEN FND_API.G_EXC_ERROR THEN
367 ROLLBACK TO insert_AR_Interface_PVT;
368 x_return_status := FND_API.G_RET_STS_ERROR ;
369 FND_MSG_PUB.Count_And_Get
370 ( p_count => x_msg_count ,
371 p_data => x_msg_data
372 );
373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
374 ROLLBACK TO insert_AR_Interface_PVT;
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
376 FND_MSG_PUB.Count_And_Get
377 ( p_count => x_msg_count ,
378 p_data => x_msg_data
379 );
380 WHEN OTHERS THEN
381 ROLLBACK TO insert_AR_Interface_PVT;
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 IF FND_MSG_PUB.Check_Msg_Level
384 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
385 THEN
386 FND_MSG_PUB.Add_Exc_Msg
387 ( G_PKG_NAME ,
388 l_api_name
389 );
390 END IF;
391 FND_MSG_PUB.Count_And_Get
392 ( p_count => x_msg_count ,
393 p_data => x_msg_data
394 );
395 END insert_AR_Interface;
396
397
398
399 PROCEDURE insert_WOB_Table
400 ( p_api_version IN NUMBER ,
401 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
402 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
403 p_validation_level IN NUMBER :=
404 FND_API.G_VALID_LEVEL_FULL ,
405 x_return_status OUT NOCOPY VARCHAR2 ,
406 x_msg_count OUT NOCOPY NUMBER ,
407 x_msg_data OUT NOCOPY VARCHAR2 ,
408 p_wob_rec IN WO_Billing_Rec_Type
409 ) IS
410 l_api_name CONSTANT VARCHAR2(30) := 'insert_WO_Table';
411 l_api_version CONSTANT NUMBER := 1.0;
412 l_rounded_amount NUMBER;
413 l_rounded_unit_price NUMBER;
414 l_rounded_conv_rate NUMBER;
415
416 BEGIN
417 -- Standard Start of API savepoint
418 SAVEPOINT insert_WO_Table_PVT;
419 -- Standard call to check for call compatibility.
420 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
421 p_api_version ,
422 l_api_name ,
423 G_PKG_NAME )
424 THEN
425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426 END IF;
427 -- Initialize message list if p_init_msg_list is set to TRUE.
428 IF FND_API.to_Boolean( p_init_msg_list ) THEN
429 FND_MSG_PUB.initialize;
430 END IF;
431 -- Initialize API return status to success
432 x_return_status := FND_API.G_RET_STS_SUCCESS;
433 -- API body
434
435 -- check all the "not null" fields
436 if (p_wob_rec.organization_id is null) or
437 --(p_wob_rec.customer_id is null) or
438 --(p_wob_rec.bill_to_address_id is null) or
439 (p_wob_rec.wip_entity_id is null) or
440 (p_wob_rec.billed_inventory_item_id is null) or
441 (p_wob_rec.billed_uom_code is null) or
442 (p_wob_rec.billed_amount is null) or
443 --(p_wob_rec.invoice_trx_number is null) or
444 --(p_wob_rec.invoice_line_number is null) or
445 (p_wob_rec.currency_code is null)
446 then
447 FND_MESSAGE.SET_NAME('EAM', 'EAM_WRONG_PARAM_COST_PL');
448 FND_MSG_PUB.Add;
452 -- get rounded amount
449 RAISE FND_API.G_EXC_ERROR;
450 end if;
451
453 select decode(c1.minimum_accountable_unit,
454 NULL, round(p_wob_rec.billed_amount, c1.precision),
455 round(p_wob_rec.billed_amount/c1.minimum_accountable_unit)
456 * c1.minimum_accountable_unit )
457 into l_rounded_amount
458 from
459 fnd_currencies c1
460 where
461 c1.currency_code = p_wob_rec.currency_code;
462
463 -- get rounded cost_or_list_price
464 select decode(c1.minimum_accountable_unit,
465 NULL, round(p_wob_rec.cost_or_listprice, c1.precision),
466 round(p_wob_rec.cost_or_listprice/c1.minimum_accountable_unit)
467 * c1.minimum_accountable_unit )
468 into l_rounded_unit_price
469 from
470 fnd_currencies c1
471 where
472 c1.currency_code = p_wob_rec.currency_code;
473
474 -- get rounded conversion rate
475 select decode(c1.minimum_accountable_unit,
476 NULL, round(p_wob_rec.conversion_rate, c1.precision),
477 round(p_wob_rec.conversion_rate/c1.minimum_accountable_unit)
478 * c1.minimum_accountable_unit )
479 into l_rounded_conv_rate
480 from
481 fnd_currencies c1
482 where
483 c1.currency_code = p_wob_rec.currency_code;
484
485
486 insert into eam_work_order_bills
487 (
488 ORGANIZATION_ID ,
489 CUSTOMER_ID ,
490 BILL_TO_ADDRESS_ID ,
491 WIP_ENTITY_ID ,
492 OPERATION_SEQ_NUM ,
493 INVENTORY_ITEM_ID ,
494 RESOURCE_ID ,
495 BILLED_INVENTORY_ITEM_ID ,
496 BILLED_UOM_CODE ,
497 BILLED_QUANTITY ,
498 PRICE_LIST_HEADER_ID ,
499 COST_TYPE_ID ,
500 COST_OR_LISTPRICE ,
501 COSTPLUS_PERCENTAGE ,
502 BILLED_AMOUNT ,
503 INVOICE_TRX_NUMBER ,
504 INVOICE_LINE_NUMBER ,
505 CURRENCY_CODE ,
506 CONVERSION_RATE ,
507 CONVERSION_TYPE_CODE ,
508 CONVERSION_RATE_DATE ,
509 PROJECT_ID ,
510 TASK_ID ,
511 WORK_REQUEST_ID ,
512 PA_EVENT_ID ,
513 BILLING_BASIS,
514 BILLING_METHOD,
515 LAST_UPDATE_DATE ,
516 LAST_UPDATED_BY ,
517 CREATION_DATE ,
518 CREATED_BY ,
519 LAST_UPDATE_LOGIN
520 )
521 values
522 (
523 p_wob_rec.organization_id,
524 p_wob_rec.customer_id,
525 p_wob_rec.bill_to_address_id,
526 p_wob_rec.wip_entity_id,
527 p_wob_rec.operation_seq_num,
528 p_wob_rec.inventory_item_id,
529 p_wob_rec.resource_id,
530 p_wob_rec.billed_inventory_item_id,
531 p_wob_rec.billed_uom_code,
532 p_wob_rec.billed_quantity,
533 p_wob_rec.price_list_header_id,
534 p_wob_rec.cost_type_id,
535 -- p_wob_rec.cost_or_listprice,
536 l_rounded_unit_price,
537 p_wob_rec.costplus_percentage,
538 -- p_wob_rec.billed_amount,
539 l_rounded_amount,
540 p_wob_rec.invoice_trx_number,
541 p_wob_rec.invoice_line_number,
542 p_wob_rec.currency_code,
543 -- p_wob_rec.conversion_rate,
544 l_rounded_conv_rate,
545 p_wob_rec.conversion_type_code,
546 p_wob_rec.conversion_rate_date,
547 p_wob_rec.project_id,
548 p_wob_rec.task_id,
549 p_wob_rec.work_request_id,
550 p_wob_rec.pa_event_id,
551 p_wob_rec.billing_basis,
552 p_wob_rec.billing_method,
553 sysdate,
554 fnd_global.user_id,
555 sysdate ,
556 fnd_global.user_id,
557 fnd_global.login_id
558 );
559
560
561
562 -- End of API body.
563 -- Standard check of p_commit.
564 IF FND_API.To_Boolean( p_commit ) THEN
565 COMMIT WORK;
566 END IF;
567 -- Standard call to get message count and if count is 1, get message info.
568 FND_MSG_PUB.Count_And_Get
569 ( p_count => x_msg_count ,
570 p_data => x_msg_data
571 );
572 EXCEPTION
573 WHEN FND_API.G_EXC_ERROR THEN
574 ROLLBACK TO insert_WO_Table_PVT;
575 x_return_status := FND_API.G_RET_STS_ERROR ;
576 FND_MSG_PUB.Count_And_Get
577 ( p_count => x_msg_count ,
578 p_data => x_msg_data
579 );
580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
581 ROLLBACK TO insert_WO_Table_PVT;
582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
583 FND_MSG_PUB.Count_And_Get
584 ( p_count => x_msg_count ,
585 p_data => x_msg_data
586 );
587 WHEN OTHERS THEN
588 ROLLBACK TO insert_WO_Table_PVT;
589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
590 IF FND_MSG_PUB.Check_Msg_Level
591 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
592 THEN
593 FND_MSG_PUB.Add_Exc_Msg
594 ( G_PKG_NAME ,
595 l_api_name
596 );
597 END IF;
601 );
598 FND_MSG_PUB.Count_And_Get
599 ( p_count => x_msg_count ,
600 p_data => x_msg_data
602 END insert_WOB_Table;
603
604
605 /*
606 PROCEDURE insert_PAEvent_Table
607 ( p_api_version IN NUMBER
608 ,
609 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
610 p_commit IN VARCHAR2 := FND_API.G_FALSE
611 ,
612 p_validation_level IN NUMBER :=
613 FND_API.G_VALID_LEVEL_FULL
614 ,
615 x_return_status OUT NOCOPY VARCHAR2 ,
616 x_msg_count OUT NOCOPY NUMBER
617 ,
618 x_msg_data OUT NOCOPY VARCHAR2
619 ,
620 p_pa_rec IN WO_Billing_PA_Event_Rec_Type
621 )
622 IS
623
624 l_api_name CONSTANT VARCHAR2(30) := 'insert_PAEvent_Table';
625 l_api_version CONSTANT NUMBER := 1.0;
626 --l_event_num number;
627 --l_event_id number;
628
629 l_multi_currency_billing_flag VARCHAR2(15);
630 l_baseline_funding_flag VARCHAR2(15);
631 l_revproc_currency_code VARCHAR2(15);
632 l_invproc_currency_code VARCHAR2(30);
633 l_project_currency_code VARCHAR2(15);
634 l_project_bil_rate_date_code VARCHAR2(30);
635 l_project_bil_rate_type VARCHAR2(30);
636 l_project_bil_rate_date DATE;
637 l_project_bil_exchange_rate NUMBER;
638 l_projfunc_currency_code VARCHAR2(15);
639 l_projfunc_bil_rate_date_code VARCHAR2(30);
640 l_projfunc_bil_rate_type VARCHAR2(30);
641 l_invproc_currency_type VARCHAR2(30);
642 l_projfunc_bil_rate_date DATE;
643 l_projfunc_bil_exchange_rate NUMBER;
644 l_funding_rate_date_code VARCHAR2(30);
645 l_funding_rate_type VARCHAR2(30);
646 l_funding_rate_date DATE;
647 l_funding_exchange_rate NUMBER;
648 l_return_status VARCHAR2(30);
649 l_msg_count NUMBER;
650 l_msg_data VARCHAR2(30);
651 l_rounded_amount NUMBER;
652
653
654 BEGIN
655 -- Standard Start of API savepoint
656 SAVEPOINT insert_PAEvent_Table_PVT;
657 -- Standard call to check for call compatibility.
658 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
659 p_api_version ,
660 l_api_name ,
661 G_PKG_NAME )
662 THEN
663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
664 END IF;
665 -- Initialize message list if p_init_msg_list is set to TRUE.
666 IF FND_API.to_Boolean( p_init_msg_list ) THEN
667 FND_MSG_PUB.initialize;
668 END IF;
669 -- Initialize API return status to success
670 x_return_status := FND_API.G_RET_STS_SUCCESS;
671 -- API body
672
673 /*
674 -- get event_num
675
676 SELECT NVL(MAX(Event_Num) , 0) + 1 into l_event_num
677 FROM pa_events
678 WHERE Project_ID = p_pa_rec.project_id
679 AND (( Task_ID IS NULL and p_pa_rec.task_id is null) OR Task_ID = p_pa_rec.task_id);
680
681 -- get event_id
682
683 SELECT pa_events_s.nextval into l_event_id
684 FROM dual;
685 *######/
686
687 -- get pa default values
688 --dbms_output.put_line('before defaults');
689
690 PA_MULTI_CURRENCY_BILLING.get_project_defaults
691 ( P_project_id => p_pa_rec.project_id
692 , X_multi_currency_billing_flag => l_multi_currency_billing_flag
693 , X_baseline_funding_flag => l_baseline_funding_flag
694 , X_revproc_currency_code => l_revproc_currency_code
695 , X_invproc_currency_type => l_invproc_currency_type
696 , X_invproc_currency_code => l_invproc_currency_code
697 , X_project_currency_code => l_project_currency_code
698 , X_project_bil_rate_date_code => l_project_bil_rate_date_code
699 , X_project_bil_rate_type => l_project_bil_rate_type
700 , X_project_bil_rate_date => l_project_bil_rate_date
701 , X_project_bil_exchange_rate => l_project_bil_exchange_rate
702 , X_projfunc_currency_code => l_projfunc_currency_code
703 , X_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code
704 , X_projfunc_bil_rate_type => l_projfunc_bil_rate_type
705 , X_projfunc_bil_rate_date => l_projfunc_bil_rate_date
706 , X_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate
707 , X_funding_rate_date_code => l_funding_rate_date_code
708 , X_funding_rate_type => l_funding_rate_type
709 , X_funding_rate_date => l_funding_rate_date
710 , X_funding_exchange_rate => l_funding_exchange_rate
711 , X_return_status => l_return_status
712 , X_msg_count => l_msg_count
713 , X_msg_data => l_msg_data);
714
715 if (l_return_status='E') then
716 FND_MESSAGE.SET_NAME('EAM', 'EAM_INVALID_PROJ_ID');
717 FND_MSG_PUB.Add;
718 RAISE FND_API.G_EXC_ERROR;
719 end if;
720
721
722 -- Get the rounded amount
723
724 select decode(c1.minimum_accountable_unit,
725 NULL, round(p_pa_rec.bill_trans_bill_amount, c1.precision),
726 round(p_pa_rec.bill_trans_bill_amount/c1.minimum_accountable_unit)
727 * c1.minimum_accountable_unit )
728 into l_rounded_amount
729 from
730 fnd_currencies c1
731 where
732 c1.currency_code = p_pa_rec.billing_currency_code;
733
734
735 --dbms_output.put_line('got defaults');
736 -- insert into pa_events
737 insert into pa_events
738 (
739 task_id,
740 event_num,
741 event_type,
742 description,
743 bill_amount,
744 revenue_amount,
745 revenue_distributed_flag,
746 bill_hold_flag,
747 project_id,
748 organization_id,
749 calling_place,
750 calling_process,
751 event_id,
752 reference1,
753 reference2,
754 reference3,
755 reference4,
756 billed_flag,
757 bill_trans_currency_code,
758 bill_trans_bill_amount,
759 bill_trans_rev_amount,
760 project_currency_code,
761 projfunc_currency_code,
762 funding_rate_type,
763 funding_rate_date,
764 funding_exchange_rate,
765 revproc_currency_code,
766 invproc_currency_code,
767 completion_date,
768 last_update_date,
769 last_updated_by,
770 creation_date,
771 created_by,
772 last_update_login)
773 values
774 (
775 p_pa_rec.task_id,
776 p_pa_rec.event_num,
777 'Manual',
778 'Event for Work Order #' || p_pa_rec.wip_entity_id || ', ' || p_pa_rec.wip_entity_name,
779 0,
780 0,
781 'N',
782 'N',
783 p_pa_rec.project_id,
784 p_pa_rec.organization_id,
785 'EAM',
786 'Work Order Billing',
787 p_pa_rec.event_id,
788 p_pa_rec.wip_entity_id,
789 p_pa_rec.wip_entity_name,
790 p_pa_rec.work_request_id,
791 p_pa_rec.service_request_id,
792 'N',
793 p_pa_rec.billing_currency_code,
794 --p_pa_rec.bill_trans_bill_amount,
795 --p_pa_rec.bill_trans_rev_amount,
796 l_rounded_amount,
797 l_rounded_amount,
798 l_project_currency_code,
799 l_projfunc_currency_code,
800 l_funding_rate_type,
801 l_funding_rate_date,
802 l_funding_exchange_rate,
803 l_revproc_currency_code,
804 l_invproc_currency_code,
805 sysdate,
806 sysdate ,
807 fnd_global.user_id,
808 sysdate,
809 fnd_global.user_id,
810 fnd_global.login_id
811 );
812
813 --dbms_output.put_line('finished inserting');
814
815
816 -- End of API body.
817 -- Standard check of p_commit.
818 IF FND_API.To_Boolean( p_commit ) THEN
819 COMMIT WORK;
820 END IF;
821 -- Standard call to get message count and if count is 1, get message info.
822 FND_MSG_PUB.Count_And_Get
823 ( p_count => x_msg_count ,
824 p_data => x_msg_data
825 );
826 EXCEPTION
827 WHEN FND_API.G_EXC_ERROR THEN
828 ROLLBACK TO insert_PAEvent_Table_PVT;
829 x_return_status := FND_API.G_RET_STS_ERROR ;
830 FND_MSG_PUB.Count_And_Get
831 ( p_count => x_msg_count ,
832 p_data => x_msg_data
833 );
834 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
835 ROLLBACK TO insert_PAEvent_Table_PVT;
836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
837 FND_MSG_PUB.Count_And_Get
838 ( p_count => x_msg_count ,
839 p_data => x_msg_data
840 );
841 WHEN OTHERS THEN
842 ROLLBACK TO insert_PAEvent_Table_PVT;
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
844 IF FND_MSG_PUB.Check_Msg_Level
845 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
846 THEN
847 FND_MSG_PUB.Add_Exc_Msg
848 ( G_PKG_NAME ,
849 l_api_name
850 );
851 END IF;
852 FND_MSG_PUB.Count_And_Get
853 ( p_count => x_msg_count ,
854 p_data => x_msg_data
855 );
856 END insert_PAEvent_Table;
857 */
858
859
860
861
862
863 END EAM_WorkOrderBilling_PVT;
864