1 PACKAGE BODY CSD_CHARGE_LINE_UTIL AS
2 /* $Header: csdvclub.pls 120.5.12020000.2 2013/03/15 00:27:13 takwong 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 -- This cause for bug#14116085 issue.. this only excute once .. so if it is same item with different quantity. .it is wrong here.
1634 -- so remove this l_last_inv_item <> l_curr_inv_item issue.
1635 --bug#14116085 --14193854
1636
1637 IF (NOT l_skip_curr_rec) THEN
1638
1639 if (lc_stat_level >= lc_debug_level) then
1640 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1641 'Current item being processed - id = ' || l_curr_inv_item
1642 || ' name = ' || p_MLE_lines_tbl( i ).item_name
1643 || ', for count = ' || i);
1644 end if;
1645
1646 --DBMS_OUTPUT.put_line( 'NEW: Inside the item loop again' );
1647
1648 -- get the selling price of the item
1649 -- if no selling price, then we cannot determine charge, so
1650 -- log a warning.
1651 -- The reason we put the following code in a plsql block
1652 -- is because the API throws an excetion when there is no success.
1653 BEGIN
1654 if (lc_proc_level >= lc_debug_level) then
1655 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1656 'Calling CSD_PROCESS_UTIL.GET_CHARGE_SELLING_PRICE with'
1657 || ' p_inventory_item_id = ' || p_MLE_lines_tbl( i ).inventory_item_id
1658 || ' p_price_list_header_id = ' || p_price_list_id
1659 || ' p_unit_of_measure_code = ' || p_MLE_lines_tbl( i ).uom
1660 || ' p_currency_code = ' || p_currency_code
1661 || ' p_quantity_required = ' || p_MLE_lines_tbl( i ).quantity);
1662 end if;
1663 l_unit_selling_price := NULL;
1664
1665 /*bug#3875036 */
1666 CSD_PROCESS_UTIL.GET_CHARGE_SELLING_PRICE( p_inventory_item_id => p_MLE_lines_tbl( i ).inventory_item_id,
1667 p_price_list_header_id => p_price_list_id,
1668 p_unit_of_measure_code => p_MLE_lines_tbl( i ).uom,
1669 p_currency_code => p_currency_code,
1670 p_quantity_required => p_MLE_lines_tbl( i ).quantity,
1671 p_account_id => l_account_id, --bug#3875036
1672 p_org_id => l_org_id, -- Added for R12
1673 p_pricing_rec => l_pricing_rec,
1674 x_selling_price => l_unit_selling_price,
1675 x_return_status => l_return_status,
1676 x_msg_count => l_msg_count,
1677 x_msg_data => l_msg_data );
1678
1679 if (lc_proc_level >= lc_debug_level) then
1680 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1681 'Returned form CSD_PROCESS_UTIL.GET_CHARGE_SELLING_PRICE with'
1682 || ' l_unit_selling_price = ' || l_unit_selling_price);
1683 end if;
1684
1685 -- If API returned NULL value for the selling price
1686 -- we consider it's an error.
1687 IF l_unit_selling_price IS NULL THEN
1688 RAISE FND_API.G_eXC_ERROR;
1689 END IF;
1690
1691 EXCEPTION
1692 -- The reason we only handle 'FND_API.G_EXC_ERROR' exception
1693 -- is because the above API throws only one type of exception.
1694 -- If there is any other exception then it should be caught
1695 -- outside the loop in the main EXCEPTION block of the procedure.
1696 WHEN FND_API.G_EXC_ERROR THEN
1697 --DBMS_OUTPUT.put_line( 'NEW: The unit price is '
1698 -- || l_unit_selling_price );
1699 --DBMS_OUTPUT.put_line( 'NEW: The status is '
1700 -- || l_return_status );
1701
1702 if (lc_proc_level >= lc_debug_level) then
1703 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1704 'Inside EXC ERROR for the block calling CSD_PROCESS_UTIL.GET_CHARGE_SELLING_PRICE.');
1705 end if;
1706
1707 l_skip_curr_rec := TRUE;
1708 -- Get the price list name if the it's NULL
1709 -- and then use it to log message.
1710 IF ( l_price_list_name IS NULL ) THEN
1711 --DBMS_OUTPUT.put_line( 'NEW: price list is NULL' );
1712 BEGIN
1713 SELECT name
1714 INTO l_price_list_name
1715 FROM qp_list_headers_vl
1716 WHERE list_header_id = p_price_list_id;
1717 EXCEPTION
1718 WHEN NO_DATA_FOUND THEN
1719 if (lc_stat_level >= lc_debug_level) then
1720 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1721 'Could not determine the price list name for p_price_list_id = ' || p_price_list_id);
1722 end if;
1723 --DBMS_OUTPUT.put_line( 'NEW: no data found' );
1724 l_price_list_name := p_price_list_id;
1725 END;
1726 END IF;
1727 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_NOPRICE_ITEM_UOM' );
1728 -- Unable to determine selling price for the item $ITEM and unit of measure $UOM for the price list $PRICE_LIST.
1729 FND_MESSAGE.SET_TOKEN( 'ITEM',
1730 p_MLE_lines_tbl( i ).item_name );
1731 FND_MESSAGE.SET_TOKEN( 'PRICE_LIST', l_price_list_name );
1732 FND_MESSAGE.SET_TOKEN( 'UOM', p_MLE_lines_tbl( i ).uom );
1733 --DBMS_OUTPUT.put_line( 'NEW: before the message was added' );
1734 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1735 --DBMS_OUTPUT.put_line( 'NEW: after the message was added' );
1736 END;
1737
1738 --DBMS_OUTPUT.put_line( 'NEW: after getting the selling price' );
1739
1740 if (lc_stat_level >= lc_debug_level) then
1741 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1742 'Checking if the Transaction Billing Type is null. '
1743 || 'p_MLE_lines_tbl(' || i || ').txn_billing_type_id = ' || p_MLE_lines_tbl( i ).txn_billing_type_id);
1744 end if;
1745
1746 -- Txn Billing type is required. Log a warning if it is missing.
1747 -- Else we derive 'No Charge Flag' and 'Line Type/Category Code'.
1748 IF ( p_MLE_lines_tbl( i ).txn_billing_type_id IS NULL ) THEN
1749 --DBMS_OUTPUT.put_line( 'NEW: txn bill type null' );
1750
1751 if (lc_stat_level >= lc_debug_level) then
1752 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1753 'Transaction billing type found to be NULL');
1754 end if;
1755
1756 l_skip_curr_rec := TRUE;
1757 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_NO_ITEM_SAR' );
1758 -- Unable to determine service activity billing type for the item $ITEM.
1759 FND_MESSAGE.SET_TOKEN( 'ITEM', p_MLE_lines_tbl( i ).item_name );
1760 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1761 ELSE
1762 --DBMS_OUTPUT.put_line( 'NEW: txn bill type is NOT NULL' );
1763
1764 if (lc_stat_level >= lc_debug_level) then
1765 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1766 'Transaction billing type is NOT NULL');
1767 end if;
1768
1769 if (lc_proc_level >= lc_debug_level) then
1770 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1771 'Calling procedure CSD_PROCESS_UTIL.Get_No_Chg_Flag with '
1772 || 'p_MLE_lines_tbl( i ).txn_billing_type_id = ' || p_MLE_lines_tbl( i ).txn_billing_type_id);
1773 end if;
1774
1775 -- Check if the 'No Charge Flag' is checked for the txn billing type.
1776 l_no_charge_flag := CSD_PROCESS_UTIL.Get_No_Chg_Flag( p_MLE_lines_tbl( i ).txn_billing_type_id );
1777
1778 if (lc_proc_level >= lc_debug_level) then
1779 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1780 'Returned from procedure CSD_PROCESS_UTIL.Get_No_Chg_Flag with '
1781 || 'l_no_charge_flag = ' || l_no_charge_flag);
1782 end if;
1783
1784 /*
1785 The extended price is set to 0 in the later part of the code.
1786
1787 IF ( NVL( l_no_charge_flag, 'N' ) = 'Y' ) THEN
1788 l_unit_selling_price := 0;
1789 END IF;
1790 */
1791
1792 --DBMS_OUTPUT.put_line( 'NEW: Before calling get line type' );
1793
1794 -- Initialize the variables.
1795 l_line_type_id := NULL;
1796 l_line_category_code := NULL;
1797
1798 if (lc_proc_level >= lc_debug_level) then
1799 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1800 'Calling procedure CSD_PROCESS_UTIL.get_line_type with '
1801 || 'p_txn_billing_type_id = ' || p_MLE_lines_tbl( i ).txn_billing_type_id
1802 || ' and p_org_id = ' || l_org_id);
1803 end if;
1804
1805 -- Get the line type for the txn billing type.
1806 CSD_PROCESS_UTIL.get_line_type( p_txn_billing_type_id => p_MLE_lines_tbl( i ).txn_billing_type_id,
1807 p_org_id => l_org_id,
1808 x_line_type_id => l_line_type_id,
1809 x_line_category_code => l_line_category_code,
1810 x_return_status => l_return_status );
1811
1812 if (lc_proc_level >= lc_debug_level) then
1813 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1814 'Returned from procedure CSD_PROCESS_UTIL.get_line_type with '
1815 || 'x_line_type_id = ' || l_line_type_id
1816 || ', x_line_category_code = ' || l_line_category_code
1817 || ', x_return_status = ' || l_return_status);
1818 end if;
1819
1820 --DBMS_OUTPUT.put_line( 'NEW: after get line type' );
1821
1822 -- Line type id is a required field. If the value is NULL then
1823 -- the current record is ineligible for further processing.
1824 IF ( l_line_type_id IS NULL
1825 OR l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1826 --DBMS_OUTPUT.put_line( 'NEW: inside the line_typeId NULL check.' );
1827 l_skip_curr_rec := TRUE;
1828 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_LINE_TYPE_MISS');
1829 -- Line type, for the current organization, has not been set for the item $ITEM. Check the service activity billing types set up.
1830 FND_MESSAGE.SET_TOKEN( 'ITEM', p_MLE_lines_tbl( i ).item_name );
1831 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1832 END IF;
1833
1834 --DBMS_OUTPUT.put_line( 'NEW: after line type id null check' );
1835
1836 IF ( l_line_category_code IS NULL
1837 OR l_line_category_code <> G_LINE_CATEGORY_CODE_ORDER ) THEN
1838 --DBMS_OUTPUT.put_line( 'NEW: inside line cat code null check' );
1839 l_skip_curr_rec := TRUE;
1840 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_LINE_CAT_MISS');
1841 -- 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.
1842 FND_MESSAGE.SET_TOKEN( 'ITEM', p_MLE_lines_tbl( i ).item_name );
1843 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1844 END IF;
1845 END IF;
1846
1847 --DBMS_OUTPUT.put_line( 'NEW: after line cat null check' );
1848
1849 -- We ignore the record if instance number is required
1850 -- for the item, as we are unable to determine the instance number.
1851 -- Charges require Instance number on a line if the txn billing
1852 -- type is defined as an IB subtype and item is IB trackable.
1853 --
1854
1855 -- vkjain 01/20/2004
1856 -- The following comment does not apply as Charges DO validate for
1857 -- instance num even for ORDER/SHIP lines. The code has been uncommented
1858 -- and the validation is reinstated.
1859 /**************************************************************************
1860 -- Shiv Ragunathan, 11/07/03, Commenting out this as based on the discussion
1861 -- with Vivek, Charges requires Instance Number for the above case
1862 -- only for RMA lines, Since actual lines are considered as Order lines,
1863 -- not RMA lines, commenting this out, Later the owner of the code can remove
1864 -- this piece of code and obsolete the message, if not used
1865 **************************************************************************/
1866
1867 IF ( p_MLE_lines_tbl( i ).txn_billing_type_id IS NOT NULL AND
1868 p_MLE_lines_tbl( i ).comms_nl_trackable_flag = 'Y') THEN
1869 --DBMS_OUTPUT.put_line( 'NEW: Inside the IB check' );
1870 l_num_subtypes := 0;
1871
1872 if (lc_stat_level >= lc_debug_level) then
1873 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1874 'Opening cursor count_csi_txn_subtypes using '
1875 || 'txn_billing_type_id = ' || p_MLE_lines_tbl( i ).txn_billing_type_id);
1876 end if;
1877
1878 -- check if any csi txn subtypes exist. if at least one does, then throw an error
1879 OPEN count_csi_txn_subtypes( p_MLE_lines_tbl( i ).txn_billing_type_id );
1880 FETCH count_csi_txn_subtypes INTO l_num_subtypes;
1881 CLOSE count_csi_txn_subtypes;
1882
1883 if (lc_stat_level >= lc_debug_level) then
1884 FND_LOG.STRING(lc_stat_level, lc_mod_name,
1885 'Closed cursor count_csi_txn_subtypes. Value returned '
1886 || 'l_num_subtypes = ' || l_num_subtypes);
1887 end if;
1888
1889 --DBMS_OUTPUT.put_line( 'NEW: l_num_subtypes is ' || l_num_subtypes );
1890 --DBMS_OUTPUT.put_line( 'NEW: item is ' || p_MLE_lines_tbl( i ).inventory_item_id );
1891 IF ( l_num_subtypes > 0 ) THEN
1892 l_skip_curr_rec := TRUE;
1893 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_IB_REQ' );
1894 -- Instance number for the item $ITEM is required, based on it's service activity billing type set up.
1895 FND_MESSAGE.SET_TOKEN( 'ITEM',
1896 p_MLE_lines_tbl( i ).item_name );
1897 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1898 END IF;
1899 END IF;
1900
1901 --DBMS_OUTPUT.put_line( 'NEW: outside IB check' );
1902
1903 -- Defaulting the discount price to be same as the extended price.
1904 -- This is useful in the event no contract or discount
1905 -- is deteremined. In that case, the discounted price should be
1906 -- same as the extended price.
1907 l_extended_price := l_unit_selling_price * nvl(p_MLE_lines_tbl( i ).quantity,0);
1908 l_discounted_price := l_extended_price;
1909
1910 -- We would like to derive the discount only if the current line had
1911 -- no prior errors and the selling price is not zero.
1912 -- Assuming no discount can be applied if the price is zero.
1913 IF ( NOT l_skip_curr_rec
1914 AND l_unit_selling_price > 0
1915 AND ( p_contract_line_id IS NOT NULL )
1916 AND ( p_business_process_id IS NOT NULL ))
1917 /*AND ( l_coverage_txn_group_id IS NOT NULL )*/ -- 6882951, rfieldma
1918 -- FP of 6823603
1919 -- r12 re-arch, l_coverage_txn_group_id is allowed
1920 -- to be null.
1921 THEN
1922 --DBMS_OUTPUT.put_line( 'NEW: To get the discouted price.' );
1923
1924 if (lc_proc_level >= lc_debug_level) then
1925 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1926 'Calling procedure CSD_CHARGE_LINE_UTIL.GET_DISCOUNTEDPRICE with values - '
1927 || 'p_contract_line_id = ' || p_contract_line_id
1928 || ', p_repair_type_id = ' || p_repair_type_id
1929 || ', p_txn_billing_type_id = ' || p_MLE_lines_tbl( i ).txn_billing_type_id
1930 || ', p_coverage_txn_grp_id = ' || l_coverage_txn_group_id
1931 || ', p_extended_price = ' || l_extended_price
1932 || ', p_no_charge_flag = ' || l_no_charge_flag);
1933 end if;
1934
1935 CSD_CHARGE_LINE_UTIL.GET_DISCOUNTEDPRICE( p_api_version => 1.0,
1936 p_init_msg_list => 'F',
1937 p_contract_line_id => p_contract_line_id,
1938 p_repair_type_id => p_repair_type_id,
1939 p_txn_billing_type_id => p_MLE_lines_tbl( i ).txn_billing_type_id,
1940 p_coverage_txn_grp_id => l_coverage_txn_group_id,
1941 p_extended_price => l_extended_price,
1942 p_no_charge_flag => 'N', --l_no_charge_flag, as we always
1943 -- want to know the discount amount.
1944 x_discounted_price => l_discounted_price,
1945 x_return_status => l_return_status,
1946 x_msg_count => l_msg_count,
1947 x_msg_data => l_msg_data );
1948
1949 if (lc_proc_level >= lc_debug_level) then
1950 FND_LOG.STRING(lc_proc_level, lc_mod_name,
1951 'Returned from procedure CSD_CHARGE_LINE_UTIL.GET_DISCOUNTEDPRICE with values - '
1952 || 'x_discounted_price = ' || l_discounted_price
1953 || ', x_return_status = ' || l_return_status);
1954 end if;
1955
1956 IF (( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1957 OR ( l_discounted_price IS NULL )) THEN
1958 l_skip_curr_rec := TRUE;
1959 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_DISCOUNT_PRICE');
1960 -- There was an error while trying to get discount price for the item $ITEM using the contract $CONTRACT_NUMBER.
1961 FND_MESSAGE.SET_TOKEN( 'ITEM', p_MLE_lines_tbl( i ).item_name);
1962 FND_MESSAGE.SET_TOKEN( 'CONTRACT_NUMBER', l_contract_number );
1963 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_ERROR_MSG );
1964 END IF;
1965 END IF;
1966
1967 -- We do not do Actuals costing.
1968 /*
1969 CSD_COST_ANALYSIS_PVT.Get_InvItemCost(
1970 p_api_version => 1.0,
1971 p_commit => fnd_api.g_false,
1972 p_init_msg_list => fnd_api.g_false,
1973 p_validation_level => fnd_api.g_valid_level_full,
1974 x_return_status => x_return_status,
1975 x_msg_count => x_msg_count,
1976 x_msg_data => x_msg_data,
1977 p_inventory_item_id => p_MLE_lines_tbl(i).inventory_item_id,
1978 p_quantity => p_MLE_lines_tbl(i).quantity,
1979 p_organization_id => p_organization_id,
1980 p_charge_date => sysdate,
1981 p_currency_code => p_currency_code,
1982 x_item_cost => px_charge_lines_tbl(l_curRow).item_cost
1983 );
1984 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1985 px_charge_lines_tbl(l_curRow).item_cost := null;
1986 --
1987 -- give warning message that cost could not be determined?
1988 -- x_warning_flag := FND_API.G_TRUE;
1989 -- x_return_status := FND_API.G_RET_STS_ERROR;
1990 --FND_MESSAGE.SET_NAME('CSD','CSD_EST_ESTIMATED_CHARGE_ERR');
1991 --FND_MESSAGE.SET_TOKEN('CONTRACT_NUMBER',px_charge_lines_tbl(l_curRow).contract_number);
1992 --FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_WARNING_MSG );
1993 -- l_skip_curr_rec := TRUE;
1994 end if;
1995 */
1996
1997 l_last_inv_item := l_curr_inv_item;
1998 END IF;
1999
2000 IF ( NOT l_skip_curr_rec ) THEN
2001 -- This ensures that we always add a record for a new index.
2002 l_curRow := px_valid_MLE_lines_tbl.COUNT + 1;
2003
2004 if (lc_stat_level >= lc_debug_level) then
2005 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2006 'Current record# ' || l_curRow || ' is not skipped');
2007 end if;
2008
2009 --DBMS_OUTPUT.put_line( 'NEW: Inside the valid loop '
2010 -- || l_curRow );
2011
2012 -- Populate the 'valid' MLE table.
2013 px_valid_MLE_lines_tbl( l_curRow ).inventory_item_id := p_MLE_lines_tbl( i ).inventory_item_id;
2014 px_valid_MLE_lines_tbl( l_curRow ).uom := p_MLE_lines_tbl( i ).uom;
2015 px_valid_MLE_lines_tbl( l_curRow ).quantity := p_MLE_lines_tbl( i ).quantity;
2016 px_valid_MLE_lines_tbl( l_curRow ).item_name := p_MLE_lines_tbl( i ).item_name;
2017 px_valid_MLE_lines_tbl( l_curRow ).comms_nl_trackable_flag := p_MLE_lines_tbl( i ).comms_nl_trackable_flag;
2018 px_valid_MLE_lines_tbl( l_curRow ).txn_billing_type_id := p_MLE_lines_tbl( i ).txn_billing_type_id;
2019 px_valid_MLE_lines_tbl( l_curRow ).transaction_type_id := p_MLE_lines_tbl( i ).transaction_type_id;
2020 px_valid_MLE_lines_tbl( l_curRow ).source_code := p_MLE_lines_tbl( i ).source_code;
2021 px_valid_MLE_lines_tbl( l_curRow ).source_id1 := p_MLE_lines_tbl( i ).source_id1;
2022 px_valid_MLE_lines_tbl( l_curRow ).source_id2 := p_MLE_lines_tbl( i ).source_id2;
2023 px_valid_MLE_lines_tbl( l_curRow ).item_cost := p_MLE_lines_tbl( i ).item_cost;
2024 px_valid_MLE_lines_tbl( l_curRow ).override_charge_flag := NVL( l_no_charge_flag,
2025 'N' );
2026
2027 -- Added for ER 3607765, vkjain.
2028 px_valid_MLE_lines_tbl( l_curRow ).resource_id := p_MLE_lines_tbl( i ).resource_id;
2029
2030 -- values from individual params
2031 px_charge_lines_tbl( l_curRow ).incident_id := p_incident_id;-- derived value, do we need this?
2032 px_charge_lines_tbl( l_curRow ).business_process_id := p_business_process_id;-- optional value?, used if derived value not available
2033 px_charge_lines_tbl( l_curRow ).currency_code := p_currency_code;-- derived value, do we need this?
2034 px_charge_lines_tbl( l_curRow ).price_list_id := p_price_list_id;-- required param
2035
2036 -- values from MLE_LINES
2037 px_charge_lines_tbl( l_curRow ).txn_billing_type_id := p_MLE_lines_tbl( i ).txn_billing_type_id;-- required param
2038 px_charge_lines_tbl( l_curRow ).transaction_type_id := p_MLE_lines_tbl( i ).transaction_type_id;
2039 px_charge_lines_tbl( l_curRow ).inventory_item_id_in := p_MLE_lines_tbl( i ).inventory_item_id;-- required param
2040 px_charge_lines_tbl( l_curRow ).unit_of_measure_code := p_MLE_lines_tbl( i ).uom;-- required param
2041 px_charge_lines_tbl( l_curRow ).quantity_required := p_MLE_lines_tbl( i ).quantity;-- required param
2042 px_charge_lines_tbl( l_curRow ).selling_price := l_unit_selling_price;
2043 IF (l_no_charge_flag = 'Y') THEN
2044 px_charge_lines_tbl( l_curRow ).after_warranty_cost := 0;
2045 ELSE
2046 px_charge_lines_tbl( l_curRow ).after_warranty_cost := l_discounted_price;
2047 END IF;
2048
2049 -- null items
2050 px_charge_lines_tbl( l_curRow ).source_number := NULL;
2051 px_charge_lines_tbl( l_curRow ).original_source_number := NULL;
2052 --px_charge_lines_tbl(l_curRow).reference_number := null;
2053 --px_charge_lines_tbl(l_curRow).order_number := null;
2054 --px_charge_lines_tbl(l_curRow).original_system_reference := null;
2055 --px_charge_lines_tbl(l_curRow).lot_number := null;
2056 --px_charge_lines_tbl(l_curRow).instance_id := null;
2057 --px_charge_lines_tbl(l_curRow).instance_number := null;
2058 --px_charge_lines_tbl(l_curRow).coverage_bill_rate_id := null;
2059 --px_charge_lines_tbl(l_curRow).sub_inventory := null;
2060 --px_charge_lines_tbl(l_curRow).return_reason := null;
2061 --px_charge_lines_tbl(l_curRow).last_update_date := null;
2062 --px_charge_lines_tbl(l_curRow).last_updated_by := null;
2063 --px_charge_lines_tbl(l_curRow).created_by := null;
2064 --px_charge_lines_tbl(l_curRow).last_update_login := null;
2065 --px_charge_lines_tbl(l_curRow).security_group_id := null;
2066
2067 -- non-null items
2068 --px_charge_lines_tbl(l_curRow).return_by_date := sysdate;
2069 --px_charge_lines_tbl(l_curRow).creation_date := sysdate;
2070 -- px_charge_lines_tbl(l_curRow).charge_line_type := G_CHARGE_LINE_TYPE_ACTUAL;
2071 px_charge_lines_tbl( l_curRow ).line_type_id := l_line_type_id;
2072 px_charge_lines_tbl( l_curRow ).line_category_code := l_line_category_code;
2073 px_charge_lines_tbl( l_curRow ).original_source_code := G_CHARGES_SOURCE_CODE_DR;
2074 px_charge_lines_tbl( l_curRow ).original_source_id := p_repair_line_id;
2075 px_charge_lines_tbl( l_curRow ).source_code := G_CHARGES_SOURCE_CODE_DR;
2076 px_charge_lines_tbl( l_curRow ).source_id := p_repair_line_id;
2077 px_charge_lines_tbl( l_curRow ).charge_line_type := p_charge_line_type;
2078 px_charge_lines_tbl( l_curRow ).no_charge_flag := l_no_charge_flag;
2079 px_charge_lines_tbl( l_curRow ).interface_to_oe_flag := 'N';
2080 px_charge_lines_tbl( l_curRow ).contract_id := l_contract_id;
2081 --Contract re arch changes for R12
2082 px_charge_lines_tbl( l_curRow ).contract_line_id := p_contract_line_id;
2083 px_charge_lines_tbl( l_curRow ).coverage_id := l_coverage_id;
2084 px_charge_lines_tbl( l_curRow ).coverage_txn_group_id := l_coverage_txn_group_id;
2085 px_charge_lines_tbl( l_curRow ).apply_contract_discount := 'N';
2086 /* IF l_coverage_txn_group_id IS NOT NULL THEN */ -- 6823603, rfieldma, due to r12 re-arch
2087 -- there is not restriction on txn_grp_id
2088 px_charge_lines_tbl( l_curRow ).contract_discount_amount := l_extended_price - l_discounted_price;
2089 /* END IF;*/
2090
2091 ELSE
2092 -- If the current row is not valid then set the OUT flag.
2093 x_warning_flag := FND_API.G_TRUE;
2094 --DBMS_OUTPUT.put_line( 'NEW: Inside the warnings ..'
2095 -- || x_warning_flag );
2096
2097 END IF;
2098
2099 END LOOP;
2100
2101 if (lc_stat_level >= lc_debug_level) then
2102 FND_LOG.STRING(lc_stat_level, lc_mod_name,
2103 'End LOOP through p_MLE_lines_tbl table');
2104 end if;
2105
2106 --
2107 -- End API Body
2108 --
2109
2110 --IF ( x_warning_flag = FND_API.G_TRUE ) THEN
2111 --DBMS_OUTPUT.put_line( 'NEW: TRUE WARNING' );
2112 --END IF;
2113
2114 -- Standard check of p_commit.
2115
2116 IF FND_API.To_Boolean( p_commit ) THEN
2117 COMMIT WORK;
2118 END IF;-- Standard call to get message count and IF count is get message info.
2119
2120 -- logging
2121 if (lc_proc_level >= lc_debug_level) then
2122 FND_LOG.STRING(lc_proc_level, lc_mod_name || '.END',
2123 'Leaving CSD_CHARGE_LINE_UTIL.convert_to_charge_lines');
2124 end if;
2125
2126 /*
2127 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2128 p_data => x_msg_data );
2129 */
2130
2131 EXCEPTION
2132 WHEN FND_API.G_EXC_ERROR THEN
2133 ROLLBACK TO Convert_To_Actual_Lines;
2134 --DBMS_OUTPUT.put_line( 'NEW: Inside EXC ERROR' );
2135 x_return_status := FND_API.G_RET_STS_ERROR;
2136 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2137 p_data => x_msg_data );
2138
2139 -- save message in debug log
2140 IF (lc_excep_level >= lc_debug_level) THEN
2141 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2142 'EXC_ERROR['||x_msg_data||']');
2143 END IF;
2144
2145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2146 ROLLBACK TO Convert_To_Actual_Lines;
2147 --DBMS_OUTPUT.put_line( 'NEW: Inside UNEXP ERROR' );
2148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2149 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2150 p_data => x_msg_data );
2151
2152 -- save message in debug log
2153 IF (lc_excep_level >= lc_debug_level) THEN
2154 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2155 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
2156 END IF;
2157
2158 WHEN OTHERS THEN
2159 ROLLBACK TO Convert_To_Actual_Lines;
2160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2161 --DBMS_OUTPUT.put_line( 'SQLCODE= '
2162 -- || SQLCODE );
2163 --DBMS_OUTPUT.put_line( 'SQLERRM= '
2164 -- || SQLERRM );
2165
2166 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2167 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, lc_api_name );
2168 END IF;
2169
2170 -- save message in debug log
2171 IF (lc_excep_level >= lc_debug_level) THEN
2172 FND_LOG.STRING(lc_excep_level, lc_mod_name,
2173 'WHEN OTHERS THEN. SQL Message['||SQLERRM||']');
2174 END IF;
2175
2176 FND_MESSAGE.SET_NAME( 'CSD', 'CSD_CHRG_MLE_CHRG_FORMAT_ERR');
2177 -- Encountered an error while converting MLE lines into charge line format. SQLCODE = $SQLCODE, SQLERRM = $SQLERRM.
2178 FND_MESSAGE.set_token('SQLCODE' , SQLCODE);
2179 FND_MESSAGE.set_token('SQLERRM' , SQLERRM);
2180 FND_MSG_PUB.add_detail( p_message_type => FND_MSG_PUB.G_ERROR_MSG );
2181 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2182 p_data => x_msg_data );
2183 END Convert_To_Charge_Lines;
2184
2185
2186
2187 END CSD_CHARGE_LINE_UTIL;
[Home] [Help]