[Home] [Help]
PACKAGE BODY: APPS.FND_TERRITORIES_PKG
Source
1 package body FND_TERRITORIES_PKG as
2 /* $Header: AFNLDTIB.pls 120.4.12010000.2 2008/07/31 13:20:46 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 begin
369
370 if (X_OBSOLETE_FLAG is NULL) then
371 update FND_TERRITORIES set
372 EU_CODE = X_EU_CODE,
373 ISO_NUMERIC_CODE = X_ISO_NUMERIC_CODE,
374 ALTERNATE_TERRITORY_CODE = X_ALTERNATE_TERRITORY_CODE,
375 NLS_TERRITORY = X_NLS_TERRITORY,
376 ADDRESS_STYLE = X_ADDRESS_STYLE,
377 ADDRESS_VALIDATION = X_ADDRESS_VALIDATION,
378 BANK_INFO_STYLE = X_BANK_INFO_STYLE,
379 BANK_INFO_VALIDATION = X_BANK_INFO_VALIDATION,
380 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
381 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
382 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
383 ISO_TERRITORY_CODE = X_ISO_TERRITORY_CODE
384 where TERRITORY_CODE = X_TERRITORY_CODE;
385 else
386 update FND_TERRITORIES set
387 EU_CODE = X_EU_CODE,
388 ISO_NUMERIC_CODE = X_ISO_NUMERIC_CODE,
389 ALTERNATE_TERRITORY_CODE = X_ALTERNATE_TERRITORY_CODE,
390 NLS_TERRITORY = X_NLS_TERRITORY,
391 ADDRESS_STYLE = X_ADDRESS_STYLE,
392 ADDRESS_VALIDATION = X_ADDRESS_VALIDATION,
393 BANK_INFO_STYLE = X_BANK_INFO_STYLE,
394 BANK_INFO_VALIDATION = X_BANK_INFO_VALIDATION,
395 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
396 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
397 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
398 OBSOLETE_FLAG = X_OBSOLETE_FLAG,
399 ISO_TERRITORY_CODE = X_ISO_TERRITORY_CODE
400 where TERRITORY_CODE = X_TERRITORY_CODE;
401 end if;
402
403 if (sql%notfound) then
404 raise no_data_found;
405 end if;
406
407 update FND_TERRITORIES_TL set
408 TERRITORY_SHORT_NAME = X_TERRITORY_SHORT_NAME,
409 DESCRIPTION = X_DESCRIPTION,
410 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
411 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
412 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
413 SOURCE_LANG = userenv('LANG')
414 where TERRITORY_CODE = X_TERRITORY_CODE
415 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
416
417 if (sql%notfound) then
418 raise no_data_found;
419 end if;
420 end UPDATE_ROW;
421
422 procedure DELETE_ROW (
423 X_TERRITORY_CODE in VARCHAR2
424 ) is
425 begin
426 delete from FND_TERRITORIES_TL
427 where TERRITORY_CODE = X_TERRITORY_CODE;
428
429 if (sql%notfound) then
430 raise no_data_found;
431 end if;
432
433 delete from FND_TERRITORIES
434 where TERRITORY_CODE = X_TERRITORY_CODE;
435
436 if (sql%notfound) then
437 raise no_data_found;
438 end if;
439 end DELETE_ROW;
440
441 procedure ADD_LANGUAGE
442 is
443 begin
444 /* Mar/19/03 requested by Ric Ginsberg */
445 /* The following delete and update statements are commented out */
446 /* as a quick workaround to fix the time-consuming table handler issue */
447 /* Eventually we'll need to turn them into a separate fix_language procedure */
448 /*
449
450 delete from FND_TERRITORIES_TL T
451 where not exists
452 (select NULL
453 from FND_TERRITORIES B
454 where B.TERRITORY_CODE = T.TERRITORY_CODE
455 );
456
457 update FND_TERRITORIES_TL T set (
458 TERRITORY_SHORT_NAME,
459 DESCRIPTION
460 ) = (select
461 B.TERRITORY_SHORT_NAME,
462 B.DESCRIPTION
463 from FND_TERRITORIES_TL B
464 where B.TERRITORY_CODE = T.TERRITORY_CODE
465 and B.LANGUAGE = T.SOURCE_LANG)
466 where (
467 T.TERRITORY_CODE,
468 T.LANGUAGE
469 ) in (select
470 SUBT.TERRITORY_CODE,
471 SUBT.LANGUAGE
472 from FND_TERRITORIES_TL SUBB, FND_TERRITORIES_TL SUBT
473 where SUBB.TERRITORY_CODE = SUBT.TERRITORY_CODE
474 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
478 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
475 and (SUBB.TERRITORY_SHORT_NAME <> SUBT.TERRITORY_SHORT_NAME
476 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
477 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
479 ));
480 */
481
482 insert into FND_TERRITORIES_TL (
483 TERRITORY_CODE,
484 TERRITORY_SHORT_NAME,
485 CREATED_BY,
486 CREATION_DATE,
487 LAST_UPDATED_BY,
488 LAST_UPDATE_DATE,
489 LAST_UPDATE_LOGIN,
490 DESCRIPTION,
491 LANGUAGE,
492 SOURCE_LANG
493 ) select
494 B.TERRITORY_CODE,
495 B.TERRITORY_SHORT_NAME,
496 B.CREATED_BY,
497 B.CREATION_DATE,
498 B.LAST_UPDATED_BY,
499 B.LAST_UPDATE_DATE,
500 B.LAST_UPDATE_LOGIN,
501 B.DESCRIPTION,
502 L.LANGUAGE_CODE,
503 B.SOURCE_LANG
504 from FND_TERRITORIES_TL B, FND_LANGUAGES L
505 where L.INSTALLED_FLAG in ('I', 'B')
506 and B.LANGUAGE = userenv('LANG')
507 and not exists
508 (select NULL
509 from FND_TERRITORIES_TL T
510 where T.TERRITORY_CODE = B.TERRITORY_CODE
511 and T.LANGUAGE = L.LANGUAGE_CODE);
512 end ADD_LANGUAGE;
513
514 procedure TRANSLATE_ROW (
515 X_TERRITORY_CODE in VARCHAR2,
516 X_TERRITORY_SHORT_NAME in VARCHAR2,
517 X_DESCRIPTION in VARCHAR2,
518 X_OWNER in VARCHAR2
519 ) is
520 begin
521 TRANSLATE_ROW (
522 X_TERRITORY_CODE => X_TERRITORY_CODE ,
523 X_TERRITORY_SHORT_NAME => X_TERRITORY_SHORT_NAME ,
524 X_DESCRIPTION => X_DESCRIPTION,
525 X_OWNER => X_OWNER,
526 X_LAST_UPDATE_DATE => null,
527 X_CUSTOM_MODE => null);
528 end TRANSLATE_ROW;
529
530 procedure LOAD_ROW (
531 X_TERRITORY_CODE in VARCHAR2,
532 X_EU_CODE in VARCHAR2 default NULL,
533 X_ISO_NUMERIC_CODE in VARCHAR2,
534 X_ALTERNATE_TERRITORY_CODE in VARCHAR2 default NULL,
535 X_NLS_TERRITORY in VARCHAR2 default NULL,
536 X_ADDRESS_STYLE in VARCHAR2 default NULL,
537 X_ADDRESS_VALIDATION in VARCHAR2 default NULL,
538 X_BANK_INFO_STYLE in VARCHAR2 default NULL,
539 X_BANK_INFO_VALIDATION in VARCHAR2 default NULL,
540 X_TERRITORY_SHORT_NAME in VARCHAR2,
541 X_DESCRIPTION in VARCHAR2,
542 X_OWNER in VARCHAR2
543 ) is
544 begin
545 LOAD_ROW (
546 X_TERRITORY_CODE => X_TERRITORY_CODE ,
547 X_DESCRIPTION => X_DESCRIPTION,
548 X_EU_CODE => X_EU_CODE ,
549 X_ISO_NUMERIC_CODE => X_ISO_NUMERIC_CODE ,
550 X_NLS_TERRITORY => X_NLS_TERRITORY,
551 X_ALTERNATE_TERRITORY_CODE => X_ALTERNATE_TERRITORY_CODE ,
552 X_ADDRESS_STYLE => X_ADDRESS_STYLE ,
553 X_ADDRESS_VALIDATION => X_ADDRESS_VALIDATION ,
554 X_BANK_INFO_STYLE => X_BANK_INFO_STYLE ,
555 X_OWNER => X_OWNER,
556 X_BANK_INFO_VALIDATION => X_BANK_INFO_VALIDATION ,
557 X_TERRITORY_SHORT_NAME => X_TERRITORY_SHORT_NAME ,
558 X_LAST_UPDATE_DATE => null,
559 X_CUSTOM_MODE => null);
560
561 end LOAD_ROW;
562
563
564 procedure TRANSLATE_ROW (
565 X_TERRITORY_CODE in VARCHAR2,
566 X_TERRITORY_SHORT_NAME in VARCHAR2,
567 X_DESCRIPTION in VARCHAR2,
568 X_OWNER in VARCHAR2,
569 X_LAST_UPDATE_DATE in VARCHAR2,
570 X_CUSTOM_MODE in VARCHAR2
571 ) is
572 f_luby number; -- entity owner in file
573 f_ludate date; -- entity update date in file
574 db_luby number; -- entity owner in db
575 db_ludate date; -- entity update date in db
576
577 begin
578 -- Translate owner to file_last_updated_by
579 f_luby := fnd_load_util.owner_id(x_owner);
580
581 -- Translate char last_update_date to date
582 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
583
584 select last_updated_by, last_update_date
585 into db_luby, db_ludate
586 from FND_TERRITORIES_TL
587 where TERRITORY_CODE = X_TERRITORY_CODE
588 and language = userenv('LANG');
589
590 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
591 db_ludate, X_CUSTOM_MODE)) then
592 update FND_TERRITORIES_TL set
593 DESCRIPTION = X_DESCRIPTION,
594 TERRITORY_SHORT_NAME = X_TERRITORY_SHORT_NAME,
595 LAST_UPDATE_DATE = f_ludate,
596 LAST_UPDATED_BY = f_luby,
597 LAST_UPDATE_LOGIN = 0,
598 SOURCE_LANG = userenv('LANG')
599 where TERRITORY_CODE = X_TERRITORY_CODE
600 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
601 end if;
602
603 end TRANSLATE_ROW;
604
605 procedure LOAD_ROW (
606 X_TERRITORY_CODE in VARCHAR2,
610 X_NLS_TERRITORY in VARCHAR2 default NULL,
607 X_EU_CODE in VARCHAR2 default NULL,
608 X_ISO_NUMERIC_CODE in VARCHAR2,
609 X_ALTERNATE_TERRITORY_CODE in VARCHAR2 default NULL,
611 X_ADDRESS_STYLE in VARCHAR2 default NULL,
612 X_ADDRESS_VALIDATION in VARCHAR2 default NULL,
613 X_BANK_INFO_STYLE in VARCHAR2 default NULL,
614 X_BANK_INFO_VALIDATION in VARCHAR2 default NULL,
615 X_TERRITORY_SHORT_NAME in VARCHAR2,
616 X_DESCRIPTION in VARCHAR2,
617 X_OWNER in VARCHAR2,
618 X_LAST_UPDATE_DATE in VARCHAR2,
619 X_CUSTOM_MODE in VARCHAR2
620 ) is
621 begin
622 LOAD_ROW (
623 X_TERRITORY_CODE => X_TERRITORY_CODE ,
624 X_DESCRIPTION => X_DESCRIPTION,
625 X_EU_CODE => X_EU_CODE ,
626 X_ISO_NUMERIC_CODE => X_ISO_NUMERIC_CODE ,
627 X_NLS_TERRITORY => X_NLS_TERRITORY,
628 X_ALTERNATE_TERRITORY_CODE => X_ALTERNATE_TERRITORY_CODE ,
629 X_ADDRESS_STYLE => X_ADDRESS_STYLE ,
630 X_ADDRESS_VALIDATION => X_ADDRESS_VALIDATION ,
631 X_BANK_INFO_STYLE => X_BANK_INFO_STYLE ,
632 X_OWNER => X_OWNER,
633 X_BANK_INFO_VALIDATION => X_BANK_INFO_VALIDATION ,
634 X_TERRITORY_SHORT_NAME => X_TERRITORY_SHORT_NAME ,
635 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE ,
636 X_CUSTOM_MODE => X_CUSTOM_MODE ,
637 X_OBSOLETE_FLAG => NULL);
638 end LOAD_ROW;
639
640 procedure LOAD_ROW (
641 X_TERRITORY_CODE in VARCHAR2,
642 X_EU_CODE in VARCHAR2 default NULL,
643 X_ISO_NUMERIC_CODE in VARCHAR2,
644 X_ALTERNATE_TERRITORY_CODE in VARCHAR2 default NULL,
645 X_NLS_TERRITORY in VARCHAR2 default NULL,
646 X_ADDRESS_STYLE in VARCHAR2 default NULL,
647 X_ADDRESS_VALIDATION in VARCHAR2 default NULL,
648 X_BANK_INFO_STYLE in VARCHAR2 default NULL,
649 X_BANK_INFO_VALIDATION in VARCHAR2 default NULL,
650 X_TERRITORY_SHORT_NAME in VARCHAR2,
651 X_DESCRIPTION in VARCHAR2,
652 X_OWNER in VARCHAR2,
653 X_LAST_UPDATE_DATE in VARCHAR2,
654 X_CUSTOM_MODE in VARCHAR2,
655 X_OBSOLETE_FLAG in VARCHAR2
656 ) is
657 begin
658 LOAD_ROW (
659 X_TERRITORY_CODE => X_TERRITORY_CODE ,
660 X_DESCRIPTION => X_DESCRIPTION,
661 X_EU_CODE => X_EU_CODE ,
662 X_ISO_NUMERIC_CODE => X_ISO_NUMERIC_CODE ,
663 X_NLS_TERRITORY => X_NLS_TERRITORY,
664 X_ALTERNATE_TERRITORY_CODE => X_ALTERNATE_TERRITORY_CODE ,
665 X_ADDRESS_STYLE => X_ADDRESS_STYLE ,
666 X_ADDRESS_VALIDATION => X_ADDRESS_VALIDATION ,
667 X_BANK_INFO_STYLE => X_BANK_INFO_STYLE ,
668 X_OWNER => X_OWNER,
669 X_BANK_INFO_VALIDATION => X_BANK_INFO_VALIDATION ,
670 X_TERRITORY_SHORT_NAME => X_TERRITORY_SHORT_NAME ,
671 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE ,
672 X_CUSTOM_MODE => X_CUSTOM_MODE ,
673 X_OBSOLETE_FLAG => X_OBSOLETE_FLAG ,
674 X_ISO_TERRITORY_CODE => NULL );
675 end LOAD_ROW;
676
677 procedure LOAD_ROW (
678 X_TERRITORY_CODE in VARCHAR2,
679 X_EU_CODE in VARCHAR2 default NULL,
680 X_ISO_NUMERIC_CODE in VARCHAR2,
681 X_ALTERNATE_TERRITORY_CODE in VARCHAR2 default NULL,
682 X_NLS_TERRITORY in VARCHAR2 default NULL,
683 X_ADDRESS_STYLE in VARCHAR2 default NULL,
684 X_ADDRESS_VALIDATION in VARCHAR2 default NULL,
685 X_BANK_INFO_STYLE in VARCHAR2 default NULL,
686 X_BANK_INFO_VALIDATION in VARCHAR2 default NULL,
687 X_TERRITORY_SHORT_NAME in VARCHAR2,
688 X_DESCRIPTION in VARCHAR2,
689 X_OWNER in VARCHAR2,
690 X_LAST_UPDATE_DATE in VARCHAR2,
691 X_CUSTOM_MODE in VARCHAR2,
692 X_OBSOLETE_FLAG in VARCHAR2,
693 X_ISO_TERRITORY_CODE in VARCHAR2
694 ) is
695 f_luby number; -- entity owner in file
696 f_ludate date; -- entity update date in file
697 db_luby number; -- entity owner in db
698 db_ludate date; -- entity update date in db
699 X_ROWID varchar2(64);
700 user_id number;
701
702 -- Bug4493112 - Local variables moved from UPDATE_ROW to LOAD_ROW.
703
704 L_ISO_NUMERIC_CODE VARCHAR2(3);
705 L_ALTERNATE_TERRITORY_CODE VARCHAR2(30);
706 L_NLS_TERRITORY VARCHAR2(30);
707 L_ADDRESS_STYLE VARCHAR2(30);
708 L_ADDRESS_VALIDATION VARCHAR2(30);
709 L_BANK_INFO_STYLE VARCHAR2(30);
710 L_BANK_INFO_VALIDATION VARCHAR2(30);
711 L_EU_CODE VARCHAR2(3);
712 L_ISO_TERRITORY_CODE VARCHAR2(3);
713
714 begin
715
716 -- Translate owner to file_last_updated_by
717 f_luby := fnd_load_util.owner_id(x_owner);
718
719 -- Translate char last_update_date to date
720 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
721
722 begin
723 select last_updated_by, last_update_date
724 into db_luby, db_ludate
725 from FND_TERRITORIES
726 where TERRITORY_CODE = X_TERRITORY_CODE;
727
728 -- Bug4493112 Moved decode select statement from UPDATE_ROW to LOAD_ROW.
729 -- Bug4648984 Moved sql to inside exception block to handle the
730 -- no data found.
731
732 select
733 decode(x_iso_numeric_code, fnd_territories_pkg.null_char, null,
734 null, u.iso_numeric_code,
735 x_iso_numeric_code),
739 decode(x_nls_territory, fnd_territories_pkg.null_char, null,
736 decode(x_alternate_territory_code,fnd_territories_pkg.null_char, null,
737 null, u.alternate_territory_code,
738 x_alternate_territory_code),
740 null, u.nls_territory,
741 x_nls_territory),
742 decode(x_address_style, fnd_territories_pkg.null_char, null,
743 null, u.address_style,
744 x_address_style),
745 decode(x_address_validation, fnd_territories_pkg.null_char, null,
746 null, u.address_validation,
747 x_address_validation),
748 decode(x_bank_info_style, fnd_territories_pkg.null_char, null,
749 null, u.bank_info_style,
750 x_bank_info_style),
751 decode(x_bank_info_validation, fnd_territories_pkg.null_char, null,
752 null, u.bank_info_validation,
753 x_bank_info_validation),
754 decode(x_eu_code, fnd_territories_pkg.null_char, null,
755 null, u.eu_code,
756 x_eu_code),
757 decode(x_iso_territory_code, fnd_territories_pkg.null_char, null,
758 null, u.iso_territory_code,
759 x_iso_territory_code)
760 into l_iso_numeric_code, l_alternate_territory_code, l_nls_territory,
761 l_address_style, l_address_validation, l_bank_info_style,
762 l_bank_info_validation, l_eu_code, l_iso_territory_code
763 from fnd_territories u
764 where territory_code = x_territory_code;
765
766 -- Bug4493112 Modified code to use local variables in UPDATE_ROW and
767 -- INSERT_ROW procedure calls.
768
769 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
770 db_ludate, X_CUSTOM_MODE)) then
771 FND_TERRITORIES_PKG.UPDATE_ROW(
772 X_TERRITORY_CODE,
773 L_EU_CODE,
774 L_ISO_NUMERIC_CODE,
775 L_ALTERNATE_TERRITORY_CODE,
776 L_NLS_TERRITORY,
777 L_ADDRESS_STYLE,
778 L_ADDRESS_VALIDATION,
779 L_BANK_INFO_STYLE,
780 L_BANK_INFO_VALIDATION,
781 X_TERRITORY_SHORT_NAME,
782 X_DESCRIPTION,
783 f_ludate,
784 f_luby,
785 0,
786 X_OBSOLETE_FLAG,
787 L_ISO_TERRITORY_CODE);
788 end if;
789 exception
790 when no_data_found then
791
792 -- bug7270106 - Need to correctly translate the provided NULL value
793 -- for inserting.
794
795 select
796 decode(x_iso_numeric_code, fnd_territories_pkg.null_char, null,
797 null, null,x_iso_numeric_code),
798 decode(x_alternate_territory_code,fnd_territories_pkg.null_char, null,
799 null, null, x_alternate_territory_code),
800 decode(x_nls_territory, fnd_territories_pkg.null_char, null,
801 null, null, x_nls_territory),
802 decode(x_address_style, fnd_territories_pkg.null_char, null,
803 null, null, x_address_style),
804 decode(x_address_validation, fnd_territories_pkg.null_char, null,
805 null, null, x_address_validation),
806 decode(x_bank_info_style, fnd_territories_pkg.null_char, null,
807 null, null, x_bank_info_style),
808 decode(x_bank_info_validation, fnd_territories_pkg.null_char, null,
809 null, null, x_bank_info_validation),
810 decode(x_eu_code, fnd_territories_pkg.null_char, null,
811 null, null, x_eu_code),
812 decode(x_iso_territory_code, fnd_territories_pkg.null_char, null,
813 null, null, x_iso_territory_code)
814 into l_iso_numeric_code, l_alternate_territory_code, l_nls_territory,
815 l_address_style, l_address_validation, l_bank_info_style,
816 l_bank_info_validation, l_eu_code, l_iso_territory_code
817 from dual;
818
819 FND_TERRITORIES_PKG.INSERT_ROW(
820 X_ROWID,
821 X_TERRITORY_CODE,
822 L_EU_CODE,
823 L_ISO_NUMERIC_CODE,
824 L_ALTERNATE_TERRITORY_CODE,
825 L_NLS_TERRITORY,
826 L_ADDRESS_STYLE,
827 L_ADDRESS_VALIDATION,
828 L_BANK_INFO_STYLE,
829 L_BANK_INFO_VALIDATION,
830 X_TERRITORY_SHORT_NAME,
831 X_DESCRIPTION,
832 f_ludate,
833 f_luby,
834 f_ludate,
835 f_luby,
836 0,
837 X_OBSOLETE_FLAG,
838 L_ISO_TERRITORY_CODE);
839 end;
840 end LOAD_ROW;
841
842 end FND_TERRITORIES_PKG;