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