1 PACKAGE ARP_STAX AUTHID CURRENT_USER AS
2 /* $Header: ARPLSTXS.pls 115.6 2002/11/15 22:03:57 thwon ship $ */
3
4 /*-------------------------------------------------------------------------+
5 | |
6 | PUBLIC EXCEPTIONS |
7 | |
8 +-------------------------------------------------------------------------*/
9
10
11 /*-------------------------------------------------------------------------+
12 | |
13 | PUBLIC DATATYPES |
14 | |
15 +-------------------------------------------------------------------------*/
16
17
18 TYPE TAB_ID_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
19
20 /*-------------------------------------------------------------------------+
21 | |
22 | PUBLIC VARIABLES |
23 | |
24 | PUBLIC VARIABLES ARE ONLY SUPPORTED SERVER SIDE, WITHIN PL/SQL 2. |
25 | CLIENT APPLICATIONS, FOR EXAMPLE: SRW20 and FORMS40 CAN ONLY ACCESS |
26 | THEIR VALUES THROUGH COVER FUNCTIONS. |
27 | |
28 | VARIABLE: triggers_enabled |
29 | If set to true, (default) row and statement table triggers will |
30 | fire on each of tables associated with Receivables Sales Tax |
31 | |
32 | VARIABLE: location_rate_transfer_id |
33 | This is a counter which gives the next index to use on the PL/SQL |
34 | table: location_segment_id. This is incremented by the trigger |
35 | AR_LOCATION_RATES_BRIU, and reset to zero by the triggers: |
36 | AR_LOCATION_RATES_BSIU, and AR_LOCATION_RATES_BRIU |
37 | |
38 | VARIABLE: location_segment_id |
39 | Used by the trigger on AR_LOCATION_RATES_BRIU and provides a table |
40 | of used location_segment_ids, which can then be populated on mass by |
41 | the trigger: AR_LOCATION_RATES_ASIU - this works around the database |
42 | restriction: Mutating tables. |
43 | |
44 | VARIABLE: empty_id_table |
45 | This public variable is used to release the memory of a populated |
46 | PL/SQL array once all of the rows have been processed. By assigning |
47 | this empty array to a used array, all of the elements in the used |
48 | array get deleted from memory. |
49 | |
50 +-------------------------------------------------------------------------*/
51
52
53 triggers_enabled BOOLEAN := TRUE;
54
55 transfer_rates_initialised BOOLEAN := FALSE;
56 transfer_rates_manual BOOLEAN := FALSE;
57
58 location_rates_transfer_id NUMBER;
59
60 location_segment_id TAB_ID_TYPE;
61
62 empty_id_table TAB_ID_TYPE;
63
64 loc_rate NUMBER;
65
66
67 /*-------------------------------------------------------------------------+
68 | |
69 | PUBLIC PROCEDURES |
70 | |
71 +-------------------------------------------------------------------------*/
72
73
74 PROCEDURE Purge_Sales_Tax ;
75
76 PROCEDURE Site_Use_Sales_Tax( address_id in number ) ;
77
78 PROCEDURE Initialise_Transfer_Rates;
79
80 PROCEDURE Implement_Transfer_Rates;
81
82 PROCEDURE Populate_Sales_Tax( statement_type in varchar2,
83 location_ccid in number,
84 p_location_id_segment_1 in number,
85 p_location_id_segment_2 in number,
86 p_location_id_segment_3 in number,
87 p_location_id_segment_4 in number,
88 p_location_id_segment_5 in number,
89 p_location_id_segment_6 in number,
90 p_location_id_segment_7 in number,
91 p_location_id_segment_8 in number,
92 p_location_id_segment_9 in number,
93 p_location_id_segment_10 in number ) ;
94
95 PROCEDURE Propogate_sales_tax;
96
97
98 PROCEDURE enable_triggers;
99
100 PROCEDURE disable_triggers;
101
102 PROCEDURE renumber_tax_lines( customer_trx_id in number,
103 trx_type in varchar2 default 'TAX' );
104
105 PROCEDURE combine_tax_rates;
106
107
108 /*------------------------------------------------------------------------+
109 | PUBLIC PROCEDURE |
110 | find_tax_exemption_id |
111 | |
112 | CALLED BY sales tax engine |
113 | |
114 | DESCRIPTION |
115 | Each transaction line may be forced exempt from taxes by the user. |
116 | When exempted; "find_tax_exemption_id" is called, passing in the |
117 | Exemption Certificate Number and Reason Code. If no "Unapproved", |
118 | "Manual" or "Primary" Exemption exists for this Customer, Location, |
119 | Tax Code and Reason this routine will optionally create an Automatic |
120 | exemption with a status of "Unapproved" and a location that matches |
121 | the flexfield qualifier: "EXEMPT_LEVEL". |
122 | |
123 | REQUIRES |
124 | BILL TO_CUSTOMER_ID Bill To Customer ID (mandatory) |
125 | SHIP_TO_CUSTOMER_ID Ship To Customer ID |
126 | SHIP_TO_SITE_ID Identifies the ship to site from which we |
127 | can deduce the State, County and City |
128 | Or other segments applicable to the sales |
129 | tax location flexfield. |
130 | INVENTORY_ITEM_ID Item exemptions are if found, used |
131 | TRX_DATE Tax Date for this transaction |
132 | TAX_CODE Tax Code for this transaction |
133 | TAX_EXEMPT_FLAG "S"tandard; "E"xempt or "R"equire |
134 | REASON_CODE Mandatory for all Exempt transactions |
135 | CERTIFICATE Optional, used in Exempt transactions |
136 | PERCENT_EXEMPT Exemption, Percentage of Tax that is |
137 | Exempt; or NULL if no Exemption is |
138 | applicable. |
139 | INSERT_ALLOWED If False and "E" is called but not |
140 | valid exemption is on file; this routine |
141 | will return an error. |
142 | RETURNS |
143 | TAX_EXEMPTION_ID Foreign Key to "RA_TAX_EXEMPTIONS" |
144 | If NULL, this transaction is NOT exempt |
145 | CERTIFICATE Certificate Number |
146 | REASON Reason Code for exemption |
147 | INSERTED_FLAG TRUE if this call forced an insert |
148 | EXEMPTION_TYPE CUSTOMER or ITEM |
149 | |
150 | DATABASE REQUIREMENTS |
151 | View: TAX_EXEMPTIONS_QP_V This view must be installed before |
152 | this database package can be installed |
153 | |
154 | MODIFICATION HISTORY |
155 | 17 May, 1994 Nigel Smith Created. |
156 | 3 Aug, 1994 Nigel Smith BUGFIX: 228807, Exemptions are now |
157 | managed by Bill To Customer and Ship |
158 | To Site. |
159 | |
160 +------------------------------------------------------------------------*/
161
162
163 PROCEDURE find_tax_exemption_id(
164 bill_to_customer_id in number,
165 ship_to_customer_id in number,
166 ship_to_site_id in number,
167 tax_code in varchar2,
168 inventory_item_id in number,
169 trx_date in date,
170 tax_exempt_flag in varchar2,
171 insert_allowed in varchar2 default 'TRUE',
172 reason_code in out NOCOPY varchar2,
173 certificate in out NOCOPY varchar2,
174 percent_exempt out NOCOPY number,
175 inserted_flag out NOCOPY varchar2,
176 tax_exemption_id out NOCOPY number,
177 exemption_type out NOCOPY varchar2
178
179 );
180
181 /*-------------------------------------------------------------------------+
182 | |
183 | PUBLIC FUNCTIONS |
184 | |
185 +-------------------------------------------------------------------------*/
186
187
188 FUNCTION ins_sales_tax( location_id in number,
189 location_structure_id in number,
190 total_tax_rate in number,
191 location1_rate in number,
192 location2_rate in number,
193 location3_rate in number,
194 location4_rate in number,
195 location5_rate in number,
196 location6_rate in number,
197 location7_rate in number,
198 location8_rate in number,
199 location9_rate in number,
200 location10_rate in number,
201 from_postal_code in varchar2,
202 to_postal_code in varchar2,
203 start_date in date,
204 end_date in date )
205 return number;
206
207 PROCEDURE populate_segment_array( loc_segment_id in number );
208
209 /*------------------------------------------------------------------------+
210 | PUBLIC PROCEDURE |
211 | close_open_cusor |
212 | |
213 | CALLED BY package arp_process_tax.calculate_tax_f_sql |
214 | |
215 | DESCRIPTION |
216 | Close all open cursor opened in this file before the end of tax |
217 | calculation. |
218 | |
219 +------------------------------------------------------------------------*/
220 PROCEDURE close_open_cursor;
221
222 END ARP_STAX;