[Home] [Help]
PACKAGE BODY: APPS.ZX_TAXWARE_USER_PKG
Source
1 PACKAGE BODY ZX_TAXWARE_USER_PKG AS
2 /* $Header: zxtxwuserpkgb.pls 120.49.12020000.2 2012/12/21 00:56:13 ssanka ship $ */
3
4 /* ======================================================================*
5 | Global Data Types |
6 * ======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_TAXWARE_USER_PKG';
9 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
10 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
12 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
13 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
14 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
15 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
16 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.PLSQL.ZX_TAXWARE_USER_PKG.';
17
18 g_usenexpro VARCHAR2(100);
19 g_taxselparam NUMBER;
20 --g_taxtype NUMBER;
21 g_serviceind NUMBER;
22
23 g_string VARCHAR2(80);
24 l_view_name VARCHAR2(200);
25 i PLS_INTEGER;
26 g_in_out_flag VARCHAR2(1); -- Bug 5506031
27 g_ship_from_party_id ZX_PARTY_TAX_PROFILE.PARTY_ID%TYPE;
28 g_org_id RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE;
29 g_sales_repid RA_CUSTOMER_TRX_ALL.primary_salesrep_id%TYPE;
30
31 TYPE party_id_tbl_type is table of NUMBER index by VARCHAR2(100);
32 p_party_id_tbl party_id_tbl_type;
33
34 TYPE party_number_tbl_type is TABLE OF VARCHAR2(150) index by VARCHAR2(100);
35 p_party_number_tbl party_number_tbl_type;
36
37 TYPE poa_add_code_tbl_type is TABLE OF VARCHAR2(150) index by VARCHAR2(100);
38 poa_add_code_cache_tbl poa_add_code_tbl_type;
39
40
41 /***********Declaration of private procedures*************/
42 PROCEDURE POPULATE_EXEMPTION_DETAILS
43 (
44 p_bill_to_site_use IN ZX_LINES_DET_FACTORS.BILL_TO_CUST_ACCT_SITE_USE_ID%TYPE,
45 p_bill_to_location_id IN ZX_LINES_DET_FACTORS.BILL_TO_LOCATION_ID%TYPE,
46 p_bill_to_party_tax_id IN ZX_LINES_DET_FACTORS.BILL_TO_PARTY_TAX_PROF_ID%TYPE,
47 p_bill_to_site_tax_prof IN ZX_LINES_DET_FACTORS.TRADING_HQ_SITE_TAX_PROF_ID%TYPE,
48 p_hq_site_tax_prof_id IN ZX_LINES_DET_FACTORS.TRADING_HQ_SITE_TAX_PROF_ID%TYPE,
49 p_hq_party_tax_prof_id_tab IN ZX_LINES_DET_FACTORS.TRADING_HQ_PARTY_TAX_PROF_ID%TYPE,
50 p_bill_third_pty_acct_id IN ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_ID%TYPE,
51 p_product_org_id IN ZX_LINES_DET_FACTORS.PRODUCT_ORG_ID%TYPE,
52 p_product_id IN ZX_LINES_DET_FACTORS.product_id%TYPE,
53 p_cert_num IN ZX_LINES_DET_FACTORS.exempt_certificate_number%TYPE,
54 p_exmpt_rsn_code IN ZX_LINES_DET_FACTORS.exempt_reason_code%TYPE,
55 p_exemption_control_flag IN ZX_LINES_DET_FACTORS.Exemption_Control_Flag%TYPE,
56 p_tax_regime_code IN ZX_TRX_PRE_PROC_OPTIONS_GT.Tax_Regime_Code%TYPE,
57 p_position IN NUMBER,
58 p_error_status OUT NOCOPY VARCHAR2
59 );
60
61 PROCEDURE derive_view_name
62 (
63 p_application_id IN ZX_LINES_DET_FACTORS.APPLICATION_ID%TYPE,
64 p_event_class_code IN ZX_LINES_DET_FACTORS.EVENT_CLASS_CODE%TYPE,
65 p_api_name IN VARCHAR2,
66 p_adjusted_doc_trx_id IN ZX_LINES_DET_FACTORS.ADJUSTED_DOC_TRX_ID%TYPE,
67 p_line_level_action IN ZX_LINES_DET_FACTORS.LINE_LEVEL_ACTION%TYPE,
68 x_view_name OUT NOCOPY VARCHAR2
69 );
70 PROCEDURE Initialize_Nested_Tables ;
71 PROCEDURE Initialize_Exemption_Tables;
72 /* Bug 4668932 */
73 FUNCTION CHECK_GEOCODE(p_geocode IN VARCHAR2) RETURN BOOLEAN;
74 /* Bug 4668932 */
75
76 PROCEDURE derive_trx_level_attr;
77 PROCEDURE derive_product_code;
78 PROCEDURE derive_audit_flag;
79 PROCEDURE derive_ship_to_address_code;
80 PROCEDURE derive_ship_from_address_code;
81 PROCEDURE derive_poa_address_code;
82 PROCEDURE derive_poo_address_code;
83 PROCEDURE derive_customer_code;
84 PROCEDURE derive_customer_name;
85 PROCEDURE derive_division_code;
86 PROCEDURE derive_transaction_date;
87 PROCEDURE derive_company_code;
88 PROCEDURE derive_vnd_ctrl_exmpt;
89 PROCEDURE derive_use_nexpro;
90 PROCEDURE derive_service_ind;
91 PROCEDURE derive_tax_sel_param;
92 PROCEDURE derive_calculation_flag;
93
94 PROCEDURE ERROR_EXCEPTION_HANDLE(str varchar2);
95
96 /*PUBLIC PROCEDURE DEFINITIONS START HERE*/
97 /*===========================================================================+
98 | PROCEDURE
99 | Derive_Hdr_Ext_Attr
100 | IN
101 |
102 | OUT NOCOPY
103 |
104 | DESCRIPTION
105 | This routine contains the necessary logic to populate header_level user extensible
106 | attributes into ZX_PRVDR_HDR_EXTNS_GT.
107 |
108 | SCOPE - PUBLIC
109 |
110 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
111 |
112 | CALLED FROM
113 |
114 |
115 | MODIFICATION HISTORY
116 | 08/13/2004 Arnab Sengupta Created.
117 |
118 +==========================================================================*/
119 PROCEDURE Derive_Hdr_Ext_Attr( x_error_status OUT NOCOPY VARCHAR2
120 , x_messages_tbl OUT NOCOPY ZX_TAX_PARTNER_PKG.messages_tbl_type) IS
121
122 l_header_ext_attr1 ZX_PRVDR_HDR_EXTNS_GT.HEADER_EXT_VARCHAR_ATTRIBUTE1%TYPE ;
123 l_event_class_code ZX_LINES_DET_FACTORS.Event_Class_Code%TYPE;
124 l_application_id ZX_LINES_DET_FACTORS.Application_Id%TYPE;
125 l_entity_code ZX_LINES_DET_FACTORS.Entity_Code%TYPE;
126 l_trx_id ZX_LINES_DET_FACTORS.Trx_Id%TYPE;
127 l_tax_provider_id ZX_TRX_PRE_PROC_OPTIONS_GT.Tax_Provider_Id%TYPE;
128 l_tax_regime_code ZX_TRX_PRE_PROC_OPTIONS_GT.Tax_Regime_Code%TYPE;
129 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_HDR_EXT_ATTR';
130 l_exists_in_hdrs_gt NUMBER;
131
132
133 BEGIN
134
135 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
137 END IF;
138
139 /*Set the return status to Success */
140 x_error_status := FND_API.G_RET_STS_SUCCESS;
141
142 BEGIN
143 SELECT
144 ZX_DET_FACT.EVENT_CLASS_CODE ,
145 ZX_DET_FACT.APPLICATION_ID ,
146 ZX_DET_FACT.ENTITY_CODE ,
147 ZX_DET_FACT.TRX_ID ,
148 ZX_PRE_REC_OPT.TAX_PROVIDER_ID ,
149 ZX_PRE_REC_OPT.TAX_REGIME_CODE ,
150 ZX_DET_FACT.TRX_DATE ,
151 ZX_DET_FACT.RECEIVABLES_TRX_TYPE_ID
152 INTO
153 l_event_class_code,
154 l_application_id,
155 l_entity_code,
156 l_trx_id,
157 l_tax_provider_id,
158 l_tax_regime_code,
159 g_trx_date,
160 g_trx_type_id
161 FROM
162 ZX_LINES_DET_FACTORS ZX_DET_FACT ,
163 ZX_USER_PROC_INPUT_V ZX_PRE_REC_OPT
164 WHERE
165 ZX_DET_FACT.INTERNAL_ORGANIZATION_ID = ZX_PRE_REC_OPT.INTERNAL_ORGANIZATION_ID
166 AND ZX_DET_FACT.APPLICATION_ID = ZX_PRE_REC_OPT.APPLICATION_ID
167 AND ZX_DET_FACT.EVENT_CLASS_CODE = ZX_PRE_REC_OPT.EVENT_CLASS_CODE
168 AND ZX_DET_FACT.ENTITY_CODE = ZX_PRE_REC_OPT.ENTITY_CODE
169 AND ZX_DET_FACT.TRX_ID = ZX_PRE_REC_OPT.TRX_ID
170 AND ROWNUM = 1;
171 EXCEPTION WHEN OTHERS THEN
172 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
173 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
174 END IF;
175 x_error_status := FND_API.G_RET_STS_UNEXP_ERROR;
176 g_string :='No Header information present';
177 error_exception_handle(g_string);
178 x_messages_tbl:=g_messages_tbl;
179 return;
180 END;
181
182 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
183 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
184 ' Event Class code = ' || l_event_class_code);
185 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
186 ' Application Id = ' || to_char(l_application_id));
187 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
188 ' Entity code = ' || l_entity_code);
189 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
190 ' l_trx_id = ' || to_char(l_trx_id));
191 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
192 ' l_tax_provider_id = ' || to_char(l_tax_provider_id));
193 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
194 ' Tax Regime code = ' || l_tax_regime_code);
195 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
196 ' g_trx_date = ' || to_char(g_trx_date));
197 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
198 ' g_trx_type_id = ' || to_char(g_trx_type_id));
199 END IF;
200
201 l_exists_in_hdrs_gt := 0;
202 BEGIN
203 SELECT 1
204 INTO l_exists_in_hdrs_gt
205 FROM ZX_PRVDR_HDR_EXTNS_GT
206 WHERE event_class_code = l_event_class_code
207 AND application_id = l_application_id
208 AND entity_code = l_entity_code
209 AND trx_id = l_trx_id
210 AND provider_id = l_tax_provider_id
211 AND tax_regime_code = l_tax_regime_code
212 AND rownum = 1;
213 EXCEPTION WHEN OTHERS THEN
214 l_exists_in_hdrs_gt := 0;
215 END;
216
217 IF l_exists_in_hdrs_gt = 0 THEN
218 BEGIN
219 INSERT INTO
220 ZX_PRVDR_HDR_EXTNS_GT
221 (
222 EVENT_CLASS_CODE,
223 APPLICATION_ID,
224 ENTITY_CODE,
225 TRX_ID ,
226 PROVIDER_ID ,
227 TAX_REGIME_CODE,
228 HEADER_EXT_VARCHAR_ATTRIBUTE1,
229 CREATION_DATE ,
230 CREATED_BY ,
231 LAST_UPDATE_DATE,
232 LAST_UPDATED_BY ,
233 LAST_UPDATE_LOGIN
234 )
235
236 VALUES(
237 l_Event_Class_Code,
238 l_Application_Id,
239 l_Entity_Code,
240 l_Trx_Id,
241 l_Tax_Provider_Id ,
242 l_Tax_Regime_Code,
243 l_header_ext_attr1,
244 SYSDATE,
245 fnd_global.user_id ,
246 SYSDATE ,
247 fnd_global.user_id ,
248 fnd_global.conc_login_id);
249 EXCEPTION
250 WHEN OTHERS THEN
251 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
252 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
253 END IF;
254 x_error_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 g_string :='Not able to insert in to ZX_PRVDR_HDR_EXTNS_GT table ';
256 x_messages_tbl:=g_messages_tbl;
257 error_exception_handle(g_string);
258 return;
259 END;
260 END IF;
261
262 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
263 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
264 ' RETURN_STATUS = ' || x_error_status);
265 END IF;
266 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
267 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||':'||l_api_name||'()-');
268 END IF;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
273 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||':'||l_api_name,SQLERRM);
274 END IF;
275 x_error_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 g_string :='Failed in DERIVE_HDR_EXT_ATTR procedure';
277 error_exception_handle(g_string);
278 x_messages_tbl:=g_messages_tbl;
279 return;
280
281 END Derive_Hdr_Ext_Attr;
282
283
284
285 /*===========================================================================+
286 | PROCEDURE
287 | Derive_Line_Ext_Attr
288 | IN
289 |
290 | OUT NOCOPY
291 |
292 | DESCRIPTION
293 | This routine contains the necessary logic to populate header_level user extensible
294 | attributes into ZX_PRVDR_LINE_EXTNS_GT.
295 |
296 | SCOPE - PUBLIC
297 |
298 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED/PRIVATE PROCEDURES ACCESSED
299 | POPULATE_EXEMPTION_DETAILS --Private Procedure
300 | DERIVE_VIEW_NAME --Private Procedure
301 |
302 | CALLED FROM
303 |
304 |
305 | MODIFICATION HISTORY
306 | 08/13/2004 Arnab Sengupta Created.
307 |
308 +==========================================================================*/
309
310 PROCEDURE Derive_Line_Ext_Attr( x_error_status OUT NOCOPY VARCHAR2
311 , x_messages_tbl OUT NOCOPY ZX_TAX_PARTNER_PKG.messages_tbl_type) IS
312
313
314 --This is the main driver cursor for fetching all records from the ZX_LINES_DET_FACTORS
315 --to populate into the ZX_PRVDR_LINE_EXTNS_GT table
316
317
318 CURSOR PROC_LINE_CSR IS
319 SELECT
320 ZX_LINE_DET_FACT.INTERNAL_ORGANIZATION_ID ,
321 ZX_LINE_DET_FACT.APPLICATION_ID ,
322 ZX_LINE_DET_FACT.ENTITY_CODE ,
323 ZX_LINE_DET_FACT.EVENT_CLASS_CODE ,
324 ZX_LINE_DET_FACT.TRX_ID ,
325 ZUPI.TAX_PROVIDER_ID ,
326 ZUPI.TAX_REGIME_CODE ,
327 ZX_LINE_DET_FACT.TRX_LEVEL_TYPE ,
328 ZX_LINE_DET_FACT.TRX_LINE_ID ,
329 ZX_LINE_DET_FACT.PRODUCT_ID ,
330 ZX_LINE_DET_FACT.PRODUCT_ORG_ID ,
331 ZX_LINE_DET_FACT.SHIP_To_PARTY_TAX_PROF_ID ,
332 ZX_LINE_DET_FACT.SHIP_FROM_PARTY_TAX_PROF_ID ,
333 ZX_LINE_DET_FACT.EXEMPT_CERTIFICATE_NUMBER ,
334 ZX_LINE_DET_FACT.EXEMPT_REASON_CODE ,
335 ZX_LINE_DET_FACT.EXEMPTION_CONTROL_FLAG ,
336 ZX_LINE_DET_FACT.SHIP_TO_SITE_TAX_PROF_ID ,
337 ZX_LINE_DET_FACT.SHIP_TO_LOCATION_ID ,
338 ZX_LINE_DET_FACT.SHIP_TO_CUST_ACCT_SITE_USE_ID ,
339 ZX_LINE_DET_FACT.BILL_TO_CUST_ACCT_SITE_USE_ID ,
340 ZX_LINE_DET_FACT.BILL_TO_SITE_TAX_PROF_ID ,
341 ZX_LINE_DET_FACT.BILL_TO_PARTY_TAX_PROF_ID ,
342 ZX_LINE_DET_FACT.BILL_TO_LOCATION_ID ,
343 ZX_LINE_DET_FACT.TRADING_HQ_SITE_TAX_PROF_ID ,
344 ZX_LINE_DET_FACT.TRADING_HQ_PARTY_TAX_PROF_ID ,
345 ZX_LINE_DET_FACT.BILL_THIRD_PTY_ACCT_ID ,
346 ZX_LINE_DET_FACT.LINE_LEVEL_ACTION ,
347 ZX_LINE_DET_FACT.ADJUSTED_DOC_TRX_ID ,
348 ZX_LINE_DET_FACT.LINE_AMT ,
349 ZX_LINE_DET_FACT.ADJUSTED_DOC_APPLICATION_ID ,
350 ZX_LINE_DET_FACT.ADJUSTED_DOC_ENTITY_CODE ,
351 ZX_LINE_DET_FACT.ADJUSTED_DOC_EVENT_CLASS_CODE ,
352 ZX_LINE_DET_FACT.ADJUSTED_DOC_LINE_ID ,
353 ZX_LINE_DET_FACT.RECEIVABLES_TRX_TYPE_ID ,
354 ZX_LINE_DET_FACT.ADJUSTED_DOC_TRX_LEVEL_TYPE ,
355 ZX_LINE_DET_FACT.SHIP_THIRD_PTY_ACCT_SITE_ID ,
356 ZX_LINE_DET_FACT.BILL_THIRD_PTY_ACCT_SITE_ID
357 FROM
358 ZX_LINES_DET_FACTORS ZX_LINE_DET_FACT ,
359 ZX_USER_PROC_INPUT_V ZUPI ,
360 ZX_TRX_PRE_PROC_OPTIONS_GT ZTPPO
361 WHERE
362 ZX_LINE_DET_FACT.INTERNAL_ORGANIZATION_ID = ZUPI.INTERNAL_ORGANIZATION_ID
363 AND ZX_LINE_DET_FACT.APPLICATION_ID = ZUPI.APPLICATION_ID
364 AND ZX_LINE_DET_FACT.EVENT_CLASS_CODE = ZUPI.EVENT_CLASS_CODE
365 AND ZX_LINE_DET_FACT.ENTITY_CODE = ZUPI.ENTITY_CODE
366 AND ZX_LINE_DET_FACT.TRX_ID = ZUPI.TRX_ID
367 AND ZX_LINE_DET_FACT.INTERNAL_ORGANIZATION_ID = ZTPPO.INTERNAL_ORGANIZATION_ID
368 AND ZX_LINE_DET_FACT.APPLICATION_ID = ZTPPO.APPLICATION_ID
369 AND ZX_LINE_DET_FACT.EVENT_CLASS_CODE = ZTPPO.EVENT_CLASS_CODE
370 AND ZX_LINE_DET_FACT.ENTITY_CODE = ZTPPO.ENTITY_CODE
371 AND ZX_LINE_DET_FACT.TRX_ID = ZTPPO.TRX_ID
372 AND ((ZX_LINE_DET_FACT.EVENT_ID = ZTPPO.EVENT_ID)
373 OR (ZX_LINE_DET_FACT.LINE_LEVEL_ACTION = 'DELETE'));
374
375
376 --This is the cursor for fetching all records from the ZX_USR_PROC_NEG_LINE_V.
377 --The specific purpose of this view is to retain old data from zx_lines_det_factors
378 --to populate into the ZX_PRVDR_LINE_EXTNS_GT table
379
380 CURSOR PROC_NEG_LINE_CSR IS
381 SELECT
382 Internal_Organization_Id ,
383 Application_Id,
384 Entity_Code,
385 Event_Class_Code,
386 Trx_Id,
387 Tax_Provider_Id,
388 Tax_Regime_Code,
389 Trx_Line_Type,
390 Trx_Line_Id,
391 Product_Id,
392 Product_Org_Id,
393 Ship_To_Party_Tax_Profile_Id,
394 Ship_From_Party_Tax_Profile_Id,
395 Exempt_Certificate_Number,
396 Exempt_Reason_Code,
397 Exemption_Control_Flag,
398 Ship_To_Site_Tax_Prof_Id,
399 Ship_To_Location_Id,
400 Ship_To_Cust_Acct_Site_Use_Id,
401 Bill_To_Cust_Acct_Site_Use_Id,
402 Bill_To_Site_Tax_Prof_Id,
403 Bill_To_Party_Tax_Prof_Id,
404 Bill_To_Location_Id,
405 Trading_Hq_Site_Tax_Prof_Id,
406 Trading_Hq_Party_Tax_Prof_Id,
407 Bill_Third_Pty_Acct_Id,
408 Line_Level_Action,
409 Adjusted_Doc_Trx_Id,
410 Line_Amt,
411 adjusted_doc_application_id,
412 adjusted_doc_entity_code,
413 adjusted_doc_event_class_code,
414 adjusted_doc_trx_line_id,
415 Receivables_Trx_Type_Id,
416 Adjusted_Doc_Trx_Level_Type,
417 Ship_Third_Pty_Acct_Site_Id,
418 Bill_Third_Pty_Acct_Site_Id
419 FROM
420 ZX_USR_PROC_NEG_LINE_V zxproc
421 WHERE
422 zxproc.trx_line_id = g_trx_line_id;
423
424
425
426
427 --Other local variable declarations
428
429 x_exemption_record ZX_TCM_GET_EXEMPT_PKG.exemption_rec_type;
430 x_ret_status VARCHAR2(30);
431 l_errors PLS_INTEGER;
432 l_product_id ZX_LINES_DET_FACTORS.product_id%TYPE;
433 l_memo_line_id NUMBER;
434 l_is_view_name_derived VARCHAR2(1);
435 --l_view_name VARCHAR2(200); --Commented for the bug#6723111
436 l_srv_name VARCHAR2(100);
437 l_is_sales_repid_derived VARCHAR2(1);
438 l_ship_from_party_id ZX_PARTY_TAX_PROFILE.PARTY_ID%TYPE;
439 --i PLS_INTEGER;
440 j PLS_INTEGER;
441
442 l_master_org_id oe_system_parameters_all.master_organization_id%type;
443 /* Bug 4668932 */
444 l_tax_jurisdiction_rec ZX_TCM_GEO_JUR_PKG.tax_jurisdiction_rec_type;
445 l_jurisdictions_found VARCHAR2(1);
446 l_ship_to_geocode VARCHAR2(30);
447 row_count number;
448 /* Bug 4668932 */
449
450 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_LINE_EXT_ATTR';
451 BEGIN
452 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
453 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
454 END IF;
455
456 /*Set the return status to Success */
457 x_error_status := FND_API.G_RET_STS_SUCCESS;
458
459 --Initialiaze all the nested tables
460 Initialize_Nested_Tables;
461
462
463 --Open the cursor and fetch all records into the nested tables
464 --Doing a bulk fetch here
465
466 l_is_view_name_derived := 'N'; --Initializing the flag
467 l_is_sales_repid_derived := 'N'; --Initializing the flag
468 l_srv_name := ZX_API_PUB.G_PUB_SRVC;
469
470 IF g_line_negation
471 THEN
472 OPEN PROC_NEG_LINE_CSR;
473
474 FETCH PROC_NEG_LINE_CSR
475 BULK COLLECT INTO
476 internal_org_id_tab,
477 application_id_tab,
478 entity_code_tab,
479 event_class_code_tab,
480 trx_id_tab,
481 tax_provider_id_tab,
482 tax_regime_code_tab,
483 trx_line_type_tab,
484 trx_line_id_tab,
485 product_id_tab,
486 Product_Org_Id_tab,
487 ship_to_tx_id_tab,
488 ship_from_tx_id_tab,
489 cert_num_tab,
490 exmpt_rsn_code_tab,
491 exemption_control_flag_tab,
492 ship_to_site_tax_prof_tab,
493 ship_to_loc_id_tab ,
494 ship_to_site_use_tab,
495 bill_to_site_use_tab,
496 bill_to_site_tax_prof_tab,
497 bill_to_party_tax_id_tab,
498 bill_to_location_id_tab,
499 trad_hq_site_tax_prof_id_tab,
500 trad_hq_party_tax_prof_id_tab,
501 bill_third_pty_acct_id_tab,
502 line_level_action_tab,
503 adjusted_doc_trx_id_tab,
504 line_amount_tab,
505 adj_doc_appl_id_tab,
506 adj_doc_entity_code_tab,
507 adj_evnt_cls_code_tab,
508 adj_doc_line_id_tab,
509 trx_type_id_tab,
510 adj_doc_trx_level_type_tab,
511 ship_third_pty_site_tab,
512 bill_third_pty_site_tab;
513
514 CLOSE PROC_NEG_LINE_CSR;
515
516
517
518 ELSE
519 OPEN PROC_LINE_CSR;
520
521 FETCH PROC_LINE_CSR
522 BULK COLLECT INTO
523 internal_org_id_tab,
524 application_id_tab,
525 entity_code_tab,
526 event_class_code_tab,
527 trx_id_tab,
528 tax_provider_id_tab,
529 tax_regime_code_tab,
530 trx_line_type_tab,
531 trx_line_id_tab,
532 product_id_tab,
533 Product_Org_Id_tab,
534 ship_to_tx_id_tab,
535 ship_from_tx_id_tab,
536 cert_num_tab,
537 exmpt_rsn_code_tab,
538 exemption_control_flag_tab,
539 ship_to_site_tax_prof_tab,
540 ship_to_loc_id_tab ,
541 ship_to_site_use_tab,
542 bill_to_site_use_tab,
543 bill_to_site_tax_prof_tab,
544 bill_to_party_tax_id_tab,
545 bill_to_location_id_tab,
546 trad_hq_site_tax_prof_id_tab,
547 trad_hq_party_tax_prof_id_tab,
548 bill_third_pty_acct_id_tab,
549 line_level_action_tab,
550 adjusted_doc_trx_id_tab,
551 line_amount_tab,
552 adj_doc_appl_id_tab,
553 adj_doc_entity_code_tab,
554 adj_evnt_cls_code_tab,
555 adj_doc_line_id_tab,
556 trx_type_id_tab,
557 adj_doc_trx_level_type_tab,
558 ship_third_pty_site_tab,
559 bill_third_pty_site_tab;
560
561 row_count:=PROC_LINE_CSR%rowcount;
562 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
563 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
564 ' row_count := '||row_count);
565 END IF;
566
567 CLOSE PROC_LINE_CSR;
568
569
570
571 END IF;
572
573 Initialize_Exemption_Tables;
574
575
576 /*This call is exclusively used to determine the exemption_id for the given
577 set of values fetched via the cursor.The exemption_id is collected into
578 a variable x_exemption_record from which the exemption_id is obtained*/
579
580 FOR line_cntr in 1..nvl(internal_org_id_tab.last,0) --Loop 1
581 LOOP
582
583 i := line_cntr;
584
585 BEGIN
586 IF ship_from_tx_id_tab(i) IS NOT NULL
587 THEN
588 SELECT party_id INTO l_ship_from_party_id
589 FROM ZX_PARTY_TAX_PROFILE
590 WHERE party_tax_profile_id = ship_from_tx_id_tab(i);
591 END IF;
592
593 EXCEPTION WHEN NO_DATA_FOUND
594 THEN
595 l_ship_from_party_id := NULL;
596 END;
597
598 IF line_amount_tab(i) = 0 THEN -- 10411260
599 use_step_tab(i) := 'Y';
600 step_proc_flag_tab(i) := '1';
601 crit_flag_tab(i) := 'R';
602 END IF;
603
604 IF event_class_code_tab(i) <> 'INVOICE_ADJUSTMENT' AND line_amount_tab(i) <> 0
605 THEN
606
607 /* Bug Number: 6328797 - According to 11i , The exemptions should work at
608 -> Ship_To For Customer Site level.
609 -> Bill_to For Customer Level
610 So we need to pass ship_to_loc_id_tab for finding the exemptions.
611 But previously we were passing bill_to_location_id_tab to find the exemptions.
612 So, chaged passing variable to ship_to_loc_id_tab.
613
614 But to avoid the structure changes, we are not changing the Naming Convention.
615 If we change the Structure Chages we need to do in lot of packages.
616 So we are keeping the name as bill_to only but changing the passing value.
617 */
618
619 POPULATE_EXEMPTION_DETAILS
620 (
621 p_bill_to_site_use => NVL(ship_to_site_use_tab(i),bill_to_site_use_tab(i)),
622 p_bill_to_location_id => NVL(ship_to_loc_id_tab(i),bill_to_location_id_tab(i)),
623 p_bill_to_party_tax_id => bill_to_party_tax_id_tab(i),
624 p_bill_to_site_tax_prof => NVL(ship_to_site_tax_prof_tab(i),bill_to_site_tax_prof_tab(i)),
625 p_hq_site_tax_prof_id => trad_hq_site_tax_prof_id_tab(i),
626 p_hq_party_tax_prof_id_tab => trad_hq_party_tax_prof_id_tab(i),
627 p_bill_third_pty_acct_id => bill_third_pty_acct_id_tab(i),
628 p_product_org_id => product_org_id_tab(i),
629 p_product_id => product_id_tab(i),
630 p_cert_num => cert_num_tab(i),
631 p_exmpt_rsn_code => exmpt_rsn_code_tab(i),
632 p_exemption_control_flag => exemption_control_flag_tab(i),
633 p_tax_regime_code => tax_regime_code_tab(i),
634 p_position => i,
635 p_error_status => x_ret_status
636 ) ;
637 NULL;
638 END IF;
639
640 IF x_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
641 IF (g_level_exception >= g_current_runtime_level ) THEN
642 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
643 END IF;
644 x_error_status := FND_API.G_RET_STS_UNEXP_ERROR;
645 g_string :='Failed with error in procedure POPULATE_EXEMPTION_DETAILS';
646 error_exception_handle(g_string);
647 x_messages_tbl:=g_messages_tbl;
648 return;
649 END IF;
650
651 /*The next portion of the code deals with the view name derivation based on
652 application_id , event_class_code and api name .This procedure returns
653 the view name as an out parameter which is used successively .This needs
654 to be called just once as view name is a document level property association.*/
655
656
657 /**NOTE** The usage of the flag l_is_view_derived is purely to execute the derive_view_name
658 procedure just once as view name is a Document level attribute and hence need not
659 be derived for every line */
660
661 IF l_is_view_name_derived = 'N' THEN --Initially the flag is set to 'N'
662 DERIVE_VIEW_NAME
663 (p_application_id => application_id_tab(i),
664 p_event_class_code => event_class_code_tab(i),
665 p_api_name => l_srv_name,
666 p_adjusted_doc_trx_id => adjusted_doc_trx_id_tab(i),
667 p_line_level_action => line_level_action_tab(i),
668 x_view_name => l_view_name);
669 l_is_view_name_derived := 'Y'; --Once first execution is complete set flag to 'Y' to
670 --prevent re-execution of DERIVE_VIEW_NAME for successive lines
671 derive_trx_level_attr;
672 END IF;
673
674
675 /*This portion of the code is used to collect the values from the user extensible
676 procedures into nested tables based on the inputs we have collected into nested
677 tables so far (such as transaction ids, product ids,org ids etc) .This step
678 is necessary as we want to bulk insert all values into ZX_PRVDR_LINE_EXTNS_GT
679 through nested tables only.Doing this in a separate loop only for clarity*/
680
681 derive_product_code;
682 derive_audit_flag;
683 derive_ship_to_address_code;
684 derive_ship_from_address_code;
685 derive_poa_address_code;
686 derive_poo_address_code;
687 derive_customer_code;
688 derive_customer_name;
689 derive_division_code;
690 derive_transaction_date;
691 derive_company_code;
692 derive_vnd_ctrl_exmpt;
693 derive_use_nexpro;
694 derive_service_ind;
695 derive_tax_sel_param;
696 derive_calculation_flag;
697
698 END LOOP;
699
700 /*This portion of the code performs a bulk insert into the ZX_PRVDR_LINE_EXTNS_GT
701 through all the pl/sql tables populated above*/
702 IF g_line_negation THEN
703 null;
704 ELSE
705 BEGIN
706 FORALL j in 1..NVL(trx_id_tab.last,0)
707 INSERT INTO
708 ZX_PRVDR_LINE_EXTNS_GT
709 (
710 EVENT_CLASS_CODE,
711 APPLICATION_ID ,
712 ENTITY_CODE ,
713 TRX_ID ,
714 TRX_LINE_ID ,
715 TRX_LEVEL_TYPE ,
716 PROVIDER_ID ,
717 TAX_REGIME_CODE ,
718 -- LINE_EXT_VARCHAR_ATTRIBUTE1,
719 LINE_EXT_VARCHAR_ATTRIBUTE2,
720 LINE_EXT_VARCHAR_ATTRIBUTE3,
721 LINE_EXT_VARCHAR_ATTRIBUTE4,
722 LINE_EXT_VARCHAR_ATTRIBUTE5,
723 LINE_EXT_VARCHAR_ATTRIBUTE6,
724 LINE_EXT_VARCHAR_ATTRIBUTE7,
725 LINE_EXT_VARCHAR_ATTRIBUTE8,
726 LINE_EXT_VARCHAR_ATTRIBUTE9,
727 LINE_EXT_VARCHAR_ATTRIBUTE10,
728 LINE_EXT_VARCHAR_ATTRIBUTE11,
729 LINE_EXT_VARCHAR_ATTRIBUTE12,
730 LINE_EXT_VARCHAR_ATTRIBUTE13,
731 LINE_EXT_VARCHAR_ATTRIBUTE14,
732 LINE_EXT_VARCHAR_ATTRIBUTE15,
733 LINE_EXT_VARCHAR_ATTRIBUTE16,
734 LINE_EXT_VARCHAR_ATTRIBUTE17,
735 LINE_EXT_VARCHAR_ATTRIBUTE18,
736 LINE_EXT_VARCHAR_ATTRIBUTE19,
737 LINE_EXT_VARCHAR_ATTRIBUTE20,
738 LINE_EXT_VARCHAR_ATTRIBUTE21,
739 LINE_EXT_VARCHAR_ATTRIBUTE22,
740 LINE_EXT_VARCHAR_ATTRIBUTE23,
741 LINE_EXT_VARCHAR_ATTRIBUTE24,
742 LINE_EXT_NUMBER_ATTRIBUTE1,
743 LINE_EXT_NUMBER_ATTRIBUTE2,
744 LINE_EXT_NUMBER_ATTRIBUTE3,
745 LINE_EXT_NUMBER_ATTRIBUTE4,
746 LINE_EXT_NUMBER_ATTRIBUTE5,
747 LINE_EXT_NUMBER_ATTRIBUTE6,
748 LINE_EXT_DATE_ATTRIBUTE1,
749 CREATION_DATE,
750 CREATED_BY,
751 LAST_UPDATE_DATE,
752 LAST_UPDATED_BY
753 )
754 values
755 (
756 event_class_code_tab(j), --EVENT_CLASS_CODE
757 application_id_tab(j), --APPLICATION_ID
758 entity_code_tab(j), --ENTITY_CODE
759 trx_id_tab(j), --TRX_ID
760 trx_line_id_tab(j), --TRX_LINE_ID
761 trx_line_type_tab(j), --TRX_LEVEL_TYPE
762 tax_provider_id_tab(j), --PROVIDER_ID
763 tax_regime_code_tab(j), --TAX_REGIME_CODE
764 --arp_tax_type_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE1
765 arp_product_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE2
766 use_step_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE3
767 arp_state_exempt_reason_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE4
768 arp_county_exempt_reason_tab(j),--LINE_EXT_VARCHAR_ATTRIBUTE5
769 arp_city_exempt_reason_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE6
770 step_proc_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE7
771 arp_audit_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE8
772 arp_ship_to_add_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE9
773 arp_ship_from_add_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE10
774 arp_poa_add_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE11
775 arp_customer_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE12
776 arp_customer_name_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE13
777 arp_company_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE14
778 arp_division_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE15
779 arp_vnd_ctrl_exmpt_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE16
780 arp_use_nexpro_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE17
781 arp_service_ind_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE18
782 crit_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE19
783 arp_poo_add_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE20
784 calculation_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE21
785 state_cert_no_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE22
786 county_cert_no_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE23
787 city_cert_no_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE24
788 arp_state_exempt_percent_tab(j),--LINE_EXT_NUMBER_ATTRIBUTE1
789 arp_county_exempt_pct_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE2
790 arp_city_exempt_pct_tab(j) , --LINE_EXT_NUMBER_ATTRIBUTE3
791 sec_county_exempt_pct_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE4
792 sec_city_exempt_pct_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE5
793 arp_tax_sel_param_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE6
794 arp_transaction_date_tab(j), --LINE_EXT_DATE_ATTRIBUTE1
795 SYSDATE,
796 FND_GLOBAL.USER_ID,
797 SYSDATE,
798 FND_GLOBAL.USER_ID
799 );
800
801 EXCEPTION
802 WHEN OTHERS THEN
803 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
804 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
805 END IF;
806 x_error_status := FND_API.G_RET_STS_UNEXP_ERROR ;
807 g_string :='Not able to insert in to ZX_PRVDR_LINE_EXTNS_GT ';
808 error_exception_handle(g_string);
809 x_messages_tbl:=g_messages_tbl;
810 return;
811 END;
812
813 END IF;
814
815 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
816 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
817 ' RETURN_STATUS = ' || x_error_status);
818 END IF;
819
820 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
821 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||l_api_name||'()-');
822 END IF;
823
824 END Derive_Line_Ext_Attr;
825
826
827 /*===========================================================================+
828 | PROCEDURE
829 | Derive_View_Name
830 | IN
831 |
832 | OUT NOCOPY
833 |
834 | DESCRIPTION
835 | This procedureis used to derive the view name for a given combination
836 | of application_id,event_class_code,api name , adjusted doc trx id
837 | and line level action.
838 |
839 |
840 | SCOPE - PRIVATE
841 |
842 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
843 |
844 | CALLED FROM
845 |
846 |
847 | MODIFICATION HISTORY
848 | 08/13/2004 Arnab Sengupta Created.
849 |
850 +==========================================================================*/
851 PROCEDURE DERIVE_VIEW_NAME(
852 p_application_id IN ZX_LINES_DET_FACTORS.APPLICATION_ID%TYPE,
853 p_event_class_code IN ZX_LINES_DET_FACTORS.EVENT_CLASS_CODE%TYPE,
854 p_api_name IN VARCHAR2,
855 p_adjusted_doc_trx_id IN ZX_LINES_DET_FACTORS.ADJUSTED_DOC_TRX_ID%TYPE,
856 p_line_level_action IN ZX_LINES_DET_FACTORS.LINE_LEVEL_ACTION%TYPE,
857 x_view_name OUT NOCOPY VARCHAR2
858 )
859 IS
860 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_VIEW_NAME';
861 BEGIN
862 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
863 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
864 END IF;
865
866 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
867 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' p_api_name = ' || p_api_name);
868 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' p_line_level_action = ' || p_line_level_action);
869 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' p_adjusted_doc_trx_id = ' || p_adjusted_doc_trx_id);
870 END IF;
871
872 /*The next portion of the code deals with the view name derivation based on
873 application_id , event_class_code and api name and adjusted doc trx id*/
874
875 IF p_application_id = 222 THEN -- Receivables
876 IF p_api_name = 'IMPORT_DOCUMENT_WITH_TAX' THEN -- Auto invoice
877 IF p_event_class_code in ('INVOICE','DEBIT_MEMO') THEN
878 x_view_name := 'TAX_LINES_INVOICE_IMPORT_V_A INVOICE';
879 ELSIF p_event_class_code in ('CREDIT_MEMO') THEN
880 IF p_adjusted_doc_trx_id IS NOT NULL THEN -- Applied Credit Memo
881 x_view_name := 'TAX_LINES_RMA_IMPORT_V_A CREDITMEMO';
882 ELSE -- On Account Credit Memo
883 x_view_name := 'TAX_LINES_INVOICE_IMPORT_V_A INVOICE';
884 END IF;
885 END IF;
886 ELSIF p_api_name = 'CALCULATE_TAX' THEN -- Manual invoice
887 IF p_event_class_code in ('INVOICE') THEN
888 IF p_line_level_action = 'COPY_AND_CREATE' THEN -- Recurring Invoice
889 x_view_name := 'TAX_LINES_RECURR_INVOICE_V_A';
890 ELSE
891 x_view_name := 'TAX_LINES_CREATE_V_A INVOICE';
892 END IF;
893 ELSIF p_event_class_code in ('DEBIT_MEMO') THEN
894 x_view_name := 'TAX_LINES_CREATE_V_A INVOICE';
895 ELSIF p_event_class_code in ('CREDIT_MEMO') THEN
896 IF p_adjusted_doc_trx_id IS NOT NULL THEN -- Applied Credit Memo
897 x_view_name := 'TAX_LINES_CM_V_A CREDITMEMO';
898 ELSE -- On Account CM
899 x_view_name := 'TAX_LINES_CREATE_V_A INVOICE';
900 END IF;
901 ELSIF p_event_class_code in ('INVOICE_ADJUSTMENT') THEN
902 x_view_name := 'TAX_ADJUSTMENTS_V_A';
903 END IF;
904 ELSIF p_api_name in ('UPDATE_DET_FACTORS_HDR','UPDATE_LINE_DET_FACTORS') THEN -- Line negation
905 IF p_event_class_code in ('INVOICE','DEBIT_MEMO') THEN
906 x_view_name := 'TAX_LINES_CREATE_V_A INVOICE';
907 ELSIF p_event_class_code in ('CREDIT_MEMO') THEN
908 IF p_adjusted_doc_trx_id IS NOT NULL THEN -- Applied Credit Memo
909 x_view_name := 'TAX_LINES_CM_V_A CREDITMEMO';
910 ELSE -- On Account CM
911 x_view_name := 'TAX_LINES_CREATE_V_A INVOICE';
912 END IF;
913 END IF;
914 ELSIF p_api_name in ('GLOBAL_DOCUMENT_UPDATE') THEN
915 x_view_name := 'TAX_LINES_CREATE_V_A';
916 END IF;
917
918 ELSIF p_application_id in (660, 300) THEN
919 x_view_name := 'OE_TAX_LINES_SUMMARY_V_A';
920 ELSE
921 x_view_name := 'ASO_TAX_LINES_SUMMARY_V_A'; ---Default View Name Assignment
922 END IF;
923
924 IF ( g_level_statement >= g_current_runtime_level) THEN
925 FND_LOG.STRING(g_level_statement,'ZX_TAXWARE_USER_PKG.DERIVE_VIEW_NAME',
926 'x_view_name == '||x_view_name);
927 End if;
928
929
930 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
931 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
932 END IF;
933 END DERIVE_VIEW_NAME;
934
935
936 /*===========================================================================+
937 | PROCEDURE
938 | POPULATE_EXEMPTION_DETAILS
939 | IN
940 |
941 | OUT NOCOPY
942 |
943 | DESCRIPTION
944 | This procedure is used to return a record containing the exemption_id
945 | which needs to be used to fetch the other related exemption related attributes.
946 |
947 |
948 | SCOPE - PRIVATE
949 |
950 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
951 |
952 | CALLED FROM
953 |
954 |
955 | MODIFICATION HISTORY
956 | 08/13/2004 Arnab Sengupta Created.
957 |
958 +==========================================================================*/
959
960 PROCEDURE POPULATE_EXEMPTION_DETAILS
961 (
962 p_bill_to_site_use IN ZX_LINES_DET_FACTORS.BILL_TO_CUST_ACCT_SITE_USE_ID%TYPE,
963 p_bill_to_location_id IN ZX_LINES_DET_FACTORS.BILL_TO_LOCATION_ID%TYPE,
964 p_bill_to_party_tax_id IN ZX_LINES_DET_FACTORS.BILL_TO_PARTY_TAX_PROF_ID%TYPE,
965 p_bill_to_site_tax_prof IN ZX_LINES_DET_FACTORS.TRADING_HQ_SITE_TAX_PROF_ID%TYPE,
966 p_hq_site_tax_prof_id IN ZX_LINES_DET_FACTORS.TRADING_HQ_SITE_TAX_PROF_ID%TYPE,
967 p_hq_party_tax_prof_id_tab IN ZX_LINES_DET_FACTORS.TRADING_HQ_PARTY_TAX_PROF_ID%TYPE,
968 p_bill_third_pty_acct_id IN ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_ID%TYPE,
969 p_product_org_id IN ZX_LINES_DET_FACTORS.PRODUCT_ORG_ID%TYPE,
970 p_product_id IN ZX_LINES_DET_FACTORS.product_id%TYPE,
971 p_cert_num IN ZX_LINES_DET_FACTORS.exempt_certificate_number%TYPE,
972 p_exmpt_rsn_code IN ZX_LINES_DET_FACTORS.exempt_reason_code%TYPE,
973 p_exemption_control_flag IN ZX_LINES_DET_FACTORS.Exemption_Control_Flag%TYPE,
974 p_tax_regime_code IN ZX_TRX_PRE_PROC_OPTIONS_GT.Tax_Regime_Code%TYPE,
975 p_position IN NUMBER,
976 p_error_status OUT NOCOPY VARCHAR2
977 ) IS
978
979 x_ret_status VARCHAR2(30);
980 x_exempt_record ZX_TCM_GET_EXEMPT_PKG.EXEMPTION_REC_TYPE;
981
982 TYPE tax_identifier_table IS TABLE OF VARCHAR2(100)
983 INDEX BY BINARY_INTEGER;
984 tax_identifier_tab tax_identifier_table;
985
986 l_api_name CONSTANT VARCHAR2(80) := 'POPULATE_EXEMPTION_DETAILS';
987 l_location_type VARCHAR2(100);
988 l_exempt_percent ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE1%TYPE;
989 l_exempt_reason ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE4%TYPE;
990 l_certificate_number ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE3%TYPE;
991 l_tax_account_source_tax VARCHAR2(50);
992 l_jurisdiction_rec ZX_TCM_GEO_JUR_PKG.tax_jurisdiction_rec_type;
993 l_jurisdictions_found VARCHAR2(5);
994 l_multiple_jurisdictions_flag VARCHAR2(5);
995 l_ptnr_exemption_indx VARCHAR2(4000);
996
997 BEGIN
998
999 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1000 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1001 END IF;
1002
1003 /*Set the return status to Success */
1004 x_ret_status := FND_API.G_RET_STS_SUCCESS;
1005
1006 x_exempt_record := null;
1007
1008 tax_identifier_tab(1):= 'STATE';
1009 tax_identifier_tab(2):= 'COUNTY';
1010 tax_identifier_tab(3):= 'CITY';
1011
1012 /* Call the get_tax_exemptions procedure from the TCM Exemptions package to collect the
1013 exemption_id , default percentage rate into the x_exemption_record.Do this for each
1014 location specific tax ie call this procedure identically for the p_tax value of
1015 STATE,COUNTY,CITY */
1016
1017 FOR i IN tax_identifier_tab.first .. tax_identifier_tab.last
1018
1019 LOOP
1020
1021 IF event_class_code_tab(p_position) = 'CREDIT_MEMO'
1022 AND adjusted_doc_trx_id_tab(p_position) IS NOT NULL THEN
1023 l_tax_account_source_tax := NULL;
1024 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1025 FND_LOG.STRING(G_LEVEL_STATEMENT,'Tax Account Source Tax Inside Credit: ',l_tax_account_source_tax);
1026 END IF;
1027 ELSE
1028 BEGIN
1029 SELECT NVL(TAX_EXMPT_SOURCE_TAX, TAX_ACCOUNT_SOURCE_TAX) --Bug 8724051
1030 INTO l_tax_account_source_tax
1031 FROM ZX_SCO_TAXES_B_V
1032 WHERE tax_regime_code = p_tax_regime_code AND
1033 tax = tax_identifier_tab(i) AND
1034 ( g_trx_date >= effective_from AND
1035 (g_trx_date <= effective_to OR effective_to IS NULL));
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1039 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||L_API_NAME,SQLERRM);
1040 END IF;
1041 NULL;
1042 END;
1043 END IF;
1044
1045 IF l_tax_account_source_tax IS NOT NULL THEN
1046 IF l_tax_account_source_tax = 'STATE' THEN
1047 l_exempt_percent := arp_state_exempt_percent_tab(p_position);
1048 l_exempt_reason := arp_state_exempt_reason_tab(p_position);
1049 ELSIF l_tax_account_source_tax = 'COUNTY' THEN
1050 l_exempt_percent := arp_county_exempt_pct_tab(p_position);
1051 l_exempt_reason := arp_county_exempt_reason_tab(p_position);
1052 ELSIF l_tax_account_source_tax = 'CITY' THEN
1053 l_exempt_percent := arp_city_exempt_pct_tab(p_position);
1054 l_exempt_reason := arp_city_exempt_reason_tab(p_position);
1055 END IF;
1056 l_certificate_number := cert_num_tab(p_position);
1057
1058 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1059 FND_LOG.STRING(G_LEVEL_STATEMENT,'Percent: ',to_char(l_exempt_percent));
1060 FND_LOG.STRING(G_LEVEL_STATEMENT,'Reason: ',to_char(l_exempt_reason));
1061 FND_LOG.STRING(G_LEVEL_STATEMENT,'Certificate: ',to_char(l_certificate_number));
1062 END IF;
1063
1064 IF tax_identifier_tab(i) = 'STATE' THEN
1065 arp_state_exempt_percent_tab(p_position) := l_exempt_percent;
1066 arp_state_exempt_reason_tab(p_position) := l_exempt_reason;
1067 ELSIF tax_identifier_tab(i) = 'COUNTY' THEN
1068 arp_county_exempt_pct_tab(p_position) := l_exempt_percent;
1069 sec_county_exempt_pct_tab(p_position) := l_exempt_percent;
1070 arp_county_exempt_reason_tab(p_position) := l_exempt_reason;
1071 ELSIF tax_identifier_tab(i) = 'CITY' THEN
1072 arp_city_exempt_pct_tab(p_position) := l_exempt_percent;
1073 sec_city_exempt_pct_tab(p_position) := l_exempt_percent;
1074 arp_city_exempt_reason_tab(p_position) := l_exempt_reason;
1075 END IF;
1076 cert_num_tab(p_position) := l_certificate_number;
1077 -- adding code to populate exemption details in partner calculated tax lines
1078 l_ptnr_exemption_indx := to_char(trx_id_tab(p_position)) || '$' ||
1079 to_char(trx_line_id_tab(p_position)) || '$' ||
1080 l_tax_account_source_tax || '$' ||
1081 p_tax_regime_code || '$' ||
1082 to_char(tax_provider_id_tab(p_position));
1083 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1084 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' l_ptnr_exemption_indx = ' || l_ptnr_exemption_indx );
1085 END IF;
1086 IF ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl.EXISTS(l_ptnr_exemption_indx)
1087 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_id
1088 = trx_id_tab(p_position)
1089 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_line_id
1090 = trx_line_id_tab(p_position)
1091 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax
1092 = l_tax_account_source_tax
1093 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_regime_code
1094 = p_tax_regime_code
1095 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_provider_id
1096 = tax_provider_id_tab(p_position)
1097 THEN
1098 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1099 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' exemption info found in cache.' );
1100 END IF;
1101 IF tax_identifier_tab(i) = 'COUNTY' THEN
1102 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason_code :=
1103 arp_county_exempt_reason_tab(p_position);
1104 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason :=
1105 arp_county_exempt_reason_tab(p_position);
1106 ELSIF tax_identifier_tab(i) = 'CITY' THEN
1107 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason_code :=
1108 arp_city_exempt_pct_tab(p_position);
1109 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason :=
1110 arp_city_exempt_pct_tab(p_position);
1111 END IF;
1112 END IF;
1113
1114 ELSE /* There is No Source Tax */
1115 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1116 FND_LOG.STRING(G_LEVEL_STATEMENT,'Inside Else','No Source Tax');
1117 END IF;
1118 IF event_class_code_tab(p_position) = 'CREDIT_MEMO'
1119 AND adjusted_doc_trx_id_tab(p_position) IS NOT NULL THEN
1120
1121 /* Special processing is required here and we must NOT fetch the
1122 exemption id from the tcm api. The geo level field here is
1123 used to know what level of geography we are dealing with
1124 to approprirately insert into the relevant nested tables */
1125
1126 BEGIN
1127 SELECT TAX_EXEMPTION_ID,
1128 NVL(EXEMPT_RATE_MODIFIER ,0) * 100,
1129 EXEMPT_REASON_CODE,
1130 EXEMPT_CERTIFICATE_NUMBER
1131 INTO x_exempt_record.exemption_id,
1132 x_exempt_record.percent_exempt,
1133 x_exempt_record.exempt_reason_code,
1134 x_exempt_record.exempt_certificate_number
1135 FROM ZX_LINES
1136 WHERE application_id = adj_doc_appl_id_tab(p_position)
1137 AND entity_code = adj_doc_entity_code_tab(p_position)
1138 AND event_class_code = adj_evnt_cls_code_tab(p_position)
1139 AND trx_id = adjusted_doc_trx_id_tab(p_position)
1140 AND trx_line_id = adj_doc_line_id_tab(p_position)
1141 AND trx_level_type = adj_doc_trx_level_type_tab(p_position)
1142 AND tax_regime_code = p_tax_regime_code
1143 AND tax = tax_identifier_tab(i);
1144 EXCEPTION WHEN NO_DATA_FOUND THEN
1145 BEGIN
1146 SELECT TAX_EXEMPTION_ID,
1147 NVL(EXEMPT_RATE_MODIFIER ,0) * 100,
1148 EXEMPT_REASON_CODE,
1149 EXEMPT_CERTIFICATE_NUMBER
1150 INTO x_exempt_record.exemption_id,
1151 x_exempt_record.percent_exempt,
1152 x_exempt_record.exempt_reason_code,
1153 x_exempt_record.exempt_certificate_number
1154 FROM ZX_LINES
1155 WHERE application_id = adj_doc_appl_id_tab(p_position)
1156 AND entity_code = adj_doc_entity_code_tab(p_position)
1157 AND event_class_code = adj_evnt_cls_code_tab(p_position)
1158 AND trx_id = adjusted_doc_trx_id_tab(p_position)
1159 AND trx_line_id = adj_doc_line_id_tab(p_position)
1160 AND trx_level_type = adj_doc_trx_level_type_tab(p_position)
1161 AND tax_regime_code = p_tax_regime_code
1162 AND tax = 'LOCATION';
1163 EXCEPTION WHEN NO_DATA_FOUND THEN
1164 x_exempt_record.exemption_id:= NULL;
1165 x_exempt_record.exemption_id:= NULL;
1166 x_exempt_record.percent_exempt:= NULL;
1167 x_exempt_record.exempt_reason_code:= NULL;
1168 x_exempt_record.exempt_certificate_number:= NULL;
1169 END;
1170 END;
1171
1172 IF x_exempt_record.exemption_id is NOT NULL THEN
1173 /*Proceed further only if the exemption id fetched is not null*/
1174 BEGIN
1175 SELECT rate_modifier
1176 INTO x_exempt_record.percent_exempt
1177 FROM ZX_EXEMPTIONS EXMP
1178 WHERE tax_exemption_id = x_exempt_record.exemption_id;
1179 EXCEPTION WHEN NO_DATA_FOUND THEN
1180 IF (g_level_exception >= g_current_runtime_level ) THEN
1181 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1182 END IF;
1183 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
1184 g_string :='No Data found from ZX_EXEMPTIONS for provided id';
1185 error_exception_handle(g_string);
1186 --x_messages_tbl:=g_messages_tbl;
1187 RETURN;
1188 END;
1189 END IF;
1190
1191 ELSE /* Beginning of regular processing */
1192
1193 /* Adding the Code for exemptions to work as in 11i*/
1194
1195 IF ship_to_loc_id_tab(p_position) IS NOT NULL THEN
1196 l_location_type := 'SHIP_TO';
1197 ELSIF bill_to_location_id_tab(p_position) IS NOT NULL THEN
1198 l_location_type := 'BILL_TO';
1199 ELSE
1200 l_location_type := NULL;
1201 END IF;
1202
1203 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1204 FND_LOG.STRING(G_LEVEL_STATEMENT,'Location Type: ',l_location_type);
1205 FND_LOG.STRING(G_LEVEL_STATEMENT,'Location ID: ',to_char(p_bill_to_location_id));
1206 END IF;
1207
1208 IF p_bill_to_location_id IS NOT NULL THEN
1209 ZX_TCM_GEO_JUR_PKG.get_tax_jurisdictions (
1210 p_location_id => p_bill_to_location_id,
1211 p_location_type => l_location_type,
1212 p_tax => tax_identifier_tab(i),
1213 p_tax_regime_code => p_tax_regime_code,
1214 p_trx_date => g_trx_date,
1215 x_tax_jurisdiction_rec => l_jurisdiction_rec,
1216 x_jurisdictions_found => l_jurisdictions_found,
1217 x_return_status => x_ret_status);
1218 END IF;
1219
1220 IF (x_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1221 /*FND_LOG.STRING(g_level_statement,
1222 'In Populate Exemption Details',
1223 'After calling get_tax_jurisdictions, x_return_status = '|| x_ret_status);*/
1224 RETURN;
1225 ELSE
1226 IF l_jurisdiction_rec.tax_jurisdiction_id IS NOT NULL THEN
1227 l_multiple_jurisdictions_flag := 'N';
1228 /*FND_LOG.STRING(g_level_statement,
1229 'Jurisdiction ID: ',
1230 l_jurisdiction_rec.tax_jurisdiction_id);*/
1231 ELSE
1232 IF l_jurisdictions_found = 'Y' THEN
1233 l_multiple_jurisdictions_flag := 'Y';
1234 ELSE
1235 l_multiple_jurisdictions_flag := 'N';
1236 END IF;
1237 l_jurisdiction_rec.tax_jurisdiction_id := NULL;
1238 END IF;
1239 END IF;
1240
1241 /* End of changes */
1242
1243 ZX_TCM_GET_EXEMPT_PKG.get_tax_exemptions(
1244 p_bill_to_cust_site_use_id => p_bill_to_site_use,
1245 p_bill_to_cust_acct_id => p_bill_third_pty_acct_id,
1246 p_bill_to_party_site_ptp_id => p_bill_to_site_tax_prof , -- fixed for 7610995
1247 p_bill_to_party_ptp_id => p_bill_to_party_tax_id,
1248 p_sold_to_party_site_ptp_id => p_hq_site_tax_prof_id,
1249 p_sold_to_party_ptp_id => p_hq_party_tax_prof_id_tab,
1250 p_inventory_org_id => p_product_org_id,
1251 p_inventory_item_id => p_product_id,
1252 p_exempt_certificate_number => p_cert_num,
1253 p_reason_code => p_exmpt_rsn_code,
1254 p_exempt_control_flag => p_exemption_control_flag,
1255 p_tax_date => g_trx_date,
1256 p_tax_regime_code => p_tax_regime_code,
1257 p_tax => tax_identifier_tab(i),
1258 p_tax_status_code => 'STANDARD',
1259 p_tax_rate_code => 'STANDARD',
1260 p_tax_jurisdiction_id => l_jurisdiction_rec.tax_jurisdiction_id,
1261 p_multiple_jurisdictions_flag => l_multiple_jurisdictions_flag,
1262 p_event_class_rec => NULL,
1263 x_return_status => x_ret_status,
1264 x_exemption_rec => x_exempt_record
1265 );
1266
1267 END IF;
1268
1269 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1270 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' tax_identifier_tab(i) = ' || tax_identifier_tab(i));
1271 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' x_exempt_record.exemption_id = ' || x_exempt_record.exemption_id);
1272 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' x_exempt_record.exempt_certificate_number = ' || x_exempt_record.exempt_certificate_number);
1273 END IF;
1274
1275 IF x_exempt_record.exemption_id is NULL THEN
1276 use_step_tab(p_position) := 'Y';
1277 step_proc_flag_tab(p_position) := '1';
1278 crit_flag_tab(p_position) := 'R';
1279
1280 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1281 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' use_step_tab(p_position) = ' || use_step_tab(p_position));
1282 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' step_proc_flag_tab(p_position) = ' || step_proc_flag_tab(p_position));
1283 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' crit_flag_tab(p_position) = ' || crit_flag_tab(p_position));
1284 END IF;
1285 ELSIF x_exempt_record.exemption_id is NOT NULL OR
1286 (x_exempt_record.percent_exempt IS NOT NULL
1287 AND event_class_code_tab(p_position) = 'CREDIT_MEMO') THEN
1288 /* This condition check is necessary because we have to cater to the
1289 condition that if the special processing code logic returned a null
1290 exemption id then we have to skip the iteration all together.Also
1291 this possibility(exemption id being null)
1292 is only there for the special processing code logic .So we can safely
1293 use this condition without in any way harming the regular processing.
1294
1295 The overall logic here is like this:
1296 Initially populate the exemption id into a nested table at the same index .Meaning if a exemption
1297 id of 2000 gets derived for a state level tax then for the exemptions nested table store this
1298 value of 2000 at the 2000th location in the table.Also use the derived exemption id to
1299 call the ARP_TAX_VIEW_TAXWARE.GET_EXEMPTIONS in order to populate the exemption records table
1300 at the same location ie 2000.
1301
1302 ****NOTE**** Here p_position is the position in the linear table (in the main loop)
1303 into which the derived values are ultimately getting inserted .
1304 */
1305 IF NOT exemptions_info_tab.EXISTS(NVL(x_exempt_record.exemption_id, -99)) THEN
1306 IF x_exempt_record.exemption_id IS NOT NULL THEN
1307 ARP_TAX_VIEW_TAXWARE.GET_EXEMPTIONS(
1308 X_EXEMPT_RECORD.EXEMPTION_ID, --This is the input parameter for this call
1309 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct,
1310 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason,
1311 exemptions_info_tab(x_exempt_record.exemption_id).state_cert_no,
1312 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct,
1313 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason,
1314 exemptions_info_tab(x_exempt_record.exemption_id).county_cert_no,
1315 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct ,
1316 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason,
1317 exemptions_info_tab(x_exempt_record.exemption_id).city_cert_no,
1318 exemptions_info_tab(x_exempt_record.exemption_id).sec_county_exempt_percent,
1319 exemptions_info_tab(x_exempt_record.exemption_id).sec_city_exempt_percent,
1320 exemptions_info_tab(x_exempt_record.exemption_id).use_step,
1321 exemptions_info_tab(x_exempt_record.exemption_id).Step_Proc_Flag,
1322 exemptions_info_tab(x_exempt_record.exemption_id).Crit_Flag
1323 );
1324
1325 IF exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct IS NULL THEN
1326 /* If the user extensible procedure returned a null then use the default percentage
1327 rate extracted into the exemptions record*/
1328 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct := x_exempt_record.percent_exempt;
1329 END IF;
1330
1331 IF exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason IS NULL THEN
1332 /* If the user extensible procedure returned a null then use the default exempt
1333 reason extracted into the exemptions record*/
1334 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1335 END IF;
1336
1337 IF exemptions_info_tab(x_exempt_record.exemption_id).state_cert_no IS NULL THEN
1338 /* If the user extensible procedure returned a null then use the default exempt
1339 certificate number extracted into the exemptions record*/
1340 exemptions_info_tab(x_exempt_record.exemption_id).state_cert_no := SUBSTRB(x_exempt_record.exempt_certificate_number,1,15);
1341 END IF;
1342
1343 IF exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct IS NULL THEN
1344 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct:= x_exempt_record.percent_exempt;
1345 END IF;
1346
1347 IF exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason IS NULL THEN
1348 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1349 END IF;
1350
1351 IF exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct IS NULL THEN
1352 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct := x_exempt_record.percent_exempt;
1353 END IF;
1354
1355 IF exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason IS NULL THEN
1356 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1357 END IF;
1358
1359 IF exemptions_info_tab(x_exempt_record.exemption_id).Sec_County_Exempt_Percent IS NULL THEN
1360 exemptions_info_tab(x_exempt_record.exemption_id).Sec_County_Exempt_Percent := x_exempt_record.percent_exempt;
1361 END IF;
1362
1363 IF exemptions_info_tab(x_exempt_record.exemption_id).Sec_City_Exempt_Percent IS NULL THEN
1364 exemptions_info_tab(x_exempt_record.exemption_id).Sec_City_Exempt_Percent := x_exempt_record.percent_exempt;
1365 END IF;
1366
1367 END IF;
1368 END IF;
1369 IF x_exempt_record.exemption_id IS NOT NULL THEN
1370 IF tax_identifier_tab(i) = 'STATE' THEN
1371 arp_state_exempt_percent_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct;
1372 arp_state_exempt_reason_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason;
1373
1374 ELSIF tax_identifier_tab(i) = 'COUNTY' THEN
1375 arp_county_exempt_pct_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct;
1376 arp_county_exempt_reason_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason;
1377 county_cert_no_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).county_cert_no;
1378 sec_county_exempt_pct_tab(p_position) :=exemptions_info_tab(x_exempt_record.exemption_id).Sec_County_Exempt_Percent;
1379
1380 ELSIF tax_identifier_tab(i) = 'CITY' THEN
1381 arp_city_exempt_pct_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct;
1382 arp_city_exempt_reason_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason;
1383 city_cert_no_tab(p_position) :=exemptions_info_tab(x_exempt_record.exemption_id).city_cert_no;
1384 sec_city_exempt_pct_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).Sec_City_Exempt_Percent;
1385
1386 END IF;
1387 cert_num_tab(p_position) := nvl(p_cert_num,exemptions_info_tab(x_exempt_record.exemption_id).state_cert_no);
1388 ELSIF (x_exempt_record.percent_exempt IS NOT NULL AND event_class_code_tab(p_position) = 'CREDIT_MEMO') THEN
1389 IF tax_identifier_tab(i) = 'STATE' THEN
1390 arp_state_exempt_percent_tab(p_position) := x_exempt_record.percent_exempt;
1391 arp_state_exempt_reason_tab(p_position) := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1392 ELSIF tax_identifier_tab(i) = 'COUNTY' THEN
1393 arp_county_exempt_pct_tab(p_position) := x_exempt_record.percent_exempt;
1394 arp_county_exempt_reason_tab(p_position) := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1395 ELSIF tax_identifier_tab(i) = 'CITY' THEN
1396 arp_city_exempt_pct_tab(p_position) := x_exempt_record.percent_exempt;
1397 arp_city_exempt_reason_tab(p_position) := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1398 END IF;
1399 cert_num_tab(p_position) := nvl(p_cert_num, x_exempt_record.exempt_certificate_number);
1400 END IF;
1401
1402 -- adding code to populate exemption details in partner calculated tax lines
1403 l_ptnr_exemption_indx := to_char(trx_id_tab(p_position)) || '$' ||
1404 to_char(trx_line_id_tab(p_position)) || '$' ||
1405 tax_identifier_tab(i) || '$' ||
1406 p_tax_regime_code || '$' ||
1407 to_char(tax_provider_id_tab(p_position));
1408 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1409 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' l_ptnr_exemption_indx = ' || l_ptnr_exemption_indx );
1410 END IF;
1411 IF ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl.EXISTS(l_ptnr_exemption_indx)
1412 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_id
1413 = trx_id_tab(p_position)
1414 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_line_id
1415 = trx_line_id_tab(p_position)
1416 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax
1417 = tax_identifier_tab(i)
1418 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_regime_code
1419 = p_tax_regime_code
1420 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_provider_id
1421 = tax_provider_id_tab(p_position)
1422 THEN
1423 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1424 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' exemption info found in cache.' );
1425 END IF;
1426 --NULL;
1427 ELSE
1428 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_id := trx_id_tab(p_position);
1429 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_line_id := trx_line_id_tab(p_position);
1430 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax := tax_identifier_tab(i);
1431 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_regime_code := p_tax_regime_code;
1432 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_provider_id := tax_provider_id_tab(p_position);
1433 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_exemption_id := x_exempt_record.exemption_id;
1434 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).exempt_certificate_number := cert_num_tab(p_position);
1435
1436 IF x_exempt_record.exemption_id IS NOT NULL THEN
1437 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason_code :=
1438 NVL(exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason,x_exempt_record.exempt_reason_code);
1439 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason_code :=
1440 NVL(exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason,x_exempt_record.exempt_reason_code);
1441 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason_code :=
1442 NVL(exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason,x_exempt_record.exempt_reason_code);
1443 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason :=
1444 NVL(exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason,x_exempt_record.exempt_reason_code);
1445 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason :=
1446 NVL(exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason,x_exempt_record.exempt_reason_code);
1447 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason :=
1448 NVL(exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason,x_exempt_record.exempt_reason_code);
1449 ELSIF (x_exempt_record.percent_exempt IS NOT NULL AND event_class_code_tab(p_position) = 'CREDIT_MEMO') THEN
1450 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason_code := x_exempt_record.exempt_reason_code;
1451 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason_code := x_exempt_record.exempt_reason_code;
1452 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason_code := x_exempt_record.exempt_reason_code;
1453 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason := x_exempt_record.exempt_reason_code;
1454 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason := x_exempt_record.exempt_reason_code;
1455 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason := x_exempt_record.exempt_reason_code;
1456 END IF;
1457 END IF;
1458 END IF; /* Tax Account Source Tax */
1459 END IF; /*End of special processing if*/
1460
1461 END LOOP;
1462
1463 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1464 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' arp_state_exempt_percent_tab(p_position) = ' || arp_state_exempt_percent_tab(p_position));
1465 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' arp_county_exempt_pct_tab(p_position) = ' || arp_county_exempt_pct_tab(p_position));
1466 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' arp_city_exempt_pct_tab(p_position) = ' || arp_city_exempt_pct_tab(p_position));
1467 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' arp_district_exempt_pct_tab(p_position) = ' || arp_district_exempt_pct_tab(p_position));
1468 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' p_cert_num = ' || p_cert_num);
1469 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' cert_num_tab(p_position) = ' || cert_num_tab(p_position));
1470 END IF;
1471
1472 p_error_status := x_ret_status;
1473
1474 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1475 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1476 ' RETURN_STATUS = ' || p_error_status);
1477 END IF;
1478
1479 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1480 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1481 END IF;
1482
1483 EXCEPTION
1484 WHEN OTHERS THEN
1485 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
1486 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1487 END IF;
1488 p_error_status := FND_API.G_RET_STS_UNEXP_ERROR;
1489
1490 END POPULATE_EXEMPTION_DETAILS;
1491
1492 /*===========================================================================+
1493 | PROCEDURE
1494 | Initialize_Nested_Tables
1495 | IN
1496 |
1497 | OUT NOCOPY
1498 |
1499 | DESCRIPTION
1500 | This is a start up procedure that deletes any existing data from the nested
1501 | tables
1502 |
1503 |
1504 | SCOPE - PRIVATE
1505 |
1506 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1507 |
1508 | CALLED FROM
1509 |
1510 |
1511 | MODIFICATION HISTORY
1512 | 08/13/2004 Arnab Sengupta Created.
1513 |
1514 +==========================================================================*/
1515
1516 PROCEDURE Initialize_Nested_Tables
1517 IS
1518 l_api_name CONSTANT VARCHAR2(80) := 'INITIALIZE_NESTED_TABLES';
1519 BEGIN
1520 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1521 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1522 END IF;
1523
1524 internal_org_id_tab.DELETE;
1525 application_id_tab.DELETE;
1526 entity_code_tab.DELETE;
1527 event_class_code_tab.DELETE;
1528 trx_id_tab.DELETE;
1529 tax_provider_id_tab.DELETE;
1530 tax_regime_code_tab.DELETE;
1531 trx_line_type_tab.DELETE;
1532 trx_line_id_tab.DELETE;
1533 product_id_tab.DELETE;
1534 Product_Org_Id_tab.DELETE;
1535 ship_to_tx_id_tab.DELETE;
1536 ship_from_tx_id_tab.DELETE;
1537 cert_num_tab.DELETE;
1538 exmpt_rsn_code_tab.DELETE;
1539 exemption_control_flag_tab.DELETE;
1540 ship_to_site_tax_prof_tab.DELETE;
1541 ship_to_loc_id_tab.DELETE;
1542 exmpt_control_flg_tab.DELETE;
1543 arp_tax_type_tab.DELETE;
1544 arp_product_code_tab.DELETE;
1545 use_step_tab.DELETE;
1546 arp_audit_flag_tab.DELETE;
1547 arp_ship_to_add_tab.DELETE;
1548 arp_ship_from_add_tab.DELETE;
1549 arp_poa_add_code_tab.DELETE;
1550 arp_customer_code_tab.DELETE;
1551 arp_customer_name_tab.DELETE;
1552 arp_company_code_tab.DELETE;
1553 arp_division_code_tab.DELETE;
1554 arp_vnd_ctrl_exmpt_tab.DELETE;
1555 arp_use_nexpro_tab.DELETE;
1556 arp_service_ind_tab.DELETE;
1557 arp_tax_sel_param_tab.DELETE;
1558 arp_transaction_date_tab.DELETE;
1559 ship_to_address_id_tab.DELETE;
1560 ship_to_party_id_tab.DELETE;
1561 arp_state_exempt_reason_tab.DELETE;
1562 arp_county_exempt_reason_tab.DELETE;
1563 arp_city_exempt_reason_tab.DELETE;
1564 step_proc_flag_tab.DELETE;
1565 arp_state_exempt_percent_tab.DELETE;
1566 arp_county_exempt_pct_tab.DELETE;
1567 arp_city_exempt_pct_tab.DELETE;
1568 ship_to_site_use_tab.DELETE;
1569 bill_to_site_use_tab.DELETE;
1570 bill_to_site_tax_prof_tab.DELETE;
1571 bill_to_party_tax_id_tab.DELETE;
1572 bill_to_location_id_tab.DELETE;
1573 trad_hq_site_tax_prof_id_tab.DELETE;
1574 trad_hq_party_tax_prof_id_tab.DELETE;
1575 bill_third_pty_acct_id_tab.DELETE;
1576 line_level_action_tab.DELETE;
1577 adjusted_doc_trx_id_tab.DELETE;
1578 line_amount_tab.DELETE;
1579 exemptions_info_tab.DELETE;
1580 trx_type_id_tab.DELETE;
1581 state_cert_no_tab.DELETE;
1582 county_cert_no_tab.DELETE;
1583 city_cert_no_tab.DELETE;
1584 crit_flag_tab.DELETE;
1585 sec_county_exempt_pct_tab.DELETE;
1586 sec_city_exempt_pct_tab.DELETE;
1587 adj_doc_appl_id_tab.DELETE;
1588 adj_doc_entity_code_tab.DELETE;
1589 adj_evnt_cls_code_tab.DELETE;
1590 adj_doc_line_id_tab.DELETE;
1591 adj_doc_trx_level_type_tab.DELETE;
1592 ship_third_pty_site_tab.DELETE;
1593 bill_third_pty_site_tab.DELETE;
1594
1595 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1596 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||l_api_name||'()-');
1597 END IF;
1598
1599 EXCEPTION WHEN COLLECTION_IS_NULL THEN
1600 NULL;
1601
1602 END Initialize_Nested_Tables;
1603
1604 PROCEDURE Initialize_Exemption_Tables IS
1605
1606 l_api_name CONSTANT VARCHAR2(80) := 'INITIALIZE_EXEMPTION_TABLES';
1607 BEGIN
1608 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1609 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1610 END IF;
1611
1612 FOR i in 1..nvl(internal_org_id_tab.last,0) --Loop 1
1613 LOOP
1614
1615
1616 arp_state_exempt_reason_tab(i):= NULL;
1617 arp_state_exempt_percent_tab(i):= NULL;
1618 state_cert_no_tab(i):= NULL;
1619 use_step_tab(i):= NULL;
1620 step_proc_flag_tab(i):= NULL;
1621 crit_flag_tab(i):= NULL;
1622 arp_county_exempt_reason_tab(i):= NULL;
1623 arp_county_exempt_pct_tab(i):= NULL;
1624 county_cert_no_tab(i):= NULL;
1625 sec_county_exempt_pct_tab(i):= NULL;
1626 arp_city_exempt_reason_tab(i):= NULL;
1627 arp_city_exempt_pct_tab(i):= NULL;
1628 city_cert_no_tab(i):= NULL;
1629 sec_city_exempt_pct_tab(i):= NULL;
1630
1631 END LOOP;
1632 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1633 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||l_api_name||'()-');
1634 END IF;
1635
1636
1637 END Initialize_Exemption_Tables;
1638
1639 /*===========================================================================+
1640 | FUNCTION |
1641 | CHECK_GEOCODE |
1642 | |
1643 | DESCRIPTION |
1644 | Returns TRUE if the GEOCODE seems to be valid |
1645 | (in the format SSZZZZZGG) |
1646 | |
1647 | SCOPE - PRIVATE |
1648 | |
1649 | MODIFICATION HISTORY |
1650 | 01-NOV-05 Santosh Vaze Created for Bug 4668932 |
1651 | |
1652 +===========================================================================*/
1653
1654
1655 FUNCTION CHECK_GEOCODE(p_geocode IN VARCHAR2)
1656 RETURN BOOLEAN
1657 IS
1658 l_api_name CONSTANT VARCHAR2(80) := 'CHECK_GEOCODE';
1659 BEGIN
1660 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1661 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1662 END IF;
1663 if substrb(p_geocode, 1, 2) between 'AA' and 'ZZ' and
1664 substrb(p_geocode, 3, 5) between '00000' and '99999' and
1665 substrb(p_geocode, 8, 2) between '00' and '99' then
1666 return TRUE;
1667 end if;
1668
1669 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1670 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||l_api_name||'()-');
1671 END IF;
1672
1673 return FALSE;
1674 END CHECK_GEOCODE;
1675
1676 PROCEDURE DERIVE_TRX_LEVEL_ATTR
1677 IS
1678
1679 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_TRX_LEVEL_ATTR';
1680
1681 BEGIN
1682 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1683 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1684 END IF;
1685
1686 g_org_id := NULL;
1687 g_sales_repid := NULL;
1688 IF event_class_code_tab(i) = 'INVOICE_ADJUSTMENT' THEN
1689 IF adjusted_doc_trx_id_tab(i) IS NOT NULL THEN
1690 BEGIN
1691 SELECT org_id
1692 , primary_salesrep_id
1693 INTO g_org_id
1694 , g_sales_repid
1695 FROM ra_customer_trx_all
1696 WHERE customer_trx_id = adjusted_doc_trx_id_tab(i);
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 g_org_id := internal_org_id_tab(i);
1700 g_sales_repid := NULL;
1701 END;
1702 END IF;
1703 ELSE
1704 IF trx_id_tab(i) IS NOT NULL THEN
1705 IF trx_line_id_tab(i) IS NOT NULL
1706 AND l_view_name = 'OE_TAX_LINES_SUMMARY_V_V' THEN
1707 BEGIN
1708 SELECT org_id,salesrep_id
1709 INTO g_org_id,g_sales_repid
1710 FROM oe_order_lines_all
1711 WHERE header_id = trx_id_tab(i)
1712 AND line_id = trx_line_id_tab(i);
1713 EXCEPTION
1714 WHEN OTHERS THEN
1715 g_org_id := internal_org_id_tab(i);
1716 g_sales_repid := NULL;
1717 END;
1718 ELSE
1719 BEGIN
1720 SELECT org_id
1721 , primary_salesrep_id
1722 INTO g_org_id
1723 , g_sales_repid
1724 FROM ra_customer_trx_all
1725 WHERE customer_trx_id = trx_id_tab(i);
1726 EXCEPTION
1727 WHEN OTHERS THEN
1728 g_org_id := internal_org_id_tab(i);
1729 g_sales_repid := NULL;
1730 END;
1731 END IF;
1732 END IF;
1733 END IF;
1734
1735 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1736 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1737 END IF;
1738
1739 END DERIVE_TRX_LEVEL_ATTR;
1740
1741 PROCEDURE DERIVE_AUDIT_FLAG
1742 IS
1743
1744 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_AUDIT_FLAG';
1745
1746 BEGIN
1747 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1748 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1749 END IF;
1750
1751 IF l_view_name = 'TAX_ADJUSTMENTS_V_A' THEN
1752 BEGIN
1753 arp_audit_flag_tab(i) := ARP_TAX_VIEW_TAXWARE.AUDIT_FLAG
1754 (l_view_name,
1755 trx_id_tab(i),
1756 trx_line_id_tab(i));
1757 EXCEPTION WHEN OTHERS THEN
1758 arp_audit_flag_tab(i):= NULL;
1759 END;
1760
1761 IF arp_audit_flag_tab(i) IS NULL THEN
1762 BEGIN
1763 SELECT nvl(substrb(act.attribute15, 1, 1), 'Y')
1764 INTO arp_audit_flag_tab(i)
1765 FROM ar_receivables_trx act
1766 WHERE act.receivables_trx_id IN
1767 (SELECT adj.receivables_trx_id
1768 FROM ar_adjustments adj
1769 WHERE adj.adjustment_id = trx_id_tab(i))
1770 AND act.org_id = internal_org_id_tab(i);
1771 EXCEPTION WHEN OTHERS THEN
1772 arp_audit_flag_tab(i) := 'Y';
1773 END;
1774 END IF;
1775 ELSE
1776 arp_audit_flag_tab(i) := 'Y';
1777 END IF;
1778
1779 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1780 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1781 'Quote Flag from ZX_GLOBAL_STRUCTURE'||ZX_GLOBAL_STRUCTURES_PKG.g_event_class_rec.quote_flag);
1782 END IF;
1783
1784 IF ZX_GLOBAL_STRUCTURES_PKG.g_event_class_rec.quote_flag = 'Y' THEN
1785 arp_audit_flag_tab(i) := 'N';
1786 END IF;
1787 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1788 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Audit Flag :'||arp_audit_flag_tab(i));
1789 END IF;
1790
1791 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1792 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1793 END IF;
1794
1795 END DERIVE_AUDIT_FLAG;
1796
1797 PROCEDURE DERIVE_PRODUCT_CODE
1798 IS
1799
1800 l_product_id ZX_LINES_DET_FACTORS.product_id%TYPE;
1801 l_memo_line_id NUMBER;
1802 l_master_org_id oe_system_parameters_all.master_organization_id%type;
1803
1804 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_PRODUCT_CODE';
1805
1806 BEGIN
1807 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1808 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1809 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||' ',G_PKG_NAME||': '
1810 ||l_api_name||'l_view_name :'||l_view_name);
1811 END IF;
1812
1813 /* The product code function is not attached only to this view hence the if condition reads like this*/
1814
1815 IF l_view_name <> 'TAX_ADJUSTMENTS_V_A' THEN
1816
1817 /* Derive the product_id depending on the null or not null value of product_org_id*/
1818 IF product_org_id_tab(i) IS NOT NULL THEN
1819 l_product_id := product_id_tab(i);
1820 l_memo_line_id := NULL;
1821 ELSE
1822 l_product_id := NULL;
1823 l_memo_line_id := product_id_tab(i);
1824 END IF;
1825
1826 BEGIN
1827 arp_product_code_tab(i) := ARP_TAX_VIEW_TAXWARE.PRODUCT_CODE(l_view_name
1828 , trx_id_tab(i)
1829 , trx_line_id_tab(i)
1830 , l_product_id
1831 , l_memo_line_id);
1832 EXCEPTION WHEN OTHERS THEN
1833 arp_product_code_tab(i) := NULL;
1834 END;
1835
1836 IF arp_product_code_tab(i) IS NULL THEN
1837 BEGIN
1838 SELECT org_id
1839 INTO g_org_id
1840 FROM ra_customer_trx_lines_all
1841 WHERE customer_trx_id = trx_id_tab(i)
1842 AND customer_trx_line_id = trx_line_id_tab(i);
1843 EXCEPTION
1844 WHEN OTHERS THEN
1845 g_org_id := internal_org_id_tab(i);
1846 END;
1847
1848
1849 /* Bug 5612024
1850 IF MO_GLOBAL.get_current_org_id <> nvl(g_org_id, -1) THEN
1851 MO_GLOBAL.Set_Policy_Context('S', g_org_id);
1852 END IF;
1853 */
1854 l_master_org_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID', g_org_id);
1855
1856 BEGIN
1857 SELECT segment1
1858 INTO arp_product_code_tab(i)
1859 FROM mtl_system_items
1860 WHERE inventory_item_id = l_product_id
1861 AND organization_id = l_master_org_id;
1862 EXCEPTION
1863 WHEN OTHERS THEN
1864 arp_product_code_tab(i) := NULL;
1865 END;
1866 END IF;
1867 ELSE
1868 arp_product_code_tab(i) := NULL;
1869 END IF;
1870
1871 IF (g_level_statement >= g_current_runtime_level ) THEN
1872 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1873 ' arp_product_code_tab(i) = ' || arp_product_code_tab(i));
1874 END IF;
1875
1876 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1877 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1878 END IF;
1879
1880 END DERIVE_PRODUCT_CODE;
1881
1882 PROCEDURE DERIVE_SHIP_TO_ADDRESS_CODE
1883 IS
1884 l_tax_jurisdiction_rec ZX_TCM_GEO_JUR_PKG.tax_jurisdiction_rec_type;
1885 x_ret_status VARCHAR2(30);
1886 l_jurisdictions_found VARCHAR2(1);
1887 l_ship_to_geocode varchar2(30);
1888 l_state_code VARCHAR2(60);
1889 l_postal_code VARCHAR2(60);
1890
1891 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_SHIP_TO_ADDRESS_CODE';
1892
1893 BEGIN
1894 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1895 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1896 END IF;
1897
1898 ship_to_address_id_tab(i) := NULL;
1899 IF ship_to_site_use_tab(i) is NOT NULL THEN
1900 BEGIN
1901 SELECT cust_site_uses.cust_acct_site_id
1902 INTO ship_to_address_id_tab(i)
1903 FROM HZ_CUST_SITE_USES_ALL cust_site_uses
1904 WHERE cust_site_uses.site_use_id = ship_to_site_use_tab(i);
1905 EXCEPTION WHEN OTHERS THEN
1906 ship_to_address_id_tab(i) := NULL;
1907 END;
1908 END IF;
1909
1910 IF ship_to_address_id_tab(i) is NULL THEN
1911 BEGIN
1912 SELECT cust_acct_site_id
1913 INTO ship_to_address_id_tab(i)
1914 FROM HZ_CUST_SITE_USES_ALL
1915 WHERE site_use_id = bill_to_site_use_tab(i);
1916 EXCEPTION WHEN NO_DATA_FOUND THEN
1917 ship_to_address_id_tab(i) := NULL;
1918 END;
1919 END IF;
1920
1921 BEGIN
1922 arp_ship_to_add_tab(i) := ARP_TAX_VIEW_TAXWARE.SHIP_TO_ADDRESS_CODE
1923 (l_view_name,
1924 trx_id_tab(i),
1925 trx_line_id_tab(i),
1926 ship_to_address_id_tab(i),
1927 ship_to_loc_id_tab(i),
1928 g_trx_date,
1929 NULL,--p_ship_to_state
1930 NULL--p_postal_code
1931 );
1932 EXCEPTION WHEN OTHERS THEN
1933 arp_ship_to_add_tab(i):= NULL;
1934 END;
1935
1936 /* Bug 4668932 */
1937 IF arp_ship_to_add_tab(i) IS NULL
1938 THEN
1939 BEGIN
1940 SELECT decode(nvl(loc.sales_tax_inside_city_limits,'1'),'0','0','1') || loc.sales_tax_geocode
1941 INTO arp_ship_to_add_tab(i)
1942 FROM hz_locations loc
1943 WHERE loc.location_id = nvl(ship_to_loc_id_tab(i), bill_to_location_id_tab(i));
1944 EXCEPTION WHEN OTHERS THEN
1945 arp_ship_to_add_tab(i) := NULL;
1946 END;
1947 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1948 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME,'GeoCode Override: '||arp_ship_to_add_tab(i));
1949 END IF;
1950
1951 IF NOT check_geocode(substrb(nvl(arp_ship_to_add_tab(i),'XXXXXXXXXX'),2,9)) THEN
1952 ZX_TCM_GEO_JUR_PKG.get_tax_jurisdictions(
1953 p_location_id => nvl(ship_to_loc_id_tab(i), bill_to_location_id_tab(i)),
1954 p_location_type => 'SHIP_TO',
1955 p_tax => 'CITY',
1956 p_tax_regime_code => tax_regime_code_tab(i),
1957 p_trx_date => g_trx_date,
1958 x_tax_jurisdiction_rec => l_tax_jurisdiction_rec,
1959 x_jurisdictions_found => l_jurisdictions_found,
1960 x_return_status => x_ret_status);
1961
1962 IF x_ret_status = FND_API.G_RET_STS_SUCCESS THEN
1963 IF l_jurisdictions_found = 'Y' THEN
1964 IF l_tax_jurisdiction_rec.tax_jurisdiction_code IS NOT NULL THEN
1965 l_ship_to_geocode := l_tax_jurisdiction_rec.tax_jurisdiction_code;
1966
1967 arp_ship_to_add_tab(i) := '1' || substr(l_ship_to_geocode,4,2);
1968 arp_ship_to_add_tab(i) := arp_ship_to_add_tab(i) || substr(l_ship_to_geocode,-8,5);
1969 --arp_ship_to_add_tab(i) := arp_ship_to_add_tab(i) || substr(l_ship_to_geocode,-5,5);
1970 arp_ship_to_add_tab(i) := arp_ship_to_add_tab(i) ||substr(l_ship_to_geocode,-2);
1971 --arp_ship_to_add_tab(i) := arp_ship_to_add_tab(i) || '00';
1972 END IF;
1973 END IF;
1974 IF l_jurisdictions_found = 'N' OR (l_jurisdictions_found = 'Y' AND l_tax_jurisdiction_rec.tax_jurisdiction_code IS NULL) THEN
1975 BEGIN
1976 SELECT state, substr(postal_code,1,5)
1977 INTO l_state_code, l_postal_code
1978 FROM HZ_LOCATIONS
1979 WHERE location_id = NVL(ship_to_loc_id_tab(i), bill_to_location_id_tab(i));
1980 EXCEPTION
1981 WHEN OTHERS THEN
1982 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1983 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1984 'No data found for this location_id : '||NVL(ship_to_loc_id_tab(i), bill_to_location_id_tab(i)));
1985 END IF;
1986 END;
1987 arp_ship_to_add_tab(i) := '1' || NVL(l_state_code,'CA') || l_postal_code || '00';
1988 END IF;
1989 END IF;
1990 END IF;
1991 END IF;
1992
1993 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1994 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1995 END IF;
1996
1997 END DERIVE_SHIP_TO_ADDRESS_CODE;
1998
1999 PROCEDURE DERIVE_SHIP_FROM_ADDRESS_CODE
2000 IS
2001 l_sfr_geocode VARCHAR2(10);
2002 l_sfr_in_out_flag VARCHAR2(1);
2003 l_flag BOOLEAN;
2004 l_inventory_item_id NUMBER;
2005 l_master_org_id oe_system_parameters_all.master_organization_id%type;
2006 l_ship_from_party_id ZX_PARTY_TAX_PROFILE.PARTY_ID%TYPE;
2007 l_ship_from_location_id ZX_LINES_DET_FACTORS.SHIP_FROM_LOCATION_ID%TYPE;
2008
2009 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_SHIP_FROM_ADDRESS_CODE';
2010
2011 BEGIN
2012 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2013 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2014 END IF;
2015
2016 BEGIN
2017 SELECT SHIP_FROM_LOCATION_ID
2018 INTO l_ship_from_location_id
2019 FROM ZX_LINES_DET_FACTORS
2020 WHERE APPLICATION_ID = application_id_tab(i)
2021 AND ENTITY_CODE = entity_code_tab(i)
2022 AND EVENT_CLASS_CODE = event_class_code_tab(i)
2023 AND TRX_ID = trx_id_tab(i)
2024 AND TRX_LINE_ID = trx_line_id_tab(i);
2025 EXCEPTION
2026 WHEN OTHERS THEN
2027 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2028 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Error : ' || SQLERRM);
2029 END IF;
2030 l_ship_from_location_id := NULL;
2031 END;
2032
2033 BEGIN
2034 IF ship_from_tx_id_tab(i) IS NOT NULL THEN
2035 IF p_party_id_tbl.EXISTS(ship_from_tx_id_tab(i)) THEN
2036 l_ship_from_party_id := p_party_id_tbl(ship_from_tx_id_tab(i));
2037 ELSE
2038 SELECT party_id
2039 INTO l_ship_from_party_id
2040 FROM ZX_PARTY_TAX_PROFILE
2041 WHERE party_tax_profile_id = ship_from_tx_id_tab(i);
2042
2043 p_party_id_tbl(ship_from_tx_id_tab(i)) := l_ship_from_party_id;
2044 END IF;
2045 END IF;
2046 EXCEPTION WHEN NO_DATA_FOUND THEN
2047 l_ship_from_party_id := NULL;
2048 END;
2049
2050 arp_ship_from_add_tab(i) := ARP_TAX_VIEW_TAXWARE.SHIP_FROM_ADDRESS_CODE
2051 (l_view_name,
2052 trx_id_tab(i),
2053 trx_line_id_tab(i),
2054 l_ship_from_party_id);
2055
2056 IF arp_ship_from_add_tab(i) is NULL THEN
2057 BEGIN
2058 SELECT lc.loc_information13
2059 INTO arp_ship_from_add_tab(i)
2060 FROM hr_locations_all lc, hr_organization_units hr
2061 WHERE hr.organization_id = l_ship_from_party_id
2062 AND hr.location_id = lc.location_id;
2063
2064 IF arp_ship_from_add_tab(i) IS NOT NULL THEN
2065 arp_ship_from_add_tab(i) := '1'||arp_ship_from_add_tab(i);
2066 ELSE
2067 arp_ship_from_add_tab(i):= arp_tax_view_taxware.USE_SHIP_TO;
2068 END IF;
2069 EXCEPTION WHEN OTHERS THEN
2070 BEGIN
2071 SELECT lc.loc_information13
2072 INTO arp_ship_from_add_tab(i)
2073 FROM hr_locations_all lc
2074 WHERE lc.location_id = l_ship_from_location_id;
2075
2076 IF arp_ship_from_add_tab(i) IS NOT NULL THEN
2077 arp_ship_from_add_tab(i) := '1'||arp_ship_from_add_tab(i);
2078 ELSE
2079 arp_ship_from_add_tab(i):= arp_tax_view_taxware.USE_SHIP_TO;
2080 END IF;
2081 EXCEPTION WHEN OTHERS THEN
2082 arp_ship_from_add_tab(i):= arp_tax_view_taxware.USE_SHIP_TO;
2083 END;
2084 END;
2085 END IF;
2086
2087 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2088 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2089 END IF;
2090
2091 END DERIVE_SHIP_FROM_ADDRESS_CODE;
2092
2093 PROCEDURE DERIVE_POA_ADDRESS_CODE
2094 IS
2095 l_poa_geocode VARCHAR2(10);
2096 l_poa_in_out_flag VARCHAR2(1);
2097
2098 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_POA_ADDRESS_CODE';
2099
2100 BEGIN
2101 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2102 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2103 END IF;
2104
2105 BEGIN
2106 arp_poa_add_code_tab(i) := ARP_TAX_VIEW_TAXWARE.POA_ADDRESS_CODE
2107 (l_view_name,
2108 trx_id_tab(i),
2109 trx_line_id_tab(i));
2110 Exception When Others then
2111 arp_poa_add_code_tab(i) := NULL;
2112 End;
2113
2114 IF arp_poa_add_code_tab(i) is NULL then
2115 BEGIN
2116 SELECT zxprdopt.SALES_TAX_GEOCODE
2117 INTO arp_poa_add_code_tab(i)
2118 FROM ZX_PRODUCT_OPTIONS_ALL zxprdopt
2119 WHERE zxprdopt.application_id = application_id_tab(i)
2120 AND zxprdopt.org_id = internal_org_id_tab(i)
2121 AND (zxprdopt.event_class_mapping_id IS NULL
2122 OR zxprdopt.event_class_mapping_id = (SELECT EVENT_CLASS_MAPPING_ID
2123 FROM ZX_EVNT_CLS_MAPPINGS
2124 WHERE EVENT_CLASS_CODE = event_class_code_tab(i)
2125 AND APPLICATION_ID = application_id_tab(i)
2126 AND ENTITY_CODE = entity_code_tab(i)));
2127 IF arp_poa_add_code_tab(i) IS NULL THEN
2128 arp_poa_add_code_tab(i) := arp_tax_view_taxware.USE_SHIP_TO;
2129 ELSE
2130 IF NOT check_geocode(arp_poa_add_code_tab(i)) THEN
2131 arp_poa_add_code_tab(i) := arp_tax_view_taxware.USE_SHIP_TO;
2132 ELSE
2133 arp_poa_add_code_tab(i) := '1'||arp_poa_add_code_tab(i);
2134 END IF;
2135 END IF;
2136 EXCEPTION WHEN OTHERS THEN
2137 arp_poa_add_code_tab(i) := arp_tax_view_taxware.USE_SHIP_TO;
2138 END;
2139 END IF;
2140
2141 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2142 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2143 ' arp_poa_add_code_tab(i) = ' || arp_poa_add_code_tab(i));
2144 END IF;
2145
2146 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2147 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2148 END IF;
2149
2150 END DERIVE_POA_ADDRESS_CODE;
2151
2152 PROCEDURE DERIVE_POO_ADDRESS_CODE
2153 IS
2154 l_poa_geocode VARCHAR2(10);
2155 l_poa_in_out_flag VARCHAR2(1);
2156
2157 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_POO_ADDRESS_CODE';
2158
2159 BEGIN
2160 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2161 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2162 END IF;
2163
2164 BEGIN
2165 arp_poo_add_code_tab(i) := ARP_TAX_VIEW_TAXWARE.POO_ADDRESS_CODE
2166 (l_view_name,
2167 trx_id_tab(i),
2168 trx_line_id_tab(i),
2169 g_sales_repid);
2170 Exception When Others then
2171 arp_poo_add_code_tab(i) := NULL;
2172 End;
2173
2174 IF arp_poo_add_code_tab(i) is NULL then
2175 BEGIN
2176 select sales_tax_geocode
2177 into arp_poo_add_code_tab(i)
2178 from ra_salesreps
2179 where salesrep_id = g_sales_repid;
2180 IF arp_poo_add_code_tab(i) IS NULL THEN
2181 arp_poo_add_code_tab(i) := arp_tax_view_taxware.USE_SHIP_TO;
2182 ELSE
2183 IF NOT check_geocode(arp_poo_add_code_tab(i)) THEN
2184 arp_poo_add_code_tab(i) := arp_tax_view_taxware.USE_SHIP_TO;
2185 ELSE
2186 arp_poo_add_code_tab(i) := '1'||arp_poo_add_code_tab(i);
2187 END IF;
2188 END IF;
2189 EXCEPTION WHEN OTHERS THEN
2190 arp_poo_add_code_tab(i) := arp_tax_view_taxware.USE_SHIP_TO;
2191 END;
2192 END IF;
2193
2194
2195 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2196 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2197 ' arp_poo_add_code_tab(i) = ' || arp_poo_add_code_tab(i));
2198 END IF;
2199
2200 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2201 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2202 END IF;
2203
2204 END DERIVE_POO_ADDRESS_CODE;
2205
2206 PROCEDURE DERIVE_CUSTOMER_CODE
2207 IS
2208
2209 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_CUSTOMER_CODE';
2210
2211 BEGIN
2212 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2213 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2214 END IF;
2215
2216 arp_customer_code_tab(i) := ARP_TAX_VIEW_TAXWARE.CUSTOMER_CODE
2217 (l_view_name,
2218 trx_id_tab(i),
2219 trx_line_id_tab(i));
2220 IF arp_customer_code_tab(i) IS NULL THEN
2221 /* Bug 5007293: During negation: ZX_PTNR_LOCATION_INFO_GT is not yet populated */
2222
2223 BEGIN
2224
2225 SELECT account_number
2226 INTO arp_customer_code_tab(i)
2227 FROM HZ_CUST_ACCOUNTS
2228 WHERE cust_account_id = bill_third_pty_acct_id_tab(i);
2229
2230 EXCEPTION
2231 WHEN OTHERS THEN
2232 arp_customer_code_tab(i) := NULL;
2233 END;
2234
2235 IF arp_customer_code_tab(i) IS NULL THEN
2236 IF g_line_negation THEN
2237 IF p_party_number_tbl.EXISTS(bill_to_party_tax_id_tab(i)) THEN
2238 arp_customer_code_tab(i) := p_party_number_tbl(bill_to_party_tax_id_tab(i));
2239 ELSE
2240 BEGIN
2241 SELECT pty.party_number
2242 INTO arp_customer_code_tab(i)
2243 FROM hz_parties pty,
2244 zx_party_tax_profile ptp
2245 WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
2246 AND ptp.party_id = pty.party_id;
2247 EXCEPTION WHEN OTHERS THEN
2248 arp_customer_code_tab(i) := NULL;
2249 END;
2250 p_party_number_tbl(bill_to_party_tax_id_tab(i)) := arp_customer_code_tab(i);
2251 END IF;
2252 ELSE
2253 BEGIN
2254 SELECT zpli.bill_to_party_number
2255 INTO arp_customer_code_tab(i)
2256 FROM ZX_PTNR_LOCATION_INFO_GT zpli
2257 , ZX_EVNT_CLS_MAPPINGS zecm
2258 WHERE zpli.EVENT_CLASS_MAPPING_ID = zecm.EVENT_CLASS_MAPPING_ID
2259 AND zecm.EVENT_CLASS_CODE = event_class_code_tab(i)
2260 AND zecm.APPLICATION_ID = application_id_tab(i)
2261 AND zecm.ENTITY_CODE = entity_code_tab(i)
2262 AND zpli.TRX_ID = trx_id_tab(i)
2263 AND zpli.TRX_LINE_ID = trx_line_id_tab(i);
2264 EXCEPTION WHEN OTHERS THEN
2265 arp_customer_code_tab(i) := NULL;
2266 END;
2267
2268 IF arp_customer_code_tab(i) IS NULL THEN
2269 IF p_party_number_tbl.EXISTS(bill_to_party_tax_id_tab(i)) THEN
2270 arp_customer_code_tab(i) := p_party_number_tbl(bill_to_party_tax_id_tab(i));
2271 ELSE
2272 BEGIN
2273 SELECT pty.party_number
2274 INTO arp_customer_code_tab(i)
2275 FROM hz_parties pty,
2276 zx_party_tax_profile ptp
2277 WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
2278 AND ptp.party_id = pty.party_id;
2279 EXCEPTION WHEN OTHERS THEN
2280 arp_customer_code_tab(i) := NULL;
2281 END;
2282 p_party_number_tbl(bill_to_party_tax_id_tab(i)) := arp_customer_code_tab(i);
2283 END IF;
2284 END IF;
2285 END IF;
2286 END IF;
2287 END IF;
2288 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2289 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2290 END IF;
2291
2292 EXCEPTION WHEN OTHERS THEN
2293 arp_customer_code_tab(i):= NULL;
2294 END DERIVE_CUSTOMER_CODE;
2295
2296 PROCEDURE DERIVE_CUSTOMER_NAME
2297 IS
2298
2299 l_party_id ZX_PARTY_TAX_PROFILE.PARTY_ID%TYPE;
2300 l_ptp_id ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
2301 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_CUSTOMER_NAME';
2302
2303 BEGIN
2304 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2305 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2306 END IF;
2307
2308
2309 BEGIN
2310 arp_customer_name_tab(i) := ARP_TAX_VIEW_TAXWARE.CUSTOMER_NAME
2311 (l_view_name,
2312 trx_id_tab(i),
2313 trx_line_id_tab(i));
2314
2315 IF arp_customer_name_tab(i) IS NULL
2316 THEN
2317 /* Bug 5007293: During negation: ZX_PTNR_LOCATION_INFO_GT is not yet populated */
2318 IF g_line_negation THEN
2319 BEGIN
2320 SELECT pty.party_name
2321 INTO arp_customer_name_tab(i)
2322 FROM hz_parties pty,
2323 zx_party_tax_profile ptp
2324 WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
2325 AND ptp.party_id = pty.party_id;
2326 EXCEPTION WHEN OTHERS THEN
2327 arp_customer_name_tab(i) := NULL;
2328 END;
2329 ELSE
2330 BEGIN
2331 SELECT zpli.BILL_TO_PARTY_NAME
2332 INTO arp_customer_name_tab(i)
2333 FROM ZX_PTNR_LOCATION_INFO_GT zpli
2334 , ZX_EVNT_CLS_MAPPINGS zecm
2335 WHERE zpli.EVENT_CLASS_MAPPING_ID = zecm.EVENT_CLASS_MAPPING_ID
2336 AND zecm.EVENT_CLASS_CODE = event_class_code_tab(i)
2337 AND zecm.APPLICATION_ID = application_id_tab(i)
2338 AND zecm.ENTITY_CODE = entity_code_tab(i)
2339 AND zpli.TRX_ID = trx_id_tab(i)
2340 AND zpli.TRX_LINE_ID = trx_line_id_tab(i);
2341 EXCEPTION WHEN OTHERS THEN
2342 arp_customer_name_tab(i) := NULL;
2343 END;
2344
2345 IF arp_customer_name_tab(i) IS NULL
2346 THEN
2347 BEGIN
2348 SELECT pty.party_name
2349 INTO arp_customer_name_tab(i)
2350 FROM hz_parties pty,
2351 zx_party_tax_profile ptp
2352 WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
2353 AND ptp.party_id = pty.party_id;
2354 EXCEPTION WHEN OTHERS THEN
2355 arp_customer_name_tab(i) := NULL;
2356 END;
2357 END IF;
2358 END IF;
2359 END IF;
2360 EXCEPTION WHEN OTHERS THEN
2361 arp_customer_name_tab(i):= NULL;
2362 END;
2363
2364 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2365 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2366 END IF;
2367
2368 END DERIVE_CUSTOMER_NAME;
2369
2370 PROCEDURE DERIVE_DIVISION_CODE
2371 IS
2372
2373 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_DIVISION_CODE';
2374
2375 BEGIN
2376 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2377 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2378 END IF;
2379
2380 arp_division_code_tab(i) := ARP_TAX_VIEW_TAXWARE.DIVISION_CODE
2381 (l_view_name,
2382 trx_id_tab(i),
2383 trx_line_id_tab(i));
2384 if(arp_division_code_tab(i) is NULL) then
2385 arp_division_code_tab(i):='01';
2386 end if;
2387
2388 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2389 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2390 END IF;
2391
2392 END DERIVE_DIVISION_CODE;
2393
2394 PROCEDURE DERIVE_TRANSACTION_DATE
2395 IS
2396
2397 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_TRANSACTION_DATE';
2398
2399 BEGIN
2400 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2401 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2402 END IF;
2403
2404 arp_transaction_date_tab(i) := ARP_TAX_VIEW_TAXWARE.TRANSACTION_DATE
2405 (l_view_name,
2406 trx_id_tab(i),
2407 trx_line_id_tab(i));
2408 IF arp_transaction_date_tab(i) IS NULL THEN
2409 BEGIN
2410 SELECT trx_line_gl_date
2411 INTO arp_transaction_date_tab(i)
2412 FROM zx_lines_det_factors
2413 WHERE internal_organization_id = internal_org_id_tab(i)
2414 AND application_id = application_id_tab(i)
2415 AND Entity_Code = entity_code_tab(i)
2416 AND Event_Class_Code = event_class_code_tab(i)
2417 AND trx_id = trx_id_tab(i)
2418 AND trx_line_id = trx_line_id_tab(i);
2419
2420 EXCEPTION WHEN OTHERS THEN
2421 arp_transaction_date_tab(i) := NULL;
2422 END;
2423 END IF;
2424
2425 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2426 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2427 END IF;
2428
2429 EXCEPTION WHEN OTHERS THEN
2430 arp_transaction_date_tab(i) := NULL;
2431 END DERIVE_TRANSACTION_DATE;
2432
2433 PROCEDURE DERIVE_COMPANY_CODE
2434 IS
2435
2436 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_COMPANY_CODE';
2437
2438 BEGIN
2439 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2440 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2441 END IF;
2442
2443 arp_company_code_tab(i) := ARP_TAX_VIEW_TAXWARE.COMPANY_CODE
2444 (l_view_name,
2445 trx_id_tab(i),
2446 trx_line_id_tab(i));
2447 IF (arp_company_code_tab(i) is NULL) THEN
2448 arp_company_code_tab(i) := '01';
2449 END IF;
2450
2451 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2452 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2453 END IF;
2454
2455 END DERIVE_COMPANY_CODE;
2456
2457 PROCEDURE DERIVE_VND_CTRL_EXMPT
2458 IS
2459
2460 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_VND_CTRL_EXMPT';
2461
2462 BEGIN
2463 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2464 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2465 END IF;
2466
2467 arp_vnd_ctrl_exmpt_tab(i):= ARP_TAX_VIEW_TAXWARE.VENDOR_CONTROL_EXEMPTIONS
2468 (l_view_name,
2469 trx_id_tab(i),
2470 trx_line_id_tab(i),
2471 trx_type_id_tab(i));
2472 if(arp_vnd_ctrl_exmpt_tab(i)) is NULL then
2473 BEGIN
2474
2475 select attribute1
2476 into arp_vnd_ctrl_exmpt_tab(i)
2477 from ra_cust_trx_types
2478 where cust_trx_type_id = trx_type_id_tab(i)
2479 and org_id = internal_org_id_tab(i);
2480
2481 EXCEPTION
2482 when no_data_found then
2483 arp_vnd_ctrl_exmpt_tab(i):=NULL;
2484
2485 END;
2486 End if;
2487
2488 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2489 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2490 END IF;
2491
2492 END DERIVE_VND_CTRL_EXMPT;
2493
2494 PROCEDURE DERIVE_USE_NEXPRO
2495 IS
2496
2497 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_USE_NEXPRO';
2498
2499 BEGIN
2500 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2501 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2502 END IF;
2503
2504 arp_use_nexpro_tab(i) := ARP_TAX_VIEW_TAXWARE.USE_NEXPRO
2505 (l_view_name,
2506 trx_id_tab(i),
2507 trx_line_id_tab(i));
2508 if(arp_use_nexpro_tab(i) is NULL) then
2509 arp_use_nexpro_tab(i) := g_usenexpro;
2510 end if;
2511
2512 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2513 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2514 END IF;
2515
2516 END DERIVE_USE_NEXPRO;
2517
2518 PROCEDURE DERIVE_SERVICE_IND
2519 IS
2520
2521 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_SERVICE_IND';
2522
2523 BEGIN
2524 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2525 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2526 END IF;
2527
2528 arp_service_ind_tab(i) := ARP_TAX_VIEW_TAXWARE.SERVICE_INDICATOR
2529 (l_view_name,
2530 trx_id_tab(i),
2531 trx_line_id_tab(i));
2532 if(arp_service_ind_tab(i) is NULL) then
2533 arp_service_ind_tab(i) := g_serviceind;
2534 end if;
2535
2536 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2537 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2538 END IF;
2539
2540 END DERIVE_SERVICE_IND;
2541
2542 PROCEDURE DERIVE_TAX_SEL_PARAM
2543 IS
2544
2545 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_TAX_SEL_PARAM';
2546
2547 BEGIN
2548 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2549 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2550 END IF;
2551
2552 arp_tax_sel_param_tab(i) := ARP_TAX_VIEW_TAXWARE.TAX_SEL_PARM
2553 (l_view_name,
2554 trx_id_tab(i),
2555 trx_line_id_tab(i));
2556 if(arp_tax_sel_param_tab(i) is NULL) then
2557 arp_tax_sel_param_tab(i) := g_taxselparam;
2558 end if;
2559
2560 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2561 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2562 END IF;
2563
2564 END DERIVE_TAX_SEL_PARAM;
2565
2566 PROCEDURE DERIVE_CALCULATION_FLAG
2567 IS
2568
2569 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_CALCULATION_FLAG';
2570
2571 BEGIN
2572 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2573 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2574 END IF;
2575
2576 calculation_flag_tab(i) := ARP_TAX_VIEW_TAXWARE.Calculation_Flag
2577 (l_view_name,
2578 trx_id_tab(i),
2579 trx_line_id_tab(i));
2580 if(calculation_flag_tab(i) is NULL) then
2581 calculation_flag_tab(i) := '00000';
2582 end if;
2583
2584 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2585 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2586 END IF;
2587
2588 END DERIVE_CALCULATION_FLAG;
2589
2590
2591 PROCEDURE ERROR_EXCEPTION_HANDLE(str varchar2) is
2592
2593 cursor error_exception_cursor is
2594 select EVNT_CLS_MAPPING_ID,
2595 TRX_ID,
2596 TAX_REGIME_CODE
2597 from ZX_TRX_PRE_PROC_OPTIONS_GT;
2598
2599 l_docment_type_id number;
2600 l_trasaction_id number;
2601 l_tax_regime_code varchar2(80);
2602
2603 Begin
2604 open error_exception_cursor;
2605 fetch error_exception_cursor into l_docment_type_id,l_trasaction_id,l_tax_regime_code;
2606
2607 G_MESSAGES_TBL.DOCUMENT_TYPE_ID(err_count) := l_docment_type_id;
2608 G_MESSAGES_TBL.TRANSACTION_ID(err_count) := l_trasaction_id;
2609 G_MESSAGES_TBL.COUNTRY_CODE(err_count) := l_tax_regime_code;
2610 G_MESSAGES_TBL.ERROR_MESSAGE_TYPE(err_count) := 'ERROR';
2611 G_MESSAGES_TBL.ERROR_MESSAGE_STRING(err_count) := str;
2612
2613 err_count :=err_count+1;
2614
2615 close error_exception_cursor;
2616
2617 End ERROR_EXCEPTION_HANDLE;
2618
2619 PROCEDURE INITIALIZE IS
2620 BEGIN
2621 IF (g_level_procedure >= g_current_runtime_level ) THEN
2622 FND_LOG.STRING(g_level_procedure, 'ZX.PARTNER.ARP_TAX_VIEW_TAXWARE.INITIALIZE',
2623 'ZX.PARTNER.ARP_TAX_VIEW_TAXWARE.INITIALIZE(+)');
2624 END IF;
2625
2626 g_usenexpro := fnd_profile.value('ZX_TAXVDR_USENEXPRO');
2627 g_taxselparam := TO_NUMBER(fnd_profile.value('ZX_TAXVDR_TAXSELPARAM'));
2628 -- g_taxtype := TO_NUMBER(fnd_profile.value('ZX_TAXVDR_TAXTYPE'));
2629 g_serviceind := TO_NUMBER(fnd_profile.value('ZX_TAXVDR_SERVICEIND'));
2630 IF (g_level_procedure >= g_current_runtime_level ) THEN
2631 FND_LOG.STRING(g_level_procedure, 'ZX.PARTNER.ARP_TAX_VIEW_TAXWARE.INITIALIZE',
2632 'ZX.PARTNER.ARP_TAX_VIEW_TAXWARE.INITIALIZE(-)');
2633 END IF;
2634
2635 END INITIALIZE;
2636
2637 Begin
2638 initialize;
2639
2640 END ZX_TAXWARE_USER_PKG;