1 PACKAGE BODY CSD_CHARGE_LINE_UTIL AS
2 /* $Header: csdvclub.pls 120.5 2008/03/11 22:26:24 rfieldma ship $ */
3 --
4 -- Package name : CSD_CHARGE_LINE_UTIL
5 -- Purpose : This package contains the utilities for handling
6 -- price list and contract for charge lines.
7 -- History :
8 -- Version Date Name Description
9 -- 115.9 10/24/02 glam Created.
10
11
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_CHARGE_LINE_UTIL';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvclub.pls';
14 g_debug NUMBER := csd_gen_utility_pvt.g_debug_level;
15
16 /*----------------------------------------------------------------*/
17 /* function name: Get_PLCurrCode */
18 /* description : Gets the currency for a price list */
19 /* */
20 /* p_price_list_id Price List ID to get currency */
21 /* */
22 /*----------------------------------------------------------------*/
23 FUNCTION Get_PLCurrCode (
24 p_price_list_id IN NUMBER
25 )
26 RETURN VARCHAR2
27 IS
28 l_curr_code VARCHAR2(15) := NULL;
29
30 BEGIN
31
32 -- get currency code from price list
33 SELECT currency_code
34 INTO l_curr_code
35 FROM qp_list_headers_b
36 WHERE list_header_id = p_price_list_id;
37
38 IF (l_curr_code IS NOT NULL) THEN
39 RETURN l_curr_code;
40 ELSE
41 RAISE no_data_found;
42 END IF;
43
44 EXCEPTION
45 WHEN no_data_found THEN
46 FND_MESSAGE.SET_NAME('CSD','CSD_API_PL_CURR_CODE');
47 FND_MESSAGE.SET_TOKEN('PRICE_LIST_ID',p_price_list_id);
48 FND_MSG_PUB.Add;
49 -- saupadhy 2826127
50 RETURN NULL ; -- RETURN -1
51
52 WHEN others THEN
53 FND_MESSAGE.SET_NAME('CSD','CSD_API_PL_CURR_CODE');
54 FND_MESSAGE.SET_TOKEN('PRICE_LIST_ID',p_price_list_id);
55 FND_MSG_PUB.Add;
56 -- saupadhy 2826127
57 RETURN NULL ; -- RETURN -1
58
59 END Get_PLCurrCode;
60
61 /*----------------------------------------------------------------*/
62 /* procedure name: Get_DefaultPriceList */
63 /* description : Gets the price list from contract (default */
64 /* contract if null), if not, default price list */
65 /* from profile option. */
66 /* */
67 /* p_api_version Standard IN param */
68 /* p_init_msg_list Standard IN param */
69 /* p_repair_type_id Repair Type ID */
70 /* p_repair_line_id Repair Line ID */
71 /* p_contract_line_id Contract Line ID */
72 /* p_currency_code RO Currency */
73 /* x_return_status Standard OUT param */
74 /* x_msg_count Standard OUT param */
75 /* x_msg_data Standard OUT param */
76 /* */
77 /*----------------------------------------------------------------*/
78 PROCEDURE Get_DefaultPriceList
79 (
80 p_api_version IN NUMBER,
81 p_init_msg_list IN VARCHAR2,
82 p_repair_line_id IN NUMBER,
83 p_repair_type_id IN NUMBER,
84 p_contract_line_id IN NUMBER,
85 p_currency_code IN VARCHAR2,
86 x_contract_validated OUT NOCOPY BOOLEAN,
87 x_default_pl_id OUT NOCOPY NUMBER,
88 x_return_status OUT NOCOPY VARCHAR2,
89 x_msg_count OUT NOCOPY NUMBER,
90 x_msg_data OUT NOCOPY VARCHAR2
91 )
92
93 IS
94 l_api_name CONSTANT VARCHAR2(30) := 'Get_DefaultPriceList';
95 l_api_version CONSTANT NUMBER := 1.0;
96
97 l_default_contract NUMBER := NULL;
98 l_contract_line_id NUMBER := NULL;
99 l_contract_pl_id NUMBER := NULL;
100 l_billing_pl_id NUMBER := NULL;
101 l_contract_validated BOOLEAN;
102 l_default_pl_id NUMBER := NULL;
103
104 -- gilam: bug 3542319 - added flag to indicate if contract is used
105 l_use_contract_pl BOOLEAN;
106
107 -- gilam: bug 3542319 - added cursor to get repair type price list
108 CURSOR c_rt_pl_id(p_repair_type_id number) IS
109 SELECT price_list_header_id
110 FROM csd_repair_types_b
111 WHERE repair_type_id = p_repair_type_id;
112
113 BEGIN
114
115 --debug msg
116 IF (g_debug > 0 ) THEN
117 csd_gen_utility_pvt.ADD ('Get_DefaultPriceList Begin: p_repair_line_id ='|| p_repair_line_id);
118 END IF;
119
120 -- Standard Start of API savepoint
121 SAVEPOINT Get_DefaultPriceList;
122
123 -- Standard call to check for call compatibility.
124 IF NOT FND_API.Compatible_API_Call (l_api_version,
125 p_api_version,
126 l_api_name,
127 G_PKG_NAME)
128 THEN
129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
130 END IF;
131
132 -- Initialize message list if p_init_msg_list is set to TRUE.
133 IF FND_API.to_Boolean(p_init_msg_list) THEN
134 FND_MSG_PUB.initialize;
135 END IF;
136
137 -- Initialize API return status to success
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139
140 --
141 -- Begin API Body
142 --
143
144 IF (g_debug > 0 ) THEN
145 csd_gen_utility_pvt.dump_api_info ( p_pkg_name => G_PKG_NAME,
146 p_api_name => l_api_name );
147 END IF;
148
149 --debug msg
150 IF (g_debug > 0 ) THEN
151 csd_gen_utility_pvt.ADD ('Check required parameters and validate them');
152 END IF;
153
154 -- Check the required parameters
155 CSD_PROCESS_UTIL.Check_Reqd_Param
156 ( p_param_value => p_repair_line_id,
157 p_param_name => 'REPAIR_LINE_ID',
158 p_api_name => l_api_name);
159
160 CSD_PROCESS_UTIL.Check_Reqd_Param
161 ( p_param_value => p_repair_type_id,
162 p_param_name => 'REPAIR_TYPE_ID',
163 p_api_name => l_api_name);
164
165 CSD_PROCESS_UTIL.Check_Reqd_Param
166 ( p_param_value => p_currency_code,
167 p_param_name => 'CURRENCY_CODE',
168 p_api_name => l_api_name);
169
170 -- Validate the repair type ID
171 IF NOT( CSD_PROCESS_UTIL.Validate_repair_type_id ( p_repair_type_id => p_repair_type_id )) THEN
172 RAISE FND_API.G_EXC_ERROR;
173 END IF;
174
175 -- Validate the repair line ID
176 IF NOT( CSD_PROCESS_UTIL.Validate_rep_line_id ( p_repair_line_id => p_repair_line_id )) THEN
177 RAISE FND_API.G_EXC_ERROR;
178 END IF;
179
180
181 --debug msg
182 IF (g_debug > 0 ) THEN
183 csd_gen_utility_pvt.ADD ('Check required parameters and validation complete');
184 END IF;
185
186 -- If contract is passed in, get price list from contract and verify currency
187 -- If no contract is passed, get price list from default contract and verify currency
188 -- If currency is different from RO currency, contract price list will not be used
189 -- If contract does not have price list, get default price list set in profile option
190 -- If currency of default price list is different from RO currency, default price list will not be used
191 -- gilam: bug 3542319 - add repair type price list option
192 -- If default price list cannot be used or if user did not set default price list, get repair type price list
193 -- If repair type price list is not set or repair type price list has different currency from RO's, null will be returned
194
195 --debug msg
196 IF (g_debug > 0 ) THEN
197 csd_gen_utility_pvt.ADD ('Get price list from contract');
198 END IF;
199
200 -- no contract is passed in, get default contract
201 IF (p_contract_line_id IS NULL) THEN
202
203 l_contract_line_id := Get_DefaultContract(p_repair_line_id);
204 IF (l_contract_line_id = -1) THEN
205 -- gilam: bug 3542319 - remove raising the exception and set flag to false
206 --RAISE FND_API.G_EXC_ERROR;
207 l_use_contract_pl := FALSE;
208 --
209 END IF;
210
211 -- contract is passed in
212 ELSE
213
214 l_contract_line_id := p_contract_line_id;
215
216 END IF;
217
218 -- get contract price list
219 -- gilam: bug 3542319 - added check for -1 condition
220 IF (l_contract_line_id IS NOT NULL and l_contract_line_id <> -1) THEN
221
222 --debug msg
223 IF (g_debug > 0 ) THEN
224 csd_gen_utility_pvt.ADD ('Call Get_ContractPriceList API: l_contract_line_id ='|| l_contract_line_id);
225 END IF;
226
227 Get_ContractPriceList
228 (
229 p_api_version => l_api_version,
230 p_init_msg_list => 'T',
231 p_contract_line_id => l_contract_line_id,
232 p_repair_type_id => p_repair_type_id,
233 p_currency_code => p_currency_code,
234 x_contract_validated => l_contract_validated,
235 x_contract_pl_id => l_contract_pl_id,
236 x_billing_pl_id => l_billing_pl_id,
237 x_return_status => x_return_status,
238 x_msg_count => x_msg_count,
239 x_msg_data => x_msg_data
240 );
241
242 -- gilam: bug 3542319 - changed logic for contract price list
243 -- set it to false first, then change it to yes if contract price list is used
244 l_use_contract_pl := FALSE;
245
246 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
247 x_contract_validated := l_contract_validated;
248
249 IF (l_contract_validated) THEN
250 IF (l_contract_pl_id IS NOT NULL) THEN
251 l_use_contract_pl := TRUE;
252 x_default_pl_id := l_contract_pl_id;
253 ELSIF (l_billing_pl_id IS NOT NULL) THEN
254 l_use_contract_pl := TRUE;
255 x_default_pl_id := l_billing_pl_id;
256 END IF;
257 END IF;
258
259 END IF;
260 -- gilam: bug 3542319
261
262 END IF;
263
264 -- if no contract has passed in, or if there is contract and contract is valid and contract has no price list
265 -- or if there is error on the contract, then get default price list from profile option
266 -- if currency of default price list is different from RO currency, default price list will not be used
267
268 -- gilam: bug 3542319 - changed IF condition
269 --IF ((l_contract_line_id IS NULL) OR
270 -- (l_contract_validated AND l_contract_line_id IS NOT NULL AND l_contract_pl_id IS NULL)) THEN
271
272 IF NOT l_use_contract_pl THEN
273
274 --debug msg
275 IF (g_debug > 0 ) THEN
276 csd_gen_utility_pvt.ADD ('Get default price list from profile option');
277 END IF;
278
279 l_default_pl_id := Fnd_Profile.value('CSD_Default_Price_List');
280
281 IF (l_default_pl_id IS NOT NULL) THEN
282 IF (Validate_PriceList(l_default_pl_id, p_currency_code)) THEN
283 x_default_pl_id := l_default_pl_id;
284 END IF;
285 END IF;
286
287 -- gilam: bug 3542319 - added repair type price list default option
288 IF (x_default_pl_id IS NULL) THEN
289
290 open c_rt_pl_id(p_repair_type_id);
291 fetch c_rt_pl_id into l_default_pl_id;
292
293 -- if repair type price list is set
294 IF (c_rt_pl_id%FOUND) THEN
295 IF (Validate_PriceList(l_default_pl_id, p_currency_code)) THEN
296 x_default_pl_id := l_default_pl_id;
297 END IF;
298 END IF;
299
300 close c_rt_pl_id;
301
302 END IF;
303
304 END IF;
305 -- gilam; end bug 3542319
306
307
308 -- API body ends here
309
310 -- Standard call to get message count and IF count is get message info.
311 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
312 p_data => x_msg_data );
313
314 EXCEPTION
315
316 WHEN FND_API.G_EXC_ERROR THEN
317 ROLLBACK TO Get_DefaultPriceList;
318 x_return_status := FND_API.G_RET_STS_ERROR ;
319 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
320 p_data => x_msg_data);
321
322 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
323 ROLLBACK TO Get_DefaultPriceList;
324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
325 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
326 p_data => x_msg_data );
327
328 WHEN OTHERS THEN
329 ROLLBACK TO Get_DefaultPriceList;
330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
331 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
332 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
333 l_api_name );
334 END IF;
335 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
336 p_data => x_msg_data );
337
338 END Get_DefaultPriceList;
339
340
341 /*----------------------------------------------------------------*/
342 /* function name: Get_RO_PriceList */
343 /* description : Gets the price list header id for a repair order*/
344 /* */
345 /* p_repair_line_id Repair Line ID to get Price List */
346 /* */
347 /*----------------------------------------------------------------*/
348 FUNCTION Get_RO_PriceList
349 (
350 p_repair_line_id IN NUMBER
351 )
352 RETURN NUMBER
353 IS
354 l_price_list_id NUMBER;
355 BEGIN
356 SELECT price_list_header_id
357 INTO l_price_list_id
358 FROM csd_repairs
359 WHERE repair_line_id = p_repair_line_id;
360
361 IF (l_price_list_id is NOT NULL) THEN
362 return l_price_list_id;
363 ELSE
364 return null;
365 END IF;
366
367 EXCEPTION
368 WHEN others THEN
369 RETURN NULL;
370 END Get_RO_PriceList;
371
372 /* bug#3875036 */
373 /*----------------------------------------------------------------*/
374 /* function name: Get_SR_AccountId */
375 /* description : Gets the SR Customer Account Id for a repair order */
376 /* */
377 /* p_repair_line_id Repair Line ID to get SR Customer Account Id */
378 /* */
379 /*----------------------------------------------------------------*/
380 FUNCTION Get_SR_AccountId
381 (
382 p_repair_line_id IN NUMBER
383 )
384 RETURN NUMBER
385 IS
386 l_account_id NUMBER;
387
388 BEGIN
389
390 SELECT account_id
391 INTO l_account_id
392 FROM cs_incidents_all_b ciab, csd_repairs cr
393 WHERE cr.repair_line_id = p_repair_line_id and cr.incident_id = ciab.incident_id;
394
395 IF (l_account_id is NOT NULL) THEN
396 return l_account_id;
397 ELSE
398 return null;
399 END IF;
400
401 EXCEPTION
402 WHEN others THEN
403 RETURN NULL;
404 END Get_SR_AccountId;
405
406
407 /*----------------------------------------------------------------*/
408 /* function name: Get_DefaultContract */
409 /* description : Gets the default contract set in Product */
410 /* Coverage. */
411 /* */
412 /* p_repair_line_id Repair Line ID to get contract */
413 /* */
414 /*----------------------------------------------------------------*/
415 FUNCTION Get_DefaultContract
416 (
417 p_repair_line_id IN NUMBER
418 )
419 RETURN NUMBER
420 IS
421
422 l_contract_line_id NUMBER := NULL;
423
424 BEGIN
425
426 -- get default contract using repair order number
427 SELECT contract_line_id
428 INTO l_contract_line_id
429 FROM csd_repairs
430 WHERE repair_line_id = p_repair_line_id;
431
432 IF (l_contract_line_id IS NOT NULL) THEN
433 RETURN l_contract_line_id;
434 ELSE
435 RETURN NULL;
436 END IF;
437
438 EXCEPTION
439
440 WHEN others THEN
441 FND_MESSAGE.SET_NAME('CSD','CSD_API_DEFAULT_CONTRACT');
442 FND_MESSAGE.SET_TOKEN('REPAIR_LINE_ID',p_repair_line_id);
443 FND_MSG_PUB.Add;
444 RETURN -1;
445
446 END Get_DefaultContract;
447
448 /*----------------------------------------------------------------*/
449 /* procedure name: Get_ContractPriceList */
450 /* description : procedure used to get ets the price list */
451 /* specified by the contract */
452 /* */
453 /* p_api_version Standard IN param */
454 /* p_init_msg_list Standard IN param */
455 /* p_contract_line_id Contract Line ID */
456 /* p_repair_type_id Repair Type ID */
457 /* p_currency_code RO Currency */
458 /* x_contract_validated Whether the contract can be used */
459 /* x_contract_pl_id Contract Price List ID */
460 /* x_billing_pl_id Billing Price List ID */
461 /* x_return_status Standard OUT param */
462 /* x_msg_count Standard OUT param */
463 /* x_msg_data Standard OUT param */
464 /* */
465 /*----------------------------------------------------------------*/
466 PROCEDURE Get_ContractPriceList
467 (
468 p_api_version IN NUMBER,
469 p_init_msg_list IN VARCHAR2,
470 p_contract_line_id IN NUMBER,
471 p_repair_type_id IN NUMBER,
472 p_currency_code IN VARCHAR2,
473 x_contract_validated OUT NOCOPY BOOLEAN,
474 x_contract_pl_id OUT NOCOPY NUMBER,
475 x_billing_pl_id OUT NOCOPY NUMBER,
476 x_return_status OUT NOCOPY VARCHAR2,
477 x_msg_count OUT NOCOPY NUMBER,
478 x_msg_data OUT NOCOPY VARCHAR2
479 )
480 IS
481 l_api_name CONSTANT VARCHAR2(30) := 'Get_ContractPriceList';
482 l_api_version CONSTANT NUMBER := 1.0;
483
484 l_contract_pl_id NUMBER := NULL;
485 l_bus_process_id NUMBER;
486 l_use_pl BOOLEAN;
487 l_date DATE := sysdate;
488 l_pl_out_tbl OKS_CON_COVERAGE_PUB.pricing_tbl_type;
489 i NUMBER := 1;
490
491 -- gilam: bug 3542319 - added flag for checking contract bp price list and variable for billing pl
492 l_use_contract_bp_pl BOOLEAN;
493 l_billing_pl_id NUMBER := NULL;
494 --
495
496 BEGIN
497
498 --debug msg
499 IF (g_debug > 0 ) THEN
500 csd_gen_utility_pvt.ADD ('Get_ContractPriceList procedure: p_contract_line_id ='|| p_contract_line_id);
501 END IF;
502
503 -- Standard Start of API savepoint
504 SAVEPOINT Get_ContractPriceList;
505
506 -- Standard call to check for call compatibility.
507 IF NOT FND_API.Compatible_API_Call (l_api_version,
508 p_api_version,
509 l_api_name,
510 G_PKG_NAME)
511 THEN
512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
513 END IF;
514
515 -- Initialize message list if p_init_msg_list is set to TRUE.
516 IF FND_API.to_Boolean(p_init_msg_list) THEN
517 FND_MSG_PUB.initialize;
518 END IF;
519
520 -- Initialize API return status to success
521 x_return_status := FND_API.G_RET_STS_SUCCESS;
522
523 --
524 -- Begin API Body
525 --
526
527 IF (g_debug > 0 ) THEN
528 csd_gen_utility_pvt.dump_api_info ( p_pkg_name => G_PKG_NAME,
529 p_api_name => l_api_name );
530 END IF;
531
532 --debug msg
533 IF (g_debug > 0 ) THEN
534 csd_gen_utility_pvt.ADD ('Check required param and validate them');
535 END IF;
536
537
538 -- Check the required parameters
539 CSD_PROCESS_UTIL.Check_Reqd_Param
540 ( p_param_value => p_contract_line_id,
541 p_param_name => 'CONTRACT_LINE_ID',
542 p_api_name => l_api_name);
543
544 CSD_PROCESS_UTIL.Check_Reqd_Param
545 ( p_param_value => p_repair_type_id,
546 p_param_name => 'REPAIR_TYPE_ID',
547 p_api_name => l_api_name);
548
549
550 -- Validate the repair line ID
551 IF NOT( CSD_PROCESS_UTIL.Validate_repair_type_id ( p_repair_type_id => p_repair_type_id )) THEN
552 RAISE FND_API.G_EXC_ERROR;
553 END IF;
554
555 BEGIN
556
557 SELECT business_process_id
558 INTO l_bus_process_id
559 FROM csd_repair_types_b
560 WHERE repair_type_id = p_repair_type_id;
561
562 EXCEPTION
563
564 WHEN others THEN
565 FND_MESSAGE.SET_NAME('CSD','CSD_API_CONTRACT_PL');
566 FND_MESSAGE.SET_TOKEN('REPAIR_TYPE_ID', p_repair_type_id);
567 FND_MSG_PUB.Add;
568 RAISE FND_API.G_EXC_ERROR;
569
570 END;
571
572 --debug msg
573 IF (g_debug > 0 ) THEN
574 csd_gen_utility_pvt.ADD ('Call OKS_Con_Coverage_PUB.Get_BP_PriceList API: p_contract_line_id ='|| p_contract_line_id);
575 END IF;
576
577 -- gilam: bug 3542319 - changed the logic to handle contract price lists
578 BEGIN
579
580 -- Call OKS_Con_Coverage_PUB.Get_BP_PriceList API
581 OKS_CON_COVERAGE_PUB.GET_BP_PRICELIST
582 (
583 p_api_version => l_api_version,
584 p_init_msg_list => 'T',
585 p_contract_line_id => p_contract_line_id,
586 p_business_process_id => l_bus_process_id,
587 p_request_date => l_date,
588 x_return_status => x_return_status,
589 x_msg_count => x_msg_count,
590 x_msg_data => x_msg_data,
591 x_pricing_tbl => l_pl_out_tbl
592 );
593
594
595 IF (g_debug > 0 ) THEN
596
597 csd_gen_utility_pvt.ADD ('Call OKS API to get price list: return status ='|| x_return_status);
598 csd_gen_utility_pvt.ADD ('l_pl_out_tbl(i).bp_price_list_id: '|| l_pl_out_tbl(i).bp_price_list_id);
599
600 END IF;
601
602
603 EXCEPTION
604
605 WHEN no_data_found THEN
606
607 l_use_contract_bp_pl := FALSE;
608
609 END;
610
611 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
612
613 -- only 1 row should be returned
614 IF (l_pl_out_tbl.COUNT = 1) THEN
615
616 -- contract has bp price list
617 IF (l_pl_out_tbl(i).bp_price_list_id IS NOT NULL) THEN
618
619 l_use_contract_bp_pl := TRUE;
620
621 -- contract does not have bp price list
622 ELSE
623
624 l_use_contract_bp_pl := FALSE;
625
626 IF (l_pl_out_tbl(i).contract_price_list_id IS NOT NULL) THEN
627
628 l_billing_pl_id := l_pl_out_tbl(i).contract_price_list_id;
629
630 END IF;
631
632 END IF;
633
634 ELSE
635
636 -- contract does not have any price list or has errors, set flag to false
637 l_use_contract_bp_pl := FALSE;
638
639 END IF;
640
641 ELSIF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
642
643 -- contract has errors, set flag to false
644 l_use_contract_bp_pl := FALSE;
645
646 END IF;
647
648 -- setting contract validated to true since no validation is required now
649 x_contract_validated := TRUE;
650
651 -- 1) if contract business process price list should be used
652 IF (l_use_contract_bp_pl) THEN
653
654 IF (l_pl_out_tbl(i).bp_price_list_id IS NOT NULL) THEN
655 l_contract_pl_id := l_pl_out_tbl(i).bp_price_list_id;
656 l_use_pl := Validate_PriceList(l_contract_pl_id, p_currency_code);
657
658 -- contract bp price list has currency same as RO
659 IF (l_use_pl) THEN
660 x_contract_pl_id := l_contract_pl_id;
661
662 -- contract price list has currency different from RO
663 ELSIF (NOT l_use_pl) THEN
664 x_contract_pl_id := NULL;
665
666 END IF;
667
668 END IF;
669
670 ELSE
671
672 -- 2) else if contract price list exists
673 IF (l_billing_pl_id IS NOT NULL) then
674
675 l_use_pl := Validate_PriceList(l_billing_pl_id, p_currency_code);
676 x_contract_pl_id := NULL;
677
678 -- contract billing price list has currency same as RO
679 IF (l_use_pl) THEN
680 x_billing_pl_id := l_billing_pl_id;
681
682 -- contract billing price list has currency different from RO
683 ELSIF (NOT l_use_pl) THEN
684 x_billing_pl_id := NULL;
685
686 END IF;
687
688 END IF;
689
690 END IF;
691 -- gilam: end bug 3542319 - changed the logic to handle contract price lists
692
693
694 --
695 -- End API Body
696 --
697
698 -- Standard call to get message count and IF count is get message info.
699 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
700 p_data => x_msg_data );
701
702 EXCEPTION
703
704 WHEN FND_API.G_EXC_ERROR THEN
705 ROLLBACK TO Get_ContractPriceList;
706 x_return_status := FND_API.G_RET_STS_ERROR ;
707 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
708 p_data => x_msg_data);
709
710 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
711 ROLLBACK TO Get_ContractPriceList;
712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
713 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
714 p_data => x_msg_data );
715
716 WHEN OTHERS THEN
717 ROLLBACK TO Get_ContractPriceList;
718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
719 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
720 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
721 l_api_name );
722 END IF;
723
724 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
725 p_data => x_msg_data );
726
727 END Get_ContractPriceList;
728
729 /*----------------------------------------------------------------*/
730 /* procedure name: Validate_Contract */
731 /* description : Checks the currency of the contract to see if */
732 /* it matches the one for repair order */
733 /* */
734 /* p_api_version Standard IN param */
735 /* p_init_msg_list Standard IN param */
736 /* p_contract_line_id Contract Line ID to check currency*/
737 /* p_repair_type_id Repair Type ID to get contract PL */
738 /* p_ro_currency_code RO currency code */
739 /* x_contract_currency_code Contract PriceList Currency */
740 /* x_contract_validated Whether the contract can be used */
741 /* x_return_status Standard OUT param */
742 /* x_msg_count Standard OUT param */
743 /* x_msg_data Standard OUT param */
744 /* */
745 /*----------------------------------------------------------------*/
746 PROCEDURE Validate_Contract
747 (
748 p_api_version IN NUMBER,
749 p_init_msg_list IN VARCHAR2,
750 p_contract_line_id IN NUMBER,
751 p_repair_type_id IN NUMBER,
752 p_ro_currency_code IN VARCHAR2,
753 x_contract_currency_code OUT NOCOPY VARCHAR2,
754 x_contract_validated OUT NOCOPY BOOLEAN,
755 x_return_status OUT NOCOPY VARCHAR2,
756 x_msg_count OUT NOCOPY NUMBER,
757 x_msg_data OUT NOCOPY VARCHAR2
758 )
759 IS
760
761 l_api_name CONSTANT VARCHAR2(30) := 'Validate_contract';
762 l_api_version CONSTANT NUMBER := 1.0;
763
764 l_contract_validated BOOLEAN;
765 l_contract_pl_id NUMBER;
766 l_billing_pl_id NUMBER;
767
768 BEGIN
769
770 --debug msg
771 IF (g_debug > 0 ) THEN
772 csd_gen_utility_pvt.ADD ('Validate Contract function: p_contract_line_id ='|| p_contract_line_id);
773 END IF;
774
775 -- Standard Start of API savepoint
776 SAVEPOINT Validate_Contract;
777
778 -- Standard call to check for call compatibility.
779 IF NOT FND_API.Compatible_API_Call (l_api_version,
780 p_api_version,
781 l_api_name,
782 G_PKG_NAME)
783 THEN
784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
785 END IF;
786
787 -- Initialize message list if p_init_msg_list is set to TRUE.
788 IF FND_API.to_Boolean(p_init_msg_list) THEN
789 FND_MSG_PUB.initialize;
790 END IF;
791
792 -- Initialize API return status to success
793 x_return_status := FND_API.G_RET_STS_SUCCESS;
794
795 --
796 -- Begin API Body
797 --
798
799 IF (g_debug > 0 ) THEN
800 csd_gen_utility_pvt.dump_api_info ( p_pkg_name => G_PKG_NAME,
801 p_api_name => l_api_name );
802 END IF;
803
804 --debug msg
805 IF (g_debug > 0 ) THEN
806 csd_gen_utility_pvt.ADD ('Check required parameters and validate them');
807 END IF;
808
809 CSD_PROCESS_UTIL.Check_Reqd_Param
810 ( p_param_value => p_repair_type_id,
811 p_param_name => 'REPAIR_TYPE_ID',
812 p_api_name => l_api_name);
813
814 CSD_PROCESS_UTIL.Check_Reqd_Param
815 ( p_param_value => p_ro_currency_code,
816 p_param_name => 'RO_CURRENCY_CODE',
817 p_api_name => l_api_name);
818
819 -- Validate the repair type ID
820 IF NOT( CSD_PROCESS_UTIL.Validate_repair_type_id ( p_repair_type_id => p_repair_type_id )) THEN
821 RAISE FND_API.G_EXC_ERROR;
822 END IF;
823
824
825 --debug msg
826 IF (g_debug > 0 ) THEN
827 csd_gen_utility_pvt.ADD ('Call Get_ContractPriceList procedure: p_contract_line_id ='|| p_contract_line_id);
828 END IF;
829
830 IF (p_contract_line_id IS NOT NULL) THEN
831 Get_ContractPriceList
832 (
833 p_api_version => l_api_version,
834 p_init_msg_list => 'T',
835 p_contract_line_id => p_contract_line_id,
836 p_repair_type_id => p_repair_type_id,
837 p_currency_code => p_ro_currency_code,
838 x_contract_validated => l_contract_validated,
839 x_contract_pl_id => l_contract_pl_id,
840 x_billing_pl_id => l_billing_pl_id,
841 x_return_status => x_return_status,
842 x_msg_count => x_msg_count,
843 x_msg_data => x_msg_data
844 );
845
846 ELSE
847
848 --debug msg
849 IF (g_debug > 0 ) THEN
850 csd_gen_utility_pvt.ADD ('p_contract_line_id is null');
851 END IF;
852
853 RAISE FND_API.G_EXC_ERROR;
854
855 END IF;
856
857 --debug msg
858 IF (g_debug > 0 ) THEN
859 csd_gen_utility_pvt.ADD ('Call Get_ContractPriceList procedure: return status ='|| x_return_status);
860 END IF;
861
862 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
863
864 x_contract_validated := l_contract_validated;
865
866 IF (l_contract_pl_id IS NOT NULL) THEN
867 x_contract_currency_code := Get_PLCurrCode (l_contract_pl_id);
868 ELSE
869 x_contract_currency_code := Get_PLCurrCode (l_billing_pl_id);
870 END IF;
871
872 ELSIF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
873
874 --debug msg
875 IF (g_debug > 0 ) THEN
876 csd_gen_utility_pvt.ADD ('Get_ContractPL procedure failed');
877 END IF;
878
879 RAISE FND_API.G_EXC_ERROR;
880
881 END IF;
882
883 EXCEPTION
884
885 WHEN FND_API.G_EXC_ERROR THEN
886 ROLLBACK TO Validate_Contract;
887 x_return_status := FND_API.G_RET_STS_ERROR ;
888 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
889 p_data => x_msg_data);
890
891 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
892 ROLLBACK TO Validate_Contract;
893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
894 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
895 p_data => x_msg_data );
896
897 WHEN OTHERS THEN
898 ROLLBACK TO Validate_Contract;
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
900 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
901 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
902 l_api_name );
903 END IF;
904
905 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
906 p_data => x_msg_data );
907
908 END Validate_Contract;
909
910
911 /*----------------------------------------------------------------*/
912 /* function name: Validate_PriceList */
913 /* description : Checks the currency the price list to see if it */
914 /* matches the one for repair order */
915 /* */
916 /* p_price_list_id Price List ID to get currency */
917 /* p_currency_code RO currency */
918 /* */
919 /*----------------------------------------------------------------*/
920 FUNCTION Validate_PriceList (
921 p_price_list_id IN NUMBER,
922 p_currency_code IN VARCHAR2
923 )
924 RETURN BOOLEAN
925 IS
926 l_pl_curr_code VARCHAR2(15) := NULL;
927 l_result VARCHAR2(10);
928
929 reqd_param_failed EXCEPTION;
930
931 BEGIN
932
933 -- get currency of price list and verify with RO currency
934 IF (p_price_list_id IS NOT NULL AND p_currency_code IS NOT NULL) THEN
935 l_pl_curr_code := Get_PLCurrCode(p_price_list_id);
936 IF (l_pl_curr_code = p_currency_code) THEN
937 RETURN TRUE;
938 ELSE
939 RETURN FALSE;
940 END IF;
941 ELSE
942 Raise reqd_param_failed;
943 END IF;
944
945 EXCEPTION
946
947 WHEN reqd_param_failed THEN
948 FND_MESSAGE.SET_NAME('CSD','CSD_API_VALIDATE_PL');
949 FND_MESSAGE.SET_TOKEN('MISSING PARAM: PRICE_LIST_ID', p_price_list_id);
950 FND_MESSAGE.SET_TOKEN('CURRENCY_CODE',p_currency_code);
951 FND_MSG_PUB.Add;
952 RETURN NULL;
953
954 WHEN others THEN
955 FND_MESSAGE.SET_NAME('CSD','CSD_API_VALIDATE_PL');
956 FND_MESSAGE.SET_TOKEN('PRICE_LIST_ID',p_price_list_id);
957 FND_MSG_PUB.Add;
958 RETURN NULL;
959
960 END Validate_PriceList;
961
962
963 /*----------------------------------------------------------------*/
964 /* procedure name: Get_DiscountedPrice */
965 /* description : procedure used to get the discounted price */
966 /* for applying a contract */
967 /* */
968 /* p_api_version Standard IN param */
969 /* p_init_msg_list Standard IN param */
970 /* p_contract_line_id Contract Line ID */
971 /* p_repair_type_id Repair Type ID */
972 /* p_txn_billing_type_id Transaction Billing Type ID */
973 /* p_coverage_txn_grp_id Coverage Transaction Group ID */
974 /* p_extended_price Extended Price */
975 /* p_no_charge_flag No Charge Flag */
976 /* x_discounted_price Discounted Price */
977 /* x_return_status Standard OUT param */
978 /* x_msg_count Standard OUT param */
979 /* x_msg_data Standard OUT param */
980 /* */
981 /*----------------------------------------------------------------*/
982 PROCEDURE Get_DiscountedPrice
983 (
984 p_api_version IN NUMBER,
985 p_init_msg_list IN VARCHAR2,
986 p_contract_line_id IN NUMBER,
987 p_repair_type_id IN NUMBER,
988 p_txn_billing_type_id IN NUMBER,
989 p_coverage_txn_grp_id IN NUMBER,
990 p_extended_price IN NUMBER,
991 p_no_charge_flag IN VARCHAR2,
992 x_discounted_price OUT NOCOPY NUMBER,
993 x_return_status OUT NOCOPY VARCHAR2,
994 x_msg_count OUT NOCOPY NUMBER,
995 x_msg_data OUT NOCOPY VARCHAR2
996 )
997 IS
998 l_api_name CONSTANT VARCHAR2(30) := 'Apply_contract';
999 l_api_version CONSTANT NUMBER := 1.0;
1000
1001 l_bus_process_id NUMBER;
1002 l_request_date DATE := sysdate;
1003 l_contract_in_tbl OKS_CON_COVERAGE_PUB.ser_tbl_type;
1004 l_contract_out_tbl OKS_CON_COVERAGE_PUB.cov_tbl_type;
1005 i NUMBER := 1;
1006
1007 BEGIN
1008
1009 --debug msg
1010 IF (g_debug > 0 ) THEN
1011 csd_gen_utility_pvt.ADD ('Get_DiscountedPrice procedure: p_contract_line_id ='|| p_contract_line_id);
1012 END IF;
1013
1014 -- Standard Start of API savepoint
1015 SAVEPOINT Get_DiscountedPrice;
1016
1017 -- Standard call to check for call compatibility.
1018 IF NOT FND_API.Compatible_API_Call (l_api_version,
1019 p_api_version,
1020 l_api_name,
1021 G_PKG_NAME)
1022 THEN
1023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1024 END IF;
1025
1026 -- Initialize message list if p_init_msg_list is set to TRUE.
1027 IF FND_API.to_Boolean(p_init_msg_list) THEN
1028 FND_MSG_PUB.initialize;
1029 END IF;
1030
1031 -- Initialize API return status to success
1032 x_return_status := FND_API.G_RET_STS_SUCCESS;
1033
1034 --
1035 -- Begin API Body
1036 --
1037
1038 IF (g_debug > 0 ) THEN
1039 csd_gen_utility_pvt.dump_api_info ( p_pkg_name => G_PKG_NAME,
1040 p_api_name => l_api_name );
1041 END IF;
1042
1043 --debug msg
1044 IF (g_debug > 0 ) THEN
1045 csd_gen_utility_pvt.ADD ('Check required parameters and validate them');
1046 END IF;
1047
1048 -- Check the required parameters
1049 CSD_PROCESS_UTIL.Check_Reqd_Param
1050 ( p_param_value => p_contract_line_id,
1051 p_param_name => 'CONTRACT_LINE_ID',
1052 p_api_name => l_api_name);
1053
1054 CSD_PROCESS_UTIL.Check_Reqd_Param
1055 ( p_param_value => p_repair_type_id,
1056 p_param_name => 'REPAIR_TYPE_ID',
1057 p_api_name => l_api_name);
1058
1059 CSD_PROCESS_UTIL.Check_Reqd_Param
1060 ( p_param_value => p_txn_billing_type_id,
1061 p_param_name => 'TRANSACTION_BILLING_TYPE_ID',
1062 p_api_name => l_api_name);
1063
1064 CSD_PROCESS_UTIL.Check_Reqd_Param
1065 ( p_param_value => p_extended_price,
1066 p_param_name => 'EXTENDED_PRICE',
1067 p_api_name => l_api_name);
1068
1069
1070 -- Validate the repair type ID
1071 IF NOT( CSD_PROCESS_UTIL.Validate_repair_type_id ( p_repair_type_id => p_repair_type_id )) THEN
1072 RAISE FND_API.G_EXC_ERROR;
1073 END IF;
1074
1075 BEGIN
1076
1077 -- get business process id for repair type
1078 SELECT business_process_id
1079 INTO l_bus_process_id
1080 FROM csd_repair_types_b
1081 WHERE repair_type_id = p_repair_type_id;
1082
1083 EXCEPTION
1084
1085 WHEN others THEN
1086 FND_MESSAGE.SET_NAME('CSD','CSD_API_DISCOUNTED_PRICE');
1087 FND_MESSAGE.SET_TOKEN('REPAIR_TYPE_ID', p_repair_type_id);
1088 FND_MSG_PUB.Add;
1089 RAISE FND_API.G_EXC_ERROR;
1090
1091 END;
1092
1093 -- Set l_contract_in_tbl attributes
1094 -- only passing 1 row, so i = 1
1095
1096 l_contract_in_tbl(i).contract_line_id := p_contract_line_id;
1097 --l_contract_in_tbl(i).txn_group_id := p_coverage_txn_grp_id;
1098 -- contract rearch changes for R12
1099 l_contract_in_tbl(i).txn_group_id := null;
1100 l_contract_in_tbl(i).business_process_id := l_bus_process_id;
1101 l_contract_in_tbl(i).billing_type_id := p_txn_billing_type_id;
1102 l_contract_in_tbl(i).request_date := l_request_date;
1103
1104 IF (p_no_charge_flag = 'Y') THEN
1105 l_contract_in_tbl(i).charge_amount := 0;
1106 ELSE
1107 l_contract_in_tbl(i).charge_amount := p_extended_price;
1108 END IF;
1109
1110 --debug msg
1111 IF (g_debug > 0 ) THEN
1112 csd_gen_utility_pvt.ADD ('Call OKS_Con_Coverage_PUB.Apply_Contract_Coverage API: p_contract_line_id ='|| p_contract_line_id);
1113 END IF;
1114
1115
1116 -- Call OKS_Con_Coverage_PUB.Apply_Contract_Coverage API
1117 OKS_CON_COVERAGE_PUB.APPLY_CONTRACT_COVERAGE
1118 (
1119 p_api_version => l_api_version,
1120 p_init_msg_list => p_init_msg_list, -- 'T' Changed by vkjain.
1121 p_est_amt_tbl => l_contract_in_tbl,
1122 x_return_status => x_return_status,
1123 x_msg_count => x_msg_count,
1124 x_msg_data => x_msg_data,
1125 x_est_discounted_amt_tbl => l_contract_out_tbl
1126 );
1127
1128
1129 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1130
1131 -- only 1 row should be returned
1132 IF (l_contract_out_tbl.COUNT > 1) THEN
1133 RAISE FND_API.G_EXC_ERROR;
1134 ELSE
1135
1136 IF (l_contract_out_tbl(i).discounted_amount IS NULL) THEN
1137 x_discounted_price := l_contract_in_tbl(i).charge_amount;
1138 ELSE
1139 x_discounted_price := l_contract_out_tbl(i).discounted_amount;
1140 END IF;
1141 END IF;
1142
1143 ELSIF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1144 RAISE FND_API.G_EXC_ERROR;
1145 END IF;
1146
1147 -- API body ends here
1148
1149 -- Standard call to get message count and IF count is get message info.
1150 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1151 p_data => x_msg_data );
1152
1153 EXCEPTION
1154
1155 WHEN FND_API.G_EXC_ERROR THEN
1156 ROLLBACK TO Get_DiscountedPrice;
1157 x_return_status := FND_API.G_RET_STS_ERROR ;
1158 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1159 p_data => x_msg_data);
1160
1161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1162 ROLLBACK TO Get_DiscountedPrice;
1163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1164 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1165 p_data => x_msg_data );
1166
1167 WHEN OTHERS THEN
1168 ROLLBACK TO Get_DiscountedPrice;
1169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1170 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1171 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
1172 l_api_name );
1173 END IF;
1174 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1175 p_data => x_msg_data );
1176
1177
1178 END Get_DiscountedPrice;
1179
1180 /*----------------------------------------------------------------*/
1181 /* procedure name: Get_CoverageInfo */
1182 /* description : procedure used to get the converage information*/
1183 /* for a given contract line and business process */
1184 /* */
1185 /* p_contract_line_id Contract Line ID */
1186 /* p_business_process_id Business process ID */
1187 /* x_return_status Standard OUT param */
1188 /* x_msg_count Standard OUT param */
1189 /* x_msg_data Standard OUT param */
1190 /* x_contract_id Contract ID */
1191 /* x_contract_number Contract Number */
1192 /* x_coverage_id Coverage ID */
1193 /* x_coverage_txn_grp_id Coverage Transaction Group ID */
1194 /* */
1195 /*----------------------------------------------------------------*/
1196
1197 PROCEDURE Get_CoverageInfo (p_contract_line_id IN NUMBER,
1198 p_business_process_id IN NUMBER,
1199 x_return_status OUT NOCOPY VARCHAR2,
1200 x_msg_count OUT NOCOPY NUMBER,
1201 x_msg_data OUT NOCOPY VARCHAR2,
1202 x_contract_id OUT NOCOPY NUMBER,
1203 x_contract_number OUT NOCOPY VARCHAR2, -- swai: bug fix 4770958
1204 -- x_contract_number OUT NOCOPY NUMBER, -- swai: bug fix 4770958
1205 x_coverage_id OUT NOCOPY NUMBER,
1206 x_coverage_txn_group_id OUT NOCOPY NUMBER
1207 ) IS
1208
1209 l_api_name CONSTANT VARCHAR2(30) := 'Get_CoverageInfo';
1210
1211 -- Selects coverage information for charge line record
1212 --Contract re arch changes for R12
1213 /**
1214 cursor c_coverage_info IS
1215 SELECT cov.contract_id,
1216 cov.contract_number,
1217 cov.actual_coverage_id,
1218 ent.txn_group_id
1219 FROM oks_ent_coverages_v cov,
1220 oks_ent_txn_groups_v ent
1221 WHERE cov.actual_coverage_id = ent.coverage_id
1222 AND cov.contract_line_id = p_contract_line_id
1223 AND ent.business_process_id = p_business_process_id;
1224 */
1225 cursor c_coverage_info IS
1226 SELECT cov.contract_id,
1227 cov.contract_number,
1228 cov.actual_coverage_id
1229 FROM oks_ent_coverages_v cov
1230 WHERE cov.contract_line_id = p_contract_line_id;
1231
1232 BEGIN
1233
1234 --debug msg
1235 IF (g_debug > 0 ) THEN
1236 csd_gen_utility_pvt.ADD ('Get_CoverageInfo procedure: p_contract_line_id ='|| p_contract_line_id);
1237 csd_gen_utility_pvt.ADD ('Get_CoverageInfo procedure: p_business_process_id='|| p_business_process_id);
1238 END IF;
1239
1240 -- Initialize API return status to success
1241 x_return_status := FND_API.G_RET_STS_SUCCESS;
1242
1243 IF (g_debug > 0 ) THEN
1244 csd_gen_utility_pvt.dump_api_info ( p_pkg_name => G_PKG_NAME,
1245 p_api_name => l_api_name );
1246 END IF;
1247
1248 --debug msg
1249 IF (g_debug > 0 ) THEN
1250 csd_gen_utility_pvt.ADD ('Check required parameters and validate them');
1251 END IF;
1252
1253 -- Check the required parameters
1254 CSD_PROCESS_UTIL.Check_Reqd_Param
1255 ( p_param_value => p_contract_line_id,
1256 p_param_name => 'CONTRACT_LINE_ID',
1257 p_api_name => l_api_name);
1258
1259 CSD_PROCESS_UTIL.Check_Reqd_Param
1260 ( p_param_value => p_business_process_id,
1261 p_param_name => 'BUSINESS_PROCESS_ID',
1262 p_api_name => l_api_name);
1263
1264 x_coverage_txn_group_id := NULL;
1265
1266 IF (g_debug > 0 ) THEN
1267 csd_gen_utility_pvt.ADD ('Get_CoverageInfo: Opening cursor');
1268 END IF;
1269
1270 x_coverage_txn_group_id := NULL;
1271
1272 -- Open the cursor and fetch the values.
1273 OPEN c_coverage_info;
1274 FETCH c_coverage_info
1275 INTO x_contract_id,
1276 x_contract_number,
1277 x_coverage_id;
1278 -- contracts re arch changes for R12
1279 --x_coverage_txn_group_id;
1280
1281 CLOSE c_coverage_info;
1282
1283 -- Checking only for x_coverage_txn_group_id as
1284 -- we use it for determining discounts.
1285 -- contracts re arch changes for R12
1286 /****
1287 IF (x_coverage_txn_group_id IS NULL) THEN
1288 -- put a debug message.
1289 IF (g_debug > 0 ) THEN
1290 csd_gen_utility_pvt.ADD ('Get_CoverageInfo: No coverage information found.');
1291 END IF;
1292
1293 -- Log an error message.
1294 FND_MESSAGE.SET_NAME('CSD', 'CSD_CHRG_UTIL_NO_COVG');
1295 -- No coverage information found for contract and business process.
1296 FND_MSG_PUB.ADD;
1297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1298 END IF;
1299 ******/
1300 x_coverage_txn_group_id := null;
1301
1302 EXCEPTION
1303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1305 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1306 p_data => x_msg_data );
1307 WHEN OTHERS THEN
1308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1309 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1310 THEN
1311 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
1312 l_api_name );
1313 END IF;
1314 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1315 p_data => x_msg_data );
1316 END Get_CoverageInfo;
1317
1318 /*----------------------------------------------------------------*/
1319 /* procedure name: Convert_To_Charge_Lines */
1320 /* description : The procedure takes in a set of generic MLE */
1321 /* records and sorts out 'good' records that are */
1322 /* eligible for creating charge lines. It also */
1323 /* logs warning messages for 'bad' records */
1324 /* indicating the reason. */
1325 /* */
1326 /*----------------------------------------------------------------*/
1327
1328 PROCEDURE Convert_To_Charge_Lines( p_api_version IN NUMBER,
1329 p_commit IN VARCHAR2,
1330 p_init_msg_list IN VARCHAR2,
1331 p_validation_level IN NUMBER,
1332 x_return_status OUT NOCOPY VARCHAR2,
1333 x_msg_count OUT NOCOPY NUMBER,
1334 x_msg_data OUT NOCOPY VARCHAR2,
1335 p_est_act_module_code IN VARCHAR2,
1336 p_est_act_msg_entity IN VARCHAR2,
1337 p_charge_line_type IN VARCHAR2,
1338 p_repair_line_id IN NUMBER,
1339 p_repair_actual_id IN NUMBER,
1340 p_repair_type_id IN NUMBER,
1341 p_business_process_id IN NUMBER,
1342 p_currency_code IN VARCHAR2,
1343 p_incident_id IN NUMBER,
1344 p_organization_id IN NUMBER,
1345 p_price_list_id IN NUMBER,
1346 p_contract_line_id IN NUMBER,
1347 p_MLE_lines_tbl IN MLE_LINES_TBL_TYPE,
1348 px_valid_MLE_lines_tbl IN OUT NOCOPY MLE_LINES_TBL_TYPE,
1349 px_charge_lines_tbl IN OUT NOCOPY CHARGE_LINES_TBL_TYPE,
1350 x_warning_flag OUT NOCOPY VARCHAR2 ) IS
1351
1352 -- CONSTANTS --
1353 -- API constants
1354 lc_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1355 lc_stat_level CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
1356 lc_proc_level CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
1357 lc_event_level CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
1358 lc_excep_level CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
1359 lc_error_level CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
1360 lc_unexp_level CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
1361 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_charge_line_util.convert_to_charge_lines';
1362 lc_api_name CONSTANT VARCHAR2(30) := 'CONVERT_TO_CHARGE_LINES';
1363 lc_api_version CONSTANT NUMBER := 1.0;
1364
1365 -- Pricing attributes
1366 l_pricing_rec csd_process_util.pricing_attr_rec;
1367
1368 --bug#3875036
1369 l_account_id NUMBER := NULL;
1370
1371 -- Generic constants definitions
1372 G_CHARGES_SOURCE_CODE_DR CONSTANT VARCHAR2(30) := 'DR';
1373 G_LINE_CATEGORY_CODE_ORDER CONSTANT VARCHAR2(30) := 'ORDER';
1374
1375 -- VARIABLES --
1376 -- Stores the ID for the last and current item in the loop.
1377 l_curr_inv_item NUMBER := NULL;
1378 l_last_inv_item NUMBER := NULL;
1379
1380 -- Used only to log warning message
1381 l_price_list_name VARCHAR2(240) := NULL;
1382
1383 -- Stores the count of install base subtypes that are
1384 -- defined for the given transaction type.
1385 l_num_subtypes NUMBER := NULL;
1386
1387 -- Stores 'after warranty cost', unit selling price and the
1388 -- discounted price for the item.
1389 l_extended_price NUMBER;
1390 l_unit_selling_price NUMBER := NULL;
1391 l_discounted_price NUMBER := NULL;
1392
1393 -- Stores the flag value for the current item in the loop.
1394 l_no_charge_flag VARCHAR2(1) := NULL;
1395
1396 -- Stores the contract information that is applicable to all lines.
1397 l_contract_id NUMBER := NULL;
1398 l_contract_number VARCHAR2(120) := NULL; -- swai bug fix 4770958
1399 -- l_contract_number NUMBER := NULL; -- swai bug fix 4770958
1400 l_coverage_id NUMBER := NULL;
1401 l_coverage_txn_group_id NUMBER := NULL;
1402
1403 -- Stores the ORG ID that is applicable to all lines.
1404 l_org_id NUMBER := NULL;
1405
1406 -- The derived line type and category code for each line based
1407 -- on the set up for transaction billing type.
1408 l_line_type_id NUMBER := NULL;
1409 l_line_category_code VARCHAR2(30) := NULL;
1410
1411 -- Indicates whether or not to skip the current record
1412 -- while putting in the valid list of values.
1413 -- If the value is TRUE then it means that the current
1414 -- record is ineligible for further processing.
1415 l_skip_curr_rec BOOLEAN;
1416
1417 l_numRows NUMBER;
1418 l_curRow NUMBER;
1419
1420 l_return_status VARCHAR2(1);
1421 l_msg_count NUMBER;
1422 l_msg_data VARCHAR2(2000);
1423
1424 -- cursors --
1425
1426 -- number of txn subtypes for a given txn billing type
1427 CURSOR count_csi_txn_subtypes( p_txn_billing_type_id NUMBER ) IS
1428 SELECT COUNT( * )
1429 FROM csi_txn_sub_types ib,
1430 cs_txn_billing_types cs,
1431 CSI_TXN_TYPES ctt
1432 WHERE cs.txn_billing_type_id = p_txn_billing_type_id
1433 AND ib.cs_transaction_type_id = cs.transaction_type_id
1434 AND ib.non_src_reference_reqd = 'Y'
1435 AND ib.update_ib_flag = 'Y'
1436 AND ctt.transaction_type_id = ib.transaction_type_id
1437 AND nvl(ctt.source_application_id, 660) = 660 -- For Order Management 'ONT'
1438 AND nvl(ctt.source_transaction_type, 'OM_SHIPMENT') = 'OM_SHIPMENT';
1439
1440 BEGIN
1441
1442 -- Standard Start of API savepoint
1443 SAVEPOINT Convert_To_Actual_Lines;
1444
1445 -- Standard call to check for call compatibility.
1446 IF NOT FND_API.Compatible_API_Call( lc_api_version,
1447 p_api_version,
1448 lc_api_name,
1449 G_PKG_NAME ) THEN
1450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1451 END IF;
1452
1453 -- Initialize message list if p_init_msg_list is set to TRUE.
1454 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1455 FND_MSG_PUB.initialize;
1456 END IF;
1457
1458 -- logging
1459 if (lc_proc_level >= lc_debug_level) then
1460 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.BEGIN',
1461 'Entering CSD_CHARGE_LINE_UTIL.convert_to_charge_lines');
1462 end if;
1463
1464 -- log parameters
1465 if (lc_stat_level >= lc_debug_level) then
1466 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1467 'p_api_version: ' || p_api_version);
1468 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1469 'p_commit: ' || p_commit);
1470 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1471 'p_init_msg_list: ' || p_init_msg_list);
1472 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1473 'p_validation_level: ' || p_validation_level);
1474 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1475 'p_est_act_module_code: ' || p_est_act_module_code);
1476 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1477 'p_est_act_msg_entity: ' || p_est_act_msg_entity);
1478 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1479 'p_charge_line_type: ' || p_charge_line_type);
1480 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1481 'p_repair_line_id: ' || p_repair_line_id);
1482 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1483 'p_repair_actual_id: ' || p_repair_actual_id);
1484 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1485 'p_repair_type_id: ' || p_repair_type_id);
1486 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1487 'p_business_process_id: ' || p_business_process_id);
1488 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1489 'p_currency_code: ' || p_currency_code);
1490 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1491 'p_incident_id: ' || p_incident_id);
1492 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1493 'p_organization_id: ' || p_organization_id);
1494 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1495 'p_price_list_id: ' || p_price_list_id);
1496 FND_LOG.STRING(lc_stat_level, lc_mod_name || '.parameter_logging',
1497 'p_contract_line_id: ' || p_contract_line_id);
1498 end if;
1499
1500 -- Initialize API return status to success
1501 x_return_status := FND_API.G_RET_STS_SUCCESS;
1502
1503 --
1504 -- Begin API Body
1505 --
1506
1507 --DBMS_OUTPUT.put_line( 'NEW: before the api begin' );
1508
1509 -- Get the org id. It will be used later to derive
1510 -- line type id.
1511 if (lc_proc_level >= lc_debug_level) then
1512 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1513 'Calling CSD_PROCESS_UTIL.get_org_id with p_incident_id = ' || p_incident_id);
1514 end if;
1515
1516 l_org_id := CSD_PROCESS_UTIL.get_org_id( p_incident_id );
1517
1518 /* bug#3875036 */
1519 l_account_id := Get_SR_AccountId(p_repair_line_id);
1520
1521 if (lc_proc_level >= lc_debug_level) then
1522 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1523 'Returned from CSD_PROCESS_UTIL.get_org_id.'
1524 || ' l_org_id = ' || l_org_id);
1525 end if;
1526
1527 --DBMS_OUTPUT.put_line( 'NEW: The org id was got '
1528 -- || l_org_id );
1529
1530 IF ( l_org_id IS NULL ) THEN
1531 if (lc_proc_level >= lc_debug_level) then
1532 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1533 'Could not determine the operating unit for'
1534 || ' p_incident_id = ' || p_incident_id);
1535 end if;
1536 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_NO_OPER_UNIT');
1537 -- Could not determine the operating unit. Operating unit is required to derive line types.
1538 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_ERROR_MSG );
1539 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1540 END IF;
1541
1542 -- Get the coverage details for the business process and
1543 -- Contract line id.
1544 -- This is placed outside the loop as the API output
1545 -- is applicable to all lines.
1546 IF ( p_contract_line_id IS NOT NULL
1547 AND p_business_process_id IS NOT NULL ) THEN
1548 if (lc_proc_level >= lc_debug_level) then
1549 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1550 'Calling CSD_CHARGE_LINE_UTIL.Get_CoverageInfo with '
1551 || 'p_contract_line_id = ' || p_contract_line_id
1552 || ' and p_business_process_id = ' || p_business_process_id);
1553 end if;
1554 --DBMS_OUTPUT.put_line( 'NEW: inside to get coverage info' );
1555 CSD_CHARGE_LINE_UTIL.Get_CoverageInfo( p_contract_line_id => p_contract_line_id,
1556 p_business_process_id => p_business_process_id,
1557 x_return_status => x_return_status,
1558 x_msg_count => x_msg_count,
1559 x_msg_data => x_msg_data,
1560 x_contract_id => l_contract_id,
1561 x_contract_number => l_contract_number,
1562 x_coverage_id => l_coverage_id,
1563 x_coverage_txn_group_id => l_coverage_txn_group_id );
1564
1565 if (lc_proc_level >= lc_debug_level) then
1566 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1567 'Returned from CSD_CHARGE_LINE_UTIL.Get_CoverageInfo with '
1568 || 'l_coverage_txn_group_id = ' || l_coverage_txn_group_id);
1569 end if;
1570
1571 -- swai: bug fix 4770958 (FP of 4499468)
1572 -- due to rearch of contracts for r12,
1573 -- no need to check for null coverage txn group anymore
1574 -- IF ( l_coverage_txn_group_id IS NULL
1575 -- OR x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1576 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1577 if (lc_proc_level >= lc_debug_level) then
1578 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1579 'Coverage information could not be determined for the contract and business process.');
1580 end if;
1581 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_UTIL_NO_COVG');
1582 -- Coverage information could not be determined for the contract and business process.
1583 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_ERROR_MSG );
1584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1585 END IF;
1586 END IF;
1587
1588 l_numRows := p_MLE_lines_tbl.COUNT;
1589
1590 if (lc_stat_level >= lc_debug_level) then
1591 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1592 'Begin LOOP through p_MLE_lines_tbl table');
1593 end if;
1594
1595 FOR i IN 1..l_numRows LOOP
1596 --DBMS_OUTPUT.put_line( 'NEW: Inside the main loop' );
1597
1598 -- Reset the values for the current record.
1599 l_skip_curr_rec := FALSE;
1600 l_curr_inv_item := p_MLE_lines_tbl( i ).inventory_item_id;
1601
1602 if (lc_stat_level >= lc_debug_level) then
1603 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1604 'Processing item - id = ' || l_curr_inv_item
1605 || ' name = ' || p_MLE_lines_tbl( i ).item_name
1606 || ', for count = ' || i);
1607 end if;
1608
1609 -- If a line happens to be -ve then we should ignore the line.
1610 -- This may happen when we use SUM(qty) for material transactions
1611 -- as more material can be returned than issued.
1612 IF ( p_MLE_lines_tbl( i ).quantity IS NULL
1613 OR p_MLE_lines_tbl( i ).quantity <= 0 ) THEN
1614 l_skip_curr_rec := TRUE;
1615 if (lc_stat_level >= lc_debug_level) then
1616 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1617 'The transaction quantity value is either NULL or 0. '
1618 || 'p_MLE_lines_tbl( i ).quantity = ' || p_MLE_lines_tbl( i ).quantity);
1619 end if;
1620 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_NEG_QTY');
1621 -- The net transactable quantity for the item $ITEM was found to be negative.
1622 -- The lines for the item will be ignored.
1623 FND_MESSAGE.SET_TOKEN( 'ITEM', p_MLE_lines_tbl( i ).item_name );
1624 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1625 END IF;
1626
1627 -- We derive/validate lots of values that depend just on the item.
1628 -- Hence, for each subsequent record we need not do the entire processing
1629 -- again if the item is same. We utilize the values from the previous
1630 -- processing for the current record.
1631 IF ((l_last_inv_item IS NULL
1632 OR ( l_last_inv_item <> l_curr_inv_item )) AND NOT l_skip_curr_rec) THEN
1633
1634 if (lc_stat_level >= lc_debug_level) then
1635 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1636 'Current item being processed - id = ' || l_curr_inv_item
1637 || ' name = ' || p_MLE_lines_tbl( i ).item_name
1638 || ', for count = ' || i);
1639 end if;
1640
1641 --DBMS_OUTPUT.put_line( 'NEW: Inside the item loop again' );
1642
1643 -- get the selling price of the item
1644 -- if no selling price, then we cannot determine charge, so
1645 -- log a warning.
1646 -- The reason we put the following code in a plsql block
1647 -- is because the API throws an excetion when there is no success.
1648 BEGIN
1649 if (lc_proc_level >= lc_debug_level) then
1650 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1651 'Calling CSD_PROCESS_UTIL.GET_CHARGE_SELLING_PRICE with'
1652 || ' p_inventory_item_id = ' || p_MLE_lines_tbl( i ).inventory_item_id
1653 || ' p_price_list_header_id = ' || p_price_list_id
1654 || ' p_unit_of_measure_code = ' || p_MLE_lines_tbl( i ).uom
1655 || ' p_currency_code = ' || p_currency_code
1656 || ' p_quantity_required = ' || p_MLE_lines_tbl( i ).quantity);
1657 end if;
1658 l_unit_selling_price := NULL;
1659
1660 /*bug#3875036 */
1661 CSD_PROCESS_UTIL.GET_CHARGE_SELLING_PRICE( p_inventory_item_id => p_MLE_lines_tbl( i ).inventory_item_id,
1662 p_price_list_header_id => p_price_list_id,
1663 p_unit_of_measure_code => p_MLE_lines_tbl( i ).uom,
1664 p_currency_code => p_currency_code,
1665 p_quantity_required => p_MLE_lines_tbl( i ).quantity,
1666 p_account_id => l_account_id, --bug#3875036
1667 p_org_id => l_org_id, -- Added for R12
1668 p_pricing_rec => l_pricing_rec,
1669 x_selling_price => l_unit_selling_price,
1670 x_return_status => l_return_status,
1671 x_msg_count => l_msg_count,
1672 x_msg_data => l_msg_data );
1673
1674 if (lc_proc_level >= lc_debug_level) then
1675 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1676 'Returned form CSD_PROCESS_UTIL.GET_CHARGE_SELLING_PRICE with'
1677 || ' l_unit_selling_price = ' || l_unit_selling_price);
1678 end if;
1679
1680 -- If API returned NULL value for the selling price
1681 -- we consider it's an error.
1682 IF l_unit_selling_price IS NULL THEN
1683 RAISE FND_API.G_eXC_ERROR;
1684 END IF;
1685
1686 EXCEPTION
1687 -- The reason we only handle 'FND_API.G_EXC_ERROR' exception
1688 -- is because the above API throws only one type of exception.
1689 -- If there is any other exception then it should be caught
1690 -- outside the loop in the main EXCEPTION block of the procedure.
1691 WHEN FND_API.G_EXC_ERROR THEN
1692 --DBMS_OUTPUT.put_line( 'NEW: The unit price is '
1693 -- || l_unit_selling_price );
1694 --DBMS_OUTPUT.put_line( 'NEW: The status is '
1695 -- || l_return_status );
1696
1697 if (lc_proc_level >= lc_debug_level) then
1698 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1699 'Inside EXC ERROR for the block calling CSD_PROCESS_UTIL.GET_CHARGE_SELLING_PRICE.');
1700 end if;
1701
1702 l_skip_curr_rec := TRUE;
1703 -- Get the price list name if the it's NULL
1704 -- and then use it to log message.
1705 IF ( l_price_list_name IS NULL ) THEN
1706 --DBMS_OUTPUT.put_line( 'NEW: price list is NULL' );
1707 BEGIN
1708 SELECT name
1709 INTO l_price_list_name
1710 FROM qp_list_headers_vl
1711 WHERE list_header_id = p_price_list_id;
1712 EXCEPTION
1713 WHEN NO_DATA_FOUND THEN
1714 if (lc_stat_level >= lc_debug_level) then
1715 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1716 'Could not determine the price list name for p_price_list_id = ' || p_price_list_id);
1717 end if;
1718 --DBMS_OUTPUT.put_line( 'NEW: no data found' );
1719 l_price_list_name := p_price_list_id;
1720 END;
1721 END IF;
1722 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_NOPRICE_ITEM_UOM' );
1723 -- Unable to determine selling price for the item $ITEM and unit of measure $UOM for the price list $PRICE_LIST.
1724 FND_MESSAGE.SET_TOKEN( 'ITEM',
1725 p_MLE_lines_tbl( i ).item_name );
1726 FND_MESSAGE.SET_TOKEN( 'PRICE_LIST', l_price_list_name );
1727 FND_MESSAGE.SET_TOKEN( 'UOM', p_MLE_lines_tbl( i ).uom );
1728 --DBMS_OUTPUT.put_line( 'NEW: before the message was added' );
1729 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1730 --DBMS_OUTPUT.put_line( 'NEW: after the message was added' );
1731 END;
1732
1733 --DBMS_OUTPUT.put_line( 'NEW: after getting the selling price' );
1734
1735 if (lc_stat_level >= lc_debug_level) then
1736 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1737 'Checking if the Transaction Billing Type is null. '
1738 || 'p_MLE_lines_tbl(' || i || ').txn_billing_type_id = ' || p_MLE_lines_tbl( i ).txn_billing_type_id);
1739 end if;
1740
1741 -- Txn Billing type is required. Log a warning if it is missing.
1742 -- Else we derive 'No Charge Flag' and 'Line Type/Category Code'.
1743 IF ( p_MLE_lines_tbl( i ).txn_billing_type_id IS NULL ) THEN
1744 --DBMS_OUTPUT.put_line( 'NEW: txn bill type null' );
1745
1746 if (lc_stat_level >= lc_debug_level) then
1747 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1748 'Transaction billing type found to be NULL');
1749 end if;
1750
1751 l_skip_curr_rec := TRUE;
1752 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_NO_ITEM_SAR' );
1753 -- Unable to determine service activity billing type for the item $ITEM.
1754 FND_MESSAGE.SET_TOKEN( 'ITEM', p_MLE_lines_tbl( i ).item_name );
1755 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1756 ELSE
1757 --DBMS_OUTPUT.put_line( 'NEW: txn bill type is NOT NULL' );
1758
1759 if (lc_stat_level >= lc_debug_level) then
1760 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1761 'Transaction billing type is NOT NULL');
1762 end if;
1763
1764 if (lc_proc_level >= lc_debug_level) then
1765 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1766 'Calling procedure CSD_PROCESS_UTIL.Get_No_Chg_Flag with '
1767 || 'p_MLE_lines_tbl( i ).txn_billing_type_id = ' || p_MLE_lines_tbl( i ).txn_billing_type_id);
1768 end if;
1769
1770 -- Check if the 'No Charge Flag' is checked for the txn billing type.
1771 l_no_charge_flag := CSD_PROCESS_UTIL.Get_No_Chg_Flag( p_MLE_lines_tbl( i ).txn_billing_type_id );
1772
1773 if (lc_proc_level >= lc_debug_level) then
1774 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1775 'Returned from procedure CSD_PROCESS_UTIL.Get_No_Chg_Flag with '
1776 || 'l_no_charge_flag = ' || l_no_charge_flag);
1777 end if;
1778
1779 /*
1780 The extended price is set to 0 in the later part of the code.
1781
1782 IF ( NVL( l_no_charge_flag, 'N' ) = 'Y' ) THEN
1783 l_unit_selling_price := 0;
1784 END IF;
1785 */
1786
1787 --DBMS_OUTPUT.put_line( 'NEW: Before calling get line type' );
1788
1789 -- Initialize the variables.
1790 l_line_type_id := NULL;
1791 l_line_category_code := NULL;
1792
1793 if (lc_proc_level >= lc_debug_level) then
1794 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1795 'Calling procedure CSD_PROCESS_UTIL.get_line_type with '
1796 || 'p_txn_billing_type_id = ' || p_MLE_lines_tbl( i ).txn_billing_type_id
1797 || ' and p_org_id = ' || l_org_id);
1798 end if;
1799
1800 -- Get the line type for the txn billing type.
1801 CSD_PROCESS_UTIL.get_line_type( p_txn_billing_type_id => p_MLE_lines_tbl( i ).txn_billing_type_id,
1802 p_org_id => l_org_id,
1803 x_line_type_id => l_line_type_id,
1804 x_line_category_code => l_line_category_code,
1805 x_return_status => l_return_status );
1806
1807 if (lc_proc_level >= lc_debug_level) then
1808 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1809 'Returned from procedure CSD_PROCESS_UTIL.get_line_type with '
1810 || 'x_line_type_id = ' || l_line_type_id
1811 || ', x_line_category_code = ' || l_line_category_code
1812 || ', x_return_status = ' || l_return_status);
1813 end if;
1814
1815 --DBMS_OUTPUT.put_line( 'NEW: after get line type' );
1816
1817 -- Line type id is a required field. If the value is NULL then
1818 -- the current record is ineligible for further processing.
1819 IF ( l_line_type_id IS NULL
1820 OR l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1821 --DBMS_OUTPUT.put_line( 'NEW: inside the line_typeId NULL check.' );
1822 l_skip_curr_rec := TRUE;
1823 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_LINE_TYPE_MISS');
1824 -- Line type, for the current organization, has not been set for the item $ITEM. Check the service activity billing types set up.
1825 FND_MESSAGE.SET_TOKEN( 'ITEM', p_MLE_lines_tbl( i ).item_name );
1826 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1827 END IF;
1828
1829 --DBMS_OUTPUT.put_line( 'NEW: after line type id null check' );
1830
1831 IF ( l_line_category_code IS NULL
1832 OR l_line_category_code <> G_LINE_CATEGORY_CODE_ORDER ) THEN
1833 --DBMS_OUTPUT.put_line( 'NEW: inside line cat code null check' );
1834 l_skip_curr_rec := TRUE;
1835 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_LINE_CAT_MISS');
1836 -- Line category code, for the current organization, is either incorrect or not set for the item $ITEM. Check the service activity billing types set up.
1837 FND_MESSAGE.SET_TOKEN( 'ITEM', p_MLE_lines_tbl( i ).item_name );
1838 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1839 END IF;
1840 END IF;
1841
1842 --DBMS_OUTPUT.put_line( 'NEW: after line cat null check' );
1843
1844 -- We ignore the record if instance number is required
1845 -- for the item, as we are unable to determine the instance number.
1846 -- Charges require Instance number on a line if the txn billing
1847 -- type is defined as an IB subtype and item is IB trackable.
1848 --
1849
1850 -- vkjain 01/20/2004
1851 -- The following comment does not apply as Charges DO validate for
1852 -- instance num even for ORDER/SHIP lines. The code has been uncommented
1853 -- and the validation is reinstated.
1854 /**************************************************************************
1855 -- Shiv Ragunathan, 11/07/03, Commenting out this as based on the discussion
1856 -- with Vivek, Charges requires Instance Number for the above case
1857 -- only for RMA lines, Since actual lines are considered as Order lines,
1858 -- not RMA lines, commenting this out, Later the owner of the code can remove
1859 -- this piece of code and obsolete the message, if not used
1860 **************************************************************************/
1861
1862 IF ( p_MLE_lines_tbl( i ).txn_billing_type_id IS NOT NULL AND
1863 p_MLE_lines_tbl( i ).comms_nl_trackable_flag = 'Y') THEN
1864 --DBMS_OUTPUT.put_line( 'NEW: Inside the IB check' );
1865 l_num_subtypes := 0;
1866
1867 if (lc_stat_level >= lc_debug_level) then
1868 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1869 'Opening cursor count_csi_txn_subtypes using '
1870 || 'txn_billing_type_id = ' || p_MLE_lines_tbl( i ).txn_billing_type_id);
1871 end if;
1872
1873 -- check if any csi txn subtypes exist. if at least one does, then throw an error
1874 OPEN count_csi_txn_subtypes( p_MLE_lines_tbl( i ).txn_billing_type_id );
1875 FETCH count_csi_txn_subtypes INTO l_num_subtypes;
1876 CLOSE count_csi_txn_subtypes;
1877
1878 if (lc_stat_level >= lc_debug_level) then
1879 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1880 'Closed cursor count_csi_txn_subtypes. Value returned '
1881 || 'l_num_subtypes = ' || l_num_subtypes);
1882 end if;
1883
1884 --DBMS_OUTPUT.put_line( 'NEW: l_num_subtypes is ' || l_num_subtypes );
1885 --DBMS_OUTPUT.put_line( 'NEW: item is ' || p_MLE_lines_tbl( i ).inventory_item_id );
1886 IF ( l_num_subtypes > 0 ) THEN
1887 l_skip_curr_rec := TRUE;
1888 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_IB_REQ' );
1889 -- Instance number for the item $ITEM is required, based on it's service activity billing type set up.
1890 FND_MESSAGE.SET_TOKEN( 'ITEM',
1891 p_MLE_lines_tbl( i ).item_name );
1892 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1893 END IF;
1894 END IF;
1895
1896 --DBMS_OUTPUT.put_line( 'NEW: outside IB check' );
1897
1898 -- Defaulting the discount price to be same as the extended price.
1899 -- This is useful in the event no contract or discount
1900 -- is deteremined. In that case, the discounted price should be
1901 -- same as the extended price.
1902 l_extended_price := l_unit_selling_price * nvl(p_MLE_lines_tbl( i ).quantity,0);
1903 l_discounted_price := l_extended_price;
1904
1905 -- We would like to derive the discount only if the current line had
1906 -- no prior errors and the selling price is not zero.
1907 -- Assuming no discount can be applied if the price is zero.
1908 IF ( NOT l_skip_curr_rec
1909 AND l_unit_selling_price > 0
1910 AND ( p_contract_line_id IS NOT NULL )
1911 AND ( p_business_process_id IS NOT NULL ))
1912 /*AND ( l_coverage_txn_group_id IS NOT NULL )*/ -- 6882951, rfieldma
1913 -- FP of 6823603
1914 -- r12 re-arch, l_coverage_txn_group_id is allowed
1915 -- to be null.
1916 THEN
1917 --DBMS_OUTPUT.put_line( 'NEW: To get the discouted price.' );
1918
1919 if (lc_proc_level >= lc_debug_level) then
1920 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1921 'Calling procedure CSD_CHARGE_LINE_UTIL.GET_DISCOUNTEDPRICE with values - '
1922 || 'p_contract_line_id = ' || p_contract_line_id
1923 || ', p_repair_type_id = ' || p_repair_type_id
1924 || ', p_txn_billing_type_id = ' || p_MLE_lines_tbl( i ).txn_billing_type_id
1925 || ', p_coverage_txn_grp_id = ' || l_coverage_txn_group_id
1926 || ', p_extended_price = ' || l_extended_price
1927 || ', p_no_charge_flag = ' || l_no_charge_flag);
1928 end if;
1929
1930 CSD_CHARGE_LINE_UTIL.GET_DISCOUNTEDPRICE( p_api_version => 1.0,
1931 p_init_msg_list => 'F',
1932 p_contract_line_id => p_contract_line_id,
1933 p_repair_type_id => p_repair_type_id,
1934 p_txn_billing_type_id => p_MLE_lines_tbl( i ).txn_billing_type_id,
1935 p_coverage_txn_grp_id => l_coverage_txn_group_id,
1936 p_extended_price => l_extended_price,
1937 p_no_charge_flag => 'N', --l_no_charge_flag, as we always
1938 -- want to know the discount amount.
1939 x_discounted_price => l_discounted_price,
1940 x_return_status => l_return_status,
1941 x_msg_count => l_msg_count,
1942 x_msg_data => l_msg_data );
1943
1944 if (lc_proc_level >= lc_debug_level) then
1945 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1946 'Returned from procedure CSD_CHARGE_LINE_UTIL.GET_DISCOUNTEDPRICE with values - '
1947 || 'x_discounted_price = ' || l_discounted_price
1948 || ', x_return_status = ' || l_return_status);
1949 end if;
1950
1951 IF (( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1952 OR ( l_discounted_price IS NULL )) THEN
1953 l_skip_curr_rec := TRUE;
1954 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_DISCOUNT_PRICE');
1955 -- There was an error while trying to get discount price for the item $ITEM using the contract $CONTRACT_NUMBER.
1956 FND_MESSAGE.SET_TOKEN( 'ITEM', p_MLE_lines_tbl( i ).item_name);
1957 FND_MESSAGE.SET_TOKEN( 'CONTRACT_NUMBER', l_contract_number );
1958 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_ERROR_MSG );
1959 END IF;
1960 END IF;
1961
1962 -- We do not do Actuals costing.
1963 /*
1964 CSD_COST_ANALYSIS_PVT.Get_InvItemCost(
1965 p_api_version => 1.0,
1966 p_commit => fnd_api.g_false,
1967 p_init_msg_list => fnd_api.g_false,
1968 p_validation_level => fnd_api.g_valid_level_full,
1969 x_return_status => x_return_status,
1970 x_msg_count => x_msg_count,
1971 x_msg_data => x_msg_data,
1972 p_inventory_item_id => p_MLE_lines_tbl(i).inventory_item_id,
1973 p_quantity => p_MLE_lines_tbl(i).quantity,
1974 p_organization_id => p_organization_id,
1975 p_charge_date => sysdate,
1976 p_currency_code => p_currency_code,
1977 x_item_cost => px_charge_lines_tbl(l_curRow).item_cost
1978 );
1979 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1980 px_charge_lines_tbl(l_curRow).item_cost := null;
1981 --
1982 -- give warning message that cost could not be determined?
1983 -- x_warning_flag := FND_API.G_TRUE;
1984 -- x_return_status := FND_API.G_RET_STS_ERROR;
1985 --FND_MESSAGE.SET_NAME('CSD','CSD_EST_ESTIMATED_CHARGE_ERR');
1986 --FND_MESSAGE.SET_TOKEN('CONTRACT_NUMBER',px_charge_lines_tbl(l_curRow).contract_number);
1987 --FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1988 -- l_skip_curr_rec := TRUE;
1989 end if;
1990 */
1991
1992 l_last_inv_item := l_curr_inv_item;
1993 END IF;
1994
1995 IF ( NOT l_skip_curr_rec ) THEN
1996 -- This ensures that we always add a record for a new index.
1997 l_curRow := px_valid_MLE_lines_tbl.COUNT + 1;
1998
1999 if (lc_stat_level >= lc_debug_level) then
2000 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2001 'Current record# ' || l_curRow || ' is not skipped');
2002 end if;
2003
2004 --DBMS_OUTPUT.put_line( 'NEW: Inside the valid loop '
2005 -- || l_curRow );
2006
2007 -- Populate the 'valid' MLE table.
2008 px_valid_MLE_lines_tbl( l_curRow ).inventory_item_id := p_MLE_lines_tbl( i ).inventory_item_id;
2009 px_valid_MLE_lines_tbl( l_curRow ).uom := p_MLE_lines_tbl( i ).uom;
2010 px_valid_MLE_lines_tbl( l_curRow ).quantity := p_MLE_lines_tbl( i ).quantity;
2011 px_valid_MLE_lines_tbl( l_curRow ).item_name := p_MLE_lines_tbl( i ).item_name;
2012 px_valid_MLE_lines_tbl( l_curRow ).comms_nl_trackable_flag := p_MLE_lines_tbl( i ).comms_nl_trackable_flag;
2013 px_valid_MLE_lines_tbl( l_curRow ).txn_billing_type_id := p_MLE_lines_tbl( i ).txn_billing_type_id;
2014 px_valid_MLE_lines_tbl( l_curRow ).transaction_type_id := p_MLE_lines_tbl( i ).transaction_type_id;
2015 px_valid_MLE_lines_tbl( l_curRow ).source_code := p_MLE_lines_tbl( i ).source_code;
2016 px_valid_MLE_lines_tbl( l_curRow ).source_id1 := p_MLE_lines_tbl( i ).source_id1;
2017 px_valid_MLE_lines_tbl( l_curRow ).source_id2 := p_MLE_lines_tbl( i ).source_id2;
2018 px_valid_MLE_lines_tbl( l_curRow ).item_cost := p_MLE_lines_tbl( i ).item_cost;
2019 px_valid_MLE_lines_tbl( l_curRow ).override_charge_flag := NVL( l_no_charge_flag,
2020 'N' );
2021
2022 -- Added for ER 3607765, vkjain.
2023 px_valid_MLE_lines_tbl( l_curRow ).resource_id := p_MLE_lines_tbl( i ).resource_id;
2024
2025 -- values from individual params
2026 px_charge_lines_tbl( l_curRow ).incident_id := p_incident_id;-- derived value, do we need this?
2027 px_charge_lines_tbl( l_curRow ).business_process_id := p_business_process_id;-- optional value?, used if derived value not available
2028 px_charge_lines_tbl( l_curRow ).currency_code := p_currency_code;-- derived value, do we need this?
2029 px_charge_lines_tbl( l_curRow ).price_list_id := p_price_list_id;-- required param
2030
2031 -- values from MLE_LINES
2032 px_charge_lines_tbl( l_curRow ).txn_billing_type_id := p_MLE_lines_tbl( i ).txn_billing_type_id;-- required param
2033 px_charge_lines_tbl( l_curRow ).transaction_type_id := p_MLE_lines_tbl( i ).transaction_type_id;
2034 px_charge_lines_tbl( l_curRow ).inventory_item_id_in := p_MLE_lines_tbl( i ).inventory_item_id;-- required param
2035 px_charge_lines_tbl( l_curRow ).unit_of_measure_code := p_MLE_lines_tbl( i ).uom;-- required param
2036 px_charge_lines_tbl( l_curRow ).quantity_required := p_MLE_lines_tbl( i ).quantity;-- required param
2037 px_charge_lines_tbl( l_curRow ).selling_price := l_unit_selling_price;
2038 IF (l_no_charge_flag = 'Y') THEN
2039 px_charge_lines_tbl( l_curRow ).after_warranty_cost := 0;
2040 ELSE
2041 px_charge_lines_tbl( l_curRow ).after_warranty_cost := l_discounted_price;
2042 END IF;
2043
2044 -- null items
2045 px_charge_lines_tbl( l_curRow ).source_number := NULL;
2046 px_charge_lines_tbl( l_curRow ).original_source_number := NULL;
2047 --px_charge_lines_tbl(l_curRow).reference_number := null;
2048 --px_charge_lines_tbl(l_curRow).order_number := null;
2049 --px_charge_lines_tbl(l_curRow).original_system_reference := null;
2050 --px_charge_lines_tbl(l_curRow).lot_number := null;
2051 --px_charge_lines_tbl(l_curRow).instance_id := null;
2052 --px_charge_lines_tbl(l_curRow).instance_number := null;
2053 --px_charge_lines_tbl(l_curRow).coverage_bill_rate_id := null;
2054 --px_charge_lines_tbl(l_curRow).sub_inventory := null;
2055 --px_charge_lines_tbl(l_curRow).return_reason := null;
2056 --px_charge_lines_tbl(l_curRow).last_update_date := null;
2057 --px_charge_lines_tbl(l_curRow).last_updated_by := null;
2058 --px_charge_lines_tbl(l_curRow).created_by := null;
2059 --px_charge_lines_tbl(l_curRow).last_update_login := null;
2060 --px_charge_lines_tbl(l_curRow).security_group_id := null;
2061
2062 -- non-null items
2063 --px_charge_lines_tbl(l_curRow).return_by_date := sysdate;
2064 --px_charge_lines_tbl(l_curRow).creation_date := sysdate;
2065 -- px_charge_lines_tbl(l_curRow).charge_line_type := G_CHARGE_LINE_TYPE_ACTUAL;
2066 px_charge_lines_tbl( l_curRow ).line_type_id := l_line_type_id;
2067 px_charge_lines_tbl( l_curRow ).line_category_code := l_line_category_code;
2068 px_charge_lines_tbl( l_curRow ).original_source_code := G_CHARGES_SOURCE_CODE_DR;
2069 px_charge_lines_tbl( l_curRow ).original_source_id := p_repair_line_id;
2070 px_charge_lines_tbl( l_curRow ).source_code := G_CHARGES_SOURCE_CODE_DR;
2071 px_charge_lines_tbl( l_curRow ).source_id := p_repair_line_id;
2072 px_charge_lines_tbl( l_curRow ).charge_line_type := p_charge_line_type;
2073 px_charge_lines_tbl( l_curRow ).no_charge_flag := l_no_charge_flag;
2074 px_charge_lines_tbl( l_curRow ).interface_to_oe_flag := 'N';
2075 px_charge_lines_tbl( l_curRow ).contract_id := l_contract_id;
2076 --Contract re arch changes for R12
2077 px_charge_lines_tbl( l_curRow ).contract_line_id := p_contract_line_id;
2078 px_charge_lines_tbl( l_curRow ).coverage_id := l_coverage_id;
2079 px_charge_lines_tbl( l_curRow ).coverage_txn_group_id := l_coverage_txn_group_id;
2080 px_charge_lines_tbl( l_curRow ).apply_contract_discount := 'N';
2081 /* IF l_coverage_txn_group_id IS NOT NULL THEN */ -- 6823603, rfieldma, due to r12 re-arch
2082 -- there is not restriction on txn_grp_id
2083 px_charge_lines_tbl( l_curRow ).contract_discount_amount := l_extended_price - l_discounted_price;
2084 /* END IF;*/
2085
2086 ELSE
2087 -- If the current row is not valid then set the OUT flag.
2088 x_warning_flag := FND_API.G_TRUE;
2089 --DBMS_OUTPUT.put_line( 'NEW: Inside the warnings ..'
2090 -- || x_warning_flag );
2091
2092 END IF;
2093
2094 END LOOP;
2095
2096 if (lc_stat_level >= lc_debug_level) then
2097 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2098 'End LOOP through p_MLE_lines_tbl table');
2099 end if;
2100
2101 --
2102 -- End API Body
2103 --
2104
2105 --IF ( x_warning_flag = FND_API.G_TRUE ) THEN
2106 --DBMS_OUTPUT.put_line( 'NEW: TRUE WARNING' );
2107 --END IF;
2108
2109 -- Standard check of p_commit.
2110
2111 IF FND_API.To_Boolean( p_commit ) THEN
2112 COMMIT WORK;
2113 END IF;-- Standard call to get message count and IF count is get message info.
2114
2115 -- logging
2116 if (lc_proc_level >= lc_debug_level) then
2117 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
2118 'Leaving CSD_CHARGE_LINE_UTIL.convert_to_charge_lines');
2119 end if;
2120
2121 /*
2122 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2123 p_data => x_msg_data );
2124 */
2125
2126 EXCEPTION
2127 WHEN FND_API.G_EXC_ERROR THEN
2128 ROLLBACK TO Convert_To_Actual_Lines;
2129 --DBMS_OUTPUT.put_line( 'NEW: Inside EXC ERROR' );
2130 x_return_status := FND_API.G_RET_STS_ERROR;
2131 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2132 p_data => x_msg_data );
2133
2134 -- save message in debug log
2135 IF (lc_excep_level >= lc_debug_level) THEN
2136 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2137 'EXC_ERROR['||x_msg_data||']');
2138 END IF;
2139
2140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2141 ROLLBACK TO Convert_To_Actual_Lines;
2142 --DBMS_OUTPUT.put_line( 'NEW: Inside UNEXP ERROR' );
2143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2144 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2145 p_data => x_msg_data );
2146
2147 -- save message in debug log
2148 IF (lc_excep_level >= lc_debug_level) THEN
2149 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2150 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
2151 END IF;
2152
2153 WHEN OTHERS THEN
2154 ROLLBACK TO Convert_To_Actual_Lines;
2155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2156 --DBMS_OUTPUT.put_line( 'SQLCODE= '
2157 -- || SQLCODE );
2158 --DBMS_OUTPUT.put_line( 'SQLERRM= '
2159 -- || SQLERRM );
2160
2161 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2162 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, lc_api_name );
2163 END IF;
2164
2165 -- save message in debug log
2166 IF (lc_excep_level >= lc_debug_level) THEN
2167 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2168 'WHEN OTHERS THEN. SQL Message['||SQLERRM||']');
2169 END IF;
2170
2171 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_MLE_CHRG_FORMAT_ERR');
2172 -- Encountered an error while converting MLE lines into charge line format. SQLCODE = $SQLCODE, SQLERRM = $SQLERRM.
2173 FND_MESSAGE.set_token('SQLCODE' , SQLCODE);
2174 FND_MESSAGE.set_token('SQLERRM' , SQLERRM);
2175 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_ERROR_MSG );
2176 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2177 p_data => x_msg_data );
2178 END Convert_To_Charge_Lines;
2179
2180
2181
2182 END CSD_CHARGE_LINE_UTIL;