PACKAGE BODY: APPS.OKL_COMMON_FUNCTIONS

Source

1 PACKAGE BODY OKL_COMMON_FUNCTIONS AS
2 /* $Header: OKLRCOMB.pls 120.2 2006/07/11 09:43:24 dkagrawa noship $ */
3 
4   ---------------------------------------------------------------------------
5   -- FUNCTION get_unrefunded_cures
6   ---------------------------------------------------------------------------
7   FUNCTION get_unrefunded_cures(
8      p_contract_id		IN NUMBER,
9      x_unrefunded_cures	      OUT NOCOPY NUMBER)
10   RETURN VARCHAR2
11   IS
12 
13     -- Get unrefunded cures for a contract
14     /*CURSOR unrefunded_cures_csr(p_contract_id NUMBER) IS
15       SELECT SUM(amount)
16       FROM   OKL_cure_payment_lines
17       WHERE  chr_id = p_contract_id
18       AND    status = 'CURES_IN_POSSESSION'; */
19 
20     l_unrefunded_cures NUMBER := 0;
21     l_api_version      NUMBER;
22     l_return_status    VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
23     l_msg_count        NUMBER;
24     l_msg_data         VARCHAR2(2000);
25 
26   BEGIN
27 
28     --OPEN  unrefunded_cures_csr(p_contract_id);
29     --FETCH unrefunded_cures_csr INTO l_unrefunded_cures;
30     --CLOSE unrefunded_cures_csr;
31 
32     x_unrefunded_cures := l_unrefunded_cures;
33 
34     RETURN l_return_status;
35     EXCEPTION
36     WHEN OTHERS THEN
37       OKC_API.SET_MESSAGE( p_app_name     => G_APP_NAME
38                           ,p_msg_name     => G_UNEXPECTED_ERROR
39                           ,p_token1       => G_SQLCODE_TOKEN
40                           ,p_token1_value => SQLCODE
41                           ,p_token2       => G_SQLERRM_TOKEN
42                           ,p_token2_value => SQLERRM);
43       l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
44       RETURN(l_return_status);
45 
46   END get_unrefunded_cures;
47 
48   ---------------------------------------------------------------------------
49   -- FUNCTION get_unrefunded_cures
50   ---------------------------------------------------------------------------
51   FUNCTION get_cured_status (p_contract_number IN NUMBER)
52     RETURN VARCHAR2 IS
53   CURSOR c_cured (p_chr_id NUMBER)  is
54     SELECT 'Y'
55     FROM    OKL_CURE_PAYMENT_LINES
56     WHERE EXISTS (SELECT 1
57                   FROM   OKL_CURE_PAYMENT_LINES
58                   WHERE  status = 'CURES_IN_POSSESSION'
59                   AND    cured_flag = 'Y'
60                   AND    chr_id = p_chr_id);
61     ls_cured_flag  VARCHAR2(1) := 'N';
62   BEGIN
63     OPEN c_cured(p_contract_number );
64     FETCH c_cured INTO ls_cured_flag;
65     IF(c_cured%NOTFOUND) THEN
66        ls_cured_flag := 'N' ;
67          CLOSE c_cured ;
68          return(ls_cured_flag);
69     END if ;
70     CLOSE c_cured;
71       return(ls_cured_flag);
72   END get_cured_status;
73 
74 END OKL_COMMON_FUNCTIONS;