[Home] [Help]
PACKAGE BODY: APPS.ZX_TAXWARE_USER_PKG
Source
1 PACKAGE BODY ZX_TAXWARE_USER_PKG AS
2 /* $Header: zxtxwuserpkgb.pls 120.34.12010000.2 2008/11/12 12:55:00 spasala 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 ,
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 event_class_code_tab(i) <> 'INVOICE_ADJUSTMENT' AND line_amount_tab(i) <> 0
599 THEN
600
601 /* Bug Number: 6328797 - According to 11i , The exemptions should work at
602 -> Ship_To For Customer Site level.
603 -> Bill_to For Customer Level
604 So we need to pass ship_to_loc_id_tab for finding the exemptions.
605 But previously we were passing bill_to_location_id_tab to find the exemptions.
606 So, chaged passing variable to ship_to_loc_id_tab.
607
608 But to avoid the structure changes, we are not changing the Naming Convention.
609 If we change the Structure Chages we need to do in lot of packages.
610 So we are keeping the name as bill_to only but changing the passing value.
611 */
612
613 POPULATE_EXEMPTION_DETAILS
614 (
615 p_bill_to_site_use => bill_to_site_use_tab(i),
616 p_bill_to_location_id => NVL(ship_to_loc_id_tab(i),bill_to_location_id_tab(i)),
617 p_bill_to_party_tax_id => bill_to_party_tax_id_tab(i),
618 p_bill_to_site_tax_prof => bill_to_site_tax_prof_tab(i),
619 p_hq_site_tax_prof_id => trad_hq_site_tax_prof_id_tab(i),
620 p_hq_party_tax_prof_id_tab => trad_hq_party_tax_prof_id_tab(i),
621 p_bill_third_pty_acct_id => bill_third_pty_acct_id_tab(i),
622 p_product_org_id => product_org_id_tab(i),
623 p_product_id => product_id_tab(i),
624 p_cert_num => cert_num_tab(i),
625 p_exmpt_rsn_code => exmpt_rsn_code_tab(i),
626 p_exemption_control_flag => exemption_control_flag_tab(i),
627 p_tax_regime_code => tax_regime_code_tab(i),
628 p_position => i,
629 p_error_status => x_ret_status
630 ) ;
631 NULL;
632 END IF;
633
634 IF x_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
635 IF (g_level_exception >= g_current_runtime_level ) THEN
636 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
637 END IF;
638 x_error_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 g_string :='Failed with error in procedure POPULATE_EXEMPTION_DETAILS';
640 error_exception_handle(g_string);
641 x_messages_tbl:=g_messages_tbl;
642 return;
643 END IF;
644
645 /*The next portion of the code deals with the view name derivation based on
646 application_id , event_class_code and api name .This procedure returns
647 the view name as an out parameter which is used successively .This needs
648 to be called just once as view name is a document level property association.*/
649
650
651 /**NOTE** The usage of the flag l_is_view_derived is purely to execute the derive_view_name
652 procedure just once as view name is a Document level attribute and hence need not
653 be derived for every line */
654
655 IF l_is_view_name_derived = 'N' THEN --Initially the flag is set to 'N'
656 DERIVE_VIEW_NAME
657 (p_application_id => application_id_tab(i),
658 p_event_class_code => event_class_code_tab(i),
659 p_api_name => l_srv_name,
660 p_adjusted_doc_trx_id => adjusted_doc_trx_id_tab(i),
661 p_line_level_action => line_level_action_tab(i),
662 x_view_name => l_view_name);
663 l_is_view_name_derived := 'Y'; --Once first execution is complete set flag to 'Y' to
664 --prevent re-execution of DERIVE_VIEW_NAME for successive lines
665 derive_trx_level_attr;
666 END IF;
667
668
669 /*This portion of the code is used to collect the values from the user extensible
670 procedures into nested tables based on the inputs we have collected into nested
671 tables so far (such as transaction ids, product ids,org ids etc) .This step
672 is necessary as we want to bulk insert all values into ZX_PRVDR_LINE_EXTNS_GT
673 through nested tables only.Doing this in a separate loop only for clarity*/
674
675 derive_product_code;
676 derive_audit_flag;
677 derive_ship_to_address_code;
678 derive_ship_from_address_code;
679 derive_poa_address_code;
680 derive_poo_address_code;
681 derive_customer_code;
682 derive_customer_name;
683 derive_division_code;
684 derive_transaction_date;
685 derive_company_code;
686 derive_vnd_ctrl_exmpt;
687 derive_use_nexpro;
688 derive_service_ind;
689 derive_tax_sel_param;
690 derive_calculation_flag;
691
692 END LOOP;
693
694 /*This portion of the code performs a bulk insert into the ZX_PRVDR_LINE_EXTNS_GT
695 through all the pl/sql tables populated above*/
696 IF g_line_negation THEN
697 null;
698 ELSE
699 BEGIN
700 FORALL j in 1..NVL(trx_id_tab.last,0)
701 INSERT INTO
702 ZX_PRVDR_LINE_EXTNS_GT
703 (
704 EVENT_CLASS_CODE,
705 APPLICATION_ID ,
706 ENTITY_CODE ,
707 TRX_ID ,
708 TRX_LINE_ID ,
709 TRX_LEVEL_TYPE ,
710 PROVIDER_ID ,
711 TAX_REGIME_CODE ,
712 -- LINE_EXT_VARCHAR_ATTRIBUTE1,
713 LINE_EXT_VARCHAR_ATTRIBUTE2,
714 LINE_EXT_VARCHAR_ATTRIBUTE3,
715 LINE_EXT_VARCHAR_ATTRIBUTE4,
716 LINE_EXT_VARCHAR_ATTRIBUTE5,
717 LINE_EXT_VARCHAR_ATTRIBUTE6,
718 LINE_EXT_VARCHAR_ATTRIBUTE7,
719 LINE_EXT_VARCHAR_ATTRIBUTE8,
720 LINE_EXT_VARCHAR_ATTRIBUTE9,
721 LINE_EXT_VARCHAR_ATTRIBUTE10,
722 LINE_EXT_VARCHAR_ATTRIBUTE11,
723 LINE_EXT_VARCHAR_ATTRIBUTE12,
724 LINE_EXT_VARCHAR_ATTRIBUTE13,
725 LINE_EXT_VARCHAR_ATTRIBUTE14,
726 LINE_EXT_VARCHAR_ATTRIBUTE15,
727 LINE_EXT_VARCHAR_ATTRIBUTE16,
728 LINE_EXT_VARCHAR_ATTRIBUTE17,
729 LINE_EXT_VARCHAR_ATTRIBUTE18,
730 LINE_EXT_VARCHAR_ATTRIBUTE19,
731 LINE_EXT_VARCHAR_ATTRIBUTE20,
732 LINE_EXT_VARCHAR_ATTRIBUTE21,
733 LINE_EXT_VARCHAR_ATTRIBUTE22,
734 LINE_EXT_VARCHAR_ATTRIBUTE23,
735 LINE_EXT_VARCHAR_ATTRIBUTE24,
736 LINE_EXT_NUMBER_ATTRIBUTE1,
737 LINE_EXT_NUMBER_ATTRIBUTE2,
738 LINE_EXT_NUMBER_ATTRIBUTE3,
739 LINE_EXT_NUMBER_ATTRIBUTE4,
740 LINE_EXT_NUMBER_ATTRIBUTE5,
741 LINE_EXT_NUMBER_ATTRIBUTE6,
742 LINE_EXT_DATE_ATTRIBUTE1,
743 CREATION_DATE,
744 CREATED_BY,
745 LAST_UPDATE_DATE,
746 LAST_UPDATED_BY
747 )
748 values
749 (
750 event_class_code_tab(j), --EVENT_CLASS_CODE
751 application_id_tab(j), --APPLICATION_ID
752 entity_code_tab(j), --ENTITY_CODE
753 trx_id_tab(j), --TRX_ID
754 trx_line_id_tab(j), --TRX_LINE_ID
755 trx_line_type_tab(j), --TRX_LEVEL_TYPE
756 tax_provider_id_tab(j), --PROVIDER_ID
757 tax_regime_code_tab(j), --TAX_REGIME_CODE
758 --arp_tax_type_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE1
759 arp_product_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE2
760 use_step_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE3
761 arp_state_exempt_reason_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE4
762 arp_county_exempt_reason_tab(j),--LINE_EXT_VARCHAR_ATTRIBUTE5
763 arp_city_exempt_reason_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE6
764 step_proc_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE7
765 arp_audit_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE8
766 arp_ship_to_add_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE9
767 arp_ship_from_add_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE10
768 arp_poa_add_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE11
769 arp_customer_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE12
770 arp_customer_name_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE13
771 arp_company_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE14
772 arp_division_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE15
773 arp_vnd_ctrl_exmpt_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE16
774 arp_use_nexpro_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE17
775 arp_service_ind_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE18
776 crit_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE19
777 arp_poo_add_code_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE20
778 calculation_flag_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE21
779 state_cert_no_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE22
780 county_cert_no_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE23
781 city_cert_no_tab(j), --LINE_EXT_VARCHAR_ATTRIBUTE24
782 arp_state_exempt_percent_tab(j),--LINE_EXT_NUMBER_ATTRIBUTE1
783 arp_county_exempt_pct_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE2
784 arp_city_exempt_pct_tab(j) , --LINE_EXT_NUMBER_ATTRIBUTE3
785 sec_county_exempt_pct_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE4
786 sec_city_exempt_pct_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE5
787 arp_tax_sel_param_tab(j), --LINE_EXT_NUMBER_ATTRIBUTE6
788 arp_transaction_date_tab(j), --LINE_EXT_DATE_ATTRIBUTE1
789 SYSDATE,
790 FND_GLOBAL.USER_ID,
791 SYSDATE,
792 FND_GLOBAL.USER_ID
793 );
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
798 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
799 END IF;
800 x_error_status := FND_API.G_RET_STS_UNEXP_ERROR ;
801 g_string :='Not able to insert in to ZX_PRVDR_LINE_EXTNS_GT ';
802 error_exception_handle(g_string);
803 x_messages_tbl:=g_messages_tbl;
804 return;
805 END;
806
807 END IF;
808
809 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
810 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
811 ' RETURN_STATUS = ' || x_error_status);
812 END IF;
813
814 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
815 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||l_api_name||'()-');
816 END IF;
817
818 END Derive_Line_Ext_Attr;
819
820
821 /*===========================================================================+
822 | PROCEDURE
823 | Derive_View_Name
824 | IN
825 |
826 | OUT NOCOPY
827 |
828 | DESCRIPTION
829 | This procedureis used to derive the view name for a given combination
830 | of application_id,event_class_code,api name , adjusted doc trx id
831 | and line level action.
832 |
833 |
834 | SCOPE - PRIVATE
835 |
836 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
837 |
838 | CALLED FROM
839 |
840 |
841 | MODIFICATION HISTORY
842 | 08/13/2004 Arnab Sengupta Created.
843 |
844 +==========================================================================*/
845 PROCEDURE DERIVE_VIEW_NAME(
846 p_application_id IN ZX_LINES_DET_FACTORS.APPLICATION_ID%TYPE,
847 p_event_class_code IN ZX_LINES_DET_FACTORS.EVENT_CLASS_CODE%TYPE,
848 p_api_name IN VARCHAR2,
849 p_adjusted_doc_trx_id IN ZX_LINES_DET_FACTORS.ADJUSTED_DOC_TRX_ID%TYPE,
850 p_line_level_action IN ZX_LINES_DET_FACTORS.LINE_LEVEL_ACTION%TYPE,
851 x_view_name OUT NOCOPY VARCHAR2
852 )
853 IS
854 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_VIEW_NAME';
855 BEGIN
856 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
857 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
858 END IF;
859
860 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
861 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' p_api_name = ' || p_api_name);
862 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' p_line_level_action = ' || p_line_level_action);
863 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' p_adjusted_doc_trx_id = ' || p_adjusted_doc_trx_id);
864 END IF;
865
866 /*The next portion of the code deals with the view name derivation based on
867 application_id , event_class_code and api name and adjusted doc trx id*/
868
869 IF p_application_id = 222 THEN -- Receivables
870 IF p_api_name = 'IMPORT_DOCUMENT_WITH_TAX' THEN -- Auto invoice
871 IF p_event_class_code in ('INVOICE','DEBIT_MEMO') THEN
872 x_view_name := 'TAX_LINES_INVOICE_IMPORT_V_A INVOICE';
873
874 ELSIF p_event_class_code in ('CREDIT_MEMO') THEN
875 IF p_adjusted_doc_trx_id IS NOT NULL THEN -- Applied Credit Memo
876 x_view_name := 'TAX_LINES_RMA_IMPORT_V_A CREDITMEMO';
877
878 ELSE -- On Account Credit Memo
879 x_view_name := 'TAX_LINES_INVOICE_IMPORT_V_A INVOICE';
880 END IF;
881 END IF;
882
883 ELSIF p_api_name = 'CALCULATE_TAX' THEN -- Manual invoice
884 IF p_event_class_code in ('INVOICE') THEN
885 IF p_line_level_action = 'COPY_AND_CREATE' THEN -- Recurring Invoice
886 x_view_name := 'TAX_LINES_RECURR_INVOICE_V_A';
887 ELSE
888 x_view_name := 'TAX_LINES_CREATE_V_A INVOICE';
889 END IF;
890
891 ELSIF p_event_class_code in ('DEBIT_MEMO') THEN
892 x_view_name := 'TAX_LINES_CREATE_V_A INVOICE';
893
894 ELSIF p_event_class_code in ('CREDIT_MEMO') THEN
895
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
905 ELSIF p_api_name = 'UPDATE_DET_FACTORS_HDR' THEN -- Line negation
906 IF p_event_class_code in ('INVOICE','DEBIT_MEMO') THEN
907 x_view_name := 'TAX_LINES_CREATE_V_A INVOICE';
908 ELSIF p_event_class_code in ('CREDIT_MEMO') THEN
909 IF p_adjusted_doc_trx_id IS NOT NULL THEN -- Applied Credit Memo
910 x_view_name := 'TAX_LINES_CM_V_A CREDITMEMO';
911 ELSE -- On Account CM
912 x_view_name := 'TAX_LINES_CREATE_V_A INVOICE';
913 END IF;
914 END IF;
915 END IF;
916
917 ELSIF p_application_id in (660, 300) THEN
918 x_view_name := 'OE_TAX_LINES_SUMMARY_V_A';
919 ELSE
920 x_view_name := 'ASO_TAX_LINES_SUMMARY_V_A'; ---Default View Name Assignment
921 END IF;
922
923 IF ( g_level_statement >= g_current_runtime_level) THEN
924 FND_LOG.STRING(g_level_statement,'ZX_TAXWARE_USER_PKG.DERIVE_VIEW_NAME',
925 'x_view_name == '||x_view_name);
926 End if;
927
928
929 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
930 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
931 END IF;
932
933
934 END DERIVE_VIEW_NAME;
935
936
937 /*===========================================================================+
938 | PROCEDURE
939 | POPULATE_EXEMPTION_DETAILS
940 | IN
941 |
942 | OUT NOCOPY
943 |
944 | DESCRIPTION
945 | This procedure is used to return a record containing the exemption_id
946 | which needs to be used to fetch the other related exemption related attributes.
947 |
948 |
949 | SCOPE - PRIVATE
950 |
951 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
952 |
953 | CALLED FROM
954 |
955 |
956 | MODIFICATION HISTORY
957 | 08/13/2004 Arnab Sengupta Created.
958 |
959 +==========================================================================*/
960
961 PROCEDURE POPULATE_EXEMPTION_DETAILS
962 (
963 p_bill_to_site_use IN ZX_LINES_DET_FACTORS.BILL_TO_CUST_ACCT_SITE_USE_ID%TYPE,
964 p_bill_to_location_id IN ZX_LINES_DET_FACTORS.BILL_TO_LOCATION_ID%TYPE,
965 p_bill_to_party_tax_id IN ZX_LINES_DET_FACTORS.BILL_TO_PARTY_TAX_PROF_ID%TYPE,
966 p_bill_to_site_tax_prof IN ZX_LINES_DET_FACTORS.TRADING_HQ_SITE_TAX_PROF_ID%TYPE,
967 p_hq_site_tax_prof_id IN ZX_LINES_DET_FACTORS.TRADING_HQ_SITE_TAX_PROF_ID%TYPE,
968 p_hq_party_tax_prof_id_tab IN ZX_LINES_DET_FACTORS.TRADING_HQ_PARTY_TAX_PROF_ID%TYPE,
969 p_bill_third_pty_acct_id IN ZX_LINES_DET_FACTORS.BILL_THIRD_PTY_ACCT_ID%TYPE,
970 p_product_org_id IN ZX_LINES_DET_FACTORS.PRODUCT_ORG_ID%TYPE,
971 p_product_id IN ZX_LINES_DET_FACTORS.product_id%TYPE,
972 p_cert_num IN ZX_LINES_DET_FACTORS.exempt_certificate_number%TYPE,
973 p_exmpt_rsn_code IN ZX_LINES_DET_FACTORS.exempt_reason_code%TYPE,
974 p_exemption_control_flag IN ZX_LINES_DET_FACTORS.Exemption_Control_Flag%TYPE,
975 p_tax_regime_code IN ZX_TRX_PRE_PROC_OPTIONS_GT.Tax_Regime_Code%TYPE,
976 p_position IN NUMBER,
977 p_error_status OUT NOCOPY VARCHAR2
978 ) IS
979
980 x_ret_status VARCHAR2(30);
981 x_exempt_record ZX_TCM_GET_EXEMPT_PKG.EXEMPTION_REC_TYPE;
982
983 TYPE tax_identifier_table IS TABLE OF VARCHAR2(100)
984 INDEX BY BINARY_INTEGER;
985 tax_identifier_tab tax_identifier_table;
986
987 l_api_name CONSTANT VARCHAR2(80) := 'POPULATE_EXEMPTION_DETAILS';
988 l_location_type VARCHAR2(100);
989 l_exempt_percent ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_NUMBER_ATTRIBUTE1%TYPE;
990 l_exempt_reason ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE4%TYPE;
991 l_certificate_number ZX_PRVDR_LINE_EXTNS_GT.LINE_EXT_VARCHAR_ATTRIBUTE3%TYPE;
992 l_tax_account_source_tax VARCHAR2(50);
993 l_jurisdiction_rec ZX_TCM_GEO_JUR_PKG.tax_jurisdiction_rec_type;
994 l_jurisdictions_found VARCHAR2(5);
995 l_multiple_jurisdictions_flag VARCHAR2(5);
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 TAX_ACCOUNT_SOURCE_TAX
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 ELSIF l_tax_account_source_tax = 'DISTRICT' THEN
1056 l_exempt_percent := arp_district_exempt_pct_tab(p_position);
1057 l_exempt_reason := arp_district_exempt_rs_tab(p_position);
1058 END IF;
1059 l_certificate_number := cert_num_tab(p_position);
1060
1061 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1062 FND_LOG.STRING(G_LEVEL_STATEMENT,'Percent: ',to_char(l_exempt_percent));
1063 FND_LOG.STRING(G_LEVEL_STATEMENT,'Reason: ',to_char(l_exempt_reason));
1064 FND_LOG.STRING(G_LEVEL_STATEMENT,'Certificate: ',to_char(l_certificate_number));
1065 END IF;
1066
1067 IF tax_identifier_tab(i) = 'STATE' THEN
1068 arp_state_exempt_percent_tab(p_position) := l_exempt_percent;
1069 arp_state_exempt_reason_tab(p_position) := l_exempt_reason;
1070 ELSIF tax_identifier_tab(i) = 'COUNTY' THEN
1071 arp_county_exempt_pct_tab(p_position) := l_exempt_percent;
1072 arp_county_exempt_reason_tab(p_position) := l_exempt_reason;
1073 ELSIF tax_identifier_tab(i) = 'CITY' THEN
1074 arp_city_exempt_pct_tab(p_position) := l_exempt_percent;
1075 arp_city_exempt_reason_tab(p_position) := l_exempt_reason;
1076 ELSIF tax_identifier_tab(i) = 'DISTRICT' THEN
1077 arp_district_exempt_pct_tab(p_position) := l_exempt_percent;
1078 arp_district_exempt_rs_tab(p_position) := l_exempt_reason;
1079 END IF;
1080 cert_num_tab(p_position) := l_certificate_number;
1081
1082 ELSE /* There is No Source Tax */
1083 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1084 FND_LOG.STRING(G_LEVEL_STATEMENT,'Inside Else','No Source Tax');
1085 END IF;
1086 IF event_class_code_tab(p_position) = 'CREDIT_MEMO'
1087 AND adjusted_doc_trx_id_tab(p_position) IS NOT NULL THEN
1088
1089 /* Special processing is required here and we must NOT fetch the
1090 exemption id from the tcm api. The geo level field here is
1091 used to know what level of geography we are dealing with
1092 to approprirately insert into the relevant nested tables */
1093
1094 BEGIN
1095 SELECT TAX_EXEMPTION_ID
1096 INTO x_exempt_record.exemption_id
1097 FROM ZX_LINES
1098 WHERE application_id = adj_doc_appl_id_tab(p_position)
1099 AND entity_code = adj_doc_entity_code_tab(p_position)
1100 AND event_class_code = adj_evnt_cls_code_tab(p_position)
1101 AND trx_id = adjusted_doc_trx_id_tab(p_position)
1102 AND trx_line_id = adj_doc_line_id_tab(p_position)
1103 AND trx_level_type = adj_doc_trx_level_type_tab(p_position)
1104 AND tax_regime_code = p_tax_regime_code
1105 AND tax = tax_identifier_tab(i);
1106 EXCEPTION WHEN NO_DATA_FOUND THEN
1107 x_exempt_record.exemption_id:= NULL;
1108 END;
1109
1110 IF x_exempt_record.exemption_id is NOT NULL THEN
1111 /*Proceed further only if the exemption id fetched is not null*/
1112 BEGIN
1113 SELECT rate_modifier
1114 INTO x_exempt_record.percent_exempt
1115 FROM ZX_EXEMPTIONS EXMP
1116 WHERE tax_exemption_id = x_exempt_record.exemption_id;
1117 EXCEPTION WHEN NO_DATA_FOUND THEN
1118 IF (g_level_exception >= g_current_runtime_level ) THEN
1119 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1120 END IF;
1121 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
1122 g_string :='No Data found from ZX_EXEMPTIONS for provided id';
1123 error_exception_handle(g_string);
1124 --x_messages_tbl:=g_messages_tbl;
1125 RETURN;
1126 END;
1127 END IF;
1128
1129 ELSE /* Beginning of regular processing */
1130
1131 /* Adding the Code for exemptions to work as in 11i*/
1132
1133 IF ship_to_loc_id_tab(p_position) IS NOT NULL THEN
1134 l_location_type := 'SHIP_TO';
1135 ELSIF bill_to_location_id_tab(p_position) IS NOT NULL THEN
1136 l_location_type := 'BILL_TO';
1137 ELSE
1138 l_location_type := NULL;
1139 END IF;
1140
1141 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1142 FND_LOG.STRING(G_LEVEL_STATEMENT,'Location Type: ',l_location_type);
1143 FND_LOG.STRING(G_LEVEL_STATEMENT,'Location ID: ',to_char(p_bill_to_location_id));
1144 END IF;
1145
1146 IF p_bill_to_location_id IS NOT NULL THEN
1147 ZX_TCM_GEO_JUR_PKG.get_tax_jurisdictions (
1148 p_location_id => p_bill_to_location_id,
1149 p_location_type => l_location_type,
1150 p_tax => tax_identifier_tab(i),
1151 p_tax_regime_code => p_tax_regime_code,
1152 p_trx_date => g_trx_date,
1153 x_tax_jurisdiction_rec => l_jurisdiction_rec,
1154 x_jurisdictions_found => l_jurisdictions_found,
1155 x_return_status => x_ret_status);
1156 END IF;
1157
1158 IF (x_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1159 /*FND_LOG.STRING(g_level_statement,
1160 'In Populate Exemption Details',
1161 'After calling get_tax_jurisdictions, x_return_status = '|| x_ret_status);*/
1162 RETURN;
1163 ELSE
1164 IF l_jurisdiction_rec.tax_jurisdiction_id IS NOT NULL THEN
1165 l_multiple_jurisdictions_flag := 'N';
1166 /*FND_LOG.STRING(g_level_statement,
1167 'Jurisdiction ID: ',
1168 l_jurisdiction_rec.tax_jurisdiction_id);*/
1169 ELSE
1170 IF l_jurisdictions_found = 'Y' THEN
1171 l_multiple_jurisdictions_flag := 'Y';
1172 ELSE
1173 l_multiple_jurisdictions_flag := 'N';
1174 END IF;
1175 l_jurisdiction_rec.tax_jurisdiction_id := NULL;
1176 END IF;
1177 END IF;
1178
1179 /* End of changes */
1180
1181 ZX_TCM_GET_EXEMPT_PKG.get_tax_exemptions(
1182 p_bill_to_cust_site_use_id => p_bill_to_site_use,
1183 p_bill_to_cust_acct_id => p_bill_third_pty_acct_id,
1184 p_bill_to_party_site_ptp_id => p_bill_to_location_id ,
1185 p_bill_to_party_ptp_id => p_bill_to_party_tax_id,
1186 p_sold_to_party_site_ptp_id => p_hq_site_tax_prof_id,
1187 p_sold_to_party_ptp_id => p_hq_party_tax_prof_id_tab,
1188 p_inventory_org_id => p_product_org_id,
1189 p_inventory_item_id => p_product_id,
1190 p_exempt_certificate_number => p_cert_num,
1191 p_reason_code => p_exmpt_rsn_code,
1192 p_exempt_control_flag => p_exemption_control_flag,
1193 p_tax_date => g_trx_date,
1194 p_tax_regime_code => p_tax_regime_code,
1195 p_tax => tax_identifier_tab(i),
1196 p_tax_status_code => 'STANDARD',
1197 p_tax_rate_code => 'STANDARD',
1198 p_tax_jurisdiction_id => l_jurisdiction_rec.tax_jurisdiction_id,
1199 p_multiple_jurisdictions_flag => l_multiple_jurisdictions_flag,
1200 p_event_class_rec => NULL,
1201 x_return_status => x_ret_status,
1202 x_exemption_rec => x_exempt_record
1203 );
1204
1205 END IF;
1206
1207 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1208 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' tax_identifier_tab(i) = ' || tax_identifier_tab(i));
1209 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' x_exempt_record.exemption_id = ' || x_exempt_record.exemption_id);
1210 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' x_exempt_record.exempt_certificate_number = ' || x_exempt_record.exempt_certificate_number);
1211 END IF;
1212
1213 IF x_exempt_record.exemption_id is NULL THEN
1214 use_step_tab(p_position) := 'Y';
1215 step_proc_flag_tab(p_position) := '1';
1216 crit_flag_tab(p_position) := 'R';
1217
1218 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1219 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' use_step_tab(p_position) = ' || use_step_tab(p_position));
1220 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' step_proc_flag_tab(p_position) = ' || step_proc_flag_tab(p_position));
1221 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' crit_flag_tab(p_position) = ' || crit_flag_tab(p_position));
1222 END IF;
1223 ELSIF x_exempt_record.exemption_id is NOT NULL THEN
1224 /* This condition check is necessary because we have to cater to the
1225 condition that if the special processing code logic returned a null
1226 exemption id then we have to skip the iteration all together.Also
1227 this possibility(exemption id being null)
1228 is only there for the special processing code logic .So we can safely
1229 use this condition without in any way harming the regular processing.
1230
1231 The overall logic here is like this:
1232 Initially populate the exemption id into a nested table at the same index .Meaning if a exemption
1233 id of 2000 gets derived for a state level tax then for the exemptions nested table store this
1234 value of 2000 at the 2000th location in the table.Also use the derived exemption id to
1235 call the ARP_TAX_VIEW_TAXWARE.GET_EXEMPTIONS in order to populate the exemption records table
1236 at the same location ie 2000.
1237
1238 ****NOTE**** Here p_position is the position in the linear table (in the main loop)
1239 into which the derived values are ultimately getting inserted .
1240 */
1241 IF NOT exemptions_info_tab.EXISTS(x_exempt_record.exemption_id) THEN
1242 ARP_TAX_VIEW_TAXWARE.GET_EXEMPTIONS(
1243 X_EXEMPT_RECORD.EXEMPTION_ID, --This is the input parameter for this call
1244 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct,
1245 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason,
1246 exemptions_info_tab(x_exempt_record.exemption_id).state_cert_no,
1247 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct,
1248 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason,
1249 exemptions_info_tab(x_exempt_record.exemption_id).county_cert_no,
1250 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct ,
1251 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason,
1252 exemptions_info_tab(x_exempt_record.exemption_id).city_cert_no,
1253 exemptions_info_tab(x_exempt_record.exemption_id).sec_county_exempt_percent,
1254 exemptions_info_tab(x_exempt_record.exemption_id).sec_city_exempt_percent,
1255 exemptions_info_tab(x_exempt_record.exemption_id).use_step,
1256 exemptions_info_tab(x_exempt_record.exemption_id).Step_Proc_Flag,
1257 exemptions_info_tab(x_exempt_record.exemption_id).Crit_Flag
1258 );
1259
1260 IF exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct IS NULL THEN
1261 /* If the user extensible procedure returned a null then use the default percentage
1262 rate extracted into the exemptions record*/
1263 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct := x_exempt_record.percent_exempt;
1264 END IF;
1265
1266 IF exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason IS NULL THEN
1267 /* If the user extensible procedure returned a null then use the default exempt
1268 reason extracted into the exemptions record*/
1269 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1270 END IF;
1271
1272 IF exemptions_info_tab(x_exempt_record.exemption_id).state_cert_no IS NULL THEN
1273 /* If the user extensible procedure returned a null then use the default exempt
1274 certificate number extracted into the exemptions record*/
1275 exemptions_info_tab(x_exempt_record.exemption_id).state_cert_no := SUBSTRB(x_exempt_record.exempt_certificate_number,1,15);
1276 END IF;
1277
1278 IF exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct IS NULL THEN
1279 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct:= x_exempt_record.percent_exempt;
1280 END IF;
1281
1282 IF exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason IS NULL THEN
1283 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1284 END IF;
1285
1286 IF exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct IS NULL THEN
1287 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct := x_exempt_record.percent_exempt;
1288 END IF;
1289
1290 IF exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason IS NULL THEN
1291 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1292 END IF;
1293
1294 IF exemptions_info_tab(x_exempt_record.exemption_id).Sec_County_Exempt_Percent IS NULL THEN
1295 exemptions_info_tab(x_exempt_record.exemption_id).Sec_County_Exempt_Percent := x_exempt_record.percent_exempt;
1296 END IF;
1297
1298 IF exemptions_info_tab(x_exempt_record.exemption_id).Sec_City_Exempt_Percent IS NULL THEN
1299 exemptions_info_tab(x_exempt_record.exemption_id).Sec_City_Exempt_Percent := x_exempt_record.percent_exempt;
1300 END IF;
1301
1302 END IF;
1303 IF tax_identifier_tab(i) = 'STATE' THEN
1304 arp_state_exempt_percent_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct;
1305 arp_state_exempt_reason_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason;
1306
1307 ELSIF tax_identifier_tab(i) = 'COUNTY' THEN
1308 arp_county_exempt_pct_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct;
1309 arp_county_exempt_reason_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason;
1310 county_cert_no_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).county_cert_no;
1311 sec_county_exempt_pct_tab(p_position) :=exemptions_info_tab(x_exempt_record.exemption_id).Sec_County_Exempt_Percent;
1312
1313 ELSIF tax_identifier_tab(i) = 'CITY' THEN
1314 arp_city_exempt_pct_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct;
1315 arp_city_exempt_reason_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason;
1316 city_cert_no_tab(p_position) :=exemptions_info_tab(x_exempt_record.exemption_id).city_cert_no;
1317 sec_city_exempt_pct_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).Sec_City_Exempt_Percent;
1318
1319 END IF;
1320 cert_num_tab(p_position) := nvl(p_cert_num,exemptions_info_tab(x_exempt_record.exemption_id).state_cert_no);
1321 END IF; /* Tax Account Source Tax */
1322 END IF; /*End of special processing if*/
1323
1324 END LOOP;
1325
1326 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1327 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));
1328 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));
1329 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));
1330 -- 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));
1331 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' p_cert_num = ' || p_cert_num);
1332 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' cert_num_tab(p_position) = ' || cert_num_tab(p_position));
1333 END IF;
1334
1335 p_error_status := x_ret_status;
1336
1337 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1338 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1339 ' RETURN_STATUS = ' || p_error_status);
1340 END IF;
1341
1342 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1343 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1344 END IF;
1345
1346 EXCEPTION
1347 WHEN OTHERS THEN
1348 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
1349 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1350 END IF;
1351 p_error_status := FND_API.G_RET_STS_UNEXP_ERROR;
1352
1353 END POPULATE_EXEMPTION_DETAILS;
1354
1355 /*===========================================================================+
1356 | PROCEDURE
1357 | Initialize_Nested_Tables
1358 | IN
1359 |
1360 | OUT NOCOPY
1361 |
1362 | DESCRIPTION
1363 | This is a start up procedure that deletes any existing data from the nested
1364 | tables
1365 |
1366 |
1367 | SCOPE - PRIVATE
1368 |
1369 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1370 |
1371 | CALLED FROM
1372 |
1373 |
1374 | MODIFICATION HISTORY
1375 | 08/13/2004 Arnab Sengupta Created.
1376 |
1377 +==========================================================================*/
1378
1379 PROCEDURE Initialize_Nested_Tables
1380 IS
1381 l_api_name CONSTANT VARCHAR2(80) := 'INITIALIZE_NESTED_TABLES';
1382 BEGIN
1383 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1384 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1385 END IF;
1386
1387 internal_org_id_tab.DELETE;
1388 application_id_tab.DELETE;
1389 entity_code_tab.DELETE;
1390 event_class_code_tab.DELETE;
1391 trx_id_tab.DELETE;
1392 tax_provider_id_tab.DELETE;
1393 tax_regime_code_tab.DELETE;
1394 trx_line_type_tab.DELETE;
1395 trx_line_id_tab.DELETE;
1396 product_id_tab.DELETE;
1397 Product_Org_Id_tab.DELETE;
1398 ship_to_tx_id_tab.DELETE;
1399 ship_from_tx_id_tab.DELETE;
1400 cert_num_tab.DELETE;
1401 exmpt_rsn_code_tab.DELETE;
1402 exemption_control_flag_tab.DELETE;
1403 ship_to_site_tax_prof_tab.DELETE;
1404 ship_to_loc_id_tab.DELETE;
1405 exmpt_control_flg_tab.DELETE;
1406 arp_tax_type_tab.DELETE;
1407 arp_product_code_tab.DELETE;
1408 use_step_tab.DELETE;
1409 arp_audit_flag_tab.DELETE;
1410 arp_ship_to_add_tab.DELETE;
1411 arp_ship_from_add_tab.DELETE;
1412 arp_poa_add_code_tab.DELETE;
1413 arp_customer_code_tab.DELETE;
1414 arp_customer_name_tab.DELETE;
1415 arp_company_code_tab.DELETE;
1416 arp_division_code_tab.DELETE;
1417 arp_vnd_ctrl_exmpt_tab.DELETE;
1418 arp_use_nexpro_tab.DELETE;
1419 arp_service_ind_tab.DELETE;
1420 arp_tax_sel_param_tab.DELETE;
1421 arp_transaction_date_tab.DELETE;
1422 ship_to_address_id_tab.DELETE;
1423 ship_to_party_id_tab.DELETE;
1424 arp_state_exempt_reason_tab.DELETE;
1425 arp_county_exempt_reason_tab.DELETE;
1426 arp_city_exempt_reason_tab.DELETE;
1427 step_proc_flag_tab.DELETE;
1428 arp_state_exempt_percent_tab.DELETE;
1429 arp_county_exempt_pct_tab.DELETE;
1430 arp_city_exempt_pct_tab.DELETE;
1431 ship_to_site_use_tab.DELETE;
1432 bill_to_site_use_tab.DELETE;
1433 bill_to_site_tax_prof_tab.DELETE;
1434 bill_to_party_tax_id_tab.DELETE;
1435 bill_to_location_id_tab.DELETE;
1436 trad_hq_site_tax_prof_id_tab.DELETE;
1437 trad_hq_party_tax_prof_id_tab.DELETE;
1438 bill_third_pty_acct_id_tab.DELETE;
1439 line_level_action_tab.DELETE;
1440 adjusted_doc_trx_id_tab.DELETE;
1441 line_amount_tab.DELETE;
1442 exemptions_info_tab.DELETE;
1443 trx_type_id_tab.DELETE;
1444 state_cert_no_tab.DELETE;
1445 county_cert_no_tab.DELETE;
1446 city_cert_no_tab.DELETE;
1447 crit_flag_tab.DELETE;
1448 sec_county_exempt_pct_tab.DELETE;
1449 sec_city_exempt_pct_tab.DELETE;
1450 adj_doc_appl_id_tab.DELETE;
1451 adj_doc_entity_code_tab.DELETE;
1452 adj_evnt_cls_code_tab.DELETE;
1453 adj_doc_line_id_tab.DELETE;
1454 adj_doc_trx_level_type_tab.DELETE;
1455 ship_third_pty_site_tab.DELETE;
1456 bill_third_pty_site_tab.DELETE;
1457
1458 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1459 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||l_api_name||'()-');
1460 END IF;
1461
1462 EXCEPTION WHEN COLLECTION_IS_NULL THEN
1463 NULL;
1464
1465 END Initialize_Nested_Tables;
1466
1467 PROCEDURE Initialize_Exemption_Tables IS
1468
1469 l_api_name CONSTANT VARCHAR2(80) := 'INITIALIZE_EXEMPTION_TABLES';
1470 BEGIN
1471 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1472 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1473 END IF;
1474
1475 FOR i in 1..nvl(internal_org_id_tab.last,0) --Loop 1
1476 LOOP
1477
1478
1479 arp_state_exempt_reason_tab(i):= NULL;
1480 arp_state_exempt_percent_tab(i):= NULL;
1481 state_cert_no_tab(i):= NULL;
1482 use_step_tab(i):= NULL;
1483 step_proc_flag_tab(i):= NULL;
1484 crit_flag_tab(i):= NULL;
1485 arp_county_exempt_reason_tab(i):= NULL;
1486 arp_county_exempt_pct_tab(i):= NULL;
1487 county_cert_no_tab(i):= NULL;
1488 sec_county_exempt_pct_tab(i):= NULL;
1489 arp_city_exempt_reason_tab(i):= NULL;
1490 arp_city_exempt_pct_tab(i):= NULL;
1491 city_cert_no_tab(i):= NULL;
1492 sec_city_exempt_pct_tab(i):= NULL;
1493
1494 END LOOP;
1495 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1496 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||l_api_name||'()-');
1497 END IF;
1498
1499
1500 END Initialize_Exemption_Tables;
1501
1502 /*===========================================================================+
1503 | FUNCTION |
1504 | CHECK_GEOCODE |
1505 | |
1506 | DESCRIPTION |
1507 | Returns TRUE if the GEOCODE seems to be valid |
1508 | (in the format SSZZZZZGG) |
1509 | |
1510 | SCOPE - PRIVATE |
1511 | |
1512 | MODIFICATION HISTORY |
1513 | 01-NOV-05 Santosh Vaze Created for Bug 4668932 |
1514 | |
1515 +===========================================================================*/
1516
1517
1518 FUNCTION CHECK_GEOCODE(p_geocode IN VARCHAR2)
1519 RETURN BOOLEAN
1520 IS
1521 l_api_name CONSTANT VARCHAR2(80) := 'CHECK_GEOCODE';
1522 BEGIN
1523 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1524 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1525 END IF;
1526 if substrb(p_geocode, 1, 2) between 'AA' and 'ZZ' and
1527 substrb(p_geocode, 3, 5) between '00000' and '99999' and
1528 substrb(p_geocode, 8, 2) between '00' and '99' then
1529 return TRUE;
1530 end if;
1531
1532 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1533 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME ||l_api_name||'()-');
1534 END IF;
1535
1536 return FALSE;
1537 END CHECK_GEOCODE;
1538
1539 PROCEDURE DERIVE_TRX_LEVEL_ATTR
1540 IS
1541
1542 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_TRX_LEVEL_ATTR';
1543
1544 BEGIN
1545 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1546 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1547 END IF;
1548
1549 g_org_id := NULL;
1550 g_sales_repid := NULL;
1551 IF event_class_code_tab(i) = 'INVOICE_ADJUSTMENT' THEN
1552 IF adjusted_doc_trx_id_tab(i) IS NOT NULL THEN
1553 BEGIN
1554 SELECT org_id
1555 , primary_salesrep_id
1556 INTO g_org_id
1557 , g_sales_repid
1558 FROM ra_customer_trx_all
1559 WHERE customer_trx_id = adjusted_doc_trx_id_tab(i);
1560 EXCEPTION
1561 WHEN OTHERS THEN
1562 g_org_id := internal_org_id_tab(i);
1563 g_sales_repid := NULL;
1564 END;
1565 END IF;
1566 ELSE
1567 IF trx_id_tab(i) IS NOT NULL THEN
1568 BEGIN
1569 SELECT org_id
1570 , primary_salesrep_id
1571 INTO g_org_id
1572 , g_sales_repid
1573 FROM ra_customer_trx_all
1574 WHERE customer_trx_id = trx_id_tab(i);
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 g_org_id := internal_org_id_tab(i);
1578 g_sales_repid := NULL;
1579 END;
1580 END IF;
1581 END IF;
1582
1583 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1584 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1585 END IF;
1586
1587 END DERIVE_TRX_LEVEL_ATTR;
1588
1589 PROCEDURE DERIVE_AUDIT_FLAG
1590 IS
1591
1592 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_AUDIT_FLAG';
1593
1594 BEGIN
1595 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1596 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1597 END IF;
1598
1599 IF l_view_name = 'TAX_ADJUSTMENTS_V_A' THEN
1600 BEGIN
1601 arp_audit_flag_tab(i) := ARP_TAX_VIEW_TAXWARE.AUDIT_FLAG
1602 (l_view_name,
1603 trx_id_tab(i),
1604 trx_line_id_tab(i));
1605 EXCEPTION WHEN OTHERS THEN
1606 arp_audit_flag_tab(i):= NULL;
1607 END;
1608
1609 IF arp_audit_flag_tab(i) IS NULL THEN
1610 BEGIN
1611 SELECT nvl(substrb(act.attribute15, 1, 1), 'Y')
1612 INTO arp_audit_flag_tab(i)
1613 FROM ar_receivables_trx act
1614 WHERE act.receivables_trx_id IN
1615 (SELECT adj.receivables_trx_id
1616 FROM ar_adjustments adj
1617 WHERE adj.adjustment_id = trx_id_tab(i))
1618 AND act.org_id = internal_org_id_tab(i);
1619 EXCEPTION WHEN OTHERS THEN
1620 arp_audit_flag_tab(i) := 'Y';
1621 END;
1622 END IF;
1623 ELSE
1624 arp_audit_flag_tab(i) := 'Y';
1625 END IF;
1626
1627 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1628 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1629 'Quote Flag from ZX_GLOBAL_STRUCTURE'||ZX_GLOBAL_STRUCTURES_PKG.g_event_class_rec.quote_flag);
1630 END IF;
1631
1632 IF ZX_GLOBAL_STRUCTURES_PKG.g_event_class_rec.quote_flag = 'Y' THEN
1633 arp_audit_flag_tab(i) := 'N';
1634 END IF;
1635 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1636 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Audit Flag :'||arp_audit_flag_tab(i));
1637 END IF;
1638
1639 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1640 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1641 END IF;
1642
1643 END DERIVE_AUDIT_FLAG;
1644
1645 PROCEDURE DERIVE_PRODUCT_CODE
1646 IS
1647
1648 l_product_id ZX_LINES_DET_FACTORS.product_id%TYPE;
1649 l_memo_line_id NUMBER;
1650 l_master_org_id oe_system_parameters_all.master_organization_id%type;
1651
1652 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_PRODUCT_CODE';
1653
1654 BEGIN
1655 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1656 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1657 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||' ',G_PKG_NAME||': '
1658 ||l_api_name||'l_view_name :'||l_view_name);
1659 END IF;
1660
1661 /* The product code function is not attached only to this view hence the if condition reads like this*/
1662
1663 IF l_view_name <> 'TAX_ADJUSTMENTS_V_A' THEN
1664
1665 /* Derive the product_id depending on the null or not null value of product_org_id*/
1666 IF product_org_id_tab(i) IS NOT NULL THEN
1667 l_product_id := product_id_tab(i);
1668 l_memo_line_id := NULL;
1669 ELSE
1670 l_product_id := NULL;
1671 l_memo_line_id := product_id_tab(i);
1672 END IF;
1673
1674 BEGIN
1675 arp_product_code_tab(i) := ARP_TAX_VIEW_TAXWARE.PRODUCT_CODE(l_view_name
1676 , trx_id_tab(i)
1677 , trx_line_id_tab(i)
1678 , l_product_id
1679 , l_memo_line_id);
1680 EXCEPTION WHEN OTHERS THEN
1681 arp_product_code_tab(i) := NULL;
1682 END;
1683
1684 IF arp_product_code_tab(i) IS NULL THEN
1685 BEGIN
1686 SELECT org_id
1687 INTO g_org_id
1688 FROM ra_customer_trx_lines_all
1689 WHERE customer_trx_id = trx_id_tab(i)
1690 AND customer_trx_line_id = trx_line_id_tab(i);
1691 EXCEPTION
1692 WHEN OTHERS THEN
1693 g_org_id := internal_org_id_tab(i);
1694 END;
1695
1696
1697 /* Bug 5612024
1698 IF MO_GLOBAL.get_current_org_id <> nvl(g_org_id, -1) THEN
1699 MO_GLOBAL.Set_Policy_Context('S', g_org_id);
1700 END IF;
1701 */
1702 l_master_org_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID', g_org_id);
1703
1704 BEGIN
1705 SELECT segment1
1706 INTO arp_product_code_tab(i)
1707 FROM mtl_system_items
1708 WHERE inventory_item_id = l_product_id
1709 AND organization_id = l_master_org_id;
1710 EXCEPTION
1711 WHEN OTHERS THEN
1712 arp_product_code_tab(i) := NULL;
1713 END;
1714 END IF;
1715 ELSE
1716 arp_product_code_tab(i) := NULL;
1717 END IF;
1718
1719 IF (g_level_statement >= g_current_runtime_level ) THEN
1720 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1721 ' arp_product_code_tab(i) = ' || arp_product_code_tab(i));
1722 END IF;
1723
1724 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1725 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1726 END IF;
1727
1728 END DERIVE_PRODUCT_CODE;
1729
1730 PROCEDURE DERIVE_SHIP_TO_ADDRESS_CODE
1731 IS
1732 l_tax_jurisdiction_rec ZX_TCM_GEO_JUR_PKG.tax_jurisdiction_rec_type;
1733 x_ret_status VARCHAR2(30);
1734 l_jurisdictions_found VARCHAR2(1);
1735 l_ship_to_geocode varchar2(30);
1736 l_state_code VARCHAR2(60);
1737 l_postal_code VARCHAR2(60);
1738
1739 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_SHIP_TO_ADDRESS_CODE';
1740
1741 BEGIN
1742 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1743 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1744 END IF;
1745
1746 ship_to_address_id_tab(i) := NULL;
1747 IF ship_to_site_use_tab(i) is NOT NULL THEN
1748 BEGIN
1749 SELECT cust_site_uses.cust_acct_site_id
1750 INTO ship_to_address_id_tab(i)
1751 FROM HZ_CUST_SITE_USES_ALL cust_site_uses
1752 WHERE cust_site_uses.site_use_id = ship_to_site_use_tab(i);
1753 EXCEPTION WHEN OTHERS THEN
1754 ship_to_address_id_tab(i) := NULL;
1755 END;
1756 END IF;
1757
1758 IF ship_to_address_id_tab(i) is NULL THEN
1759 BEGIN
1760 SELECT cust_acct_site_id
1761 INTO ship_to_address_id_tab(i)
1762 FROM HZ_CUST_SITE_USES_ALL
1763 WHERE site_use_id = bill_to_site_use_tab(i);
1764 EXCEPTION WHEN NO_DATA_FOUND THEN
1765 ship_to_address_id_tab(i) := NULL;
1766 END;
1767 END IF;
1768
1769 BEGIN
1770 arp_ship_to_add_tab(i) := ARP_TAX_VIEW_TAXWARE.SHIP_TO_ADDRESS_CODE
1771 (l_view_name,
1772 trx_id_tab(i),
1773 trx_line_id_tab(i),
1774 ship_to_address_id_tab(i),
1775 ship_to_loc_id_tab(i),
1776 g_trx_date,
1777 NULL,--p_ship_to_state
1778 NULL--p_postal_code
1779 );
1780 EXCEPTION WHEN OTHERS THEN
1781 arp_ship_to_add_tab(i):= NULL;
1782 END;
1783
1784 /* Bug 4668932 */
1785 IF arp_ship_to_add_tab(i) IS NULL
1786 THEN
1787 BEGIN
1788 SELECT decode(nvl(loc.sales_tax_inside_city_limits,'1'),'0','0','1') || loc.sales_tax_geocode
1789 INTO arp_ship_to_add_tab(i)
1790 FROM hz_locations loc
1791 WHERE loc.location_id = nvl(ship_to_loc_id_tab(i), bill_to_location_id_tab(i));
1792 EXCEPTION WHEN OTHERS THEN
1793 arp_ship_to_add_tab(i) := NULL;
1794 END;
1795 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1796 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME,'GeoCode Override: '||arp_ship_to_add_tab(i));
1797 END IF;
1798
1799 IF NOT check_geocode(substrb(nvl(arp_ship_to_add_tab(i),'XXXXXXXXXX'),2,9)) THEN
1800 ZX_TCM_GEO_JUR_PKG.get_tax_jurisdictions(
1801 p_location_id => nvl(ship_to_loc_id_tab(i), bill_to_location_id_tab(i)),
1802 p_location_type => 'SHIP_TO',
1803 p_tax => 'CITY',
1804 p_tax_regime_code => tax_regime_code_tab(i),
1805 p_trx_date => g_trx_date,
1806 x_tax_jurisdiction_rec => l_tax_jurisdiction_rec,
1807 x_jurisdictions_found => l_jurisdictions_found,
1808 x_return_status => x_ret_status);
1809
1810 IF x_ret_status = FND_API.G_RET_STS_SUCCESS THEN
1811 IF l_jurisdictions_found = 'Y' THEN
1812 IF l_tax_jurisdiction_rec.tax_jurisdiction_code IS NOT NULL THEN
1813 l_ship_to_geocode := l_tax_jurisdiction_rec.tax_jurisdiction_code;
1814
1815 arp_ship_to_add_tab(i) := '1' || substr(l_ship_to_geocode,4,2);
1816 arp_ship_to_add_tab(i) := arp_ship_to_add_tab(i) || substr(l_ship_to_geocode,-8,5);
1817 --arp_ship_to_add_tab(i) := arp_ship_to_add_tab(i) || substr(l_ship_to_geocode,-5,5);
1818 arp_ship_to_add_tab(i) := arp_ship_to_add_tab(i) ||substr(l_ship_to_geocode,-2);
1819 --arp_ship_to_add_tab(i) := arp_ship_to_add_tab(i) || '00';
1820 END IF;
1821 END IF;
1822 IF l_jurisdictions_found = 'N' OR (l_jurisdictions_found = 'Y' AND l_tax_jurisdiction_rec.tax_jurisdiction_code IS NULL) THEN
1823 BEGIN
1824 SELECT state, substr(postal_code,1,5)
1825 INTO l_state_code, l_postal_code
1826 FROM HZ_LOCATIONS
1827 WHERE location_id = NVL(ship_to_loc_id_tab(i), bill_to_location_id_tab(i));
1828 EXCEPTION
1829 WHEN OTHERS THEN
1830 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1831 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1832 'No data found for this location_id : '||NVL(ship_to_loc_id_tab(i), bill_to_location_id_tab(i)));
1833 END IF;
1834 END;
1835 arp_ship_to_add_tab(i) := '1' || NVL(l_state_code,'CA') || l_postal_code || '00';
1836 END IF;
1837 END IF;
1838 END IF;
1839 END IF;
1840
1841 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1842 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1843 END IF;
1844
1845 END DERIVE_SHIP_TO_ADDRESS_CODE;
1846
1847 PROCEDURE DERIVE_SHIP_FROM_ADDRESS_CODE
1848 IS
1849 l_sfr_geocode VARCHAR2(10);
1850 l_sfr_in_out_flag VARCHAR2(1);
1851 l_flag BOOLEAN;
1852 l_inventory_item_id NUMBER;
1853 l_master_org_id oe_system_parameters_all.master_organization_id%type;
1854 l_ship_from_party_id ZX_PARTY_TAX_PROFILE.PARTY_ID%TYPE;
1855
1856 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_SHIP_FROM_ADDRESS_CODE';
1857
1858 BEGIN
1859 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1860 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1861 END IF;
1862
1863 BEGIN
1864 IF ship_from_tx_id_tab(i) IS NOT NULL THEN
1865 IF p_party_id_tbl.EXISTS(ship_from_tx_id_tab(i)) THEN
1866 l_ship_from_party_id := p_party_id_tbl(ship_from_tx_id_tab(i));
1867 ELSE
1868 SELECT party_id
1869 INTO l_ship_from_party_id
1870 FROM ZX_PARTY_TAX_PROFILE
1871 WHERE party_tax_profile_id = ship_from_tx_id_tab(i);
1872
1873 p_party_id_tbl(ship_from_tx_id_tab(i)) := l_ship_from_party_id;
1874 END IF;
1875 END IF;
1876
1877 EXCEPTION WHEN NO_DATA_FOUND THEN
1878 l_ship_from_party_id := NULL;
1879 END;
1880
1881 arp_ship_from_add_tab(i) := ARP_TAX_VIEW_TAXWARE.SHIP_FROM_ADDRESS_CODE
1882 (l_view_name,
1883 trx_id_tab(i),
1884 trx_line_id_tab(i),
1885 l_ship_from_party_id);
1886
1887 IF arp_ship_from_add_tab(i) is NULL THEN
1888 BEGIN
1889 select lc.loc_information13
1890 into arp_ship_from_add_tab(i)
1891 from hr_locations_all lc, hr_organization_units hr
1892 where hr.organization_id = l_ship_from_party_id
1893 and hr.location_id = lc.location_id;
1894
1895 arp_ship_from_add_tab(i) := '1'||arp_ship_from_add_tab(i);
1896 EXCEPTION WHEN OTHERS THEN
1897 arp_ship_from_add_tab(i):=arp_tax_view_taxware.USE_SHIP_TO;
1898 END;
1899 END IF;
1900
1901 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1902 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1903 END IF;
1904
1905 END DERIVE_SHIP_FROM_ADDRESS_CODE;
1906
1907 PROCEDURE DERIVE_POA_ADDRESS_CODE
1908 IS
1909 l_poa_geocode VARCHAR2(10);
1910 l_poa_in_out_flag VARCHAR2(1);
1911
1912 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_POA_ADDRESS_CODE';
1913
1914 BEGIN
1915 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1916 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1917 END IF;
1918
1919 BEGIN
1920 arp_poa_add_code_tab(i) := ARP_TAX_VIEW_TAXWARE.POA_ADDRESS_CODE
1921 (l_view_name,
1922 trx_id_tab(i),
1923 trx_line_id_tab(i));
1924 Exception When Others then
1925 arp_poa_add_code_tab(i) := NULL;
1926 End;
1927
1928 IF arp_poa_add_code_tab(i) is NULL then
1929 BEGIN
1930 SELECT zxprdopt.SALES_TAX_GEOCODE
1931 INTO arp_poa_add_code_tab(i)
1932 FROM ZX_PRODUCT_OPTIONS_ALL zxprdopt
1933 WHERE zxprdopt.application_id = 222
1934 AND zxprdopt.org_id = internal_org_id_tab(i)
1935 AND zxprdopt.event_class_mapping_id = (SELECT EVENT_CLASS_MAPPING_ID
1936 FROM ZX_EVNT_CLS_MAPPINGS
1937 WHERE EVENT_CLASS_CODE = event_class_code_tab(i)
1938 AND APPLICATION_ID = application_id_tab(i)
1939 AND ENTITY_CODE = entity_code_tab(i));
1940 arp_poa_add_code_tab(i) := '1'||arp_poa_add_code_tab(i);
1941 EXCEPTION WHEN OTHERS THEN
1942 arp_poa_add_code_tab(i) := arp_tax_view_taxware.USE_SHIP_TO;
1943 END;
1944 END IF;
1945
1946 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1947 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1948 ' arp_poa_add_code_tab(i) = ' || arp_poa_add_code_tab(i));
1949 END IF;
1950
1951 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1952 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1953 END IF;
1954
1955 END DERIVE_POA_ADDRESS_CODE;
1956
1957 PROCEDURE DERIVE_POO_ADDRESS_CODE
1958 IS
1959 l_poa_geocode VARCHAR2(10);
1960 l_poa_in_out_flag VARCHAR2(1);
1961
1962 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_POO_ADDRESS_CODE';
1963
1964 BEGIN
1965 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1966 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1967 END IF;
1968
1969 BEGIN
1970 arp_poo_add_code_tab(i) := ARP_TAX_VIEW_TAXWARE.POO_ADDRESS_CODE
1971 (l_view_name,
1972 trx_id_tab(i),
1973 trx_line_id_tab(i),
1974 g_sales_repid);
1975 Exception When Others then
1976 arp_poo_add_code_tab(i) := NULL;
1977 End;
1978
1979 IF arp_poo_add_code_tab(i) is NULL then
1980 BEGIN
1981 select sales_tax_geocode
1982 into arp_poo_add_code_tab(i)
1983 from ra_salesreps
1984 where salesrep_id = g_sales_repid;
1985
1986 arp_poo_add_code_tab(i) := '1'||arp_poo_add_code_tab(i);
1987
1988 EXCEPTION WHEN OTHERS THEN
1989 arp_poo_add_code_tab(i) := arp_tax_view_taxware.USE_SHIP_TO;
1990 END;
1991 END IF;
1992
1993
1994 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1995 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1996 ' arp_poo_add_code_tab(i) = ' || arp_poo_add_code_tab(i));
1997 END IF;
1998
1999 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2000 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2001 END IF;
2002
2003 END DERIVE_POO_ADDRESS_CODE;
2004
2005 PROCEDURE DERIVE_CUSTOMER_CODE
2006 IS
2007
2008 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_CUSTOMER_CODE';
2009
2010 BEGIN
2011 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2012 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2013 END IF;
2014
2015 arp_customer_code_tab(i) := ARP_TAX_VIEW_TAXWARE.CUSTOMER_CODE
2016 (l_view_name,
2017 trx_id_tab(i),
2018 trx_line_id_tab(i));
2019 IF arp_customer_code_tab(i) IS NULL THEN
2020 /* Bug 5007293: During negation: ZX_PTNR_LOCATION_INFO_GT is not yet populated */
2021
2022 BEGIN
2023
2024 SELECT account_number
2025 INTO arp_customer_code_tab(i)
2026 FROM HZ_CUST_ACCOUNTS
2027 WHERE cust_account_id = bill_third_pty_acct_id_tab(i);
2028
2029 EXCEPTION
2030 WHEN OTHERS THEN
2031 arp_customer_code_tab(i) := NULL;
2032 END;
2033
2034 IF arp_customer_code_tab(i) IS NULL THEN
2035 IF g_line_negation THEN
2036 IF p_party_number_tbl.EXISTS(bill_to_party_tax_id_tab(i)) THEN
2037 arp_customer_code_tab(i) := p_party_number_tbl(bill_to_party_tax_id_tab(i));
2038 ELSE
2039 BEGIN
2040 SELECT pty.party_number
2041 INTO arp_customer_code_tab(i)
2042 FROM hz_parties pty,
2043 zx_party_tax_profile ptp
2044 WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
2045 AND ptp.party_id = pty.party_id;
2046 EXCEPTION WHEN OTHERS THEN
2047 arp_customer_code_tab(i) := NULL;
2048 END;
2049 p_party_number_tbl(bill_to_party_tax_id_tab(i)) := arp_customer_code_tab(i);
2050 END IF;
2051 ELSE
2052 BEGIN
2053 SELECT zpli.bill_to_party_number
2054 INTO arp_customer_code_tab(i)
2055 FROM ZX_PTNR_LOCATION_INFO_GT zpli
2056 , ZX_EVNT_CLS_MAPPINGS zecm
2057 WHERE zpli.EVENT_CLASS_MAPPING_ID = zecm.EVENT_CLASS_MAPPING_ID
2058 AND zecm.EVENT_CLASS_CODE = event_class_code_tab(i)
2059 AND zecm.APPLICATION_ID = application_id_tab(i)
2060 AND zecm.ENTITY_CODE = entity_code_tab(i)
2061 AND zpli.TRX_ID = trx_id_tab(i)
2062 AND zpli.TRX_LINE_ID = trx_line_id_tab(i);
2063 EXCEPTION WHEN OTHERS THEN
2064 arp_customer_code_tab(i) := NULL;
2065 END;
2066
2067 IF arp_customer_code_tab(i) IS NULL THEN
2068 IF p_party_number_tbl.EXISTS(bill_to_party_tax_id_tab(i)) THEN
2069 arp_customer_code_tab(i) := p_party_number_tbl(bill_to_party_tax_id_tab(i));
2070 ELSE
2071 BEGIN
2072 SELECT pty.party_number
2073 INTO arp_customer_code_tab(i)
2074 FROM hz_parties pty,
2075 zx_party_tax_profile ptp
2076 WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
2077 AND ptp.party_id = pty.party_id;
2078 EXCEPTION WHEN OTHERS THEN
2079 arp_customer_code_tab(i) := NULL;
2080 END;
2081 p_party_number_tbl(bill_to_party_tax_id_tab(i)) := arp_customer_code_tab(i);
2082 END IF;
2083 END IF;
2084 END IF;
2085 END IF;
2086 END IF;
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 EXCEPTION WHEN OTHERS THEN
2092 arp_customer_code_tab(i):= NULL;
2093 END DERIVE_CUSTOMER_CODE;
2094
2095 PROCEDURE DERIVE_CUSTOMER_NAME
2096 IS
2097
2098 l_party_id ZX_PARTY_TAX_PROFILE.PARTY_ID%TYPE;
2099 l_ptp_id ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
2100 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_CUSTOMER_NAME';
2101
2102 BEGIN
2103 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2104 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2105 END IF;
2106
2107
2108 BEGIN
2109 arp_customer_name_tab(i) := ARP_TAX_VIEW_TAXWARE.CUSTOMER_NAME
2110 (l_view_name,
2111 trx_id_tab(i),
2112 trx_line_id_tab(i));
2113
2114 IF arp_customer_name_tab(i) IS NULL
2115 THEN
2116 /* Bug 5007293: During negation: ZX_PTNR_LOCATION_INFO_GT is not yet populated */
2117 IF g_line_negation THEN
2118 BEGIN
2119 SELECT pty.party_name
2120 INTO arp_customer_name_tab(i)
2121 FROM hz_parties pty,
2122 zx_party_tax_profile ptp
2123 WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
2124 AND ptp.party_id = pty.party_id;
2125 EXCEPTION WHEN OTHERS THEN
2126 arp_customer_name_tab(i) := NULL;
2127 END;
2128 ELSE
2129 BEGIN
2130 SELECT zpli.BILL_TO_PARTY_NAME
2131 INTO arp_customer_name_tab(i)
2132 FROM ZX_PTNR_LOCATION_INFO_GT zpli
2133 , ZX_EVNT_CLS_MAPPINGS zecm
2134 WHERE zpli.EVENT_CLASS_MAPPING_ID = zecm.EVENT_CLASS_MAPPING_ID
2135 AND zecm.EVENT_CLASS_CODE = event_class_code_tab(i)
2136 AND zecm.APPLICATION_ID = application_id_tab(i)
2137 AND zecm.ENTITY_CODE = entity_code_tab(i)
2138 AND zpli.TRX_ID = trx_id_tab(i)
2139 AND zpli.TRX_LINE_ID = trx_line_id_tab(i);
2140 EXCEPTION WHEN OTHERS THEN
2141 arp_customer_name_tab(i) := NULL;
2142 END;
2143
2144 IF arp_customer_name_tab(i) IS NULL
2145 THEN
2146 BEGIN
2147 SELECT pty.party_name
2148 INTO arp_customer_name_tab(i)
2149 FROM hz_parties pty,
2150 zx_party_tax_profile ptp
2151 WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
2152 AND ptp.party_id = pty.party_id;
2153 EXCEPTION WHEN OTHERS THEN
2154 arp_customer_name_tab(i) := NULL;
2155 END;
2156 END IF;
2157 END IF;
2158 END IF;
2159 EXCEPTION WHEN OTHERS THEN
2160 arp_customer_name_tab(i):= NULL;
2161 END;
2162
2163 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2164 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2165 END IF;
2166
2167 END DERIVE_CUSTOMER_NAME;
2168
2169 PROCEDURE DERIVE_DIVISION_CODE
2170 IS
2171
2172 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_DIVISION_CODE';
2173
2174 BEGIN
2175 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2176 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2177 END IF;
2178
2179 arp_division_code_tab(i) := ARP_TAX_VIEW_TAXWARE.DIVISION_CODE
2180 (l_view_name,
2181 trx_id_tab(i),
2182 trx_line_id_tab(i));
2183 if(arp_division_code_tab(i) is NULL) then
2184 arp_division_code_tab(i):='01';
2185 end if;
2186
2187 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2188 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2189 END IF;
2190
2191 END DERIVE_DIVISION_CODE;
2192
2193 PROCEDURE DERIVE_TRANSACTION_DATE
2194 IS
2195
2196 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_TRANSACTION_DATE';
2197
2198 BEGIN
2199 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2200 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2201 END IF;
2202
2203 arp_transaction_date_tab(i) := ARP_TAX_VIEW_TAXWARE.TRANSACTION_DATE
2204 (l_view_name,
2205 trx_id_tab(i),
2206 trx_line_id_tab(i));
2207 IF arp_transaction_date_tab(i) IS NULL THEN
2208 BEGIN
2209 SELECT trx_line_gl_date
2210 INTO arp_transaction_date_tab(i)
2211 FROM zx_lines_det_factors
2212 WHERE internal_organization_id = internal_org_id_tab(i)
2213 AND application_id = application_id_tab(i)
2214 AND Entity_Code = entity_code_tab(i)
2215 AND Event_Class_Code = event_class_code_tab(i)
2216 AND trx_id = trx_id_tab(i)
2217 AND trx_line_id = trx_line_id_tab(i);
2218
2219 EXCEPTION WHEN OTHERS THEN
2220 arp_transaction_date_tab(i) := NULL;
2221 END;
2222 END IF;
2223
2224 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2225 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2226 END IF;
2227
2228 EXCEPTION WHEN OTHERS THEN
2229 arp_transaction_date_tab(i) := NULL;
2230 END DERIVE_TRANSACTION_DATE;
2231
2232 PROCEDURE DERIVE_COMPANY_CODE
2233 IS
2234
2235 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_COMPANY_CODE';
2236
2237 BEGIN
2238 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2239 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2240 END IF;
2241
2242 arp_company_code_tab(i) := ARP_TAX_VIEW_TAXWARE.COMPANY_CODE
2243 (l_view_name,
2244 trx_id_tab(i),
2245 trx_line_id_tab(i));
2246 IF (arp_company_code_tab(i) is NULL) THEN
2247 arp_company_code_tab(i) := '01';
2248 END IF;
2249
2250 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2251 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2252 END IF;
2253
2254 END DERIVE_COMPANY_CODE;
2255
2256 PROCEDURE DERIVE_VND_CTRL_EXMPT
2257 IS
2258
2259 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_VND_CTRL_EXMPT';
2260
2261 BEGIN
2262 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2263 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2264 END IF;
2265
2266 arp_vnd_ctrl_exmpt_tab(i):= ARP_TAX_VIEW_TAXWARE.VENDOR_CONTROL_EXEMPTIONS
2267 (l_view_name,
2268 trx_id_tab(i),
2269 trx_line_id_tab(i),
2270 trx_type_id_tab(i));
2271 if(arp_vnd_ctrl_exmpt_tab(i)) is NULL then
2272 BEGIN
2273
2274 select attribute1
2275 into arp_vnd_ctrl_exmpt_tab(i)
2276 from ra_cust_trx_types
2277 where cust_trx_type_id = trx_type_id_tab(i)
2278 and org_id = internal_org_id_tab(i);
2279
2280 EXCEPTION
2281 when no_data_found then
2282 arp_vnd_ctrl_exmpt_tab(i):=NULL;
2283
2284 END;
2285 End if;
2286
2287 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2288 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2289 END IF;
2290
2291 END DERIVE_VND_CTRL_EXMPT;
2292
2293 PROCEDURE DERIVE_USE_NEXPRO
2294 IS
2295
2296 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_USE_NEXPRO';
2297
2298 BEGIN
2299 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2300 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2301 END IF;
2302
2303 arp_use_nexpro_tab(i) := ARP_TAX_VIEW_TAXWARE.USE_NEXPRO
2304 (l_view_name,
2305 trx_id_tab(i),
2306 trx_line_id_tab(i));
2307 if(arp_use_nexpro_tab(i) is NULL) then
2308 arp_use_nexpro_tab(i) := g_usenexpro;
2309 end if;
2310
2311 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2312 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2313 END IF;
2314
2315 END DERIVE_USE_NEXPRO;
2316
2317 PROCEDURE DERIVE_SERVICE_IND
2318 IS
2319
2320 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_SERVICE_IND';
2321
2322 BEGIN
2323 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2324 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2325 END IF;
2326
2327 arp_service_ind_tab(i) := ARP_TAX_VIEW_TAXWARE.SERVICE_INDICATOR
2328 (l_view_name,
2329 trx_id_tab(i),
2330 trx_line_id_tab(i));
2331 if(arp_service_ind_tab(i) is NULL) then
2332 arp_service_ind_tab(i) := g_serviceind;
2333 end if;
2334
2335 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2336 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2337 END IF;
2338
2339 END DERIVE_SERVICE_IND;
2340
2341 PROCEDURE DERIVE_TAX_SEL_PARAM
2342 IS
2343
2344 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_TAX_SEL_PARAM';
2345
2346 BEGIN
2347 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2348 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2349 END IF;
2350
2351 arp_tax_sel_param_tab(i) := ARP_TAX_VIEW_TAXWARE.TAX_SEL_PARM
2352 (l_view_name,
2353 trx_id_tab(i),
2354 trx_line_id_tab(i));
2355 if(arp_tax_sel_param_tab(i) is NULL) then
2356 arp_tax_sel_param_tab(i) := g_taxselparam;
2357 end if;
2358
2359 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2360 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2361 END IF;
2362
2363 END DERIVE_TAX_SEL_PARAM;
2364
2365 PROCEDURE DERIVE_CALCULATION_FLAG
2366 IS
2367
2368 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_CALCULATION_FLAG';
2369
2370 BEGIN
2371 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2372 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2373 END IF;
2374
2375 calculation_flag_tab(i) := ARP_TAX_VIEW_TAXWARE.Calculation_Flag
2376 (l_view_name,
2377 trx_id_tab(i),
2378 trx_line_id_tab(i));
2379 if(calculation_flag_tab(i) is NULL) then
2380 calculation_flag_tab(i) := '00000';
2381 end if;
2382
2383 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2384 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2385 END IF;
2386
2387 END DERIVE_CALCULATION_FLAG;
2388
2389
2390 PROCEDURE ERROR_EXCEPTION_HANDLE(str varchar2) is
2391
2392 cursor error_exception_cursor is
2393 select EVNT_CLS_MAPPING_ID,
2394 TRX_ID,
2395 TAX_REGIME_CODE
2396 from ZX_TRX_PRE_PROC_OPTIONS_GT;
2397
2398 l_docment_type_id number;
2399 l_trasaction_id number;
2400 l_tax_regime_code varchar2(80);
2401
2402 Begin
2403 open error_exception_cursor;
2404 fetch error_exception_cursor into l_docment_type_id,l_trasaction_id,l_tax_regime_code;
2405
2406 G_MESSAGES_TBL.DOCUMENT_TYPE_ID(err_count) := l_docment_type_id;
2407 G_MESSAGES_TBL.TRANSACTION_ID(err_count) := l_trasaction_id;
2408 G_MESSAGES_TBL.COUNTRY_CODE(err_count) := l_tax_regime_code;
2409 G_MESSAGES_TBL.ERROR_MESSAGE_TYPE(err_count) := 'ERROR';
2410 G_MESSAGES_TBL.ERROR_MESSAGE_STRING(err_count) := str;
2411
2412 err_count :=err_count+1;
2413
2414 close error_exception_cursor;
2415
2416 End ERROR_EXCEPTION_HANDLE;
2417
2418 PROCEDURE INITIALIZE IS
2419 BEGIN
2420 IF (g_level_procedure >= g_current_runtime_level ) THEN
2421 FND_LOG.STRING(g_level_procedure, 'ZX.PARTNER.ARP_TAX_VIEW_TAXWARE.INITIALIZE',
2422 'ZX.PARTNER.ARP_TAX_VIEW_TAXWARE.INITIALIZE(+)');
2423 END IF;
2424
2425 g_usenexpro := fnd_profile.value('ZX_TAXVDR_USENEXPRO');
2426 g_taxselparam := TO_NUMBER(fnd_profile.value('ZX_TAXVDR_TAXSELPARAM'));
2427 -- g_taxtype := TO_NUMBER(fnd_profile.value('ZX_TAXVDR_TAXTYPE'));
2428 g_serviceind := TO_NUMBER(fnd_profile.value('ZX_TAXVDR_SERVICEIND'));
2429 IF (g_level_procedure >= g_current_runtime_level ) THEN
2430 FND_LOG.STRING(g_level_procedure, 'ZX.PARTNER.ARP_TAX_VIEW_TAXWARE.INITIALIZE',
2431 'ZX.PARTNER.ARP_TAX_VIEW_TAXWARE.INITIALIZE(-)');
2432 END IF;
2433
2434 END INITIALIZE;
2435
2436 Begin
2437 initialize;
2438
2439 END ZX_TAXWARE_USER_PKG;