[Home] [Help]
PACKAGE BODY: APPS.FTE_REGION_ZONE_LOADER
Source
1 PACKAGE BODY FTE_REGION_ZONE_LOADER AS
2 /* $Header: FTERZLRB.pls 120.5.12000000.2 2007/07/24 10:29:21 sankarun ship $ */
3 -------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_REGION_ZONE_LOADER --
6 -- TYPE: BODY --
7 -- DESCRIPTION: Contains Zone and Region functions for R12 Bulk Loader --
8 -- --
9 -- PROCEDURES and FUNCTIONS: --
10 -- --
11 -- FUNCTION GET_NEXT_REGION_ID --
12 -- GET_ZONE_ID --
13 -- ADD_ZONE --
14 -- PROCEDURE --
15 -- PROCESS_DATA --
16 -- PROCESS_ZONE --
17 -- PROCESS_REGION --
18 -------------------------------------------------------------------------- --
19
20 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_REGION_ZONE_LOADER';
21 G_USER_ID CONSTANT NUMBER := FND_GLOBAL.USER_ID;
22
23 TYPE LANE_ID_TBL IS TABLE OF FTE_LANES.LANE_ID%TYPE INDEX BY BINARY_INTEGER;
24
25 TYPE REGION_ID_TAB IS TABLE OF WSH_REGIONS_INTERFACE.REGION_ID%TYPE INDEX BY BINARY_INTEGER;
26 TYPE COUNTRY_CODE_TAB IS TABLE OF WSH_REGIONS_INTERFACE.COUNTRY_CODE%TYPE INDEX BY BINARY_INTEGER;
27 TYPE STATE_CODE_TAB IS TABLE OF WSH_REGIONS_INTERFACE.STATE_CODE%TYPE INDEX BY BINARY_INTEGER;
28 TYPE CITY_CODE_TAB IS TABLE OF WSH_REGIONS_INTERFACE.CITY_CODE%TYPE INDEX BY BINARY_INTEGER;
29
30 TYPE LANGUAGE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.LANGUAGE%TYPE INDEX BY BINARY_INTEGER;
31 TYPE COUNTRY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.COUNTRY%TYPE INDEX BY BINARY_INTEGER;
32 TYPE STATE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.STATE%TYPE INDEX BY BINARY_INTEGER;
33 TYPE CITY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.CITY%TYPE INDEX BY BINARY_INTEGER;
34 TYPE POSTAL_CODE_FROM_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_FROM%TYPE INDEX BY BINARY_INTEGER;
35 TYPE POSTAL_CODE_TO_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_TO%TYPE INDEX BY BINARY_INTEGER;
36
37
38 --_______________________________________________________________________________________--
39 --
40 -- FUNCTION GET_NEXT_REGION_ID
41 --
42 -- PURPOSE: Get the next avaiable region id for insertion
43 --
44 -- Returns region id, -1 if error occured
45 --_______________________________________________________________________________________--
46
47 FUNCTION GET_NEXT_REGION_ID RETURN NUMBER IS
48
49 CURSOR GET_REGION_ID IS
50 SELECT WSH_REGIONS_S.NEXTVAL FROM DUAL;
51 l_region_id NUMBER := -1;
52
53 BEGIN
54
55 OPEN GET_REGION_ID;
56 FETCH GET_REGION_ID INTO l_region_id;
57 CLOSE GET_REGION_ID;
58
59 RETURN l_region_id;
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 IF ( GET_REGION_ID%ISOPEN) THEN
64 CLOSE GET_REGION_ID;
65 END IF;
66 FTE_UTIL_PKG.Write_LogFile('GET_NEXT_REGION_ID', 'UNEXPECTED ERROR',sqlerrm);
67 RAISE;
68 END GET_NEXT_REGION_ID;
69
70 --_______________________________________________________________________________________--
71 --
72 -- FUNCTION GET_ZONE_ID
73 --
74 -- Purpose
75 -- Get the region_id of a zone from the wsh_regions_tl table.
76 --
77 -- IN Parameters
78 -- 1. p_zone_name: The name of the zone.
79 -- 2. p_exact_match: A boolean which specifies whether the match on zone_name
80 -- should be exact.
81 --
82 -- RETURNS: A p_zone_table. If a match was found, this p_zone_table contains a
83 -- single p_zone_record with the name and id of the FIRST match.
84 -- If there was no match found, this p_zone_record is NULL.
85 --_______________________________________________________________________________________--
86
87 FUNCTION GET_ZONE_ID(p_zone_name IN VARCHAR2 ) RETURN NUMBER IS
88
89 CURSOR GET_ZONE_ID(p_zone_name VARCHAR2) IS
90 SELECT region_id
91 FROM wsh_regions_tl
92 WHERE zone = p_zone_name
93 --BUG 6067174 : Zone ID returned should be specific to the instance language.
94 and language = userenv('lang');
95
96 l_zone_id NUMBER := -1;
97 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.GET_ZONE_ID';
98
99 BEGIN
100
101 FTE_UTIL_PKG.Enter_Debug(l_module_name);
102 FTE_UTIL_PKG.Write_LogFile(l_module_name,'p_zone_name',p_zone_name);
103
104 OPEN GET_ZONE_ID(p_zone_name);
105 FETCH GET_ZONE_ID INTO l_zone_id;
106 CLOSE GET_ZONE_ID;
107
108 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
109 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_zone_id', l_zone_id);
110 END IF;
111
112 FTE_UTIL_PKG.Exit_Debug(l_module_name);
113 RETURN l_zone_id;
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 FTE_UTIL_PKG.Exit_Debug(l_module_name);
118 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXPECTED ERROR in GET_ZONE_ID',sqlerrm);
119 RAISE;
120 END GET_ZONE_ID;
121
122
123 --_______________________________________________________________________________________--
124 --
125 -- FUNCTION ADD_ZONE
126 --
127 -- Purpose: Add a zone to wsh_regions table
128 --
129 -- IN parameters:
130 -- 1. p_zone_name: name of the zone to be added
131 -- 2. p_validate_flag: validate flag
132 -- 3. p_supplier_id: supplier id
133 --
134 -- OUT parameters:
135 -- 1. x_status: status of the processing, -1 means no error
136 -- 2. x_error_msg: error message if any.
137 --
138 -- Returns zone id, -1 if any errors occured
139 --_______________________________________________________________________________________--
140
141 FUNCTION ADD_ZONE(p_zone_name IN VARCHAR2,
142 p_validate_flag IN BOOLEAN,
143 p_supplier_id IN NUMBER,
144 p_region_type IN VARCHAR2) RETURN NUMBER IS
145
146 l_zone_id NUMBER := -1;
147 l_rows_affected NUMBER;
148
149 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.ADD_ZONE';
150
151 BEGIN
152
153 FTE_UTIL_PKG.Enter_Debug(l_module_name);
154
155 IF ( p_validate_flag ) THEN
156 l_zone_id := FTE_REGION_ZONE_LOADER.GET_ZONE_ID(p_zone_name);
157 END IF;
158
159 IF (l_zone_id = -1) THEN
160
161 l_zone_id := GET_NEXT_REGION_ID;
162
163 IF ( FTE_BULKLOAD_PKG.g_debug_on ) THEN
164 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'l_zone_id', l_zone_id);
165 END IF;
166
167 IF (l_zone_id = -1) THEN
168 RETURN l_zone_id;
169 END IF;
170
171 INSERT INTO WSH_REGIONS(
172 REGION_ID,
173 PARENT_REGION_ID,
174 REGION_TYPE,
175 LAST_UPDATE_DATE,
176 LAST_UPDATED_BY,
177 CREATION_DATE,
178 CREATED_BY)
179 VALUES(
180 l_zone_id,
181 -1,
182 p_region_type,
183 SYSDATE,
184 G_USER_ID,
185 SYSDATE,
186 G_USER_ID);
187
188 INSERT INTO WSH_REGIONS_TL(
189 REGION_ID,
190 LANGUAGE,
191 ZONE,
192 LAST_UPDATE_DATE,
193 LAST_UPDATED_BY,
194 CREATION_DATE,
195 CREATED_BY)
196 VALUES(
197 l_zone_id,
198 USERENV('LANG'),
199 p_zone_name,
200 SYSDATE,
201 G_USER_ID,
202 SYSDATE,
203 G_USER_ID);
204 END IF;
205
206 FTE_UTIL_PKG.Exit_Debug(l_module_name);
207 RETURN l_zone_id;
208
209 EXCEPTION
210 WHEN OTHERS THEN
211 l_zone_id := -1;
212 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR', SQLERRM);
213 FTE_UTIL_PKG.Exit_Debug(l_module_name);
214 RETURN l_zone_id;
215 END ADD_ZONE;
216
217 --_______________________________________________________________________________________--
218 --
219 -- FUNCTION GET_REGION_ID
220 --
221 -- Purpose: call wsh_regions_search_pkg and get region information
222 --
223 -- IN parameters:
224 -- 1. p_region_info: region information record
225 --
226 -- OUT parameters:
227 -- 1. x_status: status, -1 if no error
228 -- 2. x_error_msg: error message if error
229 --_______________________________________________________________________________________--
230
231 FUNCTION GET_REGION_ID(p_region_info IN WSH_REGIONS_SEARCH_PKG.REGION_REC)
232
233 RETURN NUMBER IS
234
235 x_region_info WSH_REGIONS_SEARCH_PKG.REGION_REC;
236
237 l_country_code VARCHAR2(3) := '';
238 l_country VARCHAR2(50);
239 l_state P_REGION_INFO.STATE%TYPE;
240 l_city P_REGION_INFO.CITY%TYPE;
241 l_city_code VARCHAR2(2) := '';
242 l_state_code VARCHAR2(3) := '';
243
244 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.GET_REGION_ID';
245
246 BEGIN
247
248 FTE_UTIL_PKG.Enter_Debug(l_module_name);
249
250 l_country := p_region_info.COUNTRY;
251 l_city := p_region_info.CITY;
252 l_state := p_region_info.STATE;
253
254 --+
255 -- WSH_REGIONS_SEARCH_PKG.Get_Region_Info expects a country
256 -- name as its first argument, and not a country code
257 -- i.e. 'United States' instead of 'US'. If we THINK that the
258 -- 'country' argument supplied is a country code, we pass it as
259 -- the 'country code' argument.
260 --+
261
262 IF (LENGTH(l_country) <= 3) THEN
263 -- we are assuming that no country name has 3 or less characters.
264 l_country_code := l_country;
265 l_country := NULL;
266 END IF;
267
268 IF (LENGTH(l_state) <= 3) THEN
269 l_state_code := l_state;
270 l_state := NULL;
271 END IF;
272
273 IF (LENGTH(l_city) <= 2) THEN
274 l_city_code := l_city;
275 l_city := NULL;
276 END IF;
277
278 WSH_REGIONS_SEARCH_PKG.Get_Region_Info(
279 p_country => l_country,
280 p_country_region => '',
281 p_state => l_state,
282 p_city => l_city,
283 p_postal_code_from => p_region_info.postal_code_from,
284 p_postal_code_to => p_region_info.postal_code_to,
285 p_zone => p_region_info.zone,
286 p_lang_code => 'US',
287 p_country_code => l_country_code,
288 p_country_region_code => '',
289 p_state_code => l_state_code,
290 p_city_code => l_city_code,
291 p_region_type => '',
292 p_interface_flag => 'N',
293 p_search_flag => 'Y',
294 x_region_info => x_region_info);
295
296 FTE_UTIL_PKG.Exit_Debug(l_module_name);
297 RETURN x_region_info.region_id;
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXPECTED ERROR', sqlerrm);
302 FTE_UTIL_PKG.Exit_Debug(l_module_name);
303 END GET_REGION_ID;
304
305
306 --_______________________________________________________________________________________--
307 --
308 -- FUNCTION GET_REGION_ID
309 --
310 -- Purpose: call wsh_regions_search_pkg and get region information
311 --
312 -- IN parameters:
313 -- 1. p_region_info: region information record
314 -- 2. p_recursively_flag: recursive search flag
315 --
316 -- RETURN
317 -- 1. region id
318 --_______________________________________________________________________________________--
319
320 FUNCTION GET_REGION_ID(p_region_info IN WSH_REGIONS_SEARCH_PKG.REGION_REC,
321 p_recursively_flag IN VARCHAR2)
322
323 RETURN NUMBER IS
324
325 x_region_id NUMBER;
326
327 l_country_code VARCHAR2(3) := '';
328 l_country VARCHAR2(50);
329 l_state P_REGION_INFO.STATE%TYPE;
330 l_city P_REGION_INFO.CITY%TYPE;
331 l_city_code VARCHAR2(2) := '';
332 l_state_code VARCHAR2(3) := '';
333
334 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.GET_REGION_ID';
335
336 BEGIN
337
338 FTE_UTIL_PKG.Enter_Debug(l_module_name);
339
340 l_country := p_region_info.COUNTRY;
341 l_city := p_region_info.CITY;
342 l_state := p_region_info.STATE;
343
344 --+
345 -- WSH_REGIONS_SEARCH_PKG.Get_Region_Info expects a country
346 -- name as its first argument, and not a country code
347 -- i.e. 'United States' instead of 'US'. If we THINK that the
348 -- 'country' argument supplied is a country code, we pass it as
349 -- the 'country code' argument.
350 --+
351 IF (LENGTH(l_country) <= 3) THEN
352 -- we are assuming that no country name has 3 or less characters.
353 l_country_code := l_country;
354 l_country := NULL;
355 END IF;
356 IF (LENGTH(l_state) <= 3) THEN
357 l_state_code := l_state;
358 l_state := NULL;
359 END IF;
360 IF (LENGTH(l_city) <= 2) THEN
361 l_city_code := l_city;
362 l_city := NULL;
363 END IF;
364
365 WSH_REGIONS_SEARCH_PKG.Get_Region_Info(
366 p_country => l_country,
367 p_country_region => '',
368 p_state => l_state,
369 p_city => l_city,
370 p_postal_code_from => p_region_info.postal_code_from,
371 p_postal_code_to => p_region_info.postal_code_to,
372 p_zone => p_region_info.zone,
373 p_lang_code => 'US',
374 p_country_code => l_country_code,
375 p_country_region_code => '',
376 p_state_code => l_state_code,
377 p_city_code => l_city_code,
378 p_region_type => '',
379 p_interface_flag => 'N',
380 p_search_flag => 'Y',
381 p_recursively_flag => 'Y',
382 x_region_id => x_region_id);
383
384 FTE_UTIL_PKG.Exit_Debug(l_module_name);
385
386 RETURN x_region_id;
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXPECTED ERROR', sqlerrm);
391 FTE_UTIL_PKG.Exit_Debug(l_module_name);
392 END GET_REGION_ID;
393
394 --_______________________________________________________________________________________--
395 --
396 -- FUNCTION INSERT_PARTY_REGION
397 --
398 -- Purpose: To insert into wsh_zone_regions for party
399 --
400 -- IN parameters:
401 -- 1. p_region_id
402 -- 2. p_parent_region_id
403 -- 3. p_supllier_id
404 -- 4. p_validate_flag
405 -- 5. p_postal_code_from
406 -- 6. p_postal_code_to
407 --
408 -- RETURN
409 -- 1. p_part_region_id
410 --_______________________________________________________________________________________--
411
412 FUNCTION INSERT_PARTY_REGION(p_region_id IN NUMBER,
413 p_parent_region_id IN NUMBER,
414 p_supplier_id IN NUMBER,
415 p_validate_flag IN BOOLEAN,
416 p_postal_code_from IN NUMBER,
417 p_postal_code_to IN NUMBER)
418 RETURN NUMBER IS
419
420 l_result NUMBER := -1;
421 l_party_region_id NUMBER := -1;
422
423 l_postal_code_from VARCHAR2(25) := '';
424 l_postal_code_to VARCHAR2(25) := '';
425
426 l_zone_flag VARCHAR2(3);
427
428 CURSOR GET_PARTY_REGION_ID IS
429 SELECT
430 zone_region_id
431 FROM
432 wsh_zone_regions
433 WHERE
434 region_id = p_region_id AND
435 parent_region_id = p_parent_region_id AND
436 party_id = p_supplier_id AND
437 (p_postal_code_from IS NULL OR postal_code_from = p_postal_code_from) AND
438 (p_postal_code_to IS NULL OR postal_code_to = p_postal_code_to) ;
439
440 CURSOR GET_NEXT_PARTY_REGION_ID IS
441 SELECT WSH_ZONE_REGIONS_S.NEXTVAL FROM DUAL;
442
443 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.INSERT_PARTY_REGION';
444
445 BEGIN
446
447 FTE_UTIL_PKG.Enter_Debug(l_module_name);
448 IF (p_validate_flag) THEN
449 OPEN GET_PARTY_REGION_ID;
450 FETCH GET_PARTY_REGION_ID INTO l_party_region_id;
451 CLOSE GET_PARTY_REGION_ID;
452
453 IF (l_party_region_id <> -1 ) THEN
454 FTE_UTIL_PKG.Exit_Debug(l_module_name);
455 RETURN l_party_region_id;
456 END IF;
457 END IF;
458
459 OPEN GET_NEXT_PARTY_REGION_ID;
460 FETCH GET_NEXT_PARTY_REGION_ID INTO l_party_region_id;
461 CLOSE GET_NEXT_PARTY_REGION_ID;
462
463 IF (p_region_id = p_parent_region_id) THEN
464 l_zone_flag := 'N';
465 ELSE
466 l_zone_flag := 'Y';
467 END IF;
468
469 IF (p_postal_code_from IS NOT NULL) THEN
470 l_postal_code_from := p_postal_code_from;
471 IF (p_postal_code_to IS NULL) THEN
472 l_postal_code_to := p_postal_code_to;
473 END IF;
474 END IF;
475
476 INSERT INTO WSH_ZONE_REGIONS(
477 ZONE_REGION_ID,
478 REGION_ID,
479 PARENT_REGION_ID,
480 PARTY_ID,
481 ZONE_FLAG,
482 LAST_UPDATE_DATE,
483 LAST_UPDATED_BY,
484 CREATION_DATE,
485 CREATED_BY,
486 POSTAL_CODE_FROM,
487 POSTAL_CODE_TO)
488 VALUES(
489 l_party_region_id,
490 p_region_id,
491 p_parent_region_id,
492 p_supplier_id,
493 l_zone_flag,
494 SYSDATE,
495 FND_GLOBAL.USER_ID,
496 SYSDATE,
497 FND_GLOBAL.USER_ID,
498 l_postal_code_from,
499 l_postal_code_to);
500
501 IF (SQL%ROWCOUNT < 0) THEN
502 l_party_region_id := -1;
503 END IF;
504
505 FTE_UTIL_PKG.Exit_Debug(l_module_name);
506 RETURN l_party_region_id;
507
508 EXCEPTION
509 WHEN OTHERS THEN
510
511 IF (GET_NEXT_PARTY_REGION_ID%ISOPEN) THEN
512 CLOSE GET_NEXT_PARTY_REGION_ID;
513 END IF;
514
515 IF (GET_PARTY_REGION_ID%ISOPEN) THEN
516 CLOSE GET_PARTY_REGION_ID;
517 END IF;
518
519 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXPECTED ERROR', sqlerrm);
520 FTE_UTIL_PKG.Exit_Debug(l_module_name);
521
522 END INSERT_PARTY_REGION;
523
524 --_______________________________________________________________________________________--
525 --
526 -- PROCEDURE PROCESS_ZONE
527 --
528 -- Purpose: process the lines in p_table for zones
529 --
530 -- IN parameters:
531 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
532 -- 2. p_line_number: line number for the beginning of the block
533 -- 3. p_region_type: type of region
534 --
535 -- OUT parameters:
536 -- 1. x_status: status of the processing, -1 means no error
537 -- 2. x_error_msg: error message if any.
538 --_______________________________________________________________________________________--
539
540 PROCEDURE PROCESS_ZONE (p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
541 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
542 p_line_number IN NUMBER,
543 p_region_type IN VARCHAR2,
544 x_status OUT NOCOPY NUMBER,
545 x_error_msg OUT NOCOPY VARCHAR2) IS
546
547 l_action VARCHAR2(100);
548 l_zone_name VARCHAR2(200);
549 l_country VARCHAR2(100);
550 l_zone_id NUMBER;
551 l_region_id NUMBER;
552 l_region_rec WSH_REGIONS_SEARCH_PKG.region_rec;
553 l_temp_id NUMBER;
554
555 l_values FTE_BULKLOAD_PKG.data_values_tbl;
556
557 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.PROCESS_ZONE';
558
559 BEGIN
560 FTE_UTIL_PKG.Enter_Debug(l_module_name);
561 x_status := -1;
562
563 --+
564 -- Validate the column names
565 --+
566 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
567 p_type => 'ZONE',
568 p_line_number => p_line_number-1,
569 x_status => x_status,
570 x_error_msg => x_error_msg);
571 FOR i IN 1..p_block_data.COUNT LOOP
572
573 l_values := p_block_data(i);
574
575 FTE_VALIDATION_PKG.VALIDATE_ZONE(p_values => l_values,
576 p_line_number => p_line_number+i-1,
577 p_region_type => p_region_type,
578 p_action => l_action,
579 p_zone_name => l_zone_name,
580 p_country => l_country,
581 p_zone_id => l_zone_id,
582 p_region_rec => l_region_rec,
583 p_region_id => l_region_id,
584 x_status => x_status,
585 x_error_msg => x_error_msg);
586 IF (x_status <> -1) THEN
587 FTE_UTIL_PKG.Write_LogFile(l_module_name,'FTE_VALIDATION_PKG.VALIDATE_ZONE returned with error ', x_error_msg);
588 FTE_UTIL_PKG.Exit_Debug(l_module_name);
589 RETURN;
590 END IF;
591
592 IF ( FTE_BULKLOAD_PKG.g_debug_on ) THEN
593 FTE_UTIL_PKG.Write_LogFile(l_module_name, ' l_zone_id', l_zone_id);
594 FTE_UTIL_PKG.Write_LogFile(l_module_name, ' l_region_id', l_region_id);
595 END IF;
596
597 IF (l_zone_id = -1) THEN
598
599 l_zone_id := ADD_ZONE(l_zone_name, FALSE, -1, p_region_type);
600
601 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
602 FTE_UTIL_PKG.Write_LogFile(l_module_name, ' l_zone_id', l_zone_id);
603 END IF;
604
605 END IF;
606
607 IF (l_zone_id = -1) THEN
608 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'Unable to create new Zone');
609 RETURN;
610 END IF;
611
612 WSH_REGIONS_PKG.UPDATE_ZONE_REGION(p_insert_type => 'INSERT',
613 p_zone_region_id => l_region_id,
614 p_zone_id => l_zone_id,
615 p_region_id => l_region_id,
616 p_country => l_region_rec.country,
617 p_state => l_region_rec.state,
618 p_city => l_region_rec.city,
619 p_postal_code_from => l_region_rec.postal_code_from,
620 p_postal_code_to => l_region_rec.postal_code_to,
621 p_lang_code => USERENV('LANG'),
622 p_country_code => '',
623 p_state_code => '',
624 p_city_code => '',
625 p_user_id => -1,
626 p_zone_type => p_region_type,
627 x_zone_region_id => l_temp_id,
628 x_region_id => l_region_id,
629 x_status => x_status,
630 x_error_msg => x_error_msg);
631
632 IF (x_status = 2) THEN
633 FTE_UTIL_PKG.Write_LogFile(l_module_name,'WSH_REGIONS_PKG.UPDATE_ZONE_REGION returned with error',x_error_msg);
634 FTE_UTIL_PKG.Exit_Debug(l_module_name);
635 RETURN;
636 END IF;
637
638 IF (x_error_msg IS NOT NULL) THEN
639 IF (x_error_msg = 'WSH_REGION_NOT_FOUND') THEN
640 FTE_UTIL_PKG.Write_OutFile('FTE__REGION_NOT_FOUND', 'C', p_line_number);
641 FTE_UTIL_PKG.Exit_Debug(l_module_name);
642 RETURN;
643 ELSIF (x_error_msg = 'WSH_REGION_EXISTS_IN_ZONE') THEN
644 FTE_UTIL_PKG.Write_OutFile('FTE_REGION_EXISTS_IN_ZONE', 'D', p_line_number);
645 FTE_UTIL_PKG.Exit_Debug(l_module_name);
646 RETURN;
647 ELSIF (x_error_msg <> 'WSH_SAME_REGION_IN_ZONE') THEN
648 FTE_UTIL_PKG.Write_OutFile('FTE_SAME_REGION_IN_ZONE', 'D', p_line_number);
649 FTE_UTIL_PKG.Exit_Debug(l_module_name);
650 RETURN;
651 ELSE -- ignore error FTE_SAME_REGION_IN_ZONE
652 x_status := -1;
653 END IF;
654 ELSE
655 x_status := -1;
656 END IF;
657 END LOOP;
658
659 FTE_UTIL_PKG.Exit_Debug(l_module_name);
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 x_status := 2;
664 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR', SQLERRM);
665 FTE_UTIL_PKG.Exit_Debug(l_module_name);
666
667 END PROCESS_ZONE;
668
669
670 --_______________________________________________________________________________________--
671 --
672 -- PROCEDURE PROCESS_REGION
673 --
674 -- PURPOSE: process the lines in p_table for zones
675 --
676 -- IN parameters:
677 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
678 -- 2. p_line_number: line number for the beginning of the block
679 -- 3. p_region_type: type of region
680 --
681 -- OUT parameters:
682 -- 1. x_status: status of the processing, -1 means no error
683 -- 2. x_error_msg: error message if any.
684 --_______________________________________________________________________________________--
685
686 PROCEDURE PROCESS_REGION(p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
687 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
688 p_line_number IN NUMBER,
689 x_status OUT NOCOPY NUMBER,
690 x_error_msg OUT NOCOPY VARCHAR2) IS
691
692 l_action VARCHAR2(25);
693 l_interface_region_id NUMBER;
694 l_type CONSTANT VARCHAR2(15) := 'REGION';
695
696 l_request_id NUMBER;
697
698 l_values FTE_BULKLOAD_PKG.data_values_tbl;
699
700 L_REGION_ID REGION_ID_TAB;
701 L_COUNTRY_CODE COUNTRY_CODE_TAB;
702 L_STATE_CODE STATE_CODE_TAB;
703 L_CITY_CODE CITY_CODE_TAB;
704 L_LANGUAGE LANGUAGE_TAB;
705 L_COUNTRY COUNTRY_TAB;
706 L_STATE STATE_TAB;
707 L_CITY CITY_TAB;
708 L_POSTAL_CODE_FROM POSTAL_CODE_FROM_TAB;
709 L_POSTAL_CODE_TO POSTAL_CODE_TO_TAB;
710
711 CURSOR GET_INTERFACE_REGION_ID IS
712 SELECT WSH_REGIONS_INTERFACE_S.NEXTVAL
713 FROM DUAL;
714
715 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.PROCESS_REGION';
716
717 BEGIN
718
719 FTE_UTIL_PKG.Enter_Debug(l_module_name);
720 x_status := -1;
721
722 FTE_VALIDATION_PKG.VALIDATE_COLUMNS(p_keys => p_block_header,
723 p_type => l_type,
724 p_line_number => p_line_number-1,
725 x_status => x_status,
726 x_error_msg => x_error_msg);
727
728 FOR i IN 1..p_block_data.COUNT LOOP
729
730 l_values := p_block_data(i);
731 l_action := l_values('ACTION');
732
733 l_action := UPPER(l_action);
734
735 FTE_VALIDATION_PKG.VALIDATE_ACTION(p_action => l_action,
736 p_type => l_type,
737 p_line_number => p_line_number + i + 1,
738 x_status => x_status,
739 x_error_msg => x_error_msg);
740 IF (x_status <> -1) THEN
741 FTE_UTIL_PKG.Write_LogFile(l_module_name,'FTE_VALIDATION_PKG.VALIDATE_ACTION failed');
742 FTE_UTIL_PKG.Exit_Debug(l_module_name);
743 RETURN;
744 END IF;
745
746 IF (l_action = 'ADD') THEN
747
748 OPEN GET_INTERFACE_REGION_ID;
749 FETCH GET_INTERFACE_REGION_ID INTO l_interface_region_id;
750 CLOSE GET_INTERFACE_REGION_ID;
751
752 L_REGION_ID(i) := l_interface_region_id;
753 L_COUNTRY_CODE(i):= FTE_UTIL_PKG.GET_DATA('COUNTRY_CODE', l_values);
754 L_STATE_CODE(i) := FTE_UTIL_PKG.GET_DATA('STATE_CODE', l_values);
755 L_CITY_CODE(i) := FTE_UTIL_PKG.GET_DATA('CITY_CODE', l_values);
756
757 L_LANGUAGE(i) := NVL(USERENV('LANG'),'US');
758 L_COUNTRY(i) := FTE_UTIL_PKG.GET_DATA('COUNTRY', l_values);
759 L_STATE(i) := FTE_UTIL_PKG.GET_DATA('STATE', l_values);
760 L_CITY(i) := FTE_UTIL_PKG.GET_DATA('CITY', l_values);
761 L_POSTAL_CODE_FROM(i) := FTE_UTIL_PKG.GET_DATA('POSTAL_CODE_FROM', l_values);
762 L_POSTAL_CODE_TO(i) := FTE_UTIL_PKG.GET_DATA('POSTAL_CODE_TO', l_values);
763
764 END IF;
765
766 END LOOP;
767
768 FORALL i in L_REGION_ID.FIRST..L_REGION_ID.LAST
769
770 INSERT INTO WSH_REGIONS_INTERFACE(
771 REGION_ID,
772 PARENT_REGION_ID,
773 COUNTRY_CODE,
774 STATE_CODE,
775 CITY_CODE,
776 CREATED_BY,
777 CREATION_DATE,
778 LAST_UPDATED_BY,
779 LAST_UPDATE_DATE,
780 LAST_UPDATE_LOGIN,
781 PROCESSED_FLAG)
782 VALUES(
783 L_REGION_ID(i),
784 -1,
785 L_COUNTRY_CODE(i),
786 L_STATE_CODE(i),
787 L_CITY_CODE(i),
788 FND_GLOBAL.USER_ID,
789 SYSDATE,
790 FND_GLOBAL.USER_ID,
791 SYSDATE,
792 FND_GLOBAL.USER_ID,
793 NULL);
794
795 FORALL i in L_REGION_ID.FIRST..L_REGION_ID.LAST
796
797 INSERT INTO WSH_REGIONS_TL_INTERFACE(
798 LANGUAGE,
799 REGION_ID,
800 COUNTRY,
801 STATE,
802 CITY,
803 POSTAL_CODE_FROM,
804 POSTAL_CODE_TO,
805 CREATED_BY,
806 CREATION_DATE,
807 LAST_UPDATED_BY,
808 LAST_UPDATE_DATE,
809 LAST_UPDATE_LOGIN)
810 VALUES(
811 L_LANGUAGE(i),
812 L_REGION_ID(i),
813 L_COUNTRY(i),
814 L_STATE(i),
815 L_CITY(i),
816 L_POSTAL_CODE_FROM(i),
817 L_POSTAL_CODE_TO(i),
818 FND_GLOBAL.USER_ID,
819 SYSDATE,
820 FND_GLOBAL.USER_ID,
821 SYSDATE,
822 FND_GLOBAL.USER_ID);
823
824 l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'WSH',
825 program => 'WSHRGINT',
826 description => null,
827 start_time => null,
828 sub_request => false);
829 IF (FTE_BULKLOAD_PKG.g_debug_on) THEN
830 FTE_UTIL_PKG.Write_LogFile(l_module_name,'l_request_id',l_request_id);
831 END IF;
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 x_status := 2;
836 IF (GET_INTERFACE_REGION_ID%ISOPEN) THEN
837 CLOSE GET_INTERFACE_REGION_ID;
838 END IF;
839
840 FTE_UTIL_PKG.Write_LogFile(l_module_name, 'UNEXPECTED ERROR', SQLERRM);
841 FTE_UTIL_PKG.Exit_Debug(l_module_name);
842
843 END PROCESS_REGION;
844
845 --_______________________________________________________________________________________--
846 --
847 -- PROCEDURE PROCESS_DATA
848 --
849 -- Purpose: Call appropriate process function according to the type.
850 --
851 -- IN parameters:
852 -- 1. p_type: type of the block (Zone or Region)
853 -- 2. p_table: pl/sql table of STRINGARRAY containing the block information
854 -- 3. p_line_number: line number for the beginning of the block
855 --
856 -- OUT parameters:
857 -- 1. x_status: status of the processing, -1 means no error
858 -- 2. x_error_msg: error message if any.
859 --_______________________________________________________________________________________--
860
861 PROCEDURE PROCESS_DATA(p_type IN VARCHAR2,
862 p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
863 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
864 p_line_number IN NUMBER,
865 x_status OUT NOCOPY NUMBER,
866 x_error_msg OUT NOCOPY VARCHAR2) IS
867
868 l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.PROCESS_DATA';
869
870 BEGIN
871 FTE_UTIL_PKG.Enter_Debug(l_module_name);
872 x_status := -1;
873
874 IF(FTE_BULKLOAD_PKG.g_debug_on) THEN
875 FTE_UTIL_PKG.Write_LogFile(l_module_name,'p_type', p_type);
876 END IF;
877
878 IF (p_type = 'ZONE') THEN
879
880 PROCESS_ZONE(p_block_header => p_block_header,
881 p_block_data => p_block_data,
882 p_line_number => p_line_number,
883 p_region_type => '10',
884 x_status => x_status,
885 x_error_msg => x_error_msg);
886
887 ELSIF (p_type = 'REGION') THEN
888
889 PROCESS_REGION(p_block_header => p_block_header,
890 p_block_data => p_block_data,
891 p_line_number => p_line_number,
892 x_status => x_status,
893 x_error_msg => x_error_msg);
894 ELSE
895
896 x_status := 2;
897 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Invalid Type',p_type);
898 FTE_UTIL_PKG.Exit_Debug(l_module_name);
899 RETURN;
900
901 END IF;
902
903 IF( x_status <> -1) THEN
904 FTE_UTIL_PKG.Write_LogFile(l_module_name,'Error occured in process zone');
905 END IF;
906
907 FTE_UTIL_PKG.Exit_Debug(l_module_name);
908
909 EXCEPTION
910 WHEN OTHERS THEN
911 x_status := 2;
912 FTE_UTIL_PKG.Write_LogFile(l_module_name,'UNEXPECTED ERROR OCCURED', sqlerrm);
913 FTE_UTIL_PKG.Exit_Debug(l_module_name);
914 END PROCESS_DATA;
915
916 END FTE_REGION_ZONE_LOADER;