[Home] [Help]
PACKAGE BODY: APPS.OKL_INVESTOR_INVOICE_DISB_PUB
Source
1 PACKAGE BODY OKL_INVESTOR_INVOICE_DISB_PUB AS
2 /* $Header: OKLPIDBB.pls 120.5 2007/06/28 22:36:52 ssiruvol ship $ */
3
4 PROCEDURE OKL_INVESTOR_DISBURSEMENT_IN
5 (p_api_version IN NUMBER
6 ,p_init_msg_list IN VARCHAR2
7 ,x_return_status OUT NOCOPY VARCHAR2
8 ,x_msg_count OUT NOCOPY NUMBER
9 ,x_msg_data OUT NOCOPY VARCHAR2
10 ,p_investor_agreement IN VARCHAR2
11 ,px_to_date IN DATE)
12 IS
13 l_api_version NUMBER ;
14 l_init_msg_list VARCHAR2(1) ;
15 l_return_status VARCHAR2(1);
16 l_msg_count NUMBER ;
17 l_msg_data VARCHAR2(2000);
18
19
20 BEGIN
21
22 SAVEPOINT OKL_INVESTOR_DISBURSEMENT;
23
24 okl_investor_invoice_disb_pvt.OKL_INVESTOR_DISBURSEMENT(
25 p_api_version => p_api_version
26 ,p_init_msg_list => p_init_msg_list
27 ,x_return_status => x_return_status
28 ,x_msg_count => x_msg_count
29 ,x_msg_data => x_msg_data
30 ,p_investor_agreement => p_investor_agreement
31 ,p_to_date => px_to_date);
32
33 IF ( l_return_status = Fnd_Api.G_RET_STS_ERROR ) THEN
34 RAISE Fnd_Api.G_EXC_ERROR;
35 ELSIF (l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR ) THEN
36 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
37 END IF;
38
39 EXCEPTION
40
41 WHEN Fnd_Api.G_EXC_ERROR THEN
42 ROLLBACK TO OKL_INVESTOR_DISBURSEMENT;
43 x_return_status := Fnd_Api.G_RET_STS_ERROR;
44 x_msg_count := l_msg_count ;
45 x_msg_data := l_msg_data ;
46 Fnd_Msg_Pub.count_and_get(
47 p_count => x_msg_count
48 ,p_data => x_msg_data);
49 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
50 ROLLBACK TO OKL_INVESTOR_DISBURSEMENT;
51 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
52 x_msg_count := l_msg_count ;
53 x_msg_data := l_msg_data ;
54 Fnd_Msg_Pub.count_and_get(
55 p_count => x_msg_count
56 ,p_data => x_msg_data);
57 WHEN OTHERS THEN
58 ROLLBACK TO OKL_INVESTOR_DISBURSEMENT;
59 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
60 x_msg_count := l_msg_count ;
61 x_msg_data := l_msg_data ;
62 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_INVESTOR_INVOICE_DISB_PUB',
63 'OKL_INVESTOR_DISBURSEMENT');
64 Fnd_Msg_Pub.count_and_get(
65 p_count => x_msg_count
66 ,p_data => x_msg_data);
67 END OKL_INVESTOR_DISBURSEMENT_IN;
68
69
70 PROCEDURE OKL_INVESTOR_DISBURSEMENT
71 (errbuf OUT NOCOPY VARCHAR2
72 ,retcode OUT NOCOPY NUMBER
73 ,p_investor_agreement IN VARCHAR2
74 ,p_to_date IN VARCHAR2)
75 IS
76
77 -- Local Variables
78 l_api_version NUMBER := 1;
79 lx_msg_count NUMBER;
80 lx_msg_data VARCHAR2(450);
81 l_msg_index_out NUMBER;
82 lx_return_status VARCHAR(1);
83
84
85 -- Input parameters to the conc program
86 l_from_date DATE;
87 l_to_date DATE;
88
89
90 -- Log Meesage reporting
91 l_request_id NUMBER;
92
93 CURSOR req_id_csr IS
94 SELECT
95 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
96 FROM dual;
97
98 CURSOR tap_cnt_succ_csr( p_req_id NUMBER ) IS
99 SELECT count(*)
100 FROM okl_trx_ap_invoices_v a,
101 okl_txl_ap_inv_lns_v b
102 WHERE a.id = b.tap_id AND
103 a.trx_status_code = 'ENTERED' AND
104 a.request_id = p_req_id ;
105
106 CURSOR tap_cnt_err_csr( p_req_id NUMBER ) IS
107 SELECT count(*)
108 FROM okl_trx_ap_invoices_v a,
109 okl_txl_ap_inv_lns_v b
110 WHERE a.id = b.tap_id AND
111 a.trx_status_code = 'ERROR' AND
112 a.request_id = p_req_id ;
113
114 l_succ_cnt NUMBER;
115 l_err_cnt NUMBER;
116
117 BEGIN
118
119 l_succ_cnt := 0;
120 l_err_cnt := 0;
121
122 -- Get the request Id
123 l_request_id := NULL;
124 OPEN req_id_csr;
125 FETCH req_id_csr INTO l_request_id;
126 CLOSE req_id_csr;
127
128 -- Format Input parameters
129 -- IF p_from_date IS NOT NULL THEN
130 -- l_from_date := FND_DATE.CANONICAL_TO_DATE(p_from_date);
131 -- END IF;
132
133 IF p_to_date IS NOT NULL THEN
134 --commented out by pgomes on 03/25/2003
135 --removed the comments stmathew 04/07/2004
136 --convert to fnd_canonical date
137 l_to_date := FND_DATE.CANONICAL_TO_DATE(p_to_date);
138 END IF;
139
140 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Create Investor Invoice Disbursements');
141 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '***********************************************');
142 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Program Run Date: '||sysdate||' Request Id: '||l_request_id);
143 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Investor Agreement: '||p_investor_agreement);
144 -- FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'From Invoice Date: '||l_from_date);
145 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'To Invoice Date: '||l_to_date);
146 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '***********************************************');
147
148 OKL_INVESTOR_DISBURSEMENT_IN
149 (p_api_version => l_api_version
150 ,p_init_msg_list => OKC_API.G_FALSE
151 ,x_return_status => lx_return_status
152 ,x_msg_count => lx_msg_count
153 ,x_msg_data => errbuf
154 ,p_investor_agreement => p_investor_agreement
155 ,px_to_date => l_to_date);
156
157
158 -- Success Count
159 OPEN tap_cnt_succ_csr( l_request_id );
160 FETCH tap_cnt_succ_csr INTO l_succ_cnt;
161 CLOSE tap_cnt_succ_csr;
162
163 -- Error Count
164 OPEN tap_cnt_err_csr( l_request_id );
165 FETCH tap_cnt_err_csr INTO l_err_cnt;
166 CLOSE tap_cnt_err_csr;
167
168
169 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Number of Successful records in OKL_TRX_AP_INVOICES_B :'||l_succ_cnt);
170 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Number of Errored records in OKL_TRX_AP_INVOICES_B :'||l_err_cnt);
171
172
173 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Success or Error Detailed Messages (If Any):');
174
175 IF lx_msg_count >= 1 THEN
176 FOR i in 1..lx_msg_count LOOP
177 fnd_msg_pub.get (
178 p_msg_index => i,
179 p_encoded => 'F',
180 p_data => lx_msg_data,
181 p_msg_index_out => l_msg_index_out);
182
183 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,to_char(i) || ': ' || lx_msg_data);
184 END LOOP;
185 END IF;
186 EXCEPTION
187 WHEN OTHERS THEN
188 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
189
190 END OKL_INVESTOR_DISBURSEMENT;
191
192 END OKL_INVESTOR_INVOICE_DISB_PUB;