[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERIES_PUB
Source
1 PACKAGE BODY WSH_DELIVERIES_PUB as
2 /* $Header: WSHDEPBB.pls 120.5 2010/08/10 09:41:54 anvarshn ship $ */
3
4 --===================
5 -- CONSTANTS
6 --===================
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_DELIVERIES_PUB';
8 -- add your constants here if any
9
10 --===================
11 -- PROCEDURES
12 --===================
13
14 PROCEDURE Rtrim_delivery (
15 p_in_rec IN WSH_DELIVERIES_PUB.Delivery_Pub_Rec_Type,
16 p_out_rec OUT NOCOPY WSH_DELIVERIES_PUB.Delivery_Pub_Rec_Type)
17 IS
18 l_debug_on BOOLEAN;
19 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||
20 G_PKG_NAME || '.' || 'Rtrim_delivery';
21 BEGIN
22
23 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
24 --
25 IF l_debug_on IS NULL
26 THEN
27 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
28 END IF;
29 --
30 IF l_debug_on THEN
31 wsh_debug_sv.push (l_module_name, 'Rtrim_delivery');
32 END IF;
33
34 p_out_rec := p_in_rec;
35
36
37 p_out_rec.NAME :=RTRIM(p_in_rec.NAME);
38 p_out_rec.DELIVERY_TYPE :=RTRIM(p_in_rec.DELIVERY_TYPE);
39 p_out_rec.LOADING_ORDER_FLAG :=RTRIM(p_in_rec.LOADING_ORDER_FLAG);
40 p_out_rec.LOADING_ORDER_DESC :=RTRIM(p_in_rec.LOADING_ORDER_DESC);
41 p_out_rec.INITIAL_PICKUP_LOCATION_CODE :=RTRIM(p_in_rec.INITIAL_PICKUP_LOCATION_CODE);
42 p_out_rec.ORGANIZATION_CODE :=RTRIM(p_in_rec.ORGANIZATION_CODE);
43 p_out_rec.ULTIMATE_DROPOFF_LOCATION_CODE :=RTRIM(p_in_rec.ULTIMATE_DROPOFF_LOCATION_CODE);
44 p_out_rec.CUSTOMER_NUMBER :=RTRIM(p_in_rec.CUSTOMER_NUMBER);
45 p_out_rec.INTMED_SHIP_TO_LOCATION_CODE :=RTRIM(p_in_rec.INTMED_SHIP_TO_LOCATION_CODE);
46 p_out_rec.POOLED_SHIP_TO_LOCATION_CODE :=RTRIM(p_in_rec.POOLED_SHIP_TO_LOCATION_CODE);
47 p_out_rec.CARRIER_CODE :=RTRIM(p_in_rec.CARRIER_CODE);
48 p_out_rec.SHIP_METHOD_CODE :=RTRIM(p_in_rec.SHIP_METHOD_CODE);
49 p_out_rec.SHIP_METHOD_NAME :=RTRIM(p_in_rec.SHIP_METHOD_NAME);
50 p_out_rec.FREIGHT_TERMS_CODE :=RTRIM(p_in_rec.FREIGHT_TERMS_CODE);
51 p_out_rec.FREIGHT_TERMS_NAME :=RTRIM(p_in_rec.FREIGHT_TERMS_NAME);
52 p_out_rec.FOB_CODE :=RTRIM(p_in_rec.FOB_CODE);
53 p_out_rec.FOB_NAME :=RTRIM(p_in_rec.FOB_NAME);
54 p_out_rec.FOB_LOCATION_CODE :=RTRIM(p_in_rec.FOB_LOCATION_CODE);
55 p_out_rec.WAYBILL :=RTRIM(p_in_rec.WAYBILL);
56 p_out_rec.DOCK_CODE :=RTRIM(p_in_rec.DOCK_CODE);
57 p_out_rec.ACCEPTANCE_FLAG :=RTRIM(p_in_rec.ACCEPTANCE_FLAG);
58 p_out_rec.ACCEPTED_BY :=RTRIM(p_in_rec.ACCEPTED_BY);
59 p_out_rec.ACKNOWLEDGED_BY :=RTRIM(p_in_rec.ACKNOWLEDGED_BY);
60 p_out_rec.CONFIRMED_BY :=RTRIM(p_in_rec.CONFIRMED_BY);
61 p_out_rec.ASN_STATUS_CODE :=RTRIM(p_in_rec.ASN_STATUS_CODE);
62 p_out_rec.WEIGHT_UOM_CODE :=RTRIM(p_in_rec.WEIGHT_UOM_CODE);
63 p_out_rec.WEIGHT_UOM_DESC :=RTRIM(p_in_rec.WEIGHT_UOM_DESC);
64 p_out_rec.VOLUME_UOM_CODE :=RTRIM(p_in_rec.VOLUME_UOM_CODE);
65 p_out_rec.VOLUME_UOM_DESC :=RTRIM(p_in_rec.VOLUME_UOM_DESC);
66 p_out_rec.ADDITIONAL_SHIPMENT_INFO :=RTRIM(p_in_rec.ADDITIONAL_SHIPMENT_INFO);
67 p_out_rec.CURRENCY_CODE :=RTRIM(p_in_rec.CURRENCY_CODE);
68 p_out_rec.CURRENCY_NAME :=RTRIM(p_in_rec.CURRENCY_NAME);
69 p_out_rec.ATTRIBUTE_CATEGORY :=RTRIM(p_in_rec.ATTRIBUTE_CATEGORY);
70 p_out_rec.ATTRIBUTE1 :=RTRIM(p_in_rec.ATTRIBUTE1);
71 p_out_rec.ATTRIBUTE2 :=RTRIM(p_in_rec.ATTRIBUTE2);
72 p_out_rec.ATTRIBUTE3 :=RTRIM(p_in_rec.ATTRIBUTE3);
73 p_out_rec.ATTRIBUTE4 :=RTRIM(p_in_rec.ATTRIBUTE4);
74 p_out_rec.ATTRIBUTE5 :=RTRIM(p_in_rec.ATTRIBUTE5);
75 p_out_rec.ATTRIBUTE6 :=RTRIM(p_in_rec.ATTRIBUTE6);
76 p_out_rec.ATTRIBUTE7 :=RTRIM(p_in_rec.ATTRIBUTE7);
77 p_out_rec.ATTRIBUTE8 :=RTRIM(p_in_rec.ATTRIBUTE8);
78 p_out_rec.ATTRIBUTE9 :=RTRIM(p_in_rec.ATTRIBUTE9);
79 p_out_rec.ATTRIBUTE10 :=RTRIM(p_in_rec.ATTRIBUTE10);
80 p_out_rec.ATTRIBUTE11 :=RTRIM(p_in_rec.ATTRIBUTE11);
81 p_out_rec.ATTRIBUTE12 :=RTRIM(p_in_rec.ATTRIBUTE12);
82 p_out_rec.ATTRIBUTE13 :=RTRIM(p_in_rec.ATTRIBUTE13);
83 p_out_rec.ATTRIBUTE14 :=RTRIM(p_in_rec.ATTRIBUTE14);
84 p_out_rec.ATTRIBUTE15 :=RTRIM(p_in_rec.ATTRIBUTE15);
85 p_out_rec.TP_ATTRIBUTE_CATEGORY :=RTRIM(p_in_rec.TP_ATTRIBUTE_CATEGORY);
86 p_out_rec.TP_ATTRIBUTE1 :=RTRIM(p_in_rec.TP_ATTRIBUTE1);
87 p_out_rec.TP_ATTRIBUTE2 :=RTRIM(p_in_rec.TP_ATTRIBUTE2);
88 p_out_rec.TP_ATTRIBUTE3 :=RTRIM(p_in_rec.TP_ATTRIBUTE3);
89 p_out_rec.TP_ATTRIBUTE4 :=RTRIM(p_in_rec.TP_ATTRIBUTE4);
90 p_out_rec.TP_ATTRIBUTE5 :=RTRIM(p_in_rec.TP_ATTRIBUTE5);
91 p_out_rec.TP_ATTRIBUTE6 :=RTRIM(p_in_rec.TP_ATTRIBUTE6);
92 p_out_rec.TP_ATTRIBUTE7 :=RTRIM(p_in_rec.TP_ATTRIBUTE7);
93 p_out_rec.TP_ATTRIBUTE8 :=RTRIM(p_in_rec.TP_ATTRIBUTE8);
94 p_out_rec.TP_ATTRIBUTE9 :=RTRIM(p_in_rec.TP_ATTRIBUTE9);
95 p_out_rec.TP_ATTRIBUTE10 :=RTRIM(p_in_rec.TP_ATTRIBUTE10);
96 p_out_rec.TP_ATTRIBUTE11 :=RTRIM(p_in_rec.TP_ATTRIBUTE11);
97 p_out_rec.TP_ATTRIBUTE12 :=RTRIM(p_in_rec.TP_ATTRIBUTE12);
98 p_out_rec.TP_ATTRIBUTE13 :=RTRIM(p_in_rec.TP_ATTRIBUTE13);
99 p_out_rec.TP_ATTRIBUTE14 :=RTRIM(p_in_rec.TP_ATTRIBUTE14);
100 p_out_rec.TP_ATTRIBUTE15 :=RTRIM(p_in_rec.TP_ATTRIBUTE15);
101 p_out_rec.GLOBAL_ATTRIBUTE_CATEGORY :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE_CATEGORY);
102 p_out_rec.GLOBAL_ATTRIBUTE1 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE1);
103 p_out_rec.GLOBAL_ATTRIBUTE2 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE2);
104 p_out_rec.GLOBAL_ATTRIBUTE3 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE3);
105 p_out_rec.GLOBAL_ATTRIBUTE4 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE4);
106 p_out_rec.GLOBAL_ATTRIBUTE5 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE5);
107 p_out_rec.GLOBAL_ATTRIBUTE6 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE6);
108 p_out_rec.GLOBAL_ATTRIBUTE7 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE7);
109 p_out_rec.GLOBAL_ATTRIBUTE8 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE8);
110 p_out_rec.GLOBAL_ATTRIBUTE9 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE9);
111 p_out_rec.GLOBAL_ATTRIBUTE10 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE10);
112 p_out_rec.GLOBAL_ATTRIBUTE11 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE11);
113 p_out_rec.GLOBAL_ATTRIBUTE12 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE12);
114 p_out_rec.GLOBAL_ATTRIBUTE13 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE13);
115 p_out_rec.GLOBAL_ATTRIBUTE14 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE14);
116 p_out_rec.GLOBAL_ATTRIBUTE15 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE15);
117 p_out_rec.GLOBAL_ATTRIBUTE16 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE16);
118 p_out_rec.GLOBAL_ATTRIBUTE17 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE17);
119 p_out_rec.GLOBAL_ATTRIBUTE18 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE18);
120 p_out_rec.GLOBAL_ATTRIBUTE19 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE19);
121 p_out_rec.GLOBAL_ATTRIBUTE20 :=RTRIM(p_in_rec.GLOBAL_ATTRIBUTE20);
122 p_out_rec.COD_CURRENCY_CODE :=RTRIM(p_in_rec.COD_CURRENCY_CODE);
123 p_out_rec.COD_REMIT_TO :=RTRIM(p_in_rec.COD_REMIT_TO);
124 p_out_rec.COD_CHARGE_PAID_BY :=RTRIM(p_in_rec.COD_CHARGE_PAID_BY);
125 p_out_rec.PROBLEM_CONTACT_REFERENCE :=RTRIM(p_in_rec.PROBLEM_CONTACT_REFERENCE);
126 p_out_rec.PORT_OF_LOADING :=RTRIM(p_in_rec.PORT_OF_LOADING);
127 p_out_rec.PORT_OF_DISCHARGE :=RTRIM(p_in_rec.PORT_OF_DISCHARGE);
128 p_out_rec.FTZ_NUMBER :=RTRIM(p_in_rec.FTZ_NUMBER);
129 p_out_rec.ROUTED_EXPORT_TXN :=RTRIM(p_in_rec.ROUTED_EXPORT_TXN);
130 p_out_rec.ENTRY_NUMBER :=RTRIM(p_in_rec.ENTRY_NUMBER);
131 p_out_rec.ROUTING_INSTRUCTIONS :=RTRIM(p_in_rec.ROUTING_INSTRUCTIONS);
132 p_out_rec.IN_BOND_CODE :=RTRIM(p_in_rec.IN_BOND_CODE);
133 p_out_rec.SHIPPING_MARKS :=RTRIM(p_in_rec.SHIPPING_MARKS);
134 p_out_rec.SERVICE_LEVEL :=RTRIM(p_in_rec.SERVICE_LEVEL);
135 p_out_rec.MODE_OF_TRANSPORT :=RTRIM(p_in_rec.MODE_OF_TRANSPORT);
136 p_out_rec.ASSIGNED_TO_FTE_TRIPS :=RTRIM(p_in_rec.ASSIGNED_TO_FTE_TRIPS);
137 p_out_rec.AUTO_SC_EXCLUDE_FLAG :=RTRIM(p_in_rec.AUTO_SC_EXCLUDE_FLAG);
138 p_out_rec.AUTO_AP_EXCLUDE_FLAG :=RTRIM(p_in_rec.AUTO_AP_EXCLUDE_FLAG);
139 p_out_rec.client_code :=RTRIM(p_in_rec.client_code ); -- LSP PROJECT
140
141
142 IF l_debug_on THEN
143 WSH_DEBUG_SV.pop(l_module_name);
144 END IF;
145
146 EXCEPTION
147
148 WHEN OTHERS THEN
149 wsh_util_core.default_handler (
150 'WSH_TRIP_STOPS_GRP.Rtrim_delivery', l_module_name);
151 IF l_debug_on THEN
152 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured.'||
153 ' Oracle error message is '|| SQLERRM,
154 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
155 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
156 END IF;
157 RAISE;
158
159 END Rtrim_delivery;
160
161
162 --========================================================================
163 -- PROCEDURE : Validate_Delivery PRIVATE
164 --
165 -- PARAMETERS: p_delivery_info Attributes for the delivery entity
166 -- p_action_code 'CREATE', 'UPDATE'
167 -- x_return_status Return status of API
168 -- COMMENT : Validates p_delivery_info by calling column specific validations
169 --========================================================================
170 PROCEDURE Validate_Delivery
171 (p_delivery_info IN OUT NOCOPY delivery_pub_rec_type,
172 p_action_code IN VARCHAR2,
173 x_return_status OUT NOCOPY VARCHAR2) IS
174
175 l_assigned_to_trip VARCHAR2(1);
176
177 -- OTM R12, glog proj
178 l_adjusted_amount NUMBER;
179 l_debug_on BOOLEAN;
180 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||G_PKG_NAME || '.' || 'Validate_Delivery';
181
182 BEGIN
183 --
184 --
185 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
186 --
187 IF l_debug_on IS NULL
188 THEN
189 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
190 END IF;
191 --
192 IF l_debug_on THEN
193 wsh_debug_sv.push (l_module_name, 'Validate_Delivery');
194 END IF;
195 --
196 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
197
198 IF (p_action_code <> 'CREATE') THEN
199
200 IF (p_delivery_info.delivery_id <> FND_API.G_MISS_NUM) OR (p_delivery_info.name <> FND_API.G_MISS_CHAR) THEN
201
202 IF (p_delivery_info.name <> FND_API.G_MISS_CHAR) THEN
203 p_delivery_info.delivery_id := NULL;
204 END IF;
205
206 wsh_util_validate.validate_delivery_name(
207 p_delivery_info.delivery_id,
208 p_delivery_info.name,
209 x_return_status);
210
211 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
212 RETURN;
213 END IF;
214
215 END IF;
216
217 END IF;
218
222 p_delivery_info.organization_id := NULL;
219 IF (p_delivery_info.organization_id <> FND_API.G_MISS_NUM) OR (p_delivery_info.organization_code <> FND_API.G_MISS_CHAR) THEN
220
221 IF (p_delivery_info.organization_code <> FND_API.G_MISS_CHAR) THEN
223 END IF;
224
225 wsh_util_validate.validate_org(
226 p_delivery_info.organization_id,
227 p_delivery_info.organization_code,
228 x_return_status);
229
230 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
231 RETURN;
232 END IF;
233 END IF;
234
235 IF (nvl(p_delivery_info.delivery_type,'STANDARD') NOT IN ('STANDARD','CONSOLIDATED')) THEN
236 p_delivery_info.delivery_type := 'STANDARD';
237
238 END IF;
239
240 IF (p_delivery_info.loading_order_flag <> FND_API.G_MISS_CHAR) OR (p_delivery_info.loading_order_desc <> FND_API.G_MISS_CHAR) THEN
241
242 IF (p_delivery_info.loading_order_desc <> FND_API.G_MISS_CHAR) THEN
243 p_delivery_info.loading_order_flag := NULL;
244 END IF;
245
246 wsh_util_validate.validate_loading_order(
247 p_delivery_info.loading_order_flag,
248 p_delivery_info.loading_order_desc,
249 x_return_status);
250
251 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
252 RETURN;
253 END IF;
254 END IF;
255
256 IF (p_delivery_info.initial_pickup_location_id <> FND_API.G_MISS_NUM) OR (p_delivery_info.initial_pickup_location_code <> FND_API.G_MISS_CHAR) THEN
257
258 IF (p_delivery_info.initial_pickup_location_code <> FND_API.G_MISS_CHAR) THEN
259 p_delivery_info.initial_pickup_location_id := NULL;
260 END IF;
261
262 wsh_util_validate.validate_location(
263 p_delivery_info.initial_pickup_location_id,
264 p_delivery_info.initial_pickup_location_code,
265 x_return_status);
266
267 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
268 RETURN;
269 END IF;
270 END IF;
271
272 IF (p_delivery_info.ultimate_dropoff_location_id <> FND_API.G_MISS_NUM) OR (p_delivery_info.ultimate_dropoff_location_code <> FND_API.G_MISS_CHAR) THEN
273
274 IF (p_delivery_info.ultimate_dropoff_location_code <> FND_API.G_MISS_CHAR) THEN
275 p_delivery_info.ultimate_dropoff_location_id := NULL;
276 END IF;
277
278 wsh_util_validate.validate_location(
279 p_delivery_info.ultimate_dropoff_location_id,
280 p_delivery_info.ultimate_dropoff_location_code,
281 x_return_status);
282
283 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
284 RETURN;
285 END IF;
286 END IF;
287
288 IF (p_delivery_info.intmed_ship_to_location_id <> FND_API.G_MISS_NUM) OR (p_delivery_info.intmed_ship_to_location_code <> FND_API.G_MISS_CHAR) THEN
289
290 IF (p_delivery_info.intmed_ship_to_location_code <> FND_API.G_MISS_CHAR) THEN
291 p_delivery_info.intmed_ship_to_location_id := NULL;
292 END IF;
293
294 wsh_util_validate.validate_location(
295 p_delivery_info.intmed_ship_to_location_id,
296 p_delivery_info.intmed_ship_to_location_code,
297 x_return_status);
298
299 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
300 RETURN;
301 END IF;
302 END IF;
303
304 IF (p_delivery_info.pooled_ship_to_location_id <> FND_API.G_MISS_NUM) OR (p_delivery_info.pooled_ship_to_location_code <> FND_API.G_MISS_CHAR) THEN
305
306 IF (p_delivery_info.pooled_ship_to_location_code <> FND_API.G_MISS_CHAR) THEN
307 p_delivery_info.pooled_ship_to_location_id := NULL;
308 END IF;
309
310 wsh_util_validate.validate_location(
311 p_delivery_info.pooled_ship_to_location_id,
312 p_delivery_info.pooled_ship_to_location_code,
313 x_return_status);
314
315 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
316 RETURN;
317 END IF;
318 END IF;
319
320 IF (p_delivery_info.customer_id <> FND_API.G_MISS_NUM) OR (p_delivery_info.customer_number <> FND_API.G_MISS_CHAR) THEN
321
322 IF (p_delivery_info.customer_number <> FND_API.G_MISS_CHAR) THEN
323 p_delivery_info.customer_id := NULL;
324 END IF;
325
326 wsh_util_validate.validate_customer(
327 p_delivery_info.customer_id,
328 p_delivery_info.customer_number,
329 x_return_status);
330
331 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
332 RETURN;
333 END IF;
334 END IF;
335
336 -- Carrier ID is not validated as it is not used...Ship method is used instead.
337
338 IF (p_delivery_info.ship_method_code <> FND_API.G_MISS_CHAR) OR (p_delivery_info.ship_method_name <> FND_API.G_MISS_CHAR) THEN
339
340 IF (p_delivery_info.ship_method_code <> FND_API.G_MISS_CHAR) THEN
341 p_delivery_info.ship_method_name := NULL;
342 END IF;
343
344 wsh_util_validate.validate_ship_method(
345 p_delivery_info.ship_method_code,
346 p_delivery_info.ship_method_name,
347 x_return_status);
348
349 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
350 RETURN;
351 END IF;
352 END IF;
353
354 IF (p_delivery_info.freight_terms_code <> FND_API.G_MISS_CHAR) OR (p_delivery_info.freight_terms_name <> FND_API.G_MISS_CHAR) THEN
355
359
356 IF (p_delivery_info.freight_terms_name <> FND_API.G_MISS_CHAR) THEN
357 p_delivery_info.freight_terms_name := NULL;
358 END IF;
360 wsh_util_validate.validate_freight_terms(
361 p_delivery_info.freight_terms_code,
362 p_delivery_info.freight_terms_name,
363 x_return_status);
364
365 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
366 RETURN;
367 END IF;
368 END IF;
369
370 IF (p_delivery_info.fob_code <> FND_API.G_MISS_CHAR) OR (p_delivery_info.fob_name <> FND_API.G_MISS_CHAR) THEN
371
372 IF (p_delivery_info.fob_name <> FND_API.G_MISS_CHAR) THEN
373 p_delivery_info.fob_code := NULL;
374 END IF;
375
376 wsh_util_validate.validate_fob(
377 p_delivery_info.fob_code,
378 p_delivery_info.fob_name,
379 x_return_status);
380
381 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
382 RETURN;
383 END IF;
384 END IF;
385
386 IF (p_delivery_info.fob_location_id <> FND_API.G_MISS_NUM) OR (p_delivery_info.fob_location_code <> FND_API.G_MISS_CHAR) THEN
387
388 IF (p_delivery_info.fob_location_code <> FND_API.G_MISS_CHAR) THEN
389 p_delivery_info.fob_location_id := NULL;
390 END IF;
391
392 wsh_util_validate.validate_location(
393 p_delivery_info.fob_location_id,
394 p_delivery_info.fob_location_code,
395 x_return_status);
396
397 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
398 RETURN;
399 END IF;
400 END IF;
401
402 IF (p_delivery_info.weight_uom_code <> FND_API.G_MISS_CHAR) OR (p_delivery_info.weight_uom_desc <> FND_API.G_MISS_CHAR) THEN
403
404 IF (p_delivery_info.weight_uom_desc <> FND_API.G_MISS_CHAR) THEN
405 p_delivery_info.weight_uom_code := NULL;
406 END IF;
407
408 wsh_util_validate.validate_uom(
409 'WEIGHT',
410 p_delivery_info.organization_id,
411 p_delivery_info.weight_uom_code,
412 p_delivery_info.weight_uom_desc,
413 x_return_status);
414
415 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
416 RETURN;
417 END IF;
418 END IF;
419
420 IF (p_delivery_info.volume_uom_code <> FND_API.G_MISS_CHAR) OR (p_delivery_info.volume_uom_desc <> FND_API.G_MISS_CHAR) THEN
421
422 IF (p_delivery_info.volume_uom_desc <> FND_API.G_MISS_CHAR) THEN
423 p_delivery_info.volume_uom_code := NULL;
424 END IF;
425
426 wsh_util_validate.validate_uom(
427 'VOLUME',
428 p_delivery_info.organization_id,
429 p_delivery_info.volume_uom_code,
430 p_delivery_info.volume_uom_desc,
431 x_return_status);
432
433 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
434 RETURN;
435 END IF;
436 END IF;
437
438 IF (p_delivery_info.currency_code <> FND_API.G_MISS_CHAR) OR (p_delivery_info.currency_name <> FND_API.G_MISS_CHAR) THEN
439
440 IF (p_delivery_info.currency_name <> FND_API.G_MISS_CHAR) THEN
441 p_delivery_info.currency_code := NULL;
442 END IF;
443
444 -- OTM R12, glog project changes, to use name value notation
445 WSH_UTIL_VALIDATE.validate_currency(
446 p_currency_code => p_delivery_info.currency_code,
447 p_currency_name => p_delivery_info.currency_name,
448 p_amount => NULL,
449 x_return_status => x_return_status,
450 x_adjusted_amount => l_adjusted_amount); -- OTM R12,glog project
451
452 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
453 RETURN;
454 END IF;
455 END IF;
456
457
458 --
459 -- manifesting code changes
460 -- disallow update of ship method or its components if the delivery is assigned to trip.
461 --
462
463 IF (p_delivery_info.ship_method_code <> FND_API.G_MISS_CHAR) OR (p_delivery_info.ship_method_code IS NULL) OR
464 (p_delivery_info.carrier_id <> FND_API.G_MISS_NUM) OR (p_delivery_info.carrier_id IS NULL) OR
465 (p_delivery_info.service_level <> FND_API.G_MISS_CHAR) OR (p_delivery_info.service_level IS NULL) OR
466 (p_delivery_info.mode_of_transport <> FND_API.G_MISS_CHAR) OR (p_delivery_info.mode_of_transport IS NULL) THEN
467
468 l_assigned_to_trip := WSH_Delivery_Validations.Del_Assigned_To_Trip
469 (p_delivery_id => p_delivery_info.delivery_id,
470 x_return_status => x_return_status);
471
472 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
473
474 RETURN;
475
476 ELSIF l_assigned_to_trip = 'Y' THEN
477 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_ASSIGNED_ERROR');
478 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(p_delivery_info.delivery_id));
479 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
480 wsh_util_core.add_message(x_return_status);
481 RETURN;
482 END IF;
483 END IF;
484
485
486
487 EXCEPTION
488 WHEN others THEN
489 wsh_util_core.default_handler('WSH_DELIVERIES_PUB.Validate_Delivery');
490 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
491
492 END Validate_Delivery;
493
497 x_pvt_rec OUT NOCOPY wsh_new_deliveries_pvt.delivery_rec_type,
494 /* I Harmonization -- rvishnuv */
495 PROCEDURE map_pub_to_pvt(
496 p_pub_rec IN delivery_pub_rec_type,
498 x_return_status OUT NOCOPY VARCHAR2)
499 IS
500 --
501 l_debug_on BOOLEAN;
502 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_PUB_TO_PVT';
503
504 BEGIN
505 --
506 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
507 --
508 IF l_debug_on IS NULL
509 THEN
510 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
511 END IF;
512 --
513 IF l_debug_on THEN
514 WSH_DEBUG_SV.push(l_module_name);
515 END IF;
516 x_return_status := wsh_util_core.g_ret_sts_success;
517 --
518 x_pvt_rec.DELIVERY_ID := p_pub_rec.DELIVERY_ID;
519 x_pvt_rec.NAME := p_pub_rec.NAME;
520 x_pvt_rec.DELIVERY_TYPE := p_pub_rec.DELIVERY_TYPE;
521 x_pvt_rec.LOADING_SEQUENCE := p_pub_rec.LOADING_SEQUENCE;
522 x_pvt_rec.LOADING_ORDER_FLAG := p_pub_rec.LOADING_ORDER_FLAG;
523 x_pvt_rec.LOADING_ORDER_DESC := p_pub_rec.LOADING_ORDER_DESC;
524 x_pvt_rec.INITIAL_PICKUP_DATE := p_pub_rec.INITIAL_PICKUP_DATE;
525 x_pvt_rec.INITIAL_PICKUP_LOCATION_ID := p_pub_rec.INITIAL_PICKUP_LOCATION_ID;
526 x_pvt_rec.INITIAL_PICKUP_LOCATION_CODE := p_pub_rec.INITIAL_PICKUP_LOCATION_CODE;
527 x_pvt_rec.ORGANIZATION_ID := p_pub_rec.ORGANIZATION_ID;
528 x_pvt_rec.ORGANIZATION_CODE := p_pub_rec.ORGANIZATION_CODE;
529 x_pvt_rec.ULTIMATE_DROPOFF_LOCATION_ID := p_pub_rec.ULTIMATE_DROPOFF_LOCATION_ID;
530 x_pvt_rec.ULTIMATE_DROPOFF_LOCATION_CODE := p_pub_rec.ULTIMATE_DROPOFF_LOCATION_CODE;
531 x_pvt_rec.ULTIMATE_DROPOFF_DATE := p_pub_rec.ULTIMATE_DROPOFF_DATE;
532 x_pvt_rec.CUSTOMER_ID := p_pub_rec.CUSTOMER_ID;
533 x_pvt_rec.CUSTOMER_NUMBER := p_pub_rec.CUSTOMER_NUMBER;
534 x_pvt_rec.INTMED_SHIP_TO_LOCATION_ID := p_pub_rec.INTMED_SHIP_TO_LOCATION_ID;
535 x_pvt_rec.INTMED_SHIP_TO_LOCATION_CODE := p_pub_rec.INTMED_SHIP_TO_LOCATION_CODE;
536 x_pvt_rec.POOLED_SHIP_TO_LOCATION_ID := p_pub_rec.POOLED_SHIP_TO_LOCATION_ID;
537 x_pvt_rec.POOLED_SHIP_TO_LOCATION_CODE := p_pub_rec.POOLED_SHIP_TO_LOCATION_CODE;
538 x_pvt_rec.CARRIER_ID := p_pub_rec.CARRIER_ID;
539 x_pvt_rec.CARRIER_CODE := p_pub_rec.CARRIER_CODE;
540 x_pvt_rec.SHIP_METHOD_CODE := p_pub_rec.SHIP_METHOD_CODE;
541 x_pvt_rec.SHIP_METHOD_NAME := p_pub_rec.SHIP_METHOD_NAME;
542 x_pvt_rec.FREIGHT_TERMS_CODE := p_pub_rec.FREIGHT_TERMS_CODE;
543 x_pvt_rec.FREIGHT_TERMS_NAME := p_pub_rec.FREIGHT_TERMS_NAME;
544 x_pvt_rec.FOB_CODE := p_pub_rec.FOB_CODE;
545 x_pvt_rec.FOB_NAME := p_pub_rec.FOB_NAME;
546 x_pvt_rec.FOB_LOCATION_ID := p_pub_rec.FOB_LOCATION_ID;
547 x_pvt_rec.FOB_LOCATION_CODE := p_pub_rec.FOB_LOCATION_CODE;
548 x_pvt_rec.WAYBILL := p_pub_rec.WAYBILL;
549 x_pvt_rec.DOCK_CODE := p_pub_rec.DOCK_CODE;
550 x_pvt_rec.ACCEPTANCE_FLAG := p_pub_rec.ACCEPTANCE_FLAG;
551 x_pvt_rec.ACCEPTED_BY := p_pub_rec.ACCEPTED_BY;
552 x_pvt_rec.ACCEPTED_DATE := p_pub_rec.ACCEPTED_DATE;
553 x_pvt_rec.ACKNOWLEDGED_BY := p_pub_rec.ACKNOWLEDGED_BY;
554 x_pvt_rec.CONFIRMED_BY := p_pub_rec.CONFIRMED_BY;
555 x_pvt_rec.CONFIRM_DATE := p_pub_rec.CONFIRM_DATE;
556 x_pvt_rec.ASN_DATE_SENT := p_pub_rec.ASN_DATE_SENT;
557 x_pvt_rec.ASN_STATUS_CODE := p_pub_rec.ASN_STATUS_CODE;
558 x_pvt_rec.ASN_SEQ_NUMBER := p_pub_rec.ASN_SEQ_NUMBER;
559 x_pvt_rec.GROSS_WEIGHT := p_pub_rec.GROSS_WEIGHT;
560 x_pvt_rec.NET_WEIGHT := p_pub_rec.NET_WEIGHT;
561 x_pvt_rec.WEIGHT_UOM_CODE := p_pub_rec.WEIGHT_UOM_CODE;
562 x_pvt_rec.WEIGHT_UOM_DESC := p_pub_rec.WEIGHT_UOM_DESC;
563 x_pvt_rec.VOLUME := p_pub_rec.VOLUME;
564 x_pvt_rec.VOLUME_UOM_CODE := p_pub_rec.VOLUME_UOM_CODE;
565 x_pvt_rec.VOLUME_UOM_DESC := p_pub_rec.VOLUME_UOM_DESC;
566 x_pvt_rec.ADDITIONAL_SHIPMENT_INFO := p_pub_rec.ADDITIONAL_SHIPMENT_INFO;
567 x_pvt_rec.CURRENCY_CODE := p_pub_rec.CURRENCY_CODE;
568 x_pvt_rec.CURRENCY_NAME := p_pub_rec.CURRENCY_NAME;
569 x_pvt_rec.ATTRIBUTE_CATEGORY := p_pub_rec.ATTRIBUTE_CATEGORY;
570 x_pvt_rec.ATTRIBUTE1 := p_pub_rec.ATTRIBUTE1;
571 x_pvt_rec.ATTRIBUTE2 := p_pub_rec.ATTRIBUTE2;
572 x_pvt_rec.ATTRIBUTE3 := p_pub_rec.ATTRIBUTE3;
573 x_pvt_rec.ATTRIBUTE4 := p_pub_rec.ATTRIBUTE4;
574 x_pvt_rec.ATTRIBUTE5 := p_pub_rec.ATTRIBUTE5;
575 x_pvt_rec.ATTRIBUTE6 := p_pub_rec.ATTRIBUTE6;
576 x_pvt_rec.ATTRIBUTE7 := p_pub_rec.ATTRIBUTE7;
577 x_pvt_rec.ATTRIBUTE8 := p_pub_rec.ATTRIBUTE8;
578 x_pvt_rec.ATTRIBUTE9 := p_pub_rec.ATTRIBUTE9;
579 x_pvt_rec.ATTRIBUTE10 := p_pub_rec.ATTRIBUTE10;
580 x_pvt_rec.ATTRIBUTE11 := p_pub_rec.ATTRIBUTE11;
581 x_pvt_rec.ATTRIBUTE12 := p_pub_rec.ATTRIBUTE12;
582 x_pvt_rec.ATTRIBUTE13 := p_pub_rec.ATTRIBUTE13;
583 x_pvt_rec.ATTRIBUTE14 := p_pub_rec.ATTRIBUTE14;
584 x_pvt_rec.ATTRIBUTE15 := p_pub_rec.ATTRIBUTE15;
585 x_pvt_rec.TP_ATTRIBUTE_CATEGORY := p_pub_rec.TP_ATTRIBUTE_CATEGORY;
586 x_pvt_rec.TP_ATTRIBUTE1 := p_pub_rec.TP_ATTRIBUTE1;
587 x_pvt_rec.TP_ATTRIBUTE2 := p_pub_rec.TP_ATTRIBUTE2;
588 x_pvt_rec.TP_ATTRIBUTE3 := p_pub_rec.TP_ATTRIBUTE3;
589 x_pvt_rec.TP_ATTRIBUTE4 := p_pub_rec.TP_ATTRIBUTE4;
590 x_pvt_rec.TP_ATTRIBUTE5 := p_pub_rec.TP_ATTRIBUTE5;
591 x_pvt_rec.TP_ATTRIBUTE6 := p_pub_rec.TP_ATTRIBUTE6;
592 x_pvt_rec.TP_ATTRIBUTE7 := p_pub_rec.TP_ATTRIBUTE7;
593 x_pvt_rec.TP_ATTRIBUTE8 := p_pub_rec.TP_ATTRIBUTE8;
594 x_pvt_rec.TP_ATTRIBUTE9 := p_pub_rec.TP_ATTRIBUTE9;
595 x_pvt_rec.TP_ATTRIBUTE10 := p_pub_rec.TP_ATTRIBUTE10;
596 x_pvt_rec.TP_ATTRIBUTE11 := p_pub_rec.TP_ATTRIBUTE11;
600 x_pvt_rec.TP_ATTRIBUTE15 := p_pub_rec.TP_ATTRIBUTE15;
597 x_pvt_rec.TP_ATTRIBUTE12 := p_pub_rec.TP_ATTRIBUTE12;
598 x_pvt_rec.TP_ATTRIBUTE13 := p_pub_rec.TP_ATTRIBUTE13;
599 x_pvt_rec.TP_ATTRIBUTE14 := p_pub_rec.TP_ATTRIBUTE14;
601 x_pvt_rec.GLOBAL_ATTRIBUTE_CATEGORY := p_pub_rec.GLOBAL_ATTRIBUTE_CATEGORY;
602 x_pvt_rec.GLOBAL_ATTRIBUTE1 := p_pub_rec.GLOBAL_ATTRIBUTE1;
603 x_pvt_rec.GLOBAL_ATTRIBUTE2 := p_pub_rec.GLOBAL_ATTRIBUTE2;
604 x_pvt_rec.GLOBAL_ATTRIBUTE3 := p_pub_rec.GLOBAL_ATTRIBUTE3;
605 x_pvt_rec.GLOBAL_ATTRIBUTE4 := p_pub_rec.GLOBAL_ATTRIBUTE4;
606 x_pvt_rec.GLOBAL_ATTRIBUTE5 := p_pub_rec.GLOBAL_ATTRIBUTE5;
607 x_pvt_rec.GLOBAL_ATTRIBUTE6 := p_pub_rec.GLOBAL_ATTRIBUTE6;
608 x_pvt_rec.GLOBAL_ATTRIBUTE7 := p_pub_rec.GLOBAL_ATTRIBUTE7;
609 x_pvt_rec.GLOBAL_ATTRIBUTE8 := p_pub_rec.GLOBAL_ATTRIBUTE8;
610 x_pvt_rec.GLOBAL_ATTRIBUTE9 := p_pub_rec.GLOBAL_ATTRIBUTE9;
611 x_pvt_rec.GLOBAL_ATTRIBUTE10 := p_pub_rec.GLOBAL_ATTRIBUTE10;
612 x_pvt_rec.GLOBAL_ATTRIBUTE11 := p_pub_rec.GLOBAL_ATTRIBUTE11;
613 x_pvt_rec.GLOBAL_ATTRIBUTE12 := p_pub_rec.GLOBAL_ATTRIBUTE12;
614 x_pvt_rec.GLOBAL_ATTRIBUTE13 := p_pub_rec.GLOBAL_ATTRIBUTE13;
615 x_pvt_rec.GLOBAL_ATTRIBUTE14 := p_pub_rec.GLOBAL_ATTRIBUTE14;
616 x_pvt_rec.GLOBAL_ATTRIBUTE15 := p_pub_rec.GLOBAL_ATTRIBUTE15;
617 x_pvt_rec.GLOBAL_ATTRIBUTE16 := p_pub_rec.GLOBAL_ATTRIBUTE16;
618 x_pvt_rec.GLOBAL_ATTRIBUTE17 := p_pub_rec.GLOBAL_ATTRIBUTE17;
619 x_pvt_rec.GLOBAL_ATTRIBUTE18 := p_pub_rec.GLOBAL_ATTRIBUTE18;
620 x_pvt_rec.GLOBAL_ATTRIBUTE19 := p_pub_rec.GLOBAL_ATTRIBUTE19;
621 x_pvt_rec.GLOBAL_ATTRIBUTE20 := p_pub_rec.GLOBAL_ATTRIBUTE20;
622 x_pvt_rec.CREATION_DATE := p_pub_rec.CREATION_DATE;
623 x_pvt_rec.CREATED_BY := p_pub_rec.CREATED_BY;
624 x_pvt_rec.LAST_UPDATE_DATE := p_pub_rec.LAST_UPDATE_DATE;
625 x_pvt_rec.LAST_UPDATED_BY := p_pub_rec.LAST_UPDATED_BY;
626 x_pvt_rec.LAST_UPDATE_LOGIN := p_pub_rec.LAST_UPDATE_LOGIN;
627 x_pvt_rec.PROGRAM_APPLICATION_ID := p_pub_rec.PROGRAM_APPLICATION_ID;
628 x_pvt_rec.PROGRAM_ID := p_pub_rec.PROGRAM_ID;
629 x_pvt_rec.PROGRAM_UPDATE_DATE := p_pub_rec.PROGRAM_UPDATE_DATE;
630 x_pvt_rec.REQUEST_ID := p_pub_rec.REQUEST_ID;
631 x_pvt_rec.NUMBER_OF_LPN := p_pub_rec.NUMBER_OF_LPN;
632 x_pvt_rec.COD_AMOUNT := p_pub_rec.COD_AMOUNT;
633 x_pvt_rec.COD_CURRENCY_CODE := p_pub_rec.COD_CURRENCY_CODE;
634 x_pvt_rec.COD_REMIT_TO := p_pub_rec.COD_REMIT_TO;
635 x_pvt_rec.COD_CHARGE_PAID_BY := p_pub_rec.COD_CHARGE_PAID_BY;
636 x_pvt_rec.PROBLEM_CONTACT_REFERENCE := p_pub_rec.PROBLEM_CONTACT_REFERENCE;
637 x_pvt_rec.PORT_OF_LOADING := p_pub_rec.PORT_OF_LOADING;
638 x_pvt_rec.PORT_OF_DISCHARGE := p_pub_rec.PORT_OF_DISCHARGE;
639 x_pvt_rec.FTZ_NUMBER := p_pub_rec.FTZ_NUMBER;
640 x_pvt_rec.ROUTED_EXPORT_TXN := p_pub_rec.ROUTED_EXPORT_TXN;
641 x_pvt_rec.ENTRY_NUMBER := p_pub_rec.ENTRY_NUMBER;
642 x_pvt_rec.ROUTING_INSTRUCTIONS := p_pub_rec.ROUTING_INSTRUCTIONS;
643 x_pvt_rec.IN_BOND_CODE := p_pub_rec.IN_BOND_CODE;
644 x_pvt_rec.SHIPPING_MARKS := p_pub_rec.SHIPPING_MARKS;
645 x_pvt_rec.SERVICE_LEVEL := p_pub_rec.SERVICE_LEVEL;
646 x_pvt_rec.MODE_OF_TRANSPORT := p_pub_rec.MODE_OF_TRANSPORT;
647 x_pvt_rec.ASSIGNED_TO_FTE_TRIPS := p_pub_rec.ASSIGNED_TO_FTE_TRIPS;
648 x_pvt_rec.PLANNED_FLAG := FND_API.G_MISS_CHAR;
649 x_pvt_rec.STATUS_CODE := FND_API.G_MISS_CHAR;
650 x_pvt_rec.BATCH_ID := FND_API.G_MISS_NUM;
651 x_pvt_rec.HASH_VALUE := FND_API.G_MISS_NUM;
652 x_pvt_rec.SOURCE_HEADER_ID := FND_API.G_MISS_NUM;
653 x_pvt_rec.AUTO_SC_EXCLUDE_FLAG := p_pub_rec.AUTO_SC_EXCLUDE_FLAG;
654 x_pvt_rec.AUTO_AP_EXCLUDE_FLAG := p_pub_rec.AUTO_AP_EXCLUDE_FLAG;
655 x_pvt_rec.AP_BATCH_ID := FND_API.G_MISS_NUM;
656 /*3667348*/
657 x_pvt_rec.REASON_OF_TRANSPORT := p_pub_rec.REASON_OF_TRANSPORT;
658 x_pvt_rec.DESCRIPTION := p_pub_rec.DESCRIPTION;
659 -- Non Database field added for "Proration of weight from Delivery to delivery lines" Project(Bug#4254552).
660 IF p_pub_rec.prorate_wt_flag = FND_API.G_MISS_CHAR THEN
661 --{
662 x_pvt_rec.prorate_wt_flag := WSH_UTIL_CORE.FTE_Is_Installed ;
663 ELSIF p_pub_rec.prorate_wt_flag = 'Y' THEN
664 x_pvt_rec.prorate_wt_flag := 'Y';
665 ELSE
666 x_pvt_rec.prorate_wt_flag := 'N';
667 --}
668 END IF;
669 --
670 -- LSP PROJECT : begin
671 x_pvt_rec.client_id := p_pub_rec.client_id;
672 x_pvt_rec.client_code := p_pub_rec.client_code;
673 -- LSP PROJECT : enb
674 -- RTV changes
675 x_pvt_rec.consignee_flag := p_pub_rec.consignee_flag;
676 -- RTV changes
677
678 IF l_debug_on THEN
679 WSH_DEBUG_SV.pop(l_module_name);
680 END IF;
681 EXCEPTION
682 WHEN OTHERS THEN
683 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_DELIVERIES_PUB.map_pub_to_pvt',l_module_name);
684 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
685 --
686 IF l_debug_on THEN
687 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
688 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
689 END IF;
690 --
691 END map_pub_to_pvt;
692
693 --========================================================================
694 -- PROCEDURE : Create_Update_Delivery PUBLIC
695 --
696 -- PARAMETERS: p_api_version_number known api versionerror buffer
697 -- p_init_msg_list FND_API.G_TRUE to reset list
698 -- x_return_status return status
702 -- p_delivery_name Delivery name for update
699 -- x_msg_count number of messages in the list
700 -- x_msg_data text of messages
701 -- p_delivery_info Attributes for the delivery entity
703 -- x_delivery_id - delivery_Id of new delivery,
704 -- x_name - Name of delivery
705 -- VERSION : current version 1.0
706 -- initial version 1.0
707 -- COMMENT : Creates or updates a record in wsh_new_deliveries table
708 -- with information specified in p_delivery_info
709 --========================================================================
710
711 PROCEDURE Create_Update_Delivery(
712 p_api_version_number IN NUMBER,
713 p_init_msg_list IN VARCHAR2,
714 x_return_status OUT NOCOPY VARCHAR2,
715 x_msg_count OUT NOCOPY NUMBER,
716 x_msg_data OUT NOCOPY VARCHAR2,
717 p_action_code IN VARCHAR2,
718 p_delivery_info IN OUT NOCOPY Delivery_Pub_Rec_Type,
719 p_delivery_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
720 x_delivery_id OUT NOCOPY NUMBER,
721 x_name OUT NOCOPY VARCHAR2)
722
723 IS
724
725 l_api_version_number CONSTANT NUMBER := 1.0;
726 l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Delivery';
727
728 -- <insert here your local variables declaration>
729 l_message VARCHAR2(50);
730 l_num_errors NUMBER;
731 l_num_warnings NUMBER;
732 l_rec_attr_tab wsh_new_deliveries_pvt.delivery_attr_tbl_type;
733 l_delivery_in_rec wsh_deliveries_grp.Del_In_Rec_Type;
734 l_del_out_rec_tab wsh_deliveries_grp.Del_Out_Tbl_Type;
735 l_return_status VARCHAR2(1);
736 l_commit VARCHAR2(100) := FND_API.G_FALSE;
737 l_weight_uom_code VARCHAR2(10);
738 l_volume_uom_code VARCHAR2(10);
739 --
740 l_debug_on BOOLEAN;
741 --
742 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_DELIVERY_PUB';
743
744 BEGIN
745 --
746 --
747 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
748 --
749 IF l_debug_on IS NULL
750 THEN
751 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
752 END IF;
753 --
754 IF l_debug_on THEN
755 wsh_debug_sv.push(l_module_name);
756 END IF;
757 -- Standard call to check for call compatibility
758 IF NOT FND_API.Compatible_API_Call
759 ( l_api_version_number
760 , p_api_version_number
761 , l_api_name
762 , G_PKG_NAME
763 )
764 THEN
765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 END IF;
767
768 -- Initialize message stack if required
769 IF FND_API.to_Boolean(p_init_msg_list) THEN
770 FND_MSG_PUB.initialize;
771 END IF;
772
773
774 IF (p_action_code = 'UPDATE') THEN
775 IF (p_delivery_name IS NOT NULL) OR (p_delivery_name <> FND_API.G_MISS_CHAR) THEN
776 p_delivery_info.name := p_delivery_name;
777 END IF;
778 ELSIF ( p_action_code <> 'CREATE' ) THEN
779 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ACTION_CODE');
780 FND_MESSAGE.SET_TOKEN('ACTION_CODE',p_action_code);
781 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
782 wsh_util_core.add_message(x_return_status);
783 END IF;
784 --
785 map_pub_to_pvt(
786 p_pub_rec => p_delivery_info,
787 x_pvt_rec => l_rec_attr_tab(1),
788 x_return_status => l_return_status);
789 --
790 IF l_debug_on THEN
791 wsh_debug_sv.log(l_module_name,'Return Status After Calling map_pub_to_pvt',l_return_status);
792 END IF;
793 --
794 wsh_util_core.api_post_call(
795 p_return_status => l_return_status,
796 x_num_errors => l_num_errors,
797 x_num_warnings => l_num_warnings);
798 --
799 IF (p_action_code = 'UPDATE') THEN
800 --
801 wsh_util_validate.validate_delivery_name(
802 p_delivery_id => l_rec_attr_tab(1).delivery_id,
803 p_delivery_name => l_rec_attr_tab(1).name,
804 x_return_status => l_return_status);
805 --
806 IF l_debug_on THEN
807 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_delivery_name',l_return_status);
808 END IF;
809 --
810 wsh_util_core.api_post_call(
811 p_return_status => l_return_status,
812 x_num_errors => l_num_errors,
813 x_num_warnings => l_num_warnings);
814 --
815 -- Bug 9772252: Added code to lock the delivery when the action is 'Update'
816 IF l_debug_on THEN
817 wsh_debug_sv.log(l_module_name,'Locking del ',l_rec_attr_tab(1).delivery_id);
818 END IF;
819 BEGIN
820 WSH_NEW_DELIVERIES_PVT.Lock_Dlvy_No_Compare(l_rec_attr_tab(1).delivery_id);
821 EXCEPTION
822
823 WHEN OTHERS THEN
824 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
825 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
826 WSH_UTIL_CORE.add_message (WSH_UTIL_CORE.G_RET_STS_ERROR);
827 IF l_debug_on THEN
828 WSH_DEBUG_SV.log(l_module_name,'Could not lock delivery: ',p_delivery_info.delivery_id);
829 END IF;
830 goto count_messasge;
831 END;
832 --Bug 9772252: End of changes
833
834 -- bug 3666967 : else if p_action_code is 'CREATE'
835 -- treating non passed parameters as FND_API.G_MISS_NUM
839 l_rec_attr_tab(1).volume = FND_API.G_MISS_NUM) THEN
836 ELSIF (p_action_code = 'CREATE') THEN
837 IF (l_rec_attr_tab(1).gross_weight = FND_API.G_MISS_NUM AND
838 l_rec_attr_tab(1).net_weight = FND_API.G_MISS_NUM AND
840 l_rec_attr_tab(1).wv_frozen_flag := 'N';
841 ELSE
842 l_rec_attr_tab(1).wv_frozen_flag := 'Y';
843 END IF;
844
845 --Added for bug 8369407
846 IF (l_rec_attr_tab(1).ORGANIZATION_ID <> FND_API.G_MISS_NUM OR
847 l_rec_attr_tab(1).ORGANIZATION_CODE <> FND_API.G_MISS_CHAR
848 ) AND
849 (l_rec_attr_tab(1).WEIGHT_UOM_CODE = FND_API.G_MISS_CHAR OR
850 l_rec_attr_tab(1).VOLUME_UOM_CODE = FND_API.G_MISS_CHAR
851 ) THEN
852 --
853 wsh_util_validate.validate_org( p_org_id => l_rec_attr_tab(1).ORGANIZATION_ID ,
854 p_org_code => l_rec_attr_tab(1).ORGANIZATION_CODE ,
855 x_return_status => l_return_status );
856 --
857 IF l_debug_on THEN
858 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_org',l_return_status);
859 END IF;
860 --
861 wsh_util_core.api_post_call(p_return_status => l_return_status,
862 x_num_errors => l_num_errors,
863 x_num_warnings => l_num_warnings );
864 --
865 wsh_wv_utils.get_default_uoms( p_organization_id => l_rec_attr_tab(1).ORGANIZATION_ID,
866 x_weight_uom_code => l_weight_uom_code,
867 x_volume_uom_code => l_volume_uom_code,
868 x_return_status => l_return_status );
869 --
870 IF l_debug_on THEN
871 wsh_debug_sv.log(l_module_name,'Return Status After Calling get_default_uoms',l_return_status);
872 END IF;
873 --
874 wsh_util_core.api_post_call(p_return_status => l_return_status,
875 x_num_errors => l_num_errors,
876 x_num_warnings => l_num_warnings );
877 --
878 IF l_rec_attr_tab(1).WEIGHT_UOM_CODE = FND_API.G_MISS_CHAR THEN
879 l_rec_attr_tab(1).WEIGHT_UOM_CODE := l_weight_uom_code ;
880 END IF;
881 --
882 IF l_rec_attr_tab(1).VOLUME_UOM_CODE = FND_API.G_MISS_CHAR THEN
883 l_rec_attr_tab(1).VOLUME_UOM_CODE := l_volume_uom_code ;
884 END IF;
885 --
886 END IF;
887 END IF;
888 --
889 l_delivery_in_rec.action_code := p_action_code;
890 l_delivery_in_rec.caller := 'WSH_PUB';
891 wsh_interface_grp.create_update_delivery(
892 p_api_version_number => p_api_version_number,
893 p_init_msg_list => p_init_msg_list,
894 p_commit => l_commit,
895 p_in_rec => l_delivery_in_rec,
896 p_rec_attr_tab => l_rec_attr_tab,
897 x_del_out_rec_tab => l_del_out_rec_tab,
898 x_return_status => l_return_status,
899 x_msg_count => x_msg_count,
900 x_msg_data => x_msg_data);
901 --
902 IF l_debug_on THEN
903 wsh_debug_sv.log(l_module_name,'Return Status After Calling create_update_delivery',l_return_status);
904 END IF;
905 --
906 wsh_util_core.api_post_call(
907 p_return_status => l_return_status,
908 x_num_errors => l_num_errors,
909 x_num_warnings => l_num_warnings,
910 p_msg_data => x_msg_data);
911 --
912 --
913 IF l_del_out_rec_tab.COUNT <> 0 THEN
914 --
915 x_delivery_id := l_del_out_rec_tab(l_del_out_rec_tab.COUNT).delivery_id;
916 x_name := l_del_out_rec_tab(l_del_out_rec_tab.COUNT).name;
917 --
918 END IF;
919 --
920 IF l_num_warnings > 0 THEN
921 x_return_status := wsh_util_core.g_ret_sts_warning;
922 ELSE
923 x_return_status := wsh_util_core.g_ret_sts_success; --bug 2398628
924 END IF;
925 --
926 <<count_messasge>>--Added for bug 9772252
927 FND_MSG_PUB.Count_And_Get
928 ( p_count => x_msg_count
929 , p_data => x_msg_data
930 );
931 --
932 IF l_debug_on THEN
933 WSH_DEBUG_SV.pop(l_module_name);
934 END IF;
935 --
936 EXCEPTION
937 WHEN FND_API.G_EXC_ERROR THEN
938 x_return_status := FND_API.G_RET_STS_ERROR;
939 -- Get message count and data
940 FND_MSG_PUB.Count_And_Get
941 ( p_count => x_msg_count
942 , p_data => x_msg_data
943 );
944 --
945 IF l_debug_on THEN
946 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
947 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
948 END IF;
949 --
950 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
952 -- Get message count and data
953 FND_MSG_PUB.Count_And_Get
954 ( p_count => x_msg_count
955 , p_data => x_msg_data
956 );
957 --
958 IF l_debug_on THEN
959 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
960 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
961 END IF;
962 --
963 WHEN OTHERS THEN
964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
968 ( G_PKG_NAME
965 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
966 THEN
967 FND_MSG_PUB.Add_Exc_Msg
969 , '_x_'
970 );
971 END IF;
972 -- Get message count and data
973 FND_MSG_PUB.Count_And_Get
974 ( p_count => x_msg_count
975 , p_data => x_msg_data
976 );
977 --
978 IF l_debug_on THEN
979 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
980 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
981 END IF;
982 --
983 END Create_Update_Delivery;
984
985 PROCEDURE Delivery_Action
986 ( p_api_version_number IN NUMBER,
987 p_init_msg_list IN VARCHAR2,
988 x_return_status OUT NOCOPY VARCHAR2,
989 x_msg_count OUT NOCOPY NUMBER,
990 x_msg_data OUT NOCOPY VARCHAR2,
991 p_action_code IN VARCHAR2,
992 p_delivery_id IN NUMBER DEFAULT NULL,
993 p_delivery_name IN VARCHAR2 DEFAULT NULL,
994 p_asg_trip_id IN NUMBER DEFAULT NULL,
995 p_asg_trip_name IN VARCHAR2 DEFAULT NULL,
996 p_asg_pickup_stop_id IN NUMBER DEFAULT NULL,
997 p_asg_pickup_loc_id IN NUMBER DEFAULT NULL,
998 p_asg_pickup_stop_seq IN NUMBER DEFAULT NULL,
999 p_asg_pickup_loc_code IN VARCHAR2 DEFAULT NULL,
1000 p_asg_pickup_arr_date IN DATE DEFAULT NULL,
1001 p_asg_pickup_dep_date IN DATE DEFAULT NULL,
1002 p_asg_dropoff_stop_id IN NUMBER DEFAULT NULL,
1003 p_asg_dropoff_loc_id IN NUMBER DEFAULT NULL,
1004 p_asg_dropoff_stop_seq IN NUMBER DEFAULT NULL,
1005 p_asg_dropoff_loc_code IN VARCHAR2 DEFAULT NULL,
1006 p_asg_dropoff_arr_date IN DATE DEFAULT NULL,
1007 p_asg_dropoff_dep_date IN DATE DEFAULT NULL,
1008 p_sc_action_flag IN VARCHAR2 DEFAULT 'S',
1009 p_sc_intransit_flag IN VARCHAR2 DEFAULT 'N',
1010 p_sc_close_trip_flag IN VARCHAR2 DEFAULT 'N',
1011 p_sc_create_bol_flag IN VARCHAR2 DEFAULT 'N',
1012 p_sc_stage_del_flag IN VARCHAR2 DEFAULT 'Y',
1013 p_sc_trip_ship_method IN VARCHAR2 DEFAULT NULL,
1014 p_sc_actual_dep_date IN DATE DEFAULT NULL,
1015 p_sc_report_set_id IN NUMBER DEFAULT NULL,
1016 p_sc_report_set_name IN VARCHAR2 DEFAULT NULL,
1017 p_sc_defer_interface_flag IN VARCHAR2 DEFAULT 'Y',
1018 p_sc_send_945_flag IN VARCHAR2 DEFAULT NULL,
1019 p_sc_rule_id IN NUMBER DEFAULT NULL,
1020 p_sc_rule_name IN VARCHAR2 DEFAULT NULL,
1021 p_wv_override_flag IN VARCHAR2 DEFAULT 'N',
1022 x_trip_id OUT NOCOPY VARCHAR2,
1023 x_trip_name OUT NOCOPY VARCHAR2)
1024 --
1025 IS
1026 --
1027 l_api_version_number CONSTANT NUMBER := 1.0;
1028 l_api_name CONSTANT VARCHAR2(30):= 'Delivery_Action';
1029 --
1030 l_debug_on BOOLEAN;
1031 --
1032 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELIVERY_ACTION_PUB';
1033 --
1034 --
1035 l_action_prms wsh_deliveries_grp.action_parameters_rectype;
1036 l_del_action_out_rec wsh_deliveries_grp.Delivery_Action_Out_Rec_Type;
1037 l_delivery_id_tab wsh_util_core.id_tab_type;
1038 --
1039 l_delivery_id NUMBER := p_delivery_id;
1040 --
1041 l_num_errors NUMBER := 0;
1042 l_num_warnings NUMBER := 0;
1043 l_return_status VARCHAR2(1);
1044 l_first NUMBER := 0;
1045 l_commit VARCHAR2(100) := FND_API.G_FALSE;
1046 --
1047 BEGIN
1048 --
1049 --
1050 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1051 --
1052 IF l_debug_on IS NULL
1053 THEN
1054 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1055 END IF;
1056 --
1057 IF l_debug_on THEN
1058 wsh_debug_sv.push(l_module_name);
1059 END IF;
1060 --
1061 -- Standard call to check for call compatibility
1062 IF NOT FND_API.Compatible_API_Call
1063 ( l_api_version_number
1064 , p_api_version_number
1065 , l_api_name
1066 , G_PKG_NAME
1067 )
1068 THEN
1069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1070 END IF;
1071
1072 -- Initialize message stack if required
1073 IF FND_API.to_Boolean(p_init_msg_list) THEN
1074 FND_MSG_PUB.initialize;
1075 END IF;
1076
1077 wsh_util_validate.validate_delivery_name(
1078 p_delivery_id => l_delivery_id,
1079 p_delivery_name => p_delivery_name,
1080 x_return_status => l_return_status);
1081 --
1082 IF l_debug_on THEN
1083 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_delivery_name',l_return_status);
1084 END IF;
1085 --
1086 wsh_util_core.api_post_call(
1087 p_return_status => l_return_status,
1088 x_num_errors => l_num_errors,
1089 x_num_warnings => l_num_warnings);
1090 --
1091 l_action_prms.caller := 'WSH_PUB';
1092 l_action_prms.action_code := p_action_code;
1093 --
1094 -- initializing the action specific parameters
1095 l_action_prms.trip_id := p_asg_trip_id;
1096 l_action_prms.trip_name := p_asg_trip_name;
1097 l_action_prms.pickup_stop_id := p_asg_pickup_stop_id;
1098 l_action_prms.pickup_loc_id := p_asg_pickup_loc_id;
1099 l_action_prms.pickup_stop_seq := p_asg_pickup_stop_seq;
1100 l_action_prms.pickup_loc_code := p_asg_pickup_loc_code;
1104 l_action_prms.dropoff_loc_id := p_asg_dropoff_loc_id;
1101 l_action_prms.pickup_arr_date := p_asg_pickup_arr_date;
1102 l_action_prms.pickup_dep_date := p_asg_pickup_dep_date;
1103 l_action_prms.dropoff_stop_id := p_asg_dropoff_stop_id;
1105 l_action_prms.dropoff_stop_seq := p_asg_dropoff_stop_seq;
1106 l_action_prms.dropoff_loc_code := p_asg_dropoff_loc_code;
1107 l_action_prms.dropoff_arr_date := p_asg_dropoff_arr_date;
1108 l_action_prms.dropoff_dep_date := p_asg_dropoff_dep_date;
1109 l_action_prms.action_flag := p_sc_action_flag;
1110 l_action_prms.intransit_flag := p_sc_intransit_flag;
1111 l_action_prms.close_trip_flag := p_sc_close_trip_flag;
1112 l_action_prms.bill_of_lading_flag := p_sc_create_bol_flag;
1113 l_action_prms.stage_del_flag := p_sc_stage_del_flag;
1114 l_action_prms.ship_method_code := p_sc_trip_ship_method;
1115 l_action_prms.actual_dep_date := p_sc_actual_dep_date;
1116 l_action_prms.report_set_id := p_sc_report_set_id;
1117 l_action_prms.report_set_name := p_sc_report_set_name;
1118 l_action_prms.defer_interface_flag := p_sc_defer_interface_flag;
1119 l_action_prms.send_945_flag := p_sc_send_945_flag;
1120 l_action_prms.sc_rule_id := p_sc_rule_id;
1121 l_action_prms.sc_rule_name := p_sc_rule_name;
1122 l_action_prms.override_flag := p_wv_override_flag;
1123 IF p_action_code = 'WT-VOL' THEN
1124 l_action_prms.override_flag := 'Y';
1125 END IF;
1126 --
1127 l_delivery_id_tab(1) := l_delivery_id;
1128 --
1129 wsh_interface_grp.Delivery_Action(
1130 p_api_version_number => p_api_version_number,
1131 p_init_msg_list => p_init_msg_list,
1132 p_commit => l_commit,
1133 p_action_prms => l_action_prms,
1134 p_delivery_id_tab => l_delivery_id_tab,
1135 x_delivery_out_rec => l_del_action_out_rec,
1136 x_return_status => x_return_status,
1137 x_msg_count => x_msg_count,
1138 x_msg_data => x_msg_data);
1139 --
1140 IF l_debug_on THEN
1141 wsh_debug_sv.log(l_module_name,'Return Status After Calling Delivery_Action Wrapper',x_return_status);
1142 END IF;
1143 --
1144 wsh_util_core.api_post_call(
1145 p_return_status => x_return_status,
1146 x_num_errors => l_num_errors,
1147 x_num_warnings => l_num_warnings);
1148 --
1149 IF p_action_code = 'AUTOCREATE-TRIP' THEN
1150 IF l_del_action_out_rec.result_id_tab.count > 0 THEN
1151 l_first := l_del_action_out_rec.result_id_tab.first;
1152 IF l_del_action_out_rec.result_id_tab(l_first) IS NOT NULL THEN
1153 x_trip_id := l_del_action_out_rec.result_id_tab(l_first);
1154 x_trip_name := wsh_trips_pvt.get_name(l_del_action_out_rec.result_id_tab(l_first));
1155 END IF;
1156 END IF;
1157 END IF;
1158 --
1159 IF l_num_warnings > 0 THEN
1160 x_return_status := wsh_util_core.g_ret_sts_warning;
1161 ELSE
1162 x_return_status := wsh_util_core.g_ret_sts_success; --bug 2398628
1163 END IF;
1164 --
1165 FND_MSG_PUB.Count_And_Get
1166 ( p_count => x_msg_count
1167 , p_data => x_msg_data
1168 );
1169 --
1170 IF l_debug_on THEN
1171 WSH_DEBUG_SV.pop(l_module_name);
1172 END IF;
1173 --
1174 EXCEPTION
1175 WHEN FND_API.G_EXC_ERROR THEN
1176 x_return_status := FND_API.G_RET_STS_ERROR;
1177 -- Get message count and data
1178 FND_MSG_PUB.Count_And_Get
1179 ( p_count => x_msg_count
1180 , p_data => x_msg_data
1181 );
1182 --
1183 IF l_debug_on THEN
1184 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1185 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1186 END IF;
1187 --
1188 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1190 -- Get message count and data
1191 FND_MSG_PUB.Count_And_Get
1192 ( p_count => x_msg_count
1193 , p_data => x_msg_data
1194 );
1195 --
1196 IF l_debug_on THEN
1197 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1198 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1199 END IF;
1200 --
1201 WHEN OTHERS THEN
1202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1203 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1204 THEN
1205 FND_MSG_PUB.Add_Exc_Msg
1206 ( G_PKG_NAME
1207 , '_x_'
1208 );
1209 END IF;
1210 -- Get message count and data
1211 FND_MSG_PUB.Count_And_Get
1212 ( p_count => x_msg_count
1213 , p_data => x_msg_data
1214 );
1215 --
1216 IF l_debug_on THEN
1217 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1218 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1219 END IF;
1220 --
1221
1222 END Delivery_Action;
1223
1224 --============================================================================
1228 -- p_organization_code Organization Code
1225 -- PROCEDURE : Generate_Documents PUBLIC API
1226 --
1227 -- PARAMETERS : p_report_set_name Report Set Name
1229 -- p_delivery_name Delivery Name's
1230 -- x_msg_count Error Message Count
1231 -- x_msg_data Error Message
1232 -- x_return_status Return Status
1233 --
1234 -- VERSION : current version 1.0
1235 -- initial version 1.0
1236 --
1237 -- COMMENT : This Procedure is created for Backward Compatability.
1238 -- This procedure inturn calls procedure Delivery_Actions for
1239 -- generating documents.
1240 --
1241 -- CREATED BY : version 1.0.1 UESHANKA
1242 -- CREATION DT : version 1.0.1 12/MAR/2003
1243 --
1244 --============================================================================
1245
1246 PROCEDURE Generate_Documents
1247 ( p_report_set_name IN VARCHAR2,
1248 p_organization_code IN VARCHAR2,
1249 p_delivery_name IN WSH_UTIL_CORE.Column_Tab_Type,
1250 x_msg_count OUT NOCOPY NUMBER,
1251 x_msg_data OUT NOCOPY VARCHAR2,
1252 x_return_status OUT NOCOPY VARCHAR2
1253 ) IS
1254 -- Local Variables
1255 l_delivery_count NUMBER;
1256 l_count NUMBER;
1257 l_delivery_id NUMBER;
1258 l_delivery_name WSH_NEW_DELIVERIES.Name%TYPE;
1259 l_num_errors NUMBER;
1260 l_num_warnings NUMBER;
1261
1262 -- Debug Variables
1263 l_debug_on BOOLEAN;
1264 l_module_name CONSTANT VARCHAR2(75) := 'WSH.PLSQL.' ||
1265 G_PKG_NAME ||
1266 '.Generate_Documents';
1267
1268 -- Parameter Variables for WSH_DOCUMENT_SET.Print_Document_Sets
1269 l_report_set_id NUMBER;
1270 l_organization_id NUMBER;
1271 l_trip_id WSH_TRIPS.Trip_Id%TYPE;
1272 l_trip_name WSH_TRIPS.Name%TYPE;
1273 l_msg_count NUMBER;
1274 l_msg_data VARCHAR2(500);
1275 l_return_status VARCHAR2(1);
1276 l_delivery_ids WSH_UTIL_CORE.Id_Tab_Type;
1277 l_document_param_info WSH_DOCUMENT_SETS.Document_Set_Tab_Type;
1278
1279 -- Exception
1280 WSH_INVALID_ORGANIZATION EXCEPTION;
1281 WSH_INVALID_DELIVERY EXCEPTION;
1282 BEGIN
1283 -- Enabling Debug Starts
1284 l_debug_on := WSH_DEBUG_INTERFACE.G_DEBUG;
1285
1286 IF ( l_debug_on IS NULL ) THEN
1287 l_debug_on := WSH_DEBUG_SV.Is_Debug_Enabled;
1288 END IF;
1289
1290 IF l_debug_on THEN
1291 WSH_DEBUG_SV.Push(l_module_name, 'Generate_Documents');
1292 WSH_DEBUG_SV.Logmsg(l_module_name, 'Parameters passed to Generate_Documents');
1293 WSH_DEBUG_SV.Logmsg(l_module_name, 'P_Report_Set_Name => ' || p_report_set_name);
1294 WSH_DEBUG_SV.Logmsg(l_module_name, 'P_Organization_Code => ' || p_organization_code);
1295 IF p_delivery_name.count > 0 THEN
1296 WSH_DEBUG_SV.Logmsg(l_module_name, 'Delivery Names Passed : ');
1297 FOR i IN 1..p_delivery_name.count LOOP
1298 WSH_DEBUG_SV.Logmsg(l_module_name, 'P_Delivery_Name(' || i || ') => '
1299 || p_delivery_name(i));
1300 END LOOP;
1301 END IF;
1302 END IF;
1303
1304 -- Assigning Success to x_return_status Initially
1305 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1306 l_count := 0;
1307
1308 -- Validation of Organization_Code
1309 WSH_UTIL_VALIDATE.Validate_Org(
1310 l_organization_id ,
1311 p_organization_code,
1312 x_return_status );
1313 IF l_debug_on THEN
1314 WSH_DEBUG_SV.Logmsg(l_module_name, 'Return status from Validate_Org : ' || x_return_status);
1315 END IF;
1316
1317 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1318 RAISE WSH_INVALID_ORGANIZATION;
1319 END IF;
1320
1321 -- Validation of Delivery Names
1322 l_delivery_count := p_delivery_name.count;
1323
1324 IF (nvl(l_delivery_count, 0) > 0) THEN
1325 BEGIN
1326 FOR i in 1..l_delivery_count LOOP
1327
1328 l_delivery_name := p_delivery_name(i);
1329 l_delivery_id := NULL;
1330 WSH_UTIL_VALIDATE.Validate_Delivery_Name(
1331 l_delivery_id ,
1332 l_delivery_name,
1333 x_return_status);
1334 IF l_debug_on THEN
1335 WSH_DEBUG_SV.Logmsg(l_module_name, 'Return status for Delivery Name (' ||
1336 l_delivery_name || ') : ' || x_return_status);
1337 END IF;
1338
1339 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1340 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_DELIVERY');
1341 FND_MESSAGE.Set_Token('DELIVERY', l_delivery_name);
1342 WSH_UTIL_CORE.Add_Message(x_return_status);
1343 ELSE
1344 l_count := l_count + 1;
1345 l_delivery_ids(l_count) := l_delivery_id;
1346 END IF;
1347
1348 END LOOP;
1349 END;
1350 ELSE
1351 RAISE WSH_INVALID_DELIVERY;
1352 END IF;
1353
1354 -- WSH_DELIVERIES_PUB.Delivery_Actions called for generating and printing reports.
1355 IF (l_count > 0) THEN
1356 FOR i IN 1..l_count LOOP -- { l_Count For Loop
1357
1358 l_delivery_id := l_delivery_ids(i);
1359 WSH_DELIVERIES_PUB.Delivery_Action(
1360 p_api_version_number => 1.0,
1361 p_init_msg_list => 'F',
1362 p_action_code => 'PRINT-DOC-SETS',
1363 p_sc_report_set_name => p_report_set_name,
1364 p_delivery_id => l_delivery_id ,
1365 x_msg_count => l_msg_count,
1366 x_msg_data => l_msg_data,
1367 x_trip_id => l_trip_id ,
1368 x_trip_name => l_trip_name,
1369 x_return_status => l_return_status);
1370
1371 WSH_UTIL_CORE.Api_Post_Call(
1372 p_return_status => l_return_status,
1373 x_num_errors => l_num_errors ,
1374 x_num_warnings => l_num_warnings );
1375
1376 IF l_debug_on THEN
1377 WSH_DEBUG_SV.Logmsg(l_module_name, 'Return status for Delivery Id (' ||
1378 l_delivery_id || ') : ' || l_return_status);
1379 END IF;
1380
1381 END LOOP; -- } l_Count For Loop.
1382
1383 IF l_num_warnings > 0 THEN
1384 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1385 ELSE
1386 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1387 END IF;
1388
1389 ELSE
1390 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1391 IF l_debug_on THEN
1392 WSH_DEBUG_SV.Logmsg(l_module_name,
1393 'Deliveries passed are Invalid for generating ' || upper(p_report_set_name));
1394 END IF;
1395
1396 END IF;
1397
1398 FND_MSG_PUB.Count_And_Get(
1399 p_count => x_msg_count,
1400 p_data => x_msg_data );
1401
1402 IF l_debug_on THEN
1403 WSH_DEBUG_SV.Pop(l_module_name);
1404 END IF;
1405
1406 -- Exception Handling Block
1407 EXCEPTION
1408 WHEN FND_API.G_EXC_ERROR THEN
1409 x_return_status := FND_API.G_RET_STS_ERROR;
1410 IF l_debug_on THEN
1411 WSH_DEBUG_SV.Logmsg(l_module_name,
1412 'Expected error occured. Oracle error message is ' || SQLERRM,
1413 WSH_DEBUG_SV.C_EXCEP_LEVEL);
1414 WSH_DEBUG_SV.Pop(l_module_name, 'EXCEPTION: FND_API.G_EXC_ERROR');
1415 END IF;
1416 FND_MSG_PUB.Count_And_Get(
1417 p_count => x_msg_count,
1418 p_data => x_msg_data );
1419
1420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1422 IF l_debug_on THEN
1423 WSH_DEBUG_SV.Logmsg(l_module_name,
1424 'Unexpected error occured. Oracle error message is ' || SQLERRM,
1425 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1426 WSH_DEBUG_SV.Pop(l_module_name, 'EXCEPTION: FND_API.G_EXC_UNEXPECTED_ERROR');
1427 END IF;
1428 FND_MSG_PUB.Count_And_Get(
1429 p_count => x_msg_count,
1430 p_data => x_msg_data );
1431
1432 WHEN WSH_INVALID_ORGANIZATION THEN
1433 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1434 IF l_debug_on THEN
1435 WSH_DEBUG_SV.Logmsg(l_module_name, 'WSH_INVALID_ORGANIZATION : ' || x_return_status);
1436 END IF;
1437 FND_MSG_PUB.Count_And_Get(
1438 p_count => x_msg_count,
1439 p_data => x_msg_data );
1440
1441 WHEN WSH_INVALID_DELIVERY THEN
1442 FND_MESSAGE.Set_Name('WSH', 'WSH_DOC_INVALID_DELIVERY');
1443 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1444 IF l_debug_on THEN
1445 WSH_DEBUG_SV.Logmsg(l_module_name, 'WSH_INVALID_DELIVERY : ' || x_return_status);
1446 END IF;
1447 WSH_UTIL_CORE.Add_Message(x_return_status);
1448 FND_MSG_PUB.Count_And_Get(
1449 p_count => x_msg_count,
1450 p_data => x_msg_data );
1451
1452 WHEN OTHERS THEN
1453 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERIES_PUB.GENEREATE_DOCUMENTS');
1454 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1455 IF l_debug_on THEN
1456 WSH_DEBUG_SV.Logmsg(l_module_name, 'Unexpected Error : ' || x_return_status);
1457 WSH_DEBUG_SV.Logmsg(l_module_name, 'Error Message : ' || SQLERRM);
1458 END IF;
1459 FND_MSG_PUB.Count_And_Get(
1460 p_count => x_msg_count,
1461 p_data => x_msg_data );
1462 END Generate_Documents;
1463
1464
1465
1466 END WSH_DELIVERIES_PUB;