[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| update STATUS column of AR_TAX_INTERFACE with |
| IGNORED-NARROWER-ZIP for the record which zip range is narrower than |
| the the other. |
| |
| MODIFICATION HISTORY |
| 20-Oct-98 Toru Kawamura Created. |
| 05-AUG-02 Satyadeep Bugfix 2377918 |
| Chandrashekar |
| |-----------| |
| |-----| |
| Such records in the interface will not |
| be marked as ignore-narrower-zip |
----------------------------------------------------------------------------*/
PROCEDURE ELIMINATE_OVERLAPPED_RANGE (
senior_segment in varchar2 default null,
default_start_date in date default to_date('01-01-1900', 'dd-mm-yyyy'))IS
cursor c_overlap_candidate(senior_segment in varchar2 ) IS
select distinct
ci.interface_line_id,
ci.location_segment_id,
ci.segment_qualifier,
trunc(nvl(ci.start_date, default_start_date)) start_date,
nvl(ci.end_date, arp_standard.max_end_date) end_date,
nvl(ci.from_postal_code,arp_standard.sysparm.from_postal_code ) from_postal_code,
nvl(ci.to_postal_code,arp_standard.sysparm.to_postal_code ) to_postal_code,
ci.rate_type,
ci.tax_rate,
ci.parent_location_id,
ci.location_id,
ci.location_value,
ci.status
from
ar_tax_interface ci,
ar_tax_interface co,
ar_tax_interface st
where ci.interface_line_id in (
select
distinct t1.interface_line_id
from
ar_tax_interface t1,
ar_tax_interface t2
where t1.location_value = t2.location_value
and t1.segment_qualifier = 'CITY'
and t1.parent_location_id = t2.parent_location_id
and t1.interface_line_id <> t2.interface_line_id
and ( ((( nvl(t1.from_postal_code,arp_standard.sysparm.from_postal_code)
>= nvl(t2.from_postal_code, arp_standard.sysparm.from_postal_code) )
and ( nvl(t1.from_postal_code, arp_standard.sysparm.from_postal_code)
<= nvl(t2.to_postal_code, arp_standard.sysparm.to_postal_code) ))
or (( nvl(t1.to_postal_code, arp_standard.sysparm.to_postal_code)
>= nvl(t2.from_postal_code, arp_standard.sysparm.from_postal_code) )
and ( nvl(t1.to_postal_code, arp_standard.sysparm.to_postal_code)
<= nvl(t2.to_postal_code, arp_standard.sysparm.to_postal_code) )))
and not (( nvl(t1.from_postal_code, arp_standard.sysparm.from_postal_code)
<= nvl(t2.from_postal_code, arp_standard.sysparm.from_postal_code) )
and ( nvl(t1.to_postal_code, arp_standard.sysparm.to_postal_code)
>= nvl(t2.to_postal_code, arp_standard.sysparm.to_postal_code) ))
and not (( nvl(t1.from_postal_code, arp_standard.sysparm.from_postal_code)
>= nvl(t2.from_postal_code, arp_standard.sysparm.from_postal_code) )
and ( nvl(t1.to_postal_code, arp_standard.sysparm.to_postal_code)
<= nvl(t2.to_postal_code, arp_standard.sysparm.to_postal_code) )) )
and ( trunc(nvl(t1.start_date, default_start_date)) = trunc(nvl(t2.start_date, default_start_date)) )
and not( ( nvl(t1.from_postal_code, arp_standard.sysparm.from_postal_code)
= nvl(t2.from_postal_code, arp_standard.sysparm.from_postal_code))
and ( nvl(t1.to_postal_code, arp_standard.sysparm.to_postal_code)
= nvl(t2.to_postal_code, arp_standard.sysparm.to_postal_code) ) )
)
and st.location_value like nvl(senior_segment,'%')
and co.parent_location_id = st.location_id
and ci.parent_location_id = co.location_id
and ci.status is null
order by ci.parent_location_id, ci.location_value, ci.location_id;
select distinct
interface_line_id,
location_segment_id,
segment_qualifier,
nvl(start_date, default_start_date) start_date,
nvl(end_date, arp_standard.max_end_date) end_date,
nvl(from_postal_code, arp_standard.sysparm.from_postal_code ) from_postal_code,
nvl(to_postal_code, arp_standard.sysparm.to_postal_code ) to_postal_code,
rate_type,
tax_rate,
parent_location_id,
location_value
from
ar_tax_interface
where interface_line_id <> p_interface_line_id
and segment_qualifier = p_segment_qualifier
and parent_location_id = p_parent_location_id
and location_value = p_location_value
and rate_type = p_rate_type
and status is null
and ( ((nvl(p_from_postal_code, arp_standard.sysparm.from_postal_code)
>= nvl(from_postal_code, arp_standard.sysparm.from_postal_code)
and nvl(p_from_postal_code, arp_standard.sysparm.from_postal_code)
<= nvl(to_postal_code, arp_standard.sysparm.to_postal_code))
or (nvl(p_to_postal_code, arp_standard.sysparm.to_postal_code)
>= nvl(from_postal_code, arp_standard.sysparm.from_postal_code)
and nvl(p_to_postal_code, arp_standard.sysparm.to_postal_code)
<= nvl(to_postal_code, arp_standard.sysparm.to_postal_code)))
and not (nvl(p_from_postal_code, arp_standard.sysparm.from_postal_code)
>= nvl(from_postal_code, arp_standard.sysparm.from_postal_code)
and nvl(p_to_postal_code, arp_standard.sysparm.to_postal_code)
<= nvl(to_postal_code, arp_standard.sysparm.to_postal_code))
and not (nvl(p_from_postal_code, arp_standard.sysparm.from_postal_code)
<= nvl(from_postal_code, arp_standard.sysparm.from_postal_code)
and nvl(p_to_postal_code, arp_standard.sysparm.to_postal_code)
>= nvl(to_postal_code, arp_standard.sysparm.to_postal_code)))
and not (nvl(p_from_postal_code, arp_standard.sysparm.from_postal_code)
= nvl(from_postal_code, arp_standard.sysparm.from_postal_code)
and nvl(p_to_postal_code, arp_standard.sysparm.to_postal_code)
= nvl(to_postal_code, arp_standard.sysparm.to_postal_code))
and trunc(nvl(p_start_date,default_start_date)) = trunc(nvl(start_date, default_start_date));
arp_util_tax.debug('in ar_tax_interface table, will be updated with IGNORED-NARROWER-ZIP');
sqlstmt := 'update ar_tax_interface set status =' ||''''||
'IGNORED-NARROWER-ZIP'||''''||' where interface_line_id in ('||in_clause||')';
arp_util_tax.debug('Update statement is <><><><><><>');
select count(*) into sel_count from ar_tax_interface where status = 'IGNORED-NARROWER-ZIP';
| UPDATE-INSERT Old record updates, new inserted as it stands |
| IGNORE Reject new record |
| ADJUST New record only needs adjusting |
| OVERRIDE New record replace old record |
| |
| EXCEPTIONS RAISED |
| |
| NOTES |
| |
| EXAMPLE |
| |
| MODIFICATION HISTORY |
| 25-Feb-93 Nigel Smith Created. |
| |
+---------------------------------------------------------------------------*/
FUNCTION dates_overlap( old_start in out NOCOPY date,
old_end in out NOCOPY date,
new_start in out NOCOPY date,
new_end in out NOCOPY date ) return BOOLEAN IS
begin -- return true if the old and new dates overlap
if ( old_start between new_start and new_end ) or
( old_end between new_start and new_end ) then
return(TRUE);
/* The new record starts before the existing record, update the new */
/* record so that it has an end date */
new_end := arp_standard.ceil(trunc( old_start -1 ));
action := 'INSERT';
/* it can be inserted */
old_end := arp_standard.ceil(new_start - 1);
action := 'UPDATE-INSERT';
else /* Dates do not overlap, insert new date, as it stands */
action := 'INSERT';
select DISTINCT
i.interface_line_id,
i.segment_qualifier,
level,
i.location_id,
upper(i.location_value) location_value,
i.location_value location_user_value,
nvl(i.location_description, initcap(i.location_value)) location_description,
i.parent_location_id,
nvl(i.from_postal_code, arp_standard.sysparm.from_postal_code ) from_postal_code,
nvl(i.to_postal_code, arp_standard.sysparm.to_postal_code ) to_postal_code,
trunc(nvl(i.start_date, default_start_date)) start_date,
nvl(i.end_date, arp_standard.max_end_date) end_date,
i.tax_rate,
i.location_segment_id,
i.status,
location_attribute_category,
location_attribute1,
location_attribute2,
location_attribute3,
location_attribute4,
location_attribute5,
location_attribute6,
location_attribute7,
location_attribute8,
location_attribute9,
location_attribute10,
location_attribute11,
location_attribute12,
location_attribute13,
location_attribute14,
location_attribute15,
rate_attribute_category,
rate_attribute1,
rate_attribute2,
rate_attribute3,
rate_attribute4,
rate_attribute5,
rate_attribute6,
rate_attribute7,
rate_attribute8,
rate_attribute9,
rate_attribute10,
rate_attribute11,
rate_attribute12,
rate_attribute13,
rate_attribute14,
rate_attribute15,
decode(i.segment_qualifier, 'CITY',
nvl(i.override_structure_id,
arp_standard.sysparm.location_structure_id),
null) override_structure_id,
override_rate1,
override_rate2,
override_rate3,
override_rate4,
override_rate5,
override_rate6,
override_rate7,
override_rate8,
override_rate9,
override_rate10
from ar_tax_interface i
where i.rate_type = 'SALES'
start with parent_location_id is null
and i.location_value like nvl(senior_segment,'%')
and i.rate_type = 'SALES'
and i.status is null
connect by prior location_id = parent_location_id
and i.rate_type = 'SALES'
order by rpad( location_id, 15, '0'), start_date; /*trunc(nvl(i.start_date, default_start_date));*/
select r.location_rate_id,
r.tax_rate,
r.from_postal_code,
r.to_postal_code,
r.start_date,
r.end_date,
r.override_rate1,
r.override_rate2,
r.override_rate3,
r.override_rate4,
r.override_rate5,
r.override_rate6,
r.override_rate7,
r.override_rate8,
r.override_rate9,
r.override_rate10,
r.attribute_category,
r.attribute1,
r.attribute2,
r.attribute3,
r.attribute4,
r.attribute5,
r.attribute6,
r.attribute7,
r.attribute8,
r.attribute9,
r.attribute10,
r.attribute11,
r.attribute12,
r.attribute13,
r.attribute14,
r.attribute15
from ar_location_rates r
where r.location_segment_id = p_location_segment_id
and ( p_from_postal_code between r.from_postal_code and r.to_postal_code
or p_to_postal_code between r.from_postal_code and r.to_postal_code
or (p_from_postal_code <= r.from_postal_code and
p_to_postal_code >= r.to_postal_code))
and ( p_start_date between r.start_date and nvl(r.end_date, r.start_date)
or p_end_date between r.start_date and nvl(r.end_date, r.start_date)
or (p_start_date <= r.start_date and
p_end_date >= nvl(r.end_date, p_end_date)))
and nvl(r.attribute1, -99) = nvl(p_rate_attribute1, -99)
order by from_postal_code, start_date
for update of tax_rate, from_postal_code, to_postal_code, start_date, end_date;
/*** Update STATUS column of AR_TAX_INTERFACE with NARROWER-ZIP-RANGE for the ***/
/*** records which are narrower than the other. ***/
ELIMINATE_OVERLAPPED_RANGE(senior_segment,
default_start_date);
if arp_adds.location_segment_inserted then
arp_adds.ins_location_rates( location_id(current_level),
arp_standard.sysparm.from_postal_code,
arp_standard.sysparm.to_postal_code,
arp_standard.min_start_date,
arp_standard.max_end_date,
0,
attribute_category => 'TRIGGER' );
| Loop over each record of the tax_interface table, selecting the rows |
| in the hierarchy of the location flexfield. |
| The Tree Walk Query Ensures that every parent value is read before |
| reading any new children. It also ensures that all children for a given |
| parent are found next to each other. |
| |
| Example Data |
| Qual LEVEL LOCATION_VALUE ZIP_START ZIP_END TAX_RATE |
| -------- ----- ------------------------ ---------- ---------- --------- |
| STATE 1 CA 90000 96699 6.25 |
| COUNTY 2 SACRAMENTO 1.5 |
| CITY 3 ARDEN ARCADE 95825 95825 0 |
| |
| If any records are orphans, the declared parent does not exist, they |
| will not be found by this cursor, and so the status of each will remain |
| unchanged. |
+--------------------------------------------------------------------------*/
FOR interface in interface_c( arp_standard.profile.request_id, senior_segment )
LOOP
BEGIN
interface.start_date := trunc( interface.start_date );
| This is needed to ensure that the select distinct, and order by |
| clauses added to interface_c will return the row data in the |
| correct order. |
+------------------------------------------------------------------*/
/* MB conversion - substr to substrb, converted because this is
basically a cmparsion */
if substrb( interface.location_id, 1, lengthb(interface.parent_location_id ) )
<> interface.parent_location_id then
arp_standard.fnd_message( 'AR_TAXI_LOCATION_BAD_PARENT',
'LOCATION_ID', interface.location_id,
'PARENT_LOCATION_ID', interface.parent_location_id );
| this parent before, if not insert the new value. |
| find_location_segment_id returns with the internal id for this |
| segment. |
+------------------------------------------------------------------*/
this_location := null;
new_location := arp_adds.location_segment_inserted;
END IF; /* Segment Value has not changed, dont attempt to re-insert it */
action := 'INSERT';
action := 'NEW-LOCATION-INSERT';
record needs updating or inserting */
-- Bug 2609220 added parameter rate_attribute1 to cursor
for rates in location_rates_c( location_id(current_level),
nvl(interface.from_postal_code,arp_standard.sysparm.from_postal_code),
nvl(interface.to_postal_code,arp_standard.sysparm.to_postal_code),
interface.start_date,
interface.end_date,
interface.rate_attribute1 )
loop
if interface.status = 'IGNORED-NARROWER-ZIP' then
action := 'IGNORED-NARROWER-ZIP';
ACTION := 'ZIP-RANGE-UPDATED'; -- logic to end date broader range later in the code
action := 'INSERT';
ACTION := 'ZIP-RANGE-UPDATED';
if action = 'UPDATE-INSERT' then
if pg_debug='Y' then
arp_util_tax.debug( 'UPDATE old data: ' || rates.location_rate_id || ' ' ||
rates.from_postal_code || '->' || rates.to_postal_code || ' ' ||
rates.start_date || '->' || rates.end_date ||
' = ' || rates.tax_rate );
update ar_location_rates
set start_date = rates.start_date,
end_date = rates.end_date,
program_id = arp_standard.profile.program_id,
program_application_id = arp_standard.profile.program_application_id,
program_update_date = sysdate,
request_id = arp_standard.profile.request_id,
LAST_UPDATED_BY = arp_standard.profile.user_id,
LAST_UPDATE_DATE = sysdate
where current of location_rates_c;
arp_util_tax.debug( 'UPDATE RATE-ADJUST old data: ' || rates.location_rate_id || ' ' ||
rates.from_postal_code || '->' || rates.to_postal_code || ' ' ||
rates.start_date || '->' || rates.end_date ||
' = ' || rates.tax_rate );
update ar_location_rates
set from_postal_code = rates.from_postal_code,
to_postal_code = rates.to_postal_code,
start_date = rates.start_date,
end_date = rates.end_date,
tax_rate = rates.tax_rate,
override_rate1 = interface.override_rate1,
override_rate2 = interface.override_rate2,
override_rate3 = interface.override_rate3,
override_rate4 = interface.override_rate4,
override_rate5 = interface.override_rate5,
override_rate6 = interface.override_rate6,
override_rate7 = interface.override_rate7,
override_rate8 = interface.override_rate8,
override_rate9 = interface.override_rate9,
override_rate10 = interface.override_rate10,
program_id = arp_standard.profile.program_id,
program_application_id = arp_standard.profile.program_application_id,
program_update_date = sysdate,
request_id = arp_standard.profile.request_id,
LAST_UPDATED_BY = arp_standard.profile.user_id,
LAST_UPDATE_DATE = sysdate,
attribute_category = interface.rate_attribute_category,
attribute1 = interface.rate_attribute1,
attribute2 = interface.rate_attribute2,
attribute3 = interface.rate_attribute3,
attribute4 = interface.rate_attribute4,
attribute5 = interface.rate_attribute5,
attribute6 = interface.rate_attribute6,
attribute7 = interface.rate_attribute7,
attribute8 = interface.rate_attribute8,
attribute9 = interface.rate_attribute9,
attribute10 = interface.rate_attribute10,
attribute11 = interface.rate_attribute11,
attribute12 = interface.rate_attribute12,
attribute13 = interface.rate_attribute13,
attribute14 = interface.rate_attribute14,
attribute15 = interface.rate_attribute15
where current of location_rates_c;
elsif action = 'ZIP-RANGE-UPDATED' then
if pg_debug='Y' then
arp_util_tax.debug( 'ZIP-RANGE-UPDATED old data: ' || rates.location_rate_id || ' ' ||
rates.from_postal_code || '->' || rates.to_postal_code || ' ' ||
rates.start_date || '->' || rates.end_date ||
' = ' || rates.tax_rate );
update ar_location_rates
set end_date = start_date,
program_id = arp_standard.profile.program_id,
program_application_id = arp_standard.profile.program_application_id,
program_update_date = sysdate,
request_id = arp_standard.profile.request_id,
LAST_UPDATED_BY = arp_standard.profile.user_id,
LAST_UPDATE_DATE = sysdate
where current of location_rates_c;
if action = 'ZIP-RANGE-UPDATED' then
if (arp_standard.ceil(interface.end_date) = interface.start_date) then
if interface.end_date is not null then
interface.end_date := interface.end_date +1;
action := 'UPDATE-INSERT';
if action = 'INSERT' or
action = 'UPDATE-INSERT' or
action = 'NEW-LOCATION-INSERT' then
-- Bug 2609220 added parameter p_rate_attribute1
for rates in location_rates_c( location_id(current_level),
interface.from_postal_code,
interface.to_postal_code,
interface.start_date,
interface.end_date,
interface.rate_attribute1 )
loop
action := 'ALREADY-EXISTS';
if action = 'INSERT' or
action = 'UPDATE-INSERT' or
action = 'NEW-LOCATION-INSERT' then
if pg_debug='Y' then
arp_util_tax.debug( 'Row inserted: ' || location_id(current_level) || ' ' ||
interface.from_postal_code || '->' || interface.to_postal_code || ' ' ||
interface.start_date || '->' || interface.end_date );
UPDATE AR_TAX_INTERFACE
SET STATUS = action,
/*** MB conversion substr to substrb ***/
ERROR_MESSAGE = substrb(error_text,1,240),
LOCATION_SEGMENT_ID = this_location,
LAST_UPDATED_BY = arp_standard.profile.user_id,
LAST_UPDATE_DATE = sysdate,
PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id,
PROGRAM_ID = arp_standard.profile.program_id,
REQUEST_ID = arp_standard.profile.request_id
WHERE interface_line_id = interface.interface_line_id;
select distinct location_value
from ar_tax_interface
where parent_location_id is null
and rate_type = 'SALES'
and location_value like nvl(senior_segment,location_value)
order by location_value;
select distinct location_value
from ar_tax_interface
where parent_location_id is null
and rate_type = 'SALES'
and location_value like senior_segment
order by location_value;
select max(lengthb(location_value))
into max_location_width
from ar_tax_interface;