[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.3 2009/04/20 15:08:03 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 and
367 pov.end_date_active IS NULL; -- Bug 8446711
368 End If;
369 Exception
370 When Others Then
371 l_vendor_id := NULL;
372 l_utr_type := NULL;
373 End;
374 If (l_vendor_id Is Not NUll) Then
375 l_match_flag := 'M';
376 l_utr_type := 'P';
377 Else
378 Begin
379 If (C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr Is Not Null) Then
380 Select pov.vendor_id
381 Into l_vendor_id
382 From ap_suppliers pov
383 Where pov.unique_tax_reference_num = C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr and
384 pov.end_date_active IS NULL; -- Bug 8446711
385 End If;
386 Exception
387 When Others Then
388 l_vendor_id := NULL;
389 l_utr_type := NULL;
390 End;
391 If (l_vendor_id Is Not Null) Then
392 l_match_flag := 'M';
393 l_utr_type := 'U';
394 End If;
395 End If;
396 End If;
397 /* Updating the interface table with match flag - to generate the report */
398 Update igi_cis_cdrom_lines_t
399 Set match_flag = l_match_flag
400 Where subcontractor_utr = C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr;
401
402 /* if CD-ROM subcontractor matches with AP_SUPPLIERS subcontractor and user asks for Upload also */
403 If (l_match_flag = 'M' And p_upl_option = 'U') Then
404 If (upper(C_Igi_Cis_Cdrom_Lines_Rec.tax_treatment) = 'NET') Then
405 l_awt_group_id := fnd_profile.VALUE(l_prof_net_wth);
406 Elsif (upper(C_Igi_Cis_Cdrom_Lines_Rec.tax_treatment) = 'GROSS') Then
407 l_awt_group_id := fnd_profile.VALUE(l_prof_gross_wth);
408 End If;
409
410 /* calling to update existing certificates .. */
411 /* Bug 5705187 */
412 IGI_CIS2007_TAX_EFF_DATE.main (
413 p_vendor_id => l_vendor_id,
414 p_vendor_site_id => NULL,
415 p_tax_grp_id => l_awt_group_id,
416 p_pay_tax_grp_id => l_awt_group_id, /* Bug 7218825 */
417 p_source => 'CDROM',
418 p_effective_date => l_date
419 );
420
421 /* calling PO API to update PO tables - AP_SUPPLIERS, AP_SUPPLIER_SITES */
422 Igi_cis2007_igipverp_pkg.pr_po_api(l_vendor_id,
423 NULL,
424 l_matched_flag,
425 l_date,
426 l_awt_group_id,
427 l_utr_type,
428 C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_utr,
429 C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_name,
430 C_Igi_Cis_Cdrom_Lines_Rec.subcontractor_ref_id,
431 fnd_global.conc_request_id); --Bug 5606118
432
433 End If;
434 End LooP;
435 Exception
436 When Others Then
437 fnd_message.retrieve(l_message);
438 fnd_file.put_line(fnd_file.log, l_message);
439 Raise;
440 End Match_And_Update;
441
442 Procedure Generate_Report(p_upl_option IN Varchar2) Is
443 l_message VARCHAR2(1000);
444 Cursor C_Cdrom_Matched_Vendors Is
445 Select subcontractor_utr,
446 subcontractor_name,
447 subcontractor_ref_id,
448 tax_month_last_paid,
449 tax_treatment
450 From igi_cis_cdrom_lines_t
451 Where match_flag = 'M'
452 Order by subcontractor_name;
453
454 Cursor C_Cdrom_Unmatched_Vendors Is
455 Select subcontractor_utr,
456 subcontractor_name,
457 subcontractor_ref_id,
458 tax_month_last_paid,
459 tax_treatment
460 From igi_cis_cdrom_lines_t
461 Where match_flag = 'U'
462 Order by subcontractor_name;
463
464 Cursor C_Vendor_Not_Found Is
465 Select vendor_name,
466 partnership_utr ,
467 unique_tax_reference_num ,
468 national_insurance_number,
469 company_registration_number
470 From ap_suppliers
471 Where cis_enabled_flag = 'Y'
472 and vendor_id not in (
473 SELECT p.vendor_id
474 FROM ap_suppliers p, igi_cis_cdrom_lines_t l
475 WHERE (p.partnership_utr = l.subcontractor_utr
476 Or p.unique_tax_reference_num = l.subcontractor_utr)
477 And match_flag = 'M')
478 Order by vendor_name;
479
480 Cursor C_Site_Invoice_Count(P_Utr in igi_cis_cdrom_lines_t.subcontractor_utr%Type) Is
481 Select atr.tax_name tax_name,
482 atr.tax_rate tax_rate,
483 pvs.vendor_site_code site_code,
484 nvl(inv.inv_count,0) inv_count
485 From ap_suppliers pv,
486 ap_supplier_sites pvs,
487 ap_awt_tax_rates atr,
488 (Select count(distinct(aps.invoice_id)) inv_count,
489 api.vendor_id,
490 api.vendor_site_id
491 From ap_payment_schedules aps,
492 ap_invoices api
493 Where aps.amount_remaining > 0
494 And api.invoice_id = aps.invoice_id
495 Group by api.vendor_id, api.vendor_site_id) inv
496 Where (pv.partnership_utr = P_Utr
497 Or pv.unique_tax_reference_num = P_Utr)
498 And pvs.vendor_id = pv.vendor_id
499 And atr.vendor_id(+) = pvs.vendor_id
500 And atr.vendor_site_id(+) = pvs.vendor_site_id
501 And trunc(sysdate) between trunc(nvl(atr.start_date(+), sysdate))
502 And trunc(nvl(atr.end_date(+), sysdate))
503 And inv.vendor_id(+) = atr.vendor_id
504 And inv.vendor_site_id(+) = atr.vendor_site_id
505 Order by pvs.vendor_site_code, atr.tax_name;
506 l_rec_count Number;
507 Begin
508 fnd_file.put_line(fnd_file.output, '');
509 If (p_upl_option = 'U') Then
510 fnd_file.put_line(fnd_file.output,'CD-ROM Data : Matched and Updated');
511 fnd_file.put_line(fnd_file.output,'---------------------------------');
512 Else
513 fnd_file.put_line(fnd_file.output, 'CD-ROM Data : Matched');
514 fnd_file.put_line(fnd_file.output, '---------------------');
515 End If;
516
517 For C_Cdrom_Matched_Vendors_Rec IN C_Cdrom_Matched_Vendors Loop
518 fnd_file.put_line(fnd_file.output,'');
519 fnd_file.put_line(fnd_file.output,
520 'Subcontractor UTR Subcontractor Name NINO or CRN Tax month last paid Tax treatment');
521 fnd_file.put_line(fnd_file.output,
522 '----------------- ---------------------------- ----------- ------------------- -------------');
523 fnd_file.put_line(fnd_file.output,
524 rpad(nvl(to_char(C_Cdrom_Matched_Vendors_Rec.subcontractor_utr),' '),17) || ' ' ||
525 rpad(nvl(C_Cdrom_Matched_Vendors_Rec.subcontractor_name,' '),28) || ' ' ||
526 rpad(nvl(C_Cdrom_Matched_Vendors_Rec.subcontractor_ref_id,' '),11) || ' ' ||
527 rpad(nvl(to_char(C_Cdrom_Matched_Vendors_Rec.tax_month_last_paid),' '),19) || ' ' ||
528 C_Cdrom_Matched_Vendors_Rec.tax_treatment);
529 l_rec_count := 0;
530 For C_Site_Invoice_Count_Rec in C_Site_Invoice_Count(
531 C_Cdrom_Matched_Vendors_Rec.subcontractor_utr) Loop
532 If l_rec_count = 0 Then
533 fnd_file.put_line(fnd_file.output,'');
534 fnd_file.put_line(fnd_file.output,
535 'Current Withholding Tax Certificate Details for ' ||
536 C_Cdrom_Matched_Vendors_Rec.subcontractor_name);
537 fnd_file.put_line(fnd_file.output,
538 '-----------------------------------------------');
539 fnd_file.put_line(fnd_file.output,
540 'Site Tax Code Tax Rate Number of Open Invoices For the Site');
541 fnd_file.put_line(fnd_file.output,
542 '--------------- --------------- --------------- ------------------------------------');
543 End If;
544 fnd_file.put_line(fnd_file.output,
545 rpad(nvl(C_Site_Invoice_Count_Rec.site_code,' '),15) || ' ' ||
546 rpad(nvl(C_Site_Invoice_Count_Rec.tax_name,' '),15) || ' ' ||
547 rpad(nvl(to_char(C_Site_Invoice_Count_Rec.tax_rate),' '),15) || ' ' ||
548 rpad(nvl(to_char(C_Site_Invoice_Count_Rec.inv_count),' '),15));
549 l_rec_count := l_rec_count + 1;
550 End Loop;
551 End Loop;
552
553 fnd_file.put_line(fnd_file.output, '');
554 fnd_file.put_line(fnd_file.output,'');
555
556 If (p_upl_option = 'U') Then
557 fnd_file.put_line(fnd_file.output,'CD-ROM Data : Unmatched and Not Updated');
558 fnd_file.put_line(fnd_file.output,'---------------------------------------');
559 Else
560 fnd_file.put_line(fnd_file.output, 'CD-ROM Data : Unmatched');
561 fnd_file.put_line(fnd_file.output, '-----------------------');
562 End If;
563
564 fnd_file.put_line(fnd_file.output,'');
565 fnd_file.put_line(fnd_file.output,
566 'Subcontractor UTR Subcontractor Name NINO or CRN Tax month last paid Tax treatment');
567 fnd_file.put_line(fnd_file.output,
568 '----------------- ---------------------------- ----------- ------------------- -------------');
569
570 For C_Cdrom_Unmatched_Vendors_Rec IN C_Cdrom_Unmatched_Vendors Loop
571 fnd_file.put_line(fnd_file.output,
572 rpad(nvl(to_char(C_Cdrom_Unmatched_Vendors_Rec.subcontractor_utr),' '),17) || ' ' ||
573 rpad(nvl(C_Cdrom_Unmatched_Vendors_Rec.subcontractor_name,' '),28) || ' ' ||
574 rpad(nvl(C_Cdrom_Unmatched_Vendors_Rec.subcontractor_ref_id,' '),11) || ' ' ||
575 rpad(nvl(to_char(C_Cdrom_Unmatched_Vendors_Rec.tax_month_last_paid),' '),19) || ' ' ||
576 C_Cdrom_Unmatched_Vendors_Rec.tax_treatment);
577 End Loop;
578
579 fnd_file.put_line(fnd_file.output,'');
580 fnd_file.put_line(fnd_file.output,'');
581 fnd_file.put_line(fnd_file.output,
582 'CIS Enabled Subcontractors not found in the CD-ROM data received from HMRC.');
583 fnd_file.put_line(fnd_file.output,
584 '---------------------------------------------------------------------------');
585 fnd_file.put_line(fnd_file.output,'');
586 fnd_file.put_line(fnd_file.output,
587 'Vendor Name Subcontractor UTR Partnership UTR NINO CRN ');
588 fnd_file.put_line(fnd_file.output,
589 '----------------------------- ----------------- --------------- ---------- --------- ');
590
591 For C_Vendor_Not_Found_Rec IN C_Vendor_Not_Found Loop
592 fnd_file.put_line(fnd_file.output,
593 rpad(nvl(C_Vendor_Not_Found_Rec.vendor_name,' '),29) || ' ' ||
594 rpad(nvl(to_char(C_Vendor_Not_Found_Rec.unique_tax_reference_num),' '),17) || ' ' ||
595 rpad(nvl(to_char(C_Vendor_Not_Found_Rec.partnership_utr),' '),15) || ' ' ||
596 rpad(nvl(C_Vendor_Not_Found_Rec.national_insurance_number,' '),10) || ' ' ||
597 C_Vendor_Not_Found_Rec.company_registration_number);
598 End Loop;
599 Exception
600 When Others Then
601 fnd_message.retrieve(l_message);
602 fnd_file.put_line(fnd_file.log, l_message);
603 Raise;
604 End Generate_Report;
605
606 Procedure Cis_Duplicate_Data Is
607 l_dup_rec CHAR(1) := 'N';
608 l_count Number := 0;
609
610 Cursor C_Partnership_UTR Is
611 Select PARTNERSHIP_UTR
612 From ap_suppliers
613 Where cis_enabled_flag='Y' and
614 end_date_active IS NULL -- Bug 8446711
615 Group by PARTNERSHIP_UTR
616 Having count(PARTNERSHIP_UTR) > 1;
617
618 Cursor C_Subcontractor_UTR IS
619 Select UNIQUE_TAX_REFERENCE_NUM
620 From ap_suppliers
621 Where cis_enabled_flag='Y' and
622 end_date_active IS NULL -- Bug 8446711
623 Group by UNIQUE_TAX_REFERENCE_NUM
624 Having count(UNIQUE_TAX_REFERENCE_NUM) > 1;
625 Begin
626 For C_Partnership_UTR_Rec in C_Partnership_UTR Loop
627 l_dup_rec := 'Y';
628 End Loop;
629
630 For C_Subcontractor_UTR_Rec in C_Subcontractor_UTR Loop
631 l_dup_rec := 'Y';
632 End Loop;
633
634 If l_dup_rec = 'Y' Then
635 fnd_file.put_line(fnd_file.output, '');
636 fnd_file.put_line(fnd_file.output,
637 'The CD-ROM Data Upload Process found duplicate values in the system.');
638 fnd_file.put_line(fnd_file.output,
639 'These duplicate values could lead to potential data corruption.');
640 fnd_file.put_line(fnd_file.output,
641 'CD-ROM records matching to duplicate records in the system would not be uploaded');
642 fnd_file.put_line(fnd_file.output,
643 'Please rectify the duplicate data in your system and then submit the CD-ROM Upload Process again.');
644 l_count := 0;
645 For C_Partnership_UTR_Rec in C_Partnership_UTR Loop
646 If l_count = 0 Then
647 fnd_file.put_line(fnd_file.output, '');
648 fnd_file.put_line(fnd_file.output,
649 'Duplicate Partnership Unique Taxpayer Reference found in the system');
650 fnd_file.put_line(fnd_file.output,
651 '-------------------------------------------------------------------');
652 End If;
653 fnd_file.put_line(fnd_file.output, C_Partnership_UTR_Rec.PARTNERSHIP_UTR);
654 l_count := l_count + 1;
655 End loop;
656
657 l_count := 0;
658 For C_Subcontractor_UTR_Rec in C_Subcontractor_UTR Loop
659 If l_count = 0 Then
660 fnd_file.put_line(fnd_file.output, '');
661 fnd_file.put_line(fnd_file.output,
662 'Duplicate Subcontractor Unique Taxpayer Reference found in the system');
663 fnd_file.put_line(fnd_file.output,
664 '---------------------------------------------------------------------');
665 End If;
666 fnd_file.put_line(fnd_file.output, C_Subcontractor_UTR_Rec.UNIQUE_TAX_REFERENCE_NUM);
667 l_count := l_count + 1;
668 End Loop;
669 End If;
670 End Cis_Duplicate_Data;
671
672 Procedure Cdrom_Duplicate_Data Is
673 l_dup_rec CHAR(1) := 'N';
674 l_count Number := 0;
675
676 Cursor C_Subcontractor_UTR IS
677 Select SUBCONTRACTOR_UTR
678 From igi_cis_cdrom_lines_t
679 Group by SUBCONTRACTOR_UTR
680 Having count(SUBCONTRACTOR_UTR) >1;
681 Begin
682 For C_Subcontractor_UTR_Rec in C_Subcontractor_UTR Loop
683 l_dup_rec := 'Y';
684 End Loop;
685
686 If l_dup_rec = 'Y' Then
687 fnd_file.put_line(fnd_file.output, '');
688 fnd_file.put_line(fnd_file.output,
689 'The CD-ROM Data Upload Process found duplicate values in the CD-ROM data received from HMRC.');
690 fnd_file.put_line(fnd_file.output,
691 'These Duplicate values could lead to potential data corruption.');
692 fnd_file.put_line(fnd_file.output,
693 'Duplicate CD-ROM records would not be uploaded');
694 fnd_file.put_line(fnd_file.output,
695 'Please rectify the CD-ROM data and then submit the CD-ROM Upload Process again.');
696 l_count := 0;
697 For C_Subcontractor_UTR_Rec in C_Subcontractor_UTR Loop
698 If l_count = 0 Then
699 fnd_file.put_line(fnd_file.output, '');
700 fnd_file.put_line(fnd_file.output,
701 'Duplicate Subcontractor Unique Taxpayer Reference found in the CD-ROM data');
702 fnd_file.put_line(fnd_file.output,
703 '--------------------------------------------------------------------------');
704 End If;
705 fnd_file.put_line(fnd_file.output, C_Subcontractor_UTR_Rec.SUBCONTRACTOR_UTR);
706 l_count := l_count + 1;
707 End Loop;
708 End If;
709 End Cdrom_Duplicate_Data;
710
711 Procedure Import_Cdrom_Data_Process( Errbuf OUT NOCOPY Varchar2,
712 Retcode OUT NOCOPY Number,
713 P_Upl_Option IN Varchar2 ) Is
714 l_csv_file_name Varchar2(2000) := Null;
715 l_message Varchar2(1000);
716 l_meaning Varchar2(80);
717 E_No_Csv_File_Error Exception;
718 E_Update_Not_Allowed Exception;
719
720 Cursor C_Upl_Meaning Is
721 Select Meaning
722 From Igi_lookups
723 Where Lookup_type = 'IGI_CIS2007_CDROM_OPTION'
724 and lookup_code = 'U';
725 Begin
726 fnd_file.put_line(fnd_file.output, 'Date : ' || SYSDATE);
727 fnd_file.put_line(fnd_file.output,
728 'Construction Industry Scheme : CD-ROM Data Upload Process Report');
729 fnd_file.put_line(fnd_file.output,
730 '----------------------------------------------------------------');
731 fnd_file.put_line(fnd_file.output,'');
732 l_csv_file_name := fnd_profile.VALUE('IGI_CIS2007_CDROM_DATA_PATH');
733 fnd_file.put_line(fnd_file.log, 'Upload Option: ' || p_upl_option);
734 fnd_file.put_line(fnd_file.log, 'CD-ROM csv file: ' || l_csv_file_name);
735 fnd_file.put_line(fnd_file.log, '');
736
737 If P_Upl_Option = 'U' Then
738 If Trunc(sysdate) < trunc(to_date(fnd_profile.value('IGI_CIS2007_LIB_DATE'),'DD-MM-YYYY')) Then
739 For C_Upl_Meaning_Rec in C_Upl_Meaning Loop
740 l_meaning := C_Upl_Meaning_Rec.Meaning;
741 End Loop;
742 fnd_file.put_line(fnd_file.output,
743 'The ' || l_meaning || ' Option of the CD-ROM Data Upload Process ' ||
744 'cannot be run before ' || to_char(to_date(fnd_profile.value('IGI_CIS2007_LIB_DATE'),
745 'DD-MM-YYYY'), 'DD-MON-YYYY'));
746 Raise E_Update_Not_Allowed;
747 End If;
748 End If;
749
750 If (l_csv_file_name IS NULL) Then
751 Raise E_No_Csv_File_Error;
752 END IF;
753
754 Spawn_Loader(l_csv_file_name);
755
756 Cis_Duplicate_Data;
757 Cdrom_Duplicate_Data;
758
759 Match_And_Update(p_upl_option);
760 Generate_Report(p_upl_option);
761 Exception
762 When E_No_Csv_File_Error Then
763 fnd_message.set_name('IGI', 'IGI_CDROM_NO_FILES_TO_IMPORT');
764 fnd_message.set_token('FILE_NAME', l_csv_file_name);
765 l_message := fnd_message.get;
766 fnd_file.put_line(fnd_file.log, l_message);
767 retcode := 2;
768 errbuf := l_message;
769 WHEN Others Then
770 fnd_message.retrieve(l_message);
771 fnd_file.put_line(fnd_file.log, l_message);
772 retcode := 2;
773 errbuf := l_message;
774 End Import_Cdrom_Data_Process;
775
776 End IGI_CIS2007_CDROM_PKG;