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