[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS_CI36_DATA_PKG
Source
1 PACKAGE BODY IGI_CIS_CI36_DATA_PKG as
2 /* $Header: igiciseb.pls 115.19 2003/12/17 13:54:28 hkaniven noship $ */
3
4
5
6 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7 l_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
8 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
9 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
10 l_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
11 l_error_level NUMBER := FND_LOG.LEVEL_ERROR;
12 l_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
13 l_path VARCHAR2(50) := 'IGI.PLSQL.igiciseb.IGI_CIS_CI36_DATA_PKG.';
14
15
16 PROCEDURE Debug ( p_level IN NUMBER, p_path IN VARCHAR2, p_mesg IN VARCHAR2 ) is
17 BEGIN
18 IF (p_level >= l_debug_level ) THEN
19 FND_LOG.STRING (p_level , l_path || p_path , p_mesg );
20 END IF;
21
22 END ;
23
24
25
26 PROCEDURE Extract_data ( Errbuf OUT NOCOPY VARCHAR2,
27 Retcode OUT NOCOPY NUMBER,
28 x_cis_report IN VARCHAR2,
29 x_operating_unit IN VARCHAR2,
30 --x_sob_name IN VARCHAR2,
31 --x_set_of_books_id IN NUMBER,
32 x_low_date1 IN VARCHAR2,
33 x_high_date1 IN VARCHAR2,
34 x_vendor_id IN NUMBER
35 )
36 is
37
38
39
40 CURSOR c_cis_payments IS
41 SELECT Invoice_id , Invoice_num, Invoice_payment_id, Amount, Pmt_vch_number, Pmt_vch_amount,
42 Pmt_vch_description, Check_number, Check_date, Vendor_id, Vendor_name, Vendor_site_id,
43 Vendor_site_code, Group_id, Certificate_type, Certificate_number, Certificate_description,
44 Ni_number
45 FROM igi_cis_ci36_payments;
46
47 /* Modified the AND condition to include nvl check for icatr1.end_date Bug Bug#2443887 -solakshm*/
48 CURSOR c_cis_awt_tax_rates (P_vendor_id igi_cis_awt_tax_rates.vendor_id%TYPE,
49 P_vendor_site_id igi_cis_awt_tax_rates.vendor_site_id%TYPE,
50 P_check_date igi_cis_ci36_payments.Check_date%TYPE )
51 IS
52 SELECT icatr1.certificate_type , icatr1.certificate_number ,
53 Substr(icatr1.comments,1,50) "comments", icatr1.ni_number
54 FROM igi_cis_awt_tax_rates icatr1
55 WHERE icatr1.vendor_id = P_vendor_id
56 AND icatr1.vendor_site_id = P_vendor_site_id
57 AND icatr1.tax_name = fnd_profile.value ('IGI_CIS_TAX_CODE')
58 AND TRUNC(NVL(P_check_date,icatr1.start_date))
59 BETWEEN TRUNC(icatr1.start_date)
60 AND nvl(TRUNC(icatr1.end_date),to_date('9999/12/31','YYYY/MM/DD'))
61 AND NVL(icatr1.priority, '-1') =
62 (SELECT NVL(MIN(icatr2.priority), '-1')
63 FROM igi_cis_awt_tax_rates icatr2
64 WHERE icatr2.vendor_id = P_vendor_id
65 AND icatr2.vendor_site_id = P_vendor_site_id
66 AND icatr2.tax_name = fnd_profile.value ('IGI_CIS_TAX_CODE')
67 AND TRUNC(NVL(P_check_date,icatr2.start_date))
68 BETWEEN TRUNC(icatr2.start_date)
69 AND nvl(TRUNC(icatr2.end_date),to_date('9999/12/31','YYYY/MM/DD')));
70
71 /* Added this cursor for Bug 2938921 rgopalan */
72 -- Bug 3102763 (Tpradhan), Removed the ABS function which was applied for cis_amount
73 -- Bug 3146816 (Tpradhan), flipped the sign for cis_amount by multiplying it with -1
74 CURSOR cur_insert_into_extract (p_invoice_id number)
75 IS
76 SELECT
77 certificate_type,
78 vendor_name,
79 vendor_site_code,
80 certificate_number,
81 certificate_description,
82 ni_number,
83 pmt_vch_number,
84 pmt_vch_description,
85 SUM (DECODE (awt_group_id,icip.group_id,0,
86 NVL (DECODE (line_type_lookup_code, 'ITEM', aid.amount, 0), 0)))
87 material_amount,
88 SUM (NVL (DECODE (awt_group_id, icip.group_id, aid.amount,0),0)) labor_amount,
89 SUM (NVL (DECODE (line_type_lookup_code,'AWT',-aid.amount,0),0)) cis_amount,
90 (NVL (icip.Amount,0) - SUM (NVL (DECODE (line_type_lookup_code, 'TAX', aid.amount, 0), 0))) net_amount
91 FROM
92 ap_invoice_distributions aid,
93 igi_cis_ci36_payments icip
94 WHERE
95 aid.invoice_id = icip.invoice_id
96 AND aid.invoice_id = p_invoice_id
97 GROUP BY
98 certificate_type,
99 vendor_name,
100 vendor_site_code,
101 certificate_number,
102 certificate_description,
103 ni_number,
104 pmt_vch_number,
105 pmt_vch_description,
106 icip.Amount;
107
108
109 /* Added this cursor for Bug 2938921 rgopalan */
110 CURSOR cur_invoice_list
111 IS
112 SELECT
113 ai.invoice_id,
114 ai.invoice_num,
115 icip.segment1,
116 icip.check_number,
117 icip.check_date,
118 icip.amount,
119 icip.address_line1,
120 icip.address_line2,
121 icip.address_line3,
122 icip.zip,
123 icip.pmt_vch_received_date
124 FROM
125 ap_invoices ai, igi_cis_ci36_payments icip
126 WHERE
127 ai.invoice_id = icip.invoice_id;
128
129
130 /* Cursors for the log */
131 Cursor cur_log1 is
132 select count(*) from igi_cis_ci36_extract;
133
134 Cursor cur_log2 is
135 select count(*) from igi_cis_ci36_payments;
136
137 l_invoice_num igi_cis_ci36_payments.invoice_num%TYPE;
138 l_certificate_type igi_cis_awt_tax_rates.certificate_type%TYPE;
139 l_certificate_number igi_cis_awt_tax_rates.certificate_number%TYPE;
140 l_certificate_description Varchar2(50);
141 l_ni_number igi_cis_awt_tax_rates.ni_number%TYPE;
142 l_return_status Number;
143 x_low_date date;
144 x_high_date date;
145 l_count1 Number := 0;
146 l_count2 Number := 0;
147 l_cis_records Number := 0;
148 l_errbuf VARCHAR2(100) := '';
149
150 BEGIN
151 /* deleting all the records from the temporary table as it will have data
152 related to previous session */
153
154 x_low_date := to_date(x_low_date1, 'YYYY/MM/DD HH24:MI:SS');
155 x_high_date := to_date(x_high_date1, 'YYYY/MM/DD HH24:MI:SS');
156
157 /***************** This is for the log file ******************/
158
159
160 Debug(l_state_level, 'Extract_data',' *************** Populating the CIS Temporary Table *************** ');
161 Debug(l_state_level, 'Extract_data',' PARAMTER LIST');
162 Debug(l_state_level, 'Extract_data',' x_cis_report -> ' || x_cis_report);
163 Debug(l_state_level, 'Extract_data',' x_low_date -> ' || x_low_date );
164 Debug(l_state_level, 'Extract_data',' x_high_date -> ' || x_high_date );
165 Debug(l_state_level, 'Extract_data',' ');
166 Debug(l_state_level, 'Extract_data',' Deleting rows from temporary table');
167
168
169
170 open cur_log1;
171 fetch cur_log1 INTO l_count1;
172 close cur_log1;
173
174 open cur_log2;
175 fetch cur_log2 INTO l_count2;
176 close cur_log2;
177
178
179 Debug(l_state_level, 'Extract_data',' No. of records selected for deletion' ||
180 ' from igi_cis_ci36_extract : ' || l_count1);
181 Debug(l_state_level, 'Extract_data',' No. of records selected for deletion' ||
182 ' from igi_cis_ci36_payments : ' || l_count2);
183
184 /***************** This is for the log file ******************/
185
186 DELETE FROM igi_cis_ci36_extract;
187 DELETE FROM igi_cis_ci36_payments;
188
189 COMMIT;
190
191 /***************** This is for the log file ******************/
192 /***************** This is for the log file ******************/
193
194 Debug(l_state_level, 'Extract_data',' all rows deleted ');
195 Debug(l_state_level, 'Extract_data',' ');
196 Debug(l_state_level, 'Extract_data',' Inserting records into igi_cis_ci36_payments Table ');
197
198
199
200 INSERT INTO igi_cis_ci36_payments (
201 INVOICE_PAYMENT_ID,
202 INVOICE_ID,
203 PMT_VCH_NUMBER,
204 PMT_VCH_AMOUNT,
205 PMT_VCH_DESCRIPTION,
206 AMOUNT,
207 PAYMENT_NUM,
208 PMT_VCH_RECEIVED_DATE,
209 INVOICE_NUM,
210 VENDOR_ID,
211 VENDOR_SITE_ID,
212 CHECK_NUMBER,
213 CHECK_DATE,
214 GROUP_ID
215 )
216 SELECT
217 icip.invoice_payment_id,
218 icip.invoice_id,
219 icip.pmt_vch_number,
220 icip.pmt_vch_amount,
221 icip.pmt_vch_description,
222 icip.amount,
223 icip.payment_num,
224 icip.pmt_vch_received_date,
225 ai.invoice_num,
226 ai.vendor_id,
227 ai.vendor_site_id,
228 ac.check_number,
229 ac.check_date,
230 aag.group_id
231 FROM
232 igi_cis_invoice_payments icip,
233 ap_invoices ai,
234 ap_awt_groups aag,
235 ap_checks ac
236 WHERE ai.payment_status_flag = 'Y'
237 AND ai.invoice_id = icip.invoice_id
238 AND ai.awt_group_id = aag.group_id
239 AND aag.name = igi_cis_get_profile.cis_tax_group
240 AND icip.check_id = ac.check_id
241 AND ac.void_date IS NULL
242 AND ai.vendor_id = nvl(x_vendor_id,ai.vendor_id)
243 AND TRUNC(NVL(ac.check_date,x_low_date)) BETWEEN TRUNC(x_low_date) AND TRUNC(x_high_date);
244
245
246 COMMIT;
247
248 /***************** This is for the log file ******************/
249 l_count1 := 0;
250 open cur_log2;
251 fetch cur_log2 INTO l_count1;
252 close cur_log2;
253
254 Debug(l_state_level, 'Extract_data',' No. of records inserted : ' || l_count1);
255 Debug(l_state_level, 'Extract_data',' ');
256 Debug(l_state_level, 'Extract_data',' deleting all black listed' ||
257 ' vendors from igi_cis_ci36_payments ');
258
259
260
261
262 /***************** This is for the log file ******************/
263
264 /* deleting all black listed vendors */
265 DELETE FROM igi_cis_ci36_payments a
266 WHERE EXISTS (SELECT 'x' FROM po_vendors b
267 WHERE b.vendor_id = a.vendor_id
268 AND nvl(b.enabled_flag,'N') <> 'Y');
269
270 /***************** This is for the log file ******************/
271 l_count2 := 0;
272 open cur_log2;
273 fetch cur_log2 INTO l_count2;
274 close cur_log2;
275
276 Debug(l_state_level, 'Extract_data',' No. of records deleted : '
277 || (nvl(l_count1,0) - nvl(l_count2,0)));
278 Debug(l_state_level, 'Extract_data',' ');
279 Debug(l_state_level, 'Extract_data',' Updating the vendor name' ||
280 ' for the corresponding vendor id in igi_cis_ci36_payments ');
281
282
283 /***************** This is for the log file ******************/
284
285 /* updating the vendor name for the corresponding vendor id */
286
287 UPDATE igi_cis_ci36_payments a
288 SET (a.vendor_name,a.segment1) =
289 (SELECT vendor_name,segment1 FROM po_vendors
290 WHERE vendor_id = a.vendor_id);
291 COMMIT;
292
293 /***************** This is for the log file ******************/
294
295
296 Debug(l_state_level, 'Extract_data',' Removing the vendor sites which' ||
297 ' does not have automatic withholding'||
298 ' tax from igi_cis_ci36_payments ');
299
300
301
302
303 l_count1 := 0;
304 open cur_log2;
305 fetch cur_log2 INTO l_count1;
306 close cur_log2;
307 /***************** This is for the log file ******************/
308
309
310 /* remove the vendor sites which does not have automatic withholding tax */
311 DELETE FROM igi_cis_ci36_payments a
312 WHERE EXISTS (SELECT 'x' FROM po_vendor_sites b
313 WHERE b.vendor_site_id = a.vendor_site_id
314 AND ( nvl(b.allow_awt_flag,'N') <> 'Y'
315 OR b.awt_group_id <> a.group_id));
316
317 /***************** This is for the log file ******************/
318 l_count2 := 0;
319 open cur_log2;
320 fetch cur_log2 INTO l_count2;
321 close cur_log2;
322
323 /***************** This is for the log file ******************/
324
325
326 Debug(l_state_level, 'Extract_data',' No. of records deleted : '
327 || (nvl(l_count1,0) - nvl(l_count2,0)));
328 Debug(l_state_level, 'Extract_data',' ');
329 Debug(l_state_level, 'Extract_data',' Updating with proper vendor site code' ||
330 ' for the corresponding vendor site id' ||
331 ' in igi_cis_ci36_payments ');
332
333
334
335 /* updating with proper vendor site code for the corresponding vendor site id */
336 UPDATE igi_cis_ci36_payments a
337 SET (a.vendor_site_code,a.address_line1,A.address_line2,A.address_line3,a.zip ) =
338 (SELECT vendor_site_code,address_line1,address_line2,address_line3,zip
339 FROM po_vendor_sites b
340 WHERE b.vendor_site_id = a.vendor_site_id );
341 COMMIT;
342
343
344 Debug(l_state_level, 'Extract_data',' Updating certificate details in igi_cis_ci36_payments ');
345
346 FOR cur_cis_payments in c_cis_payments
347 LOOP
348
349 OPEN c_cis_awt_tax_rates (cur_cis_payments.vendor_id,
350 cur_cis_payments.vendor_site_id,
351 cur_cis_payments.check_date);
352
353 FETCH c_cis_awt_tax_rates INTO l_certificate_type,
354 l_certificate_number,
355 l_certificate_description,
356 l_ni_number;
357
358 IF (c_cis_awt_tax_rates%FOUND) THEN
359
360 UPDATE igi_cis_ci36_payments
361 SET certificate_type = l_certificate_type,
362 certificate_number = l_certificate_number,
363 certificate_description = l_certificate_description,
364 ni_number = l_ni_number
365 WHERE vendor_id = cur_cis_payments.vendor_id
366 AND vendor_site_id = cur_cis_payments.vendor_site_id
367 AND invoice_id = cur_cis_payments.invoice_id
368 AND invoice_payment_id = cur_cis_payments.invoice_payment_id
369 AND check_number = cur_cis_payments.check_number;
370
371 END IF;
372
373 IF (c_cis_awt_tax_rates%ISOPEN) THEN
374 CLOSE c_cis_awt_tax_rates;
375 END IF;
376
377 END LOOP;
378
379 Debug(l_state_level, 'Extract_data',' Inserting certificate details in igi_cis_ci36_extract table ');
380
381
382 /* Bug 2938921 rgopalan 25.6.2003 START*/
383
384 FOR J IN cur_invoice_list
385 LOOP
386
387 Debug(l_state_level, 'Extract_data',' Invoice number --> '|| J.invoice_num);
388
389 FOR I IN cur_insert_into_extract (J.invoice_id)
390 LOOP
391
392 INSERT INTO igi_cis_ci36_extract (
393 invoice_num,
394 Segment1,
395 check_number,
396 check_date,
397 amount,
398 address_line1,
399 address_line2,
400 address_line3,
401 zip,
402 pmt_vch_received_date,
403 certificate_type,
404 vendor_name,
405 vendor_site_code,
406 certificate_number,
407 certificate_description,
408 ni_number,
409 pmt_vch_number,
410 pmt_vch_description,
411 material_amount,
412 labor_amount,
413 cis_amount,
414 net_amount)
415 VALUES
416 (J.invoice_num,
417 J.Segment1,
418 J.check_number,
419 J.check_date,
420 J.amount,
421 J.address_line1,
422 J.address_line2,
423 J.address_line3,
424 J.zip,
425 J.pmt_vch_received_date,
426 I.certificate_type,
427 I.vendor_name,
428 I.vendor_site_code,
429 I.certificate_number,
430 I.certificate_description,
431 I.ni_number,
432 I.pmt_vch_number,
433 I.pmt_vch_description,
434 I.material_amount,
435 I.labor_amount,
436 I.cis_amount,
437 I.net_amount);
438
439 END LOOP;
440
441 END LOOP;
442
443 /* Bug 2938921 rgopalan 25.6.2003 END */
444
445 Debug(l_state_level, 'Extract_data','Commiting the changes ');
446
447 COMMIT;
448
449 /***************** This is for the log file ******************/
450 l_count1 := 0;
451 open cur_log1;
452 fetch cur_log1 INTO l_count1;
453 close cur_log1;
454
455 /***************** This is for the log file ******************/
456
457
458 Debug(l_state_level, 'Extract_data',' No. of records Inserted into igi_cis_ci36_extract table : ' || l_count1);
459 Debug(l_state_level, 'Extract_data',' ');
460 Debug(l_state_level, 'Extract_data',' Placing a request for the report ' || x_cis_report);
461
462
463
464 /* placing a request for the report */
465
466
467 IF x_cis_report = 'IGIPCI36' THEN
468
469 l_return_status := Fnd_request.submit_request
470 (APPLICATION => 'IGI',
471 PROGRAM => 'IGIPCI36',
472 DESCRIPTION => 'Construction industry scheme: CI36 report (End of Year)',
473 START_TIME => '',
474 SUB_REQUEST => FALSE,
475 ARGUMENT1 => x_low_date,
476 ARGUMENT2 => x_high_date);
477
478 Debug(l_state_level, 'Extract_data',' Placed a request for the report IGIPCI36 ');
479
480 ELSIF x_cis_report='IGIPNVCH' THEN
481
482 l_return_status := Fnd_request.submit_request
483 (APPLICATION => 'IGI',
484 PROGRAM => 'IGIPNVCH',
485 DESCRIPTION => '',
486 START_TIME => '',
487 SUB_REQUEST => FALSE,
488 ARGUMENT1 => x_low_date,
489 ARGUMENT2 => x_high_date,
490 ARGUMENT3 => x_vendor_id);
491
492 Debug(l_state_level, 'Extract_data',' Placed a request for the report IGIPNVCH');
493
494
495
496 ELSIF x_cis_report='IGIPVCH' THEN
497
498 l_return_status := Fnd_request.submit_request
499 (APPLICATION =>'IGI',
500 PROGRAM =>'IGIPVCH',
501 DESCRIPTION =>'',
502 START_TIME => '',
503 SUB_REQUEST => FALSE,
504 ARGUMENT1 => x_low_date,
505 ARGUMENT2 => x_high_date,
506 ARGUMENT3 => x_vendor_id);
507
508 Debug(l_state_level, 'Extract_data',' Placed a request for the report IGIPVCH');
509
510 END IF;
511
512 Debug(l_state_level, 'Extract_data',' **************** END **************** ');
513
514
515 EXCEPTION
516 WHEN OTHERS THEN
517
518 /* making sure all the cursors are closed properly */
519 IF (c_cis_awt_tax_rates%ISOPEN) THEN
520 CLOSE c_cis_awt_tax_rates;
521 END IF;
522
523 retcode := 2;
524
525
526 FND_MESSAGE.set_name ('IGI', 'IGI_GEN_UNHANDLED_EXCEPTION');
527 FND_MESSAGE.set_token ('NAME','IGI_CIS_CI36_DATA_PKG.EXTRACT_DATA');
528 errbuf := Fnd_message.get;
529
530 Debug(l_excep_level, 'Extract_data',errbuf);
531
532 l_errbuf := 'Error Message: ' || sqlerrm || ' Error Code: ' || to_char(sqlcode);
533 Debug(l_excep_level, 'Extract_data',l_errbuf);
534
535
536 IF ( l_unexp_level >= l_debug_level ) THEN
537 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
538 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
539 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
540 FND_LOG.MESSAGE ( l_unexp_level,l_path || 'Extract_data' , TRUE);
541 END IF;
542 APP_EXCEPTION.raise_exception;
543
544 END Extract_data;
545
546 END Igi_cis_ci36_data_pkg;