[Home] [Help]
PACKAGE BODY: APPS.ZX_TAX_CONTENT_UPLOAD
Source
1 PACKAGE BODY ZX_TAX_CONTENT_UPLOAD AS
2 /* $Header: zxldgeorateb.pls 120.36.12020000.3 2012/11/06 10:39:41 ssohal noship $ */
3
4 /* ======================================================================*
5 | Global Data Types |
6 * ======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_TAX_CONTENT_UPLOAD';
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(40) := 'ZX.PLSQL.ZX_TAX_CONTENT_UPLOAD.';
17 G_CREATED_BY_MODULE CONSTANT VARCHAR2(30) := 'EBTAX_CONTENT_UPLOAD';
18 G_RECORD_EFFECTIVE_START CONSTANT DATE := TO_DATE('01/01/1952','MM/DD/YYYY');
19
20 --
21 -- Method to setup initial data for content provider
22 --
23 PROCEDURE SETUP_DATA
24 (
25 errbuf OUT NOCOPY VARCHAR2,
26 retcode OUT NOCOPY VARCHAR2,
27 p_tax_content_source IN VARCHAR2,
28 p_tax_regime_code IN VARCHAR2,
29 p_tax_zone_type IN VARCHAR2
30 ) IS
31
32 l_api_name CONSTANT VARCHAR2(30):= 'setup_data';
33
34 CURSOR c_tax_exists
35 (
36 b_tax_regime_code VARCHAR2,
37 b_tax VARCHAR2,
38 b_content_owner_id NUMBER
39 ) IS
40 SELECT 'Y'
41 FROM ZX_TAXES_B
42 WHERE TAX_REGIME_CODE = b_tax_regime_code
43 AND TAX = b_tax
44 AND CONTENT_OWNER_ID = b_content_owner_id;
45
46 CURSOR c_tax_status_exists
47 (
48 b_tax_regime_code VARCHAR2,
49 b_tax VARCHAR2,
50 b_content_owner_id NUMBER,
51 b_tax_status_code VARCHAR2
52 ) IS
53 SELECT 'Y'
54 FROM ZX_STATUS_B
55 WHERE TAX_REGIME_CODE = b_tax_regime_code
56 AND TAX = b_tax
57 AND CONTENT_OWNER_ID = b_content_owner_id
58 AND TAX_STATUS_CODE = b_tax_status_code;
59
60 CURSOR c_geography_type_exists
61 (
62 b_geography_type VARCHAR2
63 ) IS
64 SELECT 'Y'
65 FROM HZ_GEOGRAPHY_TYPES_VL
66 WHERE GEOGRAPHY_TYPE = b_geography_type;
67
68 l_exists_flag VARCHAR2(1);
69 l_tax VARCHAR2(30);
70 l_incl_geo_type HZ_GEOGRAPHY_STRUCTURE_PUB.INCL_GEO_TYPE_TBL_TYPE;
71 l_zone_type_rec HZ_GEOGRAPHY_STRUCTURE_PUB.ZONE_TYPE_REC_TYPE;
72 l_return_status VARCHAR2(1);
73 l_msg_count NUMBER;
74 l_msg_data VARCHAR2(2000);
75
76 BEGIN
77
78 retcode := '0';
79
80 FND_FILE.PUT_LINE
81 (
82 FND_FILE.LOG,
83 'Starting setup_data.'
84 );
85
86 FOR I IN 1..3
87 LOOP
88 IF (I = 1)
89 THEN
90 l_tax := 'STATE';
91 ELSIF (I = 2)
92 THEN
93 l_tax := 'COUNTY';
94 ELSIF (I = 3)
95 THEN
96 l_tax := 'CITY';
97 ELSE
98 EXIT;
99 END IF;
100
101 l_exists_flag := 'N';
102 OPEN c_tax_exists(p_tax_regime_code,l_tax,-99);
103 FETCH c_tax_exists
104 INTO l_exists_flag;
105 CLOSE c_tax_exists;
106
107 IF (NVL(l_exists_flag,'N') = 'N')
108 THEN
109
110 FND_FILE.PUT_LINE
111 (
112 FND_FILE.LOG,
113 'Creating Tax: '||l_tax||'.'
114 );
115
116 INSERT INTO ZX_TAXES_B_TMP
117 (
118 TAX ,
119 EFFECTIVE_FROM ,
120 EFFECTIVE_TO ,
121 TAX_REGIME_CODE ,
122 TAX_TYPE_CODE ,
123 ALLOW_MANUAL_ENTRY_FLAG ,
124 ALLOW_TAX_OVERRIDE_FLAG ,
125 MIN_TXBL_BSIS_THRSHLD ,
126 MAX_TXBL_BSIS_THRSHLD ,
127 MIN_TAX_RATE_THRSHLD ,
128 MAX_TAX_RATE_THRSHLD ,
129 MIN_TAX_AMT_THRSHLD ,
130 MAX_TAX_AMT_THRSHLD ,
131 COMPOUNDING_PRECEDENCE ,
132 PERIOD_SET_NAME ,
133 EXCHANGE_RATE_TYPE ,
134 TAX_CURRENCY_CODE ,
135 TAX_PRECISION ,
136 MINIMUM_ACCOUNTABLE_UNIT ,
137 ROUNDING_RULE_CODE ,
138 TAX_STATUS_RULE_FLAG ,
139 TAX_RATE_RULE_FLAG ,
140 DEF_PLACE_OF_SUPPLY_TYPE_CODE ,
141 PLACE_OF_SUPPLY_RULE_FLAG ,
142 DIRECT_RATE_RULE_FLAG ,
143 APPLICABILITY_RULE_FLAG ,
144 TAX_CALC_RULE_FLAG ,
145 TXBL_BSIS_THRSHLD_FLAG ,
146 TAX_RATE_THRSHLD_FLAG ,
147 TAX_AMT_THRSHLD_FLAG ,
148 TAXABLE_BASIS_RULE_FLAG ,
149 DEF_INCLUSIVE_TAX_FLAG ,
150 THRSHLD_GROUPING_LVL_CODE ,
151 HAS_OTHER_JURISDICTIONS_FLAG ,
152 ALLOW_EXEMPTIONS_FLAG ,
153 ALLOW_EXCEPTIONS_FLAG ,
154 ALLOW_RECOVERABILITY_FLAG ,
155 DEF_TAX_CALC_FORMULA ,
156 TAX_INCLUSIVE_OVERRIDE_FLAG ,
157 DEF_TAXABLE_BASIS_FORMULA ,
158 DEF_REGISTR_PARTY_TYPE_CODE ,
159 REGISTRATION_TYPE_RULE_FLAG ,
160 REPORTING_ONLY_FLAG ,
161 AUTO_PRVN_FLAG ,
162 LIVE_FOR_PROCESSING_FLAG ,
163 LIVE_FOR_APPLICABILITY_FLAG ,
164 HAS_DETAIL_TB_THRSHLD_FLAG ,
165 HAS_TAX_DET_DATE_RULE_FLAG ,
166 HAS_EXCH_RATE_DATE_RULE_FLAG ,
167 HAS_TAX_POINT_DATE_RULE_FLAG ,
168 PRINT_ON_INVOICE_FLAG ,
169 USE_LEGAL_MSG_FLAG ,
170 CALC_ONLY_FLAG ,
171 PRIMARY_RECOVERY_TYPE_CODE ,
172 PRIMARY_REC_TYPE_RULE_FLAG ,
173 SECONDARY_RECOVERY_TYPE_CODE ,
174 SECONDARY_REC_TYPE_RULE_FLAG ,
175 PRIMARY_REC_RATE_DET_RULE_FLAG ,
176 SEC_REC_RATE_DET_RULE_FLAG ,
177 OFFSET_TAX_FLAG ,
178 RECOVERY_RATE_OVERRIDE_FLAG ,
179 ZONE_GEOGRAPHY_TYPE ,
180 REGN_NUM_SAME_AS_LE_FLAG ,
181 DEF_REC_SETTLEMENT_OPTION_CODE ,
182 RECORD_TYPE_CODE ,
183 ALLOW_ROUNDING_OVERRIDE_FLAG ,
184 SOURCE_TAX_FLAG ,
185 SPECIAL_INCLUSIVE_TAX_FLAG ,
186 ATTRIBUTE1 ,
187 ATTRIBUTE2 ,
188 ATTRIBUTE3 ,
189 ATTRIBUTE4 ,
190 ATTRIBUTE5 ,
191 ATTRIBUTE6 ,
192 ATTRIBUTE7 ,
193 ATTRIBUTE8 ,
194 ATTRIBUTE9 ,
195 ATTRIBUTE10 ,
196 ATTRIBUTE11 ,
197 ATTRIBUTE12 ,
198 ATTRIBUTE13 ,
199 ATTRIBUTE14 ,
200 ATTRIBUTE15 ,
201 ATTRIBUTE_CATEGORY ,
202 PARENT_GEOGRAPHY_TYPE ,
203 PARENT_GEOGRAPHY_ID ,
204 ALLOW_MASS_CREATE_FLAG ,
205 APPLIED_AMT_HANDLING_FLAG ,
206 TAX_ID ,
207 CONTENT_OWNER_ID ,
208 REP_TAX_AUTHORITY_ID ,
209 COLL_TAX_AUTHORITY_ID ,
210 THRSHLD_CHK_TMPLT_CODE ,
211 DEF_PRIMARY_REC_RATE_CODE ,
212 DEF_SECONDARY_REC_RATE_CODE ,
213 CREATED_BY ,
214 CREATION_DATE ,
215 LAST_UPDATED_BY ,
216 LAST_UPDATE_DATE ,
217 LAST_UPDATE_LOGIN ,
218 REQUEST_ID ,
219 PROGRAM_APPLICATION_ID ,
220 PROGRAM_ID ,
221 PROGRAM_LOGIN_ID ,
222 OVERRIDE_GEOGRAPHY_TYPE ,
223 OBJECT_VERSION_NUMBER ,
224 TAX_ACCOUNT_CREATE_METHOD_CODE ,
225 TAX_ACCOUNT_SOURCE_TAX ,
226 TAX_EXMPT_CR_METHOD_CODE ,
227 TAX_EXMPT_SOURCE_TAX ,
228 APPLICABLE_BY_DEFAULT_FLAG
229 )
230 VALUES
231 (
232 l_tax , -- TAX
233 G_RECORD_EFFECTIVE_START , -- EFFECTIVE_FROM
234 NULL , -- EFFECTIVE_TO
235 p_tax_regime_code , -- TAX_REGIME_CODE
236 NULL , -- TAX_TYPE_CODE
237 'N' , -- ALLOW_MANUAL_ENTRY_FLAG
238 'N' , -- ALLOW_TAX_OVERRIDE_FLAG
239 NULL , -- MIN_TXBL_BSIS_THRSHLD
240 NULL , -- MAX_TXBL_BSIS_THRSHLD
241 NULL , -- MIN_TAX_RATE_THRSHLD
242 NULL , -- MAX_TAX_RATE_THRSHLD
243 NULL , -- MIN_TAX_AMT_THRSHLD
244 NULL , -- MAX_TAX_AMT_THRSHLD
245 NULL , -- COMPOUNDING_PRECEDENCE
246 NULL , -- PERIOD_SET_NAME
247 NULL , -- EXCHANGE_RATE_TYPE
248 'USD' , -- TAX_CURRENCY_CODE
249 2 , -- TAX_PRECISION
250 NULL , -- MINIMUM_ACCOUNTABLE_UNIT
251 'DOWN' , -- ROUNDING_RULE_CODE
252 'N' , -- TAX_STATUS_RULE_FLAG
253 'N' , -- TAX_RATE_RULE_FLAG
254 'SHIP_TO_BILL_TO' , -- DEF_PLACE_OF_SUPPLY_TYPE_CODE
255 'N' , -- PLACE_OF_SUPPLY_RULE_FLAG
256 'N' , -- DIRECT_RATE_RULE_FLAG
257 'N' , -- APPLICABILITY_RULE_FLAG
258 'N' , -- TAX_CALC_RULE_FLAG
259 'N' , -- TXBL_BSIS_THRSHLD_FLAG
260 'N' , -- TAX_RATE_THRSHLD_FLAG
261 'N' , -- TAX_AMT_THRSHLD_FLAG
262 'N' , -- TAXABLE_BASIS_RULE_FLAG
263 'N' , -- DEF_INCLUSIVE_TAX_FLAG
264 NULL , -- THRSHLD_GROUPING_LVL_CODE
265 'Y' , -- HAS_OTHER_JURISDICTIONS_FLAG
266 'Y' , -- ALLOW_EXEMPTIONS_FLAG
267 'Y' , -- ALLOW_EXCEPTIONS_FLAG
268 'N' , -- ALLOW_RECOVERABILITY_FLAG
269 'STANDARD_TC' , -- DEF_TAX_CALC_FORMULA
270 'N' , -- TAX_INCLUSIVE_OVERRIDE_FLAG
271 'STANDARD_TB' , -- DEF_TAXABLE_BASIS_FORMULA
272 'SHIP_TO_PARTY' , -- DEF_REGISTR_PARTY_TYPE_CODE
273 'N' , -- REGISTRATION_TYPE_RULE_FLAG
274 'N' , -- REPORTING_ONLY_FLAG
275 'N' , -- AUTO_PRVN_FLAG
276 'N' , -- LIVE_FOR_PROCESSING_FLAG
277 'Y' , -- LIVE_FOR_APPLICABILITY_FLAG
278 'N' , -- HAS_DETAIL_TB_THRSHLD_FLAG
279 'N' , -- HAS_TAX_DET_DATE_RULE_FLAG
280 'N' , -- HAS_EXCH_RATE_DATE_RULE_FLAG
281 'N' , -- HAS_TAX_POINT_DATE_RULE_FLAG
282 'Y' , -- PRINT_ON_INVOICE_FLAG
283 'N' , -- USE_LEGAL_MSG_FLAG
284 'N' , -- CALC_ONLY_FLAG
285 NULL , -- PRIMARY_RECOVERY_TYPE_CODE
286 'N' , -- PRIMARY_REC_TYPE_RULE_FLAG
287 NULL , -- SECONDARY_RECOVERY_TYPE_CODE
288 'N' , -- SECONDARY_REC_TYPE_RULE_FLAG
289 'N' , -- PRIMARY_REC_RATE_DET_RULE_FLAG
293 l_tax , -- ZONE_GEOGRAPHY_TYPE
290 'N' , -- SEC_REC_RATE_DET_RULE_FLAG
291 'N' , -- OFFSET_TAX_FLAG
292 'N' , -- RECOVERY_RATE_OVERRIDE_FLAG
294 'N' , -- REGN_NUM_SAME_AS_LE_FLAG
295 NULL , -- DEF_REC_SETTLEMENT_OPTION_CODE
296 G_CREATED_BY_MODULE , -- RECORD_TYPE_CODE
297 NULL , -- ALLOW_ROUNDING_OVERRIDE_FLAG
298 'Y' , -- SOURCE_TAX_FLAG
299 'N' , -- SPECIAL_INCLUSIVE_TAX_FLAG
300 NULL , -- ATTRIBUTE1
301 NULL , -- ATTRIBUTE2
302 NULL , -- ATTRIBUTE3
303 NULL , -- ATTRIBUTE4
304 NULL , -- ATTRIBUTE5
305 NULL , -- ATTRIBUTE6
306 NULL , -- ATTRIBUTE7
307 NULL , -- ATTRIBUTE8
308 NULL , -- ATTRIBUTE9
309 NULL , -- ATTRIBUTE10
310 NULL , -- ATTRIBUTE11
311 NULL , -- ATTRIBUTE12
312 NULL , -- ATTRIBUTE13
313 NULL , -- ATTRIBUTE14
314 NULL , -- ATTRIBUTE15
315 NULL , -- ATTRIBUTE_CATEGORY
316 'COUNTRY' , -- PARENT_GEOGRAPHY_TYPE
317 1 , -- PARENT_GEOGRAPHY_ID
318 'N' , -- ALLOW_MASS_CREATE_FLAG
319 'P' , -- APPLIED_AMT_HANDLING_FLAG
320 zx_taxes_b_s.nextval , -- TAX_ID
321 -99 , -- CONTENT_OWNER_ID
322 NULL , -- REP_TAX_AUTHORITY_ID
323 NULL , -- COLL_TAX_AUTHORITY_ID
324 NULL , -- THRSHLD_CHK_TMPLT_CODE
325 NULL , -- DEF_PRIMARY_REC_RATE_CODE
326 NULL , -- DEF_SECONDARY_REC_RATE_CODE
327 fnd_global.user_id , -- CREATED_BY
328 SYSDATE , -- CREATION_DATE
329 fnd_global.user_id , -- LAST_UPDATED_BY
330 SYSDATE , -- LAST_UPDATE_DATE
331 fnd_global.conc_login_id , -- LAST_UPDATE_LOGIN
332 fnd_global.conc_request_id , -- REQUEST_ID
333 fnd_global.prog_appl_id , -- PROGRAM_APPLICATION_ID
334 fnd_global.conc_program_id , -- PROGRAM_ID
335 fnd_global.conc_login_id , -- PROGRAM_LOGIN_ID
336 p_tax_zone_type , -- OVERRIDE_GEOGRAPHY_TYPE
337 1 , -- OBJECT_VERSION_NUMBER
338 'CREATE_ACCOUNTS' , --TAX_ACCOUNT_CREATE_METHOD_CODE
339 decode(l_tax,'STATE', NULL,'STATE') , --TAX_ACCOUNT_SOURCE_TAX
340 'CREATE_EXEMPTIONS' , --TAX_EXMPT_CR_METHOD_CODE
341 NULL ,
342 'Y' --APPLICABLE_BY_DEFAULT_FLAG
343 );
344
345 FND_FILE.PUT_LINE
346 (
347 FND_FILE.LOG,
348 'Record Inserted: '||TO_CHAR(SQL%ROWCOUNT)
349 );
350
351 FND_FILE.PUT_LINE
352 (
353 FND_FILE.LOG,
354 'Creating TL Record for Tax '||l_tax||'.'
355 );
356
357 INSERT INTO ZX_TAXES_TL
358 (
359 LANGUAGE ,
360 SOURCE_LANG ,
361 TAX_FULL_NAME ,
362 CREATED_BY ,
363 CREATION_DATE ,
364 LAST_UPDATED_BY ,
365 LAST_UPDATE_DATE ,
366 LAST_UPDATE_LOGIN ,
367 TAX_ID
368 )
369 SELECT
370 fl.LANGUAGE_CODE ,
371 USERENV('LANG') ,
372 l_tax ,
373 fnd_global.user_id , -- CREATED_BY
374 SYSDATE , -- CREATION_DATE
375 fnd_global.user_id , -- LAST_UPDATED_BY
376 SYSDATE , -- LAST_UPDATE_DATE
377 fnd_global.conc_login_id , -- LAST_UPDATE_LOGIN
378 ztb.tax_id
379 FROM ZX_TAXES_B ztb,
380 FND_LANGUAGES fl
381 WHERE fl.INSTALLED_FLAG IN ('I', 'B')
382 AND ztb.TAX_REGIME_CODE = p_tax_regime_code
383 AND ztb.CONTENT_OWNER_ID = -99
384 AND ztb.TAX = l_tax;
385
386 FND_FILE.PUT_LINE
387 (
388 FND_FILE.LOG,
389 'Records Inserted: '||TO_CHAR(SQL%ROWCOUNT)
390 );
391
392 END IF;
393
394 l_exists_flag := 'N';
395 OPEN c_tax_status_exists(p_tax_regime_code,l_tax,-99,'STANDARD');
396 FETCH c_tax_status_exists
397 INTO l_exists_flag;
398 CLOSE c_tax_status_exists;
399
403 FND_FILE.PUT_LINE
400 IF (NVL(l_exists_flag,'N') = 'N')
401 THEN
402
404 (
405 FND_FILE.LOG,
406 'Creating STANDARD Tax Status for Tax: '||l_tax||'.'
407 );
408
409 INSERT INTO ZX_STATUS_B_TMP
410 (
411 TAX_STATUS_ID,
412 TAX_STATUS_CODE,
413 CONTENT_OWNER_ID,
414 EFFECTIVE_FROM,
415 EFFECTIVE_TO,
416 TAX,
417 TAX_REGIME_CODE,
418 RULE_BASED_RATE_FLAG,
419 ALLOW_RATE_OVERRIDE_FLAG,
420 ALLOW_EXEMPTIONS_FLAG,
421 ALLOW_EXCEPTIONS_FLAG,
422 DEFAULT_STATUS_FLAG,
423 DEFAULT_FLG_EFFECTIVE_FROM,
424 DEFAULT_FLG_EFFECTIVE_TO,
425 DEF_REC_SETTLEMENT_OPTION_CODE,
426 RECORD_TYPE_CODE,
427 ATTRIBUTE1,
428 ATTRIBUTE2,
429 ATTRIBUTE3,
430 ATTRIBUTE4,
431 ATTRIBUTE5,
432 ATTRIBUTE6,
433 ATTRIBUTE7,
434 ATTRIBUTE8,
435 ATTRIBUTE9,
436 ATTRIBUTE10,
437 ATTRIBUTE11,
438 ATTRIBUTE12,
439 ATTRIBUTE13,
440 ATTRIBUTE14,
441 ATTRIBUTE15,
442 ATTRIBUTE_CATEGORY,
443 CREATION_DATE,
444 CREATED_BY,
445 LAST_UPDATE_DATE,
446 LAST_UPDATED_BY,
447 LAST_UPDATE_LOGIN,
448 REQUEST_ID,
449 OBJECT_VERSION_NUMBER
450 )
451 VALUES
452 (
453 ZX_STATUS_B_S.NEXTVAL, --TAX_STATUS_ID
454 'STANDARD', --TAX_STATUS_CODE
455 -99, --CONTENT_OWNER_ID
456 G_RECORD_EFFECTIVE_START,--EFFECTIVE_FROM
457 NULL, --EFFECTIVE_TO
458 l_tax, --TAX
459 p_tax_regime_code, --TAX_REGIME_CODE
460 'N', --RULE_BASED_RATE_FLAG
461 'N', --ALLOW_RATE_OVERRIDE_FLAG
462 'Y', --ALLOW_EXEMPTIONS_FLAG
463 'Y', --ALLOW_EXCEPTIONS_FLAG
464 'Y', --DEFAULT_STATUS_FLAG
465 G_RECORD_EFFECTIVE_START,--DEFAULT_FLG_EFFECTIVE_FROM
466 NULL, --DEFAULT_FLG_EFFECTIVE_TO
467 NULL, --DEF_REC_SETTLEMENT_OPTION_CODE
468 G_CREATED_BY_MODULE,
469 NULL,
470 NULL,
471 NULL,
472 NULL,
473 NULL,
474 NULL,
475 NULL,
476 NULL,
477 NULL,
478 NULL,
479 NULL,
480 NULL,
481 NULL,
482 NULL,
483 NULL,
484 NULL,
485 SYSDATE,
486 fnd_global.user_id,
487 SYSDATE,
488 fnd_global.user_id,
489 fnd_global.conc_login_id,
490 fnd_global.conc_request_id ,-- Request Id
491 1
492 );
493
494 FND_FILE.PUT_LINE
495 (
496 FND_FILE.LOG,
497 'Record Inserted: '||TO_CHAR(SQL%ROWCOUNT)
498 );
499
500 FND_FILE.PUT_LINE
501 (
502 FND_FILE.LOG,
503 'Creating TL Record for the Tax Status.'
504 );
505
506 INSERT INTO ZX_STATUS_TL
507 (
508 LANGUAGE ,
509 SOURCE_LANG ,
510 TAX_STATUS_NAME ,
511 CREATED_BY ,
512 CREATION_DATE ,
513 LAST_UPDATED_BY ,
514 LAST_UPDATE_DATE ,
515 LAST_UPDATE_LOGIN ,
516 TAX_STATUS_ID
517 )
518 SELECT
519 fl.LANGUAGE_CODE ,
520 USERENV('LANG') ,
521 'STANDARD' ,
522 fnd_global.user_id , -- CREATED_BY
523 SYSDATE , -- CREATION_DATE
524 fnd_global.user_id , -- LAST_UPDATED_BY
525 SYSDATE , -- LAST_UPDATE_DATE
526 fnd_global.conc_login_id , -- LAST_UPDATE_LOGIN
527 zsb.tax_status_id
528 FROM ZX_STATUS_B zsb,
529 FND_LANGUAGES fl
530 WHERE fl.INSTALLED_FLAG IN ('I', 'B')
531 AND zsb.TAX_REGIME_CODE = p_tax_regime_code
532 AND zsb.CONTENT_OWNER_ID = -99
533 AND zsb.TAX = l_tax
534 AND zsb.TAX_STATUS_CODE = 'STANDARD';
535
536 FND_FILE.PUT_LINE
537 (
538 FND_FILE.LOG,
539 'Records Inserted: '||TO_CHAR(SQL%ROWCOUNT)
540 );
541
542 END IF;
543
544 END LOOP;
545
546 l_exists_flag := 'N';
547 OPEN c_geography_type_exists(p_tax_zone_type);
548 FETCH c_geography_type_exists
549 INTO l_exists_flag;
550 CLOSE c_geography_type_exists;
551
552 IF (NVL(l_exists_flag,'N') = 'N')
553 THEN
554
555 FND_FILE.PUT_LINE
556 (
557 FND_FILE.LOG,
558 'Creating Tax Zone Type: '||p_tax_zone_type||'.'
562 l_incl_geo_type(2) := 'CITY';
559 );
560
561 l_incl_geo_type(1) := 'COUNTY';
563 l_incl_geo_type(3) := 'STATE';
564 l_zone_type_rec.geography_type := p_tax_zone_type;
565 l_zone_type_rec.included_geography_type := l_incl_geo_type;
566 l_zone_type_rec.postal_code_range_flag := 'Y';
567 l_zone_type_rec.geography_use := 'TAX';
568 l_zone_type_rec.limited_by_geography_id := 1;
569 l_zone_type_rec.created_by_module := G_CREATED_BY_MODULE;
570 l_zone_type_rec.application_id := 235;
571 HZ_GEOGRAPHY_STRUCTURE_PUB.create_zone_type
572 (
573 'F',
574 l_zone_type_rec,
575 l_return_status,
576 l_msg_count,
577 l_msg_data
578 );
579
580 FND_FILE.PUT_LINE
581 (
582 FND_FILE.LOG,
583 'Status returned by HZ_GEOGRAPHY_STRUCTURE_PUB.create_zone_type: '||l_return_status||', and message: '||l_msg_data
584 );
585
586 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
587 THEN
588 retcode := '2';
589 errbuf := 'Error creating tax zone type: '||l_msg_data;
590 END IF;
591
592 END IF;
593
594 END SETUP_DATA;
595
596 --
597 -- Method to find geography ids and stamp interface table
598 --
599 PROCEDURE GENERATE_GEOGRAPHY_ID
600 (
601 p_tax_content_source IN VARCHAR2,
602 p_tax_regime_code IN VARCHAR2,
603 p_migrated_tax_regime_flag IN VARCHAR2,
604 p_tax_zone_type IN VARCHAR2,
605 p_last_run_version IN NUMBER
606 ) IS
607
608 CURSOR C_CNTRY_ID IS
609 SELECT GEOGRAPHY_ID
610 FROM HZ_GEOGRAPHIES
611 WHERE GEOGRAPHY_CODE = 'US'
612 AND GEOGRAPHY_TYPE = 'COUNTRY'
613 AND GEOGRAPHY_USE = 'MASTER_REF';
614
615 l_api_name CONSTANT VARCHAR2(30):= 'generate_geography_id';
616 l_rows_processed NUMBER;
617 l_start NUMBER;
618 l_end NUMBER;
619 l_cntry_geography_id NUMBER;
620 l_log VARCHAR2(2000);
621
622 TYPE rowid_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
623 TYPE geography_id_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
624 l_rowids rowid_type;
625 l_geography_ids geography_id_type;
626 l_rowcount number;
627
628 CURSOR c_get_city_rows
629 (
630 b_cntry_geography_id NUMBER
631 ) IS
632 SELECT DISTINCT
633 X.ROWID,
634 Y.GEOGRAPHY_ID
635 FROM ZX_DATA_UPLOAD_INTERFACE X,
636 HZ_GEOGRAPHIES Y,
637 ZX_DATA_UPLOAD_INTERFACE Z,
638 ZX_DATA_UPLOAD_INTERFACE ZZ
639 WHERE X.RECORD_TYPE = 6
640 AND UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
641 AND Y.GEOGRAPHY_USE = 'MASTER_REF'
642 AND Y.GEOGRAPHY_TYPE = 'CITY'
643 AND Y.GEOGRAPHY_ELEMENT1_ID = b_cntry_geography_id
644 AND Y.GEOGRAPHY_ELEMENT2_ID = Z.GEOGRAPHY_ID
645 AND Z.RECORD_TYPE = 1
646 AND Z.STATE_JURISDICTION_CODE = X.STATE_JURISDICTION_CODE
647 AND Y.GEOGRAPHY_ELEMENT3_ID = ZZ.GEOGRAPHY_ID
648 AND ZZ.RECORD_TYPE = 3
649 AND ZZ.STATE_JURISDICTION_CODE = X.STATE_JURISDICTION_CODE
650 AND ZZ.COUNTY_JURISDICTION_CODE = X.COUNTY_JURISDICTION_CODE
651 AND NVL(Y.END_DATE, TO_DATE('12-31-4712', 'MM-DD-YYYY')) = TO_DATE('12-31-4712', 'MM-DD-YYYY');
652
653 BEGIN
654
655 OPEN C_CNTRY_ID;
656 FETCH C_CNTRY_ID
657 INTO l_cntry_geography_id;
658 CLOSE C_CNTRY_ID;
659
660 l_start := DBMS_UTILITY.GET_TIME;
661
662 -- Find the state geography id using abbreviation code. Note that since we
663 -- are using code, even the name change records will get the geography_id.
664 UPDATE ZX_DATA_UPLOAD_INTERFACE x
665 SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
666 FROM HZ_GEOGRAPHIES Y
667 WHERE Y.GEOGRAPHY_NAME = X.COUNTRY_STATE_ABBREVIATION
668 AND Y.GEOGRAPHY_CODE = X.COUNTRY_STATE_ABBREVIATION
669 AND Y.COUNTRY_CODE = 'US'
670 AND Y.GEOGRAPHY_TYPE = 'STATE'
671 AND Y.GEOGRAPHY_USE = 'MASTER_REF'
672 AND Y.GEOGRAPHY_ELEMENT1_ID = L_CNTRY_GEOGRAPHY_ID
673 AND NVL(Y.END_DATE, TO_DATE('12-31-4712', 'MM-DD-YYYY'))
674 = TO_DATE('12-31-4712', 'MM-DD-YYYY')),
675 x.status = 'NOCHANGE'
676 WHERE x.record_type = 1;
677
678 l_rows_processed := SQL%ROWCOUNT;
679 l_end := DBMS_UTILITY.GET_TIME;
680 l_log := 'After State Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
681 FND_FILE.PUT_LINE
682 (
683 FND_FILE.LOG,
684 l_log
685 );
686
687 l_start := DBMS_UTILITY.GET_TIME;
688
689 -- Now update the status of the name change record.
690 UPDATE ZX_DATA_UPLOAD_INTERFACE x
691 SET x.status = 'UPDATE'
692 WHERE x.record_type = 1
693 AND x.effective_to IS NULL
694 AND EXISTS (SELECT NULL
695 FROM ZX_DATA_UPLOAD_INTERFACE y
696 WHERE y.record_type = 1
697 AND y.state_jurisdiction_code = x.state_jurisdiction_code
698 AND y.country_state_abbreviation = x.country_state_abbreviation
699 AND y.effective_to IS NOT NULL);
700
701 l_rows_processed := SQL%ROWCOUNT;
702 l_end := DBMS_UTILITY.GET_TIME;
703 l_log := 'After State Name Change Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
704 FND_FILE.PUT_LINE
705 (
706 FND_FILE.LOG,
707 l_log
708 );
709
710 l_start := DBMS_UTILITY.GET_TIME;
711
712 -- Geography Id's for County for pre-existing geographies
713 -- The state record could have been ended or it could have been sent twice
714 -- with name change, so rownum clause is used.
715 UPDATE ZX_DATA_UPLOAD_INTERFACE x
716 SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
717 FROM HZ_GEOGRAPHIES Y,
718 ZX_DATA_UPLOAD_INTERFACE Z
719 WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
720 AND Y.GEOGRAPHY_USE = 'MASTER_REF'
721 AND Y.GEOGRAPHY_TYPE = 'COUNTY'
722 AND Y.GEOGRAPHY_ELEMENT1_ID = L_CNTRY_GEOGRAPHY_ID
723 AND Y.GEOGRAPHY_ELEMENT2_ID = Z.GEOGRAPHY_ID
724 AND Z.RECORD_TYPE = 1
725 AND Z.STATE_JURISDICTION_CODE = X.STATE_JURISDICTION_CODE
726 AND NVL(Y.END_DATE, TO_DATE('12-31-4712', 'MM-DD-YYYY'))
727 = TO_DATE('12-31-4712', 'MM-DD-YYYY')
728 AND ROWNUM = 1),
729 x.status = 'NOCHANGE'
730 WHERE x.record_type = 3;
731
732 l_rows_processed := SQL%ROWCOUNT;
733 l_end := DBMS_UTILITY.GET_TIME;
734 l_log := 'After County Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
735 FND_FILE.PUT_LINE
736 (
737 FND_FILE.LOG,
738 l_log
739 );
740
741 l_start := DBMS_UTILITY.GET_TIME;
742
743 -- Geography Id's for County for pre-existing geographies, which had name
744 -- change. Use existing record with old name to find the id.
745 UPDATE ZX_DATA_UPLOAD_INTERFACE x
746 SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
747 FROM ZX_DATA_UPLOAD_INTERFACE y
748 WHERE y.RECORD_TYPE = 3
749 AND y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
750 AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
751 AND y.GEOGRAPHY_ID IS NOT NULL),
752 x.status = 'UPDATE'
753 WHERE x.record_type = 3
754 AND x.geography_id IS NULL
755 AND x.effective_to IS NULL;
756
757 l_rows_processed := SQL%ROWCOUNT;
758 l_end := DBMS_UTILITY.GET_TIME;
759 l_log := 'After County Name Change Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
760 FND_FILE.PUT_LINE
761 (
762 FND_FILE.LOG,
763 l_log
764 );
765
766 l_start := DBMS_UTILITY.GET_TIME;
767
768 -- Geography Id's for City for pre-existing geographies.
769 -- This is for the case where a city is in a single county
770 -- or city with multiple counties has been created as multiple geographies.
771 -- rownum = 1 clause is used as the there could be multiple county
772 -- or state records with different effective dates
773 --
774 -- UPDATE ZX_DATA_UPLOAD_INTERFACE x
775 -- SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
776 -- FROM HZ_GEOGRAPHIES Y,
777 -- ZX_DATA_UPLOAD_INTERFACE Z,
778 -- ZX_DATA_UPLOAD_INTERFACE ZZ
779 -- WHERE UPPER(Y.GEOGRAPHY_NAME) = UPPER(X.GEOGRAPHY_NAME)
780 -- AND Y.GEOGRAPHY_USE = 'MASTER_REF'
781 -- AND Y.GEOGRAPHY_TYPE = 'CITY'
782 -- AND Y.GEOGRAPHY_ELEMENT1_ID = L_CNTRY_GEOGRAPHY_ID
783 -- AND Y.GEOGRAPHY_ELEMENT2_ID = Z.GEOGRAPHY_ID
784 -- AND Z.RECORD_TYPE = 1
785 -- AND Z.STATE_JURISDICTION_CODE = X.STATE_JURISDICTION_CODE
786 -- AND Y.GEOGRAPHY_ELEMENT3_ID = ZZ.GEOGRAPHY_ID
787 -- AND ZZ.RECORD_TYPE = 3
788 -- AND ZZ.STATE_JURISDICTION_CODE = X.STATE_JURISDICTION_CODE
789 -- AND ZZ.COUNTY_JURISDICTION_CODE = X.COUNTY_JURISDICTION_CODE
790 -- AND ROWNUM = 1),
791 -- x.status = 'NOCHANGE'
792 -- WHERE x.record_type = 6;
793 --
794 -- l_rows_processed := SQL%ROWCOUNT;
795
796 l_rowcount := 0;
797 l_rows_processed := 0;
798
799 OPEN c_get_city_rows(l_cntry_geography_id);
800 FETCH c_get_city_rows
801 BULK COLLECT INTO l_rowids, l_geography_ids;
802
803 l_rowcount := c_get_city_rows%rowcount;
804 l_rows_processed := l_rows_processed + l_rowcount;
805
806 FORALL i IN l_rowids.FIRST..l_rowids.LAST
807 UPDATE zx_data_upload_interface
808 SET geography_id = l_geography_ids(i),
809 status = 'NOCHANGE'
810 WHERE rowid = l_rowids(i);
811
812 l_end := DBMS_UTILITY.GET_TIME;
813 l_log := 'After City Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
814 FND_FILE.PUT_LINE
815 (
816 FND_FILE.LOG,
817 l_log
818 );
819
820 -- l_start := DBMS_UTILITY.GET_TIME;
821 --
822 -- Geography Id's for City for pre-existing geographies, which had name
823 -- change. Use existing record with old name to find the id.
824 --
825 -- UPDATE ZX_DATA_UPLOAD_INTERFACE x
826 -- SET x.geography_id = (SELECT Y.GEOGRAPHY_ID
827 -- FROM ZX_DATA_UPLOAD_INTERFACE y
828 -- WHERE y.RECORD_TYPE = 6
829 -- AND y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
830 -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
831 -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
832 -- AND y.GEOGRAPHY_ID IS NOT NULL
833 -- AND ROWNUM = 1),
834 -- x.status = 'UPDATE'
835 -- WHERE x.record_type = 6
836 -- AND x.geography_id IS NULL
837 -- AND x.effective_to IS NULL;
838 --
839 -- l_rows_processed := SQL%ROWCOUNT;
840 -- l_end := DBMS_UTILITY.GET_TIME;
841 -- l_log := 'After City Name Change Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
842 -- FND_FILE.PUT_LINE
843 -- (
844 -- FND_FILE.LOG,
845 -- l_log
846 -- );
847
848 l_start := DBMS_UTILITY.GET_TIME;
849
850 -- Get new id for new state and county
851 UPDATE ZX_DATA_UPLOAD_INTERFACE
852 SET geography_id = hz_geographies_s.nextval,
853 status = 'CREATE'
854 WHERE record_type IN (1,3)
855 AND geography_id IS NULL
856 AND effective_to IS NULL;
857
858 l_rows_processed := SQL%ROWCOUNT;
859 l_end := DBMS_UTILITY.GET_TIME;
860 l_log := 'After new State and County Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
861 FND_FILE.PUT_LINE
862 (
863 FND_FILE.LOG,
864 l_log
865 );
866
867 l_start := DBMS_UTILITY.GET_TIME;
868
869 -- Get new id for new city. In the case of city divided in two or more
870 -- jurisdictions, get new id only for the first one
871 UPDATE ZX_DATA_UPLOAD_INTERFACE
872 SET geography_id = hz_geographies_s.nextval,
873 status = 'CREATE'
874 WHERE record_type = 6
875 AND geography_id IS NULL
876 AND effective_to IS NULL
877 AND (GEOGRAPHY_NAME,CITY_JURISDICTION_CODE,COUNTY_JURISDICTION_CODE,STATE_JURISDICTION_CODE)
878 IN (SELECT GEOGRAPHY_NAME,
879 CITY_JURISDICTION_CODE,
880 COUNTY_JURISDICTION_CODE,
881 STATE_JURISDICTION_CODE
882 FROM (SELECT GEOGRAPHY_NAME,
883 CITY_JURISDICTION_CODE,
884 COUNTY_JURISDICTION_CODE,
885 STATE_JURISDICTION_CODE,
886 GEOGRAPHY_ID,
887 ROW_NUMBER()
888 OVER (PARTITION BY STATE_JURISDICTION_CODE,
889 COUNTY_JURISDICTION_CODE,
890 GEOGRAPHY_NAME
891 ORDER BY GEOGRAPHY_ID,
892 EFFECTIVE_FROM)
893 AS CITY_ROW_NUMBER
894 FROM ZX_DATA_UPLOAD_INTERFACE
895 WHERE RECORD_TYPE = 6
896 --AND GEOGRAPHY_ID IS NULL
897 AND EFFECTIVE_TO IS NULL
898 )
899 WHERE CITY_ROW_NUMBER = 1
900 AND GEOGRAPHY_ID IS NULL);
901
902 l_rows_processed := SQL%ROWCOUNT;
903 l_end := DBMS_UTILITY.GET_TIME;
904 l_log := 'After new City Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
905 FND_FILE.PUT_LINE
906 (
907 FND_FILE.LOG,
908 l_log
909 );
910
911 l_start := DBMS_UTILITY.GET_TIME;
912
913 -- Use the id from previous step for multi jurisdiction cities
914 UPDATE ZX_DATA_UPLOAD_INTERFACE a
915 SET geography_id = (SELECT b.geography_id
916 FROM ZX_DATA_UPLOAD_INTERFACE b
917 WHERE b.geography_name = a.geography_name
918 AND b.STATE_JURISDICTION_CODE = a.STATE_JURISDICTION_CODE
919 AND b.COUNTY_JURISDICTION_CODE = a.COUNTY_JURISDICTION_CODE
920 AND b.record_type = 6
921 AND b.geography_id IS NOT NULL),
922 status = 'NOCHANGE'
923 WHERE record_type = 6
924 AND geography_id IS NULL
925 AND effective_to IS NULL;
926
927 l_rows_processed := SQL%ROWCOUNT;
928 l_end := DBMS_UTILITY.GET_TIME;
929 l_log := 'After new City 2nd Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
930 FND_FILE.PUT_LINE
931 (
932 FND_FILE.LOG,
933 l_log
934 );
935
936 l_start := DBMS_UTILITY.GET_TIME;
937
938 -- Update a new geography id for all remaining cities.
939 UPDATE ZX_DATA_UPLOAD_INTERFACE
940 SET geography_id = hz_geographies_s.nextval,
941 status = 'CREATE'
942 WHERE record_type = 6
943 AND geography_id IS NULL
944 AND effective_to IS NULL;
945
946 l_rows_processed := SQL%ROWCOUNT;
947 l_end := DBMS_UTILITY.GET_TIME;
948 l_log := 'After new City 3rd Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
949 FND_FILE.PUT_LINE
950 (
951 FND_FILE.LOG,
952 l_log
953 );
954
955 l_start := DBMS_UTILITY.GET_TIME;
956
957 -- Update the zip range with their corresponding city rows
958 UPDATE ZX_DATA_UPLOAD_INTERFACE a
959 SET (geography_id,status) = (SELECT b.geography_id, b.status
960 FROM ZX_DATA_UPLOAD_INTERFACE b
961 WHERE b.STATE_JURISDICTION_CODE = a.STATE_JURISDICTION_CODE
962 AND b.COUNTY_JURISDICTION_CODE = a.COUNTY_JURISDICTION_CODE
963 AND b.CITY_JURISDICTION_CODE = a.CITY_JURISDICTION_CODE
964 AND b.GEOGRAPHY_NAME = a.GEOGRAPHY_NAME
965 AND b.effective_to IS NULL
966 AND b.RECORD_TYPE = 6
967 AND ROWNUM = 1)
968 WHERE record_type = 8
969 AND geography_id IS NULL
970 AND effective_to IS NULL;
971
972 l_rows_processed := SQL%ROWCOUNT;
973 l_end := DBMS_UTILITY.GET_TIME;
974 l_log := 'After Zip Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
975 FND_FILE.PUT_LINE
976 (
977 FND_FILE.LOG,
978 l_log
979 );
980
981 -- The zone type used is different for migrated records and uploaded ones
982 -- So, separate updates.
983
984 IF (p_migrated_tax_regime_flag = 'Y')
985 THEN
986
987 l_start := DBMS_UTILITY.GET_TIME;
988
989 -- First UPDATE the zone_geography_id for pre-existing states
990 UPDATE ZX_DATA_UPLOAD_INTERFACE x
991 SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
992 WHERE y.geography_name = DECODE(p_tax_content_source,
993 'TAXWARE','ST-'||x.COUNTRY_STATE_ABBREVIATION,
994 'VERTEX','ST-'||x.STATE_JURISDICTION_CODE||'0000000',
995 'OTHERS','ST-'||x.STATE_JURISDICTION_CODE||'0000000',
996 'ST-'||x.STATE_JURISDICTION_CODE||'0000000')
997 AND y.GEOGRAPHY_TYPE = 'US_STATE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10)
998 AND NVL(y.END_DATE, TO_DATE('12-31-4712', 'MM-DD-YYYY'))
999 = TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1000 WHERE x.record_type = 1
1001 AND x.zone_geography_id IS NULL;
1002 -- AND EXISTS (SELECT NULL
1003 -- FROM ZX_DATA_UPLOAD_INTERFACE y
1004 -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1005 -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1006 -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1007 -- AND y.record_type in (9,10,11,12));
1008
1009 l_rows_processed := SQL%ROWCOUNT;
1010 l_end := DBMS_UTILITY.GET_TIME;
1011 l_log := 'After State Zone Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1012 FND_FILE.PUT_LINE
1013 (
1014 FND_FILE.LOG,
1015 l_log
1016 );
1017
1018 l_start := DBMS_UTILITY.GET_TIME;
1019
1020 -- First UPDATE the zone_geography_id for pre-existing counties
1021 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1022 SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
1023 WHERE y.geography_name = DECODE(p_tax_content_source,
1024 'TAXWARE','CO-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,21)),
1025 'VERTEX','CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000',
1026 'OTHERS','CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000',
1027 'CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000')
1028 AND y.GEOGRAPHY_TYPE = 'US_COUNTY_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10)
1029 AND NVL(y.END_DATE, TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1030 = TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1031 WHERE x.record_type = 3
1032 AND x.zone_geography_id IS NULL
1033 AND x.geography_id IS NOT NULL;
1034 -- AND EXISTS (SELECT NULL
1035 -- FROM ZX_DATA_UPLOAD_INTERFACE y
1036 -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1037 -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1038 -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1039 -- AND y.record_type in (9,10,11,12));
1040
1041 l_rows_processed := SQL%ROWCOUNT;
1042 l_end := DBMS_UTILITY.GET_TIME;
1043 l_log := 'After County Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1044 FND_FILE.PUT_LINE
1045 (
1046 FND_FILE.LOG,
1047 l_log
1048 );
1049
1050 l_start := DBMS_UTILITY.GET_TIME;
1051
1052 -- First UPDATE the zone_geography_id for pre-existing cities
1053 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1054 SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
1055 WHERE y.geography_name = DECODE(p_tax_content_source,
1056 'TAXWARE','CI-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,12))||'-'||x.CITY_JURISDICTION_CODE,
1057 'VERTEX','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),
1058 'OTHERS','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),
1059 'CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||TRIM(SUBSTR(x.CITY_JURISDICTION_CODE,1,19)))
1060 AND y.GEOGRAPHY_TYPE = 'US_CITY_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10)
1061 AND NVL(y.END_DATE, TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1062 = TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1063 WHERE x.record_type = 6
1064 AND x.zone_geography_id IS NULL
1065 AND x.geography_id IS NOT NULL;
1066 -- AND EXISTS (SELECT NULL
1067 -- FROM ZX_DATA_UPLOAD_INTERFACE y
1068 -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1069 -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1070 -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1071 -- AND y.record_type in (9,10,11,12));
1072
1073 l_rows_processed := SQL%ROWCOUNT;
1074 l_end := DBMS_UTILITY.GET_TIME;
1075 l_log := 'After City Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1076 FND_FILE.PUT_LINE
1077 (
1078 FND_FILE.LOG,
1079 l_log
1080 );
1081
1082 l_start := DBMS_UTILITY.GET_TIME;
1083
1084 -- UPDATE the existing zone_geography_id for cities with same geocode
1085 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1086 SET x.zone_geography_id = (SELECT y.zone_geography_id
1087 FROM zx_data_upload_interface y
1088 WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1089 AND x.county_jurisdiction_code = y.county_jurisdiction_code
1090 AND x.city_jurisdiction_code = y.city_jurisdiction_code
1091 AND x.geography_name <> y.geography_name
1092 AND y.zone_geography_id is not null
1093 AND y.record_type = 6
1094 AND ROWNUM = 1
1095 )
1096 WHERE x.record_type = 6
1097 AND x.zone_geography_id IS NULL
1098 AND x.geography_id IS NOT NULL;
1099
1100 l_rows_processed := SQL%ROWCOUNT;
1101 l_end := DBMS_UTILITY.GET_TIME;
1102 l_log := 'After City Zone UPDATE 2, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1103 FND_FILE.PUT_LINE
1104 (
1105 FND_FILE.LOG,
1106 l_log
1107 );
1108
1109 l_start := DBMS_UTILITY.GET_TIME;
1110
1111 -- Now, UPDATE the zone_geography_id for new records for counties AND states
1112 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1113 SET x.zone_geography_id = hz_geographies_s.nextval
1114 WHERE x.record_type IN (1,3)
1115 AND x.zone_geography_id IS NULL
1116 AND x.effective_to IS NULL;
1117 -- AND EXISTS (SELECT NULL
1118 -- FROM ZX_DATA_UPLOAD_INTERFACE y
1119 -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1120 -- AND NVL(y.COUNTY_JURISDICTION_CODE,'-1') = NVL(x.COUNTY_JURISDICTION_CODE,'-1')
1121 -- AND NVL(y.CITY_JURISDICTION_CODE,'-1') = NVL(x.CITY_JURISDICTION_CODE,'-1')
1122 -- AND y.record_type in (9,10,11,12));
1123
1124 l_rows_processed := SQL%ROWCOUNT;
1125 l_end := DBMS_UTILITY.GET_TIME;
1126 l_log := 'After new State/County Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1127 FND_FILE.PUT_LINE
1128 (
1129 FND_FILE.LOG,
1130 l_log
1131 );
1132
1133 l_start := DBMS_UTILITY.GET_TIME;
1134
1135 -- UPDATE the zone geography id for primary cities first.
1136 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1137 SET x.zone_geography_id = hz_geographies_s.nextval
1138 WHERE x.record_type = 6
1139 AND x.zone_geography_id IS NULL
1140 AND x.effective_to IS NULL
1141 AND x.primary_flag = 'Y';
1142
1143 l_rows_processed := SQL%ROWCOUNT;
1144 l_end := DBMS_UTILITY.GET_TIME;
1145 l_log := 'After new Primary City Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1146 FND_FILE.PUT_LINE
1147 (
1148 FND_FILE.LOG,
1149 l_log
1150 );
1151
1152 l_start := DBMS_UTILITY.GET_TIME;
1153
1154 -- UPDATE the zone geography id for non primary cities.
1155 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1156 SET x.zone_geography_id = (SELECT y.zone_geography_id
1157 FROM zx_data_upload_interface y
1158 WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1159 AND x.county_jurisdiction_code = y.county_jurisdiction_code
1160 AND x.city_jurisdiction_code = y.city_jurisdiction_code
1161 AND x.geography_name <> y.geography_name
1162 AND y.zone_geography_id is not null
1163 AND y.record_type = 6
1164 AND y.primary_flag = 'Y'
1165 AND ROWNUM = 1
1166 )
1167 WHERE x.record_type = 6
1168 AND x.zone_geography_id IS NULL
1169 AND x.effective_to IS NULL
1170 AND x.primary_flag = 'N';
1171
1172 l_rows_processed := SQL%ROWCOUNT;
1173 l_end := DBMS_UTILITY.GET_TIME;
1174 l_log := 'After new Non Primary City Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1175 FND_FILE.PUT_LINE
1176 (
1177 FND_FILE.LOG,
1178 l_log
1179 );
1180
1181 l_start := DBMS_UTILITY.GET_TIME;
1182
1183 -- Now, UPDATE the zone_geography_id for override rates
1184 -- UPDATE only the first row as there could be multiple rates for one
1185 -- overriding jurisdiction. First case is for city overriding state/county
1186 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1187 SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
1188 WHERE y.geography_name = DECODE(p_tax_content_source,
1189 'TAXWARE','CI-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,12))||'-'||x.CITY_JURISDICTION_CODE,
1190 'VERTEX','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),
1191 'OTHERS','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),
1192 'CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||TRIM(SUBSTR(x.CITY_JURISDICTION_CODE,1,19)))
1193 AND y.GEOGRAPHY_TYPE = 'US_OVERRIDE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10)
1194 AND NVL(y.END_DATE, TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1195 = TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1196 WHERE x.record_type IN (9,10,11,12)
1197 AND x.zone_geography_id IS NULL
1198 AND x.STATE_JURISDICTION_CODE IS NOT NULL
1199 AND x.COUNTY_JURISDICTION_CODE IS NOT NULL
1200 AND x.CITY_JURISDICTION_CODE IS NOT NULL
1201 AND x.rowid
1202 IN (SELECT row_id
1203 FROM (SELECT ROWID AS row_id,
1204 record_type,
1205 state_jurisdiction_code,
1206 county_jurisdiction_code,
1207 city_jurisdiction_code,
1208 ROW_NUMBER()
1209 OVER (PARTITION BY STATE_JURISDICTION_CODE,
1210 COUNTY_JURISDICTION_CODE,
1211 CITY_JURISDICTION_CODE
1212 ORDER BY ROWID)
1213 AS ROW_NUMBER
1214 FROM ZX_DATA_UPLOAD_INTERFACE
1215 WHERE record_type IN (9,10,11,12)
1216 AND last_updation_version > p_last_run_version
1217 AND state_jurisdiction_code IS NOT NULL
1218 AND county_jurisdiction_code IS NOT NULL
1219 AND city_jurisdiction_code IS NOT NULL
1220 AND (sales_tax_authority_level = 'STATE'
1221 OR sales_tax_authority_level = 'COUNTY'
1222 OR rental_tax_authority_level = 'STATE'
1223 OR rental_tax_authority_level = 'COUNTY'
1224 OR use_tax_authority_level = 'STATE'
1225 OR use_tax_authority_level = 'COUNTY'
1226 OR lease_tax_authority_level = 'STATE'
1227 OR lease_tax_authority_level = 'COUNTY')
1228 )
1229 WHERE row_number = 1
1230 );
1231
1232 l_rows_processed := SQL%ROWCOUNT;
1233 l_end := DBMS_UTILITY.GET_TIME;
1234 l_log := 'After City Override Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1235 FND_FILE.PUT_LINE
1236 (
1237 FND_FILE.LOG,
1238 l_log
1239 );
1240
1241 l_start := DBMS_UTILITY.GET_TIME;
1242
1243 -- Now, UPDATE the zone_geography_id for override rates
1244 -- UPDATE only the first row as there could be multiple rates for one
1245 -- overriding jurisdiction
1246 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1247 SET x.zone_geography_id = hz_geographies_s.nextval
1248 WHERE x.record_type IN (9,10,11,12)
1249 AND x.zone_geography_id IS NULL
1250 AND x.STATE_JURISDICTION_CODE IS NOT NULL
1251 AND x.COUNTY_JURISDICTION_CODE IS NOT NULL
1252 AND x.CITY_JURISDICTION_CODE IS NOT NULL
1253 AND x.rowid
1254 IN (SELECT row_id
1255 FROM (SELECT ROWID AS row_id,
1256 record_type,
1257 state_jurisdiction_code,
1258 county_jurisdiction_code,
1259 city_jurisdiction_code,
1260 ROW_NUMBER()
1261 OVER (PARTITION BY STATE_JURISDICTION_CODE,
1262 COUNTY_JURISDICTION_CODE,
1263 CITY_JURISDICTION_CODE
1264 ORDER BY ROWID)
1265 AS ROW_NUMBER
1266 FROM ZX_DATA_UPLOAD_INTERFACE
1267 WHERE record_type IN (9,10,11,12)
1268 AND last_updation_version > p_last_run_version
1269 AND state_jurisdiction_code IS NOT NULL
1270 AND county_jurisdiction_code IS NOT NULL
1271 AND city_jurisdiction_code IS NOT NULL
1272 AND (sales_tax_authority_level = 'STATE'
1273 OR sales_tax_authority_level = 'COUNTY'
1274 OR rental_tax_authority_level = 'STATE'
1275 OR rental_tax_authority_level = 'COUNTY'
1276 OR use_tax_authority_level = 'STATE'
1277 OR use_tax_authority_level = 'COUNTY'
1278 OR lease_tax_authority_level = 'STATE'
1279 OR lease_tax_authority_level = 'COUNTY')
1280 )
1281 WHERE row_number = 1
1282 );
1283
1284 l_rows_processed := SQL%ROWCOUNT;
1285 l_end := DBMS_UTILITY.GET_TIME;
1286 l_log := 'After new City Override Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1287 FND_FILE.PUT_LINE
1288 (
1289 FND_FILE.LOG,
1290 l_log
1291 );
1292
1293 l_start := DBMS_UTILITY.GET_TIME;
1294
1295 -- Now, UPDATE the zone_geography_id for override rates
1296 -- UPDATE only the first row as there could be multiple rates for one
1297 -- overriding jurisdiction. First case is for county overriding state
1298 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1299 SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
1300 WHERE y.geography_name = DECODE(p_tax_content_source,
1301 'TAXWARE','CO-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,21)),
1302 'VERTEX','CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000',
1303 'OTHERS','CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000',
1304 'CO-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||'0000')
1305 AND y.GEOGRAPHY_TYPE = 'US_OVERRIDE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10)
1306 AND NVL(y.END_DATE, TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1307 = TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1308 WHERE x.record_type IN (9,10,11,12)
1309 AND x.zone_geography_id IS NULL
1310 AND x.STATE_JURISDICTION_CODE IS NOT NULL
1311 AND x.COUNTY_JURISDICTION_CODE IS NOT NULL
1312 AND x.CITY_JURISDICTION_CODE IS NULL
1313 AND x.rowid
1314 IN (SELECT row_id
1315 FROM (SELECT ROWID AS row_id,
1316 record_type,
1317 state_jurisdiction_code,
1318 county_jurisdiction_code,
1319 ROW_NUMBER()
1320 OVER (PARTITION BY STATE_JURISDICTION_CODE,
1321 COUNTY_JURISDICTION_CODE
1322 ORDER BY ROWID)
1323 AS ROW_NUMBER
1324 FROM ZX_DATA_UPLOAD_INTERFACE
1325 WHERE record_type IN (9,10,11,12)
1326 AND last_updation_version > p_last_run_version
1327 AND state_jurisdiction_code IS NOT NULL
1328 AND county_jurisdiction_code IS NOT NULL
1329 AND city_jurisdiction_code IS NULL
1330 AND (sales_tax_authority_level = 'STATE'
1331 OR rental_tax_authority_level = 'STATE'
1332 OR use_tax_authority_level = 'STATE'
1333 OR lease_tax_authority_level = 'STATE')
1334 )
1335 WHERE row_number = 1
1336 );
1337
1338 l_rows_processed := SQL%ROWCOUNT;
1339 l_end := DBMS_UTILITY.GET_TIME;
1340 l_log := 'After County Override Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1341 FND_FILE.PUT_LINE
1342 (
1343 FND_FILE.LOG,
1344 l_log
1345 );
1346
1347 l_start := DBMS_UTILITY.GET_TIME;
1348
1349 -- Now, UPDATE the zone_geography_id for override rates
1350 -- UPDATE only the first row as there could be multiple rates for one
1351 -- overriding jurisdiction
1352 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1353 SET x.zone_geography_id = hz_geographies_s.nextval
1354 WHERE x.record_type IN (9,10,11,12)
1355 AND x.zone_geography_id IS NULL
1356 AND x.STATE_JURISDICTION_CODE IS NOT NULL
1357 AND x.COUNTY_JURISDICTION_CODE IS NOT NULL
1358 AND x.CITY_JURISDICTION_CODE IS NULL
1359 AND x.rowid
1360 IN (SELECT row_id
1361 FROM (SELECT ROWID AS row_id,
1362 record_type,
1363 state_jurisdiction_code,
1364 county_jurisdiction_code,
1365 ROW_NUMBER()
1366 OVER (PARTITION BY STATE_JURISDICTION_CODE,
1367 COUNTY_JURISDICTION_CODE
1368 ORDER BY ROWID)
1369 AS ROW_NUMBER
1370 FROM ZX_DATA_UPLOAD_INTERFACE
1371 WHERE record_type IN (9,10,11,12)
1372 AND last_updation_version > p_last_run_version
1373 AND state_jurisdiction_code IS NOT NULL
1374 AND county_jurisdiction_code IS NOT NULL
1375 AND city_jurisdiction_code IS NULL
1376 AND (sales_tax_authority_level = 'STATE'
1377 OR rental_tax_authority_level = 'STATE'
1378 OR use_tax_authority_level = 'STATE'
1379 OR lease_tax_authority_level = 'STATE')
1380 )
1381 WHERE row_number = 1
1382 );
1383
1384 l_rows_processed := SQL%ROWCOUNT;
1385 l_end := DBMS_UTILITY.GET_TIME;
1386 l_log := 'After new County Override Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1387 FND_FILE.PUT_LINE
1388 (
1389 FND_FILE.LOG,
1390 l_log
1391 );
1392
1393 ELSE
1394
1395 l_start := DBMS_UTILITY.GET_TIME;
1396
1397 -- First UPDATE the zone_geography_id for pre-existing cities,
1398 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1399 SET x.zone_geography_id = (SELECT y.geography_id FROM hz_geographies y
1400 WHERE y.geography_name = DECODE(p_tax_content_source,
1401 'TAXWARE','CI-'||x.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(x.GEOGRAPHY_NAME,1,12))||'-'||x.CITY_JURISDICTION_CODE,
1402 'VERTEX','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),
1403 'OTHERS','CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||LPAD(x.CITY_JURISDICTION_CODE,4,'0'),
1404 'CI-'||x.STATE_JURISDICTION_CODE||x.COUNTY_JURISDICTION_CODE||TRIM(SUBSTR(x.CITY_JURISDICTION_CODE,1,19)))
1405 AND y.GEOGRAPHY_USE = 'TAX'
1406 AND y.GEOGRAPHY_TYPE = p_tax_zone_type
1407 AND NVL(y.END_DATE, TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1408 = TO_DATE('12-31-4712', 'MM-DD-YYYY'))
1409 WHERE x.record_type = 6
1410 AND x.zone_geography_id IS NULL;
1411 -- AND EXISTS (SELECT NULL
1412 -- FROM ZX_DATA_UPLOAD_INTERFACE y
1413 -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1414 -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1415 -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1416 -- AND y.record_type in (9,10,11,12));
1417
1418 l_rows_processed := SQL%ROWCOUNT;
1419 l_end := DBMS_UTILITY.GET_TIME;
1420 l_log := 'After City Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1421 FND_FILE.PUT_LINE
1422 (
1423 FND_FILE.LOG,
1424 l_log
1425 );
1426
1427 l_start := DBMS_UTILITY.GET_TIME;
1428
1429 -- UPDATE the zone_geography_id for cities
1430 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1431 SET x.zone_geography_id = (SELECT y.zone_geography_id
1432 FROM zx_data_upload_interface y
1433 WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1434 AND x.county_jurisdiction_code = y.county_jurisdiction_code
1435 AND x.city_jurisdiction_code = y.city_jurisdiction_code
1436 AND x.geography_name <> y.geography_name
1437 AND y.zone_geography_id IS NOT NULL
1438 AND y.record_type = 6
1439 AND ROWNUM = 1)
1440 WHERE x.record_type = 6
1441 AND x.zone_geography_id IS NULL;
1442
1443 l_rows_processed := SQL%ROWCOUNT;
1444 l_end := DBMS_UTILITY.GET_TIME;
1445 l_log := 'After City Zone UPDATE 2, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1446 FND_FILE.PUT_LINE
1447 (
1448 FND_FILE.LOG,
1449 l_log
1450 );
1451
1452 l_start := DBMS_UTILITY.GET_TIME;
1453
1454 -- Now, UPDATE the zone_geography_id for new records,
1455 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1456 SET x.zone_geography_id = hz_geographies_s.nextval
1457 WHERE x.record_type = 6
1458 AND x.zone_geography_id IS NULL
1459 AND x.effective_to IS NULL
1460 AND x.primary_flag = 'Y';
1461 -- AND EXISTS (SELECT null
1462 -- FROM ZX_DATA_UPLOAD_INTERFACE y
1463 -- WHERE y.STATE_JURISDICTION_CODE = x.STATE_JURISDICTION_CODE
1464 -- AND y.COUNTY_JURISDICTION_CODE = x.COUNTY_JURISDICTION_CODE
1465 -- AND y.CITY_JURISDICTION_CODE = x.CITY_JURISDICTION_CODE
1466 -- AND y.record_type in (9,10,11,12));
1467
1468 l_rows_processed := SQL%ROWCOUNT;
1469 l_end := DBMS_UTILITY.GET_TIME;
1470 l_log := 'After new Primary City Zone UPDATE, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1471 FND_FILE.PUT_LINE
1472 (
1473 FND_FILE.LOG,
1474 l_log
1475 );
1476
1477 UPDATE ZX_DATA_UPLOAD_INTERFACE x
1478 SET x.zone_geography_id = (SELECT y.zone_geography_id
1479 FROM zx_data_upload_interface y
1480 WHERE x.state_jurisdiction_code = y.state_jurisdiction_code
1481 AND x.county_jurisdiction_code = y.county_jurisdiction_code
1482 AND x.city_jurisdiction_code = y.city_jurisdiction_code
1483 AND x.geography_name <> y.geography_name
1484 AND y.zone_geography_id IS NOT NULL
1485 AND y.record_type = 6
1486 AND y.primary_flag = 'Y'
1487 AND ROWNUM = 1)
1488 WHERE x.record_type = 6
1489 AND x.zone_geography_id IS NULL
1490 AND x.effective_to IS NULL
1491 AND x.primary_flag = 'N';
1492
1493 l_rows_processed := SQL%ROWCOUNT;
1494 l_end := DBMS_UTILITY.GET_TIME;
1495 l_log := 'After new City Zone Update, rows processed:'||l_rows_processed||', in time (ms):'||to_char((l_end-l_start)*10);
1496 FND_FILE.PUT_LINE
1497 (
1498 FND_FILE.LOG,
1499 l_log
1500 );
1501
1502 END IF;
1503
1504 END GENERATE_GEOGRAPHY_ID;
1505
1506 --
1507 -- Procedure to do error check
1508 --
1509 PROCEDURE DO_ERROR_CHECK
1510 (
1511 p_tax_content_source IN VARCHAR2,
1512 p_last_run_version IN NUMBER,
1513 p_tax_regime_code IN VARCHAR2,
1514 p_migrated_tax_regime_flag IN VARCHAR2
1515 ) IS
1516
1517 CURSOR c_get_zip
1518 IS
1519 SELECT ROWID as row_id,
1520 city_jurisdiction_code,
1521 county_jurisdiction_code,
1522 state_jurisdiction_code,
1523 state_jurisdiction_code||county_jurisdiction_code||city_jurisdiction_code||geography_name concat_code,
1524 zip_begin,
1525 zip_end
1526 FROM zx_data_upload_interface
1527 WHERE record_type = 08
1528 AND last_updation_version > p_last_run_version
1529 AND effective_to IS NULL;
1530
1531 CURSOR c_get_rates
1532 IS
1533 SELECT
1534 v1.row_id,
1535 v1.record_type,
1536 v1.city_jurisdiction_code,
1537 v1.county_jurisdiction_code,
1538 v1.state_jurisdiction_code,
1539 v1.tax_regime_code,
1540 v1.tax,
1541 v1.content_owner_id,
1542 v1.tax_status_code,
1543 v1.tax_jurisdiction_code,
1544 v1.tax_rate_code,
1545 v1.effective_from new_effective_from,
1546 v1.effective_to new_effective_to,
1547 v1.active_flag new_active_flag,
1548 v1.rate_type_code,
1549 v1.percentage_rate,
1550 v1.jur_effective_from,
1551 zrb.tax_rate_id,
1552 zrb.effective_from old_effective_from,
1553 zrb.effective_to old_effective_to,
1554 zrb.active_flag old_active_flag,
1555 zrb.record_type_code record_type_code
1556 FROM (
1557 SELECT v.rowid as row_id,
1558 v.record_type,
1559 v.city_jurisdiction_code,
1560 v.county_jurisdiction_code,
1561 v.state_jurisdiction_code,
1562 v.tax_regime_code,
1563 v.tax,
1564 v.content_owner_id,
1565 v.tax_status_code,
1566 decode(p_tax_content_source,
1567 'TAXWARE',decode(v.tax,'STATE',
1568 decode(to_char(jur.record_type),'1','ST-'||v.COUNTRY_STATE_ABBREVIATION,
1569 '3','ST-CO-'||v.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,21)),
1570 '6','ST-CI-'||v.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||v.city_jurisdiction_code),
1571 'COUNTY',decode(to_char(jur.record_type),
1572 '3','CO-'||v.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,21)),
1573 '6','CO-CI-'||v.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||v.city_jurisdiction_code),
1574 'CITY','CI-'||v.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||v.city_jurisdiction_code),
1575 'VERTEX',DECODE(v.tax,'STATE',
1576 decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
1577 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),'CITY','CI-')||
1578 v.state_jurisdiction_code||nvl(v.county_jurisdiction_code,'000')||lpad(nvl(v.city_jurisdiction_code,'0000'),4,'0'),
1579 'OTHERS',DECODE(v.tax,'STATE',
1580 decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
1581 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),'CITY','CI-')||
1582 v.state_jurisdiction_code||nvl(v.county_jurisdiction_code,'000')||lpad(nvl(v.city_jurisdiction_code,'0000'),4,'0'),
1583 DECODE(v.tax,'STATE',
1584 decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
1585 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),'CITY','CI-')||
1586 v.state_jurisdiction_code||nvl(v.county_jurisdiction_code,'000')||TRIM(SUBSTR(nvl(v.city_jurisdiction_code,'0000'),1,19))
1587 ) tax_jurisdiction_code,
1588 v.tax_rate_code,
1589 v.effective_from,
1590 v.effective_to,
1591 v.active_flag,
1592 v.rate_type_code,
1593 v.percentage_rate,
1594 jur.effective_from jur_effective_from
1595 FROM
1596 (SELECT x.row_id,
1597 x.record_type,
1598 x.country_state_abbreviation,
1599 x.city_jurisdiction_code,
1600 x.county_jurisdiction_code,
1601 x.state_jurisdiction_code,
1602 x.tax_regime_code,
1603 x.tax,
1604 x.content_owner_id,
1605 x.tax_status_code,
1606 x.tax_rate_code,
1607 x.effective_from,
1608 x.effective_to,
1609 x.rate_type_code,
1610 x.percentage_rate,
1611 x.active_flag
1612 FROM
1613 (SELECT rowid row_id,
1614 record_type,
1615 country_state_abbreviation,
1616 city_jurisdiction_code,
1617 county_jurisdiction_code,
1618 state_jurisdiction_code,
1619 p_tax_regime_code tax_regime_code,
1620 decode(to_char(record_type),'9',sales_tax_authority_level,'10',rental_tax_authority_level,'11',use_tax_authority_level,'12',lease_tax_authority_level) tax,
1621 -99 content_owner_id,
1622 'STANDARD' tax_status_code,
1623 decode(p_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(record_type),'10','-RENTAL','11','-USE','12','-LEASE') tax_rate_code,
1624 effective_from,
1625 effective_to,
1626 'PERCENTAGE' rate_type_code,
1627 decode(record_type,9,sales_tax_rate,10,rental_tax_rate,11,use_tax_rate,12,lease_tax_rate) percentage_rate,
1628 decode(to_char(record_type),'9',sales_tax_rate_active_flag,'10',rental_tax_rate_active_flag,'11',use_tax_rate_active_flag,'12',lease_tax_rate_active_flag) active_flag
1629 FROM
1630 zx_data_upload_interface
1631 WHERE record_type in (9,10,11,12)
1632 AND last_updation_version > p_last_run_version
1633 ) x
1634 ) v,
1635 zx_data_upload_interface jur
1636 WHERE jur.record_type = decode(v.city_jurisdiction_code,null,decode(v.county_jurisdiction_code,null,1,3),6)
1637 AND jur.state_jurisdiction_code = v.state_jurisdiction_code
1638 AND NVL(jur.county_jurisdiction_code,'-1') = NVL(v.county_jurisdiction_code,'-1')
1639 AND NVL(jur.city_jurisdiction_code,'-1') = NVL(v.city_jurisdiction_code,'-1')
1640 AND NVL(jur.primary_flag,'Y') = 'Y'
1641 AND jur.effective_to IS NULL) v1,
1642 ZX_RATES_B zrb
1643 WHERE zrb.tax_regime_code(+) = v1.tax_regime_code
1644 AND zrb.tax(+) = v1.tax
1645 AND zrb.content_owner_id(+) = v1.content_owner_id
1646 AND zrb.tax_jurisdiction_code(+) = v1.tax_jurisdiction_code
1647 AND zrb.tax_rate_code(+) = v1.tax_rate_code;
1648
1649 -- Cursor to find duplicates in the set-up
1650 -- Added for Bug#7527399
1651 CURSOR c_get_dup_rates
1652 IS
1653 SELECT
1654 v1.row_id row_id,
1655 v1.record_type record_type,
1656 v1.state_jurisdiction_code state_jurisdiction_code,
1657 v1.county_jurisdiction_code county_jurisdiction_code,
1658 v1.city_jurisdiction_code city_jurisdiction_code,
1659 v1.tax data_upload_tax,
1660 v1.tax_jurisdiction_code data_upload_jurisdiction_code,
1661 v1.tax_rate_code data_upload_tax_rate_code,
1662 v1.effective_from data_upload_effective_from,
1663 v1.active_flag data_upload_active_flag,
1664 zrb.tax_regime_code tax_regime_code,
1665 zrb.tax tax,
1666 zrb.tax_status_code tax_status_code,
1667 zrb.tax_jurisdiction_code tax_jurisdiction_code,
1668 zrb.effective_from effective_from,
1669 zrb.active_flag active_flag
1670 FROM (
1671 SELECT v.rowid as row_id,
1672 v.record_type,
1673 v.city_jurisdiction_code,
1674 v.county_jurisdiction_code,
1675 v.state_jurisdiction_code,
1676 v.tax,
1677 v.content_owner_id,
1678 DECODE(p_tax_content_source,
1679 'TAXWARE',DECODE(v.tax,
1680 'STATE',DECODE(to_char(jur.record_type),'1','ST-'||v.COUNTRY_STATE_ABBREVIATION,
1681 '3','ST-CO-'||v.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,21)),
1682 '6','ST-CI-'||v.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||v.city_jurisdiction_code),
1683 'COUNTY',decode(to_char(jur.record_type),
1684 '3','CO-'||v.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,21)),
1685 '6','CO-CI-'||v.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||v.city_jurisdiction_code),
1686 'CITY','CI-'||v.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||v.city_jurisdiction_code),
1687 'VERTEX',DECODE(v.tax,
1688 'STATE',decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
1689 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),
1690 'CITY','CI-')||v.state_jurisdiction_code||nvl(v.county_jurisdiction_code,'000')||lpad(nvl(v.city_jurisdiction_code,'0000'),4,'0'),
1691 'OTHERS',DECODE(v.tax,
1692 'STATE',decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
1693 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),
1694 'CITY','CI-')||v.state_jurisdiction_code||nvl(v.county_jurisdiction_code,'000')||lpad(nvl(v.city_jurisdiction_code,'0000'),4,'0'),
1695 DECODE(v.tax,
1696 'STATE',decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
1697 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),
1698 'CITY','CI-')||v.state_jurisdiction_code||nvl(v.county_jurisdiction_code,'000')||TRIM(SUBSTR(nvl(v.city_jurisdiction_code,'0000'),1,19))
1699 ) tax_jurisdiction_code,
1700 v.tax_rate_code,
1701 v.effective_from,
1702 v.active_flag
1703 FROM
1704 (SELECT x.row_id,
1705 x.record_type,
1706 x.country_state_abbreviation,
1707 x.city_jurisdiction_code,
1708 x.county_jurisdiction_code,
1709 x.state_jurisdiction_code,
1710 x.tax,
1711 x.content_owner_id,
1712 x.tax_rate_code,
1713 x.effective_from,
1714 x.active_flag
1715 FROM
1716 (SELECT rowid row_id,
1717 record_type,
1718 country_state_abbreviation,
1719 city_jurisdiction_code,
1720 county_jurisdiction_code,
1721 state_jurisdiction_code,
1722 decode(to_char(record_type),'9',sales_tax_authority_level,'10',rental_tax_authority_level,'11',use_tax_authority_level,'12',lease_tax_authority_level) tax,
1723 -99 content_owner_id,
1724 decode(p_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(record_type),'10','-RENTAL','11','-USE','12','-LEASE') tax_rate_code,
1725 effective_from,
1726 decode(to_char(record_type),'9',sales_tax_rate_active_flag,'10',rental_tax_rate_active_flag,'11',use_tax_rate_active_flag,'12',lease_tax_rate_active_flag) active_flag
1727 FROM zx_data_upload_interface
1728 WHERE record_type in (9,10,11,12)
1729 AND NVL(status,'CREATE') <> 'ERROR'
1730 AND last_updation_version > p_last_run_version
1731 ) x
1732 ) v,
1733 zx_data_upload_interface jur
1734 WHERE jur.record_type = DECODE(v.city_jurisdiction_code,NULL,DECODE(v.county_jurisdiction_code,NULL,1,3),6)
1735 AND jur.state_jurisdiction_code = v.state_jurisdiction_code
1736 AND NVL(jur.county_jurisdiction_code,'-1') = NVL(v.county_jurisdiction_code,'-1')
1737 AND NVL(jur.city_jurisdiction_code,'-1') = NVL(v.city_jurisdiction_code,'-1')
1738 AND NVL(jur.primary_flag,'Y') = 'Y'
1739 AND jur.effective_to IS NULL) v1,
1740 ZX_RATES_B zrb
1741 WHERE zrb.content_owner_id = v1.content_owner_id
1742 AND zrb.tax_jurisdiction_code = v1.tax_jurisdiction_code
1743 AND zrb.tax_rate_code = v1.tax_rate_code
1744 AND zrb.active_flag = v1.active_flag
1745 AND zrb.effective_from = v1.effective_from
1746 AND zrb.tax_class IS NULL
1747 AND zrb.recovery_type_code IS NULL
1748 AND zrb.tax_regime_code <> p_tax_regime_code;
1749
1750 -- Cursor to find duplicates in the data-file
1751 -- Added for Bug#7527399
1752 CURSOR c_file_dup IS
1753 SELECT v2.row_id,
1754 v2.record_type,
1755 v2.country_code,
1756 v2.state_jurisdiction_code,
1757 v2.county_jurisdiction_code,
1758 v2.city_jurisdiction_code,
1759 v2.tax,
1760 v2.effective_from,
1761 v2.active_flag,
1762 v2.rec_cnt
1763 FROM ( SELECT v.row_id,
1764 v.record_type,
1765 v.country_code,
1766 v.state_jurisdiction_code,
1767 v.county_jurisdiction_code,
1768 v.city_jurisdiction_code,
1769 v.tax,
1770 v.effective_from,
1771 v.active_flag,
1772 Count(v.row_id) OVER(PARTITION BY v.record_type,
1773 v.state_jurisdiction_code,
1774 v.county_jurisdiction_code,
1775 v.city_jurisdiction_code,
1776 v.tax,
1777 v.effective_from,
1778 v.active_flag
1779 ORDER BY v.effective_from ASC) AS rec_cnt
1780 FROM ( SELECT ROWID row_id,
1781 record_type,
1782 country_code,
1783 state_jurisdiction_code,
1784 county_jurisdiction_code,
1785 city_jurisdiction_code,
1786 DECODE(to_char(record_type),'9',sales_tax_authority_level,'10',rental_tax_authority_level,'11',use_tax_authority_level,'12',lease_tax_authority_level) tax,
1787 effective_from,
1788 DECODE(TO_CHAR(record_type),'9',sales_tax_rate_active_flag,'10',rental_tax_rate_active_flag,'11',use_tax_rate_active_flag,'12',lease_tax_rate_active_flag) active_flag
1789 FROM zx_data_upload_interface
1790 WHERE record_type in (9,10,11,12)
1791 AND NVL(status,'CREATE') <> 'ERROR' ) v,
1792 zx_data_upload_interface v1
1793 WHERE v1.record_type = DECODE(v.city_jurisdiction_code,NULL,DECODE(v.county_jurisdiction_code,NULL,1,3),6)
1794 AND v1.state_jurisdiction_code = v.state_jurisdiction_code
1795 AND NVL(v1.county_jurisdiction_code,'-1') = NVL(v.county_jurisdiction_code,'-1')
1796 AND NVL(v1.city_jurisdiction_code,'-1') = NVL(v.city_jurisdiction_code,'-1')
1797 AND NVL(v1.primary_flag,'Y') = 'Y'
1798 AND v1.effective_to IS NULL ) v2
1799 WHERE v2.rec_cnt > 1;
1800
1801 TYPE l_number_tbl_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1802
1803 l_previous_concat_code VARCHAR2(60);
1804 l_previous_zip_begin l_number_tbl_typ;
1805 l_previous_zip_end l_number_tbl_typ;
1806 l_count NUMBER;
1807 l_msg VARCHAR2(240);
1808 l_dup_rec_count NUMBER; -- Added for Bug#7527399
1809
1810 -- Start : Added for Bug#7298430
1811 TYPE l_date_tbl_typ IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1812 TYPE l_rowid_tbl_typ IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1813 TYPE l_varchar_1_tbl_typ IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
1814 TYPE l_varchar_60_tbl_typ IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
1815 TYPE l_varchar_240_tbl_typ IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
1816
1817 TYPE l_rates_rec_type IS RECORD
1818 (
1819 tax_rate_id l_number_tbl_typ,
1820 active_flag l_varchar_1_tbl_typ,
1821 effective_from l_date_tbl_typ,
1822 effective_to l_date_tbl_typ
1823 );
1824
1825 TYPE l_upload_rec_type IS RECORD
1826 (
1827 row_id l_rowid_tbl_typ,
1828 status l_varchar_60_tbl_typ,
1829 log_msg l_varchar_240_tbl_typ
1830 );
1831
1832 l_rates_count NUMBER;
1833 l_upload_count NUMBER;
1834 l_rates_rec l_rates_rec_type;
1835 l_upload_rec l_upload_rec_type;
1836 -- End : Added for Bug#7298430
1837
1838 BEGIN
1839
1840 FND_FILE.PUT_LINE
1841 (
1842 FND_FILE.OUTPUT,
1843 'Starting validation.'
1844 );
1845 -- Initializing count variables
1846 l_count := 0;
1847 l_rates_count := 1;
1848 l_upload_count := 1;
1849 l_dup_rec_count := 0; -- Added for Bug#7527399
1850 -- Check for zip overlap
1851 FOR ref_zip IN c_get_zip
1852 LOOP
1853 IF (ref_zip.concat_code <> NVL(l_previous_concat_code,' '))
1854 THEN
1855 l_previous_zip_begin.DELETE;
1856 l_previous_zip_end.DELETE;
1857 l_count := 1;
1858 l_previous_zip_begin(l_count) := ref_zip.zip_begin;
1859 l_previous_zip_end(l_count) := ref_zip.zip_end;
1860 ELSE
1861 FOR i IN 1..l_count
1862 LOOP
1863 IF ((ref_zip.zip_begin BETWEEN l_previous_zip_begin(i) AND l_previous_zip_end(i)) OR
1864 (l_previous_zip_begin(i) BETWEEN ref_zip.zip_begin AND ref_zip.zip_end))
1865 THEN
1866 l_msg := 'Overlapping zip range exists for the state: '||ref_zip.state_jurisdiction_code||', county: '||ref_zip.county_jurisdiction_code||', city : '||ref_zip.city_jurisdiction_code||'.';
1867 /* Commented for Bug#7298430
1868 UPDATE zx_data_upload_interface
1869 SET STATUS = 'ERROR',
1870 ERROR_MESSAGE = l_msg
1871 WHERE ROWID = ref_zip.row_id;
1872 FND_FILE.PUT_LINE
1873 (
1874 FND_FILE.OUTPUT,
1875 l_msg
1876 );*/
1877 l_upload_rec.log_msg(l_upload_count) := l_msg;
1878 l_upload_rec.row_id(l_upload_count) := ref_zip.row_id;
1879 l_upload_rec.status(l_upload_count) := 'ERROR';
1880
1881 l_upload_count := l_upload_count + 1;
1882 END IF;
1883 END LOOP;
1884 END IF;
1885 l_previous_zip_begin(l_count) := ref_zip.zip_begin;
1886 l_previous_zip_end(l_count) := ref_zip.zip_end;
1887 l_previous_concat_code := ref_zip.concat_code;
1888 END LOOP;
1889
1890 -- Check for rates overlap
1891 FOR ref_rates IN c_get_rates
1892 LOOP
1893 -- Rates date not in tax zone date
1894 /*IF (ref_rates.new_effective_from < ref_rates.jur_effective_from)
1895 THEN
1896 l_msg := 'The '||ref_rates.tax;
1897 IF (ref_rates.record_type = 9)
1898 THEN
1899 l_msg := l_msg||' Sales';
1900 ELSIF (ref_rates.record_type = 10)
1901 THEN
1902 l_msg := l_msg||' Rental';
1903 ELSIF (ref_rates.record_type = 11)
1904 THEN
1905 l_msg := l_msg||' Use';
1906 ELSIF (ref_rates.record_type = 12)
1907 THEN
1908 l_msg := l_msg||' Lease';
1909 END IF;
1910 l_msg := l_msg||' tax rate record for state: '||ref_rates.state_jurisdiction_code;
1911 IF (ref_rates.county_jurisdiction_code IS NOT NULL)
1912 THEN
1913 l_msg := l_msg||', county: '||ref_rates.county_jurisdiction_code;
1914 END IF;
1915 IF (ref_rates.city_jurisdiction_code IS NOT NULL)
1916 THEN
1917 l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
1918 END IF;
1919 l_msg := l_msg||' has effective date of '||to_char(ref_rates.new_effective_from,'MM/DD/YYYY')||', which is earlier than the tax zone''s effective date '||to_char(ref_rates.jur_effective_from,'MM/DD/YYYY')||'.';
1920 UPDATE zx_data_upload_interface
1921 SET STATUS = 'ERROR',
1922 ERROR_MESSAGE = l_msg
1923 WHERE ROWID = ref_rates.row_id;
1924 FND_FILE.PUT_LINE
1925 (
1926 FND_FILE.OUTPUT,
1927 l_msg
1928 );
1929 END IF; */
1930
1931 -- Overlap
1932 IF ((ref_rates.new_effective_from <> ref_rates.old_effective_from) AND
1933 (ref_rates.new_active_flag = 'Y') AND (ref_rates.old_active_flag = 'Y') AND
1934 ((ref_rates.new_effective_from BETWEEN ref_rates.old_effective_from AND NVL(ref_rates.old_effective_to,TO_DATE('12/31/4712','MM/DD/YYYY'))) OR
1935 (ref_rates.old_effective_from BETWEEN ref_rates.new_effective_from AND NVL(ref_rates.new_effective_to,TO_DATE('12/31/4712','MM/DD/YYYY')))))
1936 THEN
1937 -- Start : Added for Bug#7298430
1938 IF ref_rates.record_type_code = 'MIGRATED' THEN
1939 IF ref_rates.new_effective_from BETWEEN ref_rates.old_effective_from AND NVL(ref_rates.old_effective_to,TO_DATE('12/31/4712','MM/DD/YYYY'))
1940 THEN
1941 -- End date Old rate i.e. Update the effective_to of Old Rate to (new_effective_from - 1)
1942 l_rates_rec.tax_rate_id(l_rates_count) := ref_rates.tax_rate_id;
1943 l_rates_rec.effective_from(l_rates_count) := ref_rates.old_effective_from;
1944 l_rates_rec.effective_to(l_rates_count) := ref_rates.new_effective_from - 1;
1945 l_rates_rec.active_flag(l_rates_count) := ref_rates.old_active_flag;
1946 l_msg := 'An Upgraded '||ref_rates.tax;
1947 IF (ref_rates.record_type = 9)
1948 THEN
1949 l_msg := l_msg||' Sales';
1950 ELSIF (ref_rates.record_type = 10)
1951 THEN
1952 l_msg := l_msg||' Rental';
1953 ELSIF (ref_rates.record_type = 11)
1954 THEN
1955 l_msg := l_msg||' Use';
1956 ELSIF (ref_rates.record_type = 12)
1957 THEN
1958 l_msg := l_msg||' Lease';
1959 END IF;
1960 l_msg := l_msg||' tax rate for the state: '||ref_rates.state_jurisdiction_code;
1961 IF (ref_rates.county_jurisdiction_code IS NOT NULL)
1962 THEN
1963 l_msg := l_msg||', county: '||ref_rates.county_jurisdiction_code;
1964 END IF;
1965 IF (ref_rates.city_jurisdiction_code IS NOT NULL)
1966 THEN
1967 l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
1968 END IF;
1969 l_msg := l_msg||' has been End-Dated to '||TO_CHAR(ref_rates.new_effective_from - 1,'DD-MON-YYYY')||'.';
1970 l_upload_rec.log_msg(l_upload_count) := l_msg;
1971 l_upload_rec.row_id(l_upload_count) := ref_rates.row_id;
1972 l_upload_rec.status(l_upload_count) := '';
1973
1974 l_rates_count := l_rates_count + 1;
1975 l_upload_count := l_upload_count + 1;
1976
1977 ELSIF ref_rates.old_effective_from BETWEEN ref_rates.new_effective_from AND NVL(ref_rates.new_effective_to,TO_DATE('12/31/4712','MM/DD/YYYY'))
1978 THEN
1979 -- Update the effective_from of Old rate to new_effective_from
1980 l_rates_rec.tax_rate_id(l_rates_count) := ref_rates.tax_rate_id;
1981 l_rates_rec.effective_from(l_rates_count) := ref_rates.old_effective_from;
1982 l_rates_rec.effective_to(l_rates_count) := ref_rates.old_effective_to;
1983 l_rates_rec.active_flag(l_rates_count) := 'N';
1984 l_msg := 'An Upgraded '||ref_rates.tax;
1985 IF (ref_rates.record_type = 9)
1986 THEN
1987 l_msg := l_msg||' Sales';
1988 ELSIF (ref_rates.record_type = 10)
1989 THEN
1990 l_msg := l_msg||' Rental';
1991 ELSIF (ref_rates.record_type = 11)
1992 THEN
1993 l_msg := l_msg||' Use';
1994 ELSIF (ref_rates.record_type = 12)
1995 THEN
1996 l_msg := l_msg||' Lease';
1997 END IF;
1998 l_msg := l_msg||' tax rate for the state: '||ref_rates.state_jurisdiction_code;
1999 IF (ref_rates.county_jurisdiction_code IS NOT NULL)
2000 THEN
2001 l_msg := l_msg||', county: '||ref_rates.county_jurisdiction_code;
2002 END IF;
2003 IF (ref_rates.city_jurisdiction_code IS NOT NULL)
2004 THEN
2005 l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
2006 END IF;
2007 l_msg := l_msg||' with Effective-From '||TO_CHAR(ref_rates.old_effective_from,'DD-MON-YYYY');
2008 l_msg := l_msg||' has been disabled.';
2009 l_upload_rec.log_msg(l_upload_count) := l_msg;
2010 l_upload_rec.row_id(l_upload_count) := ref_rates.row_id;
2011 l_upload_rec.status(l_upload_count) := '';
2012
2013 l_rates_count := l_rates_count + 1;
2014 l_upload_count := l_upload_count + 1;
2015 END IF;
2016 -- End : Added for Bug#7298430
2017 ELSE
2018 l_msg := 'An active '||ref_rates.tax;
2019 IF (ref_rates.record_type = 9)
2020 THEN
2021 l_msg := l_msg||' Sales';
2022 ELSIF (ref_rates.record_type = 10)
2023 THEN
2024 l_msg := l_msg||' Rental';
2025 ELSIF (ref_rates.record_type = 11)
2026 THEN
2027 l_msg := l_msg||' Use';
2028 ELSIF (ref_rates.record_type = 12)
2029 THEN
2030 l_msg := l_msg||' Lease';
2031 END IF;
2032 l_msg := l_msg||' tax rate already exists for the state: '||ref_rates.state_jurisdiction_code;
2033 IF (ref_rates.county_jurisdiction_code IS NOT NULL)
2034 THEN
2035 l_msg := l_msg||', county: '||ref_rates.county_jurisdiction_code;
2036 END IF;
2037 IF (ref_rates.city_jurisdiction_code IS NOT NULL)
2038 THEN
2039 l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
2040 END IF;
2041 l_msg := l_msg||'.';
2042 /* -- Commented for Bug#7298430
2043 UPDATE zx_data_upload_interface
2044 SET STATUS = 'ERROR',
2045 ERROR_MESSAGE = l_msg
2046 WHERE ROWID = ref_rates.row_id;
2047 FND_FILE.PUT_LINE
2048 (
2049 FND_FILE.OUTPUT,
2050 l_msg
2051 );*/
2052 l_upload_rec.log_msg(l_upload_count) := l_msg;
2053 l_upload_rec.row_id(l_upload_count) := ref_rates.row_id;
2054 l_upload_rec.status(l_upload_count) := 'ERROR';
2055
2056 l_upload_count := l_upload_count + 1;
2057 END IF;
2058 END IF;
2059
2060 -- End dated by user
2061 IF ((ref_rates.new_effective_from = ref_rates.old_effective_from) AND
2062 (ref_rates.new_active_flag = 'N') AND (ref_rates.old_active_flag = 'N'))
2063 THEN
2064 l_msg := 'The '||ref_rates.tax;
2065 IF (ref_rates.record_type = 9)
2066 THEN
2067 l_msg := l_msg||' Sales';
2068 ELSIF (ref_rates.record_type = 10)
2069 THEN
2070 l_msg := l_msg||' Rental';
2071 ELSIF (ref_rates.record_type = 11)
2072 THEN
2073 l_msg := l_msg||' Use';
2074 ELSIF (ref_rates.record_type = 12)
2075 THEN
2076 l_msg := l_msg||' Lease';
2077 END IF;
2078 l_msg := l_msg||' tax rate record for state: '||ref_rates.state_jurisdiction_code;
2079 IF (ref_rates.county_jurisdiction_code IS NOT NULL)
2080 THEN
2081 l_msg := l_msg||', county: '||ref_rates.county_jurisdiction_code;
2082 END IF;
2083 IF (ref_rates.city_jurisdiction_code IS NOT NULL)
2084 THEN
2085 l_msg := l_msg||', city: '||ref_rates.city_jurisdiction_code;
2089 UPDATE zx_data_upload_interface
2086 END IF;
2087 l_msg := l_msg||' has already been ended by user.';
2088 /* -- Commented for Bug#7298430
2090 SET STATUS = 'ERROR',
2091 ERROR_MESSAGE = l_msg
2092 WHERE ROWID = ref_rates.row_id;
2093 FND_FILE.PUT_LINE
2094 (
2095 FND_FILE.OUTPUT,
2096 l_msg
2097 );*/
2098 l_upload_rec.log_msg(l_upload_count) := l_msg;
2099 l_upload_rec.row_id(l_upload_count) := ref_rates.row_id;
2100 l_upload_rec.status(l_upload_count) := 'ERROR';
2101
2102 l_upload_count := l_upload_count + 1;
2103 END IF;
2104 END LOOP;
2105
2106 -- Start : Added for Bug#7298430
2107 FORALL i IN INDICES OF l_rates_rec.tax_rate_id
2108 UPDATE zx_rates_b_tmp
2109 SET active_flag = l_rates_rec.active_flag(i),
2110 effective_from = l_rates_rec.effective_from(i),
2111 effective_to = l_rates_rec.effective_to(i),
2112 default_flg_effective_from = l_rates_rec.effective_from(i),
2113 default_flg_effective_to = l_rates_rec.effective_to(i)
2114 WHERE tax_rate_id = l_rates_rec.tax_rate_id(i);
2115 -- End : Added for Bug#7298430
2116
2117 -- Start : Added for Bug#7527399
2118 -- Added to check the duplicates in the existing set-up
2119 FOR dup_rates_rec IN c_get_dup_rates
2120 LOOP
2121 l_msg := 'Duplicate record for '||dup_rates_rec.data_upload_tax;
2122 IF (dup_rates_rec.record_type = 9)
2123 THEN
2124 l_msg := l_msg||' Sales';
2125 ELSIF (dup_rates_rec.record_type = 10)
2126 THEN
2127 l_msg := l_msg||' Rental';
2128 ELSIF (dup_rates_rec.record_type = 11)
2129 THEN
2130 l_msg := l_msg||' Use';
2131 ELSIF (dup_rates_rec.record_type = 12)
2132 THEN
2133 l_msg := l_msg||' Lease';
2134 END IF;
2135
2136 l_msg := l_msg||' tax rate exists for state: '||dup_rates_rec.state_jurisdiction_code;
2137 IF (dup_rates_rec.county_jurisdiction_code IS NOT NULL)
2138 THEN
2139 l_msg := l_msg||', county: '||dup_rates_rec.county_jurisdiction_code;
2140 END IF;
2141 IF (dup_rates_rec.city_jurisdiction_code IS NOT NULL)
2142 THEN
2143 l_msg := l_msg||', city: '||dup_rates_rec.city_jurisdiction_code;
2144 END IF;
2145
2146 l_msg := l_msg||' for Tax-Regime: '||dup_rates_rec.tax_regime_code;
2147 l_msg := l_msg||'. Please check your Tax Set-up.';
2148
2149 l_upload_rec.log_msg(l_upload_count) := l_msg;
2150 l_upload_rec.row_id(l_upload_count) := dup_rates_rec.row_id;
2151 l_upload_rec.status(l_upload_count) := 'ERROR';
2152 l_upload_count := l_upload_count + 1;
2153 l_dup_rec_count := l_dup_rec_count + 1;
2154 END LOOP;
2155
2156 IF l_dup_rec_count = 0 THEN
2157 -- Added to check the duplicates in the data-file
2158 FOR dup_file_rec IN c_file_dup
2159 LOOP
2160 l_msg := 'Duplicate record for '||dup_file_rec.tax;
2161 IF (dup_file_rec.record_type = 9)
2162 THEN
2163 l_msg := l_msg||' Sales';
2164 ELSIF (dup_file_rec.record_type = 10)
2165 THEN
2166 l_msg := l_msg||' Rental';
2167 ELSIF (dup_file_rec.record_type = 11)
2168 THEN
2169 l_msg := l_msg||' Use';
2170 ELSIF (dup_file_rec.record_type = 12)
2171 THEN
2172 l_msg := l_msg||' Lease';
2173 END IF;
2174
2175 l_msg := l_msg||' tax rate exists for state: '||dup_file_rec.state_jurisdiction_code;
2176 IF (dup_file_rec.county_jurisdiction_code IS NOT NULL)
2177 THEN
2178 l_msg := l_msg||', county: '||dup_file_rec.county_jurisdiction_code;
2179 END IF;
2180 IF (dup_file_rec.city_jurisdiction_code IS NOT NULL)
2181 THEN
2182 l_msg := l_msg||', city: '||dup_file_rec.city_jurisdiction_code;
2183 END IF;
2184
2185 l_msg := l_msg||' in the Data-File. Please contact your Tax Service Provider.';
2186
2187 l_upload_rec.log_msg(l_upload_count) := l_msg;
2188 l_upload_rec.row_id(l_upload_count) := dup_file_rec.row_id;
2189 l_upload_rec.status(l_upload_count) := 'ERROR';
2190 l_upload_count := l_upload_count + 1;
2191 l_dup_rec_count := l_dup_rec_count + 1;
2192 END LOOP;
2193 END IF;
2194 -- End : Added for Bug#7527399
2195
2196 -- Start : Added for Bug#7298430
2197 FORALL i IN INDICES OF l_upload_rec.row_id
2198 UPDATE zx_data_upload_interface
2199 SET STATUS = l_upload_rec.status(i),
2200 ERROR_MESSAGE = l_upload_rec.log_msg(i)
2201 WHERE ROWID = l_upload_rec.row_id(i);
2202
2203 FOR i IN NVL(l_upload_rec.log_msg.FIRST,0)..NVL(l_upload_rec.log_msg.LAST,-99)
2204 LOOP
2205 FND_FILE.PUT_LINE
2206 (
2207 FND_FILE.OUTPUT,
2208 l_upload_rec.log_msg(i)
2209 );
2210 END LOOP;
2211 -- End : Added for Bug#7298430
2212
2213 FND_FILE.PUT_LINE
2214 (
2215 FND_FILE.OUTPUT,
2216 'Validation Complete.'
2217 );
2218
2219 -- Added for Bug#7527399
2220 -- If there are any duplicates, then stop the Data Upload program and raise error.
2221 IF l_dup_rec_count > 0 THEN
2222 RAISE_APPLICATION_ERROR(-20001,'E-Business Tax cannot upload the file as duplicate records are present. Please see the output file for details.');
2223 END IF;
2224
2228 -- Procedure to create master reference geography
2225 END DO_ERROR_CHECK;
2226
2227 --
2229 --
2230 PROCEDURE CREATE_GEOGRAPHY
2231 (
2232 errbuf OUT NOCOPY VARCHAR2,
2233 retcode OUT NOCOPY VARCHAR2,
2234 p_batch_size IN NUMBER,
2235 p_worker_id IN NUMBER,
2236 p_num_workers IN NUMBER,
2237 p_tax_content_source IN VARCHAR2,
2238 p_last_run_version IN NUMBER
2239 ) IS
2240
2241 l_api_name CONSTANT VARCHAR2(30):= 'create_geography';
2242
2243 -----------------------------------------------------
2244 -- Ad parallelization variables
2245 -----------------------------------------------------
2246 l_table_owner VARCHAR2(30) := 'ZX';
2247 l_any_rows_to_process BOOLEAN;
2248 l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
2249 l_start_rowid ROWID;
2250 l_end_rowid ROWID;
2251 l_rows_processed NUMBER;
2252
2253
2254 BEGIN
2255
2256 retcode := '0';
2257
2258 /*-- Initialize the rowid ranges
2259 --
2260 ad_parallel_updates_pkg.initialize_rowid_range(
2261 ad_parallel_updates_pkg.ROWID_RANGE,
2262 l_table_owner,
2263 l_table_name,
2264 l_api_name,
2265 p_Worker_Id,
2266 p_Num_Workers,
2267 p_batch_size,
2268 0);
2269 --
2270 -- Get rowid ranges
2271 --
2272 ad_parallel_updates_pkg.get_rowid_range(
2273 l_start_rowid,
2274 l_end_rowid,
2275 l_any_rows_to_process,
2276 p_batch_size,
2277 TRUE);
2278
2279 WHILE (l_any_rows_to_process)
2280 LOOP*/
2281
2282 FND_FILE.PUT_LINE
2283 (
2284 FND_FILE.LOG,
2285 'Creating Geographies.'
2286 );
2287
2288 INSERT ALL
2289 WHEN (action_type = 'CREATE' AND existing_geography_id IS NULL AND geography_id IS NOT NULL AND geography_type IS NOT NULL) THEN
2290 INTO HZ_GEOGRAPHIES
2291 (
2292 GEOGRAPHY_ID,
2293 OBJECT_VERSION_NUMBER,
2294 GEOGRAPHY_TYPE,
2295 GEOGRAPHY_NAME,
2296 GEOGRAPHY_USE,
2297 GEOGRAPHY_CODE,
2298 START_DATE,
2299 END_DATE,
2300 MULTIPLE_PARENT_FLAG,
2301 geography_element1,
2302 geography_element1_id,
2303 geography_element1_code,
2304 geography_element2,
2305 geography_element2_id,
2306 geography_element2_code,
2307 geography_element3,
2308 geography_element3_id,
2309 geography_element4,
2310 geography_element4_id,
2311 geography_element4_code,
2312 CREATED_BY_MODULE,
2313 COUNTRY_CODE,
2314 TIMEZONE_CODE,
2315 LAST_UPDATED_BY,
2316 CREATION_DATE,
2317 CREATED_BY,
2318 LAST_UPDATE_DATE,
2319 LAST_UPDATE_LOGIN
2320 )
2321 VALUES
2322 (
2323 geography_id,
2324 1,
2325 geography_type,
2326 geography_name,
2327 'MASTER_REF',
2328 geography_code,
2329 start_date,
2330 end_date,
2331 'N',
2332 geography_element1,
2333 geography_element1_id,
2334 geography_element1_code,
2335 geography_element2,
2336 geography_element2_id,
2337 geography_element2_code,
2338 geography_element3,
2339 geography_element3_id,
2340 geography_element4,
2341 geography_element4_id,
2342 geography_element4_code,
2343 G_CREATED_BY_MODULE,
2344 country_code,
2345 'PST',
2346 fnd_global.user_id,
2347 sysdate,
2348 fnd_global.user_id,
2349 sysdate,
2350 fnd_global.conc_login_id
2351 )
2352 INTO HZ_GEOGRAPHY_IDENTIFIERS
2353 (
2354 GEOGRAPHY_ID,
2355 GEO_DATA_PROVIDER,
2356 IDENTIFIER_SUBTYPE,
2357 IDENTIFIER_VALUE,
2358 OBJECT_VERSION_NUMBER,
2359 IDENTIFIER_TYPE,
2360 PRIMARY_FLAG,
2361 LANGUAGE_CODE,
2362 GEOGRAPHY_USE,
2363 GEOGRAPHY_TYPE,
2364 CREATED_BY_MODULE,
2365 LAST_UPDATED_BY,
2366 CREATION_DATE,
2367 CREATED_BY,
2368 LAST_UPDATE_DATE,
2369 LAST_UPDATE_LOGIN
2370 )
2371 VALUES
2372 (
2373 geography_id,
2374 p_tax_content_source,
2375 'STANDARD_NAME',
2376 geography_name,
2377 1,
2378 'NAME',
2379 'Y',
2380 'US',
2381 'MASTER_REF',
2382 geography_type,
2383 G_CREATED_BY_MODULE,
2384 fnd_global.user_id,
2385 sysdate,
2386 fnd_global.user_id,
2387 sysdate,
2388 fnd_global.conc_login_id
2389 )
2393 HIERARCHY_TYPE,
2390 --Self
2391 INTO hz_hierarchy_nodes
2392 (
2394 PARENT_ID,
2395 PARENT_TABLE_NAME,
2396 PARENT_OBJECT_TYPE,
2397 CHILD_ID,
2398 CHILD_TABLE_NAME,
2399 CHILD_OBJECT_TYPE,
2400 LEVEL_NUMBER,
2401 TOP_PARENT_FLAG,
2402 LEAF_CHILD_FLAG,
2403 EFFECTIVE_START_DATE,
2404 EFFECTIVE_END_DATE,
2405 STATUS,
2406 RELATIONSHIP_ID,
2407 CREATED_BY,
2408 CREATION_DATE,
2409 LAST_UPDATED_BY,
2410 LAST_UPDATE_DATE,
2411 LAST_UPDATE_LOGIN,
2412 ACTUAL_CONTENT_SOURCE
2413 )
2414 VALUES
2415 (
2416 'MASTER_REF',
2417 geography_id,
2418 'HZ_GEOGRAPHIES',
2419 geography_type,
2420 geography_id,
2421 'HZ_GEOGRAPHIES',
2422 geography_type,
2423 0 ,
2424 'N',
2425 'Y',
2426 start_date,
2427 end_date,
2428 'A',
2429 null,
2430 fnd_global.user_id,
2431 sysdate,
2432 fnd_global.user_id,
2433 sysdate,
2434 fnd_global.conc_login_id,
2435 p_tax_content_source
2436 )
2437 WHEN (action_type = 'UPDATE' AND geography_type = 'STATE' AND geography_name1 IS NOT NULL) THEN
2438 INTO HZ_GEOGRAPHY_IDENTIFIERS
2439 (
2440 GEOGRAPHY_ID,
2441 GEO_DATA_PROVIDER,
2442 IDENTIFIER_SUBTYPE,
2443 IDENTIFIER_VALUE,
2444 OBJECT_VERSION_NUMBER,
2445 IDENTIFIER_TYPE,
2446 PRIMARY_FLAG,
2447 LANGUAGE_CODE,
2448 GEOGRAPHY_USE,
2449 GEOGRAPHY_TYPE,
2450 CREATED_BY_MODULE,
2451 LAST_UPDATED_BY,
2452 CREATION_DATE,
2453 CREATED_BY,
2454 LAST_UPDATE_DATE,
2455 LAST_UPDATE_LOGIN
2456 )
2457 VALUES
2458 (
2459 geography_id,
2460 p_tax_content_source,
2461 'STANDARD_NAME',
2462 geography_name1,
2463 1,
2464 'NAME',
2465 'N',
2466 'US',
2467 'MASTER_REF',
2468 geography_type,
2469 G_CREATED_BY_MODULE,
2470 fnd_global.user_id,
2471 sysdate,
2472 fnd_global.user_id,
2473 sysdate,
2474 fnd_global.conc_login_id
2475 )
2476 WHEN (action_type = 'UPDATE' AND geography_type IN ('COUNTY','CITY') AND geography_name IS NOT NULL) THEN
2477 INTO HZ_GEOGRAPHY_IDENTIFIERS
2478 (
2479 GEOGRAPHY_ID,
2480 GEO_DATA_PROVIDER,
2481 IDENTIFIER_SUBTYPE,
2482 IDENTIFIER_VALUE,
2483 OBJECT_VERSION_NUMBER,
2484 IDENTIFIER_TYPE,
2485 PRIMARY_FLAG,
2486 LANGUAGE_CODE,
2487 GEOGRAPHY_USE,
2488 GEOGRAPHY_TYPE,
2489 CREATED_BY_MODULE,
2490 LAST_UPDATED_BY,
2491 CREATION_DATE,
2492 CREATED_BY,
2493 LAST_UPDATE_DATE,
2494 LAST_UPDATE_LOGIN
2495 )
2496 VALUES
2497 (
2498 geography_id,
2499 p_tax_content_source,
2500 'STANDARD_NAME',
2501 geography_name,
2502 1,
2503 'NAME',
2504 'N',
2505 'US',
2506 'MASTER_REF',
2507 geography_type,
2508 G_CREATED_BY_MODULE,
2509 fnd_global.user_id,
2510 sysdate,
2511 fnd_global.user_id,
2512 sysdate,
2513 fnd_global.conc_login_id
2514 )
2515 WHEN (action_type = 'CREATE' AND geography_type = 'STATE' AND geography_name1 IS NOT NULL) THEN
2516 INTO HZ_GEOGRAPHY_IDENTIFIERS
2517 (
2518 GEOGRAPHY_ID,
2519 GEO_DATA_PROVIDER,
2520 IDENTIFIER_SUBTYPE,
2521 IDENTIFIER_VALUE,
2522 OBJECT_VERSION_NUMBER,
2523 IDENTIFIER_TYPE,
2524 PRIMARY_FLAG,
2525 LANGUAGE_CODE,
2526 GEOGRAPHY_USE,
2527 GEOGRAPHY_TYPE,
2528 CREATED_BY_MODULE,
2529 LAST_UPDATED_BY,
2530 CREATION_DATE,
2531 CREATED_BY,
2532 LAST_UPDATE_DATE,
2533 LAST_UPDATE_LOGIN
2534 )
2535 VALUES
2536 (
2537 geography_id,
2538 p_tax_content_source,
2539 'FIPS_CODE',
2540 geography_name,
2541 1,
2542 'CODE',
2543 'Y',
2544 'US',
2545 'MASTER_REF',
2546 geography_type,
2547 G_CREATED_BY_MODULE,
2551 sysdate,
2548 fnd_global.user_id,
2549 sysdate,
2550 fnd_global.user_id,
2552 fnd_global.conc_login_id
2553 )
2554 INTO HZ_GEOGRAPHY_IDENTIFIERS
2555 (
2556 GEOGRAPHY_ID,
2557 GEO_DATA_PROVIDER,
2558 IDENTIFIER_SUBTYPE,
2559 IDENTIFIER_VALUE,
2560 OBJECT_VERSION_NUMBER,
2561 IDENTIFIER_TYPE,
2562 PRIMARY_FLAG,
2563 LANGUAGE_CODE,
2564 GEOGRAPHY_USE,
2565 GEOGRAPHY_TYPE,
2566 CREATED_BY_MODULE,
2567 LAST_UPDATED_BY,
2568 CREATION_DATE,
2569 CREATED_BY,
2570 LAST_UPDATE_DATE,
2571 LAST_UPDATE_LOGIN
2572 )
2573 VALUES
2574 (
2575 geography_id,
2576 p_tax_content_source,
2577 'STANDARD_NAME',
2578 geography_name1,
2579 1,
2580 'NAME',
2581 'N',
2582 'US',
2583 'MASTER_REF',
2584 geography_type,
2585 G_CREATED_BY_MODULE,
2586 fnd_global.user_id,
2587 sysdate,
2588 fnd_global.user_id,
2589 sysdate,
2590 fnd_global.conc_login_id
2591 )
2592 WHEN (action_type = 'CREATE') THEN
2593 INTO HZ_RELATIONSHIPS
2594 (
2595 RELATIONSHIP_ID,
2596 SUBJECT_ID,
2597 SUBJECT_TYPE,
2598 SUBJECT_TABLE_NAME,
2599 OBJECT_ID,
2600 OBJECT_TYPE,
2601 OBJECT_TABLE_NAME,
2602 RELATIONSHIP_CODE,
2603 DIRECTIONAL_FLAG,
2604 COMMENTS,
2605 START_DATE,
2606 END_DATE,
2607 STATUS,
2608 CREATED_BY,
2609 CREATION_DATE,
2610 LAST_UPDATED_BY,
2611 LAST_UPDATE_DATE,
2612 LAST_UPDATE_LOGIN,
2613 CONTENT_SOURCE_TYPE,
2614 RELATIONSHIP_TYPE,
2615 OBJECT_VERSION_NUMBER,
2616 CREATED_BY_MODULE,
2617 APPLICATION_ID,
2618 DIRECTION_CODE,
2619 PERCENTAGE_OWNERSHIP,
2620 ACTUAL_CONTENT_SOURCE
2621 )
2622 VALUES
2623 (
2624 hz_relationships_s.nextval,
2625 parent_geography_id,
2626 parent_geography_type,
2627 'HZ_GEOGRAPHIES',
2628 geography_id,
2629 geography_type,
2630 'HZ_GEOGRAPHIES',
2631 'PARENT_OF',
2632 'F',
2633 null,
2634 start_date,
2635 end_date,
2636 'A',
2637 fnd_global.user_id,
2638 sysdate,
2639 fnd_global.user_id,
2640 sysdate,
2641 fnd_global.conc_login_id,
2642 G_CREATED_BY_MODULE,
2643 'MASTER_REF',
2644 1,
2645 G_CREATED_BY_MODULE,
2646 null,
2647 'P',
2648 null,
2649 p_tax_content_source
2650 )
2651 INTO HZ_RELATIONSHIPS
2652 (
2653 RELATIONSHIP_ID,
2654 SUBJECT_ID,
2655 SUBJECT_TYPE,
2656 SUBJECT_TABLE_NAME,
2657 OBJECT_ID,
2658 OBJECT_TYPE,
2659 OBJECT_TABLE_NAME,
2660 RELATIONSHIP_CODE,
2661 DIRECTIONAL_FLAG,
2662 COMMENTS,
2663 START_DATE,
2664 END_DATE,
2665 STATUS,
2666 CREATED_BY,
2667 CREATION_DATE,
2668 LAST_UPDATED_BY,
2669 LAST_UPDATE_DATE,
2670 LAST_UPDATE_LOGIN,
2671 CONTENT_SOURCE_TYPE,
2672 RELATIONSHIP_TYPE,
2673 OBJECT_VERSION_NUMBER,
2674 CREATED_BY_MODULE,
2675 APPLICATION_ID,
2676 DIRECTION_CODE,
2677 PERCENTAGE_OWNERSHIP,
2678 ACTUAL_CONTENT_SOURCE
2679 )
2680 VALUES
2681 (
2682 hz_relationships_s.nextval,
2683 geography_id,
2684 geography_type,
2685 'HZ_GEOGRAPHIES',
2686 parent_geography_id,
2687 parent_geography_type,
2688 'HZ_GEOGRAPHIES',
2689 'CHILD_OF',
2690 'B',
2691 null,
2692 start_date,
2693 end_date,
2694 'A',
2695 fnd_global.user_id,
2696 sysdate,
2697 fnd_global.user_id,
2698 sysdate,
2699 fnd_global.conc_login_id,
2700 G_CREATED_BY_MODULE,
2701 'MASTER_REF',
2702 1,
2703 G_CREATED_BY_MODULE,
2704 null,
2705 'C',
2706 null,
2707 p_tax_content_source
2708 )
2709 --Immediate Parent
2710 INTO hz_hierarchy_nodes
2711 (
2712 HIERARCHY_TYPE,
2713 PARENT_ID,
2714 PARENT_TABLE_NAME,
2715 PARENT_OBJECT_TYPE,
2719 LEVEL_NUMBER,
2716 CHILD_ID,
2717 CHILD_TABLE_NAME,
2718 CHILD_OBJECT_TYPE,
2720 TOP_PARENT_FLAG,
2721 LEAF_CHILD_FLAG,
2722 EFFECTIVE_START_DATE,
2723 EFFECTIVE_END_DATE,
2724 STATUS,
2725 RELATIONSHIP_ID,
2726 CREATED_BY,
2727 CREATION_DATE,
2728 LAST_UPDATED_BY,
2729 LAST_UPDATE_DATE,
2730 LAST_UPDATE_LOGIN,
2731 ACTUAL_CONTENT_SOURCE
2732 )
2733 VALUES
2734 (
2735 'MASTER_REF',
2736 parent_geography_id,
2737 'HZ_GEOGRAPHIES',
2738 parent_geography_type,
2739 geography_id,
2740 'HZ_GEOGRAPHIES',
2741 geography_type,
2742 1,
2743 '',
2744 '',
2745 start_date,
2746 end_date,
2747 'A',
2748 hz_relationships_s.nextval,
2749 fnd_global.user_id,
2750 sysdate,
2751 fnd_global.user_id,
2752 sysdate,
2753 fnd_global.conc_login_id,
2754 p_tax_content_source
2755 )
2756 WHEN (action_type = 'CREATE' AND geography_type = 'COUNTY') THEN
2757 INTO hz_hierarchy_nodes
2758 (
2759 HIERARCHY_TYPE,
2760 PARENT_ID,
2761 PARENT_TABLE_NAME,
2762 PARENT_OBJECT_TYPE,
2763 CHILD_ID,
2764 CHILD_TABLE_NAME,
2765 CHILD_OBJECT_TYPE,
2766 LEVEL_NUMBER,
2767 TOP_PARENT_FLAG,
2768 LEAF_CHILD_FLAG,
2769 EFFECTIVE_START_DATE,
2770 EFFECTIVE_END_DATE,
2771 STATUS,
2772 RELATIONSHIP_ID,
2773 CREATED_BY,
2774 CREATION_DATE,
2775 LAST_UPDATED_BY,
2776 LAST_UPDATE_DATE,
2777 LAST_UPDATE_LOGIN,
2778 ACTUAL_CONTENT_SOURCE
2779 )
2780 VALUES
2781 (
2782 'MASTER_REF',
2783 geography_element1_id,
2784 'HZ_GEOGRAPHIES',
2785 geography_element1_type,
2786 geography_id,
2787 'HZ_GEOGRAPHIES',
2788 geography_type,
2789 2 ,
2790 '',
2791 '',
2792 start_date,
2793 end_date,
2794 'A',
2795 null ,
2796 fnd_global.user_id,
2797 sysdate,
2798 fnd_global.user_id,
2799 sysdate,
2800 fnd_global.conc_login_id,
2801 p_tax_content_source
2802 )
2803 WHEN (action_type = 'CREATE' AND geography_type = 'CITY') THEN
2804 INTO hz_hierarchy_nodes
2805 (
2806 HIERARCHY_TYPE,
2807 PARENT_ID,
2808 PARENT_TABLE_NAME,
2809 PARENT_OBJECT_TYPE,
2810 CHILD_ID,
2811 CHILD_TABLE_NAME,
2812 CHILD_OBJECT_TYPE,
2813 LEVEL_NUMBER,
2814 TOP_PARENT_FLAG,
2815 LEAF_CHILD_FLAG,
2816 EFFECTIVE_START_DATE,
2817 EFFECTIVE_END_DATE,
2818 STATUS,
2819 RELATIONSHIP_ID,
2820 CREATED_BY,
2821 CREATION_DATE,
2822 LAST_UPDATED_BY,
2823 LAST_UPDATE_DATE,
2824 LAST_UPDATE_LOGIN,
2825 ACTUAL_CONTENT_SOURCE
2826 )
2827 VALUES
2828 (
2829 'MASTER_REF',
2830 geography_element2_id,
2831 'HZ_GEOGRAPHIES',
2832 geography_element2_type,
2833 geography_id,
2834 'HZ_GEOGRAPHIES',
2835 geography_type,
2836 2 ,
2837 '',
2838 '',
2839 start_date,
2840 end_date,
2841 'A',
2842 null ,
2843 fnd_global.user_id,
2844 sysdate,
2845 fnd_global.user_id,
2846 sysdate,
2847 fnd_global.conc_login_id,
2848 p_tax_content_source
2849 )
2850 INTO hz_hierarchy_nodes
2851 (
2852 HIERARCHY_TYPE,
2853 PARENT_ID,
2854 PARENT_TABLE_NAME,
2855 PARENT_OBJECT_TYPE,
2856 CHILD_ID,
2857 CHILD_TABLE_NAME,
2858 CHILD_OBJECT_TYPE,
2859 LEVEL_NUMBER,
2860 TOP_PARENT_FLAG,
2861 LEAF_CHILD_FLAG,
2862 EFFECTIVE_START_DATE,
2863 EFFECTIVE_END_DATE,
2864 STATUS,
2865 RELATIONSHIP_ID,
2866 CREATED_BY,
2867 CREATION_DATE,
2868 LAST_UPDATED_BY,
2869 LAST_UPDATE_DATE,
2870 LAST_UPDATE_LOGIN,
2871 ACTUAL_CONTENT_SOURCE
2872 )
2873 VALUES
2874 (
2875 'MASTER_REF',
2876 geography_element1_id,
2877 'HZ_GEOGRAPHIES',
2878 geography_element1_type,
2879 geography_id,
2880 'HZ_GEOGRAPHIES',
2884 '',
2881 geography_type,
2882 3 ,
2883 '',
2885 start_date,
2886 end_date,
2887 'A',
2888 null ,
2889 fnd_global.user_id,
2890 sysdate,
2891 fnd_global.user_id,
2892 sysdate,
2893 fnd_global.conc_login_id,
2894 p_tax_content_source
2895 )
2896 SELECT geography_id,
2897 geography_name,
2898 geography_code,
2899 geography_type,
2900 parent_geography_id,
2901 parent_geography_name,
2902 parent_geography_type,
2903 geography_element1_id,
2904 geography_element1,
2905 geography_element1_code,
2906 geography_element1_type,
2907 geography_element2_id,
2908 geography_element2,
2909 geography_element2_code,
2910 geography_element2_type,
2911 geography_element3_id,
2912 geography_element3,
2913 geography_element3_code,
2914 geography_element3_type,
2915 geography_element4_id,
2916 geography_element4,
2917 geography_element4_code,
2918 geography_element4_type,
2919 geography_name1,
2920 multiple_parent_flag,
2921 start_date,
2922 end_date,
2923 country_code,
2924 CASE WHEN status = 'CREATE'
2925 THEN 'CREATE'
2926 WHEN status = 'UPDATE' AND
2927 'EXISTS' = (SELECT 'EXISTS'
2928 FROM HZ_GEOGRAPHY_IDENTIFIERS hgi
2929 WHERE hgi.geography_id = v.geography_id
2930 AND hgi.identifier_type = 'NAME'
2931 AND hgi.identifier_subtype = 'STANDARD_NAME'
2932 AND UPPER(hgi.identifier_value) = UPPER(DECODE(geography_type,'STATE',geography_name1,geography_name)))
2933 THEN 'NOCHANGE'
2934 WHEN status = 'UPDATE'
2935 THEN 'UPDATE'
2936 ELSE NULL
2937 END AS action_type,
2938 existing_geography_id
2939 FROM
2940 (SELECT state.geography_id geography_id,
2941 state.country_state_abbreviation geography_name,
2942 state.country_state_abbreviation geography_code,
2943 'STATE' geography_type,
2944 1 parent_geography_id,
2945 'United States' parent_geography_name,
2946 'COUNTRY' parent_geography_type,
2947 1 geography_element1_id,
2948 'United States' geography_element1,
2949 'US' geography_element1_code,
2950 'COUNTRY' geography_element1_type,
2951 state.geography_id geography_element2_id,
2952 state.country_state_abbreviation geography_element2,
2953 state.country_state_abbreviation geography_element2_code,
2954 'STATE' geography_element2_type,
2955 null geography_element3_id,
2956 null geography_element3 ,
2957 null geography_element3_code,
2958 null geography_element3_type,
2959 null geography_element4_id,
2960 null geography_element4,
2961 null geography_element4_code,
2962 null geography_element4_type,
2963 state.geography_name geography_name1,
2964 state.multiple_parent_flag,
2965 state.effective_from start_date,
2966 nvl(state.effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
2967 state.country_code,
2968 state.status,
2969 (SELECT hzg.geography_id
2970 FROM HZ_GEOGRAPHIES hzg
2971 WHERE hzg.geography_id = state.geography_id) existing_geography_id
2972 FROM zx_data_upload_interface state
2973 WHERE state.record_type = 01
2974 AND state.LAST_UPDATION_VERSION > p_last_run_version
2975 AND state.geography_id IS NOT NULL
2976 AND nvl(state.status,'ERROR') IN ('CREATE','UPDATE')
2977 --AND state.rowid between l_start_rowid and l_end_rowid
2978 UNION
2979 SELECT county.geography_id geography_id,
2980 county.geography_name geography_name,
2981 null geography_code,
2982 'COUNTY' geography_type,
2983 state.geography_id parent_geography_id,
2984 state.geography_name parent_geography_name,
2985 'STATE' parent_geography_type,
2986 1 geography_element1_id,
2987 'United States' geography_element1,
2988 'US' geography_element1_code,
2989 'COUNTRY' geography_element1_type,
2990 state.geography_id geography_element2_id,
2991 state.country_state_abbreviation geography_element2,
2992 state.country_state_abbreviation geography_element2_code,
2993 'STATE' geography_element2_type,
2994 county.geography_id geography_element3_id,
2995 county.geography_name geography_element3 ,
2996 county.geography_name geography_element3_code,
2997 'COUNTY' geography_element3_type,
2998 null geography_element4_id ,
2999 null geography_element4,
3000 null geography_element4_code,
3001 null geography_element4_type,
3002 null geography_name1,
3003 county.multiple_parent_flag,
3004 county.effective_from start_date,
3005 nvl(county.effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
3006 county.country_code,
3007 county.status,
3008 (SELECT hzg.geography_id
3009 FROM HZ_GEOGRAPHIES hzg
3010 WHERE hzg.geography_id = county.geography_id) existing_geography_id
3011 FROM zx_data_upload_interface county,
3012 zx_data_upload_interface state
3013 WHERE county.record_type = 03
3014 AND county.LAST_UPDATION_VERSION > p_last_run_version
3015 AND county.geography_id IS NOT NULL
3016 AND nvl(county.status,'ERROR') IN ('CREATE','UPDATE')
3017 --AND county.rowid between l_start_rowid and l_end_rowid
3018 AND state.record_type = 01
3019 AND state.geography_id IS NOT NULL
3020 AND state.state_jurisdiction_code = county.state_jurisdiction_code
3021 AND state.country_code = county.country_code
3022 AND state.effective_to IS NULL
3023 UNION
3024 SELECT city.geography_id geography_id,
3025 city.geography_name geography_name,
3026 null geography_code,
3027 'CITY' geography_type,
3028 county.geography_id parent_geography_id,
3029 county.geography_name parent_geography_name,
3030 'COUNTY' parent_geography_type,
3031 1 geography_element1_id,
3032 'United States' geography_element1,
3033 'US' geography_element1_code,
3034 'COUNTRY' geography_element1_type,
3035 state.geography_id geography_element2_id,
3036 state.country_state_abbreviation geography_element2,
3037 state.country_state_abbreviation geography_element2_code,
3038 'STATE' geography_element2_type,
3039 county.geography_id geography_element3_id,
3040 county.geography_name geography_element3 ,
3041 county.geography_name geography_element3_code,
3042 'COUNTY' geography_element3_type,
3043 city.geography_id geography_element4_id ,
3044 city.geography_name geography_element4,
3045 null geography_element4_code,
3046 'CITY' geography_element4_type,
3047 null geography_name1,
3048 city.multiple_parent_flag,
3049 city.effective_from start_date,
3050 nvl(city.effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
3051 city.country_code,
3052 city.status,
3053 (SELECT hzg.geography_id
3054 FROM HZ_GEOGRAPHIES hzg
3055 WHERE hzg.geography_id = city.geography_id) existing_geography_id
3056 FROM zx_data_upload_interface city,
3057 zx_data_upload_interface county,
3058 zx_data_upload_interface state
3059 WHERE city.record_type = 06
3060 AND city.LAST_UPDATION_VERSION > p_last_run_version
3061 AND city.geography_id IS NOT NULL
3062 AND nvl(city.status,'ERROR') IN ('CREATE','UPDATE')
3063 --AND city.rowid between l_start_rowid and l_end_rowid
3064 AND county.record_type = 03
3065 AND county.geography_id IS NOT NULL
3066 AND county.county_jurisdiction_code = city.county_jurisdiction_code
3067 AND county.state_jurisdiction_code = city.state_jurisdiction_code
3068 AND county.country_code = city.country_code
3069 AND county.effective_to IS NULL
3070 AND state.record_type = 01
3071 AND state.geography_id IS NOT NULL
3072 AND state.state_jurisdiction_code = county.state_jurisdiction_code
3073 AND state.country_code = county.country_code
3074 AND state.effective_to IS NULL
3075 ) v;
3076
3077 FND_FILE.PUT_LINE
3078 (
3079 FND_FILE.LOG,
3080 'Records Inserted: '||TO_CHAR(SQL%ROWCOUNT)
3081 );
3082
3083 /*
3084 SELECT COUNT(*)
3085 INTO l_rows_processed
3086 FROM zx_data_upload_interface
3087 WHERE rowid between l_start_rowid and l_end_rowid;
3088 --l_rows_processed := SQL%ROWCOUNT;
3089
3090 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_end_rowid);*/
3091
3092 COMMIT;
3093 /*--
3094 -- get new range of ids
3095 --
3096 ad_parallel_updates_pkg.get_rowid_range
3097 (
3098 l_start_rowid,
3099 l_end_rowid,
3100 l_any_rows_to_process,
3101 P_batch_size,
3102 FALSE
3103 );
3104
3105 END LOOP;*/
3106
3107 EXCEPTION
3108
3109 WHEN NO_DATA_FOUND THEN
3110 retcode := '1';
3111 errbuf := 'No data found';
3112 FND_FILE.PUT_LINE
3113 (
3114 FND_FILE.LOG,
3115 'Error in '||l_api_name||': '||errbuf
3116 );
3117
3118 WHEN OTHERS THEN
3119 ROLLBACK;
3120 retcode := '2';
3121 errbuf := SQLERRM;
3122 FND_FILE.PUT_LINE
3123 (
3124 FND_FILE.LOG,
3125 'Unexpected Error in '||l_api_name||': '||errbuf
3126 );
3127
3128 END CREATE_GEOGRAPHY;
3129
3130
3131 --
3132 -- Procedure to create tax geography for cities and jurisidictions for all
3133 --
3134 PROCEDURE CREATE_TAX_ZONES
3135 (
3136 errbuf OUT NOCOPY VARCHAR2,
3137 retcode OUT NOCOPY VARCHAR2,
3138 p_tax_content_source IN VARCHAR2,
3139 p_last_run_version IN NUMBER,
3140 p_tax_regime_code IN VARCHAR2,
3141 p_tax_zone_type IN VARCHAR2
3142 ) IS
3143
3144 l_api_name CONSTANT VARCHAR2(30):= 'create_tax_zones';
3145
3146 -----------------------------------------------------
3147 -- Ad parallelization variables
3148 -----------------------------------------------------
3149 l_table_owner VARCHAR2(30) := 'ZX';
3150 l_any_rows_to_process BOOLEAN;
3151 l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
3152 l_start_rowid ROWID;
3153 l_end_rowid ROWID;
3154 l_rows_processed NUMBER;
3155
3156
3157 BEGIN
3158
3159 retcode := '0';
3160
3161 /**--
3162 -- Initialize the rowid ranges
3163 --
3164 ad_parallel_updates_pkg.initialize_rowid_range(
3165 ad_parallel_updates_pkg.ROWID_RANGE,
3166 l_table_owner,
3167 l_table_name,
3168 p_script_name,
3169 p_Worker_Id,
3170 p_Num_Workers,
3171 p_batch_size, 0);
3172 --
3173 -- Get rowid ranges
3174 --
3175 ad_parallel_updates_pkg.get_rowid_range(
3176 l_start_rowid,
3177 l_end_rowid,
3178 l_any_rows_to_process,
3179 p_batch_size,
3180 TRUE);
3181
3182
3183 WHILE (l_any_rows_to_process)
3184 LOOP**/
3185
3186 FND_FILE.PUT_LINE
3187 (
3188 FND_FILE.LOG,
3189 'Creating Tax Zones.'
3190 );
3191
3192 INSERT ALL
3193 WHEN (zone_geography_type IS NOT NULL AND zone_geography_id IS NOT NULL
3194 AND existing_geography_id IS NULL AND primary_flag = 'Y') THEN
3195 INTO HZ_GEOGRAPHIES
3196 (
3197 GEOGRAPHY_ID,
3198 OBJECT_VERSION_NUMBER,
3199 GEOGRAPHY_TYPE,
3200 GEOGRAPHY_NAME,
3201 GEOGRAPHY_USE,
3202 GEOGRAPHY_CODE,
3203 START_DATE,
3204 END_DATE,
3205 MULTIPLE_PARENT_FLAG,
3206 CREATED_BY_MODULE,
3207 COUNTRY_CODE,
3208 TIMEZONE_CODE,
3209 LAST_UPDATED_BY,
3210 CREATION_DATE,
3211 CREATED_BY,
3212 LAST_UPDATE_DATE,
3213 LAST_UPDATE_LOGIN
3214 )
3215 VALUES
3216 (
3217 zone_geography_id,
3218 1,
3219 zone_geography_type,
3220 geo_code,
3221 'TAX',
3222 geo_code,
3223 start_date,
3224 end_date,
3225 'N',
3226 G_CREATED_BY_MODULE,
3227 country_code,
3228 'PST',
3229 fnd_global.user_id,
3230 sysdate,
3231 fnd_global.user_id,
3232 sysdate,
3233 fnd_global.conc_login_id
3234 )
3235 WHEN (zone_geography_type IS NOT NULL AND zone_geography_id IS NOT NULL
3236 AND existing_geography_id IS NULL and primary_flag = 'Y') THEN
3237 INTO HZ_GEOGRAPHY_IDENTIFIERS
3238 (
3239 GEOGRAPHY_ID,
3240 GEO_DATA_PROVIDER,
3241 IDENTIFIER_SUBTYPE,
3242 IDENTIFIER_VALUE,
3243 OBJECT_VERSION_NUMBER,
3244 IDENTIFIER_TYPE,
3245 PRIMARY_FLAG,
3246 LANGUAGE_CODE,
3247 GEOGRAPHY_USE,
3248 GEOGRAPHY_TYPE,
3249 CREATED_BY_MODULE,
3250 LAST_UPDATED_BY,
3251 CREATION_DATE,
3252 CREATED_BY,
3253 LAST_UPDATE_DATE,
3254 LAST_UPDATE_LOGIN
3255 )
3256 VALUES
3257 (
3258 zone_geography_id,
3259 p_tax_content_source,
3260 'STANDARD_NAME',
3261 geo_code,
3262 1,
3263 'NAME',
3264 'Y',
3265 'US',
3266 'TAX',
3267 zone_geography_type,
3268 G_CREATED_BY_MODULE,
3269 fnd_global.user_id,
3270 sysdate,
3271 fnd_global.user_id,
3272 sysdate,
3273 fnd_global.conc_login_id
3274 )
3275 WHEN (zone_geography_type IS NOT NULL AND zone_geography_id IS NOT NULL
3276 AND existing_geography_id IS NULL and primary_flag = 'Y') THEN
3277 INTO HZ_GEOGRAPHY_IDENTIFIERS
3278 (
3279 GEOGRAPHY_ID,
3280 GEO_DATA_PROVIDER,
3281 IDENTIFIER_SUBTYPE,
3282 IDENTIFIER_VALUE,
3283 OBJECT_VERSION_NUMBER,
3284 IDENTIFIER_TYPE,
3285 PRIMARY_FLAG,
3286 LANGUAGE_CODE,
3287 GEOGRAPHY_USE,
3288 GEOGRAPHY_TYPE,
3289 CREATED_BY_MODULE,
3290 LAST_UPDATED_BY,
3291 CREATION_DATE,
3292 CREATED_BY,
3293 LAST_UPDATE_DATE,
3294 LAST_UPDATE_LOGIN
3295 )
3296 VALUES
3297 (
3298 zone_geography_id,
3299 p_tax_content_source,
3300 'GEO_CODE',
3301 geo_code,
3302 1,
3303 'CODE',
3304 'Y',
3305 'US',
3306 'TAX',
3307 zone_geography_type,
3308 G_CREATED_BY_MODULE,
3309 fnd_global.user_id,
3310 sysdate,
3311 fnd_global.user_id,
3312 sysdate,
3313 fnd_global.conc_login_id
3314 )
3315 WHEN (zone_geography_type IS NOT NULL AND zone_geography_id IS NOT NULL) THEN
3316 INTO HZ_RELATIONSHIPS
3317 (
3318 RELATIONSHIP_ID,
3319 SUBJECT_ID,
3320 SUBJECT_TYPE,
3321 SUBJECT_TABLE_NAME,
3322 OBJECT_ID,
3323 OBJECT_TYPE,
3324 OBJECT_TABLE_NAME,
3325 RELATIONSHIP_CODE,
3326 DIRECTIONAL_FLAG,
3327 COMMENTS,
3328 START_DATE,
3329 END_DATE,
3330 STATUS,
3331 CREATED_BY,
3332 CREATION_DATE,
3333 LAST_UPDATED_BY,
3334 LAST_UPDATE_DATE,
3335 LAST_UPDATE_LOGIN,
3336 CONTENT_SOURCE_TYPE,
3337 RELATIONSHIP_TYPE,
3338 OBJECT_VERSION_NUMBER,
3339 CREATED_BY_MODULE,
3340 APPLICATION_ID,
3341 DIRECTION_CODE,
3342 PERCENTAGE_OWNERSHIP,
3343 ACTUAL_CONTENT_SOURCE
3344 )
3345 VALUES
3346 (
3347 hz_relationships_s.nextval,
3348 zone_geography_id,
3349 zone_geography_type,
3350 'HZ_GEOGRAPHIES',
3351 geography_id,
3352 geography_type,
3353 'HZ_GEOGRAPHIES',
3354 'PARENT_OF',
3355 'F',
3356 null,
3357 start_date,
3358 end_date,
3359 'A',
3360 fnd_global.user_id,
3361 sysdate,
3362 fnd_global.user_id,
3363 sysdate,
3364 fnd_global.conc_login_id,
3365 G_CREATED_BY_MODULE,
3366 'TAX',
3367 1,
3368 G_CREATED_BY_MODULE,
3369 null,
3370 'P',
3371 null,
3372 p_tax_content_source
3373 )
3374 WHEN (zone_geography_type IS NOT NULL AND zone_geography_id IS NOT NULL) THEN
3375 INTO HZ_RELATIONSHIPS
3376 (
3377 RELATIONSHIP_ID,
3378 SUBJECT_ID,
3379 SUBJECT_TYPE,
3380 SUBJECT_TABLE_NAME,
3381 OBJECT_ID,
3382 OBJECT_TYPE,
3383 OBJECT_TABLE_NAME,
3384 RELATIONSHIP_CODE,
3385 DIRECTIONAL_FLAG,
3386 COMMENTS,
3387 START_DATE,
3388 END_DATE,
3389 STATUS,
3390 CREATED_BY,
3391 CREATION_DATE,
3392 LAST_UPDATED_BY,
3393 LAST_UPDATE_DATE,
3394 LAST_UPDATE_LOGIN,
3395 CONTENT_SOURCE_TYPE,
3396 RELATIONSHIP_TYPE,
3397 OBJECT_VERSION_NUMBER,
3398 CREATED_BY_MODULE,
3399 APPLICATION_ID,
3400 DIRECTION_CODE,
3401 PERCENTAGE_OWNERSHIP,
3402 ACTUAL_CONTENT_SOURCE
3403 )
3404 VALUES
3405 (
3406 hz_relationships_s.nextval,
3407 geography_id,
3408 geography_type,
3409 'HZ_GEOGRAPHIES',
3410 zone_geography_id,
3411 zone_geography_type,
3412 'HZ_GEOGRAPHIES',
3413 'CHILD_OF',
3414 'B',
3415 null,
3416 start_date,
3417 end_date,
3418 'A',
3419 fnd_global.user_id,
3420 sysdate,
3421 fnd_global.user_id,
3422 sysdate,
3423 fnd_global.conc_login_id,
3424 G_CREATED_BY_MODULE,
3425 'TAX',
3426 1,
3427 G_CREATED_BY_MODULE,
3428 null,
3429 'C',
3430 null,
3431 p_tax_content_source
3432 )
3433 -- Bug 6393452
3434 WHEN (existing_jurisdiction_id IS NULL and CITY_ROW_NUMBER = 1 and existing_zone_geography_id IS NULL
3435 AND existing_tax_rate = 1 AND primary_flag = 'Y') THEN
3436 INTO ZX_JURISDICTIONS_B
3437 (
3438 TAX_JURISDICTION_CODE,
3439 EFFECTIVE_FROM,
3440 EFFECTIVE_TO,
3441 TAX_REGIME_CODE,
3442 TAX,
3443 DEFAULT_JURISDICTION_FLAG,
3444 RECORD_TYPE_CODE,
3445 TAX_JURISDICTION_ID,
3446 ZONE_GEOGRAPHY_ID,
3447 INNER_CITY_JURISDICTION_FLAG,
3448 PRECEDENCE_LEVEL,
3449 ALLOW_TAX_REGISTRATIONS_FLAG,
3450 OBJECT_VERSION_NUMBER,
3451 CREATED_BY,
3452 CREATION_DATE,
3453 LAST_UPDATED_BY,
3457 VALUES
3454 LAST_UPDATE_DATE,
3455 LAST_UPDATE_LOGIN
3456 )
3458 (
3459 geo_code,
3460 decode(greatest(start_date,G_RECORD_EFFECTIVE_START),start_date,start_date,G_RECORD_EFFECTIVE_START),
3461 NULL,
3462 tax_regime_code,
3463 tax,
3464 'N',
3465 G_CREATED_BY_MODULE,
3466 zx_jurisdictions_b_s1.nextval,
3467 zone_geography_id,
3468 inner_city_flag,
3469 precedence_level,
3470 'Y',
3471 1,
3472 fnd_global.user_id,
3473 sysdate,
3474 fnd_global.user_id,
3475 sysdate,
3476 fnd_global.conc_login_id
3477 )
3478 SELECT v.geography_id,
3479 v.geography_type,
3480 v.tax_regime_code,
3481 v.tax,
3482 v.zone_geography_id,
3483 v.zone_geography_type,
3484 v.geo_code,
3485 v.start_date,
3486 nvl(v.end_date,to_date('12/31/4712','mm/dd/yyyy')) end_date,
3487 v.country_code,
3488 v.precedence_level,
3489 (SELECT tax_jurisdiction_id
3490 FROM zx_jurisdictions_b j
3491 WHERE j.tax_regime_code = v.tax_regime_code
3492 AND j.tax = v.tax
3493 AND j.tax_jurisdiction_code = v.geo_code) existing_jurisdiction_id,
3494 (SELECT tax_jurisdiction_id
3495 FROM zx_jurisdictions_b j
3496 WHERE j.tax_regime_code = v.tax_regime_code
3497 AND j.tax = v.tax
3498 AND j.zone_geography_id = v.zone_geography_id
3499 AND j.effective_from = DECODE(GREATEST(v.start_date,G_RECORD_EFFECTIVE_START),v.start_date,v.start_date,G_RECORD_EFFECTIVE_START)) existing_zone_geography_id,
3500 (SELECT geography_id
3501 FROM hz_geographies
3502 WHERE geography_id = v.zone_geography_id) existing_geography_id,
3503 v.inner_city_flag,
3504 -- Bug 6393452
3505 CITY_ROW_NUMBER,
3506 existing_tax_rate,
3507 primary_flag
3508 FROM
3509 (SELECT inter.geography_id,
3510 'STATE' geography_type,
3511 p_tax_regime_code tax_regime_code,
3512 'STATE' tax,
3513 inter.geography_id zone_geography_id,
3514 to_char(null) zone_geography_type,
3515 DECODE(p_tax_content_source,
3516 'TAXWARE','ST-'||inter.COUNTRY_STATE_ABBREVIATION,
3517 'VERTEX','ST-'||inter.STATE_JURISDICTION_CODE||'0000000',
3518 'OTHERS','ST-'||inter.STATE_JURISDICTION_CODE||'0000000',
3519 'ST-'||inter.STATE_JURISDICTION_CODE||'0000000') geo_code,
3520 inter.effective_from start_date,
3521 inter.effective_to end_date,
3522 inter.country_code,
3523 275 precedence_level,
3524 'N' inner_city_flag,
3525 1 CITY_ROW_NUMBER,
3526 (SELECT /*+first_rows(1)*/ 1
3527 FROM zx_data_upload_interface rate
3528 WHERE rate.record_type IN (09,10,11,12)
3529 AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3530 AND rate.county_jurisdiction_code IS NULL
3531 AND rate.city_jurisdiction_code IS NULL
3532 AND rate.LAST_UPDATION_VERSION > p_last_run_version
3533 AND ROWNUM = 1) existing_tax_rate,
3534 'Y' primary_flag
3535 FROM zx_data_upload_interface inter
3536 WHERE inter.record_type = 01
3537 AND inter.geography_id IS NOT NULL
3538 AND inter.effective_to IS NULL
3539 AND inter.LAST_UPDATION_VERSION > p_last_run_version
3540 AND p_tax_zone_type IS NOT NULL -- Means new regime
3541 UNION
3542 SELECT inter.geography_id,
3543 'STATE' geography_type,
3544 p_tax_regime_code tax_regime_code,
3545 'STATE' tax,
3546 inter.zone_geography_id,
3547 'US_STATE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10) zone_geography_type,
3548 DECODE(p_tax_content_source,
3549 'TAXWARE','ST-'||inter.COUNTRY_STATE_ABBREVIATION,
3550 'VERTEX','ST-'||inter.STATE_JURISDICTION_CODE||'0000000',
3551 'OTHERS','ST-'||inter.STATE_JURISDICTION_CODE||'0000000',
3552 'ST-'||inter.STATE_JURISDICTION_CODE||'0000000') geo_code,
3553 inter.effective_from start_date,
3554 inter.effective_to end_date,
3555 inter.country_code,
3556 275 precedence_level,
3557 'N' inner_city_flag,
3558 1 CITY_ROW_NUMBER,
3559 (SELECT /*+first_rows(1)*/ 1
3560 FROM zx_data_upload_interface rate
3561 WHERE rate.record_type IN (09,10,11,12)
3562 AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3563 AND rate.county_jurisdiction_code IS NULL
3564 AND rate.city_jurisdiction_code IS NULL
3565 AND rate.LAST_UPDATION_VERSION > p_last_run_version
3566 AND ROWNUM = 1) existing_tax_rate,
3567 'Y' primary_flag
3568 FROM zx_data_upload_interface inter
3569 WHERE inter.record_type = 01
3570 AND inter.zone_geography_id IS NOT NULL
3571 AND inter.effective_to IS NULL
3572 AND p_tax_zone_type IS NULL -- Means migrated regime
3573 -- AND EXISTS (SELECT NULL
3574 -- FROM zx_data_upload_interface rate
3575 -- WHERE rate.record_type IN (09,10,11,12)
3576 -- AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3577 -- AND rate.county_jurisdiction_code IS NULL
3578 -- AND rate.city_jurisdiction_code IS NULL
3579 -- AND rate.LAST_UPDATION_VERSION > p_last_run_version)
3580 UNION
3581 SELECT inter.geography_id,
3582 'COUNTY' geography_type,
3583 p_tax_regime_code tax_regime_code,
3584 'COUNTY' tax,
3585 inter.geography_id zone_geography_id,
3586 to_char(null) zone_geography_type,
3587 DECODE(p_tax_content_source,
3588 'TAXWARE','CO-'||inter.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(inter.GEOGRAPHY_NAME,1,21)),
3589 'VERTEX','CO-'||inter.STATE_JURISDICTION_CODE||inter.COUNTY_JURISDICTION_CODE||'0000',
3590 'OTHERS','CO-'||inter.STATE_JURISDICTION_CODE||inter.COUNTY_JURISDICTION_CODE||'0000',
3591 'CO-'||inter.STATE_JURISDICTION_CODE||inter.COUNTY_JURISDICTION_CODE||'0000') geo_code,
3592 inter.effective_from start_date,
3593 inter.effective_to end_date,
3594 inter.country_code,
3595 175 precedence_level,
3596 'N' inner_city_flag,
3597 1 CITY_ROW_NUMBER,
3598 (SELECT /*+first_rows(1)*/ 1
3599 FROM zx_data_upload_interface rate
3600 WHERE rate.record_type IN (09,10,11,12)
3601 AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3602 AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3603 AND rate.city_jurisdiction_code IS NULL
3604 AND rate.LAST_UPDATION_VERSION > p_last_run_version
3605 AND ROWNUM = 1) existing_tax_rate,
3606 'Y' primary_flag
3607 FROM zx_data_upload_interface inter
3608 WHERE inter.record_type = 03
3609 AND inter.geography_id IS NOT NULL
3610 AND inter.effective_to IS NULL
3611 AND inter.LAST_UPDATION_VERSION > p_last_run_version
3612 AND p_tax_zone_type IS NOT NULL -- Means new regime
3613 UNION
3614 SELECT inter.geography_id,
3615 'COUNTY' geography_type,
3616 p_tax_regime_code tax_regime_code,
3617 'COUNTY' tax,
3618 inter.zone_geography_id,
3619 'US_COUNTY_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10) zone_geography_type,
3620 DECODE(p_tax_content_source,
3621 'TAXWARE','CO-'||inter.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(inter.GEOGRAPHY_NAME,1,21)),
3622 'VERTEX','CO-'||inter.STATE_JURISDICTION_CODE||inter.COUNTY_JURISDICTION_CODE||'0000',
3623 'OTHERS','CO-'||inter.STATE_JURISDICTION_CODE||inter.COUNTY_JURISDICTION_CODE||'0000',
3624 'CO-'||inter.STATE_JURISDICTION_CODE||inter.COUNTY_JURISDICTION_CODE||'0000') geo_code,
3625 inter.effective_from start_date,
3626 inter.effective_to end_date,
3627 inter.country_code,
3628 175 precedence_level,
3629 'N' inner_city_flag,
3630 1 CITY_ROW_NUMBER,
3631 (SELECT /*+first_rows(1)*/ 1
3632 FROM zx_data_upload_interface rate
3633 WHERE rate.record_type IN (09,10,11,12)
3634 AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3635 AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3636 AND rate.city_jurisdiction_code IS NULL
3637 AND rate.LAST_UPDATION_VERSION > p_last_run_version
3638 AND ROWNUM = 1) existing_tax_rate,
3639 'Y' primary_flag
3640 FROM zx_data_upload_interface inter
3641 WHERE inter.record_type = 03
3642 AND inter.zone_geography_id IS NOT NULL
3643 AND inter.effective_to IS NULL
3644 AND p_tax_zone_type IS NULL -- Means migrated regime
3645 -- AND EXISTS (SELECT NULL
3646 -- FROM zx_data_upload_interface rate
3647 -- WHERE rate.record_type IN (09,10,11,12)
3648 -- AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3649 -- AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3653 SELECT inter.geography_id,
3650 -- AND rate.city_jurisdiction_code IS NULL
3651 -- AND rate.LAST_UPDATION_VERSION > p_last_run_version)
3652 UNION
3654 'CITY' geography_type,
3655 p_tax_regime_code tax_regime_code,
3656 'CITY' tax,
3657 inter.zone_geography_id,
3658 DECODE(p_tax_zone_type,null,'US_CITY_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10),p_tax_zone_type) zone_geography_type,
3659 DECODE(p_tax_content_source,
3660 'TAXWARE','CI-'||inter.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(inter.GEOGRAPHY_NAME,1,12))||'-'||inter.CITY_JURISDICTION_CODE,
3661 'VERTEX','CI-'||inter.STATE_JURISDICTION_CODE||inter.COUNTY_JURISDICTION_CODE||LPAD(inter.CITY_JURISDICTION_CODE,4,'0'),
3662 'OTHERS','CI-'||inter.STATE_JURISDICTION_CODE||inter.COUNTY_JURISDICTION_CODE||LPAD(inter.CITY_JURISDICTION_CODE,4,'0'),
3663 'CI-'||inter.STATE_JURISDICTION_CODE||inter.COUNTY_JURISDICTION_CODE||TRIM(SUBSTR(inter.CITY_JURISDICTION_CODE,1,19))) geo_code,
3664 inter.effective_from start_date,
3665 inter.effective_to end_date,
3666 inter.country_code,
3667 75 precedence_level,
3668 DECODE(TO_CHAR(inter.JURISDICTION_SERIAL_NUMBER),'1','Y','N') inner_city_flag,
3669 1 CITY_ROW_NUMBER,
3670 (SELECT /*+first_rows(1)*/ 1
3671 FROM zx_data_upload_interface rate
3672 WHERE rate.record_type IN (09,10,11,12)
3673 AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3674 AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3675 AND rate.city_jurisdiction_code = inter.city_jurisdiction_code
3676 AND rate.LAST_UPDATION_VERSION > p_last_run_version
3677 AND ROWNUM = 1) existing_tax_rate,
3678 primary_flag
3679 FROM zx_data_upload_interface inter
3680 WHERE inter.record_type = 06
3681 AND inter.zone_geography_id IS NOT NULL
3682 AND inter.effective_to IS NULL
3683 -- cities should be considered always as they might have been created earlier but their zip range or rates are sent for the first time
3684 -- AND EXISTS (SELECT NULL
3685 -- FROM zx_data_upload_interface rate
3686 -- WHERE rate.record_type IN (08,09,10,11,12)
3687 -- AND rate.state_jurisdiction_code = inter.state_jurisdiction_code
3688 -- AND rate.county_jurisdiction_code = inter.county_jurisdiction_code
3689 -- AND rate.city_jurisdiction_code = inter.city_jurisdiction_code
3690 -- AND rate.LAST_UPDATION_VERSION > p_last_run_version)
3691 UNION
3692 SELECT z.geography_id,
3693 'CITY' geography_type,
3694 p_tax_regime_code tax_regime_code,
3695 decode(to_char(inter.record_type),'9',inter.SALES_TAX_AUTHORITY_LEVEL,'10',inter.RENTAL_TAX_AUTHORITY_LEVEL,'11',inter.USE_TAX_AUTHORITY_LEVEL,'12',inter.LEASE_TAX_AUTHORITY_LEVEL) tax,
3696 DECODE(p_tax_zone_type,null,inter.zone_geography_id,z.zone_geography_id) zone_geography_id,
3697 DECODE(p_tax_zone_type,null,'US_OVERRIDE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10),null) zone_geography_type,
3698 DECODE(decode(to_char(inter.record_type),'9',inter.SALES_TAX_AUTHORITY_LEVEL,'10',inter.RENTAL_TAX_AUTHORITY_LEVEL,'11',inter.USE_TAX_AUTHORITY_LEVEL,'12',inter.LEASE_TAX_AUTHORITY_LEVEL),
3699 'STATE','ST-','COUNTY','CO-')||
3700 DECODE(p_tax_content_source,
3701 'TAXWARE','CI-'||z.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(z.GEOGRAPHY_NAME,1,12))||'-'||z.CITY_JURISDICTION_CODE,
3702 'VERTEX','CI-'||z.STATE_JURISDICTION_CODE||z.COUNTY_JURISDICTION_CODE||LPAD(z.CITY_JURISDICTION_CODE,4,'0'),
3703 'OTHERS','CI-'||z.STATE_JURISDICTION_CODE||z.COUNTY_JURISDICTION_CODE||LPAD(z.CITY_JURISDICTION_CODE,4,'0'),
3704 'CI-'||z.STATE_JURISDICTION_CODE||z.COUNTY_JURISDICTION_CODE||TRIM(SUBSTR(z.CITY_JURISDICTION_CODE,1,19))) geo_code,
3705 z.effective_from start_date,
3706 z.effective_to end_date,
3707 inter.country_code,
3708 75 precedence_level,
3709 'N' inner_city_flag,
3710 ROW_NUMBER()
3711 OVER (PARTITION BY
3712 p_tax_regime_code
3713 ,DECODE(to_char(inter.record_type),'9',inter.SALES_TAX_AUTHORITY_LEVEL,'10',inter.RENTAL_TAX_AUTHORITY_LEVEL,'11',inter.USE_TAX_AUTHORITY_LEVEL,'12',inter.LEASE_TAX_AUTHORITY_LEVEL)
3714 ,DECODE(p_tax_zone_type,null,inter.zone_geography_id,z.zone_geography_id) -- Modified for Bug#12577793
3715 ,z.effective_from
3716 ORDER BY
3717 z.effective_from
3718 ,DECODE(z.primary_flag,'N',2,1)
3719 ) AS CITY_ROW_NUMBER,
3720 1 existing_tax_rate,
3721 z.primary_flag
3722 FROM zx_data_upload_interface inter,
3723 zx_data_upload_interface z
3724 WHERE inter.record_type IN (09,10,11,12)
3725 AND (inter.SALES_TAX_AUTHORITY_LEVEL = 'STATE'
3726 OR inter.SALES_TAX_AUTHORITY_LEVEL = 'COUNTY'
3727 OR inter.RENTAL_TAX_AUTHORITY_LEVEL = 'STATE'
3728 OR inter.RENTAL_TAX_AUTHORITY_LEVEL = 'COUNTY'
3729 OR inter.USE_TAX_AUTHORITY_LEVEL = 'STATE'
3730 OR inter.USE_TAX_AUTHORITY_LEVEL = 'COUNTY'
3731 OR inter.LEASE_TAX_AUTHORITY_LEVEL = 'STATE'
3732 OR inter.LEASE_TAX_AUTHORITY_LEVEL = 'COUNTY')
3733 AND inter.STATE_JURISDICTION_CODE IS NOT NULL
3734 AND inter.COUNTY_JURISDICTION_CODE IS NOT NULL
3735 AND inter.CITY_JURISDICTION_CODE IS NOT NULL
3736 AND inter.effective_to IS NULL
3737 AND inter.LAST_UPDATION_VERSION > p_last_run_version
3738 AND (p_tax_zone_type IS NOT NULL
3739 OR inter.zone_geography_id IS NOT NULL)
3740 AND z.record_type = 06
3741 AND z.STATE_JURISDICTION_CODE = inter.STATE_JURISDICTION_CODE
3742 AND z.COUNTY_JURISDICTION_CODE = inter.COUNTY_JURISDICTION_CODE
3743 AND z.CITY_JURISDICTION_CODE = inter.CITY_JURISDICTION_CODE
3744 AND z.zone_geography_id IS NOT NULL
3745 AND z.effective_to IS NULL
3746 UNION
3747 SELECT z.geography_id,
3748 'COUNTY' geography_type,
3749 p_tax_regime_code tax_regime_code,
3750 decode(to_char(inter.record_type),'9',inter.SALES_TAX_AUTHORITY_LEVEL,'10',inter.RENTAL_TAX_AUTHORITY_LEVEL,'11',inter.USE_TAX_AUTHORITY_LEVEL,'12',inter.LEASE_TAX_AUTHORITY_LEVEL) tax,
3751 DECODE(p_tax_zone_type,null,inter.zone_geography_id,z.geography_id) zone_geography_id,
3752 DECODE(p_tax_zone_type,null,'US_OVERRIDE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10),null) zone_geography_type,
3753 DECODE(p_tax_content_source,
3754 'TAXWARE','ST-CO-'||z.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(z.GEOGRAPHY_NAME,1,21)),
3755 'VERTEX','ST-CO-'||z.STATE_JURISDICTION_CODE||z.COUNTY_JURISDICTION_CODE||'0000',
3756 'OTHERS','ST-CO-'||z.STATE_JURISDICTION_CODE||z.COUNTY_JURISDICTION_CODE||'0000',
3757 'ST-CO-'||z.STATE_JURISDICTION_CODE||z.COUNTY_JURISDICTION_CODE||'0000') geo_code,
3758 z.effective_from start_date,
3759 z.effective_to end_date,
3760 inter.country_code,
3761 175 precedence_level,
3762 'N' inner_city_flag,
3763 1 CITY_ROW_NUMBER,
3764 1 existing_tax_rate,
3765 'Y' primary_flag
3766 FROM zx_data_upload_interface inter,
3767 zx_data_upload_interface z
3768 WHERE inter.record_type IN (09,10,11,12)
3769 AND (inter.SALES_TAX_AUTHORITY_LEVEL = 'STATE'
3770 OR inter.RENTAL_TAX_AUTHORITY_LEVEL = 'STATE'
3771 OR inter.USE_TAX_AUTHORITY_LEVEL = 'STATE'
3772 OR inter.LEASE_TAX_AUTHORITY_LEVEL = 'STATE')
3773 AND inter.STATE_JURISDICTION_CODE IS NOT NULL
3774 AND inter.COUNTY_JURISDICTION_CODE IS NOT NULL
3775 AND inter.CITY_JURISDICTION_CODE IS NULL
3776 AND inter.effective_to IS NULL
3777 AND inter.LAST_UPDATION_VERSION > p_last_run_version
3778 AND (p_tax_zone_type IS NOT NULL
3779 OR inter.zone_geography_id IS NOT NULL)
3780 AND z.record_type = 03
3781 AND z.STATE_JURISDICTION_CODE = inter.STATE_JURISDICTION_CODE
3782 AND z.COUNTY_JURISDICTION_CODE = inter.COUNTY_JURISDICTION_CODE
3783 AND z.geography_id IS NOT NULL
3784 AND z.effective_to IS NULL
3785 ) v;
3786
3787 l_rows_processed := SQL%ROWCOUNT;
3788
3789 FND_FILE.PUT_LINE
3790 (
3791 FND_FILE.LOG,
3792 'Records Inserted: '||TO_CHAR(l_rows_processed)
3793 );
3794
3795 -- Copy accounts from taxes
3796 -- Do this only for newly created jurisdicition
3797 -- Use TL table and regime/tax/record type combination to find new ones
3798
3799 FND_FILE.PUT_LINE
3800 (
3801 FND_FILE.LOG,
3802 'Creating Tax Accounts for Tax Jurisdictions.'
3803 );
3804
3805 INSERT INTO ZX_ACCOUNTS
3806 (
3807 TAX_ACCOUNT_ID,
3808 OBJECT_VERSION_NUMBER,
3809 TAX_ACCOUNT_ENTITY_CODE,
3810 TAX_ACCOUNT_ENTITY_ID,
3811 LEDGER_ID,
3812 INTERNAL_ORGANIZATION_ID,
3813 TAX_ACCOUNT_CCID,
3814 INTERIM_TAX_CCID,
3815 NON_REC_ACCOUNT_CCID,
3816 ADJ_CCID,
3817 EDISC_CCID,
3818 UNEDISC_CCID,
3819 FINCHRG_CCID,
3820 ADJ_NON_REC_TAX_CCID,
3821 EDISC_NON_REC_TAX_CCID,
3822 UNEDISC_NON_REC_TAX_CCID,
3823 FINCHRG_NON_REC_TAX_CCID,
3824 RECORD_TYPE_CODE,
3825 CREATED_BY,
3826 CREATION_DATE,
3827 LAST_UPDATED_BY,
3828 LAST_UPDATE_DATE,
3829 LAST_UPDATE_LOGIN
3830 )
3831 SELECT
3832 zx_accounts_s.nextval,
3833 1,
3834 'JURISDICTION',
3835 zjb.TAX_JURISDICTION_ID,
3836 za.LEDGER_ID,
3837 za.INTERNAL_ORGANIZATION_ID,
3838 za.TAX_ACCOUNT_CCID,
3839 za.INTERIM_TAX_CCID,
3840 za.NON_REC_ACCOUNT_CCID,
3841 za.ADJ_CCID,
3842 za.EDISC_CCID,
3843 za.UNEDISC_CCID,
3844 za.FINCHRG_CCID,
3845 za.ADJ_NON_REC_TAX_CCID,
3846 za.EDISC_NON_REC_TAX_CCID,
3847 za.UNEDISC_NON_REC_TAX_CCID,
3848 za.FINCHRG_NON_REC_TAX_CCID,
3849 G_CREATED_BY_MODULE,
3850 fnd_global.user_id,
3851 sysdate,
3852 fnd_global.user_id,
3853 sysdate,
3854 fnd_global.conc_login_id
3855 FROM ZX_JURISDICTIONS_B zjb,
3856 ZX_TAXES_B ztb,
3857 ZX_ACCOUNTS za
3858 WHERE zjb.TAX_REGIME_CODE = p_tax_regime_code
3859 AND zjb.TAX IN ('STATE','COUNTY','CITY')
3860 AND zjb.RECORD_TYPE_CODE = G_CREATED_BY_MODULE
3861 AND NOT EXISTS (SELECT NULL
3862 FROM ZX_JURISDICTIONS_TL zjt
3863 WHERE zjt.TAX_JURISDICTION_ID = zjb.TAX_JURISDICTION_ID)
3864 AND ztb.TAX_REGIME_CODE = zjb.TAX_REGIME_CODE
3865 AND ztb.TAX = zjb.TAX
3866 AND ztb.CONTENT_OWNER_ID = -99 -- Added the condition for Bug#12716747 --
3867 AND ztb.SOURCE_TAX_FLAG = 'Y'
3868 AND za.TAX_ACCOUNT_ENTITY_CODE = 'TAXES'
3869 AND za.TAX_ACCOUNT_ENTITY_ID = ztb.TAX_ID
3870 AND NOT EXISTS (SELECT 1
3871 FROM ZX_ACCOUNTS
3872 WHERE TAX_ACCOUNT_ENTITY_ID = zjb.TAX_JURISDICTION_ID
3873 AND TAX_ACCOUNT_ENTITY_CODE = 'JURISDICTION'
3874 AND LEDGER_ID = za.LEDGER_ID
3875 AND INTERNAL_ORGANIZATION_ID = za.INTERNAL_ORGANIZATION_ID);
3876
3877 FND_FILE.PUT_LINE
3878 (
3879 FND_FILE.LOG,
3880 'Records Inserted: '||TO_CHAR(SQL%ROWCOUNT)
3881 );
3882
3883 FND_FILE.PUT_LINE
3884 (
3885 FND_FILE.LOG,
3886 'Creating TL for Tax Jurisdictions.'
3887 );
3888
3889 INSERT INTO ZX_JURISDICTIONS_TL
3890 (
3891 TAX_JURISDICTION_ID,
3892 TAX_JURISDICTION_NAME,
3893 CREATED_BY,
3894 CREATION_DATE,
3895 LAST_UPDATED_BY,
3896 LAST_UPDATE_DATE,
3897 LAST_UPDATE_LOGIN,
3898 LANGUAGE,
3899 SOURCE_LANG
3900 )
3901 SELECT zjb.TAX_JURISDICTION_ID,
3902 DECODE((SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3903 DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
3904 DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)||'-')
3905 FROM HZ_GEOGRAPHIES hg
3906 WHERE hg.GEOGRAPHY_ID = zjb.zone_geography_id),
3907 '-',
3908 (SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3909 DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
3910 DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)||'-')
3911 FROM hz_geographies hg_zone,
3912 hz_relationships hr,
3913 hz_geographies hg,
3914 zx_data_upload_interface zd
3915 WHERE hg_zone.GEOGRAPHY_ID = zjb.zone_geography_id
3916 AND zd.zone_geography_id = hg_zone.GEOGRAPHY_ID
3917 AND NVL(zd.primary_flag,'Y') = 'Y'
3918 AND hr.SUBJECT_ID = hg_zone.GEOGRAPHY_ID
3919 AND hr.SUBJECT_TYPE = hg_zone.GEOGRAPHY_TYPE
3920 AND hr.SUBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3921 AND hr.RELATIONSHIP_CODE = 'PARENT_OF'
3922 AND hr.DIRECTIONAL_FLAG = 'F'
3923 AND hr.OBJECT_TABLE_NAME = 'HZ_GEOGRAPHIES'
3924 AND zd.geography_id = hg.GEOGRAPHY_ID
3925 AND hg.GEOGRAPHY_ID = hr.OBJECT_ID
3926 AND hg.GEOGRAPHY_TYPE = hr.OBJECT_TYPE
3927 AND ROWNUM = 1),
3928 (SELECT SUBSTR(hg.geography_element2_code,1,2)||'-'||
3929 DECODE(hg.geography_element3,null,'',SUBSTR(hg.geography_element3,1,30)||'-')||
3930 DECODE(hg.geography_element4,null,'',SUBSTR(hg.geography_element4,1,30)||'-')
3931 FROM HZ_GEOGRAPHIES hg
3932 WHERE hg.GEOGRAPHY_ID = zjb.zone_geography_id))
3933 || zjb.TAX_JURISDICTION_CODE,
3934 fnd_global.user_id,
3935 sysdate,
3936 fnd_global.user_id,
3937 sysdate,
3938 fnd_global.conc_login_id,
3939 fl.LANGUAGE_CODE,
3940 USERENV('LANG')
3941 FROM ZX_JURISDICTIONS_B zjb,
3942 FND_LANGUAGES fl
3943 WHERE fl.INSTALLED_FLAG IN ('I', 'B')
3944 AND zjb.TAX_REGIME_CODE = p_tax_regime_code
3945 AND zjb.TAX IN ('STATE','COUNTY','CITY')
3946 AND NOT EXISTS (SELECT NULL
3947 FROM ZX_JURISDICTIONS_TL zjt
3948 WHERE zjt.TAX_JURISDICTION_ID = zjb.TAX_JURISDICTION_ID
3949 AND zjt.LANGUAGE = fl.LANGUAGE_CODE);
3950
3951 l_rows_processed := SQL%ROWCOUNT;
3952
3953 FND_FILE.PUT_LINE
3954 (
3955 FND_FILE.LOG,
3956 'Records Inserted: '||TO_CHAR(SQL%ROWCOUNT)
3957 );
3958
3959 -- Changed the For Loop (present till version 26 of 12.0.X)
3960 -- into Merge statement for Bug#12861649
3961 MERGE INTO hz_geography_ranges hgr USING
3962 (SELECT master_ref_geography_id,
3963 geography_id,
3964 geography_type,
3965 zip_begin,
3966 zip_end,
3967 start_date,
3968 end_date,
3969 hgr_row_id,
3970 postal_code_num
3971 FROM
3972 (SELECT DISTINCT
3973 city.geography_id master_ref_geography_id,
3974 city.zone_geography_id geography_id,
3975 NVL(p_tax_zone_type,'US_CITY_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10)) geography_type,
3976 zip.zip_begin,
3977 zip.zip_end,
3978 zip.effective_from start_date,
3979 NVL(zip.effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
3980 (SELECT hgr.rowid
3981 FROM hz_geography_ranges hgr
3982 WHERE hgr.GEOGRAPHY_ID = city.zone_geography_id
3983 AND hgr.GEOGRAPHY_FROM = zip.zip_begin
3984 AND hgr.START_DATE = zip.effective_from
3985 AND ROWNUM=1
3986 ) hgr_row_id,
3987 ROW_NUMBER()
3988 OVER (PARTITION BY zip.zip_begin, city.zone_geography_id,city.JURISDICTION_SERIAL_NUMBER,
3989 zip.effective_from order by zip.zip_end DESC ) as postal_code_num
3990 FROM ZX_DATA_UPLOAD_INTERFACE zip,
3991 ZX_DATA_UPLOAD_INTERFACE city
3992 WHERE zip.record_type = 08
3993 AND city.record_type = 06
3994 AND city.STATE_JURISDICTION_CODE = zip.STATE_JURISDICTION_CODE
3995 AND city.COUNTY_JURISDICTION_CODE = zip.COUNTY_JURISDICTION_CODE
3996 AND city.CITY_JURISDICTION_CODE = zip.CITY_JURISDICTION_CODE
3997 AND city.zone_geography_id IS NOT NULL
3998 AND city.geography_id IS NOT NULL
3999 AND city.primary_flag = 'Y'
4000 AND city.geography_name = zip.geography_name
4001 ) v
4002 WHERE TRIM(postal_code_num) = 1) ref_ranges
4003 ON (hgr.geography_id = ref_ranges.geography_id AND
4004 hgr.geography_from = ref_ranges.zip_begin AND
4005 hgr.start_date = ref_ranges.start_date)
4006 WHEN MATCHED THEN
4007 UPDATE
4008 SET END_DATE = ref_ranges.end_date,
4009 LAST_UPDATED_BY = fnd_global.user_id,
4010 LAST_UPDATE_DATE = sysdate,
4011 LAST_UPDATE_LOGIN = fnd_global.conc_login_id
4012 WHEN NOT MATCHED THEN
4013 INSERT
4014 (
4015 GEOGRAPHY_ID,
4016 GEOGRAPHY_FROM,
4017 START_DATE,
4018 OBJECT_VERSION_NUMBER,
4019 GEOGRAPHY_TO,
4020 IDENTIFIER_TYPE,
4021 END_DATE,
4022 GEOGRAPHY_TYPE,
4023 GEOGRAPHY_USE,
4024 MASTER_REF_GEOGRAPHY_ID,
4025 CREATED_BY_MODULE,
4026 CREATED_BY,
4027 CREATION_DATE,
4028 LAST_UPDATED_BY,
4029 LAST_UPDATE_DATE,
4030 LAST_UPDATE_LOGIN
4031 )
4032 VALUES
4033 (
4034 ref_ranges.geography_id,
4035 ref_ranges.zip_begin,
4036 ref_ranges.start_date,
4037 1,
4038 ref_ranges.zip_end,
4039 'NAME',
4040 ref_ranges.end_date,
4041 ref_ranges.geography_type,
4042 'TAX',
4043 ref_ranges.master_ref_geography_id,
4044 'EBTAX_CONTENT_UPLOAD',
4045 fnd_global.user_id,
4046 sysdate,
4047 fnd_global.user_id,
4048 sysdate,
4049 fnd_global.conc_login_id
4050 );
4051
4052 -- Migrated regime case
4053 -- insert ranges for state/county/override jurisdictions too
4054 IF (p_tax_zone_type IS NULL) THEN
4055 INSERT ALL
4056 INTO hz_geography_ranges
4057 (
4058 GEOGRAPHY_ID,
4059 GEOGRAPHY_FROM,
4060 START_DATE,
4061 OBJECT_VERSION_NUMBER,
4062 GEOGRAPHY_TO,
4063 IDENTIFIER_TYPE,
4064 END_DATE,
4065 GEOGRAPHY_TYPE,
4066 GEOGRAPHY_USE,
4067 MASTER_REF_GEOGRAPHY_ID,
4068 CREATED_BY_MODULE,
4069 CREATED_BY,
4070 CREATION_DATE,
4071 LAST_UPDATED_BY,
4072 LAST_UPDATE_DATE,
4073 LAST_UPDATE_LOGIN
4074 )
4075 VALUES
4076 (
4077 zone_geography_id,
4078 zip_begin,
4079 start_date,
4080 1,
4081 zip_end,
4082 'NAME',
4083 end_date,
4084 zone_geography_type,
4085 'TAX',
4086 geography_id,
4087 G_CREATED_BY_MODULE,
4088 fnd_global.user_id,
4089 sysdate,
4090 fnd_global.user_id,
4091 sysdate,
4095 zone_geography_id,
4092 fnd_global.conc_login_id
4093 )
4094 SELECT
4096 '0000' zip_begin,
4097 effective_from start_date,
4098 '9999' zip_end,
4099 nvl(effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
4100 DECODE(TO_CHAR(record_type),'1','US_STATE_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10),
4101 '3','US_COUNTY_ZONE_TYPE_'||SUBSTRB(p_tax_regime_code, 14,10)) zone_geography_type,
4102 geography_id
4103 FROM ZX_DATA_UPLOAD_INTERFACE inter
4104 WHERE record_type in (1,3)
4105 AND zone_geography_id IS NOT NULL
4106 AND NOT EXISTS (SELECT NULL
4107 FROM hz_geography_ranges hgr
4108 WHERE hgr.GEOGRAPHY_ID = inter.zone_geography_id
4109 AND hgr.GEOGRAPHY_FROM = '0000'
4110 AND hgr.START_DATE = inter.effective_from
4111 );
4112 END IF;
4113
4114 COMMIT;
4115
4116 /*ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_end_rowid);
4117
4118 COMMIT;
4119 --
4120 -- get new range of ids
4121 --
4122 ad_parallel_updates_pkg.get_rowid_range
4123 (
4124 l_start_rowid,
4125 l_end_rowid,
4126 l_any_rows_to_process,
4127 P_batch_size,
4128 FALSE
4129 );
4130
4131 END LOOP;*/
4132
4133 EXCEPTION
4134
4135 WHEN NO_DATA_FOUND THEN
4136 retcode := '1';
4137 errbuf := 'No data found';
4138 FND_FILE.PUT_LINE
4139 (
4140 FND_FILE.LOG,
4141 'Error in '||l_api_name||': '||errbuf
4142 );
4143
4144 WHEN OTHERS THEN
4145 ROLLBACK;
4146 retcode := '2';
4147 errbuf := SQLERRM;
4148 FND_FILE.PUT_LINE
4149 (
4150 FND_FILE.LOG,
4151 'Unexpected Error '||l_api_name||': '||errbuf
4152 );
4153
4154 END CREATE_TAX_ZONES;
4155
4156 --
4157 -- Procedure to create master geography for postal codes
4158 --
4159 PROCEDURE CREATE_ZIP
4160 (
4161 errbuf OUT NOCOPY VARCHAR2,
4162 retcode OUT NOCOPY VARCHAR2,
4163 p_tax_content_source IN VARCHAR2,
4164 p_last_run_version IN NUMBER
4165 ) IS
4166
4167 l_api_name CONSTANT VARCHAR2(30):= 'create_zip';
4168
4169 -----------------------------------------------------
4170 -- Ad parallelization variables
4171 -----------------------------------------------------
4172 l_table_owner VARCHAR2(30) := 'ZX';
4173 l_any_rows_to_process BOOLEAN;
4174 l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
4175 l_start_rowid ROWID;
4176 l_end_rowid ROWID;
4177 l_rows_processed NUMBER;
4178
4179
4180 BEGIN
4181
4182 retcode := '0';
4183
4184 /**--
4185 -- Initialize the rowid ranges
4186 --
4187 ad_parallel_updates_pkg.initialize_rowid_range(
4188 ad_parallel_updates_pkg.ROWID_RANGE,
4189 l_table_owner,
4190 l_table_name,
4191 p_script_name,
4192 p_Worker_Id,
4193 p_Num_Workers,
4194 p_batch_size, 0);
4195 --
4196 -- Get rowid ranges
4197 --
4198 ad_parallel_updates_pkg.get_rowid_range(
4199 l_start_rowid,
4200 l_end_rowid,
4201 l_any_rows_to_process,
4202 p_batch_size,
4203 TRUE);
4204
4205
4206 WHILE (l_any_rows_to_process)
4207 LOOP**/
4208
4209 FND_FILE.PUT_LINE
4210 (
4211 FND_FILE.LOG,
4212 'Creating Zip Ranges.'
4213 );
4214
4215 INSERT ALL
4216 WHEN (1=1) THEN
4217 INTO HZ_GEOGRAPHIES
4218 (
4219 GEOGRAPHY_ID,
4220 OBJECT_VERSION_NUMBER,
4221 GEOGRAPHY_TYPE,
4222 GEOGRAPHY_NAME,
4223 GEOGRAPHY_USE,
4224 GEOGRAPHY_CODE,
4225 START_DATE,
4226 END_DATE,
4227 MULTIPLE_PARENT_FLAG,
4228 geography_element1,
4229 geography_element1_id,
4230 geography_element1_code,
4231 geography_element2,
4232 geography_element2_id,
4233 geography_element2_code,
4234 geography_element3,
4235 geography_element3_id,
4236 geography_element4,
4237 geography_element4_id,
4238 geography_element4_code,
4239 geography_element5,
4240 geography_element5_id,
4241 geography_element5_code,
4242 CREATED_BY_MODULE,
4243 COUNTRY_CODE,
4244 TIMEZONE_CODE,
4245 LAST_UPDATED_BY,
4246 CREATION_DATE,
4247 CREATED_BY,
4248 LAST_UPDATE_DATE,
4249 LAST_UPDATE_LOGIN
4250 )
4251 VALUES
4252 (
4253 hz_geographies_s.nextval,
4254 1,
4255 geography_type,
4256 geography_name,
4257 'MASTER_REF',
4258 geography_code,
4259 start_date,
4260 end_date,
4261 'N',
4262 geography_element1,
4263 geography_element1_id,
4267 geography_element2_code,
4264 geography_element1_code,
4265 geography_element2,
4266 geography_element2_id,
4268 geography_element3,
4269 geography_element3_id,
4270 geography_element4,
4271 geography_element4_id,
4272 geography_element4_code,
4273 geography_name,
4274 hz_geographies_s.nextval,
4275 null,
4276 G_CREATED_BY_MODULE,
4277 country_code,
4278 'PST',
4279 fnd_global.user_id,
4280 sysdate,
4281 fnd_global.user_id,
4282 sysdate,
4283 fnd_global.conc_login_id
4284 )
4285 INTO HZ_GEOGRAPHY_IDENTIFIERS
4286 (
4287 GEOGRAPHY_ID,
4288 GEO_DATA_PROVIDER,
4289 IDENTIFIER_SUBTYPE,
4290 IDENTIFIER_VALUE,
4291 OBJECT_VERSION_NUMBER,
4292 IDENTIFIER_TYPE,
4293 PRIMARY_FLAG,
4294 LANGUAGE_CODE,
4295 GEOGRAPHY_USE,
4296 GEOGRAPHY_TYPE,
4297 CREATED_BY_MODULE,
4298 LAST_UPDATED_BY,
4299 CREATION_DATE,
4300 CREATED_BY,
4301 LAST_UPDATE_DATE,
4302 LAST_UPDATE_LOGIN
4303 )
4304 VALUES
4305 (
4306 hz_geographies_s.nextval,
4307 p_tax_content_source,
4308 'STANDARD_NAME',
4309 geography_name,
4310 1,
4311 'NAME',
4312 'Y',
4313 'US',
4314 'MASTER_REF',
4315 geography_type,
4316 G_CREATED_BY_MODULE,
4317 fnd_global.user_id,
4318 sysdate,
4319 fnd_global.user_id,
4320 sysdate,
4321 fnd_global.conc_login_id
4322 )
4323 INTO HZ_GEOGRAPHY_IDENTIFIERS
4324 (
4325 GEOGRAPHY_ID,
4326 GEO_DATA_PROVIDER,
4327 IDENTIFIER_SUBTYPE,
4328 IDENTIFIER_VALUE,
4329 OBJECT_VERSION_NUMBER,
4330 IDENTIFIER_TYPE,
4331 PRIMARY_FLAG,
4332 LANGUAGE_CODE,
4333 GEOGRAPHY_USE,
4334 GEOGRAPHY_TYPE,
4335 CREATED_BY_MODULE,
4336 LAST_UPDATED_BY,
4337 CREATION_DATE,
4338 CREATED_BY,
4339 LAST_UPDATE_DATE,
4340 LAST_UPDATE_LOGIN
4341 )
4342 VALUES
4343 (
4344 hz_geographies_s.nextval,
4345 p_tax_content_source,
4346 'FIPS_CODE',
4347 geography_code,
4348 1,
4349 'CODE',
4350 'Y',
4351 'US',
4352 'MASTER_REF',
4353 geography_type,
4354 G_CREATED_BY_MODULE,
4355 fnd_global.user_id,
4356 sysdate,
4357 fnd_global.user_id,
4358 sysdate,
4359 fnd_global.conc_login_id
4360 )
4361 INTO HZ_RELATIONSHIPS
4362 (
4363 RELATIONSHIP_ID,
4364 SUBJECT_ID,
4365 SUBJECT_TYPE,
4366 SUBJECT_TABLE_NAME,
4367 OBJECT_ID,
4368 OBJECT_TYPE,
4369 OBJECT_TABLE_NAME,
4370 RELATIONSHIP_CODE,
4371 DIRECTIONAL_FLAG,
4372 COMMENTS,
4373 START_DATE,
4374 END_DATE,
4375 STATUS,
4376 CREATED_BY,
4377 CREATION_DATE,
4378 LAST_UPDATED_BY,
4379 LAST_UPDATE_DATE,
4380 LAST_UPDATE_LOGIN,
4381 CONTENT_SOURCE_TYPE,
4382 RELATIONSHIP_TYPE,
4383 OBJECT_VERSION_NUMBER,
4384 CREATED_BY_MODULE,
4385 APPLICATION_ID,
4386 DIRECTION_CODE,
4387 PERCENTAGE_OWNERSHIP,
4388 ACTUAL_CONTENT_SOURCE
4389 )
4390 VALUES
4391 (
4392 hz_relationships_s.nextval,
4393 parent_geography_id,
4394 parent_geography_type,
4395 'HZ_GEOGRAPHIES',
4396 hz_geographies_s.nextval,
4397 geography_type,
4398 'HZ_GEOGRAPHIES',
4399 'PARENT_OF',
4400 'F',
4401 null,
4402 start_date,
4403 end_date,
4404 'A',
4405 fnd_global.user_id,
4406 sysdate,
4407 fnd_global.user_id,
4408 sysdate,
4409 fnd_global.conc_login_id,
4410 G_CREATED_BY_MODULE,
4411 'MASTER_REF',
4412 1,
4413 G_CREATED_BY_MODULE,
4414 null,
4415 'P',
4416 null,
4417 p_tax_content_source
4418 )
4419 INTO HZ_RELATIONSHIPS
4420 (
4421 RELATIONSHIP_ID,
4422 SUBJECT_ID,
4423 SUBJECT_TYPE,
4424 SUBJECT_TABLE_NAME,
4425 OBJECT_ID,
4426 OBJECT_TYPE,
4427 OBJECT_TABLE_NAME,
4428 RELATIONSHIP_CODE,
4429 DIRECTIONAL_FLAG,
4430 COMMENTS,
4431 START_DATE,
4432 END_DATE,
4433 STATUS,
4434 CREATED_BY,
4435 CREATION_DATE,
4439 CONTENT_SOURCE_TYPE,
4436 LAST_UPDATED_BY,
4437 LAST_UPDATE_DATE,
4438 LAST_UPDATE_LOGIN,
4440 RELATIONSHIP_TYPE,
4441 OBJECT_VERSION_NUMBER,
4442 CREATED_BY_MODULE,
4443 APPLICATION_ID,
4444 DIRECTION_CODE,
4445 PERCENTAGE_OWNERSHIP,
4446 ACTUAL_CONTENT_SOURCE
4447 )
4448 VALUES
4449 (
4450 hz_relationships_s.nextval,
4451 hz_geographies_s.nextval,
4452 geography_type,
4453 'HZ_GEOGRAPHIES',
4454 parent_geography_id,
4455 parent_geography_type,
4456 'HZ_GEOGRAPHIES',
4457 'CHILD_OF',
4458 'B',
4459 null,
4460 start_date,
4461 end_date,
4462 'A',
4463 fnd_global.user_id,
4464 sysdate,
4465 fnd_global.user_id,
4466 sysdate,
4467 fnd_global.conc_login_id,
4468 G_CREATED_BY_MODULE,
4469 'MASTER_REF',
4470 1,
4471 G_CREATED_BY_MODULE,
4472 null,
4473 'C',
4474 null,
4475 p_tax_content_source
4476 )
4477 --Self
4478 INTO hz_hierarchy_nodes
4479 (
4480 HIERARCHY_TYPE,
4481 PARENT_ID,
4482 PARENT_TABLE_NAME,
4483 PARENT_OBJECT_TYPE,
4484 CHILD_ID,
4485 CHILD_TABLE_NAME,
4486 CHILD_OBJECT_TYPE,
4487 LEVEL_NUMBER,
4488 TOP_PARENT_FLAG,
4489 LEAF_CHILD_FLAG,
4490 EFFECTIVE_START_DATE,
4491 EFFECTIVE_END_DATE,
4492 STATUS,
4493 RELATIONSHIP_ID,
4494 CREATED_BY,
4495 CREATION_DATE,
4496 LAST_UPDATED_BY,
4497 LAST_UPDATE_DATE,
4498 LAST_UPDATE_LOGIN,
4499 ACTUAL_CONTENT_SOURCE
4500 )
4501 VALUES
4502 (
4503 'MASTER_REF',
4504 hz_geographies_s.nextval,
4505 'HZ_GEOGRAPHIES',
4506 geography_type,
4507 hz_geographies_s.nextval,
4508 'HZ_GEOGRAPHIES',
4509 geography_type,
4510 0 ,
4511 'N',
4512 'Y',
4513 start_date,
4514 end_date,
4515 'A',
4516 null,
4517 fnd_global.user_id,
4518 sysdate,
4519 fnd_global.user_id,
4520 sysdate,
4521 fnd_global.conc_login_id,
4522 p_tax_content_source
4523 )
4524 -- City
4525 INTO hz_hierarchy_nodes
4526 (
4527 HIERARCHY_TYPE,
4528 PARENT_ID,
4529 PARENT_TABLE_NAME,
4530 PARENT_OBJECT_TYPE,
4531 CHILD_ID,
4532 CHILD_TABLE_NAME,
4533 CHILD_OBJECT_TYPE,
4534 LEVEL_NUMBER,
4535 TOP_PARENT_FLAG,
4536 LEAF_CHILD_FLAG,
4537 EFFECTIVE_START_DATE,
4538 EFFECTIVE_END_DATE,
4539 STATUS,
4540 RELATIONSHIP_ID,
4541 CREATED_BY,
4542 CREATION_DATE,
4543 LAST_UPDATED_BY,
4544 LAST_UPDATE_DATE,
4545 LAST_UPDATE_LOGIN,
4546 ACTUAL_CONTENT_SOURCE
4547 )
4548 VALUES
4549 (
4550 'MASTER_REF',
4551 parent_geography_id,
4552 'HZ_GEOGRAPHIES',
4553 parent_geography_type,
4554 hz_geographies_s.nextval,
4555 'HZ_GEOGRAPHIES',
4556 geography_type,
4557 1,
4558 '',
4559 '',
4560 start_date,
4561 end_date,
4562 'A',
4563 hz_relationships_s.nextval,
4564 fnd_global.user_id,
4565 sysdate,
4566 fnd_global.user_id,
4567 sysdate,
4568 fnd_global.conc_login_id,
4569 p_tax_content_source
4570 )
4571 -- County
4572 WHEN (geography_element3_id IS NOT NULL) THEN
4573 INTO hz_hierarchy_nodes
4574 (
4575 HIERARCHY_TYPE,
4576 PARENT_ID,
4577 PARENT_TABLE_NAME,
4578 PARENT_OBJECT_TYPE,
4579 CHILD_ID,
4580 CHILD_TABLE_NAME,
4581 CHILD_OBJECT_TYPE,
4582 LEVEL_NUMBER,
4583 TOP_PARENT_FLAG,
4584 LEAF_CHILD_FLAG,
4585 EFFECTIVE_START_DATE,
4586 EFFECTIVE_END_DATE,
4587 STATUS,
4588 RELATIONSHIP_ID,
4589 CREATED_BY,
4590 CREATION_DATE,
4591 LAST_UPDATED_BY,
4592 LAST_UPDATE_DATE,
4593 LAST_UPDATE_LOGIN,
4594 ACTUAL_CONTENT_SOURCE
4595 )
4596 VALUES
4597 (
4598 'MASTER_REF',
4599 geography_element3_id,
4600 'HZ_GEOGRAPHIES',
4601 'COUNTY',
4602 hz_geographies_s.nextval,
4603 'HZ_GEOGRAPHIES',
4604 geography_type,
4605 2 ,
4606 '',
4607 '',
4608 start_date,
4609 end_date,
4610 'A',
4611 null ,
4615 sysdate,
4612 fnd_global.user_id,
4613 sysdate,
4614 fnd_global.user_id,
4616 fnd_global.conc_login_id,
4617 p_tax_content_source
4618 )
4619 WHEN (geography_element2_id IS NOT NULL) THEN
4620 INTO hz_hierarchy_nodes
4621 (
4622 HIERARCHY_TYPE,
4623 PARENT_ID,
4624 PARENT_TABLE_NAME,
4625 PARENT_OBJECT_TYPE,
4626 CHILD_ID,
4627 CHILD_TABLE_NAME,
4628 CHILD_OBJECT_TYPE,
4629 LEVEL_NUMBER,
4630 TOP_PARENT_FLAG,
4631 LEAF_CHILD_FLAG,
4632 EFFECTIVE_START_DATE,
4633 EFFECTIVE_END_DATE,
4634 STATUS,
4635 RELATIONSHIP_ID,
4636 CREATED_BY,
4637 CREATION_DATE,
4638 LAST_UPDATED_BY,
4639 LAST_UPDATE_DATE,
4640 LAST_UPDATE_LOGIN,
4641 ACTUAL_CONTENT_SOURCE
4642 )
4643 VALUES
4644 (
4645 'MASTER_REF',
4646 geography_element2_id,
4647 'HZ_GEOGRAPHIES',
4648 'STATE',
4649 hz_geographies_s.nextval,
4650 'HZ_GEOGRAPHIES',
4651 geography_type,
4652 3 ,
4653 '',
4654 '',
4655 start_date,
4656 end_date,
4657 'A',
4658 null ,
4659 fnd_global.user_id,
4660 sysdate,
4661 fnd_global.user_id,
4662 sysdate,
4663 fnd_global.conc_login_id,
4664 p_tax_content_source
4665 )
4666 WHEN (geography_element1_id IS NOT NULL) THEN
4667 INTO hz_hierarchy_nodes
4668 (
4669 HIERARCHY_TYPE,
4670 PARENT_ID,
4671 PARENT_TABLE_NAME,
4672 PARENT_OBJECT_TYPE,
4673 CHILD_ID,
4674 CHILD_TABLE_NAME,
4675 CHILD_OBJECT_TYPE,
4676 LEVEL_NUMBER,
4677 TOP_PARENT_FLAG,
4678 LEAF_CHILD_FLAG,
4679 EFFECTIVE_START_DATE,
4680 EFFECTIVE_END_DATE,
4681 STATUS,
4682 RELATIONSHIP_ID,
4683 CREATED_BY,
4684 CREATION_DATE,
4685 LAST_UPDATED_BY,
4686 LAST_UPDATE_DATE,
4687 LAST_UPDATE_LOGIN,
4688 ACTUAL_CONTENT_SOURCE
4689 )
4690 VALUES
4691 (
4692 'MASTER_REF',
4693 geography_element1_id,
4694 'HZ_GEOGRAPHIES',
4695 'COUNTRY',
4696 hz_geographies_s.nextval,
4697 'HZ_GEOGRAPHIES',
4698 geography_type,
4699 4 ,
4700 '',
4701 '',
4702 start_date,
4703 end_date,
4704 'A',
4705 null ,
4706 fnd_global.user_id,
4707 sysdate,
4708 fnd_global.user_id,
4709 sysdate,
4710 fnd_global.conc_login_id,
4711 p_tax_content_source
4712 )
4713
4714 select v.zip_code geography_name,
4715 v.zip_code geography_code,
4716 'POSTAL_CODE' geography_type,
4717 --v.start_date,
4718 --v.end_date,
4719 MIN(v.start_date) start_date,
4720 MAX(v.end_date) end_date,
4721 g.geography_id parent_geography_id,
4722 g.geography_type parent_geography_type,
4723 g.geography_element1,
4724 g.geography_element1_id,
4725 g.geography_element1_code,
4726 g.geography_element2,
4727 g.geography_element2_id,
4728 g.geography_element2_code,
4729 g.geography_element3,
4730 g.geography_element3_id,
4731 g.geography_element3_code,
4732 g.geography_element4,
4733 g.geography_element4_id,
4734 g.geography_element4_code,
4735 g.country_code
4736 from
4737 (
4738 select geography_id,
4739 effective_from start_date,
4740 nvl(effective_to,to_date('12/31/4712','mm/dd/yyyy')) end_date,
4741 from_code,
4742 to_code,
4743 trim(to_char(val,'09999')) zip_code,cnt
4744 from (
4745 select distinct
4746 geography_id,
4747 effective_from,
4748 effective_to,
4749 zip_begin,
4750 zip_end
4751 from zx_data_upload_interface
4752 where record_type = 08
4753 and last_updation_version > p_last_run_version
4754 and city_jurisdiction_code is not null
4755 and geography_id is not null
4756 and effective_to is null
4757 and nvl(status,'CREATE') <> 'ERROR'
4758 )
4759 model
4760 partition by (geography_id,zip_begin,zip_end,effective_from,effective_to)
4761 dimension by (0 as attr)
4762 measures (0 as val,
4763 to_number(zip_begin) as from_code,
4764 to_number(zip_end) as to_code,
4765 (to_number(zip_end)-to_number(zip_begin)+1) as cnt
4766 )
4767 rules iterate (200)
4768 until (iteration_number+1 >= cnt[0])
4769 (
4770 val[iteration_number] = from_code[0]+iteration_number
4771 )
4772 ) v,
4773 hz_geographies g
4774 WHERE v.geography_id = g.geography_id
4775 AND g.country_code = 'US'
4776 AND NOT EXISTS ( SELECT /*+ordered */'1'
4777 FROM hz_geographies g1,
4778 hz_relationships rel
4779 WHERE rel.subject_id = g.geography_id
4780 AND rel.subject_type = g.geography_type
4781 AND rel.subject_table_name = 'HZ_GEOGRAPHIES'
4782 AND rel.object_id = g1.geography_id
4783 AND rel.object_type = 'POSTAL_CODE'
4784 AND rel.object_table_name = 'HZ_GEOGRAPHIES'
4785 AND g1.geography_code = v.zip_code
4786 AND g1.geography_type = 'POSTAL_CODE'
4787 AND rel.relationship_type = 'MASTER_REF')
4788 GROUP BY v.zip_code ,
4789 v.zip_code ,
4790 g.geography_id ,
4791 g.geography_type ,
4792 g.geography_element1,
4793 g.geography_element1_id,
4794 g.geography_element1_code,
4795 g.geography_element2,
4796 g.geography_element2_id,
4797 g.geography_element2_code,
4798 g.geography_element3,
4799 g.geography_element3_id,
4800 g.geography_element3_code,
4801 g.geography_element4,
4802 g.geography_element4_id,
4803 g.geography_element4_code,
4804 g.country_code;
4805
4806 FND_FILE.PUT_LINE
4807 (
4808 FND_FILE.LOG,
4809 'Records Inserted: '||TO_CHAR(SQL%ROWCOUNT)
4810 );
4811
4812 l_rows_processed := SQL%ROWCOUNT;
4813
4814 COMMIT;
4815
4816 /**ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_end_rowid);
4817
4818 COMMIT;
4819 --
4820 -- get new range of ids
4821 --
4822 ad_parallel_updates_pkg.get_rowid_range
4823 (
4824 l_start_rowid,
4825 l_end_rowid,
4826 l_any_rows_to_process,
4827 P_batch_size,
4828 FALSE
4829 );
4830
4831 END LOOP;**/
4832
4833 EXCEPTION
4834
4835 WHEN NO_DATA_FOUND THEN
4836 retcode := '1';
4837 errbuf := 'No data found';
4838 FND_FILE.PUT_LINE
4839 (
4840 FND_FILE.LOG,
4841 'Error in '||l_api_name||': '||errbuf
4842 );
4843
4844 WHEN OTHERS THEN
4845 ROLLBACK;
4846 retcode := '2';
4847 errbuf := SQLERRM;
4848 FND_FILE.PUT_LINE
4849 (
4850 FND_FILE.LOG,
4851 'Unexpected Error in '||l_api_name||': '||errbuf
4852 );
4853
4854 END CREATE_ZIP;
4855
4856 --
4857 -- Procedure to create geography identifiers for alternate city names
4858 --
4859 PROCEDURE CREATE_ALTERNATE_CITIES
4860 (
4861 errbuf OUT NOCOPY VARCHAR2,
4862 retcode OUT NOCOPY VARCHAR2,
4863 p_tax_content_source IN VARCHAR2,
4864 p_last_run_version IN NUMBER
4865 ) IS
4866
4867 l_api_name CONSTANT VARCHAR2(30):= 'create_alternate_cities';
4868
4869 -----------------------------------------------------
4870 -- Ad parallelization variables
4871 -----------------------------------------------------
4872 l_table_owner VARCHAR2(30) := 'ZX';
4873 l_any_rows_to_process BOOLEAN;
4874 l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
4875 l_start_rowid ROWID;
4876 l_end_rowid ROWID;
4877 l_rows_processed NUMBER;
4878
4879
4880 BEGIN
4881
4882 retcode := '0';
4883
4884 /**--
4885 -- Initialize the rowid ranges
4886 --
4887 ad_parallel_updates_pkg.initialize_rowid_range(
4888 ad_parallel_updates_pkg.ROWID_RANGE,
4889 l_table_owner,
4890 l_table_name,
4891 p_script_name,
4892 p_Worker_Id,
4893 p_Num_Workers,
4894 p_batch_size, 0);
4895 --
4896 -- Get rowid ranges
4897 --
4898 ad_parallel_updates_pkg.get_rowid_range(
4899 l_start_rowid,
4900 l_end_rowid,
4901 l_any_rows_to_process,
4902 p_batch_size,
4903 TRUE);
4904
4905
4906 WHILE (l_any_rows_to_process)
4907 LOOP**/
4908
4909 FND_FILE.PUT_LINE
4910 (
4911 FND_FILE.LOG,
4912 'Creating Alternate Cities.'
4913 );
4914
4915 INSERT ALL
4916 INTO HZ_GEOGRAPHY_IDENTIFIERS
4917 (
4918 GEOGRAPHY_ID,
4919 GEO_DATA_PROVIDER,
4920 IDENTIFIER_SUBTYPE,
4921 IDENTIFIER_VALUE,
4922 OBJECT_VERSION_NUMBER,
4923 IDENTIFIER_TYPE,
4924 PRIMARY_FLAG,
4925 LANGUAGE_CODE,
4926 GEOGRAPHY_USE,
4927 GEOGRAPHY_TYPE,
4928 CREATED_BY_MODULE,
4929 LAST_UPDATED_BY,
4930 CREATION_DATE,
4931 CREATED_BY,
4932 LAST_UPDATE_DATE,
4933 LAST_UPDATE_LOGIN
4934 )
4935 VALUES
4936 (
4937 geography_id,
4938 p_tax_content_source,
4939 'STANDARD_NAME',
4940 geography_name,
4941 1,
4942 'NAME',
4943 'N',
4944 'US',
4945 'MASTER_REF',
4946 geography_type,
4947 G_CREATED_BY_MODULE,
4948 fnd_global.user_id,
4949 sysdate,
4950 fnd_global.user_id,
4951 sysdate,
4952 fnd_global.conc_login_id
4953 )
4954 SELECT DISTINCT inter.geography_id,
4955 inter.geography_name geography_name,
4956 'CITY' geography_type
4957 FROM ZX_DATA_UPLOAD_INTERFACE inter
4958 WHERE inter.record_type = 07
4959 AND inter.last_updation_version > p_last_run_version
4960 AND inter.geography_id IS NOT NULL
4961 AND NOT EXISTS (SELECT 1
4962 FROM HZ_GEOGRAPHY_IDENTIFIERS hgi
4963 WHERE hgi.geography_id = inter.geography_id
4964 AND hgi.IDENTIFIER_TYPE = 'NAME'
4965 AND hgi.IDENTIFIER_SUBTYPE = 'STANDARD_NAME'
4966 AND UPPER(hgi.IDENTIFIER_VALUE) = UPPER(inter.geography_name)
4967 AND hgi.LANGUAGE_CODE = 'US');
4968
4969 FND_FILE.PUT_LINE
4970 (
4971 FND_FILE.LOG,
4972 'Records Inserted: '||TO_CHAR(SQL%ROWCOUNT)
4973 );
4974
4975 l_rows_processed := SQL%ROWCOUNT;
4976
4977 COMMIT;
4978
4979 /**ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_end_rowid);
4980
4981 COMMIT;
4982 --
4983 -- get new range of ids
4984 --
4985 ad_parallel_updates_pkg.get_rowid_range
4986 (
4987 l_start_rowid,
4988 l_end_rowid,
4989 l_any_rows_to_process,
4990 P_batch_size,
4991 FALSE
4992 );
4993
4994 END LOOP;**/
4995
4996 EXCEPTION
4997
4998 WHEN NO_DATA_FOUND THEN
4999 retcode := '1';
5000 errbuf := 'No data found';
5001 FND_FILE.PUT_LINE
5002 (
5003 FND_FILE.LOG,
5004 'Error in '||l_api_name||': '||errbuf
5005 );
5006
5007 WHEN OTHERS THEN
5008 ROLLBACK;
5009 retcode := '2';
5010 errbuf := SQLERRM;
5011 FND_FILE.PUT_LINE
5012 (
5013 FND_FILE.LOG,
5014 'Unexpected Error in '||l_api_name||': '||errbuf
5015 );
5016
5017 END CREATE_ALTERNATE_CITIES;
5018
5019 --
5020 -- Procedure to create rates
5021 --
5022 PROCEDURE CREATE_RATES
5023 (
5024 errbuf OUT NOCOPY VARCHAR2,
5025 retcode OUT NOCOPY VARCHAR2,
5026 p_tax_content_source IN VARCHAR2,
5027 p_last_run_version IN NUMBER,
5028 p_tax_regime_code IN VARCHAR2
5029 ) IS
5030
5031 l_api_name CONSTANT VARCHAR2(30):= 'create_rates';
5032
5033 CURSOR c_get_regime_migrated
5034 (
5035 b_regime_code VARCHAR2
5036 ) IS
5037 SELECT decode(record_type_code,'MIGRATED','Y','N')
5038 FROM zx_regimes_b
5039 WHERE tax_regime_code = b_regime_code;
5040
5041 l_table_owner VARCHAR2(30) := 'ZX';
5042 l_any_rows_to_process BOOLEAN;
5043 l_table_name VARCHAR2(30) := 'ZX_DATA_UPLOAD_INTERFACE';
5044 l_start_rowid ROWID;
5045 l_end_rowid ROWID;
5046 l_rows_processed NUMBER;
5047 l_migrated_tax_regime_flag VARCHAR2(1);
5048
5049
5050 BEGIN
5051
5052 retcode := '0';
5053
5054 OPEN c_get_regime_migrated(p_tax_regime_code);
5055 FETCH c_get_regime_migrated
5056 INTO l_migrated_tax_regime_flag;
5057 CLOSE c_get_regime_migrated;
5058
5059 /**--
5060 -- Initialize the rowid ranges
5061 --
5062 ad_parallel_updates_pkg.initialize_rowid_range(
5063 ad_parallel_updates_pkg.ROWID_RANGE,
5064 l_table_owner,
5065 l_table_name,
5066 p_script_name,
5067 p_Worker_Id,
5068 p_Num_Workers,
5069 p_batch_size, 0);
5070 --
5071 -- Get rowid ranges
5072 --
5073 ad_parallel_updates_pkg.get_rowid_range(
5074 l_start_rowid,
5075 l_end_rowid,
5076 l_any_rows_to_process,
5077 p_batch_size,
5078 TRUE);
5079
5080
5081 WHILE (l_any_rows_to_process)
5082 LOOP**/
5083
5084 FND_FILE.PUT_LINE
5085 (
5086 FND_FILE.LOG,
5087 'Creating/Updating Tax Rates - 1.'
5088 );
5089
5090 MERGE INTO ZX_RATES_B_TMP zrbt
5091 USING (SELECT tax_regime_code,
5092 tax,
5093 content_owner_id,
5094 tax_status_code,
5095 tax_jurisdiction_code,
5096 tax_rate_code,
5097 effective_from,
5098 effective_to,
5099 rate_type_code,
5100 percentage_rate,
5101 active_flag,
5102 default_rate_flag,
5103 RATE_COUNT
5104 FROM
5105 (SELECT DISTINCT
5106 p_tax_regime_code tax_regime_code,
5107 decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level) tax,
5108 -99 content_owner_id,
5109 'STANDARD' tax_status_code,
5110 DECODE(decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level),
5111 'STATE',decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
5112 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),
5113 'CITY','CI-')||
5114 decode(p_tax_content_source,
5115 'TAXWARE',decode(to_char(jur.record_type),
5116 '1',jur.COUNTRY_STATE_ABBREVIATION,
5117 '3',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,21)),
5118 '6',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||jur.city_jurisdiction_code),
5119 'VERTEX',DECODE(to_char(jur.record_type),
5120 '1',jur.state_jurisdiction_code||'0000000',
5121 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5122 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5123 'OTHERS',DECODE(to_char(jur.record_type),
5124 '1',jur.state_jurisdiction_code||'0000000',
5125 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5126 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5127 DECODE(to_char(jur.record_type),
5128 '1',jur.state_jurisdiction_code||'0000000',
5129 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5130 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||TRIM(SUBSTR(jur.city_jurisdiction_code,1,19)))
5131 ) tax_jurisdiction_code,
5132 decode(l_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(rt.record_type),'10','-RENTAL','11','-USE','12','-LEASE') tax_rate_code,
5133 decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START) effective_from,
5134 rt.effective_to,
5135 'PERCENTAGE' rate_type_code,
5136 decode(rt.record_type,9,rt.sales_tax_rate,10,rt.rental_tax_rate,11,rt.use_tax_rate,12,rt.lease_tax_rate) percentage_rate,
5137 decode(to_char(rt.record_type),'9',rt.sales_tax_rate_active_flag,'10',rt.rental_tax_rate_active_flag,'11',rt.use_tax_rate_active_flag,'12',rt.lease_tax_rate_active_flag) active_flag,
5138 decode(to_char(rt.record_type),'9','Y','N') default_rate_flag,
5139 count(*)
5140 OVER (PARTITION BY
5141 p_tax_regime_code,
5142 decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level) ,
5143 -99 ,
5144 'STANDARD' ,
5145 DECODE(decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level),
5146 'STATE',decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
5147 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),
5148 'CITY','CI-')||
5149 decode(p_tax_content_source,
5150 'TAXWARE',decode(to_char(jur.record_type),
5151 '1',jur.COUNTRY_STATE_ABBREVIATION,
5152 '3',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,21)),
5153 '6',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||jur.city_jurisdiction_code),
5154 'VERTEX',DECODE(to_char(jur.record_type),
5155 '1',jur.state_jurisdiction_code||'0000000',
5156 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5157 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5158 'OTHERS',DECODE(to_char(jur.record_type),
5159 '1',jur.state_jurisdiction_code||'0000000',
5160 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5161 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5162 DECODE(to_char(jur.record_type),
5163 '1',jur.state_jurisdiction_code||'0000000',
5164 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5165 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||TRIM(SUBSTR(jur.city_jurisdiction_code,1,19)))
5166 ) ,
5167 decode(l_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(rt.record_type),'10','-RENTAL','11','-USE','12','-LEASE') ,
5168 decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
5169 ) AS RATE_COUNT,
5170 (SELECT Count(*)
5171 FROM zx_rates_b
5172 WHERE TAX_REGIME_CODE = p_tax_regime_code
5173 AND tax = decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level)
5174 AND tax_status_code = 'STANDARD'
5175 AND content_owner_id = -99
5176 AND tax_jurisdiction_code = DECODE(decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level),
5177 'STATE',decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
5178 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),
5179 'CITY','CI-')||
5180 decode(p_tax_content_source,
5181 'TAXWARE',decode(to_char(jur.record_type),
5182 '1',jur.COUNTRY_STATE_ABBREVIATION,
5183 '3',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,21)),
5184 '6',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||jur.city_jurisdiction_code),
5185 'VERTEX',DECODE(to_char(jur.record_type),
5186 '1',jur.state_jurisdiction_code||'0000000',
5187 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5188 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5189 'OTHERS',DECODE(to_char(jur.record_type),
5190 '1',jur.state_jurisdiction_code||'0000000',
5191 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5192 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5193 DECODE(to_char(jur.record_type),
5194 '1',jur.state_jurisdiction_code||'0000000',
5195 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5196 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||TRIM(SUBSTR(jur.city_jurisdiction_code,1,19))))
5197 AND tax_rate_code = decode(l_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(rt.record_type),'10','-RENTAL','11','-USE','12','-LEASE')
5198 AND effective_from = decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
5199 ) l_count
5200 FROM zx_data_upload_interface rt,
5201 zx_data_upload_interface jur
5202 where rt.record_type in (9,10,11,12)
5203 and rt.last_updation_version > p_last_run_version
5204 and nvl(rt.status,'CREATE') <> 'ERROR'
5205 and jur.record_type = decode(rt.city_jurisdiction_code,null,decode(rt.county_jurisdiction_code,null,1,3),6)
5206 and jur.state_jurisdiction_code = rt.state_jurisdiction_code
5207 and nvl(jur.county_jurisdiction_code,'-1') = nvl(rt.county_jurisdiction_code,'-1')
5208 and nvl(jur.city_jurisdiction_code,'-1') = nvl(rt.city_jurisdiction_code,'-1')
5209 and nvl(jur.primary_flag,'Y') = 'Y'
5210 and jur.effective_to is null)
5211 where RATE_COUNT = 1
5212 and (l_count = 1 OR l_count = 0))v
5213 ON (zrbt.tax_regime_code = v.tax_regime_code
5214 and zrbt.content_owner_id = v.content_owner_id
5215 and zrbt.tax = v.tax
5216 and zrbt.tax_status_code = v.tax_status_code
5217 and zrbt.tax_jurisdiction_code = v.tax_jurisdiction_code
5218 and zrbt.tax_rate_code = v.tax_rate_code
5219 and zrbt.effective_from = v.effective_from)
5220 WHEN NOT MATCHED THEN
5221 INSERT
5222 (
5223 zrbt.TAX_RATE_ID,
5224 zrbt.OBJECT_VERSION_NUMBER,
5225 zrbt.TAX_RATE_CODE,
5226 zrbt.TAX_REGIME_CODE,
5227 zrbt.TAX,
5228 zrbt.TAX_STATUS_CODE,
5229 zrbt.TAX_JURISDICTION_CODE,
5230 zrbt.CONTENT_OWNER_ID,
5231 zrbt.ACTIVE_FLAG,
5232 zrbt.EFFECTIVE_FROM,
5233 zrbt.EFFECTIVE_TO,
5234 zrbt.DEFAULT_RATE_FLAG,
5235 zrbt.DEFAULT_FLG_EFFECTIVE_FROM,
5236 zrbt.DEFAULT_FLG_EFFECTIVE_TO,
5237 zrbt.RATE_TYPE_CODE,
5238 zrbt.PERCENTAGE_RATE,
5239 zrbt.ALLOW_EXEMPTIONS_FLAG,
5240 zrbt.ALLOW_EXCEPTIONS_FLAG,
5241 zrbt.RECORD_TYPE_CODE,
5242 zrbt.CREATED_BY,
5243 zrbt.CREATION_DATE,
5244 zrbt.LAST_UPDATED_BY,
5245 zrbt.LAST_UPDATE_DATE,
5246 zrbt.LAST_UPDATE_LOGIN
5247 )
5248 VALUES
5249 (
5250 zx_rates_b_s.nextval,
5251 1,
5252 v.TAX_RATE_CODE,
5253 v.TAX_REGIME_CODE,
5254 v.TAX,
5255 v.TAX_STATUS_CODE,
5256 v.TAX_JURISDICTION_CODE,
5257 v.CONTENT_OWNER_ID,
5258 v.ACTIVE_FLAG,
5259 v.EFFECTIVE_FROM,
5260 v.EFFECTIVE_TO,
5261 v.default_rate_flag,
5262 decode(v.default_rate_flag,'Y',v.EFFECTIVE_FROM,NULL),
5263 decode(v.default_rate_flag,'Y',v.EFFECTIVE_TO,NULL),
5264 v.RATE_TYPE_CODE,
5265 v.PERCENTAGE_RATE,
5266 'Y',
5267 'Y',
5268 G_CREATED_BY_MODULE,
5269 fnd_global.user_id,
5270 sysdate,
5271 fnd_global.user_id,
5272 sysdate,
5273 fnd_global.conc_login_id
5274 )
5275 WHEN MATCHED THEN
5276 UPDATE SET zrbt.PERCENTAGE_RATE = v.percentage_rate,
5277 zrbt.EFFECTIVE_TO = v.effective_to,
5278 zrbt.DEFAULT_FLG_EFFECTIVE_TO = v.effective_to,
5279 zrbt.ACTIVE_FLAG = v.active_flag,
5280 zrbt.LAST_UPDATED_BY = fnd_global.user_id,
5281 zrbt.LAST_UPDATE_DATE = sysdate,
5282 zrbt.LAST_UPDATE_LOGIN = fnd_global.conc_login_id;
5283
5284 FND_FILE.PUT_LINE
5285 (
5286 FND_FILE.LOG,
5287 'Records Processed: '||TO_CHAR(SQL%ROWCOUNT)
5288 );
5289
5290 l_rows_processed := SQL%ROWCOUNT;
5291
5292 -- added this to ensure that when we have two rate records with same effectivity, then
5293 -- active_flag is not updated as it needs to be included in the ON clause
5294 -- also need to update percentage_rate for this case.
5295
5296 FND_FILE.PUT_LINE
5297 (
5298 FND_FILE.LOG,
5299 'Creating/Updating Tax Rates - 2.'
5300 );
5301
5302 MERGE INTO ZX_RATES_B_TMP zrbt
5303 USING (SELECT tax_regime_code,
5304 tax,
5305 content_owner_id,
5306 tax_status_code,
5307 tax_jurisdiction_code,
5308 tax_rate_code,
5309 effective_from,
5310 effective_to,
5311 rate_type_code,
5312 percentage_rate,
5313 active_flag,
5314 default_rate_flag,
5315 RATE_COUNT
5316 FROM
5317 (SELECT DISTINCT
5318 p_tax_regime_code tax_regime_code,
5319 decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level) tax,
5320 -99 content_owner_id,
5321 'STANDARD' tax_status_code,
5322 DECODE(decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level),
5323 'STATE',decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
5324 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),
5325 'CITY','CI-')||
5326 decode(p_tax_content_source,
5327 'TAXWARE',decode(to_char(jur.record_type),
5328 '1',jur.COUNTRY_STATE_ABBREVIATION,
5329 '3',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,21)),
5330 '6',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||jur.city_jurisdiction_code),
5331 'VERTEX',DECODE(to_char(jur.record_type),
5332 '1',jur.state_jurisdiction_code||'0000000',
5333 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5334 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5335 'OTHERS',DECODE(to_char(jur.record_type),
5336 '1',jur.state_jurisdiction_code||'0000000',
5337 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5338 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5339 DECODE(to_char(jur.record_type),
5340 '1',jur.state_jurisdiction_code||'0000000',
5341 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5342 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||TRIM(SUBSTR(jur.city_jurisdiction_code,1,19)))
5343 ) tax_jurisdiction_code,
5344 decode(l_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(rt.record_type),'10','-RENTAL','11','-USE','12','-LEASE') tax_rate_code,
5345 decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START) effective_from,
5346 rt.effective_to,
5347 'PERCENTAGE' rate_type_code,
5348 decode(rt.record_type,9,rt.sales_tax_rate,10,rt.rental_tax_rate,11,rt.use_tax_rate,12,rt.lease_tax_rate) percentage_rate,
5349 decode(to_char(rt.record_type),'9',rt.sales_tax_rate_active_flag,'10',rt.rental_tax_rate_active_flag,'11',rt.use_tax_rate_active_flag,'12',rt.lease_tax_rate_active_flag) active_flag,
5350 decode(to_char(rt.record_type),'9','Y','N') default_rate_flag,
5351 count(*)
5352 OVER (PARTITION BY
5353 p_tax_regime_code,
5354 decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level) ,
5355 -99 ,
5356 'STANDARD' ,
5357 DECODE(decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level),
5358 'STATE',decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
5359 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),
5360 'CITY','CI-')||
5361 decode(p_tax_content_source,
5362 'TAXWARE',decode(to_char(jur.record_type),
5363 '1',jur.COUNTRY_STATE_ABBREVIATION,
5364 '3',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,21)),
5365 '6',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||jur.city_jurisdiction_code),
5366 'VERTEX',DECODE(to_char(jur.record_type),
5367 '1',jur.state_jurisdiction_code||'0000000',
5368 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5369 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5370 'OTHERS',DECODE(to_char(jur.record_type),
5371 '1',jur.state_jurisdiction_code||'0000000',
5372 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5373 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5374 DECODE(to_char(jur.record_type),
5375 '1',jur.state_jurisdiction_code||'0000000',
5376 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5377 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||TRIM(SUBSTR(jur.city_jurisdiction_code,1,19)))
5378 ) ,
5379 decode(l_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(rt.record_type),'10','-RENTAL','11','-USE','12','-LEASE') ,
5380 decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
5381 ) AS RATE_COUNT,
5382 (SELECT Count(*)
5383 FROM zx_rates_b
5384 WHERE TAX_REGIME_CODE = p_tax_regime_code
5385 AND tax = decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level)
5386 AND tax_status_code = 'STANDARD'
5387 AND content_owner_id = -99
5388 AND tax_jurisdiction_code = DECODE(decode(to_char(rt.record_type),'9',rt.sales_tax_authority_level,'10',rt.rental_tax_authority_level,'11',rt.use_tax_authority_level,'12',rt.lease_tax_authority_level),
5389 'STATE',decode(to_char(jur.record_type),'1','ST-','3','ST-CO-','6','ST-CI-'),
5390 'COUNTY',decode(to_char(jur.record_type),'3','CO-','6','CO-CI-'),
5391 'CITY','CI-')||
5392 decode(p_tax_content_source,
5393 'TAXWARE',decode(to_char(jur.record_type),
5394 '1',jur.COUNTRY_STATE_ABBREVIATION,
5395 '3',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,21)),
5396 '6',jur.COUNTRY_STATE_ABBREVIATION||'-'||UPPER(SUBSTRB(jur.GEOGRAPHY_NAME,1,12))||'-'||jur.city_jurisdiction_code),
5397 'VERTEX',DECODE(to_char(jur.record_type),
5398 '1',jur.state_jurisdiction_code||'0000000',
5399 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5400 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5401 'OTHERS',DECODE(to_char(jur.record_type),
5402 '1',jur.state_jurisdiction_code||'0000000',
5403 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5404 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||LPAD(jur.city_jurisdiction_code,4,'0')),
5405 DECODE(to_char(jur.record_type),
5406 '1',jur.state_jurisdiction_code||'0000000',
5407 '3',jur.state_jurisdiction_code||jur.county_jurisdiction_code||'0000',
5408 '6',jur.state_jurisdiction_code||jur.county_jurisdiction_code||TRIM(SUBSTR(jur.city_jurisdiction_code,1,19))))
5409 AND tax_rate_code = decode(l_migrated_tax_regime_flag,'Y','STANDARD','STD')||decode(to_char(rt.record_type),'10','-RENTAL','11','-USE','12','-LEASE')
5410 AND effective_from = decode(greatest(rt.effective_from,G_RECORD_EFFECTIVE_START),rt.effective_from,rt.effective_from,G_RECORD_EFFECTIVE_START)
5411 ) l_count
5412 FROM zx_data_upload_interface rt,
5413 zx_data_upload_interface jur
5414 where rt.record_type in (9,10,11,12)
5415 and rt.last_updation_version > p_last_run_version
5416 and nvl(rt.status,'CREATE') <> 'ERROR'
5417 and jur.record_type = decode(rt.city_jurisdiction_code,null,decode(rt.county_jurisdiction_code,null,1,3),6)
5418 and jur.state_jurisdiction_code = rt.state_jurisdiction_code
5419 and nvl(jur.county_jurisdiction_code,'-1') = nvl(rt.county_jurisdiction_code,'-1')
5420 and nvl(jur.city_jurisdiction_code,'-1') = nvl(rt.city_jurisdiction_code,'-1')
5421 and NVL(jur.primary_flag,'Y') = 'Y'
5422 and jur.effective_to is null)
5423 where RATE_COUNT > 1
5424 OR (l_count > 1 or l_count = 0))v
5425 ON (zrbt.tax_regime_code = v.tax_regime_code
5426 and zrbt.content_owner_id = v.content_owner_id
5427 and zrbt.tax = v.tax
5428 and zrbt.tax_status_code = v.tax_status_code
5429 and zrbt.tax_jurisdiction_code = v.tax_jurisdiction_code
5430 and zrbt.tax_rate_code = v.tax_rate_code
5431 and zrbt.effective_from = v.effective_from
5432 and zrbt.active_flag = v.active_flag
5433 )
5434 WHEN NOT MATCHED THEN
5435 INSERT
5436 (
5437 zrbt.TAX_RATE_ID,
5438 zrbt.OBJECT_VERSION_NUMBER,
5439 zrbt.TAX_RATE_CODE,
5440 zrbt.TAX_REGIME_CODE,
5441 zrbt.TAX,
5442 zrbt.TAX_STATUS_CODE,
5443 zrbt.TAX_JURISDICTION_CODE,
5444 zrbt.CONTENT_OWNER_ID,
5445 zrbt.ACTIVE_FLAG,
5446 zrbt.EFFECTIVE_FROM,
5447 zrbt.EFFECTIVE_TO,
5448 zrbt.DEFAULT_RATE_FLAG,
5449 zrbt.DEFAULT_FLG_EFFECTIVE_FROM,
5450 zrbt.DEFAULT_FLG_EFFECTIVE_TO,
5451 zrbt.RATE_TYPE_CODE,
5452 zrbt.PERCENTAGE_RATE,
5453 zrbt.ALLOW_EXEMPTIONS_FLAG,
5454 zrbt.ALLOW_EXCEPTIONS_FLAG,
5455 zrbt.RECORD_TYPE_CODE,
5456 zrbt.CREATED_BY,
5457 zrbt.CREATION_DATE,
5458 zrbt.LAST_UPDATED_BY,
5459 zrbt.LAST_UPDATE_DATE,
5460 zrbt.LAST_UPDATE_LOGIN
5461 )
5462 VALUES
5463 (
5464 zx_rates_b_s.nextval,
5465 1,
5466 v.TAX_RATE_CODE,
5467 v.TAX_REGIME_CODE,
5468 v.TAX,
5469 v.TAX_STATUS_CODE,
5470 v.TAX_JURISDICTION_CODE,
5471 v.CONTENT_OWNER_ID,
5472 v.ACTIVE_FLAG,
5473 v.EFFECTIVE_FROM,
5474 v.EFFECTIVE_TO,
5475 v.default_rate_flag,
5476 decode(v.default_rate_flag,'Y',v.EFFECTIVE_FROM,NULL),
5477 decode(v.default_rate_flag,'Y',v.EFFECTIVE_TO,NULL),
5478 v.RATE_TYPE_CODE,
5479 v.PERCENTAGE_RATE,
5480 'Y',
5481 'Y',
5482 G_CREATED_BY_MODULE,
5483 fnd_global.user_id,
5484 sysdate,
5485 fnd_global.user_id,
5486 sysdate,
5487 fnd_global.conc_login_id
5488 )
5489 WHEN MATCHED THEN
5490 UPDATE SET zrbt.PERCENTAGE_RATE = v.percentage_rate,
5491 zrbt.EFFECTIVE_TO = v.effective_to,
5492 zrbt.DEFAULT_FLG_EFFECTIVE_TO = v.effective_to,
5493 zrbt.LAST_UPDATED_BY = fnd_global.user_id,
5494 zrbt.LAST_UPDATE_DATE = sysdate,
5495 zrbt.LAST_UPDATE_LOGIN = fnd_global.conc_login_id;
5496
5497 FND_FILE.PUT_LINE
5498 (
5499 FND_FILE.LOG,
5500 'Records Processed: '||TO_CHAR(SQL%ROWCOUNT)
5501 );
5502
5503 l_rows_processed := SQL%ROWCOUNT;
5504
5505 -- Create rates with NULL Jurisdiction codes
5506 -- Used for Partner tax calculation.
5507
5508 FND_FILE.PUT_LINE
5509 (
5510 FND_FILE.LOG,
5511 'Creating Tax Rates with NULL Tax Jurisdiction.'
5512 );
5513
5514 INSERT ALL INTO ZX_RATES_B_TMP
5515 (
5516 TAX_RATE_ID,
5517 OBJECT_VERSION_NUMBER,
5518 TAX_RATE_CODE,
5519 TAX_REGIME_CODE,
5520 TAX,
5521 TAX_STATUS_CODE,
5522 TAX_JURISDICTION_CODE,
5523 CONTENT_OWNER_ID,
5524 ACTIVE_FLAG,
5525 EFFECTIVE_FROM,
5526 EFFECTIVE_TO,
5527 DEFAULT_RATE_FLAG,
5528 DEFAULT_FLG_EFFECTIVE_FROM,
5529 DEFAULT_FLG_EFFECTIVE_TO,
5530 RATE_TYPE_CODE,
5531 PERCENTAGE_RATE,
5532 ALLOW_EXEMPTIONS_FLAG,
5533 ALLOW_EXCEPTIONS_FLAG,
5534 RECORD_TYPE_CODE,
5535 CREATED_BY,
5536 CREATION_DATE,
5537 LAST_UPDATED_BY,
5538 LAST_UPDATE_DATE,
5539 LAST_UPDATE_LOGIN
5540 )
5541 VALUES
5542 (
5543 zx_rates_b_s.nextval,
5544 1,
5545 TAX,
5546 TAX_REGIME_CODE,
5547 TAX,
5548 'STANDARD',
5549 NULL,
5550 -99,
5551 'Y',
5552 decode(greatest(EFFECTIVE_FROM,G_RECORD_EFFECTIVE_START),EFFECTIVE_FROM,EFFECTIVE_FROM,G_RECORD_EFFECTIVE_START),
5553 NULL,
5554 'Y',
5555 decode(greatest(EFFECTIVE_FROM,G_RECORD_EFFECTIVE_START),EFFECTIVE_FROM,EFFECTIVE_FROM,G_RECORD_EFFECTIVE_START),
5556 NULL,
5557 'PERCENTAGE',
5558 0,
5559 'Y',
5560 'Y',
5561 G_CREATED_BY_MODULE,
5562 fnd_global.user_id,
5563 sysdate,
5564 fnd_global.user_id,
5565 sysdate,
5566 fnd_global.conc_login_id
5567 )
5568 SELECT tax.TAX_REGIME_CODE TAX_REGIME_CODE,
5569 tax.TAX TAX,
5570 tax.EFFECTIVE_FROM EFFECTIVE_FROM
5571 FROM ZX_TAXES_B tax
5572 WHERE tax.TAX_REGIME_CODE = p_tax_regime_code
5573 AND tax.RECORD_TYPE_CODE = G_CREATED_BY_MODULE
5574 AND tax.CONTENT_OWNER_ID = -99 -- Condition Added as a fix for Bug#8286647
5575 AND NOT EXISTS (SELECT 1 FROM ZX_RATES_B rate
5576 WHERE rate.TAX_RATE_CODE = tax.TAX
5577 AND rate.CONTENT_OWNER_ID = -99
5578 AND rate.TAX_JURISDICTION_CODE IS NULL
5579 AND rate.EFFECTIVE_FROM = tax.EFFECTIVE_FROM
5580 AND rate.ACTIVE_FLAG = 'Y');
5581
5582 FND_FILE.PUT_LINE
5583 (
5584 FND_FILE.LOG,
5585 'Records Inserted: '||TO_CHAR(SQL%ROWCOUNT)
5586 );
5587
5588 -- Copy accounts from jurisdiction
5589 -- Do this only for newly created rates
5590 -- Use TL table and regime/tax/record type combination to find new ones
5591 FND_FILE.PUT_LINE
5592 (
5593 FND_FILE.LOG,
5594 'Creating Tax Accounts for Tax Rates.'
5595 );
5596
5597 INSERT INTO ZX_ACCOUNTS
5598 (
5599 TAX_ACCOUNT_ID,
5600 OBJECT_VERSION_NUMBER,
5601 TAX_ACCOUNT_ENTITY_CODE,
5602 TAX_ACCOUNT_ENTITY_ID,
5603 LEDGER_ID,
5604 INTERNAL_ORGANIZATION_ID,
5605 TAX_ACCOUNT_CCID,
5606 INTERIM_TAX_CCID,
5607 NON_REC_ACCOUNT_CCID,
5608 ADJ_CCID,
5609 EDISC_CCID,
5610 UNEDISC_CCID,
5611 FINCHRG_CCID,
5612 ADJ_NON_REC_TAX_CCID,
5613 EDISC_NON_REC_TAX_CCID,
5614 UNEDISC_NON_REC_TAX_CCID,
5615 FINCHRG_NON_REC_TAX_CCID,
5616 RECORD_TYPE_CODE,
5617 CREATED_BY,
5618 CREATION_DATE,
5619 LAST_UPDATED_BY,
5620 LAST_UPDATE_DATE,
5621 LAST_UPDATE_LOGIN
5622 )
5623 SELECT
5624 zx_accounts_s.nextval,
5625 1,
5626 'RATES',
5627 zrb.TAX_RATE_ID,
5628 za.LEDGER_ID,
5629 za.INTERNAL_ORGANIZATION_ID,
5630 za.TAX_ACCOUNT_CCID,
5631 za.INTERIM_TAX_CCID,
5632 za.NON_REC_ACCOUNT_CCID,
5633 za.ADJ_CCID,
5634 za.EDISC_CCID,
5635 za.UNEDISC_CCID,
5636 za.FINCHRG_CCID,
5637 za.ADJ_NON_REC_TAX_CCID,
5638 za.EDISC_NON_REC_TAX_CCID,
5639 za.UNEDISC_NON_REC_TAX_CCID,
5640 za.FINCHRG_NON_REC_TAX_CCID,
5641 G_CREATED_BY_MODULE,
5642 fnd_global.user_id,
5643 sysdate,
5644 fnd_global.user_id,
5645 sysdate,
5646 fnd_global.conc_login_id
5647 FROM ZX_RATES_B zrb,
5648 ZX_JURISDICTIONS_B zjb,
5649 ZX_ACCOUNTS za
5650 WHERE zrb.TAX_REGIME_CODE = p_tax_regime_code
5651 AND zrb.TAX IN ('STATE','COUNTY','CITY')
5652 AND zrb.CONTENT_OWNER_ID = -99
5653 AND zrb.RECORD_TYPE_CODE = G_CREATED_BY_MODULE
5654 AND NOT EXISTS (SELECT NULL
5655 FROM ZX_RATES_TL zrt
5656 WHERE zrt.TAX_RATE_ID = zrb.TAX_RATE_ID)
5657 AND zjb.TAX_REGIME_CODE = zrb.TAX_REGIME_CODE
5658 AND zjb.TAX = zrb.TAX
5659 AND zjb.TAX_JURISDICTION_CODE = zrb.TAX_JURISDICTION_CODE
5660 AND za.TAX_ACCOUNT_ENTITY_CODE = 'JURISDICTION'
5661 AND za.TAX_ACCOUNT_ENTITY_ID = zjb.TAX_JURISDICTION_ID;
5662
5663 FND_FILE.PUT_LINE
5664 (
5665 FND_FILE.LOG,
5666 'Records Inserted: '||TO_CHAR(SQL%ROWCOUNT)
5667 );
5668
5669 FND_FILE.PUT_LINE
5670 (
5671 FND_FILE.LOG,
5672 'Creating TL for Tax Rates.'
5673 );
5674
5675 INSERT INTO ZX_RATES_TL
5676 (
5677 TAX_RATE_ID,
5678 TAX_RATE_NAME,
5679 CREATED_BY,
5680 CREATION_DATE,
5681 LAST_UPDATED_BY,
5682 LAST_UPDATE_DATE,
5683 LAST_UPDATE_LOGIN,
5684 LANGUAGE,
5685 SOURCE_LANG
5686 )
5687 SELECT zrb.TAX_RATE_ID,
5688 zrb.TAX_RATE_CODE,
5689 fnd_global.user_id,
5690 sysdate,
5691 fnd_global.user_id,
5692 sysdate,
5693 fnd_global.conc_login_id,
5694 fl.LANGUAGE_CODE,
5695 USERENV('LANG')
5696 FROM ZX_RATES_B zrb,
5697 FND_LANGUAGES fl
5698 WHERE fl.INSTALLED_FLAG IN ('I', 'B')
5699 AND zrb.TAX_REGIME_CODE = p_tax_regime_code
5700 AND zrb.CONTENT_OWNER_ID = -99
5701 AND zrb.TAX IN ('STATE','COUNTY','CITY')
5702 AND NOT EXISTS (SELECT NULL
5703 FROM ZX_RATES_TL zrt
5704 WHERE zrt.TAX_RATE_ID = zrb.TAX_RATE_ID
5705 AND zrt.LANGUAGE = fl.LANGUAGE_CODE);
5706
5707 FND_FILE.PUT_LINE
5708 (
5709 FND_FILE.LOG,
5710 'Records Inserted: '||TO_CHAR(SQL%ROWCOUNT)
5711 );
5712
5713 l_rows_processed := SQL%ROWCOUNT;
5714
5715 -- make the tax live for processing if there is atleast one rate defined for the tax
5716 UPDATE ZX_TAXES_B_TMP tax
5717 SET tax.LIVE_FOR_PROCESSING_FLAG = 'Y'
5718 WHERE tax.RECORD_TYPE_CODE = G_CREATED_BY_MODULE
5719 AND tax.TAX_REGIME_CODE = p_tax_regime_code
5720 AND tax.CONTENT_OWNER_ID = -99 -- Condition Added as a fix for Bug#8286647
5721 AND EXISTS (SELECT 1
5722 FROM ZX_RATES_B rate
5723 WHERE rate.TAX_REGIME_CODE = tax.TAX_REGIME_CODE
5724 AND rate.TAX = tax.TAX
5725 AND rate.CONTENT_OWNER_ID = -99 -- Condition Added as a fix for Bug#8286647
5726 AND rate.RECORD_TYPE_CODE = G_CREATED_BY_MODULE);
5727
5728 COMMIT;
5729
5730 /**ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_end_rowid);
5731
5732 COMMIT;
5733 --
5734 -- get new range of ids
5735 --
5736 ad_parallel_updates_pkg.get_rowid_range
5737 (
5738 l_start_rowid,
5739 l_end_rowid,
5740 l_any_rows_to_process,
5741 P_batch_size,
5742 FALSE
5743 );
5744
5745 END LOOP;**/
5746
5747 EXCEPTION
5748
5749 WHEN NO_DATA_FOUND THEN
5750 retcode := '1';
5751 errbuf := 'No data found';
5752 FND_FILE.PUT_LINE
5753 (
5754 FND_FILE.LOG,
5755 'Error in '||l_api_name||': '||errbuf
5756 );
5757
5758 WHEN OTHERS THEN
5759 ROLLBACK;
5760 retcode := '2';
5761 errbuf := SQLERRM;
5762 FND_FILE.PUT_LINE
5763 (
5764 FND_FILE.LOG,
5765 'Unexpected Error in '||l_api_name||': '||errbuf
5766 );
5767
5768 END CREATE_RATES;
5769
5770 --
5771 -- Procedure to pre-process interface data and call other programs
5772 --
5773 PROCEDURE PROCESS_DATA
5774 (
5775 errbuf OUT NOCOPY VARCHAR2,
5776 retcode OUT NOCOPY VARCHAR2,
5777 p_batch_size IN NUMBER,
5778 p_num_workers IN NUMBER,
5779 p_tax_content_source_id IN NUMBER,
5780 p_tax_regime_code IN VARCHAR2
5781 ) IS
5782
5783 l_api_name CONSTANT VARCHAR2(30):= 'process_data';
5784 l_error EXCEPTION;
5785
5786 CURSOR c_get_regime_migrated
5787 (
5788 b_regime_code VARCHAR2
5789 ) IS
5790 SELECT decode(record_type_code,'MIGRATED','Y','N')
5791 FROM zx_regimes_b
5792 WHERE tax_regime_code = b_regime_code;
5793
5794 CURSOR c_get_last_run
5795 (
5796 b_ptp_id NUMBER,
5797 b_regime_code VARCHAR2
5798 ) IS
5799 SELECT NVL(VERSION_LOADED,'0')
5800 FROM ZX_CONTENT_SOURCES
5801 WHERE PROVIDER_ID = b_ptp_id
5802 AND STANDARD_REGIME_CODE = b_regime_code;
5803
5804 CURSOR c_chk_ptp_party
5805 (
5806 b_ptp_id NUMBER
5807 ) IS
5808 SELECT UPPER(REPLACE(hzp.PARTY_NAME,' ','_'))
5809 FROM ZX_PARTY_TAX_PROFILE ptp,
5810 HZ_PARTIES hzp
5811 WHERE ptp.PARTY_TAX_PROFILE_ID = b_ptp_id
5812 AND ptp.PARTY_TYPE_CODE = 'TAX_PARTNER'
5813 AND ptp.PARTY_ID = hzp.PARTY_ID;
5814
5815 l_party_name HZ_PARTIES.PARTY_NAME%TYPE;
5816 l_migrated_tax_regime_flag VARCHAR2(1);
5817 l_tax_content_source VARCHAR2(80);
5818 l_tax_zone_type VARCHAR2(30);
5819 l_last_run_version NUMBER;
5820 l_request_id NUMBER;
5821 l_req_data VARCHAR2(255);
5822 l_current_pos NUMBER;
5823 l_next_pos NUMBER;
5824 l_check_status BOOLEAN;
5825 l_phase VARCHAR2(255);
5826 l_dev_phase VARCHAR2(255);
5827 l_status VARCHAR2(255);
5828 l_dev_status VARCHAR2(255);
5829 l_message VARCHAR2(255);
5830 l_submit_phase NUMBER;
5831
5832 BEGIN
5833
5834 retcode := '0';
5835
5836 IF (p_tax_content_source_id = 1)
5837 THEN
5838 l_tax_content_source := 'VERTEX';
5839 l_tax_zone_type := 'US_ZONE_TYPE_VERTEX';
5840 ELSIF (p_tax_content_source_id = 2)
5841 THEN
5842 l_tax_content_source := 'TAXWARE';
5843 l_tax_zone_type := 'US_ZONE_TYPE_TAXWARE';
5844 ELSIF (p_tax_content_source_id = 3)
5845 THEN
5846 l_tax_content_source := 'OTHERS';
5847 l_tax_zone_type := 'US_ZONE_TYPE_TAX_PARTNER';
5848 ELSE
5849 OPEN c_chk_ptp_party (p_tax_content_source_id);
5850 FETCH c_chk_ptp_party INTO l_party_name;
5851 CLOSE c_chk_ptp_party;
5852
5853 IF (l_party_name IS NOT NULL)
5854 THEN
5855 l_tax_content_source := l_party_name;
5856 IF INSTR(l_party_name, '_') > 0 AND
5857 SUBSTR(l_party_name, 1, (INSTR(l_party_name, '_') - 1)) IS NOT NULL THEN
5858 l_tax_zone_type := 'US_ZONE_TYPE_'||SUBSTR(l_party_name, 1, (INSTR(l_party_name, '_') - 1));
5859 ELSE
5860 l_tax_zone_type := 'US_ZONE_TYPE_'||l_party_name;
5861 END IF;
5862 ELSE
5863 errbuf := 'The specified content source provider is not supported. Contact support.';
5864 RAISE l_error;
5865 END IF;
5866 END IF;
5867
5868 OPEN c_get_regime_migrated(p_tax_regime_code);
5869 FETCH c_get_regime_migrated
5870 INTO l_migrated_tax_regime_flag;
5871 CLOSE c_get_regime_migrated;
5872
5873 IF (l_migrated_tax_regime_flag = 'Y')
5874 THEN
5875 l_tax_zone_type := NULL;
5876 END IF;
5877
5878 OPEN c_get_last_run(p_tax_content_source_id,p_tax_regime_code);
5879 FETCH c_get_last_run
5880 INTO l_last_run_version;
5881 CLOSE c_get_last_run;
5882
5883 IF (l_last_run_version IS NULL)
5884 THEN
5885 l_last_run_version := 0;
5886 END IF;
5887
5888 l_req_data := fnd_conc_global.request_data;
5889
5890 IF (l_req_data IS NULL)
5891 THEN
5892
5893 -- Call method to stamp ids
5894 GENERATE_GEOGRAPHY_ID
5895 (
5896 l_tax_content_source,
5897 p_tax_regime_code,
5898 l_migrated_tax_regime_flag,
5899 l_tax_zone_type,
5900 l_last_run_version
5901 );
5902
5903 COMMIT;
5904
5905 -- Call method to do error processing
5906 DO_ERROR_CHECK
5907 (
5908 l_tax_content_source,
5909 l_last_run_version,
5910 p_tax_regime_code,
5911 l_migrated_tax_regime_flag
5912 );
5913
5914 COMMIT;
5915
5916 IF (l_tax_zone_type IS NOT NULL)
5917 THEN
5918 -- Call method to setup initial data
5919 SETUP_DATA
5920 (
5921 errbuf,
5922 retcode,
5923 l_tax_content_source,
5924 p_tax_regime_code,
5925 l_tax_zone_type
5926 );
5927 IF (retcode <> '0')
5928 THEN
5929 errbuf := 'Setup_data failed with: '||errbuf;
5930 RAISE l_error;
5931 END IF;
5932
5933 COMMIT;
5934 END IF;
5935
5936 l_submit_phase := 1;
5937
5938 ELSE
5939
5940 FND_FILE.PUT_LINE
5941 (
5942 FND_FILE.LOG,
5943 'Starting status check of sub-request, ids obtained from global: '||l_req_data
5944 );
5945 -- Check status of sub-requests
5946 l_current_pos := INSTR(l_req_data,'-',1);
5947 IF (l_current_pos > 0)
5948 THEN
5949 IF ('RUNGEO' = SUBSTR(l_req_data,1,l_current_pos-1))
5950 THEN
5951 l_submit_phase := 2;
5952 ELSIF ('RUNZONE' = SUBSTR(l_req_data,1,l_current_pos-1))
5953 THEN
5954 l_submit_phase := 3;
5955 END IF;
5956 END IF;
5957
5958 WHILE (l_current_pos <> 0)
5959 LOOP
5960 l_next_pos := INSTR(l_req_data,'-',l_current_pos+1);
5961 IF (l_next_pos = 0)
5962 THEN
5963 l_request_id := TO_NUMBER(SUBSTR(l_req_data,l_current_pos+1));
5964 ELSE
5965 l_request_id := TO_NUMBER(SUBSTR(l_req_data,l_current_pos+1,l_next_pos-l_current_pos-1));
5966 END IF;
5967 IF (l_request_id IS NOT NULL)
5968 THEN
5969 l_check_status := FND_CONCURRENT.GET_REQUEST_STATUS
5970 (
5971 request_id => l_request_id,
5972 phase => l_phase,
5973 status => l_status,
5974 dev_phase => l_dev_phase,
5975 dev_status => l_dev_status,
5976 message => l_message
5977 );
5978 FND_FILE.PUT_LINE
5979 (
5980 FND_FILE.LOG,
5981 'Checking status of sub-request, id: '||l_request_id||', return dev status value:'||l_dev_status||', return status value:'||l_status
5982 );
5983 IF (l_check_status AND (l_dev_status = 'ERROR'))
5984 THEN
5985 errbuf := 'Sub-request failed with error: '||l_message;
5986 RAISE l_error;
5987 END IF;
5988 END IF;
5989 l_current_pos := l_next_pos;
5990 END LOOP;
5991
5992 END IF;
5993
5994 IF (l_submit_phase = 1)
5995 THEN
5996
5997 l_req_data := 'RUNGEO';
5998
5999 l_request_id := fnd_request.submit_request
6000 (
6001 application => 'ZX',
6002 program => 'ZXUPMGEOWKR',
6003 sub_request => true,
6004 argument1 => p_batch_size,
6005 argument2 => 1,
6006 argument3 => p_num_workers,
6010 IF (l_request_id = 0)
6007 argument4 => l_tax_content_source,
6008 argument5 => l_last_run_version
6009 );
6011 THEN
6012 errbuf := 'E-Business Tax Content Upload Master Geography Program submission failed. Contact support.';
6013 RAISE l_error;
6014 END IF;
6015
6016 l_req_data := l_req_data||'-'||l_request_id;
6017
6018 errbuf := 'Sub-requests Submitted.';
6019
6020 fnd_conc_global.set_req_globals
6021 (
6022 conc_status => 'PAUSED',
6023 request_data => l_req_data
6024 );
6025
6026 ELSIF (l_submit_phase = 2)
6027 THEN
6028
6029 l_req_data := 'RUNZONE';
6030 l_request_id := fnd_request.submit_request
6031 (
6032 application => 'ZX',
6033 program => 'ZXUPTGEOWKR',
6034 sub_request => true,
6035 argument1 => l_tax_content_source,
6036 argument2 => l_last_run_version,
6037 argument3 => p_tax_regime_code,
6038 argument4 => l_tax_zone_type
6039 );
6040 IF (l_request_id = 0)
6041 THEN
6042 errbuf := 'E-Business Tax Content Upload Tax Zone Program submission failed. Contact support.';
6043 RAISE l_error;
6044 END IF;
6045
6046 l_req_data := l_req_data||'-'||l_request_id;
6047
6048 l_request_id := fnd_request.submit_request
6049 (
6050 application => 'ZX',
6051 program => 'ZXUPMALTCITY',
6052 sub_request => true,
6053 argument1 => l_tax_content_source,
6054 argument2 => l_last_run_version
6055 );
6056 IF (l_request_id = 0)
6057 THEN
6058 errbuf := 'E-Business Tax Content Upload Alternate City Geography Program submission failed. Contact support.';
6059 RAISE l_error;
6060 END IF;
6061
6062 l_req_data := l_req_data||'-'||l_request_id;
6063
6064 errbuf := 'Sub-requests Submitted.';
6065
6066 fnd_conc_global.set_req_globals
6067 (
6068 conc_status => 'PAUSED',
6069 request_data => l_req_data
6070 );
6071
6072 ELSIF (l_submit_phase = 3)
6073 THEN
6074
6075 l_req_data := 'RUNRATE';
6076 l_request_id := fnd_request.submit_request
6077 (
6078 application => 'ZX',
6079 program => 'ZXUPMZIPWKR',
6080 sub_request => true,
6081 argument1 => l_tax_content_source,
6082 argument2 => l_last_run_version
6083 );
6084 IF (l_request_id = 0)
6085 THEN
6086 errbuf := 'E-Business Tax Content Upload Exploded Zip Program submission failed. Contact support.';
6087 RAISE l_error;
6088 END IF;
6089
6090 l_req_data := l_req_data||'-'||l_request_id;
6091
6092 l_request_id := fnd_request.submit_request
6093 (
6094 application => 'ZX',
6095 program => 'ZXUPTRATEWKR',
6096 sub_request => true,
6097 argument1 => l_tax_content_source,
6098 argument2 => l_last_run_version,
6099 argument3 => p_tax_regime_code
6100 );
6101 IF (l_request_id = 0)
6102 THEN
6103 errbuf := 'E-Business Tax Content Upload Tax Rates Program submission failed. Contact support.';
6104 RAISE l_error;
6105 END IF;
6106
6107 l_req_data := l_req_data||'-'||l_request_id;
6108
6109 errbuf := 'Sub-requests Submitted.';
6110
6111 fnd_conc_global.set_req_globals
6112 (
6113 conc_status => 'PAUSED',
6114 request_data => l_req_data
6115 );
6116
6117 END IF;
6118
6119 EXCEPTION
6120
6121 WHEN l_error THEN
6122 ROLLBACK;
6123 retcode := '2';
6124 FND_FILE.PUT_LINE
6125 (
6126 FND_FILE.LOG,
6127 'Error in '||l_api_name||': '||errbuf
6128 );
6129
6130 WHEN OTHERS THEN
6131 ROLLBACK;
6132 retcode := '2';
6133 errbuf := SQLERRM;
6134 FND_FILE.PUT_LINE
6135 (
6136 FND_FILE.LOG,
6137 'Unexpected Error in '||l_api_name||': '||errbuf
6138 );
6139
6140 END PROCESS_DATA;
6141
6142 --
6143 -- Procedure to post-process interface data and call other programs
6144 --
6145 PROCEDURE POST_PROCESS_DATA
6146 (
6147 errbuf OUT NOCOPY VARCHAR2,
6148 retcode OUT NOCOPY VARCHAR2,
6149 p_tax_content_source_id IN NUMBER,
6150 p_tax_regime_code IN VARCHAR2,
6151 p_file_location_name IN VARCHAR2
6152 ) IS
6153
6154 l_api_name CONSTANT VARCHAR2(30):= 'post_process_data';
6155 l_error EXCEPTION;
6156
6157 CURSOR c_get_max_version
6158 IS
6159 SELECT MAX(LAST_UPDATION_VERSION)
6160 FROM ZX_DATA_UPLOAD_INTERFACE;
6161
6162 CURSOR c_get_regime_name
6166 SELECT TAX_REGIME_NAME,
6163 (
6164 b_tax_regime_code VARCHAR2
6165 ) IS
6167 COUNTRY_CODE
6168 FROM ZX_REGIMES_VL
6169 WHERE TAX_REGIME_CODE = b_tax_regime_code;
6170
6171 l_last_run_version NUMBER;
6172 l_tax_regime_name VARCHAR2(240);
6173 l_country_code VARCHAR2(30);
6174 l_file_location VARCHAR2(240);
6175 l_file_name VARCHAR2(240);
6176 l_position NUMBER;
6177 l_file_start NUMBER;
6178
6179 BEGIN
6180
6181 retcode := '0';
6182
6183 OPEN c_get_max_version;
6184 FETCH c_get_max_version
6185 INTO l_last_run_version;
6186 CLOSE c_get_max_version;
6187
6188 IF (l_last_run_version IS NULL)
6189 THEN
6190 errbuf := 'Could not find last update version from the interface table. Contact support.';
6191 ELSE
6192 OPEN c_get_regime_name(p_tax_regime_code);
6193 FETCH c_get_regime_name
6194 INTO l_tax_regime_name,l_country_code;
6195 CLOSE c_get_regime_name;
6196
6197 l_file_start := 1;
6198 l_position := 1;
6199 WHILE (l_position <> 0)
6200 LOOP
6201 l_position := INSTR(p_file_location_name,'/',l_file_start);
6202 IF (l_position = 0)
6203 THEN
6204 l_file_location := SUBSTR(p_file_location_name,1,l_file_start-1);
6205 l_file_name := SUBSTR(p_file_location_name,l_file_start);
6206 EXIT;
6207 END IF;
6208 l_file_start := l_position + 1;
6209 END LOOP;
6210
6211 UPDATE ZX_CONTENT_SOURCES
6212 SET PROVIDER_REGIME_CODE = p_tax_regime_code,
6213 PROVIDER_REGIME_NAME = l_tax_regime_name,
6214 LANGUAGE = USERENV('LANG'),
6215 COUNTRY_CODE = l_country_code,
6216 VERSION_LOADED = TO_CHAR(l_last_run_version),
6217 CONTENT_FILE_LOCATION = l_file_location,
6218 CONTENT_FILE_NAME = l_file_name,
6219 PROGRAM_ID = FND_GLOBAL.CONC_PROGRAM_ID,
6220 PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID,
6221 PROGRAM_UPDATE_DATE = SYSDATE
6222 WHERE PROVIDER_ID = p_tax_content_source_id
6223 AND STANDARD_REGIME_CODE = p_tax_regime_code;
6224
6225 IF (SQL%ROWCOUNT = 0)
6226 THEN
6227 INSERT INTO ZX_CONTENT_SOURCES
6228 (
6229 PROVIDER_ID,
6230 PROVIDER_REGIME_CODE,
6231 PROVIDER_REGIME_NAME,
6232 LANGUAGE,
6233 STANDARD_REGIME_CODE,
6234 COUNTRY_CODE,
6235 LOADED_FOR_GCO_FLAG,
6236 REGIME_PURPOSE_CODE,
6237 ENTITY_GROUP_CODE,
6238 VERSION_LOADED,
6239 POINT_RELEASE_VERSION_LOADED,
6240 CONTENT_FILE_TYPE,
6241 CONTENT_FILE_LOCATION,
6242 CONTENT_FILE_NAME,
6243 PROGRAM_ID,
6244 PROGRAM_APPLICATION_ID,
6245 PROGRAM_UPDATE_DATE
6246 )
6247 VALUES
6248 (
6249 p_tax_content_source_id,
6250 p_tax_regime_code,
6251 l_tax_regime_name,
6252 USERENV('LANG'),
6253 p_tax_regime_code,
6254 l_country_code,
6255 'Y',
6256 'CONTENT',
6257 NULL,
6258 TO_CHAR(l_last_run_version),
6259 '0',
6260 'LOADER',
6261 l_file_location,
6262 l_file_name,
6263 FND_GLOBAL.CONC_PROGRAM_ID,
6264 FND_GLOBAL.PROG_APPL_ID,
6265 SYSDATE
6266 );
6267 ELSIF (SQL%ROWCOUNT <> 1)
6268 THEN
6269 errbuf := 'Could not update last update version. Contact support.';
6270 END IF;
6271 END IF;
6272
6273 EXCEPTION
6274
6275 WHEN l_error THEN
6276 ROLLBACK;
6277 retcode := '2';
6278 FND_FILE.PUT_LINE
6279 (
6280 FND_FILE.LOG,
6281 'Error in '||l_api_name||': '||errbuf
6282 );
6283
6284 WHEN OTHERS THEN
6285 ROLLBACK;
6286 retcode := '2';
6287 errbuf := SQLERRM;
6288 FND_FILE.PUT_LINE
6289 (
6290 FND_FILE.LOG,
6291 'Unexpected Error in '||l_api_name||': '||errbuf
6292 );
6293
6294 END POST_PROCESS_DATA;
6295
6296 --
6297 -- Procedure to validate parameters and call SQL LOADER
6298 --
6299 PROCEDURE LOAD_FILE
6300 (
6301 errbuf OUT NOCOPY VARCHAR2,
6302 retcode OUT NOCOPY VARCHAR2,
6303 p_file_location_name IN VARCHAR2,
6304 p_tax_content_source_id IN NUMBER,
6305 p_tax_regime_code IN VARCHAR2
6306 ) IS
6307
6308 CURSOR c_check_data
6309 IS
6310 SELECT COUNT(*)
6311 FROM ZX_DATA_UPLOAD_INTERFACE;
6312
6313 l_api_name CONSTANT VARCHAR2(30):= 'load_file';
6314 l_error EXCEPTION;
6315 l_request_id NUMBER;
6316 l_req_data VARCHAR2(255);
6317 l_data_count NUMBER;
6318
6319 BEGIN
6320
6321 retcode := '0';
6322
6323 l_req_data := fnd_conc_global.request_data;
6324
6325 IF (l_req_data IS NULL)
6326 THEN
6327
6328 l_req_data := 'LOAD-FILE';
6329 FND_FILE.PUT_LINE
6330 (
6331 FND_FILE.LOG,
6332 'Validating input parameters.'
6333 );
6334
6335 IF (p_file_location_name IS NULL)
6336 THEN
6337 errbuf := 'Please specify complete location and name of the data file.';
6338 RAISE l_error;
6339 END IF;
6340
6341 IF (p_tax_content_source_id IS NULL)
6342 THEN
6343 errbuf := 'Please select a valid Content Source.';
6344 RAISE l_error;
6345 END IF;
6346
6347 IF (p_tax_regime_code IS NULL)
6348 THEN
6349 errbuf := 'Please select a valid Tax Regime to load the data into.';
6350 RAISE l_error;
6351 END IF;
6352
6353 FND_FILE.PUT_LINE
6354 (
6355 FND_FILE.LOG,
6356 'Calling Sql*Loader to load data.'
6357 );
6358 l_request_id := fnd_request.submit_request
6359 (
6360 application => 'ZX',
6361 program => 'ZXUPSQLLOAD',
6362 sub_request => true,
6363 argument1 => p_file_location_name
6364 );
6365 IF (l_request_id = 0)
6366 THEN
6367 errbuf := 'E-Business Tax Content Upload Sql Loader Program submission failed. Contact support.';
6368 RAISE l_error;
6369 END IF;
6370
6371 errbuf := 'Sub-request Submitted.';
6372
6373 fnd_conc_global.set_req_globals
6374 (
6375 conc_status => 'PAUSED',
6376 request_data => l_req_data
6377 );
6378
6379 ELSE
6380
6381 OPEN c_check_data;
6382 FETCH c_check_data
6383 INTO l_data_count;
6384 CLOSE c_check_data;
6385
6386 IF (NVL(l_data_count,0) = 0)
6387 THEN
6388 errbuf := 'No data uploaded, check data file location and name.';
6389 RAISE l_error;
6390 END IF;
6391
6392 FND_FILE.PUT_LINE
6393 (
6394 FND_FILE.LOG,
6395 'Data uploaded into interface table. No. of records: '||l_data_count
6396 );
6397
6398 END IF;
6399
6400 EXCEPTION
6401
6402 WHEN l_error THEN
6403 retcode := '2';
6404 FND_FILE.PUT_LINE
6405 (
6406 FND_FILE.LOG,
6407 'Error in '||l_api_name||': '||errbuf
6408 );
6409
6410 WHEN OTHERS THEN
6411 retcode := '2';
6412 errbuf := SQLERRM;
6413 FND_FILE.PUT_LINE
6414 (
6415 FND_FILE.LOG,
6416 'Unexpected Error in '||l_api_name||': '||errbuf
6417 );
6418
6419 END LOAD_FILE;
6420
6421 END ZX_TAX_CONTENT_UPLOAD;