1 PACKAGE BODY igi_cis2007_igipsupr_pkg AS
2 -- $Header: igipsupb.pls 120.1.12020000.1 2012/06/27 11:04:53 appldev ship $
3
4 FUNCTION beforereport RETURN BOOLEAN IS
5 BEGIN
6 pwhereclause := ' grp.group_id (+) = pov.awt_group_id
7 AND grp_tax.group_id (+) = grp.group_id
8 AND tax_codes.name (+) = grp_tax.tax_name
9 AND tax_codes.enabled_flag (+) = ''Y''
10 AND nvl(IGI_CIS2007_IGIPSUPR_PKG.check_active(tax_codes.START_DATE,tax_codes.inactive_date),''A'') = ''A''
11 AND tax.tax_name (+) = tax_codes.name
12 AND nvl(tax.rate_type,''X'') NOT IN (''CERTIFICATE'',''EXCEPTION'')
13 AND nvl(IGI_CIS2007_IGIPSUPR_PKG.check_active(tax.START_DATE,
14 tax.END_DATE),''A'') = ''A''
15 AND pov.cis_enabled_flag = ''Y''';
16
17 -- Changes made by arvind on 15-09-2006
18 /* IF (p_report = 'IGIPVERR') THEN
19 pwhereclause := pwhereclause ||
20 ' AND decode(igi_cis2007_igipverp_pkg.igi_cis_is_vendor_paid(pov.vendor_id),
21 ''NOTPAID'',
22 nvl(pov.cis_verification_date,add_months(SYSDATE, -25)),
23 add_months(SYSDATE, -25)) < add_months(SYSDATE, -24)';
24 END IF; */
25
26 IF p_report = 'IGIPVERR' THEN
27 pwhereclause := pwhereclause ||
28 ' AND nvl(trunc(cis_verification_date),trunc(sysdate)) <= trunc(sysdate)';
29 END IF;
30
31 -- End Changes
32
33 IF (p_report = 'IGIPSUPR') THEN
34 p_active := 'A';
35 END IF;
36
37 IF (p_active IS NOT NULL) THEN
38 pwhereclause := pwhereclause ||
39 ' AND IGI_CIS2007_IGIPSUPR_PKG.check_active(pov.START_DATE_ACTIVE,
40 pov.END_DATE_ACTIVE) = ' || '''' ||
41 p_active || '''';
42 END IF;
43
44 IF (p_supplier_from IS NOT NULL) THEN
45 pwhereclause := pwhereclause || ' AND pov.vendor_name >= ' || '''' ||
46 p_supplier_from || '''';
47 END IF;
48
49 IF (p_supplier_to IS NOT NULL) THEN
50 pwhereclause := pwhereclause || ' AND pov.vendor_name <= ' || '''' ||
51 p_supplier_to || '''';
52 END IF;
53
54 porderclause := ' order by ';
55
56 IF (p_report = 'IGIPVERR') THEN
57 porderclause := porderclause || 'VERIFY_STATUS, ';
58 END IF;
59
60 IF (p_sort_by IS NOT NULL) THEN
61 porderclause := porderclause || p_sort_by;
62 ELSE
63 porderclause := porderclause || 'VENDOR_NAME';
64 END IF;
65
66 RETURN(TRUE);
67
68 END beforereport;
69
70 FUNCTION get_p_supplier_from RETURN VARCHAR2 IS
71 BEGIN
72 RETURN(p_supplier_from);
73 END get_p_supplier_from;
74
75 FUNCTION get_p_supplier_to RETURN VARCHAR2 IS
76 BEGIN
77 RETURN(p_supplier_to);
78 END get_p_supplier_to;
79
80 FUNCTION get_p_report_title RETURN VARCHAR2 IS
81 l_report_title igi_lookups.meaning%TYPE := NULL;
82 BEGIN
83 BEGIN
84 SELECT meaning
85 INTO l_report_title
86 FROM igi_lookups
87 WHERE lookup_type = 'IGI_CIS2007_NEW_REPORTS'
88 AND lookup_code = p_report;
89 EXCEPTION
90 WHEN no_data_found THEN
91 NULL;
92 END;
93 RETURN(l_report_title);
94
95 END get_p_report_title;
96
97 FUNCTION get_p_org_name RETURN VARCHAR2 IS
98 l_org_id hr_operating_units.organization_id%TYPE := NULL;
99 l_org_name hr_operating_units.NAME%TYPE := NULL;
100 BEGIN
101 BEGIN
102 l_org_id := MO_GLOBAL.get_current_org_id;
103
104 SELECT NAME
105 INTO l_org_name
106 FROM hr_operating_units
107 WHERE organization_id = l_org_id;
108
109 EXCEPTION
110 WHEN no_data_found THEN
111 NULL;
112 END;
113 RETURN(l_org_name);
114 END get_p_org_name;
115
116 FUNCTION get_p_active RETURN VARCHAR2 IS
117 l_active_meaning igi_lookups.meaning%TYPE := NULL;
118 BEGIN
119 BEGIN
120 SELECT meaning
121 INTO l_active_meaning
122 FROM igi_lookups
123 WHERE lookup_type = 'IGI_CIS2007_ACTIVE_TYPE'
124 AND lookup_code = p_active;
125 EXCEPTION
126 WHEN no_data_found THEN
127 NULL;
128 END;
129 RETURN(l_active_meaning);
130 END get_p_active;
131
132 FUNCTION get_p_sortby RETURN VARCHAR2 IS
133 l_sortby_meaning igi_lookups.meaning%TYPE := NULL;
134 l_lookup_type igi_lookups.lookup_type%TYPE := NULL;
135 BEGIN
136 BEGIN
137 IF (p_report = 'IGIPVERR') THEN
138 l_lookup_type := 'IGI_CIS2007_VERR_SORT_COLS';
139 ELSIF (p_report = 'IGIPSUPR') THEN
140 l_lookup_type := 'IGI_CIS2007_SUPR_SORT_COLS';
141 END IF;
142
143 SELECT meaning
144 INTO l_sortby_meaning
145 FROM igi_lookups
146 WHERE lookup_type = l_lookup_type
147 AND lookup_code = p_sort_by;
148 EXCEPTION
149 WHEN no_data_found THEN
150 NULL;
151 END;
152 RETURN(l_sortby_meaning);
153 END get_p_sortby;
154
155 FUNCTION check_active
156 (
157 p_start_date DATE,
158 p_end_date DATE
159 ) RETURN VARCHAR2 IS
160
161 l_active_flag VARCHAR2(1) := NULL;
162 BEGIN
163 BEGIN
164 SELECT decode(sign(trunc(nvl(p_start_date, SYSDATE)) - trunc(SYSDATE)),
165 1,
166 'I',
167 decode(sign(trunc(nvl(p_end_date, SYSDATE)) -
168 trunc(SYSDATE)),
169 -1,
170 'I',
171 'A'))
172 INTO l_active_flag
173 FROM dual;
174 EXCEPTION
175 WHEN no_data_found THEN
176 NULL;
177 END;
178 RETURN(l_active_flag);
179
180 END check_active;
181
182 FUNCTION igi_cis_is_vendor_verified (l_vendor_id NUMBER)
183 RETURN VARCHAR2
184 AS
185 verify_status VARCHAR2(20);
186 BEGIN
187
188 SELECT 'VERIFIED'
189 INTO verify_status
190 FROM ap_suppliers
191 WHERE vendor_id = l_vendor_id
192 AND cis_verification_date > add_months(sysdate,-24)
193 AND rownum = 1;
194
195 RETURN 'VERIFIED';
196 EXCEPTION
197 WHEN no_data_found THEN
198 RETURN 'NOTVERIFIED';
199
200 END igi_cis_is_vendor_verified;
201
202 FUNCTION igi_cis_is_vendor_paid (l_vendor_id NUMBER,verify_date DATE DEFAULT SYSDATE)
203 RETURN VARCHAR2
204 AS
205 tax_year_start DATE;
206 paid_status VARCHAR2(20);
207 BEGIN
208
209 IF to_date(to_char(verify_date, 'dd-mm') || '2005', 'dd-mm-yyyy') >
210 to_date('05-04-2005', 'dd-mm-yyyy')
211 THEN
212 tax_year_start := to_date('06-04-' || to_char(verify_date, 'YYYY'),'DD-MM-YYYY');
213 ELSE
214 tax_year_start := add_months(to_date('06-04-' ||
215 to_char(verify_date, 'YYYY'),'DD-MM-YYYY'),
216 - 12);
217 END IF;
218
219 SELECT 'PAID'
220 INTO paid_status
221 FROM ap_checks ac,
222 ap_invoice_payments pay,
223 -- Bug 5642198 Start
224 ap_invoices inv,
225 ap_supplier_sites pvs
226 -- Bug 5642198 End
227 WHERE ac.vendor_id = l_vendor_id
228 AND ac.void_date IS NULL
229 AND pay.check_id = ac.check_id
230 AND accounting_date > add_months(tax_year_start, -24)
231 -- Bug 5642198 Start
232 AND inv.invoice_id = pay.invoice_id
233 AND pvs.vendor_id = inv.vendor_id
234 AND pvs.vendor_site_id = inv.vendor_site_id
235 AND upper(pvs.allow_awt_flag) = 'Y'
236 -- Bug 5642198 End
237 AND rownum = 1;
238
239 RETURN 'PAID';
240 EXCEPTION
241 WHEN no_data_found THEN
242 RETURN 'NOTPAID';
243
244 END igi_cis_is_vendor_paid;
245
246 FUNCTION igi_cis_new_verified(l_vendor_id NUMBER)
247 RETURN BOOLEAN
248 --VARCHAR2
249 AS
250 verify_status VARCHAR2(20);
251 BEGIN
252
253 SELECT 'VERIFIED'
254 INTO verify_status
255 FROM ap_suppliers
256 WHERE vendor_id = l_vendor_id
257 AND cis_verification_date > add_months(sysdate,-24)
258 AND rownum = 1;
259
260 RETURN TRUE;
261 EXCEPTION
262 WHEN no_data_found THEN
263 RETURN FALSE;
264
265 END igi_cis_new_verified;
266
267 FUNCTION is_paid (p_vendor_id number,
268 p_start_date date,
269 p_end_date date) RETURN boolean
270 AS
271 paid_status varchar2(8);
272 Begin
273 SELECT 'PAID'
274 INTO paid_status
275 FROM ap_checks ac,
276 ap_invoice_payments pay,
277 ap_invoices inv,
278 ap_supplier_sites pvs
279 WHERE ac.vendor_id = p_vendor_id
280 AND ac.void_date IS NULL
281 AND pay.check_id = ac.check_id
282 AND accounting_date between p_start_date and p_end_date
283 AND inv.invoice_id = pay.invoice_id
284 AND pvs.vendor_id = inv.vendor_id
285 AND pvs.vendor_site_id = inv.vendor_site_id
286 AND pvs.allow_awt_flag = 'Y'
287 AND rownum = 1;
288 RETURN true;
289 EXCEPTION
290 WHEN no_data_found THEN
291 RETURN false;
292 End is_paid;
293
294 FUNCTION is_there_in_monthly_return(p_vendor_id number,
295 p_start_date date,
296 p_end_date date) RETURN boolean
297 AS
298 v_tmp number(1);
299 Begin
300 select 1 into v_tmp
301 from igi_cis_mth_ret_hdr_h hdr, igi_cis_mth_ret_lines_h line
302 where hdr.header_id = line.header_id
303 and line.vendor_id = p_vendor_id
304 and hdr.period_ending_date between p_start_date and p_end_date
305 and rownum = 1;
306 Return true;
307 Exception
308 when no_data_found then
309 Return false;
310 End is_there_in_monthly_return;
311
312 FUNCTION is_details_available(p_vendor_id number) RETURN boolean
313 AS
314 cursor cert is
315 select upper(certificate_type) cert_type,nvl(end_date,sysdate) edt
316 from ap_awt_tax_rates
317 where vendor_id = p_vendor_id
318 and upper(certificate_type) in ('CIS4T','CIS5','CIS6','CIS4P');
319 first_cis_txyr_start date := to_date('06-04-2007','DD-MM-YYYY');
320 Begin
321 For i in cert loop
322 If i.cert_type = 'CIS4P' Then
323 Return true;
324 End if;
325 If i.cert_type in ('CIS4T','CIS5','CIS6')
326 and i.edt >= first_cis_txyr_start Then
327 Return true;
328 End if;
329 End loop;
330 Return false;
331 End is_details_available;
332
333 FUNCTION verification_status (p_vendor_id NUMBER) RETURN VARCHAR2
334 AS
335 first_cis_txyr_start date := to_date('06-04-2007','DD-MM-YYYY');
336 first_cis_txyr_end date;
337 curr_cis_txyr_start date;
338 curr_cis_txyr_end date;
339 prev_cis_txyr_start1 date;
340 prev_cis_txyr_end1 date;
341 prev_cis_txyr_start2 date;
342 prev_cis_txyr_end2 date;
343 v_check_list1 boolean;
344 v_check_list2 boolean;
345 v_check_list3 boolean;
346 Begin
347 first_cis_txyr_end := add_months(first_cis_txyr_start,12) - 1;
348 If sysdate between to_date('06-04-' || to_char(sysdate,'YYYY'),'DD-MM-YYYY')
349 and to_date('31-12-' || to_char(sysdate,'YYYY'),'DD-MM-YYYY') Then
350 curr_cis_txyr_start := to_date('06-04-' || to_char(sysdate,'YYYY'),'DD-MM-YYYY');
351 else
352 curr_cis_txyr_start := to_date('06-04-' || to_char(to_number(to_char(sysdate,'YYYY'))-1),'DD-MM-YYYY');
353 end if;
354 curr_cis_txyr_end := add_months(curr_cis_txyr_start,12) - 1;
355 prev_cis_txyr_start1 := add_months(curr_cis_txyr_start,-12);
356 prev_cis_txyr_end1 := add_months(prev_cis_txyr_start1,12) - 1;
357 prev_cis_txyr_start2 := add_months(curr_cis_txyr_start,-24);
358 prev_cis_txyr_end2 := add_months(prev_cis_txyr_start2,12) - 1;
359
360 /*dbms_output.put_line('first_cis_txyr_start = ' || first_cis_txyr_start);
361 dbms_output.put_line('first_cis_txyr_end = ' || first_cis_txyr_end);
362 dbms_output.put_line('curr_cis_txyr_start = ' || curr_cis_txyr_start);
363 dbms_output.put_line('curr_cis_txyr_end = ' || curr_cis_txyr_end);
364 dbms_output.put_line('prev_cis_txyr_start1 = ' || prev_cis_txyr_start1);
365 dbms_output.put_line('prev_cis_txyr_end1 = ' || prev_cis_txyr_end1);
366 dbms_output.put_line('prev_cis_txyr_start2 = ' || prev_cis_txyr_start2);
367 dbms_output.put_line('prev_cis_txyr_end2 = ' || prev_cis_txyr_end2);*/
368
369 If igi_cis_new_verified(p_vendor_id) THEN
370 RETURN 'VERIFIED';
371 END IF;
372
373 --Check whether vendor paid during current tax year or not.
374 If is_paid(p_vendor_id,curr_cis_txyr_start,curr_cis_txyr_end) Then
375 --dbms_output.put_line('Paid in current year so verified');
376 Return 'VERIFIED';
377 End if;
378 --dbms_output.put_line('Not Paid in current year');
379
380 --Check whether vendor paid during previous two tax years or not.
381 --If is_paid(p_vendor_id,prev_cis_txyr_start1,prev_cis_txyr_end2) Then
382 If is_paid(p_vendor_id,prev_cis_txyr_start2,prev_cis_txyr_end1) Then
383 --dbms_output.put_line('Paid in during last two year');
384 --Check whether either of previous year is before
385 --first cis tax start year
386 If prev_cis_txyr_end1 < first_cis_txyr_start or
387 prev_cis_txyr_end2 < first_cis_txyr_start Then
388 --Check whether vendor is paid during first cis tax year
389 --dbms_output.put_line('either of previous year is before');
390 --If is_paid(p_vendor_id,first_cis_txyr_start,first_cis_txyr_end) Then
391 --If vendor paid during first cis tax year then
392 --need to satisfy following conditions
393 --dbms_output.put_line('vendor is paid during first cis tax year');
394 --1. Check Vendor is there in monthly return of first cis tax year
395 v_check_list1 := is_there_in_monthly_return(p_vendor_id,first_cis_txyr_start,first_cis_txyr_end);
396 --2. Check Vendor is paid for last two years from first cis start date
397 v_check_list2 := is_paid(p_vendor_id,add_months(first_cis_txyr_start,-24),sysdate);
398 --3. Check whether vendor details available
399 v_check_list3 := is_details_available(p_vendor_id);
400 /*If v_check_list1 then
401 dbms_output.put_line('check list 1 = true');
402 end if;
403
404 If v_check_list2 = true then
405 dbms_output.put_line('check list 2 = true');
406 end if;
407 If v_check_list3 = true then
408 dbms_output.put_line('check list 3 = true');
409 end if;*/
410
411 If v_check_list1 = true or
412 (v_check_list2 = true and
413 v_check_list3 = true) Then
414 Return 'VERIFIED';
415 End If;
416 --If vendor does not satify check list then
417 --requires verification
418 --dbms_output.put_line('vendor does not satify check list - Not verified');
419 --Return 'NOTVERIFIED';
420 --End if;
421 --If vendor is not paid during first cis tax year then
422 --requires verification
423 --dbms_output.put_line('vendor is not paid during first cis tax year - Not verified');
424 Return 'NOTVERIFIED';
425 End If;
426 --If none of the previous tax year is before first cis tax year
427 --then vendor is verified
428 Return 'VERIFIED';
429 End if;
430 --If vendor is not paid during previous two tax years then requires Verification.
431 --dbms_output.put_line('vendor is not paid during previous two tax years - Not verified');
432 Return 'NOTVERIFIED';
433 end verification_status;
434
435 FUNCTION igi_cis_is_verify_required (l_vendor_id NUMBER)
436 RETURN VARCHAR2
437 AS
438 BEGIN
439 /*IF igi_cis_is_vendor_paid(l_vendor_id) = 'NOTPAID' AND
440 igi_cis_is_vendor_verified(l_vendor_id) = 'NOTVERIFIED'
441 THEN
442 return 'NOTVERIFIED';
443 ELSE
444 return 'VERIFIED';
445 END IF;*/
446 return verification_status(l_vendor_id);
447
448 END igi_cis_is_verify_required;
449
450 END igi_cis2007_igipsupr_pkg;
[Home] [Help]