1 PACKAGE FTE_REGION_ZONE_LOADER AUTHID CURRENT_USER AS
2 /* $Header: FTERZLRS.pls 120.2.12000000.1 2007/01/18 21:25:55 appldev ship $ */
3
4
5 -----------------------------------------------------------------------------
6 -- --
7 -- NAME: FTE_REGION_ZONE_LOADER --
8 -- TYPE: SPEC --
9 -- DESCRIPTION: Contains Zone and Region functions for R12 Bulk Loader --
10 -- --
11 -- PROCEDURES and FUNCTIONS: --
12 -- --
13 -- FUNCTION: GET_NEXT_REGION_ID --
14 -- GET_REGION --
15 -- GET_ZONE_ID --
16 -- ADD_ZONE --
17 -- PROCEDURE: --
18 -- PROCESS_DATA --
19 -- PROCESS_ZONE --
20 -- PROCESS_REGION --
21 -----------------------------------------------------------------------------
22
23 TYPE ZONE_RECORD IS RECORD(id NUMBER,
24 name VARCHAR2(100));
25
26 TYPE ZONE_TABLE IS TABLE OF ZONE_RECORD INDEX BY BINARY_INTEGER;
27
28 --_______________________________________________________________________________________--
29 --
30 -- FUNCTION GET_NEXT_REGION_ID
31 --
32 -- PURPOSE: Get the next avaiable region id for insertion
33 --
34 -- Returns region id, -1 if error occured
35 --_______________________________________________________________________________________--
36
37 FUNCTION GET_NEXT_REGION_ID RETURN NUMBER;
38
39 --_______________________________________________________________________________________--
40 --
41 -- FUNCTION GET_ZONE_ID
42 --
43 -- Purpose
44 -- Get the region_id of a zone from the wsh_regions_tl table.
45 --
46 -- IN Parameters
47 -- 1. p_zone_name: The name of the zone.
48 -- 2. p_exact_match: A boolean which specifies whether the match on zone_name
49 -- should be exact.
50 --
51 -- RETURNS: A p_zone_table. If a match was found, this p_zone_table contains a
52 -- single p_zone_record with the name and id of the FIRST match.
53 -- If there was no match found, this p_zone_record is NULL.
54 --_______________________________________________________________________________________--
55
56 FUNCTION GET_ZONE_ID(p_zone_name IN VARCHAR2) RETURN NUMBER;
57
58 --_______________________________________________________________________________________--
59 --
60 -- FUNCTION GET_REGION
61 --
62 -- Purpose: call wsh_regions_search_pkg and get region information
63 --
64 -- IN parameters:
65 -- 1. p_region_info: region information record
66 --
67 -- OUT parameters:
68 -- 1. x_status: status, -1 if no error
69 -- 2. x_error_msg: error message if error
70 --_______________________________________________________________________________________--
71
72 FUNCTION GET_REGION_ID(p_region_info IN wsh_regions_search_pkg.region_rec) RETURN NUMBER;
73
74 --_______________________________________________________________________________________--
75 --
76 -- FUNCTION GET_REGION_ID
77 --
78 -- Purpose: call wsh_regions_search_pkg and get region information
79 --
80 -- IN parameters:
81 -- 1. p_region_info: region information record
82 -- 2. p_recursively_flag: recursive search flag
83 --
84 -- OUT parameters:
85 -- 1. x_status: status, -1 if no error
86 -- 2. x_error_msg: error message if error
87 --_______________________________________________________________________________________--
88
89 FUNCTION GET_REGION_ID(p_region_info IN WSH_REGIONS_SEARCH_PKG.REGION_REC,
90 p_recursively_flag IN VARCHAR2) RETURN NUMBER;
91
92 --_______________________________________________________________________________________--
93 --
94 -- FUNCTION ADD_ZONE
95 --
96 -- Purpose: Add a zone to wsh_regions table
97 --
98 -- IN parameters:
99 -- 1. p_zone_name: name of the zone to be added
100 -- 2. p_validate_flag: validate flag
101 -- 3. p_supplier_id: supplier id
102 --
103 -- OUT parameters:
104 -- 1. x_status: status of the processing, -1 means no error
105 -- 2. x_error_msg: error message if any.
106 --
107 -- Returns zone id, -1 if any errors occured
108 --_______________________________________________________________________________________--
109
110
111 FUNCTION ADD_ZONE(p_zone_name IN VARCHAR2,
112 p_validate_flag IN BOOLEAN,
113 p_supplier_id IN NUMBER,
114 p_region_type IN VARCHAR2) RETURN NUMBER;
115
116 --_______________________________________________________________________________________--
117 --
118 -- FUNCTION INSERT_PARTY_REGION
119 --
120 -- Purpose: To insert into wsh_zone_regions for party
121 --
122 -- IN parameters:
123 -- 1. p_region_id
124 -- 2. p_parent_region_id
125 -- 3. p_supllier_id
126 -- 4. p_validate_flag
127 -- 5. p_postal_code_from
128 -- 6. p_postal_code_to
129 --
130 -- RETURN
131 -- 1. p_part_region_id
132 --_______________________________________________________________________________________--
133
134 FUNCTION INSERT_PARTY_REGION(p_region_id IN NUMBER,
135 p_parent_region_id IN NUMBER,
136 p_supplier_id IN NUMBER,
137 p_validate_flag IN BOOLEAN,
138 p_postal_code_from IN NUMBER,
139 p_postal_code_to IN NUMBER)
140 RETURN NUMBER;
141 --_______________________________________________________________________________________--
142 --
143 -- PROCEDURE PROCESS_ZONE
144 --
145 -- Purpose: process the lines in p_table for zones
146 --
147 -- IN parameters:
148 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
149 -- 2. p_line_number: line number for the beginning of the block
150 -- 3. p_region_type: type of region
151 --
152 -- OUT parameters:
153 -- 1. x_status: status of the processing, -1 means no error
154 -- 2. x_error_msg: error message if any.
155 --_______________________________________________________________________________________--
156
157 PROCEDURE PROCESS_ZONE (p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
158 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
159 p_line_number IN NUMBER,
160 p_region_type IN VARCHAR2,
161 x_status OUT NOCOPY NUMBER,
162 x_error_msg OUT NOCOPY VARCHAR2);
163
164
165 --_______________________________________________________________________________________--
166 --
167 -- PROCEDURE PROCESS_REGION
168 --
169 -- PURPOSE: process the lines in p_table for zones
170 --
171 -- IN parameters:
172 -- 1. p_table: pl/sql table of STRINGARRAY containing the block information
173 -- 2. p_line_number: line number for the beginning of the block
174 -- 3. p_region_type: type of region
175 --
176 -- OUT parameters:
177 -- 1. x_status: status of the processing, -1 means no error
178 -- 2. x_error_msg: error message if any.
179 --_______________________________________________________________________________________--
180
181 PROCEDURE PROCESS_REGION(p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
182 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
183 p_line_number IN NUMBER,
184 x_status OUT NOCOPY NUMBER,
185 x_error_msg OUT NOCOPY VARCHAR2);
186
187 --_______________________________________________________________________________________--
188 --
189 -- PROCEDURE PROCESS_DATA
190 --
191 -- Purpose: Call appropriate process function according to the type.
192 --
193 -- IN parameters:
194 -- 1. p_type: type of the block (Zone or Region)
195 -- 2. p_table: pl/sql table of STRINGARRAY containing the block information
196 -- 3. p_line_number: line number for the beginning of the block
197 --
198 -- OUT parameters:
199 -- 1. x_status: status of the processing, -1 means no error
200 -- 2. x_error_msg: error message if any.
201 --_______________________________________________________________________________________--
202
203 PROCEDURE PROCESS_DATA(p_type IN VARCHAR2,
204 p_block_header IN FTE_BULKLOAD_PKG.block_header_tbl,
205 p_block_data IN FTE_BULKLOAD_PKG.block_data_tbl,
206 p_line_number IN NUMBER,
207 x_status OUT NOCOPY NUMBER,
208 x_error_msg OUT NOCOPY VARCHAR2);
209
210
211
212
213 END FTE_REGION_ZONE_LOADER;
[Home] [Help]