[Home] [Help]
PACKAGE BODY: APPS.OKL_PRINT_CONS_BILL
Source
1 PACKAGE BODY Okl_Print_Cons_Bill AS
2 /* $Header: OKLRCBPB.pls 120.2 2008/02/04 13:17:14 nikshah ship $ */
3
4 PROCEDURE print_cons_bill(
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 ) IS
10
11 CURSOR cons_hdr IS
12 SELECT a.*
13 FROM okl_cnsld_ar_hdrs_v a
14 WHERE EXISTS (SELECT 1 FROM
15 (SELECT x.id
16 FROM okl_cnsld_ar_hdrs_v x,
17 okl_cnsld_ar_lines_v y,
18 okl_cnsld_ar_strms_v z
19 WHERE x.trx_status_code like 'PROCESSED%' AND
20 x.id = y.cnr_id AND
21 y.id = z.lln_id AND
22 z.receivables_invoice_id IS NOT NULL) b
23 WHERE b.id = a.id
24 -- nikshah bug 6747706 added org check
25 AND NVL(a.ORG_ID, MO_GLOBAL.GET_CURRENT_ORG_ID) = MO_GLOBAL.GET_CURRENT_ORG_ID)
26 -- nikshah bug 6747706 end
27 ORDER BY to_number(a.consolidated_invoice_number);
28
29 CURSOR cons_line(p_cnr_id NUMBER) IS
30 SELECT sequence_number,
31 nvl(amount, 0) + nvl(tax_amount, 0) amount,
32 ilt_id,
33 khr_id,
34 kle_id
35 FROM okl_cnsld_ar_lines_v
36 WHERE cnr_id = p_cnr_id;
37
38 CURSOR invoice_line_type(p_ilt_id NUMBER) IS
39 SELECT name
40 FROM okl_invc_line_types_v
41 WHERE id = p_ilt_id;
42
43 CURSOR contract_number (p_khr_id NUMBER) IS
44 SELECT contract_number
45 FROM okc_k_headers_b
46 WHERE id = p_khr_id;
47
48 CURSOR asset_name(p_kle_id NUMBER) IS
49 SELECT name, description
50 FROM okx_asset_lines_v
51 WHERE parent_line_id = p_kle_id;
52
53 CURSOR party_name_csr ( p_party_id NUMBER ) is
54 SELECT party_name,
55 party_id
56 FROM hz_parties
57 where party_id = p_party_id;
58
59
60 CURSOR curr_csr ( p_code VARCHAR2 ) IS
61 Select name
62 from fnd_currencies_vl
63 WHERE currency_code = p_code;
64
65 l_curr_descr fnd_currencies_vl.name%type;
66
67 l_line_name VARCHAR2(150);
68
69 l_khr_id NUMBER;
70 l_kle_id NUMBER;
71 l_asset_name VARCHAR2(150);
72 l_asset_descr okx_asset_lines_v.description%TYPE;
73
74 l_contract_number okl_k_headers_full_v.contract_number%TYPE;
75
76 l_flag VARCHAR2(1);
77
78 l_formatted_amount VARCHAR2(38);
79
80 cntr NUMBER;
81
82 CURSOR bill_addr_csr ( p_id NUMBER ) IS
83 SELECT
84 CA.account_number,
85 PY.PARTY_NAME,
86 PY.ADDRESS1,
87 PY.ADDRESS2,
88 PY.ADDRESS3,
89 PY.ADDRESS4,
90 PY.CITY,
91 PY.STATE,
92 PY.POSTAL_CODE,
93 PY.COUNTRY
94 FROM HZ_CUST_ACCOUNTS CA,
95 HZ_PARTIES PY
96 WHERE CA.CUST_ACCOUNT_ID = p_id AND
97 PY.PARTY_ID = CA.PARTY_ID;
98
99
100 l_customer_number HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
101 l_customer_name hz_parties.PARTY_NAME%TYPE;
102 l_addr1 HZ_PARTIES.ADDRESS1%TYPE;
103 l_addr2 HZ_PARTIES.ADDRESS2%TYPE;
104 l_addr3 HZ_PARTIES.ADDRESS3%TYPE;
105 l_addr4 HZ_PARTIES.ADDRESS4%TYPE;
106 l_city HZ_PARTIES.CITY%TYPE;
107 l_state HZ_PARTIES.STATE%TYPE;
108 l_postal_code HZ_PARTIES.POSTAL_CODE%TYPE;
109 l_country HZ_PARTIES.COUNTRY%TYPE;
110
111 l_format_type VARCHAR2(5) := '0';
112 l_format_set BOOLEAN := FALSE;
113
114 BEGIN
115
116 -- Null out local variables
117 l_khr_id := NULL;
118 l_kle_id := NULL;
119
120 cntr := 0;
121 --Process all headers
122 FOR hdr IN cons_hdr LOOP
123 l_format_type := '0';
124 l_format_set := FALSE;
125
126 cntr := cntr + 1;
127 l_flag := 'Y';
128
129
130 -- Clear Variables
131 l_customer_name := NULL;
132 l_customer_number := NULL;
133 /*
134 OPEN party_name_csr ( hdr.ixx_id );
135 FETCH party_name_csr INTO l_customer_name, l_customer_number;
136 CLOSE party_name_csr;
137 */
138
139 l_addr1 := NULL;
140 l_addr2 := NULL;
141 l_addr3 := NULL;
142 l_addr4 := NULL;
143 l_city := NULL;
144 l_state := NULL;
145 l_postal_code := NULL;
146 l_country := NULL;
147
148 -- Clear Variables
149 l_curr_descr := NULL;
150 OPEN curr_csr ( hdr.currency_code );
151 FETCH curr_csr INTO l_curr_descr;
152 CLOSE curr_csr;
153
154 OPEN bill_addr_csr ( hdr.ixx_id );
155 FETCH bill_addr_csr INTO l_customer_number,
156 l_customer_name,
157 l_addr1,
158 l_addr2,
159 l_addr3,
160 l_addr4,
161 l_city,
162 l_state,
163 l_postal_code,
164 l_country;
165 CLOSE bill_addr_csr;
166
167
168 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rpad(' ', 54, ' ') || ('** Page : '||cntr||' **') || lpad(' ', 54, ' '));
169 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('-', 121, '-'));
170 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('Invoice Number', 20, ' ') || ': ' || rpad (hdr.consolidated_invoice_number,30,' ') || rPAD('Currency ', 20, ' ') || ': ' || rPAD(l_curr_descr, 47, ' '));
171 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('Customer Account', 20, ' ') || ': ' || rpad(l_customer_number,30,' ') || rPAD('Customer Name ', 20, ' ') || ': ' || rPAD(l_customer_name, 47, ' '));
172 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('Invoice Date', 20, ' ') || ': '|| rPAD( hdr.date_consolidated , 99, ' ') );
173
174 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD('Billing Address', 20, ' ')||': '||rPAD( l_addr1 , 99, ' '));
175
176 IF ( l_addr2 IS NOT NULL ) THEN
177 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 22, ' ') || rPAD( l_addr2 , 99, ' ') );
178 END IF;
179 IF ( l_addr3 IS NOT NULL ) THEN
180 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 22, ' ') || rPAD( l_addr3 , 99, ' ') );
181 END IF;
182 IF ( l_addr4 IS NOT NULL ) THEN
183 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 22, ' ')|| rPAD( l_addr4 , 99, ' ') );
184 END IF;
185 IF ( l_city IS NOT NULL ) THEN
186 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 22, ' ') || rPAD( l_city , 99, ' ') );
187 END IF;
188 IF ( l_state IS NOT NULL ) THEN
189 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 22, ' ') || rPAD( l_state , 99, ' ') );
190 END IF;
191 IF ( l_postal_code IS NOT NULL ) THEN
192 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 22, ' ') || rPAD( l_postal_code , 99, ' ') );
193 END IF;
194 IF ( l_country IS NOT NULL ) THEN
195 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 22, ' ') || rPAD( l_country , 99, ' ') );
196 END IF;
197
198 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rPAD(' ', 121, ' '));
199 --Process all lines
200 l_khr_id := NULL;
201 l_kle_id := NULL;
202 FOR lines IN cons_line(hdr.id) LOOP
203 l_formatted_amount := okl_accounting_util.format_amount(lines.amount, hdr.currency_code);
204
205 IF l_khr_id IS NULL AND l_kle_id IS NULL THEN
206 -- This could be the first time for this cons bill
207 --Set those to cursor variables
208 l_khr_id := lines.khr_id;
209 l_kle_id := lines.kle_id;
210 END IF;
211 --This cursor fetches the consolidated bill line
212 --name into a local variable
213 l_line_name := NULL;
214 OPEN invoice_line_type(lines.ilt_id);
215 FETCH invoice_line_type INTO l_line_name;
216 CLOSE invoice_line_type;
217
218 IF (l_line_name is null) THEN
219 l_line_name := 'NONE';
220 END IF;
221
222 l_contract_number := NULL;
223 -- Assign the lines to the correct format
224 IF ((l_format_type = '1') OR (lines.khr_id IS NOT NULL AND lines.kle_id IS NOT NULL)) THEN
225 --setting the format type
226 IF(NOT(l_format_set)) THEN
227 l_format_type := '1';
228 l_format_set := TRUE;
229 END IF;
230
231 OPEN contract_number( lines.khr_id );
232 FETCH contract_number INTO l_contract_number;
233 CLOSE contract_number;
234
235 IF (lines.khr_id <> l_khr_id) THEN
236 l_khr_id := lines.khr_id;
237 END IF;
238
239 --Get Asset Name from OKX View
240 l_asset_name := NULL;
241 l_asset_descr := NULL;
242
243 OPEN asset_name(lines.kle_id);
244 FETCH asset_name INTO l_asset_name, l_asset_descr;
245 CLOSE asset_name;
246
247 IF (l_flag = 'Y') THEN
248 l_flag := 'N';
249 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, LPAD('-', 121, '-'));
250 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rpad('Lease Contract', 30, ' ') ||rpad('Asset Number', 15, ' ') ||rpad('Asset Description', 31, ' ')|| rpad('Item', 20, ' ') || lpad('Amount',25,' '));
251 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,LPAD('-', 121, '-'));
252
253 END IF;
254 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rpad(l_contract_number, 30,' ') ||rpad(nvl(l_asset_name, ' '),15,' ')||rpad(nvl(l_asset_descr, ' '),31,' ')||rpad(l_line_name,20,' ')|| lpad(l_formatted_amount,25,' '));
255 ELSIF ((l_format_type = '2') OR (lines.khr_id IS NOT NULL AND lines.kle_id IS NULL)) THEN
256
257 --setting the format type
258 IF(NOT(l_format_set)) THEN
259 l_format_type := '2';
260 l_format_set := TRUE;
261 END IF;
262
263 OPEN contract_number( lines.khr_id );
264 FETCH contract_number INTO l_contract_number;
265 CLOSE contract_number;
266
267 IF (lines.khr_id <> l_khr_id) THEN
268 l_khr_id := lines.khr_id;
269 END IF;
270 IF (l_flag = 'Y') THEN
271 l_flag := 'N';
272
273 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, LPAD('-', 121, '-'));
274 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rpad('Lease Contract' ,45, ' ')||rpad('Item' ,51,' ')||lpad('Amount',25,' '));
275 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, LPAD('-', 121, '-'));
276 END IF;
277 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rpad(l_contract_number,45,' ')||rpad(l_line_name,51,' ')||lpad(l_formatted_amount,25,' ') );
278 ELSE
279 IF (l_flag = 'Y') THEN
280 l_flag := 'N';
281 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, LPAD('-', 121, '-'));
282 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, rpad('Item' ,96,' ') ||lpad('Amount',25,' '));
283 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, LPAD('-', 121, '-'));
284 END IF;
285 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(l_line_name,96,' ') || lpad(l_formatted_amount,25,' '));
286 END IF;
287
288 END LOOP;
289
290 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, RPAD(' ', 95, ' ') || RPAD('-', 26, '-') );
291 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, LPAD('Invoice Total : ', 96, ' ') ||lpad(okl_accounting_util.format_amount(hdr.amount,hdr.currency_code), 25 ,' ') );
292 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,RPAD('=', 121, '='));
293
294 --Start afresh for next invoice
295 l_khr_id := NULL;
296 l_kle_id := NULL;
297 END LOOP;
298 x_return_status := okl_api.g_ret_sts_success;
299 EXCEPTION
300 WHEN OTHERS THEN
301 x_return_status := okl_api.g_ret_sts_unexp_error;
302 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Error in Printing Consolidated Bill : '||SQLERRM);
303 END print_cons_bill;
304
305
306
307
308 PROCEDURE print_cons_bill_conc (
309 errbuf OUT NOCOPY VARCHAR2,
310 retcode OUT NOCOPY NUMBER
311
312 )
313
314 IS
315
316 l_api_version NUMBER := 1;
317 lx_msg_count NUMBER;
318 l_from_bill_date DATE;
319 l_to_bill_date DATE;
320 l_count1 NUMBER :=0;
321 l_count2 NUMBER :=0;
322 l_count NUMBER :=0;
323 i NUMBER :=0;
324 l_msg_index_out NUMBER :=0;
325 lx_msg_data VARCHAR2(4000);
326 lx_return_status VARCHAR2(1);
327
328 BEGIN
329
330 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Starting Consolidated Bill Printing ... ');
331
332
333
334 Okl_Print_Cons_Bill.print_cons_bill(
335 p_init_msg_list => Okl_Api.G_FALSE,
336 x_return_status => lx_return_status,
337 x_msg_count => lx_msg_count,
338 x_msg_data => errbuf
339 );
340
341 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'End Consolidated Bill Printing. ');
342 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '************************************');
343 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Program Run Date:'||SYSDATE);
344 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '***********************************************');
345 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error Counts = '||lx_msg_count);
346 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Detailed Error Messages For Each Records and Columns from TAPI ');
347 BEGIN
348 IF ( lx_msg_count > 0 ) THEN
349 FOR i IN 1..lx_msg_count LOOP
350
351 fnd_msg_pub.get (p_msg_index => i,
352 p_encoded => 'F',
353 p_data => lx_msg_data,
354 p_msg_index_out => l_msg_index_out);
355
356 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,i || ': ' || lx_msg_data);
357 END LOOP;
358 END IF;
359 EXCEPTION
360 WHEN OTHERS THEN
361 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
362
363 END;
364 EXCEPTION
365 WHEN OTHERS THEN
366 NULL ;
367 END print_cons_bill_conc;
368
369 END Okl_Print_Cons_Bill;