[Home] [Help]
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'
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;
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;
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);
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
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
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
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
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
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
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