1 package ARP_STAX_MINUS99 AUTHID DEFINER /*NOSYNC*/ AS
2 /* $Header: ARPLXSTX.txt 115.6 2004/04/30 11:34:24 rpalani noship $ */
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 | PUBLIC PROCEDURE |
109 | find_tax_exemption_id |
110 | |
111 | CALLED BY sales tax engine |
112 | |
113 | DESCRIPTION |
114 | Each transaction line may be forced exempt from taxes by the user. |
115 | When exempted; "find_tax_exemption_id" is called, passing in the |
116 | Exemption Certificate Number and Reason Code. If no "Unapproved", |
117 | "Manual" or "Primary" Exemption exists for this Customer, Location, |
118 | Tax Code and Reason this routine will optionally create an Automatic |
119 | exemption with a status of "Unapproved" and a location that matches |
120 | the flexfield qualifier: "EXEMPT_LEVEL". |
121 | |
122 | REQUIRES |
123 | BILL TO_CUSTOMER_ID Bill To Customer ID (mandatory) |
124 | SHIP_TO_CUSTOMER_ID Ship To Customer ID |
125 | SHIP_TO_SITE_ID Identifies the ship to site from which we |
126 | can deduce the State, County and City |
127 | Or other segments applicable to the sales |
128 | tax location flexfield. |
129 | INVENTORY_ITEM_ID Item exemptions are if found, used |
130 | TRX_DATE Tax Date for this transaction |
131 | TAX_CODE Tax Code for this transaction |
132 | TAX_EXEMPT_FLAG "S"tandard; "E"xempt or "R"equire |
133 | REASON_CODE Mandatory for all Exempt transactions |
134 | CERTIFICATE Optional, used in Exempt transactions |
135 | PERCENT_EXEMPT Exemption, Percentage of Tax that is |
136 | Exempt; or NULL if no Exemption is |
137 | applicable. |
138 | INSERT_ALLOWED If False and "E" is called but not |
139 | valid exemption is on file; this routine |
140 | will return an error. |
141 | RETURNS |
142 | TAX_EXEMPTION_ID Foreign Key to "RA_TAX_EXEMPTIONS" |
143 | If NULL, this transaction is NOT exempt |
144 | CERTIFICATE Certificate Number |
145 | REASON Reason Code for exemption |
146 | INSERTED_FLAG TRUE if this call forced an insert |
147 | EXEMPTION_TYPE CUSTOMER or ITEM |
148 | |
149 | DATABASE REQUIREMENTS |
150 | View: TAX_EXEMPTIONS_QP_V This view must be installed before |
151 | this database package can be installed |
152 | |
153 | MODIFICATION HISTORY |
154 | 17 May, 1994 Nigel Smith Created. |
155 | 3 Aug, 1994 Nigel Smith BUGFIX: 228807, Exemptions are now |
156 | managed by Bill To Customer and Ship |
157 | To Site. |
158 | |
159 +------------------------------------------------------------------------*/
160
161
162 PROCEDURE find_tax_exemption_id(
163 bill_to_customer_id in number,
164 ship_to_customer_id in number,
165 ship_to_site_id in number,
166 tax_code in varchar2,
167 inventory_item_id in number,
168 trx_date in date,
169 tax_exempt_flag in varchar2,
170 insert_allowed in varchar2 default 'TRUE',
171 reason_code in out NOCOPY varchar2,
172 certificate in out NOCOPY varchar2,
173 percent_exempt out NOCOPY number,
174 inserted_flag out NOCOPY varchar2,
175 tax_exemption_id out NOCOPY number,
176 exemption_type out NOCOPY varchar2
177
178 );
179
180 /*-------------------------------------------------------------------------+
181 | |
182 | PUBLIC FUNCTIONS |
183 | |
184 +-------------------------------------------------------------------------*/
185
186
187 FUNCTION ins_sales_tax( location_id in number,
188 location_structure_id in number,
189 total_tax_rate in number,
190 location1_rate in number,
191 location2_rate in number,
192 location3_rate in number,
193 location4_rate in number,
194 location5_rate in number,
195 location6_rate in number,
196 location7_rate in number,
197 location8_rate in number,
198 location9_rate in number,
199 location10_rate in number,
200 from_postal_code in varchar2,
201 to_postal_code in varchar2,
202 start_date in date,
203 end_date in date )
204 return number;
205
206 PROCEDURE populate_segment_array( loc_segment_id in number );
207
208 END ARP_STAX_MINUS99;
[Home] [Help]