[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS2007_IGIPVERP_PKG
Source
1 PACKAGE BODY igi_cis2007_igipverp_pkg AS
2 -- $Header: igipverb.pls 120.2.12010000.3 2008/12/19 13:43:28 gaprasad ship $
3
4 /* PO API to update the CORE tables */
5 PROCEDURE pr_po_core_update(p_header_id IN NUMBER) IS
6 l_awt_group_id ap_suppliers.awt_group_id%TYPE;
7 l_match_flag ap_suppliers.match_status_flag%TYPE;
8 l_request_id ap_suppliers.verification_request_id%TYPE;
9 CURSOR cur_igi_cis_verify_headers IS
10 SELECT vendor_id,
11 verification_number,
12 match_status,
13 tax_treatment_status,
14 cis_verification_date
15 FROM igi_cis_verify_lines_t
16 WHERE header_id = p_header_id;
17 --Bug 5606118
18 CURSOR c_req_id IS
19 SELECT request_id
20 FROM igi_cis_verify_headers_t
21 WHERE header_id = p_header_id;
22 --Bug 5606118
23 BEGIN
24 /* Updating the PO tables */
25 --Bug 5606118
26
27 FOR c_req_id_rec in c_req_id LOOP
28
29 l_request_id := c_req_id_rec.request_id;
30
31 END LOOP;
32
33 --Bug 5606118
34
35 FOR cur_igi_cis_verify_headers_rec IN cur_igi_cis_verify_headers
36 LOOP
37 l_match_flag := NULL;
38 l_awt_group_id := NULL;
39 /* Getting the AWT group id based on the tax treatment status */
40 IF (cur_igi_cis_verify_headers_rec.tax_treatment_status = 'net') THEN
41 l_awt_group_id := fnd_profile.VALUE('IGI_CIS2007_NET_WTH_GROUP');
42 ELSIF (cur_igi_cis_verify_headers_rec.tax_treatment_status = 'gross') THEN
43 l_awt_group_id := fnd_profile.VALUE('IGI_CIS2007_GROSS_WTH_GROUP');
44 ELSIF (cur_igi_cis_verify_headers_rec.tax_treatment_status =
45 'unmatched') THEN
46 l_awt_group_id := fnd_profile.VALUE('IGI_CIS2007_UNMATCHED_WTH_GROUP');
47 END IF;
48 IF (l_awt_group_id IS NOT NULL) THEN
49 /* AWT group id - Active / Inactive validation */
50 SELECT g.group_id
51 INTO l_awt_group_id
52 FROM ap_awt_groups g
53 WHERE g.group_id = l_awt_group_id
54 AND nvl(g.inactive_date, SYSDATE + 1) > SYSDATE;
55 SELECT lookup_code
56 INTO l_match_flag
57 FROM igi_lookups
58 WHERE upper(meaning) =
59 upper(cur_igi_cis_verify_headers_rec.match_status)
60 AND lookup_type = 'IGI_CIS2007_MATCH_STATUS';
61
62 /* calling to update certificates .. */
63 /* Bug 5705187 */
64 IGI_CIS2007_TAX_EFF_DATE.main (
65 p_vendor_id => cur_igi_cis_verify_headers_rec.vendor_id,
66 p_vendor_site_id => NULL,
67 p_tax_grp_id => l_awt_group_id,
68 p_pay_tax_grp_id => l_awt_group_id, /* Bug 7218825 */
69 p_source => 'VERIFY',
70 p_effective_date =>cur_igi_cis_verify_headers_rec.cis_verification_date
71 );
72
73 /* calling PO API to update PO tables - AP_SUPPLIERS, AP_SUPPLIER_SITES_ALL */
74 pr_po_api(cur_igi_cis_verify_headers_rec.vendor_id,
75 cur_igi_cis_verify_headers_rec.verification_number,
76 l_match_flag,
77 cur_igi_cis_verify_headers_rec.cis_verification_date,
78 l_awt_group_id,
79 NULL,
80 NULL,
81 NULL,
82 NULL,
83 l_request_id);
84 END IF;
85 END LOOP;
86 END pr_po_core_update;
87 /* API to UPDATE the HISTORY tables */
88 PROCEDURE pr_audit_update(p_header_id IN NUMBER) IS
89 BEGIN
90 /* Populating the history tables */
91 INSERT INTO igi_cis_verify_headers_h
92 (header_id,
93 request_id,
94 unique_tax_reference_num,
95 request_status_code,
96 accounts_office_reference,
97 program_id,
98 program_application_id,
99 program_login_id,
100 program_update_date,
101 last_update_date,
102 last_updated_by,
103 last_update_login,
104 creation_date,
105 created_by,
106 cis_sender_id,
107 tax_office_number,
108 paye_reference)
109 SELECT header_id,
110 request_id,
111 unique_tax_reference_num,
112 request_status_code,
113 accounts_office_reference,
114 program_id,
115 program_application_id,
116 program_login_id,
117 program_update_date,
118 last_update_date,
119 last_updated_by,
120 last_update_login,
121 creation_date,
122 created_by,
123 cis_sender_id,
124 tax_office_number,
125 paye_reference
126 FROM igi_cis_verify_headers_t
127 WHERE header_id = p_header_id;
128 INSERT INTO igi_cis_verify_lines_h
129 (header_id,
130 action_indicator,
131 vendor_type_lookup_code,
132 vendor_id,
133 vendor_name,
134 vendor_site_id,
135 vendor_site_code,
136 first_name,
137 second_name,
138 last_name,
139 salutation,
140 trading_name,
141 unique_tax_reference_num,
142 work_reference,
143 company_registration_number,
144 national_insurance_number,
145 verification_number,
146 cis_verification_date,
147 address_line1,
148 address_line2,
149 address_line3,
150 address_line4,
151 match_status,
152 post_code,
153 country,
154 contact_number,
155 tax_treatment_status,
156 last_update_date,
157 last_updated_by,
158 last_update_login,
159 creation_date,
160 created_by,
161 partnership_utr,
162 partnership_name)
163 SELECT header_id,
164 action_indicator,
165 vendor_type_lookup_code,
166 vendor_id,
167 vendor_name,
168 vendor_site_id,
169 vendor_site_code,
170 first_name,
171 second_name,
172 last_name,
173 salutation,
174 trading_name,
175 unique_tax_reference_num,
176 work_reference,
177 company_registration_number,
178 national_insurance_number,
179 verification_number,
180 cis_verification_date,
181 address_line1,
182 address_line2,
183 address_line3,
184 address_line4,
185 match_status,
186 post_code,
187 country,
188 contact_number,
189 tax_treatment_status,
190 last_update_date,
191 last_updated_by,
192 last_update_login,
193 creation_date,
194 created_by,
195 partnership_utr,
196 partnership_name
197 FROM igi_cis_verify_lines_t
198 WHERE header_id = p_header_id;
199 DELETE FROM igi_cis_verify_headers_t WHERE header_id = p_header_id;
200 DELETE FROM igi_cis_verify_lines_t WHERE header_id = p_header_id;
201 END pr_audit_update;
202 /* Function used by Verification UI */
203 FUNCTION igi_cis_is_vendor_paid
204 (
205 l_vendor_id NUMBER,
206 verify_date DATE DEFAULT SYSDATE
207 ) RETURN VARCHAR2 AS
208 tax_year_start DATE;
209 paid_status VARCHAR2(20);
210 BEGIN
211 IF to_date(to_char(verify_date, 'dd-mm') || '2005', 'dd-mm-yyyy') >
212 to_date('05-04-2005', 'dd-mm-yyyy') THEN
213 tax_year_start := to_date('06-04-' || to_char(verify_date, 'YYYY'),
214 'DD-MM-YYYY');
215 ELSE
216 tax_year_start := add_months(to_date('06-04-' ||
217 to_char(verify_date, 'YYYY'),
218 'DD-MM-YYYY'),
219 -12);
220 END IF;
221 SELECT 'PAID'
222 INTO paid_status
223 FROM ap_checks ac,
224 ap_invoice_payments pay,
225 -- Bug 5642198 Start
226 ap_invoices inv,
227 ap_supplier_sites pvs
228 -- Bug 5642198 End
229 WHERE ac.vendor_id = l_vendor_id
230 AND ac.void_date IS NULL
231 AND pay.check_id = ac.check_id
232 AND accounting_date > add_months(tax_year_start, -24)
233 -- Bug 5642198 Start
234 AND inv.invoice_id = pay.invoice_id
235 AND pvs.vendor_id = inv.vendor_id
236 AND pvs.vendor_site_id = inv.vendor_site_id
237 AND upper(pvs.allow_awt_flag) = 'Y'
238 -- Bug 5642198 End
239 AND rownum = 1;
240 RETURN 'PAID';
241 EXCEPTION
242 WHEN no_data_found THEN
243 RETURN 'NOTPAID';
244 END igi_cis_is_vendor_paid;
245 /* PO API to update AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL */
246 PROCEDURE pr_po_api
247 (
248 p_vendor_id IN NUMBER,
249 p_verification_no IN VARCHAR2,
250 p_match_status IN VARCHAR2,
251 p_verification_date DATE,
252 p_awt_group_id IN NUMBER,
253 p_utr_type IN VARCHAR2,
254 p_utr IN NUMBER,
255 p_sc_name IN VARCHAR2,
256 p_sc_ref_id IN VARCHAR2,
257 p_req_id IN NUMBER --Bug 5606118
258 ) IS
259
260 /* Bug#7218825 - CIS WITHHOLDING PROJECT DUE TO AP ENHANCEMENT 6639866 ON 12.1 */
261
262 l_org_id ap_supplier_sites_all.org_id%TYPE;
263 l_create_awt_dists_type ap_system_parameters_all.CREATE_AWT_DISTS_TYPE%TYPE;
264
265 Cursor c_povs_orgs(c_vendor_id NUMBER) is
266 select distinct org_id
267 from ap_supplier_sites_all
268 where vendor_id = c_vendor_id
269 and upper(allow_awt_flag) = 'Y';
270
271 Cursor c_awt_type(c_org_id NUMBER) is
272 select CREATE_AWT_DISTS_TYPE
273 from ap_system_parameters_all
274 where org_id = c_org_id
275 and upper(allow_awt_flag) = 'Y';
276
277 BEGIN
278 /* Updating the AWT Group id for the vendor */
279
280 UPDATE ap_suppliers pov
281 SET pov.verification_number = decode(p_verification_no,
282 NULL,
283 pov.verification_number,
284 p_verification_no),
285 pov.match_status_flag = p_match_status,
286 pov.cis_verification_date = p_verification_date,
287 pov.awt_group_id = p_awt_group_id,
288 pov.pay_awt_group_id = p_awt_group_id, /* Also Updating the PAY_AWT_GROUP_ID for Bug#7218825 */
289 pov.allow_awt_flag = 'Y',
290 pov.partnership_utr = decode(p_utr,
291 NULL,
292 pov.partnership_utr,
293 decode(p_utr_type,
294 'P',
295 p_utr,
296 decode(pov.vendor_type_lookup_code,
297 'PARTNERSHIP',
298 p_utr,
299 pov.partnership_utr))),
300 pov.verification_request_id = p_req_id, --Bug 5606118
301 pov.unique_tax_reference_num = decode(p_utr,
302 NULL,
303 pov.unique_tax_reference_num,
304 decode(p_utr_type,
305 'U',
306 p_utr,
307 decode(pov.vendor_type_lookup_code,
308 'PARTNERSHIP',
309 pov.unique_tax_reference_num,
310 p_utr))),
311 -- Start : Bug <5586655> Commented for change request
312 /* pov.trading_name = decode(p_sc_name,
313 NULL,
314 pov.trading_name,
315 decode(substr(p_sc_name,
316 1,
317 1),
318 '*',
319 pov.trading_name,
320 p_sc_name)),
321 pov.vendor_name = decode(p_sc_name,
322 NULL,
323 pov.vendor_name,
324 decode(substr(p_sc_name,
325 1,
326 1),
327 '*',
328 substr(p_sc_name,
329 2,
330 length(p_sc_name)),
331 pov.vendor_name)),
332 */
333 -- End : Bug <5586655>
334 pov.national_insurance_number = decode(p_sc_ref_id,
335 NULL,
336 pov.national_insurance_number,
337 decode(length(p_sc_ref_id),
338 9,
339 p_sc_ref_id,
340 pov.national_insurance_number)),
341 pov.company_registration_number = decode(p_sc_ref_id,
342 NULL,
343 pov.company_registration_number,
344 decode(length(p_sc_ref_id),
345 9,
346 pov.company_registration_number,
347 p_sc_ref_id))
348 WHERE pov.vendor_id = p_vendor_id;
349
350 /* Updating the debt factor vendors of the parent vendor */
351 UPDATE ap_suppliers pov
352 SET pov.awt_group_id = p_awt_group_id,
353 pov.pay_awt_group_id = p_awt_group_id, /* Also Updating the PAY_AWT_GROUP_ID for Bug#7218825 */
354 pov.allow_awt_flag = 'Y'
355 WHERE pov.cis_parent_vendor_id = p_vendor_id
356 AND pov.vendor_type_lookup_code NOT IN
357 ('SOLETRADER', 'PARTNERSHIP', 'TRUST', 'COMPANY');
358
359 /* Bug#7218825 - CIS WITHHOLDING PROJECT DUE TO AP ENHANCEMENT 6639866 ON 12.1 */
360
361 open c_povs_orgs(p_vendor_id);
362 loop
363
364 fetch c_povs_orgs into l_org_id;
365 exit when c_povs_orgs%NOTFOUND;
366
367 open c_awt_type(l_org_id);
368 loop
369
370 fetch c_awt_type into l_create_awt_dists_type;
371 exit when c_awt_type%NOTFOUND;
372
373 If l_create_awt_dists_type = 'APPROVAL'
374 then
375
376 /* Updating all the vendor sites of the vendor */
377
378 UPDATE ap_supplier_sites_all povs
379 SET povs.awt_group_id = p_awt_group_id, povs.allow_awt_flag = 'Y'
380 WHERE povs.vendor_id = p_vendor_id
381 AND povs.org_id = l_org_id
382 AND povs.allow_awt_flag = 'Y'; --Bug 5642191
383
384 /* Updating all the vendor sites of the debt factor vendors */
385
386 UPDATE ap_supplier_sites_all povs
387 SET povs.awt_group_id = p_awt_group_id, povs.allow_awt_flag = 'Y'
388 WHERE povs.vendor_id IN
389 (SELECT pov.vendor_id
390 FROM ap_suppliers pov
391 WHERE pov.cis_parent_vendor_id = p_vendor_id
392 AND pov.vendor_type_lookup_code NOT IN
393 ('SOLETRADER', 'PARTNERSHIP', 'TRUST', 'COMPANY'))
394 AND povs.org_id = l_org_id
395 AND povs.allow_awt_flag='Y'; --Bug 5642191
396
397 elsif l_create_awt_dists_type = 'PAYMENT'
398 then
399
400 /* Updating all the vendor sites of the vendor */
401
402 UPDATE ap_supplier_sites_all povs
403 SET povs.pay_awt_group_id = p_awt_group_id, povs.allow_awt_flag = 'Y'
404 WHERE povs.vendor_id = p_vendor_id
405 AND povs.org_id = l_org_id
406 AND povs.allow_awt_flag = 'Y'; --Bug 5642191
407
408 /* Updating all the vendor sites of the debt factor vendors */
409
410 UPDATE ap_supplier_sites_all povs
411 SET povs.pay_awt_group_id = p_awt_group_id, povs.allow_awt_flag = 'Y'
412 WHERE povs.vendor_id IN
413 (SELECT pov.vendor_id
414 FROM ap_suppliers pov
415 WHERE pov.cis_parent_vendor_id = p_vendor_id
416 AND pov.vendor_type_lookup_code NOT IN
417 ('SOLETRADER', 'PARTNERSHIP', 'TRUST', 'COMPANY'))
418 AND povs.org_id = l_org_id
419 AND povs.allow_awt_flag='Y'; --Bug 5642191
420
421 elsif l_create_awt_dists_type = 'BOTH'
422 then
423
424 /* Updating all the vendor sites of the vendor */
425
426 UPDATE ap_supplier_sites_all povs
427 SET povs.awt_group_id = p_awt_group_id,
428 povs.pay_awt_group_id = p_awt_group_id,
429 povs.allow_awt_flag = 'Y'
430 WHERE povs.vendor_id = p_vendor_id
431 AND povs.org_id = l_org_id
432 AND povs.allow_awt_flag = 'Y'; --Bug 5642191
433
434 /* Updating all the vendor sites of the debt factor vendors */
435
436 UPDATE ap_supplier_sites_all povs
437 SET povs.awt_group_id = p_awt_group_id,
438 povs.pay_awt_group_id = p_awt_group_id,
439 povs.allow_awt_flag = 'Y'
440 WHERE povs.vendor_id IN
441 (SELECT pov.vendor_id
442 FROM ap_suppliers pov
443 WHERE pov.cis_parent_vendor_id = p_vendor_id
444 AND pov.vendor_type_lookup_code NOT IN
445 ('SOLETRADER', 'PARTNERSHIP', 'TRUST', 'COMPANY'))
446 AND povs.org_id = l_org_id
447 AND povs.allow_awt_flag='Y'; --Bug 5642191
448
449 end if;
450
451 end loop; /* Cursor c_awt_type */
452
453 close c_awt_type;
454
455 end loop; /* Cursor c_povs_orgs */
456
457 close c_povs_orgs;
458
459 /* Bug#7218825 - CIS WITHHOLDING PROJECT DUE TO AP ENHANCEMENT 6639866 ON 12.1 */
460
461 END pr_po_api;
462 END igi_cis2007_igipverp_pkg;