1 PACKAGE BODY igi_cis2007_tax_eff_date AS
2 -- $Header: igiefdtb.pls 120.1.12010000.2 2008/12/19 12:58:32 gaprasad ship $
3 PROCEDURE main(p_vendor_id IN ap_suppliers.vendor_id%TYPE
4 , p_vendor_site_id IN ap_supplier_sites_all.vendor_site_id%TYPE
5 , p_tax_grp_id IN ap_awt_group_taxes_all.group_id%TYPE
6 , p_pay_tax_grp_id IN ap_awt_group_taxes_all.group_id%TYPE /* Bug 7218825 */
7 , p_source IN VARCHAR2
8 , p_effective_date IN DATE) IS
9
10 -- c_vendor_sites is the cursor which is used
11 -- to get all the vendor site of a vendor
12 -- when the vendor is null it fetches all the vendor's sites
13 -- check for the vendor to be cis_enabled = 'y'
14 -- and the vendor_site has allow_awt_flag = 'y'
15 CURSOR c_vendor_sites(p_vendor_id ap_suppliers.vendor_id%TYPE
16 , p_vendor_site_id ap_supplier_sites_all.vendor_site_id%TYPE) IS
17 SELECT pov.vendor_id,
18 povs.vendor_site_id,
19 povs.org_id
20 FROM ap_supplier_sites_all povs,
21 ap_suppliers pov
22 WHERE pov.vendor_id = p_vendor_id
23 AND pov.vendor_id = povs.vendor_id
24 AND povs.vendor_site_id = nvl(p_vendor_site_id, povs.vendor_site_id)
25 AND pov.cis_enabled_flag = 'Y'
26 AND pov.allow_awt_flag = 'Y'
27 AND povs.allow_awt_flag = 'Y'
28 AND org_id IS NOT NULL;
29
30 -- c_new_tax_rate will be used to get the new rate for the tax_group_id and org
31 CURSOR c_new_tax_rate(p_tax_group_id ap_awt_group_taxes.group_id%TYPE
32 , p_org_id ap_awt_group_taxes.org_id%TYPE) IS
33 SELECT atr.tax_rate new_tax_rate
34 FROM ap_tax_codes_all atc,
35 ap_awt_group_taxes_all agt,
36 ap_awt_tax_rates_all atr
37 WHERE agt.group_id = p_tax_group_id
38 AND agt.tax_name = atc.name
39 AND atc.name = atr.tax_name
40 AND atc.tax_type = 'AWT'
41 AND atr.rate_type = 'STANDARD'
42 AND(sysdate BETWEEN nvl(atr.start_date, sysdate -1)
43 AND nvl(atr.end_date, sysdate + 1))
44 AND atc.org_id = agt.org_id
45 AND atr.org_id = agt.org_id
46 AND atr.org_id = p_org_id
47 ORDER BY agt.group_id,
48 atr.tax_rate;
49
50 CURSOR c_tax_names(p_vendor_id ap_suppliers.vendor_id%TYPE
51 , p_vendor_site_id ap_supplier_sites_all.vendor_site_id%TYPE
52 , p_org_id ap_supplier_sites_all.org_id%TYPE) IS
53 SELECT DISTINCT tax_name
54 FROM ap_awt_tax_rates_all
55 WHERE vendor_id = p_vendor_id
56 AND vendor_site_id = p_vendor_site_id
57 AND org_id = p_org_id
58 AND priority = 1;
59
60 --Cursor Variables
61 lcr_vendor_site c_vendor_sites % rowtype;
62 lcr_tax_name c_tax_names % rowtype;
63
64 --Local Variables
65 l_new_tax_rate ap_awt_tax_rates_all.tax_rate%TYPE;
66 l_new_tax_rate_id ap_awt_tax_rates.tax_rate_id%TYPE;
67 l_old_tax_grp_name ap_awt_groups.name%TYPE;
68 l_new_tax_grp_name ap_awt_groups.name%TYPE;
69 l_old_tax_name ap_tax_codes_all.name%TYPE;
70 l_update_flag VARCHAR2(2);
71 l_tax_name_exists_flag boolean;
72 l_start_date DATE;
73 l_update_date DATE;
74 l_old_tax_grp_id ap_awt_group_taxes_all.group_id%TYPE;
75 l_site_old_tax_grp_id ap_awt_group_taxes_all.group_id%TYPE;
76
77 l_tax_grp_id ap_awt_group_taxes_all.group_id%TYPE; /* Bug 7218825 */
78
79 BEGIN
80
81 l_tax_grp_id := IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(p_vendor_id,p_vendor_site_id,p_tax_grp_id,p_pay_tax_grp_id); /* Bug 7218825 */
82
83 --Check for repeated call, if exists return
84 IF(g_old_p_vendor_id = p_vendor_id
85 AND g_old_p_vendor_site_id = p_vendor_site_id
86 AND g_old_p_tax_grp_id = l_tax_grp_id /* Bug 7218825 */
87 AND g_old_p_source = p_source
88 AND g_old_p_effective_date = p_effective_date) THEN
89 RETURN;
90 END IF;
91
92 g_old_p_vendor_id := p_vendor_id;
93 g_old_p_vendor_site_id := p_vendor_site_id;
94 g_old_p_tax_grp_id := l_tax_grp_id; /* Bug 7218825 */
95 g_old_p_source := p_source;
96 g_old_p_effective_date := p_effective_date;
97
98 --Loop for the vendor sites
99 FOR lcr_vendor_site IN c_vendor_sites(p_vendor_id, p_vendor_site_id)
100 LOOP --Start of Vendor Sites Loop
101 BEGIN
102
103 l_tax_grp_id := IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(lcr_vendor_site.vendor_id,lcr_vendor_site.vendor_site_id,p_tax_grp_id,p_pay_tax_grp_id); /* Bug 7218825 */
104
105 --Get the new tax rate id for the vendor and vendor site
106 OPEN c_new_tax_rate(l_tax_grp_id, lcr_vendor_site.org_id); /* Bug 7218825 */
107 FETCH c_new_tax_rate
108 INTO l_new_tax_rate;
109 CLOSE c_new_tax_rate;
110
111 BEGIN
112
113 --Fetch the old tax group id for the vendor or vendor site
114 SELECT decode(p_source, 'VENDOR SITE FORM', IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(po_site.vendor_id,po_site.vendor_site_id, po_site.awt_group_id, po_site.pay_awt_group_id)
115 , 'VENDOR FORM', IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(po.vendor_id,NULL,po.awt_group_id,po.pay_awt_group_id)
116 , 'CDROM', IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(po.vendor_id,NULL,po.awt_group_id,po.pay_awt_group_id)
117 , 'VERIFY', IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(po.vendor_id,NULL,po.awt_group_id,po.pay_awt_group_id))
118 INTO l_old_tax_grp_id
119 FROM ap_suppliers po,
120 ap_supplier_sites_all po_site
121 WHERE po.vendor_id = p_vendor_id
122 AND po_site.vendor_id = po.vendor_id
123 AND po_site.allow_awt_flag = 'Y'
124 AND po_site.vendor_site_id = nvl(p_vendor_site_id, lcr_vendor_site.vendor_site_id)
125 AND org_id = lcr_vendor_site.org_id;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 NULL;
130 END;
131
132 --Fetch the old tax group id for the vendor site
133 BEGIN
134 SELECT IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(po_site.vendor_id,po_site.vendor_site_id, po_site.awt_group_id, po_site.pay_awt_group_id)
135 INTO l_site_old_tax_grp_id
136 FROM ap_suppliers po,
137 ap_supplier_sites_all po_site
138 WHERE po.vendor_id = p_vendor_id
139 AND po_site.vendor_id = po.vendor_id
140 AND po_site.allow_awt_flag = 'Y'
141 AND po_site.vendor_site_id = nvl(p_vendor_site_id, lcr_vendor_site.vendor_site_id)
142 AND org_id = lcr_vendor_site.org_id;
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 NULL;
147 END;
148
149 --Fetch the old tax name
150 BEGIN
151 SELECT atc.name tax_name
152 INTO l_old_tax_name
153 FROM ap_awt_group_taxes_all agt,
154 ap_tax_codes_all atc
155 WHERE agt.group_id = nvl(l_site_old_tax_grp_id, l_old_tax_grp_id)
156 AND atc.tax_type = 'AWT'
157 AND agt.tax_name = atc.name
158 AND sysdate <= nvl(atc.inactive_date, sysdate + 1)
159 AND atc.org_id = agt.org_id
160 AND atc.org_id = lcr_vendor_site.org_id;
161
162 EXCEPTION
163 WHEN others THEN
164 l_old_tax_name := NULL;
165 END;
166
167 -- Check whether the old tax group id at site level is same as the new
168 -- tax group id for source as CDROM or VERIFY
169 -- If they are same, set the update flag to FALSE. Otherwise, set the
170 -- update flag to TRUE
171 IF(p_source = 'CDROM' OR p_source = 'VERIFY') --Start of UPDATE If
172 AND(l_tax_grp_id IS NOT NULL) /* Bug 7218825 */
173 AND(l_site_old_tax_grp_id IS NOT NULL)
174 AND(l_site_old_tax_grp_id = l_tax_grp_id) THEN /* Bug 7218825 */
175 l_update_flag := 'F';
176 ELSE
177 l_update_flag := 'T';
178 END IF;
179
180 -- When l_old_tax_name is Null, then there is an issue with the setup
181 -- Tax Code exists, but tax name is not defined for that particular org
182 -- In such a case, set l_update_flag to False
183 IF l_old_tax_name is NULL and l_update_flag = 'T' THEN
184 l_update_flag := 'F';
185 END IF;
186
187 -- Call from AP package happens every time Vendor or Vendor Site information
188 -- is updated. The following code ensures that insertion happens only
189 -- when the Withholding tax is changed and not otherwise
190 IF p_source = 'VENDOR SITE FORM'
191 AND l_site_old_tax_grp_id IS NOT NULL
192 AND l_site_old_tax_grp_id = l_tax_grp_id /* Bug 7218825 */
193 AND l_update_flag = 'T' THEN
194 l_update_flag := 'F';
195 END IF;
196
197 -- Call from AP package happens every time Vendor or Vendor Site information
198 -- is updated. The following code ensures that insertion happens only
199 -- when the Withholding tax is changed and not otherwise
200 IF p_source = 'VENDOR FORM'
201 AND l_old_tax_grp_id IS NOT NULL
202 AND l_old_tax_grp_id = l_tax_grp_id /* Bug 7218825 */
203 AND l_update_flag = 'T' THEN
204 l_update_flag := 'F';
205 END IF;
206
207 IF(l_new_tax_rate IS NOT NULL
208 AND(l_site_old_tax_grp_id IS NOT NULL OR(l_site_old_tax_grp_id IS NULL AND p_source <> 'VENDOR SITE FORM'))
209 AND l_update_flag = 'T') THEN
210
211 --Initialize the l_tax_name_exists_flag
212 l_tax_name_exists_flag := FALSE;
213
214 --For each tax names in ap_awt_tax_rates_all do the following
215 --1. Increment the priority of all records wit the current the tax name
216 --2. End date the tax name based on the following rules
217 -- A. If the start date is greater than p_effective_date then set the
218 -- end date of the record as the start date
219 -- B. If the start date is lesser or equal to p_effective_date then set
220 -- the end date of the record as p_effective_date
221 FOR lcr_tax_names IN c_tax_names(lcr_vendor_site.vendor_id
222 , lcr_vendor_site.vendor_site_id
223 , lcr_vendor_site.org_id)
224 LOOP -- Start of c_tax_names loop
225 BEGIN
226 --Increment the priority of all records with the current tax name
227 UPDATE ap_awt_tax_rates_all
228 SET priority = priority + 1
229 WHERE vendor_id = lcr_vendor_site.vendor_id
230 AND vendor_site_id = lcr_vendor_site.vendor_site_id
231 AND org_id = lcr_vendor_site.org_id
232 AND tax_name = lcr_tax_names.tax_name;
233
234 --Fetch the start date of the record which has the current tax name and
235 --is of priority 2
236 BEGIN
237 SELECT start_date
238 INTO l_start_date
239 FROM ap_awt_tax_rates_all
240 WHERE vendor_id = lcr_vendor_site.vendor_id
241 AND vendor_site_id = lcr_vendor_site.vendor_site_id
242 AND org_id = lcr_vendor_site.org_id
243 AND tax_name = lcr_tax_names.tax_name
244 AND priority = 2;
245
246 EXCEPTION
247 WHEN others THEN
248 l_start_date := p_effective_date;
249 END;
250
251 --End date the tax name based on the following rules
252 -- A. If the start date is greater than p_effective_date then set
253 -- l_update_date as l_start_date
254 -- B. If the start date is lesser or equal to p_effective_date then
255 -- set l_update_date as p_effective_date
256 IF l_start_date > p_effective_date THEN
257 l_update_date := l_start_date;
258 ELSE
259 l_update_date := p_effective_date;
260 END IF;
261
262 --End date the record which has the current tax name and is of
263 --priority 2
264 UPDATE ap_awt_tax_rates_all
265 SET end_date = l_update_date
266 WHERE vendor_id = lcr_vendor_site.vendor_id
267 AND vendor_site_id = lcr_vendor_site.vendor_site_id
268 AND org_id = lcr_vendor_site.org_id
269 AND tax_name = lcr_tax_names.tax_name
270 AND priority = 2;
271
272 --Fetch a new tax rate id from the sequence
273 SELECT ap_awt_tax_rates_s.nextval
274 INTO l_new_tax_rate_id
275 FROM dual;
276
277 --If a record corrosponding to the old tax name already exists then
278 --set the l_tax_name_exists_flag flag to true
279 IF l_old_tax_name = lcr_tax_names.tax_name THEN
280 l_tax_name_exists_flag := TRUE;
281 END IF;
282
283 --Insert a new record for the current tax name with priority 1
284 --Records of priority 1 will have end date as NULL
285 INSERT
286 INTO ap_awt_tax_rates_all(tax_rate_id
287 , tax_name
288 , tax_rate
289 , rate_type
290 , start_date
291 , vendor_id
292 , vendor_site_id
293 , certificate_number
294 , certificate_type
295 , comments
296 , priority
297 , org_id
298 , last_update_date
299 , last_updated_by
300 , last_update_login
301 , creation_date
302 , created_by)
303 VALUES(l_new_tax_rate_id --tax_rate_id
304 , lcr_tax_names.tax_name --tax_name
305 , l_new_tax_rate --tax_rate
306 , 'CERTIFICATE' --rate_type
307 , TRUNC(p_effective_date) --start_date
308 , lcr_vendor_site.vendor_id --vendor_id
309 , lcr_vendor_site.vendor_site_id --vendor_site_id
310 , 'CERT' --certificate_number
311 , 'STANDARD' --certificate_type
312 , initcap(p_source || ' - Tax Treatment Change') --comments
313 , 1 --priority
314 , lcr_vendor_site.org_id --org_id
315 , sysdate --last_update_date
316 , nvl(fnd_profile.VALUE('USER_ID'), 0) --last_update_by
317 , nvl(fnd_profile.VALUE('LAST_UPDATE_LOGIN'), 0) --last_update_login
318 , sysdate --creation_date
319 , nvl(fnd_profile.VALUE('USER_ID'), 0) --created_by
320 );
321
322 --Insert a corrosponding row in the history table
323 INSERT
324 INTO igi_cis_tax_treatment_h(vendor_id
325 , vendor_site_id
326 , tax_rate_id
327 , old_group_id
328 , new_group_id
329 , effective_date
330 , source_name
331 , last_update_date
332 , last_updated_by
333 , last_update_login
334 , creation_date
335 , created_by
336 , request_id
337 , program_id
338 , program_application_id
339 , program_login_id)
340 VALUES(lcr_vendor_site.vendor_id --vendor_id
341 , lcr_vendor_site.vendor_site_id --vendor_site_id
342 , l_new_tax_rate_id --tax_rate_id
343 , nvl(l_site_old_tax_grp_id, l_old_tax_grp_id) --old_group_id
344 , l_tax_grp_id --new_group_id /* Bug 7218825 */
345 , p_effective_date --effective_date
346 , p_source --source_name
347 , sysdate --last_update_date
348 , nvl(fnd_profile.VALUE('USER_ID'), 0) --last_update_by
349 , nvl(fnd_profile.VALUE('LAST_UPDATE_LOGIN'), 0) --last_update_login
350 , sysdate --creation_date
351 , nvl(fnd_profile.VALUE('USER_ID'), 0) --created_by
352 , fnd_global.conc_request_id --request_id
353 , fnd_global.conc_program_id --program_id
354 , fnd_global.prog_appl_id --program_application_id
355 , fnd_global.conc_login_id); --program_login_id
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 NULL;
360 END;
361 END LOOP; -- End of c_tax_names loop
362
363 --A record for l_old_tax_name does not exists. Insert this record
364 IF l_tax_name_exists_flag = FALSE THEN --Start of l_tax_name_exists_flag IF
365
366 SELECT ap_awt_tax_rates_s.nextval
367 INTO l_new_tax_rate_id
368 FROM dual;
369
370 --Insert a new record for the l_old_tax_name with priority 1
371 --Records of priority 1 will have end date as NULL
372 INSERT
373 INTO ap_awt_tax_rates_all(tax_rate_id
374 , tax_name
375 , tax_rate
376 , rate_type
377 , start_date
378 , vendor_id
379 , vendor_site_id
380 , certificate_number
381 , certificate_type
382 , comments
383 , priority
384 , org_id
385 , last_update_date
386 , last_updated_by
387 , last_update_login
388 , creation_date
389 , created_by)
390 VALUES(l_new_tax_rate_id --tax_rate_id
391 , l_old_tax_name --tax_name
392 , l_new_tax_rate --tax_rate
393 , 'CERTIFICATE' --rate_type
394 , TRUNC(p_effective_date) --start_date
395 , lcr_vendor_site.vendor_id --vendor_id
396 , lcr_vendor_site.vendor_site_id --vendor_site_id
397 , 'CERT' --certificate_number
398 , 'STANDARD' --certificate_type
399 , initcap(p_source || ' - Tax Treatment Change') --comments
400 , 1 --priority
401 , lcr_vendor_site.org_id --org_id
402 , sysdate --last_update_date
403 , nvl(fnd_profile.VALUE('USER_ID'), 0) --last_update_by
404 , nvl(fnd_profile.VALUE('LAST_UPDATE_LOGIN'), 0) --last_update_login
405 , sysdate --creation_date
406 , nvl(fnd_profile.VALUE('USER_ID'), 0) --created_by
407 );
408
409 --Insert a corrosponding row in the history table
410 INSERT
411 INTO igi_cis_tax_treatment_h(vendor_id
412 , vendor_site_id
413 , tax_rate_id
414 , old_group_id
415 , new_group_id
416 , effective_date
417 , source_name
418 , last_update_date
419 , last_updated_by
420 , last_update_login
421 , creation_date
422 , created_by
423 , request_id
424 , program_id
425 , program_application_id
426 , program_login_id)
427 VALUES(lcr_vendor_site.vendor_id --vendor_id
428 , lcr_vendor_site.vendor_site_id --vendor_site_id
429 , l_new_tax_rate_id --tax_rate_id
430 , nvl(l_site_old_tax_grp_id, l_old_tax_grp_id) --old_group_id
431 , l_tax_grp_id --new_group_id /* Bug 7218825 */
432 , p_effective_date --effective_date
433 , p_source --source_name
434 , sysdate --last_update_date
435 , nvl(fnd_profile.VALUE('USER_ID'), 0) --last_update_by
436 , nvl(fnd_profile.VALUE('LAST_UPDATE_LOGIN'), 0) --last_update_login
437 , sysdate --creation_date
438 , nvl(fnd_profile.VALUE('USER_ID'), 0) --created_by
439 , fnd_global.conc_request_id --request_id
440 , fnd_global.conc_program_id --program_id
441 , fnd_global.prog_appl_id --program_application_id
442 , fnd_global.conc_login_id); --program_login_id
443 END IF; --END of l_tax_name_exists_flag IF
444
445 END IF; --Start of UPDATE If
446 EXCEPTION
447 WHEN OTHERS THEN
448 NULL;
449 END;
450 END LOOP; --End of Vendor Sites Loop
451
452 EXCEPTION
453 WHEN others THEN
454 NULL;
455 END main;
456
457 PROCEDURE set_eff_date(p_eff_date DATE) IS
458 BEGIN
459 global_eff_date := p_eff_date;
460 END;
461
462 FUNCTION get_eff_date RETURN DATE IS
463 BEGIN
464 RETURN global_eff_date;
465 END;
466
467 END igi_cis2007_tax_eff_date;
468
[Home] [Help]