[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pov.vendor_id,
povs.vendor_site_id,
povs.org_id
FROM ap_supplier_sites_all povs,
ap_suppliers pov
WHERE pov.vendor_id = p_vendor_id
AND pov.vendor_id = povs.vendor_id
AND povs.vendor_site_id = nvl(p_vendor_site_id, povs.vendor_site_id)
AND pov.cis_enabled_flag = 'Y'
AND pov.allow_awt_flag = 'Y'
AND povs.allow_awt_flag = 'Y'
AND org_id IS NOT NULL;
SELECT atr.tax_rate new_tax_rate
FROM ap_tax_codes_all atc,
ap_awt_group_taxes_all agt,
ap_awt_tax_rates_all atr
WHERE agt.group_id = p_tax_group_id
AND agt.tax_name = atc.name
AND atc.name = atr.tax_name
AND atc.tax_type = 'AWT'
AND atr.rate_type = 'STANDARD'
AND(sysdate BETWEEN nvl(atr.start_date, sysdate -1)
AND nvl(atr.end_date, sysdate + 1))
AND atc.org_id = agt.org_id
AND atr.org_id = agt.org_id
AND atr.org_id = p_org_id
ORDER BY agt.group_id,
atr.tax_rate;
SELECT DISTINCT tax_name
FROM ap_awt_tax_rates_all
WHERE vendor_id = p_vendor_id
AND vendor_site_id = p_vendor_site_id
AND org_id = p_org_id
AND priority = 1;
l_update_flag VARCHAR2(2);
l_update_date DATE;
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)
, 'VENDOR FORM', IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(po.vendor_id,NULL,po.awt_group_id,po.pay_awt_group_id)
, 'CDROM', IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(po.vendor_id,NULL,po.awt_group_id,po.pay_awt_group_id)
, 'VERIFY', IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(po.vendor_id,NULL,po.awt_group_id,po.pay_awt_group_id))
INTO l_old_tax_grp_id
FROM ap_suppliers po,
ap_supplier_sites_all po_site
WHERE po.vendor_id = p_vendor_id
AND po_site.vendor_id = po.vendor_id
AND po_site.allow_awt_flag = 'Y'
AND po_site.vendor_site_id = nvl(p_vendor_site_id, lcr_vendor_site.vendor_site_id)
AND org_id = lcr_vendor_site.org_id;
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)
INTO l_site_old_tax_grp_id
FROM ap_suppliers po,
ap_supplier_sites_all po_site
WHERE po.vendor_id = p_vendor_id
AND po_site.vendor_id = po.vendor_id
AND po_site.allow_awt_flag = 'Y'
AND po_site.vendor_site_id = nvl(p_vendor_site_id, lcr_vendor_site.vendor_site_id)
AND org_id = lcr_vendor_site.org_id;
SELECT atc.name tax_name
INTO l_old_tax_name
FROM ap_awt_group_taxes_all agt,
ap_tax_codes_all atc
WHERE agt.group_id = nvl(l_site_old_tax_grp_id, l_old_tax_grp_id)
AND atc.tax_type = 'AWT'
AND agt.tax_name = atc.name
AND sysdate <= nvl(atc.inactive_date, sysdate + 1)
AND atc.org_id = agt.org_id
AND atc.org_id = lcr_vendor_site.org_id;
IF(p_source = 'CDROM' OR p_source = 'VERIFY') --Start of UPDATE If
AND(l_tax_grp_id IS NOT NULL) /* Bug 7218825 */
AND(l_site_old_tax_grp_id IS NOT NULL)
AND(l_site_old_tax_grp_id = l_tax_grp_id) THEN /* Bug 7218825 */
l_update_flag := 'F';
l_update_flag := 'T';
IF l_old_tax_name is NULL and l_update_flag = 'T' THEN
l_update_flag := 'F';
AND l_update_flag = 'T' THEN
l_update_flag := 'F';
AND l_update_flag = 'T' THEN
l_update_flag := 'F';
AND l_update_flag = 'T') THEN
--Initialize the l_tax_name_exists_flag
l_tax_name_exists_flag := FALSE;
UPDATE ap_awt_tax_rates_all
SET priority = priority + 1
WHERE vendor_id = lcr_vendor_site.vendor_id
AND vendor_site_id = lcr_vendor_site.vendor_site_id
AND org_id = lcr_vendor_site.org_id
AND tax_name = lcr_tax_names.tax_name;
SELECT start_date
INTO l_start_date
FROM ap_awt_tax_rates_all
WHERE vendor_id = lcr_vendor_site.vendor_id
AND vendor_site_id = lcr_vendor_site.vendor_site_id
AND org_id = lcr_vendor_site.org_id
AND tax_name = lcr_tax_names.tax_name
AND priority = 2;
l_update_date := l_start_date;
l_update_date := p_effective_date;
UPDATE ap_awt_tax_rates_all
SET end_date = l_update_date
WHERE vendor_id = lcr_vendor_site.vendor_id
AND vendor_site_id = lcr_vendor_site.vendor_site_id
AND org_id = lcr_vendor_site.org_id
AND tax_name = lcr_tax_names.tax_name
AND priority = 2;
SELECT ap_awt_tax_rates_s.nextval
INTO l_new_tax_rate_id
FROM dual;
INSERT
INTO ap_awt_tax_rates_all(tax_rate_id
, tax_name
, tax_rate
, rate_type
, start_date
, vendor_id
, vendor_site_id
, certificate_number
, certificate_type
, comments
, priority
, org_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by)
VALUES(l_new_tax_rate_id --tax_rate_id
, lcr_tax_names.tax_name --tax_name
, l_new_tax_rate --tax_rate
, 'CERTIFICATE' --rate_type
, TRUNC(p_effective_date) --start_date
, lcr_vendor_site.vendor_id --vendor_id
, lcr_vendor_site.vendor_site_id --vendor_site_id
, 'CERT' --certificate_number
, 'STANDARD' --certificate_type
, initcap(p_source || ' - Tax Treatment Change') --comments
, 1 --priority
, lcr_vendor_site.org_id --org_id
, sysdate --last_update_date
, nvl(fnd_profile.VALUE('USER_ID'), 0) --last_update_by
, nvl(fnd_profile.VALUE('LAST_UPDATE_LOGIN'), 0) --last_update_login
, sysdate --creation_date
, nvl(fnd_profile.VALUE('USER_ID'), 0) --created_by
);
INSERT
INTO igi_cis_tax_treatment_h(vendor_id
, vendor_site_id
, tax_rate_id
, old_group_id
, new_group_id
, effective_date
, source_name
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, request_id
, program_id
, program_application_id
, program_login_id)
VALUES(lcr_vendor_site.vendor_id --vendor_id
, lcr_vendor_site.vendor_site_id --vendor_site_id
, l_new_tax_rate_id --tax_rate_id
, nvl(l_site_old_tax_grp_id, l_old_tax_grp_id) --old_group_id
, l_tax_grp_id --new_group_id /* Bug 7218825 */
, p_effective_date --effective_date
, p_source --source_name
, sysdate --last_update_date
, nvl(fnd_profile.VALUE('USER_ID'), 0) --last_update_by
, nvl(fnd_profile.VALUE('LAST_UPDATE_LOGIN'), 0) --last_update_login
, sysdate --creation_date
, nvl(fnd_profile.VALUE('USER_ID'), 0) --created_by
, fnd_global.conc_request_id --request_id
, fnd_global.conc_program_id --program_id
, fnd_global.prog_appl_id --program_application_id
, fnd_global.conc_login_id); --program_login_id
SELECT ap_awt_tax_rates_s.nextval
INTO l_new_tax_rate_id
FROM dual;
INSERT
INTO ap_awt_tax_rates_all(tax_rate_id
, tax_name
, tax_rate
, rate_type
, start_date
, vendor_id
, vendor_site_id
, certificate_number
, certificate_type
, comments
, priority
, org_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by)
VALUES(l_new_tax_rate_id --tax_rate_id
, l_old_tax_name --tax_name
, l_new_tax_rate --tax_rate
, 'CERTIFICATE' --rate_type
, TRUNC(p_effective_date) --start_date
, lcr_vendor_site.vendor_id --vendor_id
, lcr_vendor_site.vendor_site_id --vendor_site_id
, 'CERT' --certificate_number
, 'STANDARD' --certificate_type
, initcap(p_source || ' - Tax Treatment Change') --comments
, 1 --priority
, lcr_vendor_site.org_id --org_id
, sysdate --last_update_date
, nvl(fnd_profile.VALUE('USER_ID'), 0) --last_update_by
, nvl(fnd_profile.VALUE('LAST_UPDATE_LOGIN'), 0) --last_update_login
, sysdate --creation_date
, nvl(fnd_profile.VALUE('USER_ID'), 0) --created_by
);
INSERT
INTO igi_cis_tax_treatment_h(vendor_id
, vendor_site_id
, tax_rate_id
, old_group_id
, new_group_id
, effective_date
, source_name
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, request_id
, program_id
, program_application_id
, program_login_id)
VALUES(lcr_vendor_site.vendor_id --vendor_id
, lcr_vendor_site.vendor_site_id --vendor_site_id
, l_new_tax_rate_id --tax_rate_id
, nvl(l_site_old_tax_grp_id, l_old_tax_grp_id) --old_group_id
, l_tax_grp_id --new_group_id /* Bug 7218825 */
, p_effective_date --effective_date
, p_source --source_name
, sysdate --last_update_date
, nvl(fnd_profile.VALUE('USER_ID'), 0) --last_update_by
, nvl(fnd_profile.VALUE('LAST_UPDATE_LOGIN'), 0) --last_update_login
, sysdate --creation_date
, nvl(fnd_profile.VALUE('USER_ID'), 0) --created_by
, fnd_global.conc_request_id --request_id
, fnd_global.conc_program_id --program_id
, fnd_global.prog_appl_id --program_application_id
, fnd_global.conc_login_id); --program_login_id
END IF; --Start of UPDATE If