[Home] [Help]
PACKAGE BODY: APPS.FTE_DIST_UTIL_PKG
Source
1 PACKAGE BODY FTE_DIST_UTIL_PKG AS
2 /* $Header: FTEDISXB.pls 115.1 2003/09/13 19:46:12 ablundel noship $ */
3 -- -------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_DIST_UTIL_PKG --
6 -- TYPE: PACKAGE BODY --
7 -- DESCRIPTION: Contains util procedures for mileage integration stuff --
8 -- --
9 -- --
10 -- CHANGE CONTROL LOG --
11 -- --
12 -- DATE VERSION BY BUG DESCRIPTION --
13 -- ---------- ------- -------- ------- ---------------------------------- --
14 -- 2003/07/17 J ABLUNDEL Created. --
15 -- --
16 -- -------------------------------------------------------------------------- --
17
18 --
19 -- For debug
20 --
21 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_DIST_UTILM_PKG';
22
23
24 -- -------------------------------------------------------------------------- --
25 -- --
26 -- PRIVATE PROCEDURE DEFINITIONS --
27 -- ----------------------------- --
28 -- Described in Procedure code below --
29 -- -------------------------------------------------------------------------- --
30
31
32
33 -- -------------------------------------------------------------------------- --
34 -- --
35 -- NAME: DELETE_FILES_LINES --
36 -- --
37 -- TYPE: PROCEDURE --
38 -- --
39 -- PARAMETERS (IN OUT): p_template_id IN NUMBER --
40 -- --
41 -- PARAMETERS (OUT): x_return_message OUT NOCOPY VARCHAR2 --
42 -- x_return_status OUT NOCOPY VARCHAR2 --
43 -- --
44 -- RETURN: n/a --
45 -- --
46 -- DESCRIPTION: This procedure deletes all files and lines for --
47 -- a template --
48 -- --
49 -- --
50 -- CHANGE CONTROL LOG --
51 -- ------------------ --
52 -- --
53 -- DATE VERSION BY BUG DESCRIPTION --
54 -- ---------- ------- -------- ------- ---------------------------------- --
55 -- 2003/07/17 J ABLUNDEL Created --
56 -- --
57 -- -------------------------------------------------------------------------- --
58 PROCEDURE DELETE_FILES_LINES(p_template_id IN NUMBER,
59 x_return_message OUT NOCOPY VARCHAR2,
60 x_return_status OUT NOCOPY VARCHAR2) IS
61
62
63 l_error_text VARCHAR2(2000);
64
65
66
67 cursor c_get_file_ids(cp_template_id NUMBER) IS
68 select download_file_id
69 from fte_mile_download_files
70 where template_id = cp_template_id;
71
72 l_file_ids FTE_DIST_UTIL_PKG.fte_id_tmp_num_table;
73
74
75 --
76 -- Local Debug Variable Definitions
77 --
78 l_debug_on BOOLEAN;
79 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_FILES_LINES';
80
81
82
83 BEGIN
84
85
86
87 l_file_ids.DELETE;
88
89 IF ((p_template_id = 0) OR
90 (p_template_id is null)) THEN
91 x_return_message := null;
92 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
93 RETURN;
94 END IF;
95
96 --
97 -- set the debug flag
98 --
99 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
100
101 --
102 IF l_debug_on IS NULL THEN
103 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
104 END IF;
105
106 --
107 --
108 -- Debug Statements
109 --
110 IF l_debug_on THEN
111 WSH_DEBUG_SV.push(l_module_name);
112 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
113 WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
114 WSH_DEBUG_SV.log(l_module_name,'p_template_id',p_template_id);
115 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
116 END IF;
117
118 --
119 -- Set the return flags for the start of the procedure
120 --
121 x_return_message := null;
122 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
123
124
125 IF (p_template_id is not null) THEN
126 --
127 -- get the download file ids
128 --
129 OPEN c_get_file_ids(p_template_id);
130 FETCH c_get_file_ids BULK COLLECT INTO
131 l_file_ids;
132 CLOSE c_get_file_ids;
133
134 IF (l_file_ids.COUNT > 0) THEN
135
136 --
137 -- Delete the lines
138 --
139 FORALL j in l_file_ids.FIRST..l_file_ids.LAST
140 DELETE fte_mile_download_lines
141 WHERE download_file_id = l_file_ids(j);
142
143 --
144 -- Delete the files
145 --
146 FORALL j in l_file_ids.FIRST..l_file_ids.LAST
147 DELETE fte_mile_download_files
148 WHERE download_file_id = l_file_ids(j);
149
150 commit;
151 END IF;
152 END IF;
153
154 --
155 -- Debug Statements
156 --
157 IF l_debug_on THEN
158 WSH_DEBUG_SV.pop(l_module_name);
159 END IF;
160 --
161
162 --
163 -- Everything was OK
164 --
165 x_return_message := null;
166 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
167
168 --
169 -- Lets go home
170 --
171 RETURN;
172
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 l_error_text := SQLERRM;
177
178 --
179 -- Close any open cursors
180 --
181 IF (c_get_file_ids%ISOPEN) THEN
182 CLOSE c_get_file_ids;
183 END IF;
184
185 --
186 -- Debug Statements
187 --
188 IF l_debug_on THEN
189 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_UTIL_PKG.DELETE_FILES_LINES IS ' ||L_ERROR_TEXT );
190 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
191 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
192 END IF;
193 --
194 WSH_UTIL_CORE.default_handler('FTE_DIST_UTIL_PKG.DELETE_FILES_LINES');
195 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
196 x_return_message := l_error_text;
197 --
198 -- Debug Statements
199 --
200 IF l_debug_on THEN
201 WSH_DEBUG_SV.pop(l_module_name);
202 END IF;
203 --
204 RETURN;
205
206
207
208 END DELETE_FILES_LINES;
209
210 FUNCTION GET_REGION_TYPE RETURN NUMBER IS
211
212 l_distance_lvl VARCHAR2(50);
213
214 BEGIN
215
216 fnd_profile.get('FTE_DISTANCE_LVL', l_distance_lvl);
217
218 IF (l_distance_lvl is null) THEN
219 RETURN(NULL);
220 ELSIF (l_distance_lvl = 'CITYSTATE') THEN
221 RETURN(2);
222 ELSIF (l_distance_lvl = 'ZIP') THEN
223 RETURN(3);
224 ELSIF (l_distance_lvl = 'COUNTY') THEN
225 RETURN(4);
226 ELSE
227 RETURN(NULL);
228 END IF;
229
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 return (null);
234
235 END GET_REGION_TYPE;
236
237
238
239 PROCEDURE GET_DIST_PROFILE (x_profile_value OUT NOCOPY VARCHAR2) IS
240
241 BEGIN
242
243 fnd_profile.get('FTE_DISTANCE_LVL',x_profile_value);
244 RETURN;
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 x_profile_value := null;
249 RETURN;
250
251 END GET_DIST_PROFILE;
252
253 END FTE_DIST_UTIL_PKG;