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