[Home] [Help]
PACKAGE BODY: APPS.WSH_FREIGHT_COSTS_PUB
Source
1 PACKAGE BODY WSH_FREIGHT_COSTS_PUB as
2 /* $Header: WSHFCPBB.pls 115.6 2002/11/18 20:18:40 nparikh ship $ */
3 -- standard global constants
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_FREIGHT_COSTS_PUB';
5
6
7 --===================
8 -- PROCEDURES
9 --===================
10
11 PROCEDURE Validate_freight_cost_type(
12 p_freight_cost_type IN VARCHAR2
13 , x_freight_cost_type_id IN OUT NOCOPY NUMBER
14 , x_return_status OUT NOCOPY VARCHAR2
15 )
16 IS
17 invalid_type EXCEPTION;
18 l_type_id NUMBER;
19 BEGIN
20 --
21 --
22 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
23
24 IF (x_freight_cost_type_id IS NULL) THEN
25 IF (p_freight_cost_type <> FND_API.G_MISS_CHAR) THEN
26 SELECT freight_cost_type_id INTO x_freight_cost_type_id
27 FROM wsh_freight_cost_types
28 WHERE name = p_freight_cost_type;
29 IF (SQL%NOTFOUND) THEN
30 RAISE invalid_type;
31 END IF;
32 END IF;
33 ELSE
34 SELECT freight_cost_type_id INTO l_type_id
35 FROM wsh_freight_cost_types
36 WHERE freight_cost_type_id = x_freight_cost_type_id;
37 IF (SQL%NOTFOUND) THEN
38 RAISE invalid_type;
39 END IF;
40 END IF;
41
42
43 EXCEPTION
44 WHEN No_Data_Found THEN
45 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
46 WHEN Invalid_Type THEN
47 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
48 WHEN others THEN
49 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
50
51 END validate_freight_cost_type;
52
53 PROCEDURE Delete_Freight_Costs (
54 p_api_version_number IN NUMBER
55 , p_init_msg_list IN VARCHAR2
56 , p_commit IN VARCHAR2
57 , x_return_status OUT NOCOPY VARCHAR2
58 , x_msg_count OUT NOCOPY NUMBER
59 , x_msg_data OUT NOCOPY VARCHAR2
60 , p_pub_freight_costs IN WSH_FREIGHT_COSTS_PUB.PubFreightCostRecType
61 )
62 IS
63 l_return_status VARCHAR2(30);
64 BEGIN
65 --
66 --
67 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
68
69 WSH_FREIGHT_COSTS_PVT.Delete_freight_cost(
70 p_rowid => NULL,
71 p_freight_cost_id => p_pub_freight_costs.freight_cost_id,
72 x_return_status => x_return_status);
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
77 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
78 FND_MSG_PUB.Add_Exc_Msg (
79 G_PKG_NAME,
80 '_x_'
81 );
82 END IF;
83 -- Get message count and data
84 FND_MSG_PUB.Count_And_Get (
85 p_count => x_msg_count,
86 p_data => x_msg_data
87 );
88 END Delete_Freight_Costs;
89
90
91 --Harmonizing Project **heali
92 PROCEDURE map_freightpub_to_pvt(
93 p_pub_freight_rec IN PubFreightCostRecType,
94 x_pvt_freight_rec OUT NOCOPY WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type,
95 x_return_status OUT NOCOPY VARCHAR2) IS
96
97 --
98 l_debug_on BOOLEAN;
99 --
100 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME|| '.' || 'MAP_FREIGHTPUB_TO_PVT';
101 --
102 BEGIN
103 --
104 --
105 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
106 --
107 IF l_debug_on IS NULL
108 THEN
109 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
110 END IF;
111 --
112 IF l_debug_on THEN
113 WSH_DEBUG_SV.push(l_module_name);
114 WSH_DEBUG_SV.log(l_module_name,'p_pub_freight_rec.FREIGHT_COST_ID',p_pub_freight_rec.FREIGHT_COST_ID);
115 WSH_DEBUG_SV.log(l_module_name,'p_pub_freight_rec.FREIGHT_COST_TYPE_ID',p_pub_freight_rec.FREIGHT_COST_TYPE_ID);
116 END IF;
117 --
118 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
119
120 x_pvt_freight_rec.FREIGHT_COST_ID := p_pub_freight_rec.FREIGHT_COST_ID;
121 x_pvt_freight_rec.FREIGHT_COST_TYPE_ID := p_pub_freight_rec.FREIGHT_COST_TYPE_ID;
122 x_pvt_freight_rec.UNIT_AMOUNT := p_pub_freight_rec.UNIT_AMOUNT;
123 x_pvt_freight_rec.CALCULATION_METHOD := FND_API.G_MISS_CHAR;
124 x_pvt_freight_rec.UOM := FND_API.G_MISS_CHAR;
125 x_pvt_freight_rec.QUANTITY := FND_API.G_MISS_NUM;
126 x_pvt_freight_rec.TOTAL_AMOUNT := FND_API.G_MISS_NUM;
127 x_pvt_freight_rec.CURRENCY_CODE := p_pub_freight_rec.CURRENCY_CODE;
128 x_pvt_freight_rec.CONVERSION_DATE := p_pub_freight_rec.CONVERSION_DATE;
129 x_pvt_freight_rec.CONVERSION_RATE := p_pub_freight_rec.CONVERSION_RATE;
130 x_pvt_freight_rec.CONVERSION_TYPE_CODE := p_pub_freight_rec.CONVERSION_TYPE_CODE;
131 x_pvt_freight_rec.TRIP_ID := p_pub_freight_rec.TRIP_ID;
132 x_pvt_freight_rec.STOP_ID := p_pub_freight_rec.STOP_ID;
133 x_pvt_freight_rec.DELIVERY_ID := p_pub_freight_rec.DELIVERY_ID;
134 x_pvt_freight_rec.DELIVERY_LEG_ID := p_pub_freight_rec.DELIVERY_LEG_ID;
135 x_pvt_freight_rec.DELIVERY_DETAIL_ID := p_pub_freight_rec.DELIVERY_DETAIL_ID;
136 x_pvt_freight_rec.ATTRIBUTE_CATEGORY := p_pub_freight_rec.ATTRIBUTE_CATEGORY;
137 x_pvt_freight_rec.ATTRIBUTE1 := p_pub_freight_rec.ATTRIBUTE1;
138 x_pvt_freight_rec.ATTRIBUTE2 := p_pub_freight_rec.ATTRIBUTE2;
139 x_pvt_freight_rec.ATTRIBUTE3 := p_pub_freight_rec.ATTRIBUTE3;
140 x_pvt_freight_rec.ATTRIBUTE4 := p_pub_freight_rec.ATTRIBUTE4;
141 x_pvt_freight_rec.ATTRIBUTE5 := p_pub_freight_rec.ATTRIBUTE5;
142 x_pvt_freight_rec.ATTRIBUTE6 := p_pub_freight_rec.ATTRIBUTE6;
143 x_pvt_freight_rec.ATTRIBUTE7 := p_pub_freight_rec.ATTRIBUTE7;
144 x_pvt_freight_rec.ATTRIBUTE8 := p_pub_freight_rec.ATTRIBUTE8;
145 x_pvt_freight_rec.ATTRIBUTE9 := p_pub_freight_rec.ATTRIBUTE9;
146 x_pvt_freight_rec.ATTRIBUTE10 := p_pub_freight_rec.ATTRIBUTE10;
147 x_pvt_freight_rec.ATTRIBUTE11 := p_pub_freight_rec.ATTRIBUTE11;
148 x_pvt_freight_rec.ATTRIBUTE12 := p_pub_freight_rec.ATTRIBUTE12;
149 x_pvt_freight_rec.ATTRIBUTE13 := p_pub_freight_rec.ATTRIBUTE13;
150 x_pvt_freight_rec.ATTRIBUTE14 := p_pub_freight_rec.ATTRIBUTE14;
151 x_pvt_freight_rec.ATTRIBUTE15 := p_pub_freight_rec.ATTRIBUTE15;
152 x_pvt_freight_rec.CREATION_DATE := p_pub_freight_rec.CREATION_DATE;
153 x_pvt_freight_rec.CREATED_BY := p_pub_freight_rec.CREATED_BY;
154 x_pvt_freight_rec.LAST_UPDATE_DATE := p_pub_freight_rec.LAST_UPDATE_DATE;
155 x_pvt_freight_rec.LAST_UPDATED_BY := p_pub_freight_rec.LAST_UPDATED_BY;
156 x_pvt_freight_rec.LAST_UPDATE_LOGIN := p_pub_freight_rec.LAST_UPDATE_LOGIN;
157 x_pvt_freight_rec.PROGRAM_APPLICATION_ID := p_pub_freight_rec.PROGRAM_APPLICATION_ID;
158 x_pvt_freight_rec.PROGRAM_ID := p_pub_freight_rec.PROGRAM_ID;
159 x_pvt_freight_rec.PROGRAM_UPDATE_DATE := p_pub_freight_rec.PROGRAM_UPDATE_DATE;
160 x_pvt_freight_rec.REQUEST_ID := p_pub_freight_rec.REQUEST_ID;
161
162 x_pvt_freight_rec.PRICING_LIST_HEADER_ID := FND_API.G_MISS_NUM;
163 x_pvt_freight_rec.PRICING_LIST_LINE_ID := FND_API.G_MISS_NUM;
164 x_pvt_freight_rec.APPLIED_TO_CHARGE_ID := FND_API.G_MISS_NUM;
165 x_pvt_freight_rec.CHARGE_UNIT_VALUE := FND_API.G_MISS_NUM;
166 x_pvt_freight_rec.CHARGE_SOURCE_CODE := FND_API.G_MISS_CHAR;
167 x_pvt_freight_rec.LINE_TYPE_CODE := FND_API.G_MISS_CHAR;
168 x_pvt_freight_rec.ESTIMATED_FLAG := FND_API.G_MISS_CHAR;
169 x_pvt_freight_rec.FREIGHT_CODE := FND_API.G_MISS_CHAR;
170 x_pvt_freight_rec.TRIP_NAME := p_pub_freight_rec.TRIP_NAME;
171 x_pvt_freight_rec.DELIVERY_NAME := p_pub_freight_rec.DELIVERY_NAME;
172 x_pvt_freight_rec.FREIGHT_COST_TYPE := p_pub_freight_rec.FREIGHT_COST_TYPE;
173 x_pvt_freight_rec.STOP_LOCATION_ID := p_pub_freight_rec.STOP_LOCATION_ID;
174 x_pvt_freight_rec.PLANNED_DEP_DATE := p_pub_freight_rec.PLANNED_DEP_DATE;
175
176 IF l_debug_on THEN
177 WSH_DEBUG_SV.pop(l_module_name);
178 END IF;
179 --
180 EXCEPTION
181 WHEN OTHERS THEN
182 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_FTE_INTEGRATION.map_freightpub_to_pvt',l_module_name);
183 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
184 --
185 IF l_debug_on THEN
186 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
187 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
188 END IF;
189 --
190 END;
191
192 --========================================================================
193 -- PROCEDURE : Create_Update_Freight_Costs
194 --
195 -- PARAMETERS: p_api_version_number known api versionerror buffer
196 -- p_init_msg_list initialize message stack
197 -- x_return_status return status
198 -- x_msg_count number of messages in the list
199 -- x_msg_data text of messages
200 -- p_changed_attributes changed attributes for delivery details
201 -- p_action_code action to perform
202 --
203 --
204 -- COMMENT : Validates Organization_id and Organization_code against view
205 -- org_organization_definitions. If both values are
206 -- specified then only Org_Id is used
207 --========================================================================
208 PROCEDURE Create_Update_Freight_Costs (
209 p_api_version_number IN NUMBER
210 , p_init_msg_list IN VARCHAR2
211 , p_commit IN VARCHAR2
212 , x_return_status OUT NOCOPY VARCHAR2
213 , x_msg_count OUT NOCOPY NUMBER
214 , x_msg_data OUT NOCOPY VARCHAR2
215 , p_pub_freight_costs IN WSH_FREIGHT_COSTS_PUB.PubFreightCostRecType
216 , p_action_code IN VARCHAR2
217 , x_freight_cost_id OUT NOCOPY NUMBER) IS
218
219 l_debug_on BOOLEAN;
220 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_Update_Freight_Costs';
221
222 l_api_version_number CONSTANT NUMBER := 1.0;
223 l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Freight_Costs';
224
225 l_pvt_freight_rec WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type;
226 l_in_rec WSH_FREIGHT_COSTS_GRP.FreightInRecType;
227 l_freight_info_tab WSH_FREIGHT_COSTS_GRP.freight_rec_tab_type;
228 l_out_tab WSH_FREIGHT_COSTS_GRP.freight_out_tab_type;
229
230 BEGIN
231 --
232 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
233 --
234 IF l_debug_on IS NULL
235 THEN
236 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
237 END IF;
238 --
239 IF l_debug_on THEN
240 wsh_debug_sv.push (l_module_name, 'Create_Update_Freight_Costs');
241 END IF;
242
243 IF NOT FND_API.Compatible_API_Call (l_api_version_number,p_api_version_number ,l_api_name ,G_PKG_NAME) THEN
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 END IF;
246
247 IF FND_API.to_Boolean(p_init_msg_list) THEN
248 FND_MSG_PUB.initialize;
249 END IF;
250
251 map_freightpub_to_pvt(
252 p_pub_freight_rec => p_pub_freight_costs ,
253 x_pvt_freight_rec => l_pvt_freight_rec,
254 x_return_status => x_return_status);
255 IF l_debug_on THEN
256 wsh_debug_sv.log(l_module_name, 'map_freightpub_to_pvt x_return_status',x_return_status);
257 END IF;
258
259 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
260 raise FND_API.G_EXC_ERROR;
261 END IF;
262
263 l_in_rec.caller:='PLSQL';
264 l_in_rec.phase:= 1;
265
266 IF (p_pub_freight_costs.action_code IS NOT NULL and p_pub_freight_costs.action_code <> FND_API.G_MISS_CHAR) THEN
267 l_in_rec.action_code:= p_pub_freight_costs.action_code;
268 ELSE
269 l_in_rec.action_code:= p_action_code;
270 END IF;
271
272 l_freight_info_tab(1) := l_pvt_freight_rec;
273
274 WSH_INTERFACE_GRP.Create_Update_Freight_Costs(
275 p_api_version_number => p_api_version_number,
276 p_init_msg_list => p_init_msg_list,
277 p_commit => p_commit,
278 x_return_status => x_return_status,
279 x_msg_count => x_msg_count,
280 x_msg_data => x_msg_data,
281 p_freight_info_tab => l_freight_info_tab,
282 p_in_rec => l_in_rec,
283 x_out_tab => l_out_tab );
284
285 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND l_out_tab.COUNT > 0 ) THEN
286 x_freight_cost_id := l_out_tab(l_out_tab.FIRST).freight_cost_id;
287 END IF;
288
289 FND_MSG_PUB.Count_And_Get (
290 p_count => x_msg_count,
291 p_data => x_msg_data);
292
293 IF l_debug_on THEN
294 WSH_DEBUG_SV.pop(l_module_name);
295 END IF;
296
297 EXCEPTION
298 WHEN FND_API.G_EXC_ERROR THEN
299 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
300 FND_MSG_PUB.Count_And_Get (
301 p_count => x_msg_count,
302 p_data => x_msg_data,
303 p_encoded => FND_API.G_FALSE);
304 IF l_debug_on THEN
305 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',
306 WSH_DEBUG_SV.C_EXCEP_LEVEL);
307 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
308 END IF;
309
310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
312 FND_MSG_PUB.Count_And_Get (
313 p_count => x_msg_count,
314 p_data => x_msg_data,
315 p_encoded => FND_API.G_FALSE);
316 IF l_debug_on THEN
317 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',
318 WSH_DEBUG_SV.C_EXCEP_LEVEL);
319 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
320 END IF;
321
322 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
323 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
324 FND_MSG_PUB.Count_And_Get (
325 p_count => x_msg_count,
326 p_data => x_msg_data,
327 p_encoded => FND_API.G_FALSE);
328 IF l_debug_on THEN
329 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',
330 WSH_DEBUG_SV.C_EXCEP_LEVEL);
331 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
332 END IF;
333
334 WHEN OTHERS THEN
335 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
336 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
337 FND_MSG_PUB.Count_And_Get (
338 p_count => x_msg_count,
339 p_data => x_msg_data,
340 p_encoded => FND_API.G_FALSE);
341 IF l_debug_on THEN
342 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
343 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
344 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
345 END IF;
346 END Create_Update_Freight_Costs;
347
348 --Harmonizing Project **heali
349
350
351 END WSH_FREIGHT_COSTS_PUB;