[Home] [Help]
PACKAGE BODY: APPS.FND_TERRITORIES_PKG
Source
1 package body FND_TERRITORIES_PKG as
2 /* $Header: AFNLDTIB.pls 120.7 2010/12/17 15:45:02 jvalenti ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_TERRITORY_CODE in VARCHAR2,
7 X_EU_CODE in VARCHAR2 default NULL,
8 X_ISO_NUMERIC_CODE in VARCHAR2,
9 X_ALTERNATE_TERRITORY_CODE in VARCHAR2,
10 X_NLS_TERRITORY in VARCHAR2,
11 X_ADDRESS_STYLE in VARCHAR2,
12 X_ADDRESS_VALIDATION in VARCHAR2,
13 X_BANK_INFO_STYLE in VARCHAR2,
14 X_BANK_INFO_VALIDATION in VARCHAR2,
15 X_TERRITORY_SHORT_NAME in VARCHAR2,
16 X_DESCRIPTION in VARCHAR2,
17 X_CREATION_DATE in DATE,
18 X_CREATED_BY in NUMBER,
19 X_LAST_UPDATE_DATE in DATE,
20 X_LAST_UPDATED_BY in NUMBER,
21 X_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23 begin
24 FND_TERRITORIES_PKG.INSERT_ROW(
25 X_ROWID,
26 X_TERRITORY_CODE,
27 X_EU_CODE,
28 X_ISO_NUMERIC_CODE,
29 X_ALTERNATE_TERRITORY_CODE,
30 X_NLS_TERRITORY,
31 X_ADDRESS_STYLE,
32 X_ADDRESS_VALIDATION,
33 X_BANK_INFO_STYLE,
34 X_BANK_INFO_VALIDATION,
35 X_TERRITORY_SHORT_NAME,
36 X_DESCRIPTION,
37 X_CREATION_DATE,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_LOGIN,
42 NULL);
43 end INSERT_ROW;
44
45 procedure INSERT_ROW (
46 X_ROWID in out nocopy VARCHAR2,
47 X_TERRITORY_CODE in VARCHAR2,
48 X_EU_CODE in VARCHAR2 default NULL,
49 X_ISO_NUMERIC_CODE in VARCHAR2,
50 X_ALTERNATE_TERRITORY_CODE in VARCHAR2,
51 X_NLS_TERRITORY in VARCHAR2,
52 X_ADDRESS_STYLE in VARCHAR2,
53 X_ADDRESS_VALIDATION in VARCHAR2,
54 X_BANK_INFO_STYLE in VARCHAR2,
55 X_BANK_INFO_VALIDATION in VARCHAR2,
56 X_TERRITORY_SHORT_NAME in VARCHAR2,
57 X_DESCRIPTION in VARCHAR2,
58 X_CREATION_DATE in DATE,
59 X_CREATED_BY in NUMBER,
60 X_LAST_UPDATE_DATE in DATE,
61 X_LAST_UPDATED_BY in NUMBER,
62 X_LAST_UPDATE_LOGIN in NUMBER,
63 X_OBSOLETE_FLAG in VARCHAR2
64 ) is
65 begin
66 FND_TERRITORIES_PKG.INSERT_ROW(
67 X_ROWID,
68 X_TERRITORY_CODE,
69 X_EU_CODE,
70 X_ISO_NUMERIC_CODE,
71 X_ALTERNATE_TERRITORY_CODE,
72 X_NLS_TERRITORY,
73 X_ADDRESS_STYLE,
74 X_ADDRESS_VALIDATION,
75 X_BANK_INFO_STYLE,
76 X_BANK_INFO_VALIDATION,
77 X_TERRITORY_SHORT_NAME,
78 X_DESCRIPTION,
79 X_CREATION_DATE,
80 X_CREATED_BY,
81 X_LAST_UPDATE_DATE,
82 X_LAST_UPDATED_BY,
83 X_LAST_UPDATE_LOGIN,
84 X_OBSOLETE_FLAG,
85 null);
86 end INSERT_ROW;
87
88 procedure INSERT_ROW (
89 X_ROWID in out nocopy VARCHAR2,
90 X_TERRITORY_CODE in VARCHAR2,
91 X_EU_CODE in VARCHAR2 default NULL,
92 X_ISO_NUMERIC_CODE in VARCHAR2,
93 X_ALTERNATE_TERRITORY_CODE in VARCHAR2,
94 X_NLS_TERRITORY in VARCHAR2,
95 X_ADDRESS_STYLE in VARCHAR2,
96 X_ADDRESS_VALIDATION in VARCHAR2,
97 X_BANK_INFO_STYLE in VARCHAR2,
98 X_BANK_INFO_VALIDATION in VARCHAR2,
99 X_TERRITORY_SHORT_NAME in VARCHAR2,
100 X_DESCRIPTION in VARCHAR2,
101 X_CREATION_DATE in DATE,
102 X_CREATED_BY in NUMBER,
103 X_LAST_UPDATE_DATE in DATE,
104 X_LAST_UPDATED_BY in NUMBER,
105 X_LAST_UPDATE_LOGIN in NUMBER,
106 X_OBSOLETE_FLAG in VARCHAR2,
107 X_ISO_TERRITORY_CODE in VARCHAR2
108 ) is
109 cursor C is select ROWID from FND_TERRITORIES
110 where TERRITORY_CODE = X_TERRITORY_CODE
111 ;
112 obsolete_flag varchar2(1);
113 begin
114
115 obsolete_flag := nvl(X_OBSOLETE_FLAG, 'N');
116
117 insert into FND_TERRITORIES (
118 EU_CODE,
119 TERRITORY_CODE,
120 ISO_NUMERIC_CODE,
121 ALTERNATE_TERRITORY_CODE,
122 NLS_TERRITORY,
123 ADDRESS_STYLE,
124 ADDRESS_VALIDATION,
125 BANK_INFO_STYLE,
126 BANK_INFO_VALIDATION,
127 CREATION_DATE,
128 CREATED_BY,
129 LAST_UPDATE_DATE,
130 LAST_UPDATED_BY,
131 LAST_UPDATE_LOGIN,
132 OBSOLETE_FLAG,
133 ISO_TERRITORY_CODE
134 ) values (
135 X_EU_CODE,
136 X_TERRITORY_CODE,
137 X_ISO_NUMERIC_CODE,
138 X_ALTERNATE_TERRITORY_CODE,
139 X_NLS_TERRITORY,
140 X_ADDRESS_STYLE,
141 X_ADDRESS_VALIDATION,
142 X_BANK_INFO_STYLE,
143 X_BANK_INFO_VALIDATION,
144 X_CREATION_DATE,
145 X_CREATED_BY,
146 X_LAST_UPDATE_DATE,
147 X_LAST_UPDATED_BY,
148 X_LAST_UPDATE_LOGIN,
149 obsolete_flag,
150 X_ISO_TERRITORY_CODE
151 );
152
153 insert into FND_TERRITORIES_TL (
154 TERRITORY_CODE,
155 TERRITORY_SHORT_NAME,
156 CREATED_BY,
157 CREATION_DATE,
158 LAST_UPDATED_BY,
159 LAST_UPDATE_DATE,
160 LAST_UPDATE_LOGIN,
161 DESCRIPTION,
162 LANGUAGE,
163 SOURCE_LANG
164 ) select
165 X_TERRITORY_CODE,
166 X_TERRITORY_SHORT_NAME,
167 X_CREATED_BY,
168 X_CREATION_DATE,
169 X_LAST_UPDATED_BY,
170 X_LAST_UPDATE_DATE,
171 X_LAST_UPDATE_LOGIN,
172 X_DESCRIPTION,
173 L.LANGUAGE_CODE,
174 userenv('LANG')
175 from FND_LANGUAGES L
176 where L.INSTALLED_FLAG in ('I', 'B')
177 and not exists
178 (select NULL
179 from FND_TERRITORIES_TL T
180 where T.TERRITORY_CODE = X_TERRITORY_CODE
181 and T.LANGUAGE = L.LANGUAGE_CODE);
182
183 open c;
184 fetch c into X_ROWID;
185 if (c%notfound) then
186 close c;
187 raise no_data_found;
188 end if;
189 close c;
190
191 end INSERT_ROW;
192
193 procedure LOCK_ROW (
194 X_TERRITORY_CODE in VARCHAR2,
195 X_EU_CODE in VARCHAR2 default NULL,
196 X_ISO_NUMERIC_CODE in VARCHAR2,
197 X_ALTERNATE_TERRITORY_CODE in VARCHAR2,
198 X_NLS_TERRITORY in VARCHAR2,
199 X_ADDRESS_STYLE in VARCHAR2,
200 X_ADDRESS_VALIDATION in VARCHAR2,
201 X_BANK_INFO_STYLE in VARCHAR2,
202 X_BANK_INFO_VALIDATION in VARCHAR2,
203 X_TERRITORY_SHORT_NAME in VARCHAR2,
204 X_DESCRIPTION in VARCHAR2
205 ) is
206 cursor c is select
207 EU_CODE,
208 ISO_NUMERIC_CODE,
209 ALTERNATE_TERRITORY_CODE,
210 NLS_TERRITORY,
211 ADDRESS_STYLE,
212 ADDRESS_VALIDATION,
213 BANK_INFO_STYLE,
214 BANK_INFO_VALIDATION
215 from FND_TERRITORIES
216 where TERRITORY_CODE = X_TERRITORY_CODE
217 for update of TERRITORY_CODE nowait;
218 recinfo c%rowtype;
219
220 cursor c1 is select
221 TERRITORY_SHORT_NAME,
222 DESCRIPTION,
223 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
224 from FND_TERRITORIES_TL
225 where TERRITORY_CODE = X_TERRITORY_CODE
226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
227 for update of TERRITORY_CODE nowait;
228 begin
229 open c;
230 fetch c into recinfo;
231 if (c%notfound) then
232 close c;
233 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
234 app_exception.raise_exception;
235 end if;
236 close c;
237 if ( ((recinfo.EU_CODE = X_EU_CODE)
238 OR ((recinfo.EU_CODE is null) AND (X_EU_CODE is null)))
239 AND ((recinfo.ISO_NUMERIC_CODE = X_ISO_NUMERIC_CODE)
240 OR ((recinfo.ISO_NUMERIC_CODE is null) AND (X_ISO_NUMERIC_CODE is null)))
241 AND ((recinfo.ALTERNATE_TERRITORY_CODE = X_ALTERNATE_TERRITORY_CODE)
242 OR ((recinfo.ALTERNATE_TERRITORY_CODE is null) AND (X_ALTERNATE_TERRITORY_CODE is null)))
243 AND ((recinfo.NLS_TERRITORY = X_NLS_TERRITORY)
244 OR ((recinfo.NLS_TERRITORY is null) AND (X_NLS_TERRITORY is null)))
245 AND ((recinfo.ADDRESS_STYLE = X_ADDRESS_STYLE)
246 OR ((recinfo.ADDRESS_STYLE is null) AND (X_ADDRESS_STYLE is null)))
247 AND ((recinfo.ADDRESS_VALIDATION = X_ADDRESS_VALIDATION)
248 OR ((recinfo.ADDRESS_VALIDATION is null) AND (X_ADDRESS_VALIDATION is null)))
249 AND ((recinfo.BANK_INFO_STYLE = X_BANK_INFO_STYLE)
250 OR ((recinfo.BANK_INFO_STYLE is null) AND (X_BANK_INFO_STYLE is null)))
251 AND ((recinfo.BANK_INFO_VALIDATION = X_BANK_INFO_VALIDATION)
252 OR ((recinfo.BANK_INFO_VALIDATION is null) AND (X_BANK_INFO_VALIDATION is null)))
253 ) then
254 null;
255 else
256 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
257 app_exception.raise_exception;
258 end if;
259
260 for tlinfo in c1 loop
261 if (tlinfo.BASELANG = 'Y') then
262 if ( (tlinfo.TERRITORY_SHORT_NAME = X_TERRITORY_SHORT_NAME)
263 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
264 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
265 ) then
266 null;
267 else
268 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
269 app_exception.raise_exception;
270 end if;
271 end if;
272 end loop;
273 return;
274 end LOCK_ROW;
275
276 procedure UPDATE_ROW (
277 X_TERRITORY_CODE in VARCHAR2,
278 X_EU_CODE in VARCHAR2 default NULL,
279 X_ISO_NUMERIC_CODE in VARCHAR2,
280 X_ALTERNATE_TERRITORY_CODE in VARCHAR2,
281 X_NLS_TERRITORY in VARCHAR2,
282 X_ADDRESS_STYLE in VARCHAR2,
283 X_ADDRESS_VALIDATION in VARCHAR2,
284 X_BANK_INFO_STYLE in VARCHAR2,
285 X_BANK_INFO_VALIDATION in VARCHAR2,
286 X_TERRITORY_SHORT_NAME in VARCHAR2,
287 X_DESCRIPTION in VARCHAR2,
288 X_LAST_UPDATE_DATE in DATE,
289 X_LAST_UPDATED_BY in NUMBER,
290 X_LAST_UPDATE_LOGIN in NUMBER
291 ) is
292 begin
293 FND_TERRITORIES_PKG.UPDATE_ROW(
294 X_TERRITORY_CODE,
295 X_EU_CODE,
296 X_ISO_NUMERIC_CODE,
297 X_ALTERNATE_TERRITORY_CODE,
298 X_NLS_TERRITORY,
299 X_ADDRESS_STYLE,
300 X_ADDRESS_VALIDATION,
301 X_BANK_INFO_STYLE,
302 X_BANK_INFO_VALIDATION,
303 X_TERRITORY_SHORT_NAME,
304 X_DESCRIPTION,
305 X_LAST_UPDATE_DATE,
306 X_LAST_UPDATED_BY,
307 X_LAST_UPDATE_LOGIN,
308 NULL);
309 end UPDATE_ROW;
310
311 procedure UPDATE_ROW (
312 X_TERRITORY_CODE in VARCHAR2,
313 X_EU_CODE in VARCHAR2 default NULL,
314 X_ISO_NUMERIC_CODE in VARCHAR2,
315 X_ALTERNATE_TERRITORY_CODE in VARCHAR2,
316 X_NLS_TERRITORY in VARCHAR2,
317 X_ADDRESS_STYLE in VARCHAR2,
318 X_ADDRESS_VALIDATION in VARCHAR2,
319 X_BANK_INFO_STYLE in VARCHAR2,
320 X_BANK_INFO_VALIDATION in VARCHAR2,
321 X_TERRITORY_SHORT_NAME in VARCHAR2,
322 X_DESCRIPTION in VARCHAR2,
323 X_LAST_UPDATE_DATE in DATE,
324 X_LAST_UPDATED_BY in NUMBER,
325 X_LAST_UPDATE_LOGIN in NUMBER,
326 X_OBSOLETE_FLAG in VARCHAR2
327 ) is
328 begin
329 FND_TERRITORIES_PKG.UPDATE_ROW(
330 X_TERRITORY_CODE,
331 X_EU_CODE,
332 X_ISO_NUMERIC_CODE,
333 X_ALTERNATE_TERRITORY_CODE,
334 X_NLS_TERRITORY,
335 X_ADDRESS_STYLE,
336 X_ADDRESS_VALIDATION,
337 X_BANK_INFO_STYLE,
338 X_BANK_INFO_VALIDATION,
339 X_TERRITORY_SHORT_NAME,
340 X_DESCRIPTION,
341 X_LAST_UPDATE_DATE,
342 X_LAST_UPDATED_BY,
343 X_LAST_UPDATE_LOGIN,
344 X_OBSOLETE_FLAG,
345 NULL);
346 end UPDATE_ROW;
347
348
349 procedure UPDATE_ROW (
350 X_TERRITORY_CODE in VARCHAR2,
351 X_EU_CODE in VARCHAR2 default NULL,
352 X_ISO_NUMERIC_CODE in VARCHAR2,
353 X_ALTERNATE_TERRITORY_CODE in VARCHAR2,
354 X_NLS_TERRITORY in VARCHAR2,
355 X_ADDRESS_STYLE in VARCHAR2,
356 X_ADDRESS_VALIDATION in VARCHAR2,
357 X_BANK_INFO_STYLE in VARCHAR2,
358 X_BANK_INFO_VALIDATION in VARCHAR2,
359 X_TERRITORY_SHORT_NAME in VARCHAR2,
360 X_DESCRIPTION in VARCHAR2,
361 X_LAST_UPDATE_DATE in DATE,
362 X_LAST_UPDATED_BY in NUMBER,
363 X_LAST_UPDATE_LOGIN in NUMBER,
364 X_OBSOLETE_FLAG in VARCHAR2,
365 X_ISO_TERRITORY_CODE in VARCHAR2
366 ) is
367
368 l_iso_territory_code varchar2(3);
369 l_eu_code varchar2(3);
370
371 begin
372
373 select
374 decode(x_iso_territory_code,fnd_territories_pkg.null_char,null,
375 null,u.iso_territory_code,x_iso_territory_code),
376 decode(x_eu_code,fnd_territories_pkg.null_char,null,
377 null,u.eu_code,x_eu_code)
378 into l_iso_territory_code,l_eu_code
379 from fnd_territories u
380 where territory_code = x_territory_code;
381
382 if (X_OBSOLETE_FLAG is NULL) then
383 update FND_TERRITORIES set
384 EU_CODE = L_EU_CODE,
385 ISO_NUMERIC_CODE = X_ISO_NUMERIC_CODE,
386 ALTERNATE_TERRITORY_CODE = X_ALTERNATE_TERRITORY_CODE,
387 NLS_TERRITORY = X_NLS_TERRITORY,
388 ADDRESS_STYLE = X_ADDRESS_STYLE,
389 ADDRESS_VALIDATION = X_ADDRESS_VALIDATION,
390 BANK_INFO_STYLE = X_BANK_INFO_STYLE,
391 BANK_INFO_VALIDATION = X_BANK_INFO_VALIDATION,
392 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
393 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
394 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
395 ISO_TERRITORY_CODE = L_ISO_TERRITORY_CODE
396 where TERRITORY_CODE = X_TERRITORY_CODE;
397 else
398 update FND_TERRITORIES set
399 EU_CODE = L_EU_CODE,
400 ISO_NUMERIC_CODE = X_ISO_NUMERIC_CODE,
401 ALTERNATE_TERRITORY_CODE = X_ALTERNATE_TERRITORY_CODE,
402 NLS_TERRITORY = X_NLS_TERRITORY,
403 ADDRESS_STYLE = X_ADDRESS_STYLE,
404 ADDRESS_VALIDATION = X_ADDRESS_VALIDATION,
405 BANK_INFO_STYLE = X_BANK_INFO_STYLE,
406 BANK_INFO_VALIDATION = X_BANK_INFO_VALIDATION,
407 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
408 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
409 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
410 OBSOLETE_FLAG = X_OBSOLETE_FLAG,
411 ISO_TERRITORY_CODE = L_ISO_TERRITORY_CODE
412 where TERRITORY_CODE = X_TERRITORY_CODE;
413 end if;
414
415 if (sql%notfound) then
416 raise no_data_found;
417 end if;
418
419 update FND_TERRITORIES_TL set
420 TERRITORY_SHORT_NAME = X_TERRITORY_SHORT_NAME,
421 DESCRIPTION = X_DESCRIPTION,
422 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
423 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
424 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
425 SOURCE_LANG = userenv('LANG')
426 where TERRITORY_CODE = X_TERRITORY_CODE
427 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
428
429 if (sql%notfound) then
430 raise no_data_found;
431 end if;
432 end UPDATE_ROW;
433
434 procedure DELETE_ROW (
435 X_TERRITORY_CODE in VARCHAR2
436 ) is
437 begin
438 delete from FND_TERRITORIES_TL
439 where TERRITORY_CODE = X_TERRITORY_CODE;
440
441 if (sql%notfound) then
442 raise no_data_found;
443 end if;
444
445 delete from FND_TERRITORIES
446 where TERRITORY_CODE = X_TERRITORY_CODE;
447
448 if (sql%notfound) then
449 raise no_data_found;
450 end if;
451 end DELETE_ROW;
452
453 procedure ADD_LANGUAGE
454 is
455 begin
456 /* Mar/19/03 requested by Ric Ginsberg */
457 /* The following delete and update statements are commented out */
458 /* as a quick workaround to fix the time-consuming table handler issue */
459 /* Eventually we'll need to turn them into a separate fix_language procedure */
460 /*
461
462 delete from FND_TERRITORIES_TL T
463 where not exists
464 (select NULL
465 from FND_TERRITORIES B
466 where B.TERRITORY_CODE = T.TERRITORY_CODE
467 );
468
469 update FND_TERRITORIES_TL T set (
470 TERRITORY_SHORT_NAME,
471 DESCRIPTION
472 ) = (select
473 B.TERRITORY_SHORT_NAME,
474 B.DESCRIPTION
475 from FND_TERRITORIES_TL B
476 where B.TERRITORY_CODE = T.TERRITORY_CODE
477 and B.LANGUAGE = T.SOURCE_LANG)
478 where (
479 T.TERRITORY_CODE,
480 T.LANGUAGE
481 ) in (select
482 SUBT.TERRITORY_CODE,
483 SUBT.LANGUAGE
484 from FND_TERRITORIES_TL SUBB, FND_TERRITORIES_TL SUBT
485 where SUBB.TERRITORY_CODE = SUBT.TERRITORY_CODE
486 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
487 and (SUBB.TERRITORY_SHORT_NAME <> SUBT.TERRITORY_SHORT_NAME
488 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
489 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
490 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
491 ));
492 */
493
494 insert into FND_TERRITORIES_TL (
495 TERRITORY_CODE,
496 TERRITORY_SHORT_NAME,
497 CREATED_BY,
498 CREATION_DATE,
499 LAST_UPDATED_BY,
500 LAST_UPDATE_DATE,
501 LAST_UPDATE_LOGIN,
502 DESCRIPTION,
503 LANGUAGE,
504 SOURCE_LANG
505 ) select
506 B.TERRITORY_CODE,
507 B.TERRITORY_SHORT_NAME,
508 B.CREATED_BY,
509 B.CREATION_DATE,
510 B.LAST_UPDATED_BY,
511 B.LAST_UPDATE_DATE,
512 B.LAST_UPDATE_LOGIN,
513 B.DESCRIPTION,
514 L.LANGUAGE_CODE,
515 B.SOURCE_LANG
516 from FND_TERRITORIES_TL B, FND_LANGUAGES L
517 where L.INSTALLED_FLAG in ('I', 'B')
518 and B.LANGUAGE = userenv('LANG')
519 and not exists
520 (select NULL
521 from FND_TERRITORIES_TL T
522 where T.TERRITORY_CODE = B.TERRITORY_CODE
523 and T.LANGUAGE = L.LANGUAGE_CODE);
524 end ADD_LANGUAGE;
525
526 procedure TRANSLATE_ROW (
527 X_TERRITORY_CODE in VARCHAR2,
528 X_TERRITORY_SHORT_NAME in VARCHAR2,
529 X_DESCRIPTION in VARCHAR2,
530 X_OWNER in VARCHAR2
531 ) is
532 begin
533 TRANSLATE_ROW (
534 X_TERRITORY_CODE => X_TERRITORY_CODE ,
535 X_TERRITORY_SHORT_NAME => X_TERRITORY_SHORT_NAME ,
536 X_DESCRIPTION => X_DESCRIPTION,
537 X_OWNER => X_OWNER,
538 X_LAST_UPDATE_DATE => null,
539 X_CUSTOM_MODE => null);
540 end TRANSLATE_ROW;
541
542 procedure LOAD_ROW (
543 X_TERRITORY_CODE in VARCHAR2,
544 X_EU_CODE in VARCHAR2 default NULL,
545 X_ISO_NUMERIC_CODE in VARCHAR2,
546 X_ALTERNATE_TERRITORY_CODE in VARCHAR2 default NULL,
547 X_NLS_TERRITORY in VARCHAR2 default NULL,
548 X_ADDRESS_STYLE in VARCHAR2 default NULL,
549 X_ADDRESS_VALIDATION in VARCHAR2 default NULL,
550 X_BANK_INFO_STYLE in VARCHAR2 default NULL,
551 X_BANK_INFO_VALIDATION in VARCHAR2 default NULL,
552 X_TERRITORY_SHORT_NAME in VARCHAR2,
553 X_DESCRIPTION in VARCHAR2,
554 X_OWNER in VARCHAR2
555 ) is
556 begin
557 LOAD_ROW (
558 X_TERRITORY_CODE => X_TERRITORY_CODE ,
559 X_DESCRIPTION => X_DESCRIPTION,
560 X_EU_CODE => X_EU_CODE ,
561 X_ISO_NUMERIC_CODE => X_ISO_NUMERIC_CODE ,
562 X_NLS_TERRITORY => X_NLS_TERRITORY,
563 X_ALTERNATE_TERRITORY_CODE => X_ALTERNATE_TERRITORY_CODE ,
564 X_ADDRESS_STYLE => X_ADDRESS_STYLE ,
565 X_ADDRESS_VALIDATION => X_ADDRESS_VALIDATION ,
566 X_BANK_INFO_STYLE => X_BANK_INFO_STYLE ,
567 X_OWNER => X_OWNER,
568 X_BANK_INFO_VALIDATION => X_BANK_INFO_VALIDATION ,
569 X_TERRITORY_SHORT_NAME => X_TERRITORY_SHORT_NAME ,
570 X_LAST_UPDATE_DATE => null,
571 X_CUSTOM_MODE => null);
572
573 end LOAD_ROW;
574
575
576 procedure TRANSLATE_ROW (
577 X_TERRITORY_CODE in VARCHAR2,
578 X_TERRITORY_SHORT_NAME in VARCHAR2,
579 X_DESCRIPTION in VARCHAR2,
580 X_OWNER in VARCHAR2,
581 X_LAST_UPDATE_DATE in VARCHAR2,
582 X_CUSTOM_MODE in VARCHAR2
583 ) is
584 f_luby number; -- entity owner in file
585 f_ludate date; -- entity update date in file
586 db_luby number; -- entity owner in db
587 db_ludate date; -- entity update date in db
588
589 begin
590 -- Translate owner to file_last_updated_by
591 f_luby := fnd_load_util.owner_id(x_owner);
592
593 -- Translate char last_update_date to date
594 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
595
596 select last_updated_by, last_update_date
597 into db_luby, db_ludate
598 from FND_TERRITORIES_TL
599 where TERRITORY_CODE = X_TERRITORY_CODE
600 and language = userenv('LANG');
601
602 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
603 db_ludate, X_CUSTOM_MODE)) then
604 update FND_TERRITORIES_TL set
605 DESCRIPTION = X_DESCRIPTION,
606 TERRITORY_SHORT_NAME = X_TERRITORY_SHORT_NAME,
607 LAST_UPDATE_DATE = f_ludate,
608 LAST_UPDATED_BY = f_luby,
609 LAST_UPDATE_LOGIN = 0,
610 SOURCE_LANG = userenv('LANG')
611 where TERRITORY_CODE = X_TERRITORY_CODE
612 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
613 end if;
614
615 end TRANSLATE_ROW;
616
617 procedure LOAD_ROW (
618 X_TERRITORY_CODE in VARCHAR2,
619 X_EU_CODE in VARCHAR2 default NULL,
620 X_ISO_NUMERIC_CODE in VARCHAR2,
621 X_ALTERNATE_TERRITORY_CODE in VARCHAR2 default NULL,
622 X_NLS_TERRITORY in VARCHAR2 default NULL,
623 X_ADDRESS_STYLE in VARCHAR2 default NULL,
624 X_ADDRESS_VALIDATION in VARCHAR2 default NULL,
625 X_BANK_INFO_STYLE in VARCHAR2 default NULL,
626 X_BANK_INFO_VALIDATION in VARCHAR2 default NULL,
627 X_TERRITORY_SHORT_NAME in VARCHAR2,
628 X_DESCRIPTION in VARCHAR2,
629 X_OWNER in VARCHAR2,
630 X_LAST_UPDATE_DATE in VARCHAR2,
631 X_CUSTOM_MODE in VARCHAR2
632 ) is
633 begin
634 LOAD_ROW (
635 X_TERRITORY_CODE => X_TERRITORY_CODE ,
636 X_DESCRIPTION => X_DESCRIPTION,
637 X_EU_CODE => X_EU_CODE ,
638 X_ISO_NUMERIC_CODE => X_ISO_NUMERIC_CODE ,
639 X_NLS_TERRITORY => X_NLS_TERRITORY,
640 X_ALTERNATE_TERRITORY_CODE => X_ALTERNATE_TERRITORY_CODE ,
641 X_ADDRESS_STYLE => X_ADDRESS_STYLE ,
642 X_ADDRESS_VALIDATION => X_ADDRESS_VALIDATION ,
643 X_BANK_INFO_STYLE => X_BANK_INFO_STYLE ,
644 X_OWNER => X_OWNER,
645 X_BANK_INFO_VALIDATION => X_BANK_INFO_VALIDATION ,
646 X_TERRITORY_SHORT_NAME => X_TERRITORY_SHORT_NAME ,
647 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE ,
648 X_CUSTOM_MODE => X_CUSTOM_MODE ,
649 X_OBSOLETE_FLAG => NULL);
650 end LOAD_ROW;
651
652 procedure LOAD_ROW (
653 X_TERRITORY_CODE in VARCHAR2,
654 X_EU_CODE in VARCHAR2 default NULL,
655 X_ISO_NUMERIC_CODE in VARCHAR2,
656 X_ALTERNATE_TERRITORY_CODE in VARCHAR2 default NULL,
657 X_NLS_TERRITORY in VARCHAR2 default NULL,
658 X_ADDRESS_STYLE in VARCHAR2 default NULL,
659 X_ADDRESS_VALIDATION in VARCHAR2 default NULL,
660 X_BANK_INFO_STYLE in VARCHAR2 default NULL,
661 X_BANK_INFO_VALIDATION in VARCHAR2 default NULL,
662 X_TERRITORY_SHORT_NAME in VARCHAR2,
663 X_DESCRIPTION in VARCHAR2,
664 X_OWNER in VARCHAR2,
665 X_LAST_UPDATE_DATE in VARCHAR2,
666 X_CUSTOM_MODE in VARCHAR2,
667 X_OBSOLETE_FLAG in VARCHAR2
668 ) is
669 begin
670 LOAD_ROW (
671 X_TERRITORY_CODE => X_TERRITORY_CODE ,
672 X_DESCRIPTION => X_DESCRIPTION,
673 X_EU_CODE => X_EU_CODE ,
674 X_ISO_NUMERIC_CODE => X_ISO_NUMERIC_CODE ,
675 X_NLS_TERRITORY => X_NLS_TERRITORY,
676 X_ALTERNATE_TERRITORY_CODE => X_ALTERNATE_TERRITORY_CODE ,
677 X_ADDRESS_STYLE => X_ADDRESS_STYLE ,
678 X_ADDRESS_VALIDATION => X_ADDRESS_VALIDATION ,
679 X_BANK_INFO_STYLE => X_BANK_INFO_STYLE ,
680 X_OWNER => X_OWNER,
681 X_BANK_INFO_VALIDATION => X_BANK_INFO_VALIDATION ,
682 X_TERRITORY_SHORT_NAME => X_TERRITORY_SHORT_NAME ,
683 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE ,
684 X_CUSTOM_MODE => X_CUSTOM_MODE ,
685 X_OBSOLETE_FLAG => X_OBSOLETE_FLAG ,
686 X_ISO_TERRITORY_CODE => NULL );
687 end LOAD_ROW;
688
689 procedure LOAD_ROW (
690 X_TERRITORY_CODE in VARCHAR2,
691 X_EU_CODE in VARCHAR2 default NULL,
692 X_ISO_NUMERIC_CODE in VARCHAR2,
693 X_ALTERNATE_TERRITORY_CODE in VARCHAR2 default NULL,
694 X_NLS_TERRITORY in VARCHAR2 default NULL,
695 X_ADDRESS_STYLE in VARCHAR2 default NULL,
696 X_ADDRESS_VALIDATION in VARCHAR2 default NULL,
697 X_BANK_INFO_STYLE in VARCHAR2 default NULL,
698 X_BANK_INFO_VALIDATION in VARCHAR2 default NULL,
699 X_TERRITORY_SHORT_NAME in VARCHAR2,
700 X_DESCRIPTION in VARCHAR2,
701 X_OWNER in VARCHAR2,
702 X_LAST_UPDATE_DATE in VARCHAR2,
703 X_CUSTOM_MODE in VARCHAR2,
704 X_OBSOLETE_FLAG in VARCHAR2,
705 X_ISO_TERRITORY_CODE in VARCHAR2
706 ) is
707 f_luby number; -- entity owner in file
708 f_ludate date; -- entity update date in file
709 db_luby number; -- entity owner in db
710 db_ludate date; -- entity update date in db
711 X_ROWID varchar2(64);
712 user_id number;
713
714 -- Bug4493112 - Local variables moved from UPDATE_ROW to LOAD_ROW.
715
716 L_ISO_NUMERIC_CODE VARCHAR2(3);
717 L_ALTERNATE_TERRITORY_CODE VARCHAR2(30);
718 L_NLS_TERRITORY VARCHAR2(30);
719 L_ADDRESS_STYLE VARCHAR2(30);
720 L_ADDRESS_VALIDATION VARCHAR2(30);
721 L_BANK_INFO_STYLE VARCHAR2(30);
722 L_BANK_INFO_VALIDATION VARCHAR2(30);
723 L_EU_CODE VARCHAR2(3);
724 L_ISO_TERRITORY_CODE VARCHAR2(3);
725
726 begin
727
728 -- Translate owner to file_last_updated_by
729 f_luby := fnd_load_util.owner_id(x_owner);
730
731 -- Translate char last_update_date to date
732 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
733
734 begin
735 select last_updated_by, last_update_date
736 into db_luby, db_ludate
737 from FND_TERRITORIES
738 where TERRITORY_CODE = X_TERRITORY_CODE;
739
740 -- Bug4493112 Moved decode select statement from UPDATE_ROW to LOAD_ROW.
741 -- Bug4648984 Moved sql to inside exception block to handle the
742 -- no data found.
743
744 select
745 decode(x_iso_numeric_code, fnd_territories_pkg.null_char, null,
746 null, u.iso_numeric_code,
747 x_iso_numeric_code),
748 decode(x_alternate_territory_code,fnd_territories_pkg.null_char, null,
749 null, u.alternate_territory_code,
750 x_alternate_territory_code),
751 decode(x_nls_territory, fnd_territories_pkg.null_char, null,
752 null, u.nls_territory,
753 x_nls_territory),
754 decode(x_address_style, fnd_territories_pkg.null_char, null,
755 null, u.address_style,
756 x_address_style),
757 decode(x_address_validation, fnd_territories_pkg.null_char, null,
758 null, u.address_validation,
759 x_address_validation),
760 decode(x_bank_info_style, fnd_territories_pkg.null_char, null,
761 null, u.bank_info_style,
762 x_bank_info_style),
763 decode(x_bank_info_validation, fnd_territories_pkg.null_char, null,
764 null, u.bank_info_validation,
765 x_bank_info_validation),
766 decode(x_eu_code, fnd_territories_pkg.null_char, null,
767 null, u.eu_code,
768 x_eu_code),
769 decode(x_iso_territory_code, fnd_territories_pkg.null_char, null,
770 null, u.iso_territory_code,
771 x_iso_territory_code)
772 into l_iso_numeric_code, l_alternate_territory_code, l_nls_territory,
773 l_address_style, l_address_validation, l_bank_info_style,
774 l_bank_info_validation, l_eu_code, l_iso_territory_code
775 from fnd_territories u
776 where territory_code = x_territory_code;
777
778 -- Bug4493112 Modified code to use local variables in UPDATE_ROW and
779 -- INSERT_ROW procedure calls.
780
781 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
782 db_ludate, X_CUSTOM_MODE)) then
783 FND_TERRITORIES_PKG.UPDATE_ROW(
784 X_TERRITORY_CODE,
785 L_EU_CODE,
786 L_ISO_NUMERIC_CODE,
787 L_ALTERNATE_TERRITORY_CODE,
788 L_NLS_TERRITORY,
789 L_ADDRESS_STYLE,
790 L_ADDRESS_VALIDATION,
791 L_BANK_INFO_STYLE,
792 L_BANK_INFO_VALIDATION,
793 X_TERRITORY_SHORT_NAME,
794 X_DESCRIPTION,
795 f_ludate,
796 f_luby,
797 0,
798 X_OBSOLETE_FLAG,
799 L_ISO_TERRITORY_CODE);
800 end if;
801 exception
802 when no_data_found then
803
804 -- bug7270106 - Need to correctly translate the provided NULL value
805 -- for inserting.
806
807 select
808 decode(x_iso_numeric_code, fnd_territories_pkg.null_char, null,
809 null, null,x_iso_numeric_code),
810 decode(x_alternate_territory_code,fnd_territories_pkg.null_char, null,
811 null, null, x_alternate_territory_code),
812 decode(x_nls_territory, fnd_territories_pkg.null_char, null,
813 null, null, x_nls_territory),
814 decode(x_address_style, fnd_territories_pkg.null_char, null,
815 null, null, x_address_style),
816 decode(x_address_validation, fnd_territories_pkg.null_char, null,
817 null, null, x_address_validation),
818 decode(x_bank_info_style, fnd_territories_pkg.null_char, null,
819 null, null, x_bank_info_style),
820 decode(x_bank_info_validation, fnd_territories_pkg.null_char, null,
821 null, null, x_bank_info_validation),
822 decode(x_eu_code, fnd_territories_pkg.null_char, null,
823 null, null, x_eu_code),
824 decode(x_iso_territory_code, fnd_territories_pkg.null_char, null,
825 null, null, x_iso_territory_code)
826 into l_iso_numeric_code, l_alternate_territory_code, l_nls_territory,
827 l_address_style, l_address_validation, l_bank_info_style,
828 l_bank_info_validation, l_eu_code, l_iso_territory_code
829 from dual;
830
831 FND_TERRITORIES_PKG.INSERT_ROW(
832 X_ROWID,
833 X_TERRITORY_CODE,
834 L_EU_CODE,
835 L_ISO_NUMERIC_CODE,
836 L_ALTERNATE_TERRITORY_CODE,
837 L_NLS_TERRITORY,
838 L_ADDRESS_STYLE,
839 L_ADDRESS_VALIDATION,
840 L_BANK_INFO_STYLE,
841 L_BANK_INFO_VALIDATION,
842 X_TERRITORY_SHORT_NAME,
843 X_DESCRIPTION,
844 f_ludate,
845 f_luby,
846 f_ludate,
847 f_luby,
848 0,
849 X_OBSOLETE_FLAG,
850 L_ISO_TERRITORY_CODE);
851 end;
852 end LOAD_ROW;
853
854 end FND_TERRITORIES_PKG;