[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert_allowed VARCHAR2(10),
reason_code VARCHAR2(30),
certificate VARCHAR2(80),
percent_exempt NUMBER,
inserted_flag VARCHAR2(1),
tax_exemption_id NUMBER,
exemption_type VARCHAR2(30),
hash_string VARCHAR2(1000)
);
| 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
l_transfer_rates_initialised varchar2(5);
| 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
c integer;
| 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
c integer;
| 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
c integer;
| 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_QP_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
dummy_number number;
| MODIFIES: inserts combined rates into AR_SALES_TAX |
| |
+------------------------------------------------------------------------*/
PROCEDURE combine_tax_rates IS
c integer;