[Home] [Help]
PACKAGE BODY: APPS.OKL_STREAM_BILLING_PUB
Source
1 PACKAGE BODY OKL_STREAM_BILLING_PUB AS
2 /* $Header: OKLPBSTB.pls 120.9 2008/02/07 13:16:15 zrehman noship $ */
3
4 ------------------------------------------------------------------
5 -- Procedure BIL_STREAMS to bill outstanding stream elements
6 ------------------------------------------------------------------
7
8 PROCEDURE bill_streams
9 (p_api_version IN NUMBER
10 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
11 ,x_return_status OUT NOCOPY VARCHAR2
12 ,x_msg_count OUT NOCOPY NUMBER
13 ,x_msg_data OUT NOCOPY VARCHAR2
14 ,p_ia_contract_type IN VARCHAR2 DEFAULT NULL --modified by zrehman for Bug#6788005 on 01-Feb-2008
15 ,p_contract_number IN VARCHAR2 DEFAULT NULL
16 ,p_from_bill_date IN DATE DEFAULT NULL
17 ,p_to_bill_date IN DATE DEFAULT NULL
18 ,p_cust_acct_id IN NUMBER DEFAULT NULL
19 ,p_inv_cust_acct_id IN NUMBER DEFAULT NULL --modified by zrehman for Bug#6788005 on 01-Feb-2008
20 ,p_assigned_process IN VARCHAR2 DEFAULT NULL)
21 IS
22
23 ------------------------------------------------------------
24 -- Declare variables required by APIs
25 ------------------------------------------------------------
26
27 l_api_name CONSTANT VARCHAR2(30) := 'BILL_STREAMS';
28 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
29 l_data VARCHAR2(100);
30 l_count NUMBER;
31 l_contract_number okc_k_headers_b.contract_number%TYPE;
32 l_from_bill_date DATE;
33 l_to_bill_date DATE;
34
35 BEGIN
36
37 ------------------------------------------------------------
38 -- Start processing
39 ------------------------------------------------------------
40
41 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
42
43 SAVEPOINT sp_bill_streams;
44
45 l_contract_number := p_contract_number;
46 l_from_bill_date := p_from_bill_date;
47 l_to_bill_date := p_to_bill_date;
48
49 ------------------------------------------------------------
50 ------------------------------------------------------------
51
52
53 ------------------------------------------------------------
54 ------------------------------------------------------------
55
56
57 ------------------------------------------------------------
58 -- Call process API to bill streams
59 ------------------------------------------------------------
60
61 Okl_Stream_Billing_Pvt.bill_streams (
62 p_api_version => p_api_version,
63 p_init_msg_list => p_init_msg_list,
64 x_return_status => x_return_status,
65 x_msg_count => x_msg_count,
66 x_msg_data => x_msg_data,
67 p_commit => FND_API.G_TRUE,
68 p_contract_number => l_contract_number,
69 p_from_bill_date => l_from_bill_date,
70 p_to_bill_date => l_to_bill_date,
71 p_cust_acct_id => p_cust_acct_id,
72 p_assigned_process => p_assigned_process);
73
74
75 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
76 NULL;
77 --RAISE Fnd_Api.G_EXC_ERROR;
78 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
79 NULL;
80 --RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
81 END IF;
82
83 ------------------------------------------------------------
84 ------------------------------------------------------------
85
86
87 ------------------------------------------------------------
88 ------------------------------------------------------------
89
90
91
92 EXCEPTION
93
94 ------------------------------------------------------------
95 -- Exception handling
96 ------------------------------------------------------------
97
98 WHEN Fnd_Api.G_EXC_ERROR THEN
99
100 IF p_assigned_process IS NOT NULL THEN
101 DELETE OKL_PARALLEL_PROCESSES
102 WHERE assigned_process = p_assigned_process;
103 COMMIT;
104 END IF;
105
106 ROLLBACK TO sp_bill_streams;
107 x_return_status := Fnd_Api.G_RET_STS_ERROR;
108 Fnd_Msg_Pub.Count_and_get (
109 p_encoded => Okc_Api.G_FALSE,
110 p_count => x_msg_count,
111 p_data => x_msg_data);
112
113 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
114
115 IF p_assigned_process IS NOT NULL THEN
116 DELETE OKL_PARALLEL_PROCESSES
117 WHERE assigned_process = p_assigned_process;
118 COMMIT;
119 END IF;
120
121 ROLLBACK TO sp_bill_streams;
122 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
123 Fnd_Msg_Pub.Count_and_get (
124 p_encoded => Okc_Api.G_FALSE,
125 p_count => x_msg_count,
126 p_data => x_msg_data);
127
128 WHEN OTHERS THEN
129
130 IF p_assigned_process IS NOT NULL THEN
131 DELETE OKL_PARALLEL_PROCESSES
132 WHERE assigned_process = p_assigned_process;
133 COMMIT;
134 END IF;
135
136 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
137 Fnd_Msg_Pub.ADD_EXC_MSG (
138 p_pkg_name => G_PKG_NAME,
139 p_procedure_name => l_api_name);
140 Fnd_Msg_Pub.Count_and_get (
141 p_encoded => Okc_Api.G_FALSE,
142 p_count => x_msg_count,
143 p_data => x_msg_data);
144
145
146 END bill_streams;
147
148
149
150 PROCEDURE bill_streams_conc (
151 errbuf OUT NOCOPY VARCHAR2,
152 retcode OUT NOCOPY NUMBER,
153 p_ia_contract_type IN VARCHAR2, --modified by zrehman for Bug#6788005 on 01-Feb-2008
154 p_from_bill_date IN VARCHAR2,
155 p_to_bill_date IN VARCHAR2,
156 p_contract_number IN VARCHAR2,
157 p_cust_acct_id IN NUMBER,
158 p_inv_cust_acct_id IN NUMBER, --modified by zrehman for Bug#6788005 on 01-Feb-2008
159 p_assigned_process IN VARCHAR2
160 )
161 IS
162
163 l_api_version NUMBER := 1;
164 lx_msg_count NUMBER;
165 l_from_bill_date DATE;
166 l_to_bill_date DATE;
167 l_count1 NUMBER :=0;
168 l_count2 NUMBER :=0;
169 l_count NUMBER :=0;
170 I NUMBER :=0;
171 l_msg_index_out NUMBER :=0;
172 lx_msg_data VARCHAR2(450);
173 lx_return_status VARCHAR2(1);
174
175 l_request_id NUMBER;
176
177 CURSOR req_id_csr IS
178 SELECT
179 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
180 FROM dual;
181
182 CURSOR txd_cnt_succ_csr( p_req_id NUMBER ) IS
183 SELECT count(*)
184 FROM okl_trx_ar_invoices_v a,
185 okl_txl_ar_inv_lns_v b,
186 okl_txd_ar_ln_dtls_v c
187 WHERE a.id = b.tai_id AND
188 b.id = c.til_id_details AND
189 a.trx_status_code = 'PROCESSED' AND
190 a.request_id = p_req_id ;
191
192 CURSOR txd_cnt_err_csr( p_req_id NUMBER ) IS
193 SELECT count(*)
194 FROM okl_trx_ar_invoices_v a,
195 okl_txl_ar_inv_lns_v b,
196 okl_txd_ar_ln_dtls_v c
197 WHERE a.id = b.tai_id AND
198 b.id = c.til_id_details AND
199 a.trx_status_code = 'ERROR' AND
200 a.request_id = p_req_id ;
201
202 ------------------------------------------------------------
203 -- Operating Unit
204 ------------------------------------------------------------
205 CURSOR op_unit_csr IS
206 SELECT NAME
207 FROM hr_operating_units
208 WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID;--MOAC- Concurrent request
209
210
211 l_succ_cnt NUMBER;
212 l_err_cnt NUMBER;
213 l_op_unit_name hr_operating_units.name%TYPE;
214
215 BEGIN
216
217 l_succ_cnt := 0;
218 l_err_cnt := 0;
219
220 -- Get the request Id
221 l_request_id := NULL;
222 OPEN req_id_csr;
223 FETCH req_id_csr INTO l_request_id;
224 CLOSE req_id_csr;
225
226 ----------------------------------------
227 -- Get Operating unit name
228 ----------------------------------------
229 l_op_unit_name := NULL;
230 OPEN op_unit_csr;
231 FETCH op_unit_csr INTO l_op_unit_name;
232 CLOSE op_unit_csr;
233
234 IF p_from_bill_date IS NOT NULL THEN
235 l_from_bill_date := FND_DATE.CANONICAL_TO_DATE(p_from_bill_date);
236 END IF;
237
238 IF p_to_bill_date IS NOT NULL THEN
239 l_to_bill_date := FND_DATE.CANONICAL_TO_DATE(p_to_bill_date);
240 END IF;
241
242 FND_FILE.PUT_LINE (FND_FILE.LOG, '************************************');
243 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Process Billable Streams Program');
244 FND_FILE.PUT_LINE (FND_FILE.LOG, '************************************');
245 FND_FILE.PUT_LINE (FND_FILE.LOG, 'From Bill Date = ' ||p_from_bill_date);
246 FND_FILE.PUT_LINE (FND_FILE.LOG, 'To Bill Date = ' ||p_to_bill_date);
247 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Contract Number = ' ||p_contract_number);
248 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Customer Account Id = ' ||p_cust_acct_id);
249 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Assigned Process = ' ||p_assigned_process);
250
251 okl_stream_billing_pub.bill_streams (
252 p_api_version => l_api_version,
253 p_init_msg_list => Okl_Api.G_FALSE,
254 x_return_status => lx_return_status,
255 x_msg_count => lx_msg_count,
256 x_msg_data => errbuf,
257 p_ia_contract_type => p_ia_contract_type, --modified by zrehman for Bug#6788005 on 01-Feb-2008
258 p_contract_number => p_contract_number,
259 p_from_bill_date => l_from_bill_date,
260 p_to_bill_date => l_to_bill_date,
261 p_cust_acct_id => p_cust_acct_id,
262 p_inv_cust_acct_id => p_inv_cust_acct_id, --modified by zrehman for Bug#6788005 on 01-Feb-2008
263 p_assigned_process => p_assigned_process);
264
265 if lx_return_status= 'W' then
266 retcode := 1;
267 end if;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 IF p_assigned_process IS NOT NULL THEN
272 DELETE OKL_PARALLEL_PROCESSES
273 WHERE assigned_process = p_assigned_process;
274 COMMIT;
275 END IF;
276
277 END bill_streams_conc;
278
279
280 END Okl_Stream_Billing_Pub;