[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS2007_CDROM_PKG
Source
1 PACKAGE BODY igi_cis2007_cdrom_pkg AS
2 -- $Header: igipcrub.pls 120.1.12010000.2 2008/12/19 13:30:33 gaprasad ship $
3
4 Procedure Spawn_Loader(p_csv_file_name IN Varchar2) Is
5 l_message Varchar2(1000);
6 l_data_file_name Varchar2(2000);
7 l_request1_id Number;
8 l_request2_id Number;
9 l_phase Varchar2(100);
10 l_status Varchar2(100);
11 l_dev_phase Varchar2(100);
12 l_dev_status Varchar2(100);
13
14 l_appln_name Varchar2(10) := 'IGI';
15 l_con_cp Varchar2(10) := 'IGIPCDUP';
16 l_con_cp_desc Varchar2(200):= 'IGI: CIS2007 Contractor CD-ROM Data Upload Process';
17
18 l_subcon_cp Varchar2(10) := 'IGIPSDUP';
19 l_subcon_cp_desc Varchar2(200):= 'IGI: CIS2007 Subcontractor CD-ROM Data Upload Process';
20
21 E_Request1_Submit_Error Exception;
22 E_Request2_Submit_Error Exception;
23 E_Request1_Wait_Error Exception;
24 E_Request2_Wait_Error Exception;
25 E_Loader1_Failure Exception;
26 E_Loader2_Failure Exception;
27
28 Begin
29 l_data_file_name := p_csv_file_name;
30 l_request1_id := fnd_request.submit_request(application => l_appln_name,
31 program => l_con_cp,
32 description => l_con_cp_desc,
33 start_time => NULL,
34 sub_request => FALSE,
35 argument1 => l_data_file_name,
36 argument2 => chr(0),
37 argument3 => NULL,
38 argument4 => NULL,
39 argument5 => NULL,
40 argument6 => NULL,
41 argument7 => NULL,
42 argument8 => NULL,
43 argument9 => NULL,
44 argument10 => NULL,
45 argument11 => NULL,
46 argument12 => NULL,
47 argument13 => NULL,
48 argument14 => NULL,
49 argument15 => NULL,
50 argument16 => NULL,
51 argument17 => NULL,
52 argument18 => NULL,
53 argument19 => NULL,
54 argument20 => NULL,
55 argument21 => NULL,
56 argument22 => NULL,
57 argument23 => NULL,
58 argument24 => NULL,
59 argument25 => NULL,
60 argument26 => NULL,
61 argument27 => NULL,
62 argument28 => NULL,
63 argument29 => NULL,
64 argument30 => NULL,
65 argument31 => NULL,
66 argument32 => NULL,
67 argument33 => NULL,
68 argument34 => NULL,
69 argument35 => NULL,
70 argument36 => NULL,
71 argument37 => NULL,
72 argument38 => NULL,
73 argument39 => NULL,
74 argument40 => NULL,
75 argument41 => NULL,
76 argument42 => NULL,
77 argument43 => NULL,
78 argument44 => NULL,
79 argument45 => NULL,
80 argument46 => NULL,
81 argument47 => NULL,
82 argument48 => NULL,
83 argument49 => NULL,
84 argument50 => NULL,
85 argument51 => NULL,
86 argument52 => NULL,
87 argument53 => NULL,
88 argument54 => NULL,
89 argument55 => NULL,
90 argument56 => NULL,
91 argument57 => NULL,
92 argument58 => NULL,
93 argument59 => NULL,
94 argument60 => NULL,
95 argument61 => NULL,
96 argument62 => NULL,
97 argument63 => NULL,
98 argument64 => NULL,
99 argument65 => NULL,
100 argument66 => NULL,
101 argument67 => NULL,
102 argument68 => NULL,
103 argument69 => NULL,
104 argument70 => NULL,
105 argument71 => NULL,
106 argument72 => NULL,
107 argument73 => NULL,
108 argument74 => NULL,
109 argument75 => NULL,
110 argument76 => NULL,
111 argument77 => NULL,
112 argument78 => NULL,
113 argument79 => NULL,
114 argument80 => NULL,
115 argument81 => NULL,
116 argument82 => NULL,
117 argument83 => NULL,
118 argument84 => NULL,
119 argument85 => NULL,
120 argument86 => NULL,
121 argument87 => NULL,
122 argument88 => NULL,
123 argument89 => NULL,
124 argument90 => NULL,
125 argument91 => NULL,
126 argument92 => NULL,
127 argument93 => NULL,
128 argument94 => NULL,
129 argument95 => NULL,
130 argument96 => NULL,
131 argument97 => NULL,
132 argument98 => NULL,
133 argument99 => NULL,
134 argument100 => NULL);
135
136 If l_request1_id = 0 Then
137 Raise E_Request1_Submit_Error;
138 Else
139 Commit;
140 End If;
141
142 -- Wait for request completion
143 If Not fnd_concurrent.wait_for_request(l_request1_id,
144 10, -- interval seconds
145 0, -- max wait seconds
146 l_phase,
147 l_status,
148 l_dev_phase,
149 l_dev_status,
150 l_message) Then
151 Raise E_Request1_Wait_Error;
152 End If;
153
154 -- Check request completion status
155 If l_dev_phase <> 'COMPLETE' Or l_dev_status <> 'NORMAL' Then
156 Raise E_Loader1_Failure;
157 End If;
158
159 l_request2_id := fnd_request.submit_request(application => l_appln_name,
160 program => l_subcon_cp,
161 description => l_subcon_cp_desc,
162 start_time => NULL,
163 sub_request => FALSE,
164 argument1 => l_data_file_name,
165 argument2 => chr(0),
166 argument3 => NULL,
167 argument4 => NULL,
168 argument5 => NULL,
169 argument6 => NULL,
170 argument7 => NULL,
171 argument8 => NULL,
172 argument9 => NULL,
173 argument10 => NULL,
174 argument11 => NULL,
175 argument12 => NULL,
176 argument13 => NULL,
177 argument14 => NULL,
178 argument15 => NULL,
179 argument16 => NULL,
180 argument17 => NULL,
181 argument18 => NULL,
182 argument19 => NULL,
183 argument20 => NULL,
184 argument21 => NULL,
185 argument22 => NULL,
186 argument23 => NULL,
187 argument24 => NULL,
188 argument25 => NULL,
189 argument26 => NULL,
190 argument27 => NULL,
191 argument28 => NULL,
192 argument29 => NULL,
193 argument30 => NULL,
194 argument31 => NULL,
195 argument32 => NULL,
196 argument33 => NULL,
197 argument34 => NULL,
198 argument35 => NULL,
199 argument36 => NULL,
200 argument37 => NULL,
201 argument38 => NULL,
202 argument39 => NULL,
203 argument40 => NULL,
204 argument41 => NULL,
205 argument42 => NULL,
206 argument43 => NULL,
207 argument44 => NULL,
208 argument45 => NULL,
209 argument46 => NULL,
210 argument47 => NULL,
211 argument48 => NULL,
212 argument49 => NULL,
213 argument50 => NULL,
214 argument51 => NULL,
215 argument52 => NULL,
216 argument53 => NULL,
217 argument54 => NULL,
218 argument55 => NULL,
219 argument56 => NULL,
220 argument57 => NULL,
221 argument58 => NULL,
222 argument59 => NULL,
223 argument60 => NULL,
224 argument61 => NULL,
225 argument62 => NULL,
226 argument63 => NULL,
227 argument64 => NULL,
228 argument65 => NULL,
229 argument66 => NULL,
230 argument67 => NULL,
231 argument68 => NULL,
232 argument69 => NULL,
233 argument70 => NULL,
234 argument71 => NULL,
235 argument72 => NULL,
236 argument73 => NULL,
237 argument74 => NULL,
238 argument75 => NULL,
239 argument76 => NULL,
240 argument77 => NULL,
241 argument78 => NULL,
242 argument79 => NULL,
243 argument80 => NULL,
244 argument81 => NULL,
245 argument82 => NULL,
246 argument83 => NULL,
247 argument84 => NULL,
248 argument85 => NULL,
249 argument86 => NULL,
250 argument87 => NULL,
251 argument88 => NULL,
252 argument89 => NULL,
253 argument90 => NULL,
254 argument91 => NULL,
255 argument92 => NULL,
256 argument93 => NULL,
257 argument94 => NULL,
258 argument95 => NULL,
259 argument96 => NULL,
260 argument97 => NULL,
261 argument98 => NULL,
262 argument99 => NULL,
263 argument100 => NULL);
264
265 If l_request2_id = 0 Then
266 Raise E_Request2_Submit_Error;
267 Else
268 Commit;
269 End If;
270
271 -- Wait for request completion
272 If Not fnd_concurrent.wait_for_request(l_request2_id,
273 10, -- interval seconds
274 0, -- max wait seconds
275 l_phase,
276 l_status,
277 l_dev_phase,
278 l_dev_status,
279 l_message) THEN
280 Raise E_Request2_Wait_Error;
281 End If;
282
283 -- Check request completion status
284 IF l_dev_phase <> 'COMPLETE' Or l_dev_status <> 'NORMAL' Then
285 Raise E_Loader2_Failure;
286 End If;
287 Exception
288 When E_Request1_Submit_Error Then
289 fnd_message.retrieve(l_message);
290 fnd_file.put_line(fnd_file.log, l_message);
291 Raise;
292 When E_Request2_Submit_Error Then
293 fnd_message.retrieve(l_message);
294 fnd_file.put_line(fnd_file.log, l_message);
295 Raise;
296 When E_Request1_Wait_Error Then
297 fnd_message.retrieve(l_message);
298 fnd_file.put_line(fnd_file.log, l_message);
299 Raise;
300 When E_Request2_Wait_Error Then
301 fnd_message.retrieve(l_message);
302 fnd_file.put_line(fnd_file.log, l_message);
303 Raise;
304 When E_Loader1_Failure Then
305 fnd_message.set_name('IGI', 'IGIPCDUP_NOT_NORM_COMPLETE');
306 fnd_message.set_token('FILE_NAME', l_data_file_name);
307 l_message := fnd_message.get;
308 fnd_file.put_line(fnd_file.log, l_message);
309 Raise;
310 When E_Loader2_Failure Then
311 fnd_message.set_name('IGI', 'IGIPSDUP_NOT_NORM_COMPLETE');
312 fnd_message.set_token('FILE_NAME', l_data_file_name);
313 l_message := fnd_message.get;
314 fnd_file.put_line(fnd_file.log, l_message);
315 Raise;
316 When Others Then
317 fnd_message.retrieve(l_message);
318 fnd_file.put_line(fnd_file.log, l_message);
319 Raise;
320 END Spawn_Loader;
321
322 Procedure Match_And_Update(P_Upl_Option IN Varchar2) IS
323 l_message Varchar2(1000);
324 l_match_flag Varchar2(1);
325 l_awt_group_id ap_suppliers.awt_group_id%TYPE;
326 l_vendor_id ap_suppliers.vendor_id%TYPE;
327
328 l_matched_flag ap_suppliers.match_status_flag%TYPE := 'M';
329 l_date Date := SYSDATE;
330 l_utr_type Varchar2(1) := NULL;
331
332 l_prof_net_wth Varchar2(100) := 'IGI_CIS2007_NET_WTH_GROUP';
333 l_prof_gross_wth Varchar2(100) := 'IGI_CIS2007_GROSS_WTH_GROUP';
334 l_count Number;
335
336 Cursor C_Igi_Cis_Cdrom_Lines Is
337 Select subcontractor_utr,
338 subcontractor_name,
339 subcontractor_ref_id,
340 tax_month_last_paid,
341 tax_treatment
342 From igi_cis_cdrom_lines_t;
343
344 Cursor C_Cnt(p_utr in igi_cis_cdrom_lines_t.subcontractor_utr%Type) Is
345 Select count(*) cnt
346 From igi_cis_cdrom_lines_t
347 Where subcontractor_utr = p_utr;
348 Begin
349 For C_Igi_Cis_Cdrom_Lines_Rec In C_Igi_Cis_Cdrom_Lines Loop
350 l_match_flag := 'U';
351 l_awt_group_id := NULL;
352 l_vendor_id := NULL;
353 l_utr_type := NULL;
354 l_count := 0;
355
356 For C_Cnt_Rec in C_Cnt(C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr) Loop
357 l_count := C_Cnt_Rec.cnt;
358 End Loop;
359
360 If l_count = 1 Then -- Only one record is found in the CD-ROM
361 Begin
362 If (C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr Is Not Null) Then
363 Select pov.vendor_id
364 Into l_vendor_id
365 From ap_suppliers pov
366 Where pov.partnership_utr = C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr;
367 End If;
368 Exception
369 When Others Then
370 l_vendor_id := NULL;
371 l_utr_type := NULL;
372 End;
373 If (l_vendor_id Is Not NUll) Then
374 l_match_flag := 'M';
375 l_utr_type := 'P';
376 Else
377 Begin
378 If (C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr Is Not Null) Then
379 Select pov.vendor_id
380 Into l_vendor_id
381 From ap_suppliers pov
382 Where pov.unique_tax_reference_num = C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr;
383 End If;
384 Exception
385 When Others Then
386 l_vendor_id := NULL;
387 l_utr_type := NULL;
388 End;
389 If (l_vendor_id Is Not Null) Then
390 l_match_flag := 'M';
391 l_utr_type := 'U';
392 End If;
393 End If;
394 End If;
395 /* Updating the interface table with match flag - to generate the report */
396 Update igi_cis_cdrom_lines_t
397 Set match_flag = l_match_flag
398 Where subcontractor_utr = C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr;
399
400 /* if CD-ROM subcontractor matches with AP_SUPPLIERS subcontractor and user asks for Upload also */
401 If (l_match_flag = 'M' And p_upl_option = 'U') Then
402 If (upper(C_Igi_Cis_Cdrom_Lines_Rec.tax_treatment) = 'NET') Then
403 l_awt_group_id := fnd_profile.VALUE(l_prof_net_wth);
404 Elsif (upper(C_Igi_Cis_Cdrom_Lines_Rec.tax_treatment) = 'GROSS') Then
405 l_awt_group_id := fnd_profile.VALUE(l_prof_gross_wth);
406 End If;
407
408 /* calling to update existing certificates .. */
409 /* Bug 5705187 */
410 IGI_CIS2007_TAX_EFF_DATE.main (
411 p_vendor_id => l_vendor_id,
412 p_vendor_site_id => NULL,
413 p_tax_grp_id => l_awt_group_id,
414 p_pay_tax_grp_id => l_awt_group_id, /* Bug 7218825 */
415 p_source => 'CDROM',
416 p_effective_date => l_date
417 );
418
419 /* calling PO API to update PO tables - AP_SUPPLIERS, AP_SUPPLIER_SITES */
420 Igi_cis2007_igipverp_pkg.pr_po_api(l_vendor_id,
421 NULL,
422 l_matched_flag,
423 l_date,
424 l_awt_group_id,
425 l_utr_type,
426 C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr,
427 C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_name,
428 C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_ref_id,
429 fnd_global.conc_request_id); --Bug 5606118
430
431 End If;
432 End LooP;
433 Exception
434 When Others Then
435 fnd_message.retrieve(l_message);
436 fnd_file.put_line(fnd_file.log, l_message);
437 Raise;
438 End Match_And_Update;
439
440 Procedure Generate_Report(p_upl_option IN Varchar2) Is
441 l_message VARCHAR2(1000);
442 Cursor C_Cdrom_Matched_Vendors Is
443 Select subcontractor_utr,
444 subcontractor_name,
445 subcontractor_ref_id,
446 tax_month_last_paid,
447 tax_treatment
448 From igi_cis_cdrom_lines_t
449 Where match_flag = 'M'
450 Order by subcontractor_name;
451
452 Cursor C_Cdrom_Unmatched_Vendors Is
453 Select subcontractor_utr,
454 subcontractor_name,
455 subcontractor_ref_id,
456 tax_month_last_paid,
457 tax_treatment
458 From igi_cis_cdrom_lines_t
459 Where match_flag = 'U'
460 Order by subcontractor_name;
461
462 Cursor C_Vendor_Not_Found Is
463 Select vendor_name,
464 partnership_utr ,
465 unique_tax_reference_num ,
466 national_insurance_number,
467 company_registration_number
468 From ap_suppliers
469 Where cis_enabled_flag = 'Y'
470 and vendor_id not in (
471 SELECT p.vendor_id
472 FROM ap_suppliers p, igi_cis_cdrom_lines_t l
473 WHERE (p.partnership_utr = l.subcontractor_utr
474 Or p.unique_tax_reference_num = l.subcontractor_utr)
475 And match_flag = 'M')
476 Order by vendor_name;
477
478 Cursor C_Site_Invoice_Count(P_Utr in igi_cis_cdrom_lines_t.subcontractor_utr%Type) Is
479 Select atr.tax_name tax_name,
480 atr.tax_rate tax_rate,
481 pvs.vendor_site_code site_code,
482 nvl(inv.inv_count,0) inv_count
483 From ap_suppliers pv,
484 ap_supplier_sites pvs,
485 ap_awt_tax_rates atr,
486 (Select count(distinct(aps.invoice_id)) inv_count,
487 api.vendor_id,
488 api.vendor_site_id
489 From ap_payment_schedules aps,
490 ap_invoices api
491 Where aps.amount_remaining > 0
492 And api.invoice_id = aps.invoice_id
493 Group by api.vendor_id, api.vendor_site_id) inv
494 Where (pv.partnership_utr = P_Utr
495 Or pv.unique_tax_reference_num = P_Utr)
496 And pvs.vendor_id = pv.vendor_id
497 And atr.vendor_id(+) = pvs.vendor_id
498 And atr.vendor_site_id(+) = pvs.vendor_site_id
499 And trunc(sysdate) between trunc(nvl(atr.start_date(+), sysdate))
500 And trunc(nvl(atr.end_date(+), sysdate))
501 And inv.vendor_id(+) = atr.vendor_id
502 And inv.vendor_site_id(+) = atr.vendor_site_id
503 Order by pvs.vendor_site_code, atr.tax_name;
504 l_rec_count Number;
505 Begin
506 fnd_file.put_line(fnd_file.output, '');
507 If (p_upl_option = 'U') Then
508 fnd_file.put_line(fnd_file.output,'CD-ROM Data : Matched and Updated');
509 fnd_file.put_line(fnd_file.output,'---------------------------------');
510 Else
511 fnd_file.put_line(fnd_file.output, 'CD-ROM Data : Matched');
512 fnd_file.put_line(fnd_file.output, '---------------------');
513 End If;
514
515 For C_Cdrom_Matched_Vendors_Rec IN C_Cdrom_Matched_Vendors Loop
516 fnd_file.put_line(fnd_file.output,'');
517 fnd_file.put_line(fnd_file.output,
518 'Subcontractor UTR Subcontractor Name NINO or CRN Tax month last paid Tax treatment');
519 fnd_file.put_line(fnd_file.output,
520 '----------------- ---------------------------- ----------- ------------------- -------------');
521 fnd_file.put_line(fnd_file.output,
522 rpad(nvl(to_char(C_Cdrom_Matched_Vendors_Rec.subcontractor_utr),' '),17) || ' ' ||
523 rpad(nvl(C_Cdrom_Matched_Vendors_Rec.subcontractor_name,' '),28) || ' ' ||
524 rpad(nvl(C_Cdrom_Matched_Vendors_Rec.subcontractor_ref_id,' '),11) || ' ' ||
525 rpad(nvl(to_char(C_Cdrom_Matched_Vendors_Rec.tax_month_last_paid),' '),19) || ' ' ||
526 C_Cdrom_Matched_Vendors_Rec.tax_treatment);
527 l_rec_count := 0;
528 For C_Site_Invoice_Count_Rec in C_Site_Invoice_Count(
529 C_Cdrom_Matched_Vendors_Rec.subcontractor_utr) Loop
530 If l_rec_count = 0 Then
531 fnd_file.put_line(fnd_file.output,'');
532 fnd_file.put_line(fnd_file.output,
533 'Current Withholding Tax Certificate Details for ' ||
534 C_Cdrom_Matched_Vendors_Rec.subcontractor_name);
535 fnd_file.put_line(fnd_file.output,
536 '-----------------------------------------------');
537 fnd_file.put_line(fnd_file.output,
538 'Site Tax Code Tax Rate Number of Open Invoices For the Site');
539 fnd_file.put_line(fnd_file.output,
540 '--------------- --------------- --------------- ------------------------------------');
541 End If;
542 fnd_file.put_line(fnd_file.output,
543 rpad(nvl(C_Site_Invoice_Count_Rec.site_code,' '),15) || ' ' ||
544 rpad(nvl(C_Site_Invoice_Count_Rec.tax_name,' '),15) || ' ' ||
545 rpad(nvl(to_char(C_Site_Invoice_Count_Rec.tax_rate),' '),15) || ' ' ||
546 rpad(nvl(to_char(C_Site_Invoice_Count_Rec.inv_count),' '),15));
547 l_rec_count := l_rec_count + 1;
548 End Loop;
549 End Loop;
550
551 fnd_file.put_line(fnd_file.output, '');
552 fnd_file.put_line(fnd_file.output,'');
553
554 If (p_upl_option = 'U') Then
555 fnd_file.put_line(fnd_file.output,'CD-ROM Data : Unmatched and Not Updated');
556 fnd_file.put_line(fnd_file.output,'---------------------------------------');
557 Else
558 fnd_file.put_line(fnd_file.output, 'CD-ROM Data : Unmatched');
559 fnd_file.put_line(fnd_file.output, '-----------------------');
560 End If;
561
562 fnd_file.put_line(fnd_file.output,'');
563 fnd_file.put_line(fnd_file.output,
564 'Subcontractor UTR Subcontractor Name NINO or CRN Tax month last paid Tax treatment');
565 fnd_file.put_line(fnd_file.output,
566 '----------------- ---------------------------- ----------- ------------------- -------------');
567
568 For C_Cdrom_Unmatched_Vendors_Rec IN C_Cdrom_Unmatched_Vendors Loop
569 fnd_file.put_line(fnd_file.output,
570 rpad(nvl(to_char(C_Cdrom_Unmatched_Vendors_Rec.subcontractor_utr),' '),17) || ' ' ||
571 rpad(nvl(C_Cdrom_Unmatched_Vendors_Rec.subcontractor_name,' '),28) || ' ' ||
572 rpad(nvl(C_Cdrom_Unmatched_Vendors_Rec.subcontractor_ref_id,' '),11) || ' ' ||
573 rpad(nvl(to_char(C_Cdrom_Unmatched_Vendors_Rec.tax_month_last_paid),' '),19) || ' ' ||
574 C_Cdrom_Unmatched_Vendors_Rec.tax_treatment);
575 End Loop;
576
577 fnd_file.put_line(fnd_file.output,'');
578 fnd_file.put_line(fnd_file.output,'');
579 fnd_file.put_line(fnd_file.output,
580 'CIS Enabled Subcontractors not found in the CD-ROM data received from HMRC.');
581 fnd_file.put_line(fnd_file.output,
582 '---------------------------------------------------------------------------');
583 fnd_file.put_line(fnd_file.output,'');
584 fnd_file.put_line(fnd_file.output,
585 'Vendor Name Subcontractor UTR Partnership UTR NINO CRN ');
586 fnd_file.put_line(fnd_file.output,
587 '----------------------------- ----------------- --------------- ---------- --------- ');
588
589 For C_Vendor_Not_Found_Rec IN C_Vendor_Not_Found Loop
590 fnd_file.put_line(fnd_file.output,
591 rpad(nvl(C_Vendor_Not_Found_Rec.vendor_name,' '),29) || ' ' ||
592 rpad(nvl(to_char(C_Vendor_Not_Found_Rec.unique_tax_reference_num),' '),17) || ' ' ||
593 rpad(nvl(to_char(C_Vendor_Not_Found_Rec.partnership_utr),' '),15) || ' ' ||
594 rpad(nvl(C_Vendor_Not_Found_Rec.national_insurance_number,' '),10) || ' ' ||
595 C_Vendor_Not_Found_Rec.company_registration_number);
596 End Loop;
597 Exception
598 When Others Then
599 fnd_message.retrieve(l_message);
600 fnd_file.put_line(fnd_file.log, l_message);
601 Raise;
602 End Generate_Report;
603
604 Procedure Cis_Duplicate_Data Is
605 l_dup_rec CHAR(1) := 'N';
606 l_count Number := 0;
607
608 Cursor C_Partnership_UTR Is
609 Select PARTNERSHIP_UTR
610 From ap_suppliers
611 Where cis_enabled_flag='Y'
612 Group by PARTNERSHIP_UTR
613 Having count(PARTNERSHIP_UTR) > 1;
614
615 Cursor C_Subcontractor_UTR IS
616 Select UNIQUE_TAX_REFERENCE_NUM
617 From ap_suppliers
618 Where cis_enabled_flag='Y'
619 Group by UNIQUE_TAX_REFERENCE_NUM
620 Having count(UNIQUE_TAX_REFERENCE_NUM) > 1;
621 Begin
622 For C_Partnership_UTR_Rec in C_Partnership_UTR Loop
623 l_dup_rec := 'Y';
624 End Loop;
625
626 For C_Subcontractor_UTR_Rec in C_Subcontractor_UTR Loop
627 l_dup_rec := 'Y';
628 End Loop;
629
630 If l_dup_rec = 'Y' Then
631 fnd_file.put_line(fnd_file.output, '');
632 fnd_file.put_line(fnd_file.output,
633 'The CD-ROM Data Upload Process found duplicate values in the system.');
634 fnd_file.put_line(fnd_file.output,
635 'These duplicate values could lead to potential data corruption.');
636 fnd_file.put_line(fnd_file.output,
637 'CD-ROM records matching to duplicate records in the system would not be uploaded');
638 fnd_file.put_line(fnd_file.output,
639 'Please rectify the duplicate data in your system and then submit the CD-ROM Upload Process again.');
640 l_count := 0;
641 For C_Partnership_UTR_Rec in C_Partnership_UTR Loop
642 If l_count = 0 Then
643 fnd_file.put_line(fnd_file.output, '');
644 fnd_file.put_line(fnd_file.output,
645 'Duplicate Partnership Unique Taxpayer Reference found in the system');
646 fnd_file.put_line(fnd_file.output,
647 '-------------------------------------------------------------------');
648 End If;
649 fnd_file.put_line(fnd_file.output, C_Partnership_UTR_Rec.PARTNERSHIP_UTR);
650 l_count := l_count + 1;
651 End loop;
652
653 l_count := 0;
654 For C_Subcontractor_UTR_Rec in C_Subcontractor_UTR Loop
655 If l_count = 0 Then
656 fnd_file.put_line(fnd_file.output, '');
657 fnd_file.put_line(fnd_file.output,
658 'Duplicate Subcontractor Unique Taxpayer Reference found in the system');
659 fnd_file.put_line(fnd_file.output,
660 '---------------------------------------------------------------------');
661 End If;
662 fnd_file.put_line(fnd_file.output, C_Subcontractor_UTR_Rec.UNIQUE_TAX_REFERENCE_NUM);
663 l_count := l_count + 1;
664 End Loop;
665 End If;
666 End Cis_Duplicate_Data;
667
668 Procedure Cdrom_Duplicate_Data Is
669 l_dup_rec CHAR(1) := 'N';
670 l_count Number := 0;
671
672 Cursor C_Subcontractor_UTR IS
673 Select SUBCONTRACTOR_UTR
674 From igi_cis_cdrom_lines_t
675 Group by SUBCONTRACTOR_UTR
676 Having count(SUBCONTRACTOR_UTR) >1;
677 Begin
678 For C_Subcontractor_UTR_Rec in C_Subcontractor_UTR Loop
679 l_dup_rec := 'Y';
680 End Loop;
681
682 If l_dup_rec = 'Y' Then
683 fnd_file.put_line(fnd_file.output, '');
684 fnd_file.put_line(fnd_file.output,
685 'The CD-ROM Data Upload Process found duplicate values in the CD-ROM data received from HMRC.');
686 fnd_file.put_line(fnd_file.output,
687 'These Duplicate values could lead to potential data corruption.');
688 fnd_file.put_line(fnd_file.output,
689 'Duplicate CD-ROM records would not be uploaded');
690 fnd_file.put_line(fnd_file.output,
691 'Please rectify the CD-ROM data and then submit the CD-ROM Upload Process again.');
692 l_count := 0;
693 For C_Subcontractor_UTR_Rec in C_Subcontractor_UTR Loop
694 If l_count = 0 Then
695 fnd_file.put_line(fnd_file.output, '');
696 fnd_file.put_line(fnd_file.output,
697 'Duplicate Subcontractor Unique Taxpayer Reference found in the CD-ROM data');
698 fnd_file.put_line(fnd_file.output,
699 '--------------------------------------------------------------------------');
700 End If;
701 fnd_file.put_line(fnd_file.output, C_Subcontractor_UTR_Rec.SUBCONTRACTOR_UTR);
702 l_count := l_count + 1;
703 End Loop;
704 End If;
705 End Cdrom_Duplicate_Data;
706
707 Procedure Import_Cdrom_Data_Process( Errbuf OUT NOCOPY Varchar2,
708 Retcode OUT NOCOPY Number,
709 P_Upl_Option IN Varchar2 ) Is
710 l_csv_file_name Varchar2(2000) := Null;
711 l_message Varchar2(1000);
712 l_meaning Varchar2(80);
713 E_No_Csv_File_Error Exception;
714 E_Update_Not_Allowed Exception;
715
716 Cursor C_Upl_Meaning Is
717 Select Meaning
718 From Igi_lookups
719 Where Lookup_type = 'IGI_CIS2007_CDROM_OPTION'
720 and lookup_code = 'U';
721 Begin
722 fnd_file.put_line(fnd_file.output, 'Date : ' || SYSDATE);
723 fnd_file.put_line(fnd_file.output,
724 'Construction Industry Scheme : CD-ROM Data Upload Process Report');
725 fnd_file.put_line(fnd_file.output,
726 '----------------------------------------------------------------');
727 fnd_file.put_line(fnd_file.output,'');
728 l_csv_file_name := fnd_profile.VALUE('IGI_CIS2007_CDROM_DATA_PATH');
729 fnd_file.put_line(fnd_file.log, 'Upload Option: ' || p_upl_option);
730 fnd_file.put_line(fnd_file.log, 'CD-ROM csv file: ' || l_csv_file_name);
731 fnd_file.put_line(fnd_file.log, '');
732
733 If P_Upl_Option = 'U' Then
734 If Trunc(sysdate) < trunc(to_date(fnd_profile.value('IGI_CIS2007_LIB_DATE'),'DD-MM-YYYY')) Then
735 For C_Upl_Meaning_Rec in C_Upl_Meaning Loop
736 l_meaning := C_Upl_Meaning_Rec.Meaning;
737 End Loop;
738 fnd_file.put_line(fnd_file.output,
739 'The ' || l_meaning || ' Option of the CD-ROM Data Upload Process ' ||
740 'cannot be run before ' || to_char(to_date(fnd_profile.value('IGI_CIS2007_LIB_DATE'),
741 'DD-MM-YYYY'), 'DD-MON-YYYY'));
742 Raise E_Update_Not_Allowed;
743 End If;
744 End If;
745
746 If (l_csv_file_name IS NULL) Then
747 Raise E_No_Csv_File_Error;
748 END IF;
749
750 Spawn_Loader(l_csv_file_name);
751
752 Cis_Duplicate_Data;
753 Cdrom_Duplicate_Data;
754
755 Match_And_Update(p_upl_option);
756 Generate_Report(p_upl_option);
757 Exception
758 When E_No_Csv_File_Error Then
759 fnd_message.set_name('IGI', 'IGI_CDROM_NO_FILES_TO_IMPORT');
760 fnd_message.set_token('FILE_NAME', l_csv_file_name);
761 l_message := fnd_message.get;
762 fnd_file.put_line(fnd_file.log, l_message);
763 retcode := 2;
764 errbuf := l_message;
765 WHEN Others Then
766 fnd_message.retrieve(l_message);
767 fnd_file.put_line(fnd_file.log, l_message);
768 retcode := 2;
769 errbuf := l_message;
770 End Import_Cdrom_Data_Process;
771
772 End IGI_CIS2007_CDROM_PKG;