[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ar_sales_tax_s.nextval + arp_standard.sequence_offset
from dual;
insert into ar_sales_tax(
SALES_TAX_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE,
LOCATION_ID,
rate_context,
tax_rate,
LOCATION1_RATE,
LOCATION2_RATE,
LOCATION3_RATE,
from_postal_code,
to_postal_code,
start_date,
end_date,
enabled_flag)
VALUES
(
sales_tax_id,
sysdate,
arp_standard.profile.user_id,
null,
arp_standard.profile.user_id,
sysdate,
location_id,
location_structure_id,
total_tax_rate,
LOCATION1_RATE,
LOCATION2_RATE,
LOCATION3_RATE,
from_postal_code,
to_postal_code,
start_date,
end_date,
'Y');
| Will take each distinct location_segment_id used during updates or |
| inserts to the table: AR_LOCATION_RATES and propogate these sales |
| tax rate changes into the table: AR_SALES_TAX |
| |
| This procedures fires the cursor: sel_cc to find each Location Code |
| Combination that uses a particular location_segment_id and then |
| updates each of the rows in ar_sales_tax for that specific location. |
| |
| To optimise performance of the code a note of every code combination |
| is made so that the same set of records in ar_sales_tax is only ever |
| visited once. |
| |
| EXAMPLE |
| |
| If the following Code Combinations were active on a system: |
| CA.SAN MATEO.BELMONT CCID: 1000, LOCATIONS: 4.6.8 |
| CA.SAN MATEO.FOSTER CITY CCID: 1001, LOCATIONS: 4.6.10 |
| CA.FREEMONT.FREEMONT CCID: 1002, LOCATIONS: 4.12.14 |
| FL.MIAMI.MIAMI CCID: 1003, LOCATIONS: 16.18.20 |
| |
| And the user updates AR_LOCATION_RATES for ( CA, and SAN MATEO ) |
| There would be two rows set up by the TRIGGER: AR_LOCATION_RATES_BRIU |
| in the PL/SQL table: location_segment_id, these rows would be: |
| location_segment_id(0) = 4 |
| location_segment_id(1) = 6 |
| This procedure would fire the cursor: sel_cc which for location 4 |
| would return: 3 rows, CCIDS: 1000, 10001, and 1002. |
| Sales Tax Rate records would then be regenerated for all 3 different |
| location code combinations. |
| Cursor sel_cc would then be re-fired for location 6 and return the |
| following two rows: 1000, 1001. Since both of these rows have already |
| been updated no further work is required and the procedure completes. |
| |
| MODIFICATION HISTORY |
| 22-Jan-93 Nigel Smith Created. |
| |
+-------------------------------------------------------------------------*/
PROCEDURE Implement_Transfer_Rates is
cursor sel_cc( location_segment_id in number ) IS
select
struct.location_id location_ccid,
struct.location_id_segment_1,
struct.location_id_segment_2,
struct.location_id_segment_3,
struct.location_id_segment_4,
struct.location_id_segment_5,
struct.location_id_segment_6,
struct.location_id_segment_7,
struct.location_id_segment_8,
struct.location_id_segment_9,
struct.location_id_segment_10
from ar_location_combinations struct
where LOCATION_ID_SEGMENT_1 = location_segment_id
or LOCATION_ID_SEGMENT_2 = location_segment_id
or LOCATION_ID_SEGMENT_3 = location_segment_id; -- PL/SQL Flexfield Pre-Processor
| For each distinct location_segment_id that had a rate insert or update |
| assocaited with it, find each distinct location_ccid and update |
| the table: AR_SALES_TAX for this location_ccid, updating rate |
| assignments |
+------------------------------------------------------------------------*/
FOR i in 1 .. loc_rate
LOOP
IF location_segment_id( i ) is not null
THEN
/*------------------------------------------------------------------+
| We may have updated or inserted rates for this location multiple |
| times, in which case find any occurances of this location in the |
| array and reset this element to null so that we do not repeat |
| any work. |
+------------------------------------------------------------------*/
FOR j in i+1 .. loc_rate
LOOP
IF location_segment_id( i ) = location_segment_id( j )
THEN
location_segment_id( j ) := null;
| Update the Sales Tax table for this location, deleting any |
| invalid rates, adding any new ones. |
+------------------------------------------------------------------*/
FOR rates in sel_cc( location_segment_id(i) )
LOOP
/*---------------------------------------------------------------+
| Confirm that this location_ccid has not been updated before |
+---------------------------------------------------------------*/
do_transfer_flag := TRUE;
Populate_Sales_Tax( 'Update',
rates.location_ccid,
rates.location_id_segment_1,
rates.location_id_segment_2,
rates.location_id_segment_3,
rates.location_id_segment_4,
rates.location_id_segment_5,
rates.location_id_segment_6,
rates.location_id_segment_7,
rates.location_id_segment_8,
rates.location_id_segment_9,
rates.location_id_segment_10 );
select
struct.location_id location_ccid,
struct.location_id_segment_1,
struct.location_id_segment_2,
struct.location_id_segment_3,
struct.location_id_segment_4,
struct.location_id_segment_5,
struct.location_id_segment_6,
struct.location_id_segment_7,
struct.location_id_segment_8,
struct.location_id_segment_9,
struct.location_id_segment_10
from ar_location_combinations struct,
hz_party_sites party_site,
hz_loc_assignments loc_assign,
hz_locations loc,
hz_cust_acct_sites acct_site
where struct.location_id = loc_assign.loc_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and loc.location_id = loc_assign.location_id
and NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99);
Populate_Sales_Tax( 'Update',
cc.location_ccid ,
cc.location_id_segment_1 ,
cc.location_id_segment_2 ,
cc.location_id_segment_3 ,
cc.location_id_segment_4 ,
cc.location_id_segment_5 ,
cc.location_id_segment_6 ,
cc.location_id_segment_7 ,
cc.location_id_segment_8 ,
cc.location_id_segment_9 ,
cc.location_id_segment_10 ) ;
| For each set of inserted or updateed rows in the table AR_LOCATION_RATES|
| a distinct value is placed in the column: AR_TRANSFER_CONTROL_ID |
| When all records have been inserted or updated, the after statement |
| trigger: AR_LOCATION_RATES_ASIU fires, and refetchs each of these new |
| records across into the table: AR_SALES_TAX |
| |
| This works around the kernel limitation of MUTATING tables. |
| |
| MODIFIES |
| Public variable: LOCATION_RATES_TRANSFER_ID |
| |
| EXCEPTIONS RAISED |
| |
| NOTES |
| |
| EXAMPLE |
| |
+-------------------------------------------------------------------------*/
PROCEDURE Initialise_Transfer_Rates is
BEGIN
--PG_DEBUG := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
| In order to do this, it may be necessary to insert new items into |
| the tables: AR_LOCATION_VALUES and AR_LOCATION_COMBINATIONS |
| |
| REQUIRES |
| Location_CCID Location ID for ths entry in Sales Tax |
| location_id_segments 1 .. 10, Location_segment_id for each segment |
| in the location flexfield structure. |
| |
| STATEMENT_TYPE |
| |
| INSERT New Location CCID Created, there will be *NO* |
| pre-existing sales tax data for this location |
| DELETE A Locatoin Code Combination has been deleted |
| purge ununsed sales tax rates. |
| UPDATE Existing Data May Exist, some of whic may now |
| be invalid. Purge Invalid data, creating new |
| valid data in its place. |
| EXCEPTIONS RAISED |
| |
| NOTES |
| |
| EXAMPLE |
| |
+-------------------------------------------------------------------------*/
PROCEDURE Populate_Sales_Tax( statement_type in varchar2,
location_ccid in number,
p_location_id_segment_1 in number,
p_location_id_segment_2 in number,
p_location_id_segment_3 in number,
p_location_id_segment_4 in number,
p_location_id_segment_5 in number,
p_location_id_segment_6 in number,
p_location_id_segment_7 in number,
p_location_id_segment_8 in number,
p_location_id_segment_9 in number,
p_location_id_segment_10 in number ) is
--
cursor sel_bad_rates( p_location_id in number,
p_location_id_segment_1 in number,
p_location_id_segment_2 in number,
p_location_id_segment_3 in number,
p_location_id_segment_4 in number,
p_location_id_segment_5 in number,
p_location_id_segment_6 in number,
p_location_id_segment_7 in number,
p_location_id_segment_8 in number,
p_location_id_segment_9 in number,
p_location_id_segment_10 in number ) is
select rowid, sales_tax_id
from ar_sales_tax tax
where tax.location_id = p_location_id
and tax.enabled_flag = 'Y'
and not exists (
select
'x'
from ar_location_rates r1,
ar_location_rates r2,
ar_location_rates r3
where r1.location_segment_id = p_location_id_segment_1
and r2.location_segment_id = p_location_id_segment_2
and r3.location_segment_id = p_location_id_segment_3
And R1.FROM_POSTAL_CODE <= TAX.FROM_POSTAL_CODE
AND R2.FROM_POSTAL_CODE <= TAX.FROM_POSTAL_CODE
AND R3.FROM_POSTAL_CODE <= TAX.FROM_POSTAL_CODE
And R1.TO_POSTAL_CODE <= TAX.TO_POSTAL_CODE
AND R2.TO_POSTAL_CODE <= TAX.TO_POSTAL_CODE
AND R3.TO_POSTAL_CODE <= TAX.TO_POSTAL_CODE
And R1.START_DATE <= TAX.START_DATE
AND R2.START_DATE <= TAX.START_DATE
AND R3.START_DATE <= TAX.START_DATE
And R1.END_DATE <= TAX.END_DATE
AND R2.END_DATE <= TAX.END_DATE
AND R3.END_DATE <= TAX.END_DATE
and tax.location1_rate = decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
and tax.location2_rate = decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
and tax.location3_rate = decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3)
and tax.from_postal_code = greatest( r1.from_postal_code,
r2.from_postal_code,
r3.from_postal_code )
and tax.to_postal_code = least( r1.to_postal_code,
r2.to_postal_code,
r3.to_postal_code )
and tax.start_date = greatest( r1.start_date ,
r2.start_date ,
r3.start_date )
and tax.end_date = least( r1.end_date ,
r2.end_date ,
r3.end_date )
and greatest( r1.from_postal_code,
r2.from_postal_code,
r3.from_postal_code ) <= least( r1.to_postal_code,
r2.to_postal_code,
r3.to_postal_code )
and greatest( r1.start_date ,
r2.start_date ,
r3.start_date ) <= least( r1.end_date ,
r2.end_date ,
r3.end_date ));
SELECT 'x' from dual
WHERE exists (
select 'x'
from ra_customer_trx_lines l
where l.sales_tax_id = p_sales_tax_id );
| If any rate is no longer valid, attempt to delete the record, and if |
| this fails, disable the record so that it will not be available for |
| future use. |
| |
| This is only applicable is we are updating existing locations, simply |
| defining new locations means that there can be no records in the |
| sales tax table. |
+------------------------------------------------------------------------*/
IF statement_type = 'Update' or statement_type = 'Delete'
THEN
FOR tax_rate_rec in sel_bad_rates(
location_ccid, p_location_id_segment_1, p_location_id_segment_2, p_location_id_segment_3,
p_location_id_segment_4, p_location_id_segment_5, p_location_id_segment_6,
p_location_id_segment_7, p_location_id_segment_8, p_location_id_segment_9,
p_location_id_segment_10 )
LOOP
BEGIN
OPEN RateUsed( tax_rate_rec.sales_tax_id );
DELETE FROM ar_sales_tax
WHERE rowid = tax_rate_rec.rowid;
UPDATE ar_sales_tax SET enabled_flag = 'N',
last_update_date = sysdate,
last_updated_by = arp_standard.profile.user_id
WHERE rowid = tax_rate_rec.rowid;
| table are inserted. |
+-----------------------------------------------------------------------*/
IF PG_DEBUG = 'Y' THEN
arp_util_tax.debug( 'I: Populate_sales_tax: ' || p_location_id_segment_1 || ' ' ||
p_location_id_segment_2 || ' ' ||
p_location_id_segment_3 || ' ' ||
p_location_id_segment_4 );
IF statement_type <> 'Delete'
THEN
if statement_type = 'Update'
THEN
insert into ar_sales_tax(
SALES_TAX_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE,
LOCATION_ID,
rate_context,
tax_rate,
LOCATION1_RATE,
LOCATION2_RATE,
LOCATION3_RATE,
from_postal_code,
to_postal_code,
start_date,
end_date,
enabled_flag)
select
AR_SALES_TAX_S.NEXTVAL+arp_standard.sequence_offset,
sysdate,
arp_standard.profile.user_id,
null,
arp_standard.profile.user_id,
sysdate,
location_ccid,
arp_standard.sysparm.location_structure_id,
decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
+ decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
+ decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1),
decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2),
decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
greatest( r1.from_postal_code,
r2.from_postal_code,
r3.from_postal_code ),
least( r1.to_postal_code,
r2.to_postal_code,
r3.to_postal_code ),
greatest( r1.start_date ,
r2.start_date ,
r3.start_date ),
least( r1.end_date ,
r2.end_date ,
r3.end_date ),
'Y' /* Enabled Flag */
from ar_location_rates r1,
ar_location_rates r2,
ar_location_rates r3
where r1.location_segment_id = p_location_id_segment_1
and r2.location_segment_id = p_location_id_segment_2
and r3.location_segment_id = p_location_id_segment_3
and greatest( r1.from_postal_code,
r2.from_postal_code,
r3.from_postal_code ) <= least( r1.to_postal_code,
r2.to_postal_code,
r3.to_postal_code )
and greatest( r1.start_date ,
r2.start_date ,
r3.start_date ) <= least( r1.end_date ,
r2.end_date ,
r3.end_date )
and not exists (
select 'x' from ar_sales_tax tax
where tax.location_id = location_ccid
and tax.location1_rate = decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
and tax.location2_rate = decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
and tax.location3_rate = decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3)
and tax.from_postal_code = greatest( r1.from_postal_code,
r2.from_postal_code,
r3.from_postal_code )
and tax.to_postal_code = least( r1.to_postal_code,
r2.to_postal_code,
r3.to_postal_code )
and tax.start_date = greatest( r1.start_date ,
r2.start_date ,
r3.start_date )
and tax.end_date = least( r1.end_date ,
r2.end_date ,
r3.end_date )
and tax.enabled_flag = 'Y' );
ELSE /* Statement type is INSERT, dont us: not exists clause */
insert into ar_sales_tax(
SALES_TAX_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE,
LOCATION_ID,
rate_context,
tax_rate,
LOCATION1_RATE,
LOCATION2_RATE,
LOCATION3_RATE,
from_postal_code,
to_postal_code,
start_date,
end_date,
enabled_flag)
select
AR_SALES_TAX_S.NEXTVAL+arp_standard.sequence_offset,
sysdate,
arp_standard.profile.user_id,
null,
arp_standard.profile.user_id,
sysdate,
location_ccid,
arp_standard.sysparm.location_structure_id,
decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
+ decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
+ decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1),
decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2),
decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
greatest( r1.from_postal_code,
r2.from_postal_code,
r3.from_postal_code ),
least( r1.to_postal_code,
r2.to_postal_code,
r3.to_postal_code ),
greatest( r1.start_date ,
r2.start_date ,
r3.start_date ),
least( r1.end_date ,
r2.end_date ,
r3.end_date ),
'Y' /* Enabled Flag */
from ar_location_rates r1,
ar_location_rates r2,
ar_location_rates r3
where r1.location_segment_id = p_location_id_segment_1
and r2.location_segment_id = p_location_id_segment_2
and r3.location_segment_id = p_location_id_segment_3
and greatest( r1.from_postal_code,
r2.from_postal_code,
r3.from_postal_code ) <= least( r1.to_postal_code,
r2.to_postal_code,
r3.to_postal_code )
and greatest( r1.start_date ,
r2.start_date ,
r3.start_date ) <= least( r1.end_date ,
r2.end_date ,
r3.end_date );
END IF; /* Insert or Update Mode */
END IF; /* Dont call if in delete mode */
update ar_location_combinations
set last_update_date = sysdate ,
last_updated_by = arp_standard.profile.user_id,
program_id = arp_standard.profile.program_id,
program_application_id = arp_standard.profile.program_application_id
where location_id in ( select loc_assign.loc_id
from hz_party_sites party_site,
hz_loc_assignments loc_assign,
hz_locations loc,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_uses
where site_uses.cust_acct_site_id =
acct_site.cust_acct_site_id
and acct_site.party_site_id =
party_site.party_site_id
and loc.location_id = party_site.location_id
and loc.location_id = loc_assign.location_id
and nvl(acct_site.org_id,-99) =
nvl(loc_assign.org_id,-99)
and site_uses.site_use_code = 'SHIP_TO' );
SELECT rowid, sales_tax_id from ar_sales_tax;
SELECT 'x' from dual
WHERE exists (
select 'x'
from ra_customer_trx_lines l
where l.sales_tax_id = p_sales_tax_id );
DELETE FROM ar_sales_tax
WHERE rowid = tax.rowid;
UPDATE ar_sales_tax SET enabled_flag = 'N',
last_update_date = sysdate,
last_updated_by = arp_standard.profile.user_id
WHERE rowid = tax.rowid;
| CALLED BY USER EXIT: #AR SALESTAX MODE=UPDATE |
| |
| DESCRIPTION |
| Will renumber each Tax line belonging to an invoice so that duplicate |
| line numbers no longer exist. |
| For Example:- if Original Invoice had the following lines, this |
| procedure would update each of the line numbers to be:- |
| |
| OLD INVOICE NEW_INVOICE |
| 1 ITEM LINE 1 ITEM LINE |
| 1 TAX LINE 1 TAX LINE |
| 2 TAX LINE 2 TAX LINE |
| 1 TAX LINE 3 TAX LINE |
| |
| Duplicate Line numbers can occur when a header level change is made |
| for an invoice and all but Adhoc Tax lines are deleted and then |
| recalculated. |
| |
| REQUIRES: CUSTOMER_TRX_ID |
| |
| MODIFIES: RA_CUSTOMER_TRX_LINES.LINE_NUMBER |
| |
+-------------------------------------------------------------------------*/
PROCEDURE renumber_tax_lines( customer_trx_id in number,
trx_type in varchar2 default 'TAX' ) is
cursor c_tax_lines( cust_trx_id in number, cust_trx_type in varchar2 ) is
select customer_trx_line_id,
line_number,
link_to_cust_trx_line_id
from ra_customer_trx_lines
where customer_trx_id = cust_trx_id
and line_type = cust_trx_type
and link_to_cust_trx_line_id is not null
order by link_to_cust_trx_line_id, customer_trx_line_id
for update of line_number;
update ra_customer_trx_lines set line_number = new_line_number
where current of c_tax_lines;
select p.start_date, p.end_date
from gl_period_statuses p, gl_sets_of_books g
where p.application_id = arp_standard.application_id
and p.set_of_books_id = arp_standard.sysparm.set_of_books_id
and trunc(trx_date) between p.start_date and p.end_date
and g.set_of_books_id = p.set_of_books_id
and g.accounted_period_type = p.period_type;
| INSERT_ALLOWED If False and "E" is called but not |
| valid exemption is on file; this routine |
| INSERTED_FLAG TRUE if this call forced an insert |
| EXEMPTION_TYPE CUSTOMER or ITEM |
| |
| DATABASE REQUIREMENTS |
| View: TAX_EXEMPTIONS_V This view must be installed before |
| this database package can be installed |
| |
| MODIFICATION HISTORY |
| |
| 17-May-94 Nigel Smith Created. |
| 3 Aug, 94 Nigel Smith BUGFIX: 228807, Exemptions are now |
| managed by Bill To Customer and Ship |
| To Site. |
| 13 Oct 94 Nigel Smith Bugfix: 227953, Exemptions with No |
| certificate number were not shown by Order|
| Entry. |
| 19 Oct 94 Nigel Smith Bugfix: 244306, Error Validating Address |
| during order entry; or invoice creation on|
insert_allowed in varchar2 default 'TRUE',
reason_code in out NOCOPY varchar2,
certificate in out NOCOPY varchar2,
percent_exempt out NOCOPY number,
inserted_flag out NOCOPY varchar2,
tax_exemption_id out NOCOPY number,
exemption_type out NOCOPY varchar2
) is
--
-- CURSOR: chk_customer_exemption
--
-- Check to see if this customer has an exemption at any level
--
-- The Explain Plan from this cursor, is very light weight and will for the
-- majority of calls to the tax engine, result in no data found forcing the
-- larger cursor not to be executed. This is based on the assumption
-- that the majority of Customers are not Tax Exempt; if they are an
select 'x' from dual where exists
( select 'x' from ra_tax_exemptions where
customer_id = customer );
select
x.percent_exempt,
x.tax_exemption_id,
x.reason_code,
rtrim(ltrim(nvl(x.customer_exemption_number,' '))) tax_exempt_number,
x.status,
x.start_date,
x.end_date,
decode( x.site_use_id, null,
decode(x.location_id_segment_10, null,
decode(x.location_id_segment_9, null,
decode(x.location_id_segment_8, null,
decode(x.location_id_segment_7, null,
decode(x.location_id_segment_6, null,
decode(x.location_id_segment_5, null,
decode(x.location_id_segment_4, null,
decode(x.location_id_segment_3, null,
decode(x.location_id_segment_2, null,
decode(x.location_id_segment_1, null,
11, 10), 9), 8), 7), 6), 5), 4), 3), 2), 1), 0 )
+decode( x.status, 'PRIMARY', 0,
'MANUAL', 1000,
'UNAPPROVED', 2000,
'EXPIRED', 3000, 4000 )
DISPLAY_ORDER
from
hz_cust_site_uses s,
hz_cust_acct_sites a,
hz_party_sites p,
hz_loc_assignments la,
ra_tax_exemptions x,
ar_location_combinations c
WHERE la.loc_id = c.location_id(+)
and a.party_site_id = p.party_site_id
and p.location_id = la.location_id
and nvl(a.org_id, -99) = nvl(la.org_id, -99)
and s.cust_acct_site_id = a.cust_acct_site_id
and nvl(x.location_id_segment_1, nvl(c.location_id_segment_1,-1)) = nvl(c.location_id_segment_1,-1)
and nvl(x.location_id_segment_2, nvl(c.location_id_segment_2,-1)) = nvl(c.location_id_segment_2,-1)
and nvl(x.location_id_segment_3, nvl(c.location_id_segment_3,-1)) = nvl(c.location_id_segment_3,-1)
and nvl(x.location_id_segment_4, nvl(c.location_id_segment_4,-1)) = nvl(c.location_id_segment_4,-1)
and nvl(x.location_id_segment_5, nvl(c.location_id_segment_5,-1)) = nvl(c.location_id_segment_5,-1)
and nvl(x.location_id_segment_6, nvl(c.location_id_segment_6,-1)) = nvl(c.location_id_segment_6,-1)
and nvl(x.location_id_segment_7, nvl(c.location_id_segment_7,-1)) = nvl(c.location_id_segment_7,-1)
and nvl(x.location_id_segment_8, nvl(c.location_id_segment_8,-1)) = nvl(c.location_id_segment_8,-1)
and nvl(x.location_id_segment_9, nvl(c.location_id_segment_9,-1)) = nvl(c.location_id_segment_9,-1)
and nvl(x.location_id_segment_10, nvl(c.location_id_segment_10,-1)) = nvl(c.location_id_segment_10,-1)
and x.exemption_type = 'CUSTOMER'
and nvl( x.site_use_id, s.site_use_id ) = s.site_use_id
and x.customer_id = customer
and s.site_use_id = site
and x.tax_code = taxcode
/*
* Standard Tax rules can only search for Exemptions that are marked as
* PRIMARY. All other exemptions are ignored.
*
*/
AND (( tax_exempt_flag = 'S' and x.status = 'PRIMARY' )
/*
* Transactions that are forced exempt, should only ever use an existing certificate
* number if:-
* The Certificate is not rejected or expired.
* The user supplied reason codes, and exemption numbers match those on the certificate
* (note the supplied exemption number can be null)
*
* If these conditions are NOT met, a new Unapproved certificate will be created
* to support this.
*
*/
OR ( tax_exempt_flag = 'E'
AND x.STATUS IN ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' )
AND x.REASON_CODE = reason_code
AND ( (rtrim(ltrim(x.customer_exemption_number)) = certificate_number)
or (x.customer_exemption_number IS NULL AND
certificate_number IS NULL)) ))
AND trxdate between x.start_date and nvl(x.end_date, trx_date)
ORDER BY DISPLAY_ORDER;
SELECT percent_exempt, tax_exemption_id,
reason_code tax_exempt_reason_code,
rtrim(ltrim(customer_exemption_number)) tax_exempt_number
FROM ra_tax_exemptions
WHERE inventory_item_id = item
AND tax_code = taxcode
AND trxdate between start_date and nvl(end_date, trx_date)
AND exemption_type = 'ITEM'
AND status = 'PRIMARY'; /* Bugfix 520228 */
SELECT ra_tax_exemptions_s.nextval from dual;
select c.location_id_segment_1,
c.location_id_segment_2,
c.location_id_segment_3,
c.location_id_segment_4,
c.location_id_segment_5,
c.location_id_segment_6,
c.location_id_segment_7,
c.location_id_segment_8,
c.location_id_segment_9,
c.location_id_segment_10
from ar_location_combinations c,
hz_cust_acct_sites acct_site,
hz_loc_assignments loc_assign,
hz_locations loc,
hz_party_sites party_site,
hz_cust_site_uses site_uses
where site_uses.site_use_id = site_id
and site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and loc.location_id = loc_assign.location_id
and loc_assign.loc_id = c.location_id(+); /* BUGFIX: 244306 */
l_inserted boolean := FALSE;
l_inserted := FALSE;
insert_allowed = 'TRUE' and
l_tax_exempt_reason_code is not null then
/*********************************************************************/
/* Using: EXEMPT_LEVEL qualifier, find each location_segment_id */
/* that must be populated in ra_tax_exemptions.location_id_segment_n */
/*********************************************************************/
IF PG_DEBUG = 'Y' THEN
arp_util_tax.debug( 'I: Location Qualifiers');
/* Insert automatic customer exemption */
/***************************************/
period_date_range( trx_date,
l_period_start_date,
l_period_end_date );
insert into ra_tax_exemptions(
TAX_EXEMPTION_ID,
CREATED_BY,
CREATION_DATE,
EXEMPTION_TYPE,
IN_USE_FLAG,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
PERCENT_EXEMPT,
START_DATE,
TAX_CODE,
CUSTOMER_ID,
END_DATE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
CUSTOMER_EXEMPTION_NUMBER,
REASON_CODE,
STATUS,
LOCATION_CONTEXT,
LOCATION_ID_SEGMENT_1,
LOCATION_ID_SEGMENT_2,
LOCATION_ID_SEGMENT_3,
LOCATION_ID_SEGMENT_4,
LOCATION_ID_SEGMENT_5,
LOCATION_ID_SEGMENT_6,
LOCATION_ID_SEGMENT_7,
LOCATION_ID_SEGMENT_8,
LOCATION_ID_SEGMENT_9,
LOCATION_ID_SEGMENT_10
)
values
(
l_exemption_id,
arp_standard.profile.user_id,
sysdate,
'CUSTOMER',
'Y',
arp_standard.profile.user_id,
sysdate,
100.00,
l_period_start_date,
tax_code,
bill_to_customer_id,
null,
arp_standard.application_id,
arp_standard.profile.program_id,
sysdate,
arp_standard.profile.request_id,
l_tax_exempt_number,
l_tax_exempt_reason_code,
'UNAPPROVED',
arp_standard.sysparm.location_structure_id,
l_location_id_segment(1),
l_location_id_segment(2),
l_location_id_segment(3),
l_location_id_segment(4),
l_location_id_segment(5),
l_location_id_segment(6),
l_location_id_segment(7),
l_location_id_segment(8),
l_location_id_segment(9),
l_location_id_segment(10)
);
l_inserted := TRUE;
arp_util_tax.debug('Inserting into cache after inserting into ra_tax_exemptions');
arp_util_tax.debug('Inserting into cache. Exemption is not found.');
l_inserted := FALSE;
arp_util_tax.debug('Inserting into cache. Exemption is found by query. ');
l_inserted := FALSE;
if l_inserted
then
inserted_flag := 'Y';
inserted_flag := 'N';
if l_inserted
then
IF PG_DEBUG = 'Y' THEN
arp_util_tax.debug( '<< FIND_TAX_EXEMPTION_ID( INSERTED, ' || l_exemption_type || ' ' || l_exemption_id
|| ', ' || l_percent_exempt ||' )' );
inserted_flag := 'N';
Update ra_tax_exemptions_all
set in_use_flag = 'Y'
where tax_exemption_id = l_exemption_id;
| MODIFIES: inserts combined rates into AR_SALES_TAX |
| |
+------------------------------------------------------------------------*/
PROCEDURE combine_tax_rates IS
BEGIN
insert into ar_sales_tax(
SALES_TAX_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LOCATION_ID,
rate_context,
tax_rate,
LOCATION1_RATE,
LOCATION2_RATE,
LOCATION3_RATE,
from_postal_code,
to_postal_code,
start_date,
end_date,
enabled_flag)
select
AR_SALES_TAX_S.NEXTVAL+arp_standard.sequence_offset,
sysdate,
arp_standard.profile.user_id,
null,
arp_standard.profile.user_id,
sysdate,
ccid.location_id,
arp_standard.sysparm.location_structure_id,
decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
+ decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
+ decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1),
decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2),
decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3),
greatest( r1.from_postal_code,
r2.from_postal_code,
r3.from_postal_code ),
least( r1.to_postal_code,
r2.to_postal_code,
r3.to_postal_code ),
greatest( r1.start_date ,
r2.start_date ,
r3.start_date ),
least( r1.end_date ,
r2.end_date ,
r3.end_date ),
'Y'
from ar_location_rates r1,
ar_location_rates r2,
ar_location_rates r3,
AR_LOCATION_COMBINATIONS ccid
where ccid.LOCATION_ID_SEGMENT_1 = r1.location_segment_id and
ccid.LOCATION_ID_SEGMENT_2 = r2.location_segment_id and
ccid.LOCATION_ID_SEGMENT_3 = r3.location_segment_id
and greatest( r1.from_postal_code,
r2.from_postal_code,
r3.from_postal_code ) <= least( r1.to_postal_code,
r2.to_postal_code,
r3.to_postal_code )
and greatest( r1.start_date ,
r2.start_date ,
r3.start_date ) <= least( r1.end_date ,
r2.end_date ,
r3.end_date )
and not exists (
select 'x'
from ar_sales_tax tax
where tax.location_id = ccid.location_id
and tax.location1_rate = decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
and tax.location2_rate = decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
and tax.location3_rate = decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3)
and tax.from_postal_code =
greatest( r1.from_postal_code,
r2.from_postal_code,
r3.from_postal_code )
and tax.to_postal_code =
least( r1.to_postal_code,
r2.to_postal_code,
r3.to_postal_code )
and tax.start_date =
greatest( r1.start_date ,
r2.start_date ,
r3.start_date )
and tax.end_date =
least( r1.end_date ,
r2.end_date ,
r3.end_date )
and tax.enabled_flag = 'Y' );