[Home] [Help]
PACKAGE BODY: APPS.OKL_CSBRW_PVT
Source
1 PACKAGE BODY OKL_CSBRW_PVT AS
2 /* $Header: OKLRFBRB.pls 120.2 2005/10/30 04:33:43 appldev noship $ */
3 FUNCTION cust_amount_info( p_api_version IN NUMBER,
4 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
5 x_msg_count OUT NOCOPY NUMBER,
6 x_msg_data OUT NOCOPY VARCHAR2,
7 p_cust_account_id IN NUMBER,
8 x_amnt_applied OUT NOCOPY NUMBER,
9 x_amnt_outstanding OUT NOCOPY NUMBER
10 ) RETURN VARCHAR2 AS
11
12 x_return_status VARCHAR2(1) ;
13 l_api_name CONSTANT VARCHAR2(30) := 'cust_amount_info';
14 l_api_version CONSTANT NUMBER := 1;
15
16 --skgautam added NVL for bug:3527642
17 cursor cust_amnt_applied is
18 SELECT
19 NVL(SUM (ARA.amount_applied),0)
20 FROM
21 ar_receivable_applications_all ARA,
22 ar_cash_receipts_all ACR
23 WHERE
24 ARA.status IN ( 'ACC' , 'UNAPP') AND
25 ARA.cash_receipt_id = ACR.cash_receipt_id AND
26 ACR.pay_from_customer = p_cust_account_id AND
27 ACR.status IN ( 'APP', 'UNAPP')
28 GROUP BY ACR.pay_from_customer;
29
30 --skgautam added NVL for bug:3527642
31 cursor cust_amnt_outstnading is
32 SELECT
33 NVL(SUM (APS.amount_due_remaining),0)
34 FROM
35 ar_payment_schedules_all APS,
36 ra_customer_trx_all RAC
37 WHERE
38 APS.class = 'INV' AND
39 APS.status = 'OP' AND
40 APS.customer_trx_id = RAC.customer_trx_id AND
41 RAC.bill_to_customer_id = p_cust_account_id;
42
43
44 BEGIN
45
46 x_return_status := OKC_API.START_ACTIVITY(l_api_name,
47 G_PKG_NAME,
48 p_init_msg_list,
49 l_api_version,
50 p_api_version,
51 '_PROCESS',
52 x_return_status);
53 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
54 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
55 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
56 RAISE OKC_API.G_EXCEPTION_ERROR;
57 END IF;
58
59
60 open cust_amnt_applied;
61 fetch cust_amnt_applied into x_amnt_applied;
62 CLOSE cust_amnt_applied ;
63
64 open cust_amnt_outstnading;
65 fetch cust_amnt_outstnading into x_amnt_outstanding;
66 CLOSE cust_amnt_outstnading ;
67
68
69 return (x_return_status);
70 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
71 EXCEPTION
72 WHEN OKC_API.G_EXCEPTION_ERROR THEN
73 x_return_status := OKC_API.HANDLE_EXCEPTIONS
74 (
75 l_api_name,
76 G_PKG_NAME,
77 'OKC_API.G_RET_STS_ERROR',
78 x_msg_count,
79 x_msg_data,
80 '_PROCESS'
81 );
82 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
83 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
84 (
85 l_api_name,
86 G_PKG_NAME,
87 'OKC_API.G_RET_STS_UNEXP_ERROR',
88 x_msg_count,
89 x_msg_data,
90 '_PROCESS'
91 );
92 WHEN OTHERS THEN
93 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
94 ,p_msg_name => G_UNEXPECTED_ERROR
95 ,p_token1 => G_SQLCODE_TOKEN
96 ,p_token1_value => SQLCODE
97 ,p_token2 => G_SQLERRM_TOKEN
98 ,p_token2_value => SQLERRM);
99 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
100 return (x_return_status);
101 END cust_amount_info;
102
103 END OKL_CSBRW_PVT;