[Home] [Help]
PACKAGE BODY: APPS.OKL_BPD_CREDIT_CHECK_PVT
Source
1 PACKAGE BODY OKL_BPD_CREDIT_CHECK_PVT AS
2 /* $Header: OKLRCCFB.pls 115.5 2003/09/23 21:25:24 cklee noship $ */
3
4 FUNCTION credit_check(p_api_version IN NUMBER
5 ,p_init_msg_list IN VARCHAR2
6 ,x_return_status OUT NOCOPY VARCHAR2
7 ,x_msg_count OUT NOCOPY NUMBER
8 ,x_msg_data OUT NOCOPY VARCHAR2
9 ,p_creditline_id IN NUMBER
10 ,p_credit_max IN NUMBER
11 ,P_trx_date IN DATE)
12 RETURN NUMBER IS
13
14 l_credit_remain NUMBER := 0;
15 l_disbursement_tot NUMBER := 0;
16 l_is_revolving_credit BOOLEAN := false;
17 l_dummy NUMBER;
18 l_principal_tot NUMBER := 0;
19
20 cursor c_disb_tot is
21 -- SELECT NVL(SUM(NVL(TAP.AMOUNT,0)),0)
22 SELECT TAP.AMOUNT,
23 TAP.KHR_ID
24 FROM OKL_TRX_AP_INVOICES_B TAP
25 WHERE TAP.TRX_STATUS_CODE in ('APPROVED','PROCESSED') -- push to AP
26 AND TAP.FUNDING_TYPE_CODE IS NOT NULL
27 --AND TRUNC(DATE_INVOICED) <= TRUNC(p_trx_date)
28 ;
29 --AND OKL_CREDIT_PUB.get_creditline_by_chrid(TAP.KHR_ID) = p_creditline_id;
30
31 /* comment out for now
32 cursor c_is_revolv_crd(p_creditline_id number) is
33 select 1 -- Revloving line of credit line
34 from okl_k_headers REV
35 where rev.id = p_creditline_id
36 and REV.REVOLVING_CREDIT_YN = 'Y'
37 ;
38
39 cursor c_princ_tot(p_creditline_id number) is
40 SELECT
41 -- NVL(SUM(NVL(PS.AMOUNT_APPLIED,0)),0)
42 NVL(PS.AMOUNT_APPLIED,0),
43 CN.ID KHR_ID
44 FROM
45 AR_PAYMENT_SCHEDULES_ALL PS,
46 OKL_CNSLD_AR_STRMS_B ST,
47 OKL_STRM_TYPE_TL SM,
48 OKC_K_HEADERS_B CN
49 WHERE
50 PS.CLASS IN ('INV') AND
51 ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID AND
52 SM.ID = ST.STY_ID AND
53 SM.LANGUAGE = USERENV ('LANG') AND
54 CN.ID = ST.KHR_ID AND
55 SM.NAME = 'PRINCIPAL PAYMENT' AND
56 TRUNC(NVL(PS.TRX_DATE, SYSDATE)) <= TRUNC(p_trx_date)
57 AND
58 ;
59 */
60 --AND OKL_CREDIT_PUB.get_creditline_by_chrid(CN.ID) = p_creditline_id;
61
62 begin
63
64 FOR r_ast IN c_disb_tot LOOP
65
66 IF (OKL_CREDIT_PUB.get_creditline_by_chrid(r_ast.KHR_ID) = p_creditline_id) THEN
67 l_disbursement_tot := l_disbursement_tot + NVL(r_ast.AMOUNT,0);
68 END IF;
69
70 END LOOP;
71
72 /*
73 OPEN c_disb_tot(p_creditline_id);
74 FETCH c_disb_tot into l_disbursement_tot;
75 CLOSE c_disb_tot;
76
77 OPEN c_is_revolv_crd(p_creditline_id);
78 FETCH c_is_revolv_crd into l_dummy;
79 l_is_revolving_credit := c_is_revolv_crd%FOUND;
80 CLOSE c_is_revolv_crd;
81
82 IF (l_is_revolving_credit) THEN
83
84 OPEN c_princ_tot(p_creditline_id);
85 FETCH c_princ_tot into l_principal_tot;
86 CLOSE c_princ_tot;
87
88 l_credit_remain := nvl(p_credit_max, 0) - l_disbursement_tot + l_principal_tot;
89 ELSE
90 l_credit_remain := nvl(p_credit_max, 0) - l_disbursement_tot;
91 END IF;
92 */
93 l_credit_remain := nvl(p_credit_max, 0) - l_disbursement_tot;
94 x_return_status := okl_api.G_RET_STS_SUCCESS;
95
96 RETURN l_credit_remain;
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 --l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
101 OKL_API.Set_Message(p_app_name => OKL_API.G_APP_NAME,
102 p_msg_name => 'OKL_UNEXPECTED_ERROR',
103 p_token1 => 'OKL_SQLCODE',
104 p_token1_value => SQLCODE,
105 p_token2 => 'OKL_SQLERRM',
106 p_token2_value => SQLERRM);
107 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
108 RETURN NULL;
109
110
111 END credit_check;
112
113 END OKL_BPD_CREDIT_CHECK_PVT;