[Home] [Help]
PACKAGE BODY: APPS.ZX_VTX_USER_PKG
Source
1 PACKAGE BODY ZX_VTX_USER_PKG AS
2 /* $Header: zxvtxuserpkgb.pls 120.58.12020000.2 2012/09/21 12:42:53 ssanka 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 => NVL(ship_to_site_use_tab(i),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 => NVL(ship_to_site_tax_prof_tab(i),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 VARCHAR2(4000);
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 LOOP
944
945 IF event_class_code_tab(p_position) = 'CREDIT_MEMO'
946 AND adjusted_doc_trx_id_tab(p_position) IS NOT NULL THEN
947 l_tax_account_source_tax := NULL;
948 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
949 FND_LOG.STRING(G_LEVEL_STATEMENT,'Tax Account Source Tax Inside Credit: ',l_tax_account_source_tax);
950 END IF;
951 ELSE
952 BEGIN
953 SELECT NVL(TAX_EXMPT_SOURCE_TAX, TAX_ACCOUNT_SOURCE_TAX) --Bug 8724051
954 INTO l_tax_account_source_tax
955 FROM ZX_SCO_TAXES_B_V
956 WHERE tax_regime_code = p_tax_regime_code
957 AND tax = tax_identifier_tab(i)
958 AND ( g_trx_date >= effective_from AND (g_trx_date <= effective_to OR effective_to IS NULL));
959 EXCEPTION
960 WHEN OTHERS THEN
961 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
962 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||L_API_NAME,SQLERRM);
963 END IF;
964 NULL;
965 END;
966 END IF;
967
968 IF l_tax_account_source_tax IS NOT NULL THEN
969 IF l_tax_account_source_tax = 'STATE' THEN
970 l_exempt_percent := arp_state_exempt_percent_tab(p_position);
971 l_exempt_reason := arp_state_exempt_reason_tab(p_position);
972 ELSIF l_tax_account_source_tax = 'COUNTY' THEN
973 l_exempt_percent := arp_county_exempt_pct_tab(p_position);
974 l_exempt_reason := arp_county_exempt_reason_tab(p_position);
975 ELSIF l_tax_account_source_tax = 'CITY' THEN
976 l_exempt_percent := arp_city_exempt_pct_tab(p_position);
977 l_exempt_reason := arp_city_exempt_reason_tab(p_position);
978 ELSIF l_tax_account_source_tax = 'DISTRICT' THEN
979 l_exempt_percent := arp_district_exempt_pct_tab(p_position);
980 l_exempt_reason := arp_district_exempt_rs_tab(p_position);
981 END IF;
982 l_certificate_number := cert_num_tab(p_position);
983
984 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
985 FND_LOG.STRING(G_LEVEL_STATEMENT,'Percent: ',to_char(l_exempt_percent));
986 FND_LOG.STRING(G_LEVEL_STATEMENT,'Reason: ',to_char(l_exempt_reason));
987 FND_LOG.STRING(G_LEVEL_STATEMENT,'Certificate: ',to_char(l_certificate_number));
988 END IF;
989
990 IF tax_identifier_tab(i) = 'STATE' THEN
991 arp_state_exempt_percent_tab(p_position) := l_exempt_percent;
992 arp_state_exempt_reason_tab(p_position) := l_exempt_reason;
993 ELSIF tax_identifier_tab(i) = 'COUNTY' THEN
994 arp_county_exempt_pct_tab(p_position) := l_exempt_percent;
995 arp_county_exempt_reason_tab(p_position) := l_exempt_reason;
996 ELSIF tax_identifier_tab(i) = 'CITY' THEN
997 arp_city_exempt_pct_tab(p_position) := l_exempt_percent;
998 arp_city_exempt_reason_tab(p_position) := l_exempt_reason;
999 ELSIF tax_identifier_tab(i) = 'DISTRICT' THEN
1000 arp_district_exempt_pct_tab(p_position) := l_exempt_percent;
1001 arp_district_exempt_rs_tab(p_position) := l_exempt_reason;
1002 END IF;
1003 cert_num_tab(p_position) := l_certificate_number;
1004
1005 -- adding code to populate exemption details in partner calculated tax lines
1006 l_ptnr_exemption_indx := to_char(trx_id_tab(p_position)) || '$' ||
1007 to_char(trx_line_id_tab(p_position)) || '$' ||
1008 l_tax_account_source_tax || '$' ||
1009 p_tax_regime_code || '$' ||
1010 to_char(tax_provider_id_tab(p_position));
1011 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1012 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' l_ptnr_exemption_indx = ' || l_ptnr_exemption_indx );
1013 END IF;
1014
1015 IF ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl.EXISTS(l_ptnr_exemption_indx)
1016 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_id = trx_id_tab(p_position)
1017 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_line_id = trx_line_id_tab(p_position)
1018 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax = l_tax_account_source_tax
1019 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_regime_code = p_tax_regime_code
1020 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_provider_id = tax_provider_id_tab(p_position)
1021 THEN
1022 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1023 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' exemption info found in cache.' );
1024 END IF;
1025 IF tax_identifier_tab(i) = 'COUNTY' THEN
1026 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason_code := arp_county_exempt_reason_tab(p_position);
1027 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason := arp_county_exempt_reason_tab(p_position);
1028 ELSIF tax_identifier_tab(i) = 'CITY' THEN
1029 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason_code := arp_city_exempt_pct_tab(p_position);
1030 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason := arp_city_exempt_pct_tab(p_position);
1031 ELSIF tax_identifier_tab(i) = 'DISTRICT' THEN
1032 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).di_exempt_reason_code := arp_district_exempt_rs_tab(p_position);
1033 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).di_exempt_reason := arp_district_exempt_rs_tab(p_position);
1034 END IF;
1035 END IF;
1036 ELSE /* There is No Source Tax */
1037 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1038 FND_LOG.STRING(G_LEVEL_STATEMENT,'Inside Else','No Source Tax');
1039 END IF;
1040
1041 IF event_class_code_tab(p_position) = 'CREDIT_MEMO'
1042 AND adjusted_doc_trx_id_tab(p_position) IS NOT NULL THEN
1043
1044 /* Special processing is required here and we must NOT fetch the
1045 exemption id from the tcm api. The geo level field here is
1046 used to know what level of geography we are dealing with
1047 to approprirately insert into the relevant nested tables */
1048
1049 BEGIN
1050 SELECT TAX_EXEMPTION_ID,
1051 NVL(EXEMPT_RATE_MODIFIER ,0) * 100,
1052 EXEMPT_REASON_CODE,
1053 EXEMPT_CERTIFICATE_NUMBER
1054 INTO x_exempt_record.exemption_id,
1055 x_exempt_record.percent_exempt,
1056 x_exempt_record.exempt_reason_code,
1057 x_exempt_record.exempt_certificate_number
1058 FROM ZX_LINES
1059 WHERE application_id = adj_doc_appl_id_tab(p_position)
1060 AND entity_code = adj_doc_entity_code_tab(p_position)
1061 AND event_class_code = adj_evnt_cls_code_tab(p_position)
1062 AND trx_id = adjusted_doc_trx_id_tab(p_position)
1063 AND trx_line_id = adj_doc_line_id_tab(p_position)
1064 AND trx_level_type = adj_doc_trx_level_type_tab(p_position)
1065 AND tax_regime_code = p_tax_regime_code
1066 AND tax = tax_identifier_tab(i);
1067 EXCEPTION
1068 WHEN NO_DATA_FOUND THEN
1069 BEGIN
1070 SELECT TAX_EXEMPTION_ID,
1071 NVL(EXEMPT_RATE_MODIFIER ,0) * 100,
1072 EXEMPT_REASON_CODE,
1073 EXEMPT_CERTIFICATE_NUMBER
1074 INTO x_exempt_record.exemption_id,
1075 x_exempt_record.percent_exempt,
1076 x_exempt_record.exempt_reason_code,
1077 x_exempt_record.exempt_certificate_number
1078 FROM ZX_LINES
1079 WHERE application_id = adj_doc_appl_id_tab(p_position)
1080 AND entity_code = adj_doc_entity_code_tab(p_position)
1081 AND event_class_code = adj_evnt_cls_code_tab(p_position)
1082 AND trx_id = adjusted_doc_trx_id_tab(p_position)
1083 AND trx_line_id = adj_doc_line_id_tab(p_position)
1084 AND trx_level_type = adj_doc_trx_level_type_tab(p_position)
1085 AND tax_regime_code = p_tax_regime_code
1086 AND tax = 'LOCATION';
1087 EXCEPTION
1088 WHEN NO_DATA_FOUND THEN
1089 x_exempt_record.exemption_id:= NULL;
1090 x_exempt_record.percent_exempt:= NULL;
1091 x_exempt_record.exempt_reason_code:= NULL;
1092 x_exempt_record.exempt_certificate_number:= NULL;
1093
1094 IF tax_identifier_tab(i) = 'DISTRICT' THEN
1095 BEGIN
1096 SELECT NVL(TAX_EXMPT_SOURCE_TAX, TAX_ACCOUNT_SOURCE_TAX) --Bug 8724051
1097 INTO l_tax_account_source_tax
1098 FROM ZX_SCO_TAXES_B_V
1099 WHERE tax_regime_code = p_tax_regime_code
1100 AND tax = tax_identifier_tab(i)
1101 AND ( g_trx_date >= effective_from AND (g_trx_date <= effective_to OR effective_to IS NULL));
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 l_tax_account_source_tax := NULL;
1105 END;
1106
1107 IF l_tax_account_source_tax IS NOT NULL THEN
1108 BEGIN
1109 SELECT TAX_EXEMPTION_ID,
1110 NVL(EXEMPT_RATE_MODIFIER ,0) * 100,
1111 EXEMPT_REASON_CODE,
1112 EXEMPT_CERTIFICATE_NUMBER
1113 INTO x_exempt_record.exemption_id,
1114 x_exempt_record.percent_exempt,
1115 x_exempt_record.exempt_reason_code,
1116 x_exempt_record.exempt_certificate_number
1117 FROM ZX_LINES
1118 WHERE application_id = adj_doc_appl_id_tab(p_position)
1119 AND entity_code = adj_doc_entity_code_tab(p_position)
1120 AND event_class_code = adj_evnt_cls_code_tab(p_position)
1121 AND trx_id = adjusted_doc_trx_id_tab(p_position)
1122 AND trx_line_id = adj_doc_line_id_tab(p_position)
1123 AND trx_level_type = adj_doc_trx_level_type_tab(p_position)
1124 AND tax_regime_code = p_tax_regime_code
1125 AND tax = l_tax_account_source_tax;
1126 EXCEPTION
1127 WHEN OTHERS THEN
1128 x_exempt_record.exemption_id:= NULL;
1129 x_exempt_record.percent_exempt:= NULL;
1130 x_exempt_record.exempt_reason_code:= NULL;
1131 x_exempt_record.exempt_certificate_number:= NULL;
1132 END;
1133 END IF; -- Tax Account Source check for District Tax
1134 END IF; -- Deriving the exemption details for Tax District
1135 END; -- End of logic for migrated invoices
1136 WHEN OTHERS THEN
1137 x_exempt_record.exemption_id:= NULL;
1138 x_exempt_record.percent_exempt:= NULL;
1139 x_exempt_record.exempt_reason_code:= NULL;
1140 x_exempt_record.exempt_certificate_number:= NULL;
1141 END; -- End of Cm logic to populated the exemption details from Invoice.
1142
1143 IF x_exempt_record.exemption_id is NOT NULL THEN
1144 /*Proceed further only if the exemption id fetched is not null*/
1145 BEGIN
1146 SELECT rate_modifier
1147 INTO x_exempt_record.percent_exempt
1148 FROM ZX_EXEMPTIONS EXMP
1149 WHERE tax_exemption_id = x_exempt_record.exemption_id;
1150 EXCEPTION WHEN NO_DATA_FOUND THEN
1151 IF (g_level_exception >= g_current_runtime_level ) THEN
1152 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1153 END IF;
1154 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
1155 g_string :='No Data found from ZX_EXEMPTIONS for provided id';
1156 error_exception_handle(g_string);
1157 --x_messages_tbl:=g_messages_tbl;
1158 RETURN;
1159 END;
1160 END IF;
1161
1162 ELSE /* Beginning of regular processing */
1163
1164 /* Adding the Code for exemptions to work as in 11i*/
1165
1166 IF ship_to_loc_id_tab(p_position) IS NOT NULL THEN
1167 l_location_type := 'SHIP_TO';
1168 ELSIF bill_to_location_id_tab(p_position) IS NOT NULL THEN
1169 l_location_type := 'BILL_TO';
1170 ELSE
1171 l_location_type := NULL;
1172 END IF;
1173
1174 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1175 FND_LOG.STRING(G_LEVEL_STATEMENT,'Location Type: ',l_location_type);
1176 FND_LOG.STRING(G_LEVEL_STATEMENT,'Location ID: ',to_char(p_bill_to_location_id));
1177 END IF;
1178
1179 delete from zx_jurisdictions_gt;
1180
1181 IF p_bill_to_location_id IS NOT NULL THEN
1182 ZX_TCM_GEO_JUR_PKG.get_tax_jurisdictions (
1183 p_location_id => p_bill_to_location_id,
1184 p_location_type => l_location_type,
1185 p_tax => tax_identifier_tab(i),
1186 p_tax_regime_code => p_tax_regime_code,
1187 p_trx_date => g_trx_date,
1188 x_tax_jurisdiction_rec => l_jurisdiction_rec,
1189 x_jurisdictions_found => l_jurisdictions_found,
1190 x_return_status => x_ret_status);
1191 END IF;
1192
1193 IF (x_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1194 RETURN;
1195 ELSE
1196 IF l_jurisdiction_rec.tax_jurisdiction_id IS NOT NULL THEN
1197 l_multiple_jurisdictions_flag := 'N';
1198 ELSE
1199 IF l_jurisdictions_found = 'Y' THEN
1200 l_multiple_jurisdictions_flag := 'Y';
1201 ELSE
1202 l_multiple_jurisdictions_flag := 'N';
1203 END IF;
1204 l_jurisdiction_rec.tax_jurisdiction_id := NULL;
1205 END IF;
1206 END IF;
1207
1208 /* End of changes */
1209
1210 l_event_class_rec.internal_organization_id := internal_org_id_tab(p_position);
1211 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1212 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'int org id = ' || to_char(l_event_class_rec.internal_organization_id ));
1213 END IF;
1214
1215 ZX_TCM_GET_EXEMPT_PKG.get_tax_exemptions(
1216 p_bill_to_cust_site_use_id => p_bill_to_site_use,
1217 p_bill_to_cust_acct_id => p_bill_third_pty_acct_id,
1218 -- Fixed for 6798559
1219 p_bill_to_party_site_ptp_id => p_bill_to_site_tax_prof ,
1220 -- Fixed for 6798559
1221 p_bill_to_party_ptp_id => p_bill_to_party_tax_id,
1222 p_sold_to_party_site_ptp_id => p_hq_site_tax_prof_id,
1223 p_sold_to_party_ptp_id => p_hq_party_tax_prof_id_tab,
1224 p_inventory_org_id => p_product_org_id,
1225 p_inventory_item_id => p_product_id,
1226 p_exempt_certificate_number => p_cert_num,
1227 p_reason_code => p_exmpt_rsn_code,
1228 p_exempt_control_flag => p_exemption_control_flag,
1229 p_tax_date => g_trx_date,
1230 p_tax_regime_code => p_tax_regime_code,
1231 p_tax => tax_identifier_tab(i),
1232 p_tax_status_code => 'STANDARD',
1233 p_tax_rate_code => 'STANDARD',
1234 p_tax_jurisdiction_id => l_jurisdiction_rec.tax_jurisdiction_id,
1235 p_multiple_jurisdictions_flag => l_multiple_jurisdictions_flag,
1236 p_event_class_rec => l_event_class_rec,
1237 x_return_status => x_ret_status,
1238 x_exemption_rec => x_exempt_record
1239 );
1240
1241 END IF;
1242
1243 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1244 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' tax_identifier_tab(i) = ' || tax_identifier_tab(i));
1245 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' x_exempt_record.exemption_id = ' || x_exempt_record.exemption_id);
1246 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' x_exempt_record.exempt_certificate_number = ' || x_exempt_record.exempt_certificate_number);
1247 END IF;
1248
1249 IF x_exempt_record.exemption_id is NOT NULL OR
1250 (x_exempt_record.percent_exempt IS NOT NULL AND event_class_code_tab(p_position) = 'CREDIT_MEMO') THEN
1251
1252 /* This condition check is necessary because we have to cater to the
1253 condition that if the special processing code logic returned a null
1254 exemption id then we have to skip the iteration all together.Also
1255 this possibility(exemption id being null)
1256 is only there for the special processing code logic .So we can safely
1257 use this condition without in any way harming the regular processing.
1258
1259 The overall logic here is like this:
1260 Initially populate the exemption id into a nested table at the same index .Meaning if a exemption
1261 id of 2000 gets derived for a state level tax then for the exemptions nested table store this
1262 value of 2000 at the 2000th location in the table.Also use the derived exemption id to
1263 call the ARP_TAX_VIEW_VERTEX.GET_EXEMPTIONS in order to populate the exemption records table
1264 at the same location ie 2000.
1265
1266 ****NOTE**** Here p_position is the position in the linear table (in the main loop)
1267 into which the derived values are ultimately getting inserted .
1268 */
1269 IF NOT exemptions_info_tab.EXISTS(NVL(x_exempt_record.exemption_id, -99)) THEN
1270 IF x_exempt_record.exemption_id IS NOT NULL THEN
1271 ARP_TAX_VIEW_VERTEX.GET_EXEMPTIONS(
1272 X_EXEMPT_RECORD.EXEMPTION_ID, --This is the input parameter for this call
1273 exemptions_info_tab(x_exempt_record.exemption_id).certificate_number,
1274 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct,
1275 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason,
1276 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct,
1277 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason,
1278 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct ,
1279 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason,
1280 exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_pct,
1281 exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_reason);
1282
1283 IF exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct IS NULL THEN
1284 /* If the user extensible procedure returned a null then use the default percentage
1285 rate extracted into the exemptions record*/
1286 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct := x_exempt_record.percent_exempt;
1287 END IF;
1288
1289 IF exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason IS NULL THEN
1290 /* If the user extensible procedure returned a null then use the default exempt
1291 reason extracted into the exemptions record*/
1292 exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1293 END IF;
1294
1295 IF exemptions_info_tab(x_exempt_record.exemption_id).certificate_number IS NULL THEN
1296 /* If the user extensible procedure returned a null then use the default exempt
1297 certificate number extracted into the exemptions record*/
1298 exemptions_info_tab(x_exempt_record.exemption_id).certificate_number := SUBSTRB(x_exempt_record.exempt_certificate_number,1,15);
1299 END IF;
1300
1301 IF exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct IS NULL THEN
1302 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct:= x_exempt_record.percent_exempt;
1303 END IF;
1304
1305 IF exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason IS NULL THEN
1306 exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1307 END IF;
1308
1309 IF exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct IS NULL THEN
1310 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct := x_exempt_record.percent_exempt;
1311 END IF;
1312
1313 IF exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason IS NULL THEN
1314 exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1315 END IF;
1316
1317 IF exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_pct IS NULL THEN
1318 exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_pct := x_exempt_record.percent_exempt;
1319 END IF;
1320
1321 IF exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_reason IS NULL THEN
1322 exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_reason := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1323 END IF;
1324 END IF;
1325 END IF;
1326 IF x_exempt_record.exemption_id IS NOT NULL THEN
1327 IF tax_identifier_tab(i) = 'STATE' THEN
1328 arp_state_exempt_percent_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_pct;
1329 arp_state_exempt_reason_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason;
1330 ELSIF tax_identifier_tab(i) = 'COUNTY' THEN
1331 arp_county_exempt_pct_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_pct;
1332 arp_county_exempt_reason_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason;
1333 ELSIF tax_identifier_tab(i) = 'CITY' THEN
1334 arp_city_exempt_pct_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_pct;
1335 arp_city_exempt_reason_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason;
1336 ELSIF tax_identifier_tab(i) = 'DISTRICT' THEN
1337 arp_district_exempt_pct_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_pct;
1338 arp_district_exempt_rs_tab(p_position) := exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_reason;
1339 END IF;
1340 cert_num_tab(p_position) := nvl(p_cert_num,exemptions_info_tab(x_exempt_record.exemption_id).certificate_number);
1341 ELSIF (x_exempt_record.percent_exempt IS NOT NULL AND event_class_code_tab(p_position) = 'CREDIT_MEMO') THEN
1342 IF tax_identifier_tab(i) = 'STATE' THEN
1343 arp_state_exempt_percent_tab(p_position) := x_exempt_record.percent_exempt;
1344 arp_state_exempt_reason_tab(p_position) := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1345 ELSIF tax_identifier_tab(i) = 'COUNTY' THEN
1346 arp_county_exempt_pct_tab(p_position) := x_exempt_record.percent_exempt;
1347 arp_county_exempt_reason_tab(p_position) := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1348 ELSIF tax_identifier_tab(i) = 'CITY' THEN
1349 arp_city_exempt_pct_tab(p_position) := x_exempt_record.percent_exempt;
1350 arp_city_exempt_reason_tab(p_position) := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1351 ELSIF tax_identifier_tab(i) = 'DISTRICT' THEN
1352 arp_district_exempt_pct_tab(p_position) := x_exempt_record.percent_exempt;
1353 arp_district_exempt_rs_tab(p_position) := SUBSTRB(x_exempt_record.exempt_reason_code,1,1);
1354 END IF;
1355 cert_num_tab(p_position) := nvl(p_cert_num, x_exempt_record.exempt_certificate_number);
1356 END IF;
1357
1358 -- adding code to populate exemption details in partner calculated tax lines
1359 l_ptnr_exemption_indx := to_char(trx_id_tab(p_position)) || '$' ||
1360 to_char(trx_line_id_tab(p_position)) || '$' ||
1361 tax_identifier_tab(i) || '$' ||
1362 p_tax_regime_code || '$' ||
1363 to_char(tax_provider_id_tab(p_position));
1364 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1365 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' l_ptnr_exemption_indx = ' || l_ptnr_exemption_indx );
1366 END IF;
1367 IF ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl.EXISTS(l_ptnr_exemption_indx)
1368 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_id
1369 = trx_id_tab(p_position)
1370 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_line_id
1371 = trx_line_id_tab(p_position)
1372 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax
1373 = tax_identifier_tab(i)
1374 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_regime_code
1375 = p_tax_regime_code
1376 AND ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_provider_id
1377 = tax_provider_id_tab(p_position)
1378 THEN
1379 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1380 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' exemption info found in cache.' );
1381 END IF;
1382 --NULL;
1383 ELSE
1384 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_id := trx_id_tab(p_position);
1385 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).trx_line_id := trx_line_id_tab(p_position);
1386 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax := tax_identifier_tab(i);
1387 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_regime_code := p_tax_regime_code;
1388 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_provider_id := tax_provider_id_tab(p_position);
1389 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).tax_exemption_id := x_exempt_record.exemption_id;
1390 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).exempt_certificate_number := cert_num_tab(p_position);
1391
1392 IF x_exempt_record.exemption_id IS NOT NULL THEN
1393 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason_code :=
1394 NVL(exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason,x_exempt_record.exempt_reason_code);
1395 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason_code :=
1396 NVL(exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason,x_exempt_record.exempt_reason_code);
1397 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason_code :=
1398 NVL(exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason,x_exempt_record.exempt_reason_code);
1399 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).di_exempt_reason_code :=
1400 NVL(exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_reason,x_exempt_record.exempt_reason_code);
1401 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason :=
1402 NVL(exemptions_info_tab(x_exempt_record.exemption_id).state_exempt_reason,x_exempt_record.exempt_reason_code);
1403 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason :=
1404 NVL(exemptions_info_tab(x_exempt_record.exemption_id).county_exempt_reason,x_exempt_record.exempt_reason_code);
1405 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason :=
1406 NVL(exemptions_info_tab(x_exempt_record.exemption_id).city_exempt_reason,x_exempt_record.exempt_reason_code);
1407 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).di_exempt_reason :=
1408 NVL(exemptions_info_tab(x_exempt_record.exemption_id).district_exempt_reason,x_exempt_record.exempt_reason_code);
1409 ELSIF (x_exempt_record.percent_exempt IS NOT NULL AND event_class_code_tab(p_position) = 'CREDIT_MEMO') THEN
1410 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason_code := x_exempt_record.exempt_reason_code;
1411 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason_code := x_exempt_record.exempt_reason_code;
1412 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason_code := x_exempt_record.exempt_reason_code;
1413 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).di_exempt_reason_code := x_exempt_record.exempt_reason_code;
1414 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).st_exempt_reason := x_exempt_record.exempt_reason_code;
1415 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).co_exempt_reason := x_exempt_record.exempt_reason_code;
1416 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).ci_exempt_reason := x_exempt_record.exempt_reason_code;
1417 ZX_GLOBAL_STRUCTURES_PKG.ptnr_exemption_tbl(l_ptnr_exemption_indx).di_exempt_reason := x_exempt_record.exempt_reason_code;
1418 END IF;
1419 END IF;
1420 END IF; /* Tax Account Source Tax */
1421 END IF; /*End of special processing if*/
1422 END LOOP;
1423
1424 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1425 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));
1426 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));
1427 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));
1428 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));
1429 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' p_cert_num = ' || p_cert_num);
1430 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, ' cert_num_tab(p_position) = ' || cert_num_tab(p_position));
1431 END IF;
1432
1433 p_error_status := x_ret_status;
1434
1435 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1436 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1437 ' RETURN_STATUS = ' || p_error_status);
1438 END IF;
1439
1440 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1441 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1442 END IF;
1443
1444 EXCEPTION
1445 WHEN OTHERS THEN
1446 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
1447 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1448 END IF;
1449 p_error_status := FND_API.G_RET_STS_UNEXP_ERROR;
1450
1451 END POPULATE_EXEMPTION_DETAILS;
1452
1453 /*===========================================================================+
1454 | PROCEDURE
1455 | Initialize_Nested_Tables
1456 | IN
1457 |
1458 | OUT NOCOPY
1459 |
1460 | DESCRIPTION
1461 | This is a start up procedure that deletes any existing data from the nested
1462 | tables
1463 |
1464 |
1465 | SCOPE - PRIVATE
1466 |
1467 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1468 |
1469 | CALLED FROM
1470 |
1471 |
1472 | MODIFICATION HISTORY
1473 | 08/13/2004 Arnab Sengupta Created.
1474 |
1475 +==========================================================================*/
1476
1477 PROCEDURE Initialize_Nested_Tables
1478 IS
1479
1480 l_api_name CONSTANT VARCHAR2(80) := 'INITIALIZE_NESTED_TABLES';
1481
1482 BEGIN
1483 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1484 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1485 END IF;
1486
1487 internal_org_id_tab.DELETE;
1488 application_id_tab.DELETE;
1489 entity_code_tab.DELETE;
1490 event_class_code_tab.DELETE;
1491 trx_id_tab.DELETE;
1492 tax_provider_id_tab.DELETE;
1493 tax_regime_code_tab.DELETE;
1494 trx_line_type_tab.DELETE;
1495 trx_line_id_tab.DELETE;
1496 product_id_tab.DELETE;
1497 Product_Org_Id_tab.DELETE;
1498 ship_to_tx_id_tab.DELETE;
1499 ship_from_tx_id_tab.DELETE;
1500 cert_num_tab.DELETE;
1501 exmpt_rsn_code_tab.DELETE;
1502 exemption_control_flag_tab.DELETE;
1503 ship_to_site_tax_prof_tab.DELETE;
1504 ship_to_loc_id_tab.DELETE;
1505 exmpt_control_flg_tab.DELETE;
1506 arp_trx_line_type_tab.DELETE;
1507 arp_product_code_tab.DELETE;
1508 arp_audit_flag_tab.DELETE;
1509 arp_ship_to_add_tab.DELETE;
1510 arp_ship_from_add_tab.DELETE;
1511 arp_poa_add_code_tab.DELETE;
1512 arp_customer_code_tab.DELETE;
1513 arp_customer_class_tab.DELETE;
1514 arp_company_code_tab.DELETE;
1515 arp_division_code_tab.DELETE;
1516 arp_transaction_date_tab.DELETE;
1517 ship_to_address_id_tab.DELETE;
1518 ship_to_party_id_tab.DELETE;
1519 arp_state_exempt_reason_tab.DELETE;
1520 arp_county_exempt_reason_tab.DELETE;
1521 arp_city_exempt_reason_tab.DELETE;
1522 arp_district_exempt_rs_tab.DELETE;
1523 arp_state_exempt_percent_tab.DELETE;
1524 arp_county_exempt_pct_tab.DELETE;
1525 arp_city_exempt_pct_tab.DELETE;
1526 arp_district_exempt_pct_tab.DELETE;
1527 ship_to_site_use_tab.DELETE;
1528 bill_to_site_use_tab.DELETE;
1529 bill_to_site_tax_prof_tab.DELETE;
1530 bill_to_party_tax_id_tab.DELETE;
1531 bill_to_location_id_tab.DELETE;
1532 trad_hq_site_tax_prof_id_tab.DELETE;
1533 trad_hq_party_tax_prof_id_tab.DELETE;
1534 bill_third_pty_acct_id_tab.DELETE;
1535 line_level_action_tab.DELETE;
1536 adjusted_doc_trx_id_tab.DELETE;
1537 line_amount_tab.DELETE;
1538 exemptions_info_tab.DELETE;
1539 adj_doc_appl_id_tab.DELETE;
1540 adj_doc_entity_code_tab.DELETE;
1541 adj_evnt_cls_code_tab.DELETE;
1542 adj_doc_line_id_tab.DELETE;
1543 adj_doc_trx_level_type_tab.DELETE;
1544 ship_third_pty_site_tab.DELETE;
1545 bill_third_pty_site_tab.DELETE;
1546
1547 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1548 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1549 END IF;
1550
1551 EXCEPTION WHEN COLLECTION_IS_NULL THEN
1552 NULL;
1553 END Initialize_Nested_Tables;
1554
1555
1556 /*===========================================================================+
1557 | PROCEDURE
1558 | Initialize_Exemption_Tables
1559 | IN
1560 |
1561 | OUT NOCOPY
1562 |
1563 | DESCRIPTION
1564 | This procedure is used to initialize the exemption tables
1565 | in order to avoid the no data found exception during runtime
1566 |
1567 |
1568 | SCOPE - PRIVATE
1569 |
1570 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1571 |
1572 | CALLED FROM
1573 |
1574 |
1575 | MODIFICATION HISTORY
1576 | 08/13/2004 Arnab Sengupta Created.
1577 |
1578 +==========================================================================*/
1579 PROCEDURE Initialize_Exemption_Tables IS
1580
1581 l_api_name CONSTANT VARCHAR2(80) := 'INITIALIZE_EXEMPTION_TABLES';
1582
1583 BEGIN
1584 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1585 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1586 END IF;
1587
1588 /*Initializing all nested tables pertaining to exemptions
1589 so that we do not hit a no data found issue at runtime
1590 incase some of these tables do not get populated at all
1591 positions*/
1592
1593 FOR i in 1..NVL(trx_id_tab.last,0)
1594 LOOP
1595
1596 arp_state_exempt_percent_tab(i) := NULL;
1597 arp_county_exempt_pct_tab(i) := NULL;
1598 arp_city_exempt_pct_tab(i) := NULL;
1599 arp_district_exempt_pct_tab(i) := NULL;
1600 arp_state_exempt_reason_tab(i) := NULL;
1601 arp_county_exempt_reason_tab(i) := NULL;
1602 arp_city_exempt_reason_tab(i) := NULL;
1603 arp_district_exempt_rs_tab(i) := NULL;
1604
1605 END LOOP;
1606
1607 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1608 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1609 END IF;
1610
1611 END Initialize_Exemption_Tables;
1612
1613 /*===========================================================================+
1614 | FUNCTION |
1615 | Check_Geocode |
1616 | |
1617 | DESCRIPTION |
1618 | Returns TRUE if the GEOCODE seems to be valid |
1619 | (in the format SSZZZZZGG) |
1620 | |
1621 | SCOPE - PRIVATE |
1622 | |
1623 | MODIFICATION HISTORY |
1624 | 01-NOV-2005 Santosh Vaze Created for bug 4668932 |
1625 | |
1626 +===========================================================================*/
1627
1628
1629 FUNCTION Check_Geocode(p_geocode IN VARCHAR2)
1630 RETURN BOOLEAN
1631 IS
1632 l_api_name CONSTANT VARCHAR2(80) := 'CHECK_GEOCODE';
1633
1634 BEGIN
1635 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1636 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1637 END IF;
1638
1639 if p_geocode between '000000000' and '999999999' then
1640 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1641 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1642 END IF;
1643 return TRUE;
1644 end if;
1645
1646 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1647 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1648 END IF;
1649
1650 return FALSE;
1651
1652 END Check_Geocode;
1653
1654 PROCEDURE DERIVE_TRX_LINE_TYPE
1655 IS
1656
1657 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_TRX_LINE_TYPE';
1658
1659 BEGIN
1660 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1661 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1662 END IF;
1663
1664 arp_trx_line_type_tab(i):= ARP_TAX_VIEW_VERTEX.TRX_LINE_TYPE
1665 (l_view_name,
1666 trx_id_tab(i),
1667 trx_line_id_tab(i)) ;
1668 IF (arp_trx_line_type_tab(i) is NULL) THEN
1669 arp_trx_line_type_tab(i) := 'SALE';
1670 END IF;
1671
1672 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1673 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1674 END IF;
1675
1676 END DERIVE_TRX_LINE_TYPE;
1677
1678 PROCEDURE DERIVE_AUDIT_FLAG
1679 IS
1680
1681 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_AUDIT_FLAG';
1682
1683 BEGIN
1684 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1685 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1686 END IF;
1687
1688 IF l_view_name = 'TAX_ADJUSTMENTS_V_V' THEN
1689 BEGIN
1690 arp_audit_flag_tab(i) := ARP_TAX_VIEW_VERTEX.AUDIT_FLAG
1691 (l_view_name,
1692 trx_id_tab(i),
1693 trx_line_id_tab(i));
1694 EXCEPTION WHEN OTHERS THEN
1695 arp_audit_flag_tab(i):= NULL;
1696 END;
1697
1698 IF arp_audit_flag_tab(i) IS NULL THEN
1699 BEGIN
1700 SELECT nvl(substrb(act.attribute15, 1, 1), 'Y')
1701 INTO arp_audit_flag_tab(i)
1702 FROM ar_receivables_trx act
1703 WHERE act.receivables_trx_id IN
1704 (SELECT adj.receivables_trx_id
1705 FROM ar_adjustments adj
1706 WHERE adj.adjustment_id = trx_id_tab(i))
1707 AND act.org_id = internal_org_id_tab(i);
1708 EXCEPTION WHEN OTHERS THEN
1709 arp_audit_flag_tab(i) := 'N';
1710 END;
1711 END IF;
1712 ELSE
1713 arp_audit_flag_tab(i) := 'N';
1714 END IF;
1715
1716 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1717 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1718 END IF;
1719
1720 END DERIVE_AUDIT_FLAG;
1721
1722 PROCEDURE DERIVE_PRODUCT_CODE
1723 IS
1724
1725 l_product_id ZX_LINES_DET_FACTORS.product_id%TYPE;
1726 l_memo_line_id NUMBER;
1727 l_org_id RA_CUSTOMER_TRX_LINES_ALL.ORG_ID%TYPE;
1728 l_master_org_id oe_system_parameters_all.master_organization_id%type;
1729
1730 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_PRODUCT_CODE';
1731
1732 BEGIN
1733 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1734 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1735 END IF;
1736
1737 /* The product code function is not attached only to this view hence the if condition reads like this*/
1738
1739 IF l_view_name <> 'TAX_ADJUSTMENTS_V_V' THEN
1740
1741 /* Derive the product_id depending on the null or not null value of product_org_id*/
1742 IF product_org_id_tab(i) IS NOT NULL THEN
1743 l_product_id := product_id_tab(i);
1744 l_memo_line_id := NULL;
1745 ELSE
1746 l_product_id := NULL;
1747 l_memo_line_id := product_id_tab(i);
1748 END IF;
1749
1750 BEGIN
1751 arp_product_code_tab(i) := ARP_TAX_VIEW_VERTEX.PRODUCT_CODE(l_view_name
1752 , trx_id_tab(i)
1753 , trx_line_id_tab(i)
1754 , l_product_id
1755 , l_memo_line_id);
1756 EXCEPTION WHEN OTHERS THEN
1757 arp_product_code_tab(i) := NULL;
1758 END;
1759
1760 IF arp_product_code_tab(i) IS NULL THEN
1761 BEGIN
1762 SELECT org_id
1763 INTO l_org_id
1764 FROM ra_customer_trx_lines_all
1765 WHERE customer_trx_id = trx_id_tab(i)
1766 AND customer_trx_line_id = trx_line_id_tab(i);
1767 EXCEPTION
1768 WHEN OTHERS THEN
1769 l_org_id := internal_org_id_tab(i);
1770 END;
1771
1772 /* Bug 5612024
1773 IF MO_GLOBAL.get_current_org_id <> nvl(l_org_id, -1) THEN
1774 MO_GLOBAL.Set_Policy_Context('S', l_org_id);
1775 END IF;
1776 */
1777 l_master_org_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID', l_org_id);
1778
1779 BEGIN
1780 SELECT segment1
1781 INTO arp_product_code_tab(i)
1782 FROM mtl_system_items
1783 WHERE inventory_item_id = l_product_id
1784 AND organization_id = l_master_org_id;
1785 EXCEPTION
1786 WHEN OTHERS THEN
1787 arp_product_code_tab(i) := NULL;
1788 END;
1789 END IF;
1790 ELSE
1791 arp_product_code_tab(i) := NULL;
1792 END IF;
1793
1794 IF (g_level_statement >= g_current_runtime_level ) THEN
1795 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
1796 ' arp_product_code_tab(i) = ' || arp_product_code_tab(i));
1797 END IF;
1798
1799 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1800 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1801 END IF;
1802
1803 END DERIVE_PRODUCT_CODE;
1804
1805 PROCEDURE DERIVE_SHIP_TO_ADDRESS_CODE
1806 IS
1807 l_tax_jurisdiction_rec ZX_TCM_GEO_JUR_PKG.tax_jurisdiction_rec_type;
1808 x_ret_status VARCHAR2(30);
1809 l_jurisdictions_found VARCHAR2(1);
1810 l_jur_code VARCHAR2(30);
1811 l_jur_count NUMBER;
1812
1813 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_SHIP_TO_ADDRESS_CODE';
1814
1815 BEGIN
1816 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1817 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1818 END IF;
1819
1820 ship_to_address_id_tab(i) := NULL;
1821 IF ship_to_site_use_tab(i) is NOT NULL THEN
1822 BEGIN
1823 SELECT cust_site_uses.cust_acct_site_id
1824 INTO ship_to_address_id_tab(i)
1825 FROM HZ_CUST_SITE_USES_ALL cust_site_uses
1826 WHERE cust_site_uses.site_use_id = ship_to_site_use_tab(i);
1827 EXCEPTION WHEN OTHERS THEN
1828 ship_to_address_id_tab(i) := NULL;
1829 END;
1830 END IF;
1831
1832 IF ship_to_address_id_tab(i) is NULL THEN
1833 BEGIN
1834 SELECT cust_acct_site_id
1835 INTO ship_to_address_id_tab(i)
1836 FROM HZ_CUST_SITE_USES_ALL
1837 WHERE site_use_id = bill_to_site_use_tab(i);
1838 EXCEPTION WHEN NO_DATA_FOUND THEN
1839 ship_to_address_id_tab(i) := NULL;
1840 END;
1841 END IF;
1842
1843 BEGIN --bug6523242
1844 IF ship_to_loc_id_tab(i) IS NULL THEN
1845 arp_ship_to_add_tab(i) := ARP_TAX_VIEW_VERTEX.SHIP_TO_ADDRESS_CODE
1846 (l_view_name,
1847 trx_id_tab(i),
1848 trx_line_id_tab(i),
1849 ship_to_address_id_tab(i),
1850 bill_to_location_id_tab(i),
1851 g_trx_date,
1852 NULL,--p_ship_to_state
1853 NULL--p_postal_code
1854 );
1855 ELSE
1856 arp_ship_to_add_tab(i) := ARP_TAX_VIEW_VERTEX.SHIP_TO_ADDRESS_CODE
1857 (l_view_name,
1858 trx_id_tab(i),
1859 trx_line_id_tab(i),
1860 ship_to_address_id_tab(i),
1861 ship_to_loc_id_tab(i),
1862 g_trx_date,
1863 NULL,--p_ship_to_state
1864 NULL--p_postal_code
1865 );
1866 END IF;
1867 EXCEPTION WHEN OTHERS THEN
1868 arp_ship_to_add_tab(i):= NULL;
1869 END;
1870
1871 /* Bug 4668932 */
1872 l_in_out_flag := '1'; -- Bug 5506031
1873 IF arp_ship_to_add_tab(i) IS NULL THEN
1874 BEGIN
1875 SELECT decode(nvl(loc.sales_tax_inside_city_limits,'1'),'0','0','1') || loc.sales_tax_geocode
1876 , decode(nvl(loc.sales_tax_inside_city_limits,'1'),'0','0','1')
1877 INTO arp_ship_to_add_tab(i)
1878 , l_in_out_flag -- Bug 5506031
1879 FROM hz_locations loc
1880 WHERE loc.location_id = nvl(ship_to_loc_id_tab(i), bill_to_location_id_tab(i));
1881 EXCEPTION WHEN OTHERS THEN
1882 arp_ship_to_add_tab(i) := NULL;
1883 END;
1884
1885 IF NOT check_geocode(substr(nvl(arp_ship_to_add_tab(i),'XXXXXXXXX'), 2,9)) THEN
1886 IF ship_to_loc_id_tab(i) IS NULL THEN
1887 ZX_TCM_GEO_JUR_PKG.get_tax_jurisdictions(
1888 p_location_id => bill_to_location_id_tab(i),
1889 p_location_type => 'SHIP_TO',
1890 p_tax => 'CITY',
1891 p_tax_regime_code => tax_regime_code_tab(i),
1892 p_trx_date => g_trx_date,
1893 x_tax_jurisdiction_rec => l_tax_jurisdiction_rec,
1894 x_jurisdictions_found => l_jurisdictions_found,
1895 x_return_status => x_ret_status);
1896 ELSE
1897 ZX_TCM_GEO_JUR_PKG.get_tax_jurisdictions(
1898 p_location_id => ship_to_loc_id_tab(i),
1899 p_location_type => 'SHIP_TO',
1900 p_tax => 'CITY',
1901 p_tax_regime_code => tax_regime_code_tab(i),
1902 p_trx_date => g_trx_date,
1903 x_tax_jurisdiction_rec => l_tax_jurisdiction_rec,
1904 x_jurisdictions_found => l_jurisdictions_found,
1905 x_return_status => x_ret_status);
1906 END IF;
1907 IF x_ret_status = FND_API.G_RET_STS_SUCCESS THEN
1908 IF l_jurisdictions_found = 'Y' THEN
1909 IF l_tax_jurisdiction_rec.tax_jurisdiction_code IS NOT NULL THEN
1910 l_jur_code := substr(l_tax_jurisdiction_rec.tax_jurisdiction_code, 4);
1911 ELSE
1912 BEGIN
1913 SELECT count(*)
1914 INTO l_jur_count
1915 FROM zx_jurisdictions_gt a
1916 WHERE a.tax_regime_code = tax_regime_code_tab(i)
1917 AND a.tax = 'CITY'
1918 AND substr(a.tax_jurisdiction_code, 4) BETWEEN '000000000' and '999999999'
1919 AND a.precedence_level = (SELECT min(b.precedence_level)
1920 FROM zx_jurisdictions_gt b
1921 WHERE b.tax_regime_code = a.tax_regime_code
1922 AND substr(b.tax_jurisdiction_code, 4) BETWEEN
1923 '000000000' and '999999999'
1924 AND b.tax = 'CITY');
1925 IF l_jur_count = 1 THEN
1926
1927 SELECT substr(a.tax_jurisdiction_code, 4)
1928 INTO l_jur_code
1929 FROM zx_jurisdictions_gt a
1930 WHERE a.tax_regime_code = tax_regime_code_tab(i)
1931 AND a.tax = 'CITY'
1932 AND substr(a.tax_jurisdiction_code, 4) BETWEEN '000000000' and '999999999'
1933 AND a.precedence_level = (SELECT min(b.precedence_level)
1934 FROM zx_jurisdictions_gt b
1935 WHERE b.tax_regime_code = a.tax_regime_code
1936 AND substr(b.tax_jurisdiction_code, 4)
1937 BETWEEN '000000000' and '999999999'
1938 AND b.tax = 'CITY');
1939 END IF;
1940 END;
1941 END IF;
1942 IF check_geocode(nvl(l_jur_code,'XXXXXXXXX')) THEN
1943 arp_ship_to_add_tab(i) := l_in_out_flag ||l_jur_code;
1944 ELSE
1945 IF l_jur_count > 1 THEN
1946 arp_ship_to_add_tab(i) := NVL(l_in_out_flag,1)||nvl(l_jur_code,'XXXXXXXXX');
1947 ELSE
1948 arp_ship_to_add_tab(i) := NULL;
1949 END IF;
1950 END IF;
1951 END IF;
1952 END IF;
1953 END IF;
1954 END IF;
1955
1956 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1957 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1958 END IF;
1959
1960 END DERIVE_SHIP_TO_ADDRESS_CODE;
1961
1962 PROCEDURE DERIVE_SHIP_FROM_ADDRESS_CODE
1963 IS
1964 l_sfr_geocode VARCHAR2(10);
1965 l_sfr_in_out_flag VARCHAR2(1);
1966 l_flag BOOLEAN;
1967 l_inventory_item_id NUMBER;
1968 l_master_org_id oe_system_parameters_all.master_organization_id%type;
1969 l_org_id RA_CUSTOMER_TRX_LINES_ALL.ORG_ID%TYPE;
1970 l_ship_from_party_id ZX_PARTY_TAX_PROFILE.PARTY_ID%TYPE;
1971 l_ship_from_location_id ZX_LINES_DET_FACTORS.SHIP_FROM_LOCATION_ID%TYPE;
1972
1973 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_SHIP_FROM_ADDRESS_CODE';
1974
1975 BEGIN
1976 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1977 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1978 END IF;
1979
1980 BEGIN
1981 SELECT SHIP_FROM_LOCATION_ID
1982 INTO l_ship_from_location_id
1983 FROM ZX_LINES_DET_FACTORS
1984 WHERE APPLICATION_ID = application_id_tab(i)
1985 AND ENTITY_CODE = entity_code_tab(i)
1986 AND EVENT_CLASS_CODE = event_class_code_tab(i)
1987 AND TRX_ID = trx_id_tab(i)
1988 AND TRX_LINE_ID = trx_line_id_tab(i);
1989
1990 EXCEPTION
1991 WHEN OTHERS THEN
1992 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1993 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Error : ' || SQLERRM);
1994 END IF;
1995 l_ship_from_location_id := NULL;
1996 END;
1997
1998 BEGIN
1999 IF ship_from_tx_id_tab(i) IS NOT NULL THEN
2000 IF p_party_id_tbl.EXISTS(ship_from_tx_id_tab(i)) THEN
2001 l_ship_from_party_id := p_party_id_tbl(ship_from_tx_id_tab(i));
2002 ELSE
2003 SELECT party_id
2004 INTO l_ship_from_party_id
2005 FROM ZX_PARTY_TAX_PROFILE
2006 WHERE party_tax_profile_id = ship_from_tx_id_tab(i);
2007
2008 p_party_id_tbl(ship_from_tx_id_tab(i)) := l_ship_from_party_id;
2009 END IF;
2010 END IF;
2011
2012 EXCEPTION WHEN NO_DATA_FOUND THEN
2013 l_ship_from_party_id := NULL;
2014 END;
2015
2016 arp_ship_from_add_tab(i) := ARP_TAX_VIEW_VERTEX.SHIP_FROM_ADDRESS_CODE
2017 (l_view_name,
2018 trx_id_tab(i),
2019 trx_line_id_tab(i),
2020 l_ship_from_party_id);
2021
2022 IF (arp_ship_from_add_tab(i) is NULL) THEN
2023 BEGIN
2024 l_sfr_in_out_flag := NULL;
2025 IF l_ship_from_party_id IS NOT NULL THEN
2026 BEGIN
2027 SELECT lc.loc_information13,lc.loc_information14
2028 INTO l_sfr_geocode,l_sfr_in_out_flag
2029 FROM hr_locations_all lc, hr_organization_units hr
2030 WHERE hr.organization_id = l_ship_from_party_id
2031 AND hr.location_id = lc.location_id;
2032 EXCEPTION
2033 WHEN OTHERS THEN
2034 SELECT lc.loc_information13,lc.loc_information14
2035 INTO l_sfr_geocode,l_sfr_in_out_flag
2036 FROM hr_locations_all lc
2037 WHERE lc.location_id = l_ship_from_location_id;
2038 END;
2039 END IF;
2040
2041 IF l_sfr_geocode IS NULL THEN
2042
2043 SELECT lc.loc_information13,lc.loc_information14
2044 INTO l_sfr_geocode,l_sfr_in_out_flag
2045 FROM hr_locations_all lc
2046 WHERE lc.location_id = l_ship_from_location_id;
2047
2048 END IF;
2049
2050 IF l_ship_from_party_id IS NULL AND l_sfr_geocode IS NULL THEN
2051 l_flag := TRUE;
2052 BEGIN
2053 SELECT inventory_item_id
2054 , org_id
2055 INTO l_inventory_item_id
2056 , l_org_id
2057 FROM ra_customer_trx_lines_all
2058 WHERE customer_trx_id = trx_id_tab(i)
2059 AND customer_trx_line_id = trx_line_id_tab(i);
2060 EXCEPTION
2061 WHEN others THEN
2062 l_flag := FALSE;
2063 END;
2064 IF l_flag AND l_inventory_item_id IS NULL THEN
2065 /* Bug 5612024
2066 IF MO_GLOBAL.get_current_org_id <> nvl(l_org_id, -1) THEN
2067 MO_GLOBAL.Set_Policy_Context('S', l_org_id);
2068 END IF;
2069 */
2070 l_master_org_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID', l_org_id);
2071
2072 SELECT lc.loc_information13,lc.loc_information14
2073 INTO l_sfr_geocode,l_sfr_in_out_flag
2074 FROM hr_locations_all lc, hr_organization_units hr
2075 WHERE hr.organization_id = l_master_org_id
2076 AND hr.location_id = lc.location_id;
2077 END IF;
2078 END IF;
2079
2080 IF l_sfr_geocode IS NULL THEN
2081 arp_ship_from_add_tab(i):= arp_tax_view_vertex.USE_SHIP_TO;
2082 ELSE
2083 IF NOT Check_Geocode(l_sfr_geocode) THEN
2084 arp_ship_from_add_tab(i):=ARP_TAX_VIEW_VERTEX.USE_SHIP_TO;
2085 ELSE
2086 IF l_sfr_in_out_flag IS NULL THEN
2087 arp_ship_from_add_tab(i) := 'X'||l_sfr_geocode;
2088 ELSE
2089 IF l_sfr_in_out_flag like 'N%' THEN
2090 arp_ship_from_add_tab(i) := '0'||l_sfr_geocode;
2091 ELSE
2092 arp_ship_from_add_tab(i) :=
2093 l_sfr_in_out_flag||l_sfr_geocode;
2094 END IF;
2095 END IF;
2096 END IF;
2097 END IF;
2098 EXCEPTION
2099 WHEN OTHERS THEN
2100 arp_ship_from_add_tab(i) := ARP_TAX_VIEW_VERTEX.USE_SHIP_TO;
2101 END;
2102 END IF;
2103
2104 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2105 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2106 END IF;
2107
2108 END DERIVE_SHIP_FROM_ADDRESS_CODE;
2109
2110 PROCEDURE DERIVE_POA_ADDRESS_CODE
2111 IS
2112 l_poa_geocode VARCHAR2(10);
2113 l_poa_in_out_flag VARCHAR2(1);
2114 l_sales_repid RA_CUSTOMER_TRX_ALL.primary_salesrep_id%TYPE;
2115
2116 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_POA_ADDRESS_CODE';
2117
2118 BEGIN
2119 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2120 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2121 END IF;
2122
2123 IF event_class_code_tab(i) = 'INVOICE_ADJUSTMENT' THEN
2124 BEGIN
2125 IF adjusted_doc_trx_id_tab(i) IS NOT NULL THEN
2126 IF p_salesrep_tbl.EXISTS(adjusted_doc_trx_id_tab(i)) THEN
2127 l_sales_repid := p_salesrep_tbl(adjusted_doc_trx_id_tab(i));
2128 ELSE
2129 BEGIN
2130 SELECT primary_salesrep_id
2131 INTO l_sales_repid
2132 FROM ra_customer_trx_all
2133 WHERE customer_trx_id = adjusted_doc_trx_id_tab(i);
2134 EXCEPTION
2135 WHEN NO_DATA_FOUND THEN
2136 l_sales_repid := NULL;
2137 END;
2138 p_salesrep_tbl(adjusted_doc_trx_id_tab(i)) := l_sales_repid;
2139 END IF;
2140 END IF;
2141 EXCEPTION
2142 WHEN NO_DATA_FOUND THEN
2143 l_sales_repid := NULL;
2144 END;
2145 ELSE
2146 IF trx_id_tab(i) IS NOT NULL THEN
2147 IF trx_line_id_tab(i) IS NOT NULL
2148 AND l_view_name = 'OE_TAX_LINES_SUMMARY_V_V' THEN
2149 BEGIN
2150 SELECT salesrep_id
2151 INTO l_sales_repid
2152 FROM oe_order_lines_all
2153 WHERE header_id = trx_id_tab(i)
2154 AND line_id = trx_line_id_tab(i);
2155 EXCEPTION
2156 WHEN OTHERS THEN
2157 NULL;
2158 END;
2159 ELSE
2160 BEGIN
2161 SELECT primary_salesrep_id
2162 INTO l_sales_repid
2163 FROM ra_customer_trx_all
2164 WHERE customer_trx_id = trx_id_tab(i);
2165 EXCEPTION
2166 WHEN NO_DATA_FOUND THEN
2167 l_sales_repid := NULL;
2168 END;
2169 END IF;
2170 p_salesrep_tbl(trx_id_tab(i)) := l_sales_repid;
2171 END IF;
2172 END IF;
2173
2174 arp_poa_add_code_tab(i) := ARP_TAX_VIEW_VERTEX.POA_ADDRESS_CODE
2175 (l_view_name,
2176 trx_id_tab(i),
2177 trx_line_id_tab(i),
2178 l_sales_repid);
2179 IF (arp_poa_add_code_tab(i) is NULL) THEN
2180 IF l_sales_repid IS NOT NULL THEN
2181 IF poa_add_code_cache_tbl.EXISTS(l_sales_repid) THEN
2182 arp_poa_add_code_tab(i) := poa_add_code_cache_tbl(l_sales_repid);
2183 ELSE
2184 BEGIN
2185 SELECT sales_tax_geocode,sales_tax_inside_city_limits
2186 INTO l_poa_geocode, l_poa_in_out_flag
2187 FROM ra_salesreps
2188 WHERE salesrep_id = l_sales_repid;
2189 EXCEPTION
2190 WHEN OTHERS THEN
2191 arp_poa_add_code_tab(i) := ARP_TAX_VIEW_VERTEX.USE_SHIP_TO;
2192 END;
2193
2194 IF l_poa_geocode IS NULL THEN
2195 arp_poa_add_code_tab(i) := ARP_TAX_VIEW_VERTEX.USE_SHIP_TO;
2196 ELSE
2197 IF NOT Check_Geocode(l_poa_geocode) THEN
2198 arp_poa_add_code_tab(i) := ARP_TAX_VIEW_VERTEX.USE_SHIP_TO;
2199 ELSE
2200 IF l_poa_in_out_flag IS NULL THEN
2201 arp_poa_add_code_tab(i) := l_in_out_flag||l_poa_geocode;
2202 ELSE
2203 arp_poa_add_code_tab(i) := l_poa_in_out_flag||l_poa_geocode;
2204 END IF;
2205 END IF;
2206 END IF;
2207 poa_add_code_cache_tbl(l_sales_repid) := arp_poa_add_code_tab(i);
2208 END IF;
2209 ELSE
2210 arp_poa_add_code_tab(i) := ARP_TAX_VIEW_VERTEX.USE_SHIP_TO;
2211 END IF;
2212 END IF;
2213
2214 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2215 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
2216 ' arp_poa_add_code_tab(i) = ' || arp_poa_add_code_tab(i));
2217 END IF;
2218
2219 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2220 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2221 END IF;
2222
2223 END DERIVE_POA_ADDRESS_CODE;
2224
2225 PROCEDURE DERIVE_CUSTOMER_CODE
2226 IS
2227
2228 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_CUSTOMER_CODE';
2229
2230 BEGIN
2231 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2232 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2233 END IF;
2234
2235 arp_customer_code_tab(i) := ARP_TAX_VIEW_VERTEX.CUSTOMER_CODE
2236 (l_view_name,
2237 trx_id_tab(i),
2238 trx_line_id_tab(i));
2239
2240 IF arp_customer_code_tab(i) IS NULL THEN
2241
2242 BEGIN
2243
2244 SELECT account_number
2245 INTO arp_customer_code_tab(i)
2246 FROM HZ_CUST_ACCOUNTS
2247 WHERE cust_account_id = bill_third_pty_acct_id_tab(i);
2248
2249 EXCEPTION
2250 WHEN OTHERS THEN
2251 arp_customer_code_tab(i) := NULL;
2252 END;
2253
2254 END IF;
2255
2256 IF arp_customer_code_tab(i) IS NULL THEN
2257 /* Bug 5007293: During negation: ZX_PTNR_LOCATION_INFO_GT is not yet populated */
2258 IF g_line_negation THEN
2259 IF p_party_number_tbl.EXISTS(bill_to_party_tax_id_tab(i)) THEN
2260 arp_customer_code_tab(i) := p_party_number_tbl(bill_to_party_tax_id_tab(i));
2261 ELSE
2262 BEGIN
2263 SELECT pty.party_number
2264 INTO arp_customer_code_tab(i)
2265 FROM hz_parties pty,
2266 zx_party_tax_profile ptp
2267 WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
2268 AND ptp.party_id = pty.party_id;
2269 EXCEPTION WHEN OTHERS THEN
2270 arp_customer_code_tab(i) := NULL;
2271 END;
2272 p_party_number_tbl(bill_to_party_tax_id_tab(i)) := arp_customer_code_tab(i);
2273 END IF;
2274 ELSE
2275 BEGIN
2276 SELECT zpli.bill_to_party_number
2277 INTO arp_customer_code_tab(i)
2278 FROM ZX_PTNR_LOCATION_INFO_GT zpli
2279 , ZX_EVNT_CLS_MAPPINGS zecm
2280 WHERE zpli.EVENT_CLASS_MAPPING_ID = zecm.EVENT_CLASS_MAPPING_ID
2281 AND zecm.EVENT_CLASS_CODE = event_class_code_tab(i)
2282 AND zecm.APPLICATION_ID = application_id_tab(i)
2283 AND zecm.ENTITY_CODE = entity_code_tab(i)
2284 AND zpli.TRX_ID = trx_id_tab(i)
2285 AND zpli.TRX_LINE_ID = trx_line_id_tab(i);
2286 EXCEPTION WHEN OTHERS THEN
2287 arp_customer_code_tab(i) := NULL;
2288 END;
2289
2290 IF arp_customer_code_tab(i) IS NULL THEN
2291 IF p_party_number_tbl.EXISTS(bill_to_party_tax_id_tab(i)) THEN
2292 arp_customer_code_tab(i) := p_party_number_tbl(bill_to_party_tax_id_tab(i));
2293 ELSE
2294 BEGIN
2295 SELECT pty.party_number
2296 INTO arp_customer_code_tab(i)
2297 FROM hz_parties pty,
2298 zx_party_tax_profile ptp
2299 WHERE ptp.party_tax_profile_id = bill_to_party_tax_id_tab(i)
2300 AND ptp.party_id = pty.party_id;
2301 EXCEPTION WHEN OTHERS THEN
2302 arp_customer_code_tab(i) := NULL;
2303 END;
2304 p_party_number_tbl(bill_to_party_tax_id_tab(i)) := arp_customer_code_tab(i);
2305 END IF;
2306 END IF;
2307 END IF;
2308 END IF;
2309 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2310 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2311 END IF;
2312
2313 EXCEPTION WHEN OTHERS THEN
2314 arp_customer_code_tab(i):= NULL;
2315 END DERIVE_CUSTOMER_CODE;
2316
2317 PROCEDURE DERIVE_CUSTOMER_CLASS
2318 IS
2319
2320 l_party_id ZX_PARTY_TAX_PROFILE.PARTY_ID%TYPE;
2321 l_ptp_id ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID%TYPE;
2322 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_CUSTOMER_CLASS';
2323
2324 BEGIN
2325 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2326 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2327 END IF;
2328
2329 l_ptp_id := nvl(ship_to_tx_id_tab(i),bill_to_party_tax_id_tab(i));
2330 IF l_ptp_id IS NOT NULL THEN --- Bug 6024643
2331 IF p_party_id_tbl.EXISTS(l_ptp_id) THEN
2332 l_party_id := p_party_id_tbl(l_ptp_id);
2333 ELSE
2334 SELECT party_id
2335 INTO l_party_id
2336 FROM zx_party_tax_profile
2337 WHERE party_tax_profile_id = l_ptp_id;
2338 p_party_id_tbl(l_ptp_id) := l_party_id;
2339 END IF;
2340 END IF; --- Bug 6024643
2341 BEGIN
2342 arp_customer_class_tab(i) := ARP_TAX_VIEW_VERTEX.CUSTOMER_CLASS
2343 (l_view_name,
2344 trx_id_tab(i),
2345 trx_line_id_tab(i),
2346 l_party_id);
2347 EXCEPTION
2348 WHEN OTHERS THEN
2349 arp_customer_class_tab(i) := NULL;
2350 END;
2351
2352 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2353 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2354 END IF;
2355
2356 END DERIVE_CUSTOMER_CLASS;
2357
2358 PROCEDURE DERIVE_DIVISION_CODE
2359 IS
2360
2361 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_DIVISION_CODE';
2362
2363 BEGIN
2364 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2365 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2366 END IF;
2367
2368 arp_division_code_tab(i) := ARP_TAX_VIEW_VERTEX.DIVISION_CODE
2369 (l_view_name,
2370 trx_id_tab(i),
2371 trx_line_id_tab(i));
2372 /*IF (arp_division_code_tab(i) is NULL) THEN
2373 arp_division_code_tab(i) := '01';
2374 END IF;*/
2375
2376 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2377 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2378 END IF;
2379
2380 END DERIVE_DIVISION_CODE;
2381
2382 PROCEDURE DERIVE_TRANSACTION_DATE
2383 IS
2384
2385 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_TRANSACTION_DATE';
2386
2387 BEGIN
2388 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2389 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2390 END IF;
2391
2392 arp_transaction_date_tab(i) := ARP_TAX_VIEW_VERTEX.TRANSACTION_DATE
2393 (l_view_name,
2394 trx_id_tab(i),
2395 trx_line_id_tab(i));
2396 IF arp_transaction_date_tab(i) IS NULL THEN
2397 BEGIN
2398 SELECT trx_line_gl_date
2399 INTO arp_transaction_date_tab(i)
2400 FROM zx_lines_det_factors
2401 WHERE internal_organization_id = internal_org_id_tab(i)
2402 AND application_id = application_id_tab(i)
2403 AND Entity_Code = entity_code_tab(i)
2404 AND Event_Class_Code = event_class_code_tab(i)
2405 AND trx_id = trx_id_tab(i)
2406 AND trx_line_id = trx_line_id_tab(i);
2407
2408 EXCEPTION WHEN OTHERS THEN
2409 arp_transaction_date_tab(i) := NULL;
2410 END;
2411 END IF;
2412
2413 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2414 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2415 END IF;
2416
2417 EXCEPTION WHEN OTHERS THEN
2418 arp_transaction_date_tab(i) := NULL;
2419 END DERIVE_TRANSACTION_DATE;
2420
2421 PROCEDURE DERIVE_COMPANY_CODE
2422 IS
2423
2424 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_COMPANY_CODE';
2425
2426 BEGIN
2427 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2428 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
2429 END IF;
2430
2431 arp_company_code_tab(i) := ARP_TAX_VIEW_VERTEX.COMPANY_CODE
2432 (l_view_name,
2433 trx_id_tab(i),
2434 trx_line_id_tab(i));
2435 IF (arp_company_code_tab(i) is NULL) THEN
2436 arp_company_code_tab(i) := '01';
2437 END IF;
2438
2439 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2440 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
2441 END IF;
2442
2443 END DERIVE_COMPANY_CODE;
2444
2445 PROCEDURE ERROR_EXCEPTION_HANDLE(str varchar2) is
2446
2447 cursor error_exception_cursor is
2448 select EVNT_CLS_MAPPING_ID,
2449 TRX_ID,
2450 TAX_REGIME_CODE
2451 from ZX_TRX_PRE_PROC_OPTIONS_GT;
2452
2453 l_docment_type_id number;
2454 l_trasaction_id number;
2455 l_tax_regime_code varchar2(80);
2456
2457 Begin
2458 open error_exception_cursor;
2459 fetch error_exception_cursor into l_docment_type_id,l_trasaction_id,l_tax_regime_code;
2460
2461 G_MESSAGES_TBL.DOCUMENT_TYPE_ID(err_count) := l_docment_type_id;
2462 G_MESSAGES_TBL.TRANSACTION_ID(err_count) := l_trasaction_id;
2463 G_MESSAGES_TBL.COUNTRY_CODE(err_count) := l_tax_regime_code;
2464 G_MESSAGES_TBL.ERROR_MESSAGE_TYPE(err_count) := 'ERROR';
2465 G_MESSAGES_TBL.ERROR_MESSAGE_STRING(err_count) := str;
2466
2467 err_count :=err_count+1;
2468
2469 close error_exception_cursor;
2470
2471 End ERROR_EXCEPTION_HANDLE;
2472
2473 END ZX_VTX_USER_PKG;