[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ap_awt_tax_rates_s.NEXTVAL FROM SYS.DUAL;
/*cursor to select awt data for updation*/
CURSOR c_certificate IS
SELECT rowid ,
tax_rate_id ,
tax_name,
tax_rate,
rate_type,
start_date,
end_date,
start_amount,
end_amount,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
vendor_id,
vendor_site_id,
invoice_num,
certificate_number,
certificate_type,
comments,
priority,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id
FROM ap_awt_tax_rates
WHERE tax_name = IGI_CIS_GET_PROFILE.Cis_Tax_Code
AND certificate_type = p_current_certificate_type ;
SELECT tax_rate_id , ni_number
FROM igi_cis_cert_ni_numbers
WHERE TAX_RATE_ID = c_tax_rate_id ;
SELECT 'X' from ap_awt_tax_rates
WHERE tax_name = IGI_CIS_GET_PROFILE.Cis_Tax_Code
AND ((certificate_type = decode(p_mode , 'Percentages' , p_current_certificate_type , p_new_certificate_type)) OR (certificate_type = p_current_certificate_type))
AND vendor_id = c_vendor_id
AND tax_rate_id <> c_tax_rate_id
AND ( ( start_date <= c_start_date AND end_date >= c_end_date) OR
(start_date >= c_start_date AND end_date <= c_end_date) OR
(c_start_date BETWEEN start_date AND end_date) OR
(c_end_date BETWEEN start_date AND end_date) ) ;
SELECT vendor_name FROM po_vendors
WHERE vendor_id = c_vendor_id ;
SELECT vendor_site_code FROM po_vendor_sites
WHERE vendor_site_id = c_vendor_site_id
AND org_id = c_org_id
AND vendor_id = c_vendor_id;
/*Loop for all the values selected*/
FOR l_certificate IN c_certificate
LOOP ------------------------------------------ For Loop----- Starts-
BEGIN
IF ( ( (l_certificate.end_date >=p_effective_date ) OR (l_certificate.end_date IS NULL )) AND l_certificate.start_date <= p_effective_date) THEN
validate_dates (l_certificate.start_date
,l_certificate.end_date
,p_effective_date
,l_curr_start_date
,l_curr_end_date
,l_new_start_date
,l_new_end_date
,l_special_case);
/* Update /Insert in case the date does not fall under the special case> */
IF NOT (l_special_case) THEN ------------------------------------------------------------(1)
OPEN c3 (l_certificate.vendor_id);
/* To update old record and insert in case of breaking an existing period */
IF ( ((l_certificate.end_date >= p_effective_date) OR (l_certificate.end_date IS NULL )) and l_certificate.start_date < p_effective_date) THEN ---------(3)
Generate_tax_rate_id(l_tax_rate_id);
UPDATE ap_awt_tax_rates
SET end_date = l_curr_end_date
WHERE rowid = l_certificate.rowid;
Fnd_message.set_name('IGI','IGI_CIS_UPDATED_CERT');
INSERT INTO ap_awt_tax_rates
(tax_rate_id,
tax_name,
tax_rate,
rate_type,
start_date,
end_date,
start_amount,
end_amount,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
vendor_id,
vendor_site_id,
invoice_num,
certificate_number,
certificate_type,
comments,
priority,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id)
VALUES
(l_tax_rate_id,
l_certificate.tax_name,
l_tax_rate,
l_certificate.rate_type,
l_new_start_date,
l_new_end_date,
l_certificate.start_amount,
l_certificate.end_amount,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
sysdate ,
fnd_global.user_id,
l_certificate.vendor_id,
l_certificate.vendor_site_id,
l_certificate.invoice_num,
l_certificate.certificate_number,
l_certificate_type,
l_certificate.comments,
l_certificate.priority,
l_certificate.attribute_category,
l_certificate.attribute1,
l_certificate.attribute2,
l_certificate.attribute3,
l_certificate.attribute4,
l_certificate.attribute5,
l_certificate.attribute6,
l_certificate.attribute7,
l_certificate.attribute8,
l_certificate.attribute9,
l_certificate.attribute10,
l_certificate.attribute11,
l_certificate.attribute12,
l_certificate.attribute13,
l_certificate.attribute14,
l_certificate.attribute15,
l_certificate.org_id);
Fnd_message.set_name('IGI','IGI_CIS_UPDATED_CERT');
SELECT rowid into l_row_id FROM ap_awt_tax_rates WHERE tax_rate_id=l_tax_rate_id ;
l_call_seq := 'CIS :Update Certificate Percentages' ;
igi_cis_cert_ni_numbers_pkg.insert_row
(l_row_id
,l_org_id /* Bug 3085887 11.5.10 MOAC change */
,l_tax_rate_id
,l1.ni_number
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,l_call_seq );
/* End of insert into ap_awt_tax_rates and igi_cis_ni_numbers_all*/
/* If the effective date does not break a period then update the certificate for future active certificates*/
IF l_certificate.start_date >= p_effective_date THEN ------------------------(4)
UPDATE ap_awt_tax_rates
SET tax_rate = l_tax_rate,
Certificate_type= l_certificate_type
WHERE rowid= l_certificate.rowid;
Fnd_message.set_name('IGI','IGI_CIS_UPDATED_CERT');
/* End of Update*/
ELSE /* Overlapping Certificates found*/ --------------------------------------------------------(2)
close c2;