[Home] [Help]
PACKAGE: APPS.OKL_PROCESS_SALES_TAX_PVT
Source
1 PACKAGE OKL_PROCESS_SALES_TAX_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKLRPSTS.pls 120.11 2007/08/27 22:36:55 rravikir ship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL MESSAGE CONSTANTS
5 ---------------------------------------------------------------------------
6 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
7 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
8 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
9 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
10 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
11 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
12 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
13 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
14 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
15 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
16
17 ---------------------------------------------------------------------------
18 -- GLOBAL CONSTANTS
19 ---------------------------------------------------------------------------
20 G_ESTIMATED_CALL_TYPE CONSTANT VARCHAR2(120) := 'ESTIMATED';
21 G_ACTUAL_CALL_TYPE CONSTANT VARCHAR2(120) := 'ACTUAL';
22
23 ---------------------------------------------------------------------------
24 -- GLOBAL VARIABLES
25 ---------------------------------------------------------------------------
26 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_PROCESS_SALES_TAX_PVT';
27 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
28
29 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
30 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
31
32 G_EVENT_CLASS_CODE CONSTANT VARCHAR2(30) := 'SALES_TRANSACTION_TAX_QUOTE';
33 G_TRX_LEVEL_TYPE CONSTANT VARCHAR2(30) := 'LINE';
34
35 G_ACTIVE_STATUS CONSTANT VARCHAR2(30) := 'ACTIVE';
36 G_INACTIVE_STATUS CONSTANT VARCHAR2(30) := 'INACTIVE';
37 G_CANCELLED_STATUS CONSTANT VARCHAR2(30) := 'CANCELLED';
38
39 G_UPFRONT_TAX CONSTANT VARCHAR2(30) := 'UPFRONT_TAX';
40 G_INVOICE_TAX CONSTANT VARCHAR2(30) := 'INVOICE_TAX';
41 G_TAX_SCHEDULE CONSTANT VARCHAR2(30) := 'TAX_SCHEDULE';
42
43 G_OKL_APPLICATION_ID CONSTANT NUMBER := 540;
44 G_UPDATE_LINE_LEVEL_ACTION CONSTANT VARCHAR2(30) := 'UPDATE';
45 G_CREATE_LINE_LEVEL_ACTION CONSTANT VARCHAR2(30) := 'CREATE';
46
47 G_BOOK_UPD_EVENT_CODE CONSTANT VARCHAR2(30) := 'BOOKING_UPDATE';
48 G_BOOKING_CRE_EVT_TYPE_CODE CONSTANT VARCHAR2(30) := 'BOOKING_CREATE';
49 G_BOOK_OVERRIDE_EVENT CONSTANT VARCHAR2(30) := 'BOOKING_OVERRIDE_TAX';
50 G_BOOK_DEL_EVENT_CODE CONSTANT VARCHAR2(30) := 'BOOKING_DELETE';
51 G_BOOKING_CANCEL CONSTANT VARCHAR2(30) := 'BOOKING_CANCEL';
52
53 G_REBOOK_UPD_EVENT_CODE CONSTANT VARCHAR2(30) := 'REBOOK_UPDATE';
54 G_REBOOK_OVERRIDE_EVENT CONSTANT VARCHAR2(30) := 'REBOOK_OVERRIDE_TAX';
55 G_REBOOK_CRE_EVT_TYPE_CODE CONSTANT VARCHAR2(30) := 'REBOOK_CREATE';
56 G_REBOOK_DEL_EVENT_CODE CONSTANT VARCHAR2(30) := 'REBOOK_DELETE';
57 G_REBOOK_CANCEL CONSTANT VARCHAR2(30) := 'REBOOK_CANCEL';
58
59 G_AM_QTE_CRE_EVT_TYPE_CODE CONSTANT VARCHAR2(30) := 'ESTIMATED_BILLING_CREATE';
60
61 G_ALC_EVENT_CODE CONSTANT VARCHAR2(30) := 'ASSET_RELOCATION';
62 G_ALC_CRE_EVENT_CODE CONSTANT VARCHAR2(30) := 'ASSET_RELOCATION_CREATE';
63 G_ALC_UPD_EVENT_CODE CONSTANT VARCHAR2(30) := 'ASSET_RELOCATION_UPDATE';
64 G_ALC_DEL_EVENT_CODE CONSTANT VARCHAR2(30) := 'ASSET_RELOCATION_DELETE';
65 G_ALC_OVERRIDE_EVENT CONSTANT VARCHAR2(30) := 'ASSET_RELOCATION_OVERRIDE_TAX';
66
67 G_OVERRIDE_LEVEL CONSTANT VARCHAR2(30) := 'DETAIL_OVERRIDE';
68
69 G_ASSETS_ENTITY_CODE CONSTANT VARCHAR2(30) := 'ASSETS';
70 G_CONTRACTS_ENTITY_CODE CONSTANT VARCHAR2(30) := 'CONTRACTS';
71 G_AM_QUOTES_ENTITY_CODE CONSTANT VARCHAR2(30) := 'AM_QUOTES';
72
73 G_TAX_SCH_ENTITY_CODE CONSTANT VARCHAR2(30) := 'TAX_SCHEDULE_REQUESTS';
74 G_TAX_SCH_CRE_EVT_TYPE_CODE CONSTANT VARCHAR2(30) := 'TAX_SCHEDULE_CREATE';
75
76 G_BOOKING_EVENT_CLASS_CODE CONSTANT VARCHAR2(30) := 'BOOKING';
77 G_REBOOK_EVENT_CLASS_CODE CONSTANT VARCHAR2(30) := 'REBOOK';
78 G_AM_QTE_EVENT_CLASS_CODE CONSTANT VARCHAR2(30) := 'ESTIMATED_BILLING';
79
80 G_SQ_ENTITY_CODE CONSTANT VARCHAR2(30) := 'SALES_QUOTES';
81 G_SQ_EVENT_CLASS_CODE CONSTANT VARCHAR2(30) := 'SALES_QUOTE';
82 G_SQ_CRE_EVT_TYPE_CODE CONSTANT VARCHAR2(30) := 'SALES_QUOTE_CREATE';
83
84 G_SERVICES CONSTANT VARCHAR2(30) := 'SERVICES';
85 G_GOODS CONSTANT VARCHAR2(30) := 'GOODS';
86 G_DEFAULT_PRODUCT_TYPE CONSTANT VARCHAR2(30) := 'SERVICES';
87
88 G_AR_APPLICATION_ID CONSTANT NUMBER := 222;
89 G_AR_ENTITY_CODE CONSTANT VARCHAR2(30) := 'TRANSACTIONS';
90 G_INVOICE_EVENT_CLASS_CODE CONSTANT VARCHAR2(30) := 'INVOICE';
91 G_CRE_MEM_EVENT_CLASS_CODE CONSTANT VARCHAR2(30) := 'CREDIT_MEMO';
92
93 G_AP_APPLICATION_ID CONSTANT NUMBER := 200;
94 G_AP_ENTITY_CODE CONSTANT VARCHAR2(30) := 'AP_INVOICES';
95 G_AP_EVENT_CLASS_CODE CONSTANT VARCHAR2(30) := 'STANDARD INVOICES';
96
97 ---------------------------------------------------------------------------
98 -- GLOBAL PROCESSING VARIABLES
99 ---------------------------------------------------------------------------
100 G_UFC_CODE VARCHAR2(240);
101 G_TBC_CODE VARCHAR2(240);
102 G_PC_CODE VARCHAR2(240);
103 G_TAX_CLASS_CODE VARCHAR2(50);
104
105 ---------------------------------------------------------------------------
106 -- GLOBAL DEBUG VARIABLES
107 ---------------------------------------------------------------------------
108 G_DEBUG_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
109 G_DEBUG_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
110 G_DEBUG_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
111 G_DEBUG_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
112 ---------------------------------------------------------------------------
113 -- GLOBAL EXCEPTIONS
114 ---------------------------------------------------------------------------
115 G_EXCEPTION_INSURANCE_ERROR EXCEPTION;
116
117 ---------------------------------------------------------------------------
118 -- GLOBAL DATASTRUCTURES
119 ---------------------------------------------------------------------------
120 SUBTYPE txsv_rec_type IS OKL_TAX_SOURCES_PUB.txsv_rec_type;
121 SUBTYPE txsv_tbl_type IS OKL_TAX_SOURCES_PUB.txsv_tbl_type;
122 SUBTYPE transaction_rec_type IS ZX_API_PUB.transaction_rec_type;
123 SUBTYPE legal_entity_rec_type IS XLE_UTILITIES_GRP.LegalEntity_Rec;
124 SUBTYPE zx_trx_lines_tbl_type IS OKL_TAX_INTERFACE_PVT.zx_trx_lines_tbl_type;
125 SUBTYPE tax_src_params_rec_type IS OKL_TAX_SOURCES%ROWTYPE;
126 SUBTYPE line_params_tbl_type IS OKL_TAX_INTERFACE_PVT.line_params_tbl_type;
127 TYPE tax_sources_tbl_type IS TABLE OF OKL_TAX_SOURCES%ROWTYPE INDEX BY BINARY_INTEGER;
128 SUBTYPE hdr_params_rec_type IS OKL_TAX_INTERFACE_PVT.hdr_params_rec_type;
129 SUBTYPE line_params_rec_type IS OKL_TAX_INTERFACE_PVT.line_params_rec_type;
130 TYPE tax_lines_tbl_type IS TABLE OF OKL_TAX_TRX_DETAILS%ROWTYPE INDEX BY BINARY_INTEGER;
131
132 ---------------------------------------------------------------------------
133 -- LOCAL DATASTRUCTURES
134 ---------------------------------------------------------------------------
135 TYPE prev_tx_src_rec_type IS RECORD
136 ( id NUMBER,
137 org_id NUMBER,
138 application_id NUMBER,
139 entity_code VARCHAR2(30),
140 event_class_code VARCHAR2(30));
141
142 TYPE sty_id_rec_type IS RECORD(
143 sty_id NUMBER,
144 sty_code VARCHAR2(150),
145 sty_purpose VARCHAR2(80));
146
147 TYPE tax_det_rec_type IS RECORD(
148 x_tax_code VARCHAR2(50), -- 'TAX_CLASSIFICATION_CODE' in AP interface and 'TAX_CODE' of AR Interface
149 x_trx_business_category VARCHAR2(240),
150 x_product_category VARCHAR2(240),
151 x_product_type VARCHAR2(240),
152 x_line_intended_use VARCHAR2(240),
153 x_user_defined_fisc_class VARCHAR2(240),
154 x_assessable_value NUMBER,
155 x_default_taxation_country VARCHAR2(2),
156 x_upstream_trx_reported_flag VARCHAR2(1));
157
158 TYPE tax_codes_rec_type IS RECORD (
159 khr_id NUMBER,
160 kle_id NUMBER,
161 sty_id NUMBER,
162 tbc_code VARCHAR2(240),
163 pc_code VARCHAR2(240),
164 ufc_code VARCHAR2(240),
165 tax_class_code VARCHAR2(240));
166
167 TYPE tax_codes_tbl_type IS TABLE OF tax_codes_rec_type INDEX BY BINARY_INTEGER;
168 TYPE sty_id_tbl_type IS TABLE OF sty_id_rec_type INDEX BY BINARY_INTEGER;
169 TYPE prev_tx_src_tbl_type IS TABLE OF prev_tx_src_rec_type INDEX BY BINARY_INTEGER;
170
171 TYPE asset_level_det_rec_type IS RECORD
172 (fin_asset_id NUMBER,
173 asset_number VARCHAR2(150),
174 transfer_of_title VARCHAR2(30),
175 sale_lease_back VARCHAR2(30),
176 purchase_of_lease VARCHAR2(30),
177 usage_of_equipment VARCHAR2(30),
178 vendor_site_id NUMBER,
179 age_of_equipment NUMBER,
180 inv_item_id NUMBER,
181 inv_org_id NUMBER,
182 ship_to_site_use_id NUMBER,
183 asset_pymnt_exist VARCHAR2(1),
184 bill_to_party_site_id NUMBER,
185 bill_to_location_id NUMBER,
186 bill_to_party_id NUMBER,
187 bill_to_site_use_id NUMBER,
188 ship_to_party_site_id NUMBER,
189 ship_to_location_id NUMBER,
190 ship_to_party_id NUMBER,
191 sty_id NUMBER,
192 amount NUMBER,
193 try_id NUMBER,
194 trx_line_id NUMBER );
195 TYPE asset_level_det_tbl_type IS TABLE OF asset_level_det_rec_type INDEX BY BINARY_INTEGER;
196
197 ---------------------------------------------------------------------------
198 -- PROCEDURES
199 ---------------------------------------------------------------------------
200 PROCEDURE calculate_sales_tax(
201 p_api_version IN NUMBER,
202 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2,
206 p_source_trx_id IN NUMBER,
207 p_source_trx_name IN VARCHAR2,
208 p_source_table IN VARCHAR2,
209 p_tax_call_type IN VARCHAR2 DEFAULT NULL,
210 p_serialized_asset IN VARCHAR2 DEFAULT NULL,
211 p_request_id IN NUMBER DEFAULT NULL,
212 p_alc_final_call IN VARCHAR2 DEFAULT NULL);
213
214 PROCEDURE get_billing_stream_types(
215 p_api_version IN NUMBER,
216 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2,
220 p_org_id IN NUMBER,
221 p_sty_code IN VARCHAR2,
222 x_sty_id_tbl OUT NOCOPY sty_id_tbl_type);
223
224 PROCEDURE set_tax_security_context(
225 p_api_version IN NUMBER,
226 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2,
230 p_internal_org_id IN NUMBER,
231 p_legal_entity_id IN NUMBER,
232 p_transaction_date IN DATE);
233
234 PROCEDURE process_tax_determ_override(
235 p_api_version IN NUMBER,
236 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
237 x_return_status OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 x_msg_data OUT NOCOPY VARCHAR2,
240 p_trx_id IN NUMBER,
241 p_tax_sources_id IN NUMBER,
242 p_trx_business_category IN VARCHAR2,
243 p_product_category IN VARCHAR2,
244 p_user_defined_fisc_class IN VARCHAR2,
245 p_line_intended_use IN VARCHAR2,
246 p_request_id IN NUMBER DEFAULT NULL,
247 p_asset_id IN NUMBER DEFAULT NULL);
248
249 PROCEDURE process_tax_details_override(
250 p_api_version IN NUMBER,
251 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY NUMBER,
254 x_msg_data OUT NOCOPY VARCHAR2,
255 p_event_id IN NUMBER,
256 p_internal_organization_id IN NUMBER,
257 p_trx_id IN NUMBER,
258 p_application_id IN NUMBER,
259 p_entity_code IN VARCHAR2,
260 p_event_class_code IN VARCHAR2);
261
262 PROCEDURE get_tax_determinants(
263 p_api_version IN NUMBER,
264 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_msg_count OUT NOCOPY NUMBER,
267 x_msg_data OUT NOCOPY VARCHAR2,
268 p_source_trx_id IN NUMBER,
269 p_source_trx_name IN VARCHAR2,
270 p_source_table IN VARCHAR2,
271 x_tax_det_rec OUT NOCOPY tax_det_rec_type);
272
273 PROCEDURE get_location_party_ids(
274 p_api_version IN NUMBER,
275 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2,
279 p_cust_acct_id IN NUMBER,
280 p_fin_asset_id IN NUMBER,
281 p_khr_id IN NUMBER,
282 x_bill_to_party_site_id OUT NOCOPY NUMBER,
283 x_bill_to_location_id OUT NOCOPY NUMBER,
284 x_bill_to_party_id OUT NOCOPY NUMBER,
285 x_bill_to_site_use_id OUT NOCOPY NUMBER,
286 x_ship_to_party_site_id OUT NOCOPY NUMBER,
287 x_ship_to_location_id OUT NOCOPY NUMBER,
288 x_ship_to_party_id OUT NOCOPY NUMBER,
289 x_ship_to_site_use_id OUT NOCOPY NUMBER);
290
291 PROCEDURE cancel_document_tax (
292 p_api_version IN NUMBER,
293 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
294 x_return_status OUT NOCOPY VARCHAR2,
295 x_msg_count OUT NOCOPY NUMBER,
296 x_msg_data OUT NOCOPY VARCHAR2,
297 p_source_trx_id IN NUMBER, --> ID of Pre-Rebook or of Rebook transaction in okl_trx_contracts
298 p_source_trx_name IN VARCHAR2, --> Pre-Rebook or Rebook
299 p_source_table IN VARCHAR2);
300
301 PROCEDURE copy_global_tax_data (
302 p_api_version IN NUMBER,
303 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2,
307 p_trx_id IN NUMBER,
308 p_trx_line_id IN NUMBER,
309 p_application_id IN NUMBER,
310 p_trx_level_type IN VARCHAR2,
311 p_entity_code IN VARCHAR2,
312 p_event_class_code IN VARCHAR2);
313
314 FUNCTION get_default_taxation_country(x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2,
317 p_legal_entity_id IN NUMBER)
318 RETURN VARCHAR2;
319
320 FUNCTION get_line_intended_use_name(p_intend_use_code IN VARCHAR2)
321 RETURN VARCHAR2;
322
323 FUNCTION is_serialized_and_alc(p_contract_id IN NUMBER)
324 RETURN VARCHAR2;
325
326 END OKL_PROCESS_SALES_TAX_PVT;