[Home] [Help]
PACKAGE BODY: APPS.OKL_STREAM_BILLING_PUB
Source
1 PACKAGE BODY OKL_STREAM_BILLING_PUB AS
2 /* $Header: OKLPBSTB.pls 120.9.12020000.2 2012/08/07 12:47:40 racheruv ship $ */
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 --Changed x_return_status to l_return_status by jjuneja
61
62 Okl_Stream_Billing_Pvt.bill_streams (
63 p_api_version => p_api_version,
64 p_init_msg_list => p_init_msg_list,
65 x_return_status => l_return_status,
66 x_msg_count => x_msg_count,
67 x_msg_data => x_msg_data,
68 p_commit => FND_API.G_TRUE,
69 p_contract_number => l_contract_number,
70 p_from_bill_date => l_from_bill_date,
71 p_to_bill_date => l_to_bill_date,
72 p_cust_acct_id => p_cust_acct_id,
73 p_assigned_process => p_assigned_process);
74
75
76 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
77 --Added by jjuneja
78 x_return_status:=l_return_status;
79 --End of addition by jjuneja
80 NULL;
81 --RAISE Fnd_Api.G_EXC_ERROR;
82 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
83 --Added by jjuneja
84 x_return_status:=l_return_status;
85 --End of addition by jjuneja
86 NULL;
87 --RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
88 END IF;
89
90 ------------------------------------------------------------
91 ------------------------------------------------------------
92
93
94 ------------------------------------------------------------
95 ------------------------------------------------------------
96
97
98
99 EXCEPTION
100
101 ------------------------------------------------------------
102 -- Exception handling
103 ------------------------------------------------------------
104
105 WHEN Fnd_Api.G_EXC_ERROR THEN
106
107 IF p_assigned_process IS NOT NULL THEN
108 DELETE OKL_PARALLEL_PROCESSES
109 WHERE assigned_process = p_assigned_process;
110 COMMIT;
111 END IF;
112
113 ROLLBACK TO sp_bill_streams;
114 x_return_status := Fnd_Api.G_RET_STS_ERROR;
115 Fnd_Msg_Pub.Count_and_get (
116 p_encoded => Okc_Api.G_FALSE,
117 p_count => x_msg_count,
118 p_data => x_msg_data);
119
120 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
121
122 IF p_assigned_process IS NOT NULL THEN
123 DELETE OKL_PARALLEL_PROCESSES
124 WHERE assigned_process = p_assigned_process;
125 COMMIT;
126 END IF;
127
128 ROLLBACK TO sp_bill_streams;
129 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
130 Fnd_Msg_Pub.Count_and_get (
131 p_encoded => Okc_Api.G_FALSE,
132 p_count => x_msg_count,
133 p_data => x_msg_data);
134
135 WHEN OTHERS THEN
136
137 IF p_assigned_process IS NOT NULL THEN
138 DELETE OKL_PARALLEL_PROCESSES
139 WHERE assigned_process = p_assigned_process;
140 COMMIT;
141 END IF;
142
143 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
144 Fnd_Msg_Pub.ADD_EXC_MSG (
145 p_pkg_name => G_PKG_NAME,
146 p_procedure_name => l_api_name);
147 Fnd_Msg_Pub.Count_and_get (
148 p_encoded => Okc_Api.G_FALSE,
149 p_count => x_msg_count,
150 p_data => x_msg_data);
151
152
153 END bill_streams;
154
155
156
157 PROCEDURE bill_streams_conc (
158 errbuf OUT NOCOPY VARCHAR2,
159 retcode OUT NOCOPY NUMBER,
160 p_ia_contract_type IN VARCHAR2, --modified by zrehman for Bug#6788005 on 01-Feb-2008
161 p_from_bill_date IN VARCHAR2,
162 p_to_bill_date IN VARCHAR2,
163 p_contract_number IN VARCHAR2,
164 p_cust_acct_id IN NUMBER,
165 p_inv_cust_acct_id IN NUMBER, --modified by zrehman for Bug#6788005 on 01-Feb-2008
166 p_assigned_process IN VARCHAR2
167 )
168 IS
169
170 l_api_version NUMBER := 1;
171 lx_msg_count NUMBER;
172 l_from_bill_date DATE;
173 l_to_bill_date DATE;
174 l_count1 NUMBER :=0;
175 l_count2 NUMBER :=0;
176 l_count NUMBER :=0;
177 I NUMBER :=0;
178 l_msg_index_out NUMBER :=0;
179 lx_msg_data VARCHAR2(450);
180 lx_return_status VARCHAR2(1);
181
182 l_request_id NUMBER;
183
184 CURSOR req_id_csr IS
185 SELECT
186 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
187 FROM dual;
188
189 CURSOR txd_cnt_succ_csr( p_req_id NUMBER ) IS
190 SELECT count(*)
191 FROM okl_trx_ar_invoices_v a,
192 okl_txl_ar_inv_lns_v b,
193 okl_txd_ar_ln_dtls_v c
194 WHERE a.id = b.tai_id AND
195 b.id = c.til_id_details AND
196 a.trx_status_code = 'PROCESSED' AND
197 a.request_id = p_req_id ;
198
199 CURSOR txd_cnt_err_csr( p_req_id NUMBER ) IS
200 SELECT count(*)
201 FROM okl_trx_ar_invoices_v a,
202 okl_txl_ar_inv_lns_v b,
203 okl_txd_ar_ln_dtls_v c
204 WHERE a.id = b.tai_id AND
205 b.id = c.til_id_details AND
206 a.trx_status_code = 'ERROR' AND
207 a.request_id = p_req_id ;
208
209 ------------------------------------------------------------
210 -- Operating Unit
211 ------------------------------------------------------------
212 CURSOR op_unit_csr IS
213 SELECT NAME
214 FROM hr_operating_units
215 WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID;--MOAC- Concurrent request
216
217
218 l_succ_cnt NUMBER;
219 l_err_cnt NUMBER;
220 l_op_unit_name hr_operating_units.name%TYPE;
221
222 BEGIN
223
224 l_succ_cnt := 0;
225 l_err_cnt := 0;
226
227 -- Get the request Id
228 l_request_id := NULL;
229 OPEN req_id_csr;
230 FETCH req_id_csr INTO l_request_id;
231 CLOSE req_id_csr;
232
233 ----------------------------------------
234 -- Get Operating unit name
235 ----------------------------------------
236 l_op_unit_name := NULL;
237 OPEN op_unit_csr;
238 FETCH op_unit_csr INTO l_op_unit_name;
239 CLOSE op_unit_csr;
240
241 IF p_from_bill_date IS NOT NULL THEN
242 l_from_bill_date := FND_DATE.CANONICAL_TO_DATE(p_from_bill_date);
243 END IF;
244
245 IF p_to_bill_date IS NOT NULL THEN
246 l_to_bill_date := FND_DATE.CANONICAL_TO_DATE(p_to_bill_date);
247 END IF;
248
249 FND_FILE.PUT_LINE (FND_FILE.LOG, '************************************');
250 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Process Billable Streams Program');
251 FND_FILE.PUT_LINE (FND_FILE.LOG, '************************************');
252 FND_FILE.PUT_LINE (FND_FILE.LOG, 'From Bill Date = ' ||p_from_bill_date);
253 FND_FILE.PUT_LINE (FND_FILE.LOG, 'To Bill Date = ' ||p_to_bill_date);
254 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Contract Number = ' ||p_contract_number);
255 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Customer Account Id = ' ||p_cust_acct_id);
256 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Assigned Process = ' ||p_assigned_process);
257
258 okl_stream_billing_pub.bill_streams (
259 p_api_version => l_api_version,
260 p_init_msg_list => Okl_Api.G_FALSE,
261 x_return_status => lx_return_status,
262 x_msg_count => lx_msg_count,
263 x_msg_data => errbuf,
264 p_ia_contract_type => p_ia_contract_type, --modified by zrehman for Bug#6788005 on 01-Feb-2008
265 p_contract_number => p_contract_number,
266 p_from_bill_date => l_from_bill_date,
267 p_to_bill_date => l_to_bill_date,
268 p_cust_acct_id => p_cust_acct_id,
269 p_inv_cust_acct_id => p_inv_cust_acct_id, --modified by zrehman for Bug#6788005 on 01-Feb-2008
270 p_assigned_process => p_assigned_process);
271
272 if lx_return_status= 'W' then
273 retcode := 1;
274 end if;
275 --Added by jjuneja
276 if lx_return_status= 'E' then
277 retcode:=2;
278 end if;
279 --End of addition by jjuneja
280 EXCEPTION
281 WHEN OTHERS THEN
282 IF p_assigned_process IS NOT NULL THEN
283 DELETE OKL_PARALLEL_PROCESSES
284 WHERE assigned_process = p_assigned_process;
285 COMMIT;
286 END IF;
287
288 END bill_streams_conc;
289
290
291 END Okl_Stream_Billing_Pub;