[Home] [Help]
PACKAGE BODY: APPS.WSH_INBOUND_UTIL_PKG
Source
1 PACKAGE BODY WSH_INBOUND_UTIL_PKG as
2 /* $Header: WSHIBUTB.pls 120.6 2005/09/13 17:20:27 rvishnuv noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_INBOUND_UTIL_PKG';
6
7 --========================================================================
8 -- PROCEDURE : Copy_Po_Rec_To_Line_Rec This procedure is used to copy
9 -- all the attributes into x_line_rec
10 -- either from p_po_line_rec or from
11 -- the previous record in x_line_rec
12 -- based on the value of
13 -- p_prev_line_rec_index.
14 --
15 -- PARAMETERS: p_po_line_rec PO_FTE_INTEGRATION_GRP.po_release_rec_type
16 -- x_line_rec OE_WSH_BULK_GRP.line_rec_type
17 -- p_po_line_location_id po_line_location_id of
18 -- po_line_locations_all.
19 -- p_prev_line_rec_index previous record's index of
20 -- x_line_rec.
21 -- x_return_status Return status of the API.
22 --
23 -- COMMENT : This procedure is used to copy all the attributes into
24 -- x_line_rec either from p_po_line_rec or from the previous
25 -- record in x_line_rec based on the value of p_prev_line_rec_index.
26 -- We first call the Extend_Tables procedure to extend
27 -- x_line_rec by one more index.
28 -- If the p_prev_line_rec_index is null, then we copy
29 -- the data in the x_line_rec from p_po_line_rec
30 -- else we copy the data into x_line_rec from the previous
31 -- index (p_prev_line_rec_index) of x_line_rec.
32 --========================================================================
33 PROCEDURE copy_po_rec_to_line_rec(
34 p_po_line_rec IN PO_FTE_INTEGRATION_GRP.po_release_rec_type,
35 x_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
36 p_po_line_location_id IN NUMBER,
37 p_prev_line_rec_index IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2)
39 IS
40 --{
41 l_tab_count NUMBER;
42 l_return_status VARCHAR2(1);
43 l_num_errors NUMBER := 0;
44 l_num_warnings NUMBER := 0;
45 l_additional_line_info_rec WSH_BULK_PROCESS_PVT.additional_line_info_rec_type;
46 l_action_prms WSH_BULK_TYPES_GRP.action_parameters_rectype;
47 --
48 --
49 cursor l_fr_term_fob_code_csr(p_source_header_id IN NUMBER,
50 p_source_line_id IN NUMBER,
51 p_po_shipment_line_id IN NUMBER) is
52
53 select fob_code,
54 freight_terms_code
55 from wsh_delivery_details
56 where source_line_id = p_source_line_id
57 and source_header_id = p_source_header_id
58 and po_shipment_line_id = p_po_shipment_line_id
59 and source_code = 'PO'
60 and rownum =1;
61
62 l_fob_point_code VARCHAR2(32767);
63 l_fr_terms_code VARCHAR2(32767);
64
65 --
66 --
67 --
68 l_debug_on BOOLEAN;
69 --
70 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'COPY_PO_REC_TO_LINE_REC';
71
72 --
73 --}
74 BEGIN
75 --{
76 --
77 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
78 --
79 IF l_debug_on IS NULL
80 THEN
81 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
82 END IF;
83 --
84 --
85 -- Debug Statements
86 --
87 IF l_debug_on THEN
88 WSH_DEBUG_SV.push(l_module_name);
89 --
90 WSH_DEBUG_SV.log(l_module_name,'P_PO_LINE_LOCATION_ID',P_PO_LINE_LOCATION_ID);
91 WSH_DEBUG_SV.log(l_module_name,'P_PREV_LINE_REC_INDEX',P_PREV_LINE_REC_INDEX);
92 END IF;
93 --
94 x_return_status := wsh_util_core.g_ret_sts_success;
95
96 /*
97 x_line_rec.header_id.extend;
98 x_line_rec.po_shipment_line_id.extend;
99 x_line_rec.source_blanket_reference_id.extend;
100 x_line_rec.sold_to_org_id.extend;
101 x_line_rec.sold_to_contact_id.extend;
102 x_line_rec.ship_from_location_id.extend;
103 x_line_rec.ship_to_contact_id.extend;
104 x_line_rec.deliver_to_location_id.extend;
105 x_line_rec.deliver_to_contact_id.extend;
106 x_line_rec.deliver_to_org_id.extend;
107 x_line_rec.intmed_ship_to_location_id.extend;
108 x_line_rec.intermed_ship_to_contact_id.extend;
109 x_line_rec.requested_quantity.extend;
110 x_line_rec.requested_quantity_uom.extend;
111 x_line_rec.subinventory.extend;
112 x_line_rec.shipping_method_code.extend;
113 x_line_rec.customer_item_id.extend;
114 x_line_rec.shipment_priority_code.extend;
115 x_line_rec.dep_plan_required_flag.extend;
116 x_line_rec.cust_production_seq_num.extend;
117 x_line_rec.customer_dock_code.extend;
118 x_line_rec.cust_model_serial_number.extend;
119 x_line_rec.customer_job.extend;
120 x_line_rec.customer_production_line.extend;
121 x_line_rec.net_weight.extend;
122 x_line_rec.weight_uom_code.extend;
123 x_line_rec.volume.extend;
124 x_line_rec.volume_uom_code.extend;
125 x_line_rec.mvt_stat_status.extend;
126 x_line_rec.ship_set_id.extend;
127 x_line_rec.arrival_set_id.extend;
128 x_line_rec.ship_model_complete_flag.extend;
129 x_line_rec.top_model_line_id.extend;
130 x_line_rec.source_header_number.extend;
131 x_line_rec.source_header_type_id.extend;
132 x_line_rec.source_header_type_name.extend;
133 x_line_rec.cust_po_number.extend;
134 x_line_rec.ato_line_id.extend;
135 x_line_rec.shipping_instructions.extend;
136 x_line_rec.packing_instructions.extend;
137 x_line_rec.project_id.extend;
138 x_line_rec.task_id.extend;
139 x_line_rec.gross_weight.extend;
140 x_line_rec.seal_code.extend;
141 x_line_rec.end_item_unit_number.extend;
142 x_line_rec.freight_class_cat_id.extend;
143 x_line_rec.commodity_code_cat_id.extend;
144 x_line_rec.requested_quantity2.extend;
145 x_line_rec.requested_quantity_uom2.extend;
146 x_line_rec.pickable_flag.extend;
147 x_line_rec.original_subinventory.extend;
148 x_line_rec.line_set_id.extend;
149 x_line_rec.source_document_type_id.extend;
150 x_line_rec.source_blanket_reference_num.extend;
151 x_line_rec.hold_code.extend;
152 x_line_rec.vendor_id.extend;
153 x_line_rec.ship_from_site_id.extend;
154 x_line_rec.freight_terms_code.extend;
155 x_line_rec.fob_point_code.extend;
156 x_line_rec.shipping_control.extend;
157 x_line_rec.currency_code.extend;
158 x_line_rec.source_line_number.extend;
159 x_line_rec.supplier_item_num.extend;
160 x_line_rec.source_line_type_code.extend;
161 x_line_rec.hazard_class_id.extend;
162 x_line_rec.inventory_item_id.extend;
163 x_line_rec.item_description.extend;
164 x_line_rec.revision.extend;
165 x_line_rec.po_shipment_line_number.extend;
166 x_line_rec.country_of_origin.extend;
167 x_line_rec.organization_id.extend;
168 x_line_rec.ship_to_location_id.extend;
169 x_line_rec.ordered_quantity.extend;
170 x_line_rec.order_quantity_uom.extend;
171 x_line_rec.unit_list_price.extend;
172 --x_line_rec.status_code.extend;
173 x_line_rec.preferred_grade.extend;
174 x_line_rec.cancelled_quantity.extend;
175 x_line_rec.ordered_quantity2.extend;
176 x_line_rec.ordered_quantity_uom2.extend;
177 x_line_rec.ship_tolerance_above.extend;
178 x_line_rec.ship_tolerance_below.extend;
179 x_line_rec.qty_rcv_exception_code.extend;
180 x_line_rec.request_date.extend;
181 x_line_rec.schedule_ship_date.extend;
182 x_line_rec.days_early_receipt_allowed.extend;
183 x_line_rec.days_late_receipt_allowed.extend;
184 x_line_rec.receipt_days_exception_code.extend;
185 x_line_rec.enforce_ship_to_location_code.extend;
186 x_line_rec.shipping_details_updated_on.extend;
187 x_line_rec.ship_to_org_id.extend;
188 x_line_rec.carrier_id.extend;
189 x_line_rec.tracking_number.extend;
190 x_line_rec.received_quantity2.extend;
191 -- These are additional columns needed to be extended to prevent
192 -- failing of insert
193 x_line_rec.inspection_flag.extend;
194 x_line_rec.tp_attribute1.extend;
195 x_line_rec.tp_attribute10.extend;
196 x_line_rec.tp_attribute11.extend;
197 x_line_rec.tp_attribute12.extend;
198 x_line_rec.tp_attribute13.extend;
199 x_line_rec.tp_attribute14.extend;
200 x_line_rec.tp_attribute15.extend;
201 x_line_rec.tp_attribute2.extend;
202 x_line_rec.tp_attribute3.extend;
203 x_line_rec.tp_attribute4.extend;
204 x_line_rec.tp_attribute5.extend;
205 x_line_rec.tp_attribute6.extend;
206 x_line_rec.tp_attribute7.extend;
207 x_line_rec.tp_attribute8.extend;
208 x_line_rec.tp_attribute9.extend;
209 x_line_rec.TP_CONTEXT.extend;
210 x_line_rec.shipping_interfaced_flag.extend;
211 x_line_rec.shipping_eligible_flag.extend;
212 */
213 x_line_rec.line_id.extend;
214 --x_line_rec.drop_ship_flag.extend;
215 x_line_rec.closed_flag.extend;
216 x_line_rec.closed_code.extend;
217 x_line_rec.cancelled_flag.extend;
218 x_line_rec.source_code.extend;
219
220
221 -- extending the tables for storing the data from rcv_shipment_lines
222 x_line_rec.shipment_header_id.extend;
223 x_line_rec.shipment_num.extend;
224 x_line_rec.receipt_num.extend;
225 x_line_rec.bill_of_lading.extend;
226 x_line_rec.rcv_carrier_id.extend;
227 x_line_rec.expected_receipt_date.extend;
228 x_line_rec.shipped_date.extend;
229 x_line_rec.rcv_freight_terms_code.extend;
230 x_line_rec.num_of_containers.extend;
231 x_line_rec.rcv_gross_weight.extend;
232 x_line_rec.rcv_gross_weight_uom_code.extend;
233 x_line_rec.rcv_net_weight.extend;
234 x_line_rec.rcv_net_weight_uom_code.extend;
235 x_line_rec.rcv_tare_weight.extend;
236 x_line_rec.rcv_tare_weight_uom_code.extend;
237 x_line_rec.shipment_line_id.extend;
238 x_line_rec.rcv_inventory_item_id.extend;
239 x_line_rec.rcv_revision.extend;
240 x_line_rec.rcv_item_description.extend;
241 x_line_rec.packing_slip_number.extend;
242 x_line_rec.qty_rcv_exception_code.extend;
243 --x_line_rec.days_early_receipt_allowed.extend;
244 --x_line_rec.days_late_receipt_allowed.extend;
245 x_line_rec.receipt_days_exception_code.extend;
246 x_line_rec.enforce_ship_to_location_code.extend;
247 x_line_rec.shipping_details_updated_on.extend;
248
249 -- bug 3199983
250 x_line_rec.container_num.extend;
251 x_line_rec.truck_num.extend;
252 x_line_rec.lpn_id.extend;
253 x_line_rec.asn_type.extend;
254 -- bug 3199983
255
256 l_action_prms.caller := 'WSH_IB_UTIL';
257 WSH_BULK_PROCESS_PVT.Extend_tables (
258 p_line_rec => x_line_rec,
259 p_action_prms => l_action_prms,
260 x_table_count => l_tab_count,
261 x_additional_line_info_rec => l_additional_line_info_rec,
262 x_return_status => l_return_status);
263 --
264 -- Debug Statements
265 --
266 IF l_debug_on THEN
267 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling the API Extend_tables is ', l_return_status);
268 WSH_DEBUG_SV.log(l_module_name,'l_tab_count', l_tab_count);
269 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
270 END IF;
271 --
272 wsh_util_core.api_post_call(
273 p_return_status => l_return_status,
274 x_num_warnings => l_num_warnings,
275 x_num_errors => l_num_errors);
276
277 IF nvl( l_tab_count,0) = 0 THEN
278 l_tab_count := x_line_rec.po_shipment_line_id.count;
279 END IF;
280
281
282 IF l_debug_on THEN
283 WSH_DEBUG_SV.log(l_module_name,'count of x_line_rec',l_tab_count);
284 END IF;
285
286 IF ( p_prev_line_rec_index IS NULL ) THEN
287 --{
288 IF l_debug_on THEN
289 WSH_DEBUG_SV.logmsg(l_module_name,'Before initializing the attributes');
290 END IF;
291 x_line_rec.org_id(l_tab_count) := p_po_line_rec.org_id;
292 x_line_rec.header_id(l_tab_count) := p_po_line_rec.header_id;
293 x_line_rec.line_id(l_tab_count) := p_po_line_rec.line_id;
294 x_line_rec.po_shipment_line_id(l_tab_count) := p_po_line_location_id;
295 x_line_rec.source_blanket_reference_id(l_tab_count) := p_po_line_rec.source_blanket_reference_id;
296 x_line_rec.source_blanket_reference_num(l_tab_count) := p_po_line_rec.source_blanket_reference_num;
297 x_line_rec.source_header_number(l_tab_count) := p_po_line_rec.source_header_number;
298 x_line_rec.hold_code(l_tab_count) := p_po_line_rec.hold_code;
299 x_line_rec.vendor_id(l_tab_count) := p_po_line_rec.vendor_id;
300 x_line_rec.ship_from_site_id(l_tab_count) := p_po_line_rec.ship_from_site_id;
301 IF ( x_line_rec.source_blanket_reference_id(l_tab_count) IS NOT NULL) THEN
302 --{
303 open l_fr_term_fob_code_csr(p_po_line_rec.header_id,
304 p_po_line_rec.line_id,
305 p_po_line_location_id);
306 fetch l_fr_term_fob_code_csr into l_fob_point_code, l_fr_terms_code;
307 close l_fr_term_fob_code_csr;
308 x_line_rec.freight_terms_code(l_tab_count) := l_fr_terms_code;
309 x_line_rec.fob_point_code(l_tab_count) := l_fob_point_code;
310 --}
311 ELSE
312 --{
313 x_line_rec.freight_terms_code(l_tab_count) := p_po_line_rec.freight_terms_code;
314 x_line_rec.fob_point_code(l_tab_count) := p_po_line_rec.fob_point_code;
315 --}
316 END IF;
317 IF l_debug_on THEN
318 WSH_DEBUG_SV.logmsg(l_module_name,'Middle 1');
319 END IF;
320 x_line_rec.shipping_control(l_tab_count) := p_po_line_rec.shipping_control;
321 x_line_rec.po_revision(l_tab_count) := p_po_line_rec.po_revision;
322 x_line_rec.currency_code(l_tab_count) := p_po_line_rec.currency_code;
323 x_line_rec.release_revision(l_tab_count) := p_po_line_rec.release_revision;
324 x_line_rec.source_line_number(l_tab_count) := p_po_line_rec.source_line_number;
325 x_line_rec.supplier_item_num(l_tab_count) := p_po_line_rec.supplier_item_num;
326 x_line_rec.source_line_type_code(l_tab_count) := p_po_line_rec.source_line_type_code;
327 x_line_rec.hazard_class_id(l_tab_count) := p_po_line_rec.hazard_class_id;
328 IF l_debug_on THEN
329 WSH_DEBUG_SV.logmsg(l_module_name,'Middle 2');
330 END IF;
331 x_line_rec.inventory_item_id(l_tab_count) := p_po_line_rec.inventory_item_id;
332 x_line_rec.item_description(l_tab_count) := p_po_line_rec.item_description;
333 x_line_rec.revision(l_tab_count) := p_po_line_rec.revision;
334 x_line_rec.po_shipment_line_number(l_tab_count) := p_po_line_rec.po_shipment_line_number;
335 x_line_rec.country_of_origin(l_tab_count) := p_po_line_rec.country_of_origin;
336 x_line_rec.organization_id(l_tab_count) := p_po_line_rec.organization_id;
337 x_line_rec.ship_to_org_id(l_tab_count) := null;
338 --x_line_rec.ship_to_org_id(l_tab_count) := p_po_line_rec.organization_id;
339 x_line_rec.ship_to_location_id(l_tab_count) := p_po_line_rec.ship_to_location_id;
340 x_line_rec.ordered_quantity(l_tab_count) := p_po_line_rec.ordered_quantity;
341 x_line_rec.order_quantity_uom(l_tab_count) := p_po_line_rec.order_quantity_uom;
342 x_line_rec.unit_list_price(l_tab_count) := p_po_line_rec.unit_list_price;
343 --x_line_rec.status_code(l_tab_count) := p_po_line_rec.status_code;
344 IF l_debug_on THEN
345 WSH_DEBUG_SV.logmsg(l_module_name,'Middle 3');
346 END IF;
347 x_line_rec.preferred_grade(l_tab_count) := p_po_line_rec.preferred_grade;
348 x_line_rec.cancelled_quantity(l_tab_count) := p_po_line_rec.cancelled_quantity;
349 x_line_rec.ordered_quantity2(l_tab_count) := p_po_line_rec.ordered_quantity2;
350 x_line_rec.ordered_quantity_uom2(l_tab_count) := p_po_line_rec.ordered_quantity_uom2;
351 x_line_rec.ship_tolerance_above(l_tab_count) := p_po_line_rec.ship_tolerance_above;
352 x_line_rec.ship_tolerance_below(l_tab_count) := p_po_line_rec.ship_tolerance_below;
353 x_line_rec.request_date(l_tab_count) := p_po_line_rec.request_date;
354 x_line_rec.schedule_ship_date(l_tab_count) := p_po_line_rec.schedule_ship_date;
355 x_line_rec.qty_rcv_exception_code(l_tab_count) := p_po_line_rec.qty_rcv_exception_code;
356 x_line_rec.days_early_receipt_allowed(l_tab_count) := p_po_line_rec.days_early_receipt_allowed;
357 x_line_rec.days_late_receipt_allowed(l_tab_count) := p_po_line_rec.days_late_receipt_allowed;
358 x_line_rec.receipt_days_exception_code(l_tab_count) := p_po_line_rec.receipt_days_exception_code;
359 x_line_rec.enforce_ship_to_location_code(l_tab_count) := p_po_line_rec.enforce_ship_to_location_code;
360 x_line_rec.shipping_details_updated_on(l_tab_count) := p_po_line_rec.shipping_details_updated_on;
361 x_line_rec.carrier_id(l_tab_count) := p_po_line_rec.carrier_id;
362 x_line_rec.drop_ship_flag(l_tab_count) := p_po_line_rec.drop_ship_flag;
363 x_line_rec.closed_flag(l_tab_count) := p_po_line_rec.closed_flag;
364 x_line_rec.closed_code(l_tab_count) := p_po_line_rec.closed_code;
365 x_line_rec.cancelled_flag(l_tab_count) := p_po_line_rec.cancelled_flag;
366 x_line_rec.source_code(l_tab_count) := 'PO';
367 IF l_debug_on THEN
368 WSH_DEBUG_SV.logmsg(l_module_name,'Middle 4');
369 END IF;
370 x_line_rec.shipping_interfaced_flag(l_tab_count) := 'Y';
371 x_line_rec.shipping_eligible_flag(l_tab_count) := 'Y';
372 IF l_debug_on THEN
373 WSH_DEBUG_SV.logmsg(l_module_name,'Middle 5');
374 END IF;
375 -- bug 3151205
376 x_line_rec.source_header_type_id(l_tab_count) := p_po_line_rec.source_header_type_id;
377 x_line_rec.source_header_type_name(l_tab_count) := p_po_line_rec.source_header_type_name;
378 x_line_rec.net_weight(l_tab_count) := p_po_line_rec.net_weight;
379 x_line_rec.weight_uom_code(l_tab_count) := p_po_line_rec.weight_uom_code;
380 x_line_rec.volume(l_tab_count) := p_po_line_rec.volume;
381 x_line_rec.volume_uom_code(l_tab_count) := p_po_line_rec.volume_uom_code;
382 x_line_rec.cancelled_quantity2(l_tab_count) := p_po_line_rec.cancelled_quantity2;
383 IF l_debug_on THEN
384 WSH_DEBUG_SV.logmsg(l_module_name,'After assigning the values');
385 END IF;
386 IF (nvl(x_line_rec.drop_ship_flag(l_tab_count),'N') = 'Y') THEN
387 --{
388 get_drop_ship_info(
389 p_line_rec => x_line_rec,
390 p_index => l_tab_count,
391 x_return_status => l_return_status);
392 --
393 -- Debug Statements
394 --
395 IF l_debug_on THEN
396 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
397 END IF;
398 --
399 wsh_util_core.api_post_call(
400 p_return_status => l_return_status,
401 x_num_warnings => l_num_warnings,
402 x_num_errors => l_num_errors);
403 --}
404 END IF;
405 --}
406 ELSE
407 --{
408 IF l_debug_on THEN
409 WSH_DEBUG_SV.logmsg(l_module_name,'Before assigning the values');
410 END IF;
411 x_line_rec.org_id(l_tab_count) := x_line_rec.org_id(p_prev_line_rec_index);
412 x_line_rec.header_id(l_tab_count) := x_line_rec.header_id(p_prev_line_rec_index);
413 x_line_rec.line_id(l_tab_count) := x_line_rec.line_id(p_prev_line_rec_index);
414 x_line_rec.po_shipment_line_id(l_tab_count) := x_line_rec.po_shipment_line_id(p_prev_line_rec_index);
415 x_line_rec.source_blanket_reference_id(l_tab_count) := x_line_rec.source_blanket_reference_id(p_prev_line_rec_index);
416 x_line_rec.source_blanket_reference_num(l_tab_count) := x_line_rec.source_blanket_reference_num(p_prev_line_rec_index);
417 x_line_rec.source_header_number(l_tab_count) := x_line_rec.source_header_number(p_prev_line_rec_index);
418 x_line_rec.hold_code(l_tab_count) := x_line_rec.hold_code(p_prev_line_rec_index);
419 x_line_rec.vendor_id(l_tab_count) := x_line_rec.vendor_id(p_prev_line_rec_index);
420 x_line_rec.ship_from_site_id(l_tab_count) := x_line_rec.ship_from_site_id(p_prev_line_rec_index);
421 x_line_rec.freight_terms_code(l_tab_count) := x_line_rec.freight_terms_code(p_prev_line_rec_index);
422 x_line_rec.fob_point_code(l_tab_count) := x_line_rec.fob_point_code(p_prev_line_rec_index);
423 x_line_rec.shipping_control(l_tab_count) := x_line_rec.shipping_control(p_prev_line_rec_index);
424 x_line_rec.po_revision(l_tab_count) := x_line_rec.po_revision(p_prev_line_rec_index);
425 x_line_rec.currency_code(l_tab_count) := x_line_rec.currency_code(p_prev_line_rec_index);
426 x_line_rec.release_revision(l_tab_count) := x_line_rec.release_revision(p_prev_line_rec_index);
427 x_line_rec.source_line_number(l_tab_count) := x_line_rec.source_line_number(p_prev_line_rec_index);
428 x_line_rec.supplier_item_num(l_tab_count) := x_line_rec.supplier_item_num(p_prev_line_rec_index);
429 x_line_rec.source_line_type_code(l_tab_count) := x_line_rec.source_line_type_code(p_prev_line_rec_index);
430 x_line_rec.hazard_class_id(l_tab_count) := x_line_rec.hazard_class_id(p_prev_line_rec_index);
431 x_line_rec.inventory_item_id(l_tab_count) := x_line_rec.inventory_item_id(p_prev_line_rec_index);
432 x_line_rec.item_description(l_tab_count) := x_line_rec.item_description(p_prev_line_rec_index);
433 x_line_rec.revision(l_tab_count) := x_line_rec.revision(p_prev_line_rec_index);
434 x_line_rec.po_shipment_line_number(l_tab_count) := x_line_rec.po_shipment_line_number(p_prev_line_rec_index);
435 x_line_rec.country_of_origin(l_tab_count) := x_line_rec.country_of_origin(p_prev_line_rec_index);
436 x_line_rec.organization_id(l_tab_count) := x_line_rec.organization_id(p_prev_line_rec_index);
437 x_line_rec.ship_to_org_id(l_tab_count) := null;
438 --x_line_rec.ship_to_org_id(l_tab_count) := x_line_rec.ship_to_org_id(p_prev_line_rec_index);
439 x_line_rec.ship_to_location_id(l_tab_count) := x_line_rec.ship_to_location_id(p_prev_line_rec_index);
440 x_line_rec.ordered_quantity(l_tab_count) := x_line_rec.ordered_quantity(p_prev_line_rec_index);
441 x_line_rec.order_quantity_uom(l_tab_count) := x_line_rec.order_quantity_uom(p_prev_line_rec_index);
442 x_line_rec.unit_list_price(l_tab_count) := x_line_rec.unit_list_price(p_prev_line_rec_index);
443 --x_line_rec.status_code(l_tab_count) := x_line_rec.status_code(p_prev_line_rec_index);
444 x_line_rec.preferred_grade(l_tab_count) := x_line_rec.preferred_grade(p_prev_line_rec_index);
445 x_line_rec.cancelled_quantity(l_tab_count) := x_line_rec.cancelled_quantity(p_prev_line_rec_index);
446 x_line_rec.ordered_quantity2(l_tab_count) := x_line_rec.ordered_quantity2(p_prev_line_rec_index);
447 x_line_rec.ordered_quantity_uom2(l_tab_count) := x_line_rec.ordered_quantity_uom2(p_prev_line_rec_index);
448 x_line_rec.ship_tolerance_above(l_tab_count) := x_line_rec.ship_tolerance_above(p_prev_line_rec_index);
449 x_line_rec.ship_tolerance_below(l_tab_count) := x_line_rec.ship_tolerance_below(p_prev_line_rec_index);
450 x_line_rec.qty_rcv_exception_code(l_tab_count) := x_line_rec.qty_rcv_exception_code(p_prev_line_rec_index);
451 x_line_rec.request_date(l_tab_count) := x_line_rec.request_date(p_prev_line_rec_index);
452 x_line_rec.schedule_ship_date(l_tab_count) := x_line_rec.schedule_ship_date(p_prev_line_rec_index);
453 x_line_rec.days_early_receipt_allowed(l_tab_count) := x_line_rec.days_early_receipt_allowed(p_prev_line_rec_index);
454 x_line_rec.days_late_receipt_allowed(l_tab_count) := x_line_rec.days_late_receipt_allowed(p_prev_line_rec_index);
455 x_line_rec.receipt_days_exception_code(l_tab_count) := x_line_rec.receipt_days_exception_code(p_prev_line_rec_index);
456 x_line_rec.enforce_ship_to_location_code(l_tab_count) := x_line_rec.enforce_ship_to_location_code(p_prev_line_rec_index);
457 x_line_rec.shipping_details_updated_on(l_tab_count) := x_line_rec.shipping_details_updated_on(p_prev_line_rec_index);
458 x_line_rec.carrier_id(l_tab_count) := x_line_rec.carrier_id(p_prev_line_rec_index);
459 x_line_rec.drop_ship_flag(l_tab_count) := x_line_rec.drop_ship_flag(p_prev_line_rec_index);
460 x_line_rec.closed_flag(l_tab_count) := x_line_rec.closed_flag(p_prev_line_rec_index);
461 x_line_rec.closed_code(l_tab_count) := x_line_rec.closed_code(p_prev_line_rec_index);
462 x_line_rec.cancelled_flag(l_tab_count) := x_line_rec.cancelled_flag(p_prev_line_rec_index);
463 x_line_rec.source_code(l_tab_count) := 'PO';
464 -- bug 3151205
465 x_line_rec.source_header_type_id(l_tab_count) := x_line_rec.source_header_type_id(p_prev_line_rec_index);
466 x_line_rec.source_header_type_name(l_tab_count) := x_line_rec.source_header_type_name(p_prev_line_rec_index);
467 x_line_rec.net_weight(l_tab_count) := x_line_rec.net_weight(p_prev_line_rec_index);
468 x_line_rec.weight_uom_code(l_tab_count) := x_line_rec.weight_uom_code(p_prev_line_rec_index);
469 x_line_rec.volume(l_tab_count) := x_line_rec.volume(p_prev_line_rec_index);
470 x_line_rec.volume_uom_code(l_tab_count) := x_line_rec.volume_uom_code(p_prev_line_rec_index);
471 x_line_rec.cancelled_quantity2(l_tab_count) := p_po_line_rec.cancelled_quantity2;
472 IF l_debug_on THEN
473 WSH_DEBUG_SV.logmsg(l_module_name,'After assigning the values');
474 END IF;
475 --}
476 END IF;
477 --
478 IF l_num_errors > 0
479 THEN
480 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
481 ELSIF l_num_warnings > 0
482 THEN
483 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
484 ELSE
485 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
486 END IF;
487 --
488 --}
489 --
490 -- Debug Statements
491 --
492 IF l_debug_on THEN
493 WSH_DEBUG_SV.pop(l_module_name);
494 END IF;
495 --
496 EXCEPTION
497 --{
498 WHEN FND_API.G_EXC_ERROR THEN
499 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
500 --
501 -- Debug Statements
502 --
503 IF l_debug_on THEN
504 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
505 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
506 END IF;
507 --
508 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
509 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
510 --
511 -- Debug Statements
512 --
513 IF l_debug_on THEN
514 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
515 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
516 END IF;
517 --
518 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
519 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
520 --
521 -- Debug Statements
522 --
523 IF l_debug_on THEN
524 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
525 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
526 END IF;
527 --
528 WHEN OTHERS THEN
529 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
530 wsh_util_core.default_handler('WSH_INBOUND_UTIL_PKG.COPY_PO_REC_TO_LINE_REC');
531 --}
532 --
533 -- Debug Statements
534 --
535 IF l_debug_on THEN
536 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
537 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
538 END IF;
539 --
540 END copy_po_rec_to_line_rec;
541
542 --========================================================================
543 -- PROCEDURE : get_rcv_line_attribs This procedure derives the attributes
544 -- from rcv_fte_lines_v, rcv_fte_headers_v
545 -- based on the input p_shipment_line_id
546 -- updates x_line_rec with
547 -- these attributes.
548 --
549 -- PARAMETERS: p_shipment_line_id Shipment_line_id of rcv_shipment_lines
550 -- x_line_rec OE_WSH_BULK_GRP.line_rec_type
551 -- x_return_status Return status of the API.
552 --
553 -- COMMENT : This procedure derives the x_line_rec based on the inputs
554 -- p_po_line_location_id and p_rcv_shipment_line_id
555 -- The following is the flow of this procedure -
556 -- 1. We first check if a record already exists in the
557 -- x_line_rec before performing the query. If it exists
558 -- we pass the previous record's index to l_prev_line_rec_index
559 -- else we call PO_FTE_INTEGRATION_GRP.get_po_release_attributes
560 -- to get the attributes from PO
561 -- 2. Then we call the API copy_po_rec_to_line_rec
562 -- passing the appropriate inputs based on the step1.
563 -- 3. Then we check if the input parameter p_rcv_shipment_line_id.
564 -- If it is not null, then we call get_rcv_line_attribs to
565 -- obtain the attributes into x_line_rec from rcv tables as well.
566 --========================================================================
567 PROCEDURE get_rcv_line_attribs(
568 p_shipment_line_id IN NUMBER,
569 x_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
570 x_return_status OUT NOCOPY VARCHAR2)
571 IS
572 --{
573 cursor l_shipment_header_csr (p_shipment_header_id IN NUMBER) is
574 select SHIPMENT_NUM,
575 RECEIPT_NUM,
576 BILL_OF_LADING,
577 CARRIER_ID,
578 EXPECTED_RECEIPT_DATE,
579 SHIPPED_DATE,
580 PACKING_SLIP,
581 FREIGHT_TERMS_CODE,
582 WAYBILL_AIRBILL_NUMBER,
583 NUM_OF_CONTAINERS,
584 GROSS_WEIGHT,
585 GROSS_WEIGHT_UOM_CODE,
586 NET_WEIGHT,
587 NET_WEIGHT_UOM_CODE,
588 TARE_WEIGHT,
589 TARE_WEIGHT_UOM_CODE,
590 asn_type
591 from rcv_fte_headers_v
592 where shipment_header_id = p_shipment_header_id;
593
594 cursor l_shipment_line_csr (p_shipment_line_id IN NUMBER) is
595 select shipment_line_id,
596 item_id,
597 item_revision,
598 item_description,
599 quantity_shipped,
600 quantity_received,
601 packing_slip,
602 secondary_quantity_shipped,
603 secondary_quantity_received,
604 container_num,
605 truck_num,
606 asn_lpn_id,
607 shipment_header_id
608 from rcv_fte_lines_v
609 where shipment_line_id = p_shipment_line_id;
610
611 l_line_rec_count NUMBER;
612 l_packing_slip_num VARCHAR2(32767);
613 --}
614 --
615 l_debug_on BOOLEAN;
616 --
617 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_RCV_LINE_ATTRIBS';
618 --
619 BEGIN
620 --{
621 --
622 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
623 --
624 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
625 --
626 IF l_debug_on IS NULL
627 THEN
628 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
629 END IF;
630 --
631 --
632 -- Debug Statements
633 --
634 IF l_debug_on THEN
635 WSH_DEBUG_SV.push(l_module_name);
636 --
637 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_LINE_ID',P_SHIPMENT_LINE_ID);
638 END IF;
639 --
640 l_line_rec_count := x_line_rec.po_shipment_line_id.count;
641
642 open l_shipment_line_csr(p_shipment_line_id);
643 fetch l_shipment_line_csr into x_line_rec.shipment_line_id(l_line_rec_count),
644 x_line_rec.rcv_inventory_item_id(l_line_rec_count),
645 x_line_rec.rcv_revision(l_line_rec_count),
646 x_line_rec.rcv_item_description(l_line_rec_count),
647 x_line_rec.shipped_quantity(l_line_rec_count),
648 x_line_rec.received_quantity(l_line_rec_count),
649 x_line_rec.packing_slip_number(l_line_rec_count),
650 x_line_rec.shipped_quantity2(l_line_rec_count),
651 x_line_rec.received_quantity2(l_line_rec_count),
652 x_line_rec.container_num(l_line_rec_count),
653 x_line_rec.truck_num(l_line_rec_count),
654 x_line_rec.lpn_id(l_line_rec_count),
655 x_line_rec.shipment_header_id(l_line_rec_count);
656 close l_shipment_line_csr;
657
658 IF l_debug_on THEN
659 WSH_DEBUG_SV.logmsg(l_module_name,'After fetching the shipment line');
660 END IF;
661 open l_shipment_header_csr(x_line_rec.shipment_header_id(l_line_rec_count));
662 fetch l_shipment_header_csr into x_line_rec.shipment_num(l_line_rec_count),
663 x_line_rec.receipt_num(l_line_rec_count),
664 x_line_rec.bill_of_lading(l_line_rec_count),
665 x_line_rec.rcv_carrier_id(l_line_rec_count),
666 x_line_rec.expected_receipt_date(l_line_rec_count),
667 x_line_rec.shipped_date(l_line_rec_count),
668 l_packing_slip_num,
669 x_line_rec.rcv_freight_terms_code(l_line_rec_count),
670 x_line_rec.tracking_number(l_line_rec_count),
671 x_line_rec.num_of_containers(l_line_rec_count),
672 x_line_rec.rcv_gross_weight(l_line_rec_count),
673 x_line_rec.rcv_gross_weight_uom_code(l_line_rec_count),
674 x_line_rec.rcv_net_weight(l_line_rec_count),
675 x_line_rec.rcv_net_weight_uom_code(l_line_rec_count),
676 x_line_rec.rcv_tare_weight(l_line_rec_count),
677 x_line_rec.rcv_tare_weight_uom_code(l_line_rec_count),
678 x_line_rec.asn_type(l_line_rec_count);
679 close l_shipment_header_csr;
680
681 x_line_rec.packing_slip_number(l_line_rec_count) := nvl(x_line_rec.packing_slip_number(l_line_rec_count), l_packing_slip_num);
682 --}
683 --
684 -- Debug Statements
685 --
686 IF l_debug_on THEN
687 WSH_DEBUG_SV.pop(l_module_name);
688 END IF;
689 --
690 EXCEPTION
691 --{
692 WHEN FND_API.G_EXC_ERROR THEN
693 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
694 --
695 -- Debug Statements
696 --
697 IF l_debug_on THEN
698 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
699 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
700 END IF;
701 --
702 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
703 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
704 --
705 -- Debug Statements
706 --
707 IF l_debug_on THEN
708 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
709 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
710 END IF;
711 --
712 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
713 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
714 --
715 -- Debug Statements
716 --
717 IF l_debug_on THEN
718 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
719 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
720 END IF;
721 --
722 WHEN OTHERS THEN
723 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
724 wsh_util_core.default_handler('WSH_INBOUND_UTIL_PKG.GET_RCV_LINE_ATTRIBS');
725 --}
726 --
727 -- Debug Statements
728 --
729 IF l_debug_on THEN
730 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
731 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
732 END IF;
733 --
734 END get_rcv_line_attribs;
735 --========================================================================
736 -- PROCEDURE : get_po_rcv_attributes This procedure derives the
737 -- x_line_rec based on the inputs
738 -- p_po_line_location_id and
739 -- p_rcv_shipment_line_id.
740 --
741 -- PARAMETERS: p_po_line_location_id po_line_location_id of
742 -- po_line_locations_all
743 -- p_rcv_shipment_line_id shipment_line_id of rcv_shipment_lines
744 -- x_line_rec Out parameter of type
745 -- OE_WSH_BULK_GRP.line_rec_type
746 -- x_return_status Return status of the API.
747 --
748 -- COMMENT : This procedure derives the x_line_rec based on the inputs
749 -- p_po_line_location_id and p_rcv_shipment_line_id
750 --========================================================================
751
752 PROCEDURE get_po_rcv_attributes(
753 p_po_line_location_id IN NUMBER,
754 p_rcv_shipment_line_id IN NUMBER DEFAULT NULL,
755 x_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
756 x_return_status OUT NOCOPY VARCHAR2)
757
758 IS
759 --{
760 l_po_line_rec PO_FTE_INTEGRATION_GRP.po_release_rec_type;
761 l_line_rec_count NUMBER;
762 l_get_attr_from_po BOOLEAN := FALSE;
763 l_prev_line_rec_index NUMBER;
764 l_return_status VARCHAR2(1);
765 l_msg_count NUMBER;
766 l_msg_data VARCHAR2(32767);
767 l_num_warnings NUMBER;
768 l_num_errors NUMBER;
769 --}
770 --
771 l_debug_on BOOLEAN;
772 --
773 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_PO_RCV_ATTRIBUTES';
774 --
775 BEGIN
776 --{
777
778 --
779 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
780 --
781 IF l_debug_on IS NULL
782 THEN
783 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
784 END IF;
785 --
786 --
787 -- Debug Statements
788 --
789 IF l_debug_on THEN
790 WSH_DEBUG_SV.push(l_module_name);
791 --
792 WSH_DEBUG_SV.log(l_module_name,'P_PO_LINE_LOCATION_ID',P_PO_LINE_LOCATION_ID);
793 WSH_DEBUG_SV.log(l_module_name,'P_RCV_SHIPMENT_LINE_ID',P_RCV_SHIPMENT_LINE_ID);
794 END IF;
795 --
796 x_return_status := wsh_util_core.g_ret_sts_success;
797 --
798 l_line_rec_count := x_line_rec.po_shipment_line_id.count;
799 IF l_line_rec_count < 1 THEN -- This means we have to call PO's API
800 l_get_attr_from_po := TRUE;
801 END IF;
802 IF l_line_rec_count > 0 THEN
803 --{
804 IF (x_line_rec.po_shipment_line_id(l_line_rec_count) <> p_po_line_location_id) THEN
805 --{
806 l_get_attr_from_po := TRUE;
807 --}
808 ELSE
809 --{
810 l_prev_line_rec_index := x_line_rec.po_shipment_line_id.count;
811 --}
812 END IF;
813 --}
814 END IF;
815
816 -- call PO's API to obtain all the attributes for the po_line_location_id
817 IF l_get_attr_from_po THEN
818 --{
819
820 IF (PO_CODE_RELEASE_GRP.Current_Release >= PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J) THEN
821 --{
822 --
823 -- Debug Statements
824 --
825 IF l_debug_on THEN
826 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit PO_FTE_INTEGRATION_GRP.GET_PO_RELEASE_ATTRIBUTES',WSH_DEBUG_SV.C_PROC_LEVEL);
827 END IF;
828 --
829 PO_FTE_INTEGRATION_GRP.get_po_release_attributes(
830 p_api_version => 1.0,
831 x_return_status => l_return_status,
832 x_msg_count => l_msg_count,
833 x_msg_data => l_msg_data,
834 p_line_location_id => p_po_line_location_id,
835 x_po_releases_attributes => l_po_line_rec);
836 --
837 -- Debug Statements
838 --
839 IF l_debug_on THEN
840 WSH_DEBUG_SV.log(l_module_name,'l_return_status is',l_return_status);
841 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
842 END IF;
843 --
844 wsh_util_core.api_post_call(
845 p_return_status => l_return_status,
846 x_num_warnings => l_num_warnings,
847 x_num_errors => l_num_errors);
848 --}
849 ELSE
850 --{
851 IF l_debug_on THEN
852 WSH_DEBUG_SV.logmsg(l_module_name,'PO Release less than 11.5.10');
853 END IF;
854 RAISE FND_API.G_EXC_ERROR;
855 --}
856 END IF;
857 --}
858 END IF;
859
860 copy_po_rec_to_line_rec(
861 p_po_line_rec => l_po_line_rec,
862 x_line_rec => x_line_rec,
863 p_po_line_location_id => p_po_line_location_id,
864 p_prev_line_rec_index => l_prev_line_rec_index,
865 x_return_status => l_return_status);
866 --
867 -- Debug Statements
868 --
869 IF l_debug_on THEN
870 WSH_DEBUG_SV.log(l_module_name,'l_return_status is',l_return_status);
871 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
872 END IF;
873 --
874 wsh_util_core.api_post_call(
875 p_return_status => l_return_status,
876 x_num_warnings => l_num_warnings,
877 x_num_errors => l_num_errors);
878
879 --x_line_rec.po_shipment_line_id
880
881 IF p_rcv_shipment_line_id IS NOT NULL THEN
882 --{
883 get_rcv_line_attribs(
884 p_shipment_line_id => p_rcv_shipment_line_id,
885 x_line_rec => x_line_rec,
886 x_return_status => l_return_status);
887 --
888 -- Debug Statements
889 --
890 IF l_debug_on THEN
891 WSH_DEBUG_SV.log(l_module_name,'l_return_status is',l_return_status);
892 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
893 END IF;
894 --
895 wsh_util_core.api_post_call(
896 p_return_status => l_return_status,
897 x_num_warnings => l_num_warnings,
898 x_num_errors => l_num_errors);
899 --}
900 END IF;
901 --
902 IF l_num_errors > 0
903 THEN
904 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
905 ELSIF l_num_warnings > 0
906 THEN
907 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
908 ELSE
909 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
910 END IF;
911 --
912 --}
913 --
914 -- Debug Statements
915 --
916 IF l_debug_on THEN
917 WSH_DEBUG_SV.pop(l_module_name);
918 END IF;
919 --
920 EXCEPTION
921 --{
922 WHEN FND_API.G_EXC_ERROR THEN
923 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
924 --
925 -- Debug Statements
926 --
927 IF l_debug_on THEN
928 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
929 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
930 END IF;
931 --
932 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
933 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
934 --
935 -- Debug Statements
936 --
937 IF l_debug_on THEN
938 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
939 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
940 END IF;
941 --
942 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
943 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
944 --
945 -- Debug Statements
946 --
947 IF l_debug_on THEN
948 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
949 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
950 END IF;
951 --
952 WHEN OTHERS THEN
953 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
954 wsh_util_core.default_handler('WSH_INBOUND_UTIL_PKG.GET_PO_RCV_ATTRIBUTES');
955 --}
956 --
957 -- Debug Statements
958 --
959 IF l_debug_on THEN
960 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
961 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
962 END IF;
963 --
964 END get_po_rcv_attributes;
965
966 -- Start of comments
967 -- API name : GET_DROP_SHIP_INFO
968 -- Type : Public
969 -- Pre-reqs : None.
970 -- Function : This API derives the value for all the drop ship fields
971 -- and populates the same into the p_line_rec sructure.
972 -- Parameters :
973 -- IN:
974 -- p_index IN NUMBER
975 -- The index of the record of the other i/p parameter p_line_rec
976 -- on which the API has to perform its function.
977 -- IN OUT:
978 -- p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type
979 -- A record structure which contains all the attributes required
980 -- for PO Integration for inbound shipments.
981 -- OUT:
982 -- x_return_status OUT NOCOPY VARCHAR2
983 -- Version : 1.0
984 -- Previous version 1.0
985 -- Initial version 1.0
986 -- End of comments
987
988
989 PROCEDURE get_drop_ship_info(
990 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
991 p_index IN NUMBER,
992 x_return_status OUT NOCOPY VARCHAR2
993 ) IS
994
995
996 --Cursor to get all the field/column values that are related to drop ship in the OE tables.
997 CURSOR c_drop_ship(index_num number) IS
998 SELECT
999 oh.sold_to_org_id,
1000 oh.sold_to_contact_id,
1001 oh.ship_to_contact_id,
1002 oh.ship_to_org_id,
1003 oh.deliver_to_contact_id,
1004 oh.deliver_to_org_id,
1005 ol.intmed_ship_to_contact_id,
1006 ol.shipping_method_code,
1007 ol.cust_production_seq_num,
1008 ol.customer_dock_code,
1009 ol.cust_model_serial_number,
1010 ol.customer_job,
1011 ol.customer_production_line,
1012 ol.ship_model_complete_flag,
1013 ol.top_model_line_id,
1014 oh.cust_po_number,
1015 ol.ato_line_id,
1016 oh.shipping_instructions,
1017 ol.packing_instructions,
1018 decode(item_identifier_type,
1019 'CUST',
1020 ol.ordered_item_id,
1021 NULL
1022 ) customer_item_id,
1023 ol.intmed_ship_to_org_id
1024 FROM
1025 oe_order_headers_all oh,
1026 oe_order_lines_all ol,
1027 oe_drop_ship_sources od
1028 WHERE
1029 od.header_id = oh.header_id AND
1030 od.line_id = ol.line_id AND
1031 oh.header_id = ol.header_id AND
1032 od.line_location_id = p_line_rec.po_shipment_line_id(index_num);
1033
1034
1035 l_return_status VARCHAR2(1);
1036 l_api_name CONSTANT VARCHAR2(30) := 'get_drop_ship_info';
1037 l_num_warnings NUMBER := 0;
1038 l_num_errors NUMBER := 0;
1039
1040 INVALID_DROP_SHIP_INFO EXCEPTION;
1041 --
1042 l_debug_on BOOLEAN;
1043 --
1044 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DROP_SHIP_INFO';
1045 --
1046 BEGIN
1047
1048 --
1049 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1050 --
1051 IF l_debug_on IS NULL
1052 THEN
1053 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1054 END IF;
1055 --
1056 --
1057 -- Debug Statements
1058 --
1059 IF l_debug_on THEN
1060 WSH_DEBUG_SV.push(l_module_name);
1061 WSH_DEBUG_SV.log(l_module_name,'P_INDEX',P_INDEX);
1062 WSH_DEBUG_SV.log(l_module_name,'p_line_rec.po_shipment_line_id(p_index)',p_line_rec.po_shipment_line_id(p_index));
1063 END IF;
1064 --
1065 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1066
1067 OPEN c_drop_ship(p_index);
1068 FETCH c_drop_ship
1069 INTO
1070 p_line_rec.sold_to_org_id(p_index),
1071 p_line_rec.sold_to_contact_id(p_index),
1072 p_line_rec.ship_to_contact_id(p_index),
1073 p_line_rec.ship_to_org_id(p_index),
1074 p_line_rec.deliver_to_contact_id(p_index),
1075 p_line_rec.deliver_to_org_id(p_index),
1076 p_line_rec.intermed_ship_to_contact_id(p_index),
1077 p_line_rec.shipping_method_code(p_index),
1078 p_line_rec.cust_production_seq_num(p_index),
1079 p_line_rec.customer_dock_code(p_index),
1080 p_line_rec.cust_model_serial_number(p_index),
1081 p_line_rec.customer_job(p_index),
1082 p_line_rec.customer_production_line(p_index),
1083 p_line_rec.ship_model_complete_flag(p_index),
1084 p_line_rec.top_model_line_id(p_index),
1085 p_line_rec.cust_po_number(p_index),
1086 p_line_rec.ato_line_id(p_index),
1087 p_line_rec.shipping_instructions(p_index),
1088 p_line_rec.packing_instructions(p_index),
1089 p_line_rec.customer_item_id(p_index),
1090 p_line_rec.intermed_ship_to_org_id(p_index);
1091
1092 IF (c_drop_ship%NOTFOUND) THEN
1093 CLOSE c_drop_ship;
1094 RAISE INVALID_DROP_SHIP_INFO;
1095 END IF;
1096
1097 CLOSE c_drop_ship;
1098
1099
1100
1101 IF l_debug_on THEN
1102 WSH_DEBUG_SV.log(l_module_name,' p_line_rec.deliver_to_org_id(p_index)', p_line_rec.deliver_to_org_id(p_index));
1103 WSH_DEBUG_SV.log(l_module_name,'p_line_rec.intermed_ship_to_org_id(p_index)',p_line_rec.intermed_ship_to_org_id(p_index));
1104 WSH_DEBUG_SV.log(l_module_name,'p_line_rec.ship_to_location_id(p_index)',p_line_rec.ship_to_location_id(p_index));
1105 END IF;
1106
1107 --Deriving deliver_to_location from ship_to_location_id or deliver_to_org_id
1108 IF (p_line_rec.deliver_to_org_id(p_index) IS NOT NULL) THEN
1109 --
1110 -- Debug Statements
1111 --
1112 IF l_debug_on THEN
1113 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_LOCATION_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
1114 END IF;
1115 --
1116 --Call API WSH_UTIL_CORE.GET_LOCATION_ID to get the Deliver_to_location_id.
1117 WSH_UTIL_CORE.GET_LOCATION_ID(
1118 p_mode => 'CUSTOMER SITE',
1119 p_source_id => p_line_rec.deliver_to_org_id(p_index),
1120 x_location_id => p_line_rec.deliver_to_location_id(p_index),
1121 x_api_status => l_return_status);
1122
1123 --
1124 -- Debug Statements
1125 --
1126 IF l_debug_on THEN
1127 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1128 END IF;
1129 --
1130 wsh_util_core.api_post_call(
1131 p_return_status => l_return_status,
1132 x_num_warnings => l_num_warnings,
1133 x_num_errors => l_num_errors);
1134
1135 --use the ship_to_lcation_id as the deliver_to_location_id
1136 ELSE
1137 p_line_rec.deliver_to_location_id(p_index) := p_line_rec.ship_to_location_id(p_index);
1138 END IF;
1139
1140
1141 --Deriving intmed_ship_to_location from ship_to_org_id
1142 IF (p_line_rec.intermed_ship_to_org_id(p_index) IS NOT NULL) THEN
1143 --
1144 -- Debug Statements
1145 --
1146 IF l_debug_on THEN
1147 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_LOCATION_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
1148 END IF;
1149 --
1150 --Call API WSH_UTIL_CORE.GET_LOCATION_ID to get the intmed_ship_to_location_id.
1151 WSH_UTIL_CORE.GET_LOCATION_ID(
1152 p_mode => 'CUSTOMER SITE',
1153 p_source_id => p_line_rec.intermed_ship_to_org_id(p_index),
1154 x_location_id => p_line_rec.intmed_ship_to_location_id(p_index),
1155 x_api_status => l_return_status);
1156
1157 --
1158 -- Debug Statements
1159 --
1160 IF l_debug_on THEN
1161 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1162 END IF;
1163 --
1164 wsh_util_core.api_post_call(
1165 p_return_status => l_return_status,
1166 x_num_warnings => l_num_warnings,
1167 x_num_errors => l_num_errors);
1168 END IF;
1169
1170 --
1171 IF l_num_errors > 0
1172 THEN
1173 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1174 ELSIF l_num_warnings > 0
1175 THEN
1176 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1177 ELSE
1178 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1179 END IF;
1180 --
1181 --
1182 -- Debug Statements
1183 --
1184 IF l_debug_on THEN
1185 WSH_DEBUG_SV.pop(l_module_name);
1186 END IF;
1187 --
1188 EXCEPTION
1189 WHEN FND_API.G_EXC_ERROR THEN
1190 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1191 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1192
1193 --
1194 -- Debug Statements
1195 --
1196 IF l_debug_on THEN
1197 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1198 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1199 END IF;
1200 --
1201 WHEN INVALID_DROP_SHIP_INFO THEN
1202 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1203 FND_MESSAGE.SET_NAME('WSH','WSH_IB_DERIVE_DROPSHP_FAILED');
1204 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1205 --
1206 -- Debug Statements
1207 --
1208 IF l_debug_on THEN
1209 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_DROP_SHIP_INFO exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1210 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_DROP_SHIP_INFO');
1211 END IF;
1212 --
1213 WHEN OTHERS THEN
1214 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1215 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_BULK_PROCESS_PVT.get_drop_ship_info',l_module_name);
1216 --
1217 -- Debug Statements
1218 --
1219 IF l_debug_on THEN
1220 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1221 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1222 END IF;
1223 --
1224 END get_drop_ship_info;
1225
1226
1227
1228 --
1229 --========================================================================
1230 -- PROCEDURE : split_inbound_delivery
1231 --
1232 -- PARAMETERS: p_delivery_detail_id_tbl Table of delivery lines to be split from original delivery
1233 -- p_delivery_id Original Delivery ID
1234 -- x_delivery_id New Delivery ID
1235 -- x_return_status Return status of the API
1236 --
1237 -- COMMENT : This procedure is called only from the Inbound ASN/Receipt
1238 -- integration to split an existing delivery.
1239 -- It performs the following steps:
1240 -- 01. Validate that input delivery id is not null and is a valid delivery.
1241 -- 02. Validate that input table of lines contain at least one record.
1242 -- 03. If input delivery is outbound, return with error.
1243 -- 04. Clone the original delivery
1244 -- 05. Assign input lines to new delivery
1245 -- 06. Update Freight terms for new and original delivery
1246 -- 07. Update dates for new and original delivery
1247 --
1248 --========================================================================
1249 --
1250 PROCEDURE split_inbound_delivery
1251 (
1252 p_delivery_detail_id_tbl IN wsh_util_core.id_tab_type,
1253 p_delivery_id IN NUMBER,
1254 x_delivery_id IN OUT NOCOPY NUMBER,
1255 x_return_status OUT NOCOPY VARCHAR2,
1256 p_caller IN VARCHAR2 DEFAULT 'WSH_ASN_RECEIPT'
1257 )
1258 IS
1259 CURSOR dlvy_csr (p_delivery_id IN NUMBER)
1260 IS
1261 SELECT organization_id,
1262 nvl(shipment_direction,'O') shipment_direction,
1263 name,
1264 initial_pickup_location_id,
1265 status_code
1266 FROM wsh_new_deliveries wnd
1267 WHERE delivery_id = p_delivery_id;
1268 --
1269 --
1270 l_dlvy_rec dlvy_csr%ROWTYPE;
1271 --
1272 CURSOR wdd_csr(p_delivery_detail_id IN NUMBER)
1273 IS
1274 SELECT lpn_id,
1275 tracking_number,
1276 container_name
1277 FROM wsh_delivery_details
1278 WHERE delivery_detail_id = p_delivery_detail_id;
1279 --
1280 l_lpn_id NUMBER;
1281 l_lpn_name VARCHAR2(50);
1282 l_waybill VARCHAR2(30);
1283 --
1284 --
1285 CURSOR wda_csr(p_delivery_id IN NUMBER)
1286 IS
1287 SELECT wdd.delivery_detail_id,
1288 wda.parent_delivery_detail_id
1289 FROM wsh_delivery_details wdd,
1290 wsh_delivery_assignments wda,
1291 wsh_wms_sync_tmp wwst
1292 WHERE wwst.delivery_id = p_delivery_id
1293 AND nvl(wda.type,'S') IN ('S','O')
1294 AND wdd.delivery_detail_id = wda.delivery_detail_id
1295 AND wwst.operation_type='VENDOR_MRG'
1296 AND wwst.temp_col IS NULL
1297 AND wdd.delivery_detail_id = wwst.delivery_detail_id
1298 AND wda.parent_delivery_detail_id IS NOT NULL
1299 ORDER BY wda.parent_delivery_detail_id;
1300 --
1301 l_wdd_id_tbl wsh_util_core.id_tab_type;
1302 l_parent_wdd_id_tbl wsh_util_core.id_tab_type;
1303 l_tmp_wdd_id_tbl wsh_util_core.id_tab_type;
1304 l_index NUMBER;
1305 l_parent_wdd_id NUMBER;
1306 l_prev_parent_wdd_id NUMBER;
1307 l_new_parent_wdd_id NUMBER;
1308 l_count NUMBER;
1309 l_txn_type VARCHAR2(20);
1310 l_temp_tbl wsh_util_core.id_tab_type;
1311 --
1312 l_num_warnings NUMBER := 0;
1313 l_num_errors NUMBER := 0;
1314 l_return_status VARCHAR2(10);
1315 --
1316 l_delivery_id NUMBER := NULL;
1317 l_rowid VARCHAR2(32767);
1318
1319 l_query_count NUMBER;
1320 --
1321 l_delivery_rec WSH_NEW_DELIVERIES_PVT.delivery_rec_type;
1322 l_leg_id_tbl wsh_util_core.id_tab_type;
1323 l_line_tbl wsh_util_core.id_tab_type;
1324 l_entity_ids wsh_util_core.id_tab_type;
1325 l_dlvy_freight_terms_code VARCHAR2(30);
1326 --
1327 l_debug_on BOOLEAN;
1328 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'split_inbound_delivery';
1329 --
1330 l_current_parent_wdd_id NUMBER;
1331 --
1332 BEGIN
1333 SAVEPOINT split_inbound_delivery_sp;
1334 --
1335 l_debug_on := wsh_debug_interface.g_debug;
1336 --
1337 IF l_debug_on IS NULL THEN
1338 l_debug_on := wsh_debug_sv.is_debug_enabled;
1339 END IF;
1340 --
1341 IF l_debug_on THEN
1342 wsh_debug_sv.push(l_module_name);
1343 --
1344 wsh_debug_sv.LOG(l_module_name, 'p_delivery_id', p_delivery_id);
1345 wsh_debug_sv.log(l_module_name, 'p_delivery_detail_id_tbl.COUNT', p_delivery_detail_id_tbl.COUNT);
1346 END IF;
1347 --
1348 x_return_status := wsh_util_core.g_ret_sts_success;
1349 --
1350 --
1351 IF p_delivery_id IS NULL
1352 THEN
1353 --{
1354 --
1355 -- p_delivery_id is mandatory
1356 --
1357 FND_MESSAGE.SET_NAME('WSH', 'WSH_REQUIRED_FIELD_NULL');
1358 FND_MESSAGE.SET_TOKEN('FIELD_NAME', 'p_delivery_id');
1359 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
1360 --
1361 RAISE FND_API.G_EXC_ERROR;
1362 --}
1363 END IF;
1364 --
1365 IF p_delivery_detail_id_tbl.count = 0
1366 THEN
1367 --{
1368 --
1369 -- p_delivery_detail_id_tbl should have at least one record
1370 --
1371 FND_MESSAGE.SET_NAME('WSH', 'WSH_EMPTY_TABLE_ERROR');
1372 FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'p_delivery_detail_id_tbl');
1373 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
1374 --
1375 RAISE FND_API.G_EXC_ERROR;
1376 --}
1377 END IF;
1378 --
1379
1380 --
1381 OPEN dlvy_csr(p_delivery_id);
1382 FETCH dlvy_csr INTO l_dlvy_rec;
1383 CLOSE dlvy_csr;
1384 --
1385 IF l_dlvy_rec.initial_pickup_location_id IS NULL
1386 THEN
1387 --{
1388 FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_NOT_EXIST');
1389 FND_MESSAGE.SET_TOKEN('DELIVERY_ID', p_delivery_id);
1390 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
1391 --
1392 RAISE FND_API.G_EXC_ERROR;
1393 --}
1394 END IF;
1395 --
1396 --
1397 IF l_dlvy_rec.shipment_direction IN ('O','IO')
1398 THEN
1399 --{
1400 --
1401 -- Invalid operation for outbound delivery
1402 --
1403 FND_MESSAGE.SET_NAME('WSH','WSH_NOT_IB_DLVY_ERROR');
1404 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', l_dlvy_rec.name);
1405 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
1406 --
1407 RAISE FND_API.G_EXC_ERROR;
1408 --}
1409 END IF;
1410 --
1411 --
1412 --
1413 IF x_delivery_id IS NULL THEN
1414 l_delivery_rec.wv_frozen_flag := 'N';
1415 l_delivery_rec.routing_response_id := FND_API.G_MISS_NUM ;
1416
1417 IF l_debug_on THEN
1418 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.clone',WSH_DEBUG_SV.C_PROC_LEVEL);
1419 END IF;
1420 --
1421 -- Clone the input delivery
1422 --
1423 WSH_NEW_DELIVERIES_PVT.clone
1424 (
1425 p_delivery_rec => l_delivery_rec,
1426 p_delivery_id => p_delivery_id,
1427 p_copy_legs => 'Y',
1428 x_delivery_id => l_delivery_id,
1429 x_rowid => l_rowid,
1430 x_leg_id_tab => l_leg_id_tbl,
1431 x_return_status => l_return_Status
1432 ) ;
1433 --
1434 wsh_util_core.api_post_call
1435 (
1436 p_return_status => l_return_status,
1437 x_num_warnings => l_num_warnings,
1438 x_num_errors => l_num_errors
1439 );
1440 --
1441 x_delivery_id := l_delivery_id;
1442 --
1443 ELSE
1444 l_delivery_id := x_delivery_id;
1445 --
1446 END IF;
1447 --
1448 --
1449 -- Assign input lines to new delivery.
1450 --
1451 FORALL i IN p_delivery_detail_id_tbl.FIRST..p_delivery_detail_id_tbl.LAST
1452 UPDATE wsh_delivery_assignments_v
1453 SET delivery_id = l_delivery_id,
1454 last_update_date = SYSDATE,
1455 last_updated_by = FND_GLOBAL.USER_ID,
1456 last_update_login = FND_GLOBAL.LOGIN_ID
1457 WHERE delivery_detail_id = p_delivery_detail_id_tbl(i);
1458 --
1459 --
1460 IF SQL%ROWCOUNT <> p_delivery_detail_id_tbl.COUNT
1461 THEN
1462 --{
1463 RAISE NO_DATA_FOUND; ---add message later.
1464 --}
1465 END IF;
1466 --
1467 --
1468 IF p_caller = 'WSH_VENDOR_MERGE' THEN
1469 --{
1470 OPEN wda_csr(p_delivery_id => p_delivery_id);
1471 FETCH wda_csr BULK COLLECT INTO l_wdd_id_tbl, l_parent_wdd_id_tbl;
1472 CLOSE wda_csr;
1473 --
1474 IF l_debug_on THEN
1475 wsh_debug_sv.log(l_module_name, 'l_wdd_id_tbl.COUNT', l_wdd_id_tbl.COUNT);
1476 wsh_debug_sv.log(l_module_name, 'l_parent_wdd_id_tbl.COUNT', l_parent_wdd_id_tbl.COUNT);
1477 END IF;
1478 --
1479 l_index := l_parent_wdd_id_tbl.FIRST;
1480 l_count := 1;
1481 --
1482 IF l_dlvy_rec.status_code ='IT' THEN
1483 l_txn_type := 'ASN';
1484 ELSE
1485 l_txn_type:='RECEIPT';
1486 END IF;
1487 --
1488 IF l_debug_on THEN
1489 wsh_debug_sv.log(l_module_name, 'l_txn_type', l_txn_type);
1490 END IF;
1491 --
1492 IF l_index IS NOT NULL THEN
1493 --{
1494 WHILE TRUE -- will exit when l_index IS NULL
1495 --
1496 Loop
1497 --{
1498 l_current_parent_wdd_id := l_parent_wdd_id_tbl(nvl(l_index,l_parent_wdd_id_tbl.LAST));
1499 --
1500 IF l_debug_on THEN
1501 wsh_debug_sv.logmsg(l_module_name, '---------------------------------');
1502 wsh_debug_sv.log(l_module_name, 'l_index', l_index);
1503 wsh_debug_sv.log(l_module_name, 'l_prev_parent_wdd_id', l_prev_parent_wdd_id);
1504 wsh_debug_sv.log(l_module_name, 'l_current_parent_wdd_id', l_current_parent_wdd_id);
1505 END IF;
1506 --
1507 IF l_current_parent_wdd_id <> l_prev_parent_wdd_id or l_index IS NULL
1508 THEN
1509 --{
1510 l_parent_wdd_id := l_prev_parent_wdd_id;
1511 --
1512 IF l_debug_on THEN
1513 wsh_debug_sv.logmsg(l_module_name, '-----------------------------');
1514 wsh_debug_sv.log(l_module_name, 'Processing LPN', l_parent_wdd_id);
1515 wsh_debug_sv.logmsg(l_module_name, '------------------------------');
1516 END IF;
1517 --
1518 OPEN wdd_csr(p_delivery_detail_id =>l_parent_wdd_id);
1519 FETCH wdd_csr INTO l_lpn_id, l_lpn_name, l_waybill;
1520 CLOSE wdd_csr;
1521 --
1522 IF l_debug_on THEN
1523 wsh_debug_sv.log(l_module_name, 'l_lpn_id', l_lpn_id);
1524 wsh_debug_sv.log(l_module_name, 'l_lpn_name', l_lpn_name);
1525 wsh_debug_sv.log(l_module_name, 'l_waybill', l_waybill);
1526 END IF;
1527 --
1528 l_new_parent_wdd_id :=NULL;
1529 --
1530 BEGIN
1531 --{
1532 SELECT wdd.delivery_detail_id
1533 INTO l_new_parent_wdd_id
1534 FROM wsh_delivery_Details wdd,
1535 wsh_delivery_assignments wda
1536 WHERE wda.delivery_id = l_delivery_id
1537 AND wda.delivery_detail_id = wdd.delivery_detail_id
1538 AND NVL(wda.type,'S') IN ('S','O')
1539 AND wdd.lpn_id = l_lpn_id;
1540 --
1541 EXCEPTION
1542 WHEN NO_DATA_FOUND THEN
1543 l_new_parent_wdd_id := NULL;
1544 --}
1545 END;
1546 --
1547 IF l_debug_on THEN
1548 wsh_debug_sv.log(l_module_name, 'l_new_parent_wdd_id', l_new_parent_wdd_id);
1549 END IF;
1550 --
1551 IF l_new_parent_wdd_id IS NOT NULL THEN
1552 --{
1553 FORALL I in l_tmp_wdd_id_tbl.first..l_tmp_wdd_id_tbl.last
1554 --
1555 UPDATE wsh_delivery_assignments
1556 SET parent_delivery_detail_id = l_new_parent_wdd_id
1557 WHERE delivery_detail_id = l_tmp_wdd_id_tbl(i);
1558 --
1559 IF l_debug_on THEN
1560 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TP_RELEASE.CALCULATE_CONT_DEL_TPDATES',WSH_DEBUG_SV.C_PROC_LEVEL);
1561 END IF;
1562 --
1563 l_temp_tbl.delete ;
1564 l_temp_tbl(1) := l_parent_wdd_id; --used to pass l_parent_wdd_id in the procedure below
1565 --
1566 wsh_tp_release.calculate_cont_del_tpdates(
1567 p_entity => 'LPN',
1568 p_entity_ids => l_temp_tbl,
1569 x_return_status => l_return_status
1570 );
1571 --
1572 wsh_util_core.api_post_call(
1573 p_return_status => l_return_status,
1574 x_num_warnings => l_num_warnings,
1575 x_num_errors => l_num_errors
1576 );
1577 --}
1578 ELSE
1579 --{
1580 IF l_debug_on THEN
1581 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CONTAINER_ACTIONS.PACK_INBOUND_LINES',WSH_DEBUG_SV.C_PROC_LEVEL);
1582 WSH_DEBUG_SV.log(l_module_name, 'l_tmp_wdd_id_tbl.COUNT', l_tmp_wdd_id_tbl.COUNT);
1583 END IF;
1584 --
1585 wsh_container_actions.pack_inbound_lines(
1586 p_lines_tbl => l_tmp_wdd_id_tbl,
1587 p_lpn_id => l_lpn_id,
1588 p_lpn_name => l_lpn_name,
1589 p_delivery_id => l_delivery_id,
1590 p_transactionType => l_txn_type,
1591 x_return_status => l_return_status,
1592 p_waybill_number => l_waybill,
1593 p_caller => p_caller
1594 );
1595 --
1596 wsh_util_core.api_post_call(
1597 p_return_status => l_return_status,
1598 x_num_warnings => l_num_warnings,
1599 x_num_errors => l_num_errors
1600 );
1601 --}
1602 END IF; --IF l_new_parent_wdd_id IS NOT NULL
1603 --
1604 SELECT count(1) INTO l_query_count
1605 FROM wsh_delivery_assignments
1606 WHERE delivery_id = p_delivery_id
1607 AND parent_delivery_detail_id = l_parent_wdd_id
1608 AND nvl(type,'S') IN ('S','O');
1609 --
1610 IF l_debug_on THEN
1611 wsh_debug_sv.log(l_module_name, 'l_query_count', l_query_count);
1612 END IF;
1613 --
1614 IF l_query_count = 0 THEN
1615 --{
1616 DELETE FROM wsh_delivery_assignments
1617 WHERE delivery_detail_id = l_parent_wdd_id;
1618 --
1619 DELETE FROM wsh_delivery_details
1620 WHERE delivery_detail_id = l_parent_wdd_id;
1621 --
1622 IF l_debug_on THEN
1623 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTEGRATION.DBI_UPDATE_DETAIL_LOG',WSH_DEBUG_SV.C_PROC_LEVEL);
1624 END IF;
1625 --
1626 l_temp_tbl.delete;
1627 l_temp_tbl(1) := l_parent_wdd_id; -- to pass l_parent_wdd_id in procedure below
1628 --
1629 WSH_INTEGRATION.DBI_Update_Detail_Log(
1630 p_delivery_detail_id_tab => l_temp_tbl,
1631 p_dml_type => 'DELETE',
1632 x_return_status => l_return_status
1633 );
1634 --
1635 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1637 END IF;
1638 --
1639 wsh_util_core.api_post_call(
1640 p_return_status => l_return_status,
1641 x_num_warnings => l_num_warnings,
1642 x_num_errors => l_num_errors
1643 );
1644 --}
1645 END IF; -- IF l_query_count = 0
1646 --
1647 l_tmp_wdd_id_tbl.delete;
1648 l_count := 1;
1649 --}
1650 END IF;
1651 --
1652 IF ( l_index IS NULL ) THEN
1653 EXIT; --come out of loop
1654 ELSE
1655 l_tmp_wdd_id_tbl(l_count) := l_wdd_id_tbl(l_index);
1656 l_count := l_count + 1;
1657 l_index := l_parent_wdd_id_tbl.next(l_index);
1658 l_prev_parent_wdd_id := l_current_parent_wdd_id;
1659 END IF;
1660 --}
1661 END LOOP;
1662 --}
1663 END IF; -- l_index IS NOT NULL
1664 --}
1665 END IF; -- if p_caller = 'WSH_VENDOR_MERGE'
1666 --
1667 IF l_debug_on THEN
1668 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.update_freight_terms',WSH_DEBUG_SV.C_PROC_LEVEL);
1669 END IF;
1670 --
1671 -- Update freight terms for original delivery
1672 --
1673 WSH_NEW_DELIVERY_ACTIONS.update_freight_terms
1674 (
1675 p_delivery_id => p_delivery_id,
1676 p_action_code => 'UNASSIGN',
1677 x_return_status => l_return_status,
1678 x_freight_terms_code => l_dlvy_freight_terms_code
1679 );
1680 --
1681 --
1682 wsh_util_core.api_post_call
1683 (
1684 p_return_status => l_return_status,
1685 x_num_warnings => l_num_warnings,
1686 x_num_errors => l_num_errors
1687 );
1688 --
1689 --
1690 IF l_debug_on THEN
1691 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.update_freight_terms',WSH_DEBUG_SV.C_PROC_LEVEL);
1692 END IF;
1693 --
1694 -- Update freight terms for new delivery
1695 --
1696 WSH_NEW_DELIVERY_ACTIONS.update_freight_terms
1697 (
1698 p_delivery_id => l_delivery_id,
1699 p_action_code => 'ASSIGN',
1700 x_return_status => l_return_status,
1701 x_freight_terms_code => l_dlvy_freight_terms_code
1702 );
1703 --
1704 --
1705 wsh_util_core.api_post_call
1706 (
1707 p_return_status => l_return_status,
1708 x_num_warnings => l_num_warnings,
1709 x_num_errors => l_num_errors
1710 );
1711 --
1712 l_entity_ids(1) := p_delivery_id;
1713 l_entity_ids(2) := l_delivery_id;
1714 --
1715 --
1716 --
1717 IF l_debug_on THEN
1718 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_tp_release.calculate_cont_del_tpdates',WSH_DEBUG_SV.C_PROC_LEVEL);
1719 END IF;
1720 --
1721 --
1722 -- Calculate dates for new and original delivery.
1723 --
1724 wsh_tp_release.calculate_cont_del_tpdates
1725 (
1726 p_entity => 'DLVY',
1727 p_entity_ids => l_entity_ids,
1728 x_return_status => l_return_status
1729 );
1730 --
1731 wsh_util_core.api_post_call
1732 (
1733 p_return_status => l_return_status,
1734 x_num_warnings => l_num_warnings,
1735 x_num_errors => l_num_errors
1736 );
1737 --
1738 IF l_num_errors > 0
1739 THEN
1740 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1741 ELSIF l_num_warnings > 0
1742 THEN
1743 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1744 ELSE
1745 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1746 END IF;
1747 --
1748 IF l_debug_on THEN
1749 wsh_debug_sv.pop(l_module_name);
1750 END IF;
1751 --
1752 --}
1753 EXCEPTION
1754 --{
1755 --
1756 WHEN FND_API.G_EXC_ERROR THEN
1757
1758 ROLLBACK TO split_inbound_delivery_sp;
1759 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1760 --
1761 IF l_debug_on THEN
1762 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1763 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1764 END IF;
1765 --
1766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1767
1768 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1769 ROLLBACK TO split_inbound_delivery_sp;
1770 --
1771 IF l_debug_on THEN
1772 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1773 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1774 END IF;
1775 --
1776 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1777 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1778 --
1779 IF l_debug_on THEN
1780 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1781 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1782 END IF;
1783 --
1784 WHEN OTHERS THEN
1785
1786 ROLLBACK TO split_inbound_delivery_sp;
1787 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1788 wsh_util_core.default_handler('split_inbound_delivery', l_module_name);
1789 IF l_debug_on THEN
1790 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1791 END IF;
1792 --
1793 --}
1794
1795 END split_inbound_delivery;
1796
1797 --========================================================================
1798 -- PROCEDURE : reRateDeliveries
1799 --
1800 -- PARAMETERS: p_delivery_id_tab Delivery to be Re-rated.
1801 -- x_return_status Return status of the API.
1802 --
1803 -- COMMENT : This API Rates the Trip of the input Delivery Id, if their
1804 -- corresponding legs are marked for Reprice.
1805 -- Rating is done inline (by calling FTE_TRIP_RATING_GRP.Rate_Trip)
1806 -- when the Receipt of the input Delivery was matched automatically.
1807 -- Rating is done asynchronoulsy (by FND_REQUEST.SUBMIT_REQUEST)
1808 -- when the Receipt of the input Delivery was matched Manually.
1809 -- This (inline Rating or asynchronous rating) is determined
1810 -- by the Global variable WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE.
1811 --
1812 -- Hence forth (R12 onwards) if a new Exception Handler is added for this API
1813 -- or any other new return points are added, then care should be taken
1814 -- to set Global variable WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE
1815 -- to null.
1816 --========================================================================
1817 --
1818 PROCEDURE reRateDeliveries
1819 (
1820 p_delivery_id_tab IN wsh_util_core.id_tab_type,
1821 x_return_status OUT NOCOPY VARCHAR2
1822 )
1823 IS
1824 --{
1825 CURSOR trips_csr (p_delivery_id IN NUMBER)
1826 IS
1827 SELECT wt.trip_id, wt.name, wt.lane_id,
1828 NVL(wdl.reprice_Required,'N') reprice_required
1829 FROM wsh_trips wt,
1830 wsh_trip_stops wts,
1831 wsh_delivery_legs wdl
1832 WHERE wdl.delivery_id = p_delivery_id
1833 AND wdl.pick_up_stop_id = wts.stop_id
1834 AND wt.trip_id = wts.trip_id
1835 AND wt.lane_id IS NOT NULL;
1836 --
1837 --
1838 l_return_status VARCHAR2(1);
1839 l_num_warnings NUMBER;
1840 l_num_errors NUMBER;
1841 l_requestIdList VARCHAR2(32767);
1842 l_requestId NUMBER;
1843 --
1844 --
1845 l_index NUMBER;
1846 --
1847 l_trip_tbl wsh_util_core.key_value_tab_type;
1848 l_trip_ext_tbl wsh_util_core.key_value_tab_type;
1849 --
1850 -- { IB-Phase-2
1851 l_action_params FTE_TRIP_RATING_GRP.action_param_rec;
1852 l_msg_count NUMBER;
1853 l_msg_data VARCHAR2(32767);
1854 -- } IB-Phase-2
1855 --
1856 l_debug_on BOOLEAN;
1857 --
1858 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'reRateDeliveries';
1859 --
1860 --}
1861 BEGIN
1862 --{
1863 --SAVEPOINT open_stop_begin_sp;
1864 --
1865 -- Debug Statements
1866 --
1867 --
1868 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1869 --
1870 IF l_debug_on IS NULL
1871 THEN
1872 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1873 END IF;
1874 --
1875 IF l_debug_on THEN
1876 WSH_DEBUG_SV.push(l_module_name);
1877 --
1878 WSH_DEBUG_SV.log(l_module_name,'p_delivery_id_tab.count',p_delivery_id_tab.count);
1879 END IF;
1880 --
1881 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1882 --
1883 --
1884 l_num_warnings := 0;
1885 l_num_errors := 0;
1886 --
1887 --
1888 -- { IB-Phase-2
1889 IF nvl(WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE,'MANUAL') = 'MANUAL'
1890 THEN
1891 --{
1892 l_index := p_delivery_id_tab.FIRST;
1893 --
1894 WHILE l_index IS NOT NULL
1895 LOOP
1896 --{
1897 FOR trips_rec IN trips_csr(p_delivery_id_tab(l_index))
1898 LOOP
1899 --{
1900 IF trips_Rec.reprice_required = 'Y'
1901 THEN
1902 --{
1903 IF l_debug_on THEN
1904 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_Cached_value',WSH_DEBUG_SV.C_PROC_LEVEL);
1905 END IF;
1906 --
1907 WSH_UTIL_CORE.get_Cached_value
1908 (
1909 p_cache_tbl => l_trip_tbl,
1910 p_cache_ext_tbl => l_trip_ext_tbl,
1911 p_key => trips_rec.trip_id,
1912 p_value => trips_rec.trip_id,
1913 p_action => 'GET',
1914 x_return_status => l_return_status
1915 );
1916 --
1917 --
1918 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1919 THEN
1920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1921 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1922 THEN
1923 RAISE FND_API.G_EXC_ERROR;
1924 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
1925 THEN
1926 --{
1927 IF l_debug_on THEN
1928 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FND_REQUEST.SUBMIT_REQUEST',WSH_DEBUG_SV.C_PROC_LEVEL);
1929 END IF;
1930 --
1931 l_requestId :=
1932 FND_REQUEST.SUBMIT_REQUEST
1933 (
1934 application => 'FTE',
1935 program => 'FTE_TRIP_RERATE',
1936 description => NULL,
1937 start_time => NULL,
1938 sub_request => FALSE,
1939 argument1 => trips_rec.trip_id,
1940 argument2 => trips_rec.name
1941 );
1942 --
1943 IF l_debug_on THEN
1944 WSH_DEBUG_SV.log(l_module_name,'l_requestId',l_requestId);
1945 END IF;
1946 --
1947 IF l_requestId <> 0
1948 THEN
1949 --{
1950 IF l_requestIdList IS NULL
1951 THEN
1952 l_requestIdList := l_requestId;
1953 ELSE
1954 l_requestIdList := SUBSTRB
1955 (
1956 l_requestIdList
1957 || ','
1958 || l_requestId,
1959 1,1800);
1960 END IF;
1961 --}
1962 ELSE
1963 --{
1964 FND_MESSAGE.SET_NAME('WSH', 'WSH_IB_RATE_TRIP_ERROR');
1965 FND_MESSAGE.SET_TOKEN('TRIP_NAME',trips_rec.name);
1966 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
1967 --}
1968 END IF;
1969 --}
1970 END IF;
1971 --
1972 --
1973 IF l_debug_on THEN
1974 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_Cached_value',WSH_DEBUG_SV.C_PROC_LEVEL);
1975 END IF;
1976 --
1977 WSH_UTIL_CORE.get_Cached_value
1978 (
1979 p_cache_tbl => l_trip_tbl,
1980 p_cache_ext_tbl => l_trip_ext_tbl,
1981 p_key => trips_rec.trip_id,
1982 p_value => trips_rec.trip_id,
1983 p_action => 'PUT',
1984 x_return_status => l_return_status
1985 );
1986 --
1987 IF l_debug_on THEN
1988 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1989 END IF;
1990 --
1991 WSH_UTIL_CORE.api_post_call
1992 (
1993 p_return_status => l_return_status,
1994 x_num_warnings => l_num_warnings,
1995 x_num_errors => l_num_errors
1996 );
1997 --}
1998 END IF;
1999 --}
2000 END LOOP;
2001 --
2002 l_index := p_delivery_id_tab.NEXT(l_index);
2003 --}
2004 END LOOP;
2005 --
2006 --
2007 IF l_requestIdList IS NOT NULL
2008 THEN
2009 --{
2010 FND_MESSAGE.SET_NAME('WSH', 'WSH_IB_RATE_TRIP_MESSAGE');
2011 FND_MESSAGE.SET_TOKEN('REQ_IDS',l_requestIdList);
2012 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS,l_module_name);
2013 --}
2014 END IF;
2015 --}
2016 --
2017 ELSE -- IF WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE = 'AUTO'
2018 --{
2019 l_index := p_delivery_id_tab.FIRST;
2020 --
2021 WHILE l_index IS NOT NULL
2022 LOOP
2023 --{
2024 FOR trips_rec IN trips_csr(p_delivery_id_tab(l_index))
2025 LOOP
2026 --{
2027 IF trips_Rec.reprice_required = 'Y'
2028 THEN
2029 --{
2030
2031 IF l_debug_on THEN
2032 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_Cached_value',WSH_DEBUG_SV.C_PROC_LEVEL);
2033 END IF;
2034 --
2035 WSH_UTIL_CORE.get_Cached_value
2036 (
2037 p_cache_tbl => l_trip_tbl,
2038 p_cache_ext_tbl => l_trip_ext_tbl,
2039 p_key => trips_rec.trip_id,
2040 p_value => trips_rec.trip_id,
2041 p_action => 'GET',
2042 x_return_status => l_return_status
2043 );
2044 --
2045 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2046 THEN
2047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2048 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
2049 THEN
2050 RAISE FND_API.G_EXC_ERROR;
2051 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
2052 THEN
2053 --{
2054 l_action_params.trip_id_list(l_action_params.trip_id_list.count + 1) := trips_rec.trip_id;
2055
2056 IF l_debug_on THEN
2057 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.get_Cached_value',WSH_DEBUG_SV.C_PROC_LEVEL);
2058 END IF;
2059 --
2060 WSH_UTIL_CORE.get_Cached_value
2061 (
2062 p_cache_tbl => l_trip_tbl,
2063 p_cache_ext_tbl => l_trip_ext_tbl,
2064 p_key => trips_rec.trip_id,
2065 p_value => trips_rec.trip_id,
2066 p_action => 'PUT',
2067 x_return_status => l_return_status
2068 );
2069 --
2070 IF l_debug_on THEN
2071 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2072 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2073 END IF;
2074 --
2075 wsh_util_core.api_post_call
2076 (
2077 p_return_status => l_return_status,
2078 x_num_warnings => l_num_warnings,
2079 x_num_errors => l_num_errors
2080 );
2081 --}
2082 END IF;
2083 --}
2084 END IF;
2085 --}
2086 END LOOP;
2087 --
2088 l_index := p_delivery_id_tab.NEXT(l_index);
2089 --}
2090 END LOOP;
2091 --
2092 --
2093 --}
2094 l_action_params.caller :='WSH';
2095 l_action_params.event :='RE-RATING';
2096 l_action_params.action :='RATE';
2097
2098 IF l_action_params.trip_id_list.count > 0
2099 THEN
2100 --{
2101 SAVEPOINT handle_rate_fail_sp; -- bugfix 4535358
2102 IF l_debug_on THEN
2103 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_TRIP_RATING_GRP.Rate_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
2104 END IF;
2105 --
2106 --
2107 FTE_TRIP_RATING_GRP.Rate_Trip (
2108 p_api_version => 1.0,
2109 p_init_msg_list => FND_API.G_FALSE,
2110 p_action_params => l_action_params,
2111 p_commit => FND_API.G_FALSE,
2112 p_init_prc_log => 'Y',
2113 x_return_status => l_return_status,
2114 x_msg_count => l_msg_count,
2115 x_msg_data => l_msg_data);
2116
2117 IF l_debug_on THEN
2118 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2119 END IF;
2120
2121 --bugfix 4535358
2122 --{
2123 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
2124 THEN
2125 IF l_debug_on THEN
2126 wsh_debug_sv.logmsg(l_module_name, 'Rating has failed');
2127 END IF;
2128 --Even if Rating fails for any Reason, IB will proceed with auto-matching of Receipt by
2129 --reverting to the State before Rating was initiated.
2130 ROLLBACK TO handle_rate_fail_sp;
2131 --
2132 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
2133 THEN
2134 l_num_warnings := l_num_warnings + 1;
2135 END IF;
2136 --}
2137 --}
2138 END IF;
2139
2140 END IF;
2141 --
2142 WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE := null;
2143 -- } IB-Phase-2
2144 --
2145 IF l_num_errors > 0
2146 THEN
2147 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2148 ELSIF l_num_warnings > 0
2149 THEN
2150 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2151 ELSE
2152 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2153 END IF;
2154 --
2155 --
2156 -- Debug Statements
2157 --
2158 IF l_debug_on THEN
2159 WSH_DEBUG_SV.pop(l_module_name);
2160 END IF;
2161 --
2162 --}
2163 EXCEPTION
2164 --{
2165 --
2166 WHEN FND_API.G_EXC_ERROR THEN
2167
2168 --ROLLBACK TO open_stop_begin_sp;
2169 --
2170 WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE := null; -- IB-Phase-2
2171 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2172 --
2173 IF l_debug_on THEN
2174 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2175 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2176 END IF;
2177 --
2178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2179
2180 --ROLLBACK TO open_stop_begin_sp;
2181 --
2182 WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE := null; -- IB-Phase-2
2183 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2184 --
2185 IF l_debug_on THEN
2186 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2187 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2188 END IF;
2189 --
2190 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2191 WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE := null; -- IB-Phase-2
2192 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2193 --
2194 IF l_debug_on THEN
2195 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2196 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2197 END IF;
2198 WHEN others THEN
2199 wsh_util_core.default_handler('WSH_INBOUND_UTIL_PKG.reRateDeliveries',l_module_name);
2200 --
2201 --ROLLBACK TO open_stop_begin_sp;
2202 --
2203 WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE := null; -- IB-Phase-2
2204 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2205 --
2206 -- Debug Statements
2207 --
2208 IF l_debug_on THEN
2209 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2210 END IF;
2211 --
2212 --}
2213 END reRateDeliveries;
2214
2215 PROCEDURE processStop
2216 (
2217 p_stop_id IN NUMBER,
2218 p_status_code IN VARCHAR2,
2219 p_shipements_type_flag IN VARCHAR2,
2220 p_action_code IN VARCHAR2 DEFAULT 'APPLY',
2221 x_processed OUT NOCOPY VARCHAR2,
2222 x_return_status OUT NOCOPY VARCHAR2
2223 )
2224 IS
2225 --{
2226 l_return_status VARCHAR2(1);
2227 l_num_warnings NUMBER;
2228 l_num_errors NUMBER;
2229 l_message_name VARCHAR2(50);
2230 --
2231 --
2232 CURSOR linked_stop_csr (p_stop_id IN NUMBER)
2233 IS
2234 SELECT 1
2235 FROM wsh_trip_stops wts1,
2236 wsh_trip_stops wts2
2237 WHERE wts2.stop_id = p_stop_id
2238 AND wts2.trip_id = wts1.trip_id
2239 AND wts1.physical_stop_id = p_stop_id
2240 AND wts1.status_code IN ('OP','AR')
2241 AND NVL(wts1.shipments_type_flag,'O') IN ('M','O');
2242 --
2243 --
2244 l_dummy NUMBER;
2245 l_stop_id NUMBER;
2246 l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
2247 --
2248 l_debug_on BOOLEAN;
2249 l_reopen_flag BOOLEAN;
2250 --
2251 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'processStop';
2252 --
2253 --}
2254 BEGIN
2255 --{
2256 --SAVEPOINT open_stop_begin_sp;
2257 --
2258 -- Debug Statements
2259 --
2260 --
2261 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2262 --
2263 IF l_debug_on IS NULL
2264 THEN
2265 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2266 END IF;
2267 --
2268 IF l_debug_on THEN
2269 WSH_DEBUG_SV.push(l_module_name);
2270 --
2271 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
2272 WSH_DEBUG_SV.log(l_module_name,'p_status_code',p_status_code);
2273 WSH_DEBUG_SV.log(l_module_name,'p_shipements_type_flag',p_shipements_type_flag);
2274 WSH_DEBUG_SV.log(l_module_name,'p_action_code',p_action_code);
2275 END IF;
2276 --
2277 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2278 --
2279 --
2280 l_num_warnings := 0;
2281 l_num_errors := 0;
2282 --
2283 --
2284 x_processed := 'N';
2285 l_stop_id := NULL;
2286 l_dummy := NULL;
2287 --
2288 --
2289 IF p_action_code = 'APPLY'
2290 THEN
2291 --{
2292 l_reopen_flag := FALSE;
2293 --
2294 IF p_shipements_type_flag = 'I'
2295 THEN
2296 IF p_status_code = 'CL'
2297 THEN
2298 x_processed := 'Y';
2299 ELSE
2300 --{
2301 OPEN linked_stop_csr(p_stop_id => p_stop_id);
2302 FETCH linked_stop_csr INTO l_dummy;
2303 CLOSE linked_stop_csr;
2304 --
2305 IF l_dummy IS NULL
2306 THEN
2307 l_stop_id := p_stop_id;
2308 END IF;
2309 --}
2310 END IF;
2311 ELSE --- 'M'
2312 IF p_status_code = 'CL'
2313 THEN
2314 x_processed := 'Y';
2315 END IF;
2316 END IF;
2317 --}
2318 ELSE
2319 --{
2320 l_reopen_flag := TRUE;
2321 --
2322 IF p_shipements_type_flag = 'I'
2323 THEN
2324 IF p_status_code = 'CL'
2325 THEN
2326 --{
2327 OPEN linked_stop_csr(p_stop_id => p_stop_id);
2328 FETCH linked_stop_csr INTO l_dummy;
2329 CLOSE linked_stop_csr;
2330 --
2331 IF l_dummy IS NULL
2332 THEN
2333 l_stop_id := p_stop_id;
2334 ELSE
2335 x_processed := 'Y';
2336 END IF;
2337 --}
2338 ELSE
2339 x_processed := 'Y';
2340 END IF;
2341 ELSE --- 'M'
2342 x_processed := 'Y';
2343 END IF;
2344 --}
2345 END IF;
2346 --
2347 --
2348 IF l_stop_id IS NOT NULL
2349 THEN
2350 --{
2351 l_stop_in_rec.stop_id := l_stop_id;
2352 l_stop_in_rec.put_messages := FALSE;
2353 l_stop_in_rec.caller := l_module_name;
2354 l_stop_in_rec.actual_date := SYSDATE;
2355 --
2356 --
2357 IF l_debug_on THEN
2358 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.autoCLOSE',WSH_DEBUG_SV.C_PROC_LEVEL);
2359 END IF;
2360 --
2361 WSH_TRIP_STOPS_ACTIONS.autoCloseOpen
2362 (
2363 p_in_rec => l_stop_in_rec,
2364 p_reopenStop => l_reopen_flag,
2365 x_stop_processed => x_processed,
2366 x_return_status => l_return_status
2367 );
2368 --
2369 WSH_UTIL_CORE.api_post_call
2370 (
2371 p_return_status => l_return_status,
2372 x_num_warnings => l_num_warnings,
2373 x_num_errors => l_num_errors
2374 );
2375 --
2376 IF p_action_code = 'CANCEL'
2377 THEN
2378 x_processed := 'Y';
2379 END IF;
2380 --}
2381 END IF;
2382 --
2383 --
2384 --
2385 IF l_debug_on THEN
2386 WSH_DEBUG_SV.logmsg(l_module_name,
2387 'Number of Errors='||l_num_errors||',Number of Warnings='||l_num_warnings);
2388 END IF;
2389 --
2390 IF l_num_errors > 0
2391 THEN
2392 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2393 ELSIF l_num_warnings > 0
2394 THEN
2395 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2396 ELSE
2397 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2398 END IF;
2399 --
2400 --
2401 -- Debug Statements
2402 --
2403 IF l_debug_on THEN
2404 WSH_DEBUG_SV.pop(l_module_name);
2405 END IF;
2406 --
2407 --}
2408 EXCEPTION
2409 --{
2410 --
2411 WHEN FND_API.G_EXC_ERROR THEN
2412
2413 --ROLLBACK TO open_stop_begin_sp;
2414 --
2415 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2416 --
2417 IF l_debug_on THEN
2418 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2419 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2420 END IF;
2421 --
2422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2423
2424 --ROLLBACK TO open_stop_begin_sp;
2425 --
2426 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2427 --
2428 IF l_debug_on THEN
2429 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2430 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2431 END IF;
2432 --
2433 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2434 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2435 --
2436 IF l_debug_on THEN
2437 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2438 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2439 END IF;
2440 WHEN others THEN
2441 wsh_util_core.default_handler('WSH_INBOUND_UTIL_PKG.processStop',l_module_name);
2442 --
2443 --ROLLBACK TO open_stop_begin_sp;
2444 --
2445 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2446 --
2447 -- Debug Statements
2448 --
2449 IF l_debug_on THEN
2450 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2451 END IF;
2452 --
2453 --}
2454 END processStop;
2455
2456 PROCEDURE setTripStopStatus
2457 (
2458 p_transaction_code IN VARCHAR2 DEFAULT 'RECEIPT',
2459 p_action_code IN VARCHAR2 DEFAULT 'APPLY',
2460 p_delivery_id_tab IN wsh_util_core.id_tab_type,
2461 x_return_status OUT NOCOPY VARCHAR2
2462 )
2463 IS
2464 --{
2465
2466 CURSOR first_leg_csr (p_delivery_id IN NUMBER) IS
2467 SELECT wnd.name delivery_name,
2468 wnd.status_code delivery_statusCode,
2469 wnd.ultimate_dropoff_location_id,
2470 wnd.asn_shipment_header_id,
2471 wnd.rcv_shipment_header_id,
2472 pu_stop.stop_id pu_stop_id,
2473 pu_Stop.status_code pu_stop_statusCode,
2474 NVL(pu_stop.shipments_type_flag,'O') pu_stop_shipTypeFlag,
2475 do_stop.stop_id do_stop_id,
2476 do_stop.stop_location_id do_stop_locationId,
2477 do_Stop.status_code do_stop_statusCode,
2478 NVL(do_stop.shipments_type_flag,'O') do_stop_shipTypeFlag,
2479 pu_stop.trip_id trip_id
2480 FROM wsh_trip_stops pu_stop,
2481 wsh_trip_stops do_stop,
2482 wsh_delivery_legs wdl,
2483 wsh_new_deliveries wnd
2484 WHERE wnd.delivery_id = p_delivery_id
2485 AND wdl.delivery_id = wnd.delivery_id
2486 AND NVL(wnd.shipment_direction,'O') NOT IN ('O','IO')
2487 AND wdl.pick_up_stop_id = pu_stop.stop_id
2488 AND wnd.initial_pickup_location_id = pu_stop.stop_location_id
2489 AND wdl.drop_off_stop_id = do_stop.stop_id;
2490 --
2491 CURSOR next_leg_csr (p_stop_id IN NUMBER, p_delivery_id IN NUMBER) IS
2492 SELECT next_leg_do_stop.status_code do_stop_statusCode,
2493 NVL(next_leg_do_stop.shipments_type_flag,'O') do_stop_shipTypeFlag,
2494 next_leg_do_stop.stop_location_id do_stop_locationId,
2495 next_leg_do_stop.stop_id do_stop_id,
2496 next_leg_pu_stop.status_code pu_stop_statusCode,
2497 NVL(next_leg_pu_stop.shipments_type_flag,'O') pu_stop_shipTypeFlag,
2498 next_leg_pu_stop.stop_location_id pu_stop_locationId,
2499 next_leg_pu_stop.stop_id pu_stop_id,
2500 NVL(wnd.shipment_direction,'O') shipment_direction,
2501 wnd.status_code dlvy_status_code,
2502 wnd.ultimatE_dropoff_location_id dlvy_ultimate_doLocationId,
2503 next_leg_pu_stop.trip_id trip_id --3410681
2504 FROM wsh_trip_stops next_leg_do_stop,
2505 wsh_trip_stops next_leg_pu_stop,
2506 wsh_trip_stops curr_leg_do_stop,
2507 wsh_delivery_legs next_leg,
2508 wsh_delivery_legs curr_leg,
2509 wsh_new_deliveries wnd
2510 WHERE next_leg.drop_off_stop_id = next_leg_do_stop.stop_id
2511 --AND st1.status_code = 'OP'
2512 AND next_leg.pick_up_stop_id = next_leg_pu_stop.stop_id
2513 AND next_leg_pu_stop.stop_location_id = curr_leg_do_stop.stop_location_id
2514 AND next_leg.delivery_id = curr_leg.delivery_id
2515 AND curr_leg_do_stop.stop_id = p_stop_id
2516 AND curr_leg.drop_off_stop_id = p_stop_id
2517 AND wnd.delivery_id = curr_leg.delivery_id
2518 AND wnd.delivery_id = p_delivery_id;
2519 --AND NVL(wnd.shipment_direction,'O') NOT IN ('O','IO')
2520 --
2521 --
2522 CURSOR dlvy_csr (p_delivery_id IN NUMBER)
2523 IS
2524 SELECT NVL(wnd.planned_flag,'N') planned_flag,
2525 NVL(ignore_for_planning,'N') ignore_for_planning
2526 FROM wsh_new_deliveries wnd
2527 WHERE wnd.delivery_id = p_delivery_id;
2528 --
2529
2530
2531 --Bug 3410681 fixed.
2532 --Cursor to find any stops not closed for a trip.
2533 CURSOR stop_csr (p_trip_id IN NUMBER) IS
2534 SELECT count(stop_id)
2535 FROM wsh_trip_stops
2536 WHERE trip_id = p_trip_id
2537 AND status_code <> 'CL';
2538
2539 l_trip_tbl wsh_util_core.key_value_tab_type;
2540 l_trip_ext_tbl wsh_util_core.key_value_tab_type;
2541 l_stop_count number := 0;
2542 --Bug 3410681 fixed.
2543
2544 --
2545 l_return_status VARCHAR2(1);
2546 l_num_warnings NUMBER;
2547 l_num_errors NUMBER;
2548 l_message_name VARCHAR2(50);
2549 --
2550 --
2551 l_reopen_flag BOOLEAN := FALSE;
2552 l_stop_id NUMBER;
2553 l_next_stop_id NUMBER;
2554 l_stop_locationId NUMBER;
2555 l_index NUMBER;
2556 --
2557 --
2558 l_count NUMBER := 0;
2559 l_dlvy_tbl wsh_util_core.id_tab_type;
2560 --
2561 l_trip_Status_code VARCHAR2(30);
2562 l_stop_processed VARCHAR2(10);
2563 l_allowed VARCHAR2(10);
2564 --
2565 --l_trip_in_rec WSH_TRIP_VALIDATIONS.ChgStatus_in_rec_type;
2566 l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
2567
2568
2569 --
2570 l_debug_on BOOLEAN;
2571 --
2572 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'setTripStopStatus';
2573 --
2574 --}
2575 BEGIN
2576 --{
2577 --SAVEPOINT open_stop_begin_sp;
2578 --
2579 -- Debug Statements
2580 --
2581 --
2582 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2583 --
2584 IF l_debug_on IS NULL
2585 THEN
2586 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2587 END IF;
2588 --
2589 IF l_debug_on THEN
2590 WSH_DEBUG_SV.push(l_module_name);
2591 --
2592 WSH_DEBUG_SV.log(l_module_name,'p_transaction_code',p_transaction_code);
2593 WSH_DEBUG_SV.log(l_module_name,'p_action_code',p_action_code);
2594 END IF;
2595 --
2596 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2597 --
2598 --
2599 l_num_warnings := 0;
2600 l_num_errors := 0;
2601 --
2602 --
2603 IF p_action_code = 'APPLY'
2604 THEN
2605 l_reopen_flag := FALSE;
2606 ELSE
2607 l_reopen_flag := TRUE;
2608 END IF;
2609 --
2610 l_index := p_delivery_id_tab.FIRST;
2611 --
2612 WHILE l_index IS NOT NULL
2613 LOOP
2614 --{
2615 --
2616 IF l_debug_on THEN
2617 WSH_DEBUG_SV.log(l_module_name,'l_index',l_index);
2618 WSH_DEBUG_SV.log(l_module_name,'p_delivery_id_tab(l_index)',p_delivery_id_tab(l_index));
2619 END IF;
2620 --
2621 <<first_leg_loop>>
2622 FOR first_leg_rec IN first_leg_csr(p_delivery_id_tab(l_index))
2623 LOOP
2624 --{
2625 l_stop_id := NULL;
2626 l_message_name := NULL;
2627 --
2628
2629 --Bug 3410681 fixed
2630 wsh_util_core.get_cached_value (
2631 p_cache_tbl => l_trip_tbl,
2632 p_cache_ext_tbl => l_trip_ext_tbl,
2633 p_key => first_leg_rec.trip_id,
2634 p_value => first_leg_rec.trip_id,
2635 p_action => 'PUT',
2636 x_return_status => l_return_status);
2637
2638 IF l_debug_on THEN
2639 WSH_DEBUG_SV.log(l_module_name,'wsh_util_core.get_cached_value l_return_status',l_return_status);
2640 WSH_DEBUG_SV.log(l_module_name,'l_trip_tbl.count',l_trip_tbl.count);
2641 END IF;
2642
2643 wsh_util_core.api_post_call
2644 (
2645 p_return_status => l_return_status,
2646 x_num_warnings => l_num_warnings,
2647 x_num_errors => l_num_errors
2648 );
2649 --Bug 3410681 fixed
2650
2651 IF p_transaction_code = 'ASN'
2652 THEN
2653 --{
2654 IF p_action_code = 'APPLY'
2655 AND first_leg_rec.delivery_statusCode <> 'IT'
2656 THEN
2657 l_message_name := 'WSH_DLVY_IT_ERROR';
2658 END IF;
2659 --
2660 IF p_action_code = 'CANCEL'
2661 AND first_leg_rec.delivery_statusCode <> 'OP'
2662 THEN
2663 l_message_name := 'WSH_DLVY_OP_ERROR';
2664 END IF;
2665 --
2666 IF l_message_name IS NOT NULL
2667 THEN
2668 --{
2669 FND_MESSAGE.SET_NAME('WSH', l_message_name);
2670 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',first_leg_rec.delivery_name);
2671 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
2672 RAISE FND_API.G_EXC_ERROR;
2673 --}
2674 END IF;
2675 --}
2676 ELSIF p_transaction_code = 'RECEIPT'
2677 THEN
2678 --{
2679 IF p_action_code = 'APPLY'
2680 AND first_leg_rec.delivery_statusCode <> 'CL'
2681 THEN
2682 l_message_name := 'WSH_DLVY_CL_ERROR';
2683 END IF;
2684 --
2685 IF p_action_code = 'CANCEL'
2686 THEN
2687 IF first_leg_rec.asn_shipment_header_id IS NULL
2688 THEN
2689 IF first_leg_rec.delivery_statusCode <> 'OP'
2690 THEN
2691 l_message_name := 'WSH_DLVY_OP_ERROR';
2692 END IF;
2693 ELSE
2694 IF first_leg_rec.delivery_statusCode <> 'IT'
2695 THEN
2696 l_message_name := 'WSH_DLVY_IT_ERROR';
2697 END IF;
2698 END IF;
2699 END IF;
2700 --
2701 IF l_message_name IS NOT NULL
2702 THEN
2703 --{
2704 FND_MESSAGE.SET_NAME('WSH', l_message_name);
2705 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',first_leg_rec.delivery_name);
2706 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
2707 RAISE FND_API.G_EXC_ERROR;
2708 --}
2709 END IF;
2710 --}
2711 END IF;
2712 --
2713 --
2714 IF l_debug_on THEN
2715 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.processStop',WSH_DEBUG_SV.C_PROC_LEVEL);
2716 END IF;
2717 --
2718 --
2719 WSH_INBOUND_UTIL_PKG.processStop
2720 (
2721 p_stop_id => first_leg_rec.pu_stop_id,
2722 p_status_code => first_leg_rec.pu_stop_statusCode,
2723 p_shipements_type_flag => first_leg_rec.pu_stop_shipTypeFlag,
2724 p_action_code => p_action_code,
2725 x_processed => l_stop_processed,
2726 x_return_status => l_return_status
2727 );
2728 --
2729 --
2730 --
2731 IF l_debug_on THEN
2732 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2733 WSH_DEBUG_SV.log(l_module_name,'l_stop_processed',l_stop_processed);
2734 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_util_core.api_post_call',WSH_DEBUG_SV.C_PROC_LEVEL);
2735 END IF;
2736 --
2737 wsh_util_core.api_post_call
2738 (
2739 p_return_status => l_return_status,
2740 x_num_warnings => l_num_warnings,
2741 x_num_errors => l_num_errors
2742 );
2743 --
2744 IF p_transaction_code = 'ASN'
2745 OR l_stop_processed = 'N'
2746 THEN
2747 EXIT;
2748 END IF;
2749 --
2750 --
2751 IF l_stop_processed = 'Y'
2752 THEN
2753 --{
2754 IF l_debug_on THEN
2755 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.processStop',WSH_DEBUG_SV.C_PROC_LEVEL);
2756 END IF;
2757 --
2758 --
2759 WSH_INBOUND_UTIL_PKG.processStop
2760 (
2761 p_stop_id => first_leg_rec.do_stop_id,
2762 p_status_code => first_leg_rec.do_stop_statusCode,
2763 p_shipements_type_flag => first_leg_rec.do_stop_shipTypeFlag,
2764 p_action_code => p_action_code,
2765 x_processed => l_stop_processed,
2766 x_return_status => l_return_status
2767 );
2768 --
2769 --
2770 --
2771 IF l_debug_on THEN
2772 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2773 WSH_DEBUG_SV.log(l_module_name,'l_stop_processed',l_stop_processed);
2774 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_util_core.api_post_call',WSH_DEBUG_SV.C_PROC_LEVEL);
2775 END IF;
2776 --
2777 wsh_util_core.api_post_call
2778 (
2779 p_return_status => l_return_status,
2780 x_num_warnings => l_num_warnings,
2781 x_num_errors => l_num_errors
2782 );
2783 --
2784 IF l_stop_processed = 'N'
2785 THEN
2786 EXIT;
2787 END IF;
2788 --
2789 --
2790 l_stop_locationId := first_leg_rec.do_stop_locationId;
2791 l_next_stop_Id := first_leg_rec.do_stop_id;
2792 --
2793 WHILE l_stop_locationId <> first_leg_rec.ultimate_dropoff_location_id
2794 --AND l_stop_id IS NULL
2795 LOOP
2796 --{
2797 FOR next_leg_rec IN next_leg_csr(l_next_stop_id, p_delivery_id_tab(l_index))
2798 LOOP
2799 --{
2800
2801 --Bug 3410681 fixed
2802 wsh_util_core.get_cached_value (
2803 p_cache_tbl => l_trip_tbl,
2804 p_cache_ext_tbl => l_trip_ext_tbl,
2805 p_key => next_leg_rec.trip_id,
2806 p_value => next_leg_rec.trip_id,
2807 p_action => 'PUT',
2808 x_return_status => l_return_status);
2809
2810 IF l_debug_on THEN
2811 WSH_DEBUG_SV.log(l_module_name,'wsh_util_core.get_cached_value l_return_status',l_return_status);
2812 WSH_DEBUG_SV.log(l_module_name,'l_trip_tbl.count',l_trip_tbl.count);
2813 END IF;
2814
2815 wsh_util_core.api_post_call (
2816 p_return_status => l_return_status,
2817 x_num_warnings => l_num_warnings,
2818 x_num_errors => l_num_errors);
2819
2820 --Bug 3410681 fixed
2821
2822 IF l_debug_on THEN
2823 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.processStop',WSH_DEBUG_SV.C_PROC_LEVEL);
2824 END IF;
2825 --
2826 --
2827 WSH_INBOUND_UTIL_PKG.processStop
2828 (
2829 p_stop_id => next_leg_rec.pu_stop_id,
2830 p_status_code => next_leg_rec.pu_stop_statusCode,
2831 p_shipements_type_flag => next_leg_rec.pu_stop_shipTypeFlag,
2832 p_action_code => p_action_code,
2833 x_processed => l_stop_processed,
2834 x_return_status => l_return_status
2835 );
2836 --
2837 --
2838 --
2839 IF l_debug_on THEN
2840 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2841 WSH_DEBUG_SV.log(l_module_name,'l_stop_processed',l_stop_processed);
2842 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_util_core.api_post_call',WSH_DEBUG_SV.C_PROC_LEVEL);
2843 END IF;
2844 --
2845 wsh_util_core.api_post_call
2846 (
2847 p_return_status => l_return_status,
2848 x_num_warnings => l_num_warnings,
2849 x_num_errors => l_num_errors
2850 );
2851 --
2852 IF l_stop_processed = 'N'
2853 THEN
2854 EXIT first_leg_loop;
2855 END IF;
2856 --
2857 --
2858 IF l_stop_processed = 'Y'
2859 THEN
2860 --{
2861 IF l_debug_on THEN
2862 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.processStop',WSH_DEBUG_SV.C_PROC_LEVEL);
2863 END IF;
2864 --
2865 --
2866 WSH_INBOUND_UTIL_PKG.processStop
2867 (
2868 p_stop_id => next_leg_rec.do_stop_id,
2869 p_status_code => next_leg_rec.do_stop_statusCode,
2870 p_shipements_type_flag => next_leg_rec.do_stop_shipTypeFlag,
2871 p_action_code => p_action_code,
2872 x_processed => l_stop_processed,
2873 x_return_status => l_return_status
2874 );
2875 --
2876 --
2877 --
2878 IF l_debug_on THEN
2879 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2880 WSH_DEBUG_SV.log(l_module_name,'l_stop_processed',l_stop_processed);
2881 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_util_core.api_post_call',WSH_DEBUG_SV.C_PROC_LEVEL);
2882 END IF;
2883 --
2884 wsh_util_core.api_post_call
2885 (
2886 p_return_status => l_return_status,
2887 x_num_warnings => l_num_warnings,
2888 x_num_errors => l_num_errors
2889 );
2890 --
2891 IF l_stop_processed = 'N'
2892 THEN
2893 EXIT first_leg_loop;
2894 END IF;
2895 --}
2896 END IF;
2897 --
2898 --
2899 l_stop_locationId := next_leg_rec.do_stop_locationId;
2900 l_next_stop_Id := next_leg_rec.do_stop_id;
2901 --}
2902 END LOOP;
2903 --}
2904 END LOOP;
2905 --}
2906 END IF;
2907 --}
2908 END LOOP;
2909 --
2910 l_index := p_delivery_id_tab.NEXT(l_index);
2911 --}
2912 END LOOP;
2913 --
2914 --
2915 IF p_action_code = 'APPLY'
2916 THEN
2917 --{
2918 l_index := p_delivery_id_tab.FIRST;
2919 l_count := 0;
2920 --
2921 WHILE l_index IS NOT NULL
2922 LOOP
2923 --{
2924 FOR dlvy_rec IN dlvy_csr(p_delivery_id_tab(l_index))
2925 LOOP
2926 --{
2927 IF l_debug_on THEN
2928 WSH_DEBUG_SV.log(l_module_name,'Delivery Id',p_delivery_id_tab(l_index));
2929 WSH_DEBUG_SV.log(l_module_name,'dlvy_rec.planned_flag',dlvy_rec.planned_flag);
2930 WSH_DEBUG_SV.log(l_module_name,'dlvy_rec.ignore_for_planning',dlvy_rec.ignore_for_planning);
2931 END IF;
2932 --
2933 --
2934 IF dlvy_rec.planned_flag <> 'F'
2935 AND dlvy_rec.ignore_for_planning = 'N'
2936 THEN
2937 --{
2938 IF dlvy_rec.planned_flag <> 'Y'
2939 THEN
2940 l_count := l_count + 1;
2941 l_dlvy_tbl(l_count) := p_delivery_id_tab(l_index);
2942 END IF;
2943 --}
2944 END IF;
2945 --}
2946 END LOOP;
2947 --
2948 l_index := p_delivery_id_tab.NEXT(l_index);
2949 --}
2950 END LOOP;
2951 --
2952 --
2953 IF l_count > 0
2954 THEN
2955 --{
2956 IF l_debug_on THEN
2957 WSH_DEBUG_SV.log(l_module_name,'l_count',l_count);
2958 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.Plan',WSH_DEBUG_SV.C_PROC_LEVEL);
2959 END IF;
2960 --
2961 --
2962 WSH_NEW_DELIVERY_ACTIONS.Plan
2963 (
2964 p_del_rows => l_dlvy_tbl,
2965 x_return_status => l_return_status
2966
2967 );
2968 --
2969 --
2970 IF l_debug_on THEN
2971 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2972 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2973 END IF;
2974 --
2975 --
2976 wsh_util_core.api_post_call
2977 (
2978 p_return_status => l_return_status,
2979 x_num_warnings => l_num_warnings,
2980 x_num_errors => l_num_errors
2981 );
2982 --}
2983 END IF;
2984 --
2985 --
2986 IF l_debug_on THEN
2987 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.reRateDeliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
2988 END IF;
2989 --
2990 --
2991 WSH_INBOUND_UTIL_PKG.reRateDeliveries
2992 (
2993 p_delivery_id_tab => p_delivery_id_tab,
2994 x_return_status => l_return_status
2995 );
2996 --
2997 --
2998 IF l_debug_on THEN
2999 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3000 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3001 END IF;
3002 --
3003 --
3004 wsh_util_core.api_post_call
3005 (
3006 p_return_status => l_return_status,
3007 x_num_warnings => l_num_warnings,
3008 x_num_errors => l_num_errors
3009 );
3010 --}
3011 END IF;
3012
3013 --Bug 3410681 fixed.
3014 --Give a warning, if trip has any not closed stop associate to it.
3015 IF (p_action_code = 'APPLY' AND p_transaction_code = 'RECEIPT') THEN
3016 --{
3017
3018 --Check for any stops not closed for trip.
3019 l_index := l_trip_tbl.FIRST;
3020 WHILE (l_index IS NOT NULL) LOOP
3021
3022 OPEN stop_csr(l_index);
3023 FETCH stop_csr INTO l_stop_count;
3024
3025 IF l_debug_on THEN
3026 WSH_DEBUG_SV.log(l_module_name,'trip_id',l_index);
3027 WSH_DEBUG_SV.log(l_module_name,'l_stop_count',l_stop_count);
3028 END IF;
3029
3030 IF (l_stop_count > 0) THEN
3031 FND_MESSAGE.SET_NAME('WSH', 'WSH_IB_TRIP_NOT_CLOSE');
3032 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(l_index));
3033 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
3034
3035 l_num_warnings := l_num_warnings + 1;
3036 l_stop_count := 0;
3037 END IF;
3038 CLOSE stop_csr;
3039
3040 l_index := l_trip_tbl.NEXT(l_index);
3041 END LOOP;
3042
3043 l_index := l_trip_ext_tbl.FIRST;
3044 WHILE (l_index IS NOT NULL) LOOP
3045
3046 OPEN stop_csr(l_index);
3047 FETCH stop_csr INTO l_stop_count;
3048 IF (l_stop_count > 0) THEN
3049 FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_NOT_CLOSE');
3050 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(l_index));
3051 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
3052 l_num_warnings := l_num_warnings + 1;
3053 l_stop_count := 0;
3054 END IF;
3055 CLOSE stop_csr;
3056
3057 l_index := l_trip_ext_tbl.NEXT(l_index);
3058 END LOOP;
3059 --}
3060 END IF;
3061 --Bug 3410681 fixed.
3062
3063 --
3064 --
3065 IF l_debug_on THEN
3066 WSH_DEBUG_SV.logmsg(l_module_name,
3067 'Number of Errors='||l_num_errors||',Number of Warnings='||l_num_warnings);
3068 END IF;
3069 --
3070 IF l_num_errors > 0
3071 THEN
3072 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3073 ELSIF l_num_warnings > 0
3074 THEN
3075 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3076 ELSE
3077 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3078 END IF;
3079 --
3080 --
3081 -- Debug Statements
3082 --
3083 IF l_debug_on THEN
3084 WSH_DEBUG_SV.pop(l_module_name);
3085 END IF;
3086 --
3087 --}
3088 EXCEPTION
3089 --{
3090 --
3091 WHEN FND_API.G_EXC_ERROR THEN
3092
3093 --ROLLBACK TO open_stop_begin_sp;
3094 --
3095 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3096 --
3097 IF l_debug_on THEN
3098 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3099 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3100 END IF;
3101 --
3102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3103
3104 --ROLLBACK TO open_stop_begin_sp;
3105 --
3106 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3107 --
3108 IF l_debug_on THEN
3109 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3110 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3111 END IF;
3112 --
3113 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
3114 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3115 --
3116 IF l_debug_on THEN
3117 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3118 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
3119 END IF;
3120 WHEN others THEN
3121 wsh_util_core.default_handler('WSH_INBOUND_UTIL_PKG.setTripStopStatus',l_module_name);
3122 --
3123 --ROLLBACK TO open_stop_begin_sp;
3124 --
3125 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3126 --
3127 -- Debug Statements
3128 --
3129 IF l_debug_on THEN
3130 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3131 END IF;
3132 --
3133 --}
3134 END setTripStopStatus;
3135
3136 -- Start of comments
3137 -- API name : CONVERT_QUANTITY
3138 -- Type : Public
3139 -- Pre-reqs : None.
3140 -- Function : This API is used to convert the quantity of an item
3141 -- from one UOM code to another,Like 'DOZ' to 'EA'.
3142 -- Parameters :
3143 -- IN:
3144 -- p_inv_item_id IN NUMBER DEFAULT NULL
3145 -- The inventory item ID for which the conversion is done.
3146 -- p_organization_id IN NUMBER
3147 -- The organization of the Item.
3148 -- p_quantity IN NUMBER
3149 -- p_quantity is the quantity to be converted.
3150 -- p_qty_uom_code IN VARCHAR2
3151 -- p_qty_uom_code is the code which represents the curent uom code
3152 -- of the input p_quantity.
3153 -- IN OUT:
3154 -- p_primary_uom_code IN OUT NOCOPY VARCHAR2
3155 -- p_primary_uom_code is the uom code into which the quantity has to be
3156 -- converted.
3157 -- OUT:
3158 -- x_conv_qty OUT NOCOPY NUMBER
3159 -- x_conv_qty will have the converted quantity in case of successfull
3160 -- conversion.
3161 -- Version : 1.0
3162 -- Previous version 1.0
3163 -- Initial version 1.0
3164 -- End of comments
3165
3166 PROCEDURE convert_quantity
3167 ( p_inv_item_id IN NUMBER DEFAULT NULL,
3168 p_organization_id IN NUMBER,
3169 p_primary_uom_code IN OUT NOCOPY VARCHAR2,
3170 p_quantity IN NUMBER ,
3171 p_qty_uom_code IN VARCHAR2,
3172 x_conv_qty OUT NOCOPY NUMBER,
3173 x_return_status IN OUT NOCOPY VARCHAR2
3174 ) IS
3175
3176 --To get Primary UOM code for the given inventory item.
3177 CURSOR c_inventory_item_info(v_inventory_item_id number,
3178 v_organization_id number) is
3179 SELECT primary_uom_code
3180 FROM mtl_system_items
3181 WHERE inventory_item_id = v_inventory_item_id
3182 AND organization_id = v_organization_id;
3183
3184 l_return_status VARCHAR2(1);
3185 l_is_org_type_opm VARCHAR2(1) := FND_API.G_FALSE;
3186 l_debug_on BOOLEAN;
3187 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONVERT_QUANTITY';
3188
3189 BEGIN
3190
3191
3192 --
3193 -- Debug Statements
3194 --
3195 IF l_debug_on THEN
3196 WSH_DEBUG_SV.push(l_module_name);
3197 WSH_DEBUG_SV.log(l_module_name,'p_inv_item_id',p_inv_item_id);
3198 WSH_DEBUG_SV.log(l_module_name,'p_organization_id',p_organization_id);
3199 WSH_DEBUG_SV.log(l_module_name,'p_primary_uom_code',p_primary_uom_code);
3200 WSH_DEBUG_SV.log(l_module_name,'p_quantity',p_quantity);
3201 WSH_DEBUG_SV.log(l_module_name,'p_qty_uom_code',p_qty_uom_code);
3202 END IF;
3203 --
3204
3205
3206 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3207
3208 --assigning p_quanity to x_conv quantity
3209 x_conv_qty := p_quantity;
3210
3211 --The Items primary UOM and Current UOM are not NULL.
3212 IF( (p_primary_uom_code IS NOT NULL) AND (p_qty_uom_code IS NOT NULL) )THEN
3213 -- If the UOM to be converted is same as the primary UOM.
3214 IF p_primary_uom_code = p_qty_uom_code THEN
3215
3216 --
3217 -- Debug Statements
3218 --
3219 IF l_debug_on THEN
3220 WSH_DEBUG_SV.pop(l_module_name);
3221 END IF;
3222 --
3223 return;
3224 END IF;
3225 END IF;
3226
3227 --If the primary UOM is not passed to this API.Then find it in mtl_system_items table.
3228 IF (p_inv_item_id IS NOT NULL ) AND (p_primary_uom_code IS NULL) THEN
3229 OPEN c_inventory_item_info(p_inv_item_id,p_organization_id);
3230 FETCH c_inventory_item_info INTO p_primary_uom_code;
3231 CLOSE c_inventory_item_info;
3232 END IF;
3233
3234 --If the UOMs (Primary Vs Current) are different then do a conversion
3235
3236 --If the i/p inventory ID is null
3237 -- HW OPMCONV - Removed code forking
3238 -- New condition is the OR condition
3239 IF (p_inv_item_id IS NULL
3240 OR p_primary_uom_code <> p_qty_uom_code) THEN
3241 --Call function WSH_WV_UTILS.CONVERT_UOM which returns the converted quantity.
3242 x_conv_qty := wsh_wv_utils.convert_uom(
3243 from_uom => p_qty_uom_code,
3244 to_uom => p_primary_uom_code,
3245 quantity => p_quantity,
3246 item_id => p_inv_item_id,
3247 p_max_decimal_digits => WSH_UTIL_CORE.C_MAX_DECIMAL_DIGITS);-- RV DEC_QTY
3248
3249
3250 -- HW OPMCONV - Removed code forking
3251
3252 END IF;
3253
3254 --}
3255
3256 --
3257 -- Debug Statements
3258 --
3259 IF l_debug_on THEN
3260 WSH_DEBUG_SV.pop(l_module_name);
3261 END IF;
3262 --
3263
3264 EXCEPTION
3265 WHEN OTHERS THEN
3266 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3267 WSH_UTIL_CORE.Default_Handler('WSH_INBOUND_UTIL_PKG.convert_quantity',l_module_name);
3268 IF l_debug_on THEN
3269 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3270 END IF;
3271
3272 END convert_quantity;
3273
3274 -- Start of comments
3275 -- API name : GET_OUTERMOST_LPN
3276 -- Type : Public
3277 -- Pre-reqs : None.
3278 -- Function : This API is used to determine the outermost LPN id for
3279 -- a given LPN id.
3280 -- The input p_lpn_context should be equal to 7 in the case of
3281 -- ASN/RECEIPT.
3282 -- If the outermost lpn id is found for the given lpn then
3283 -- it is set in th variable x_outermost_lpn.
3284 -- If the outermost lpn id is not found for the given lpn then
3285 -- NULL is set to the variable x_outermost_lpn.
3286 -- Parameters :
3287 -- IN:
3288 -- p_lpn_id IN NUMBER
3289 -- The lpn_id for which the outermost LPN has to be found.
3290 -- p_shipment_header_id IN NUMBER
3291 -- The shipment header ID of the given i/p LPN ID.
3292 -- p_lpn_context IN NUMBER
3293 --
3294 -- IN OUT:
3295 --
3296 -- OUT:
3297 -- x_outermost_lpn OUT NOCOPY NUMBER
3298 -- The outermost LPN ID derived for the i/p LPN ID.
3299 -- x_outermost_lpn_name OUT NOCOPY VARCHAR2
3300 -- The outermost LPNs name.
3301 -- x_return_status OUT NOCOPY VARCHAR2
3302 -- Version : 1.0
3303 -- Previous version 1.0
3304 -- Initial version 1.0
3305 -- End of comments
3306
3307 PROCEDURE GET_OUTERMOST_LPN(
3308 p_lpn_id IN NUMBER,
3309 p_shipment_header_id IN NUMBER,
3310 p_lpn_context IN NUMBER,
3311 x_outermost_lpn OUT NOCOPY NUMBER,
3312 x_outermost_lpn_name OUT NOCOPY VARCHAR2,
3313 x_return_status OUT NOCOPY VARCHAR2)
3314 IS
3315
3316 --Cursor to get the outermost LPN for a given LPN.
3317 CURSOR get_outer_most_lpn_id(p_lpn_id NUMBER,
3318 p_shipment_header_id NUMBER,
3319 p_lpn_context NUMBER)
3320 IS
3321 select
3322 --lpn_id
3323 parent_lpn_id,
3324 parent_license_plate_number
3325 from
3326 wms_lpn_histories
3327 where
3328 --parent_lpn_id is null and
3329 source_header_id = p_shipment_header_id
3330 and source_type_id = 1
3331 and lpn_context = p_lpn_context
3332 start with parent_lpn_id = p_lpn_id
3333 connect by prior parent_lpn_id = lpn_id;
3334
3335
3336 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_OUTERMOST_LPN';
3337
3338
3339 l_debug_on BOOLEAN;
3340
3341 BEGIN
3342
3343 --
3344 -- Debug Statements
3345 --
3346 IF l_debug_on THEN
3347 WSH_DEBUG_SV.push(l_module_name);
3348 WSH_DEBUG_SV.log(l_module_name,'p_lpn_id',p_lpn_id);
3349 WSH_DEBUG_SV.log(l_module_name,'p_shipment_header_id',p_shipment_header_id);
3350 WSH_DEBUG_SV.log(l_module_name,'p_lpn_context',p_lpn_context);
3351 END IF;
3352 --
3353
3354
3355 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3356 x_outermost_lpn := NULL;
3357 x_outermost_lpn_name := NULL;
3358
3359 --The loop goes up by one LPN level(Parent LPN) for the given i/p LPN, during each iteration of the loop.
3360 FOR get_outer_most_lpn_id_rec IN get_outer_most_lpn_id(p_lpn_id ,
3361 p_shipment_header_id ,
3362 p_lpn_context )
3363 LOOP
3364
3365 IF l_debug_on THEN
3366 WSH_DEBUG_SV.log(l_module_name,'get_outer_most_lpn_id_rec.parent_lpn_id',get_outer_most_lpn_id_rec.parent_lpn_id);
3367 WSH_DEBUG_SV.log(l_module_name,'get_outer_most_lpn_id_rec.parent_license_plate_number',get_outer_most_lpn_id_rec.parent_license_plate_number);
3368 END IF;
3369 x_outermost_lpn := get_outer_most_lpn_id_rec.parent_lpn_id;
3370 x_outermost_lpn_name := get_outer_most_lpn_id_rec.parent_license_plate_number;
3371
3372 END LOOP;
3373 /*
3374 FETCH get_outer_most_lpn_id INTO x_outermost_lpn;
3375 CLOSE get_outer_most_lpn_id;
3376
3377 IF x_outermost_lpn IS NULL THEN
3378 x_outermost_lpn := p_lpn_id;
3379 END IF;
3380 */
3381
3382 --
3383 -- Debug Statements
3384 --
3385 IF l_debug_on THEN
3386 WSH_DEBUG_SV.pop(l_module_name);
3387 END IF;
3388 --
3389
3390 EXCEPTION
3391 WHEN OTHERS THEN
3392 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3393 WSH_UTIL_CORE.Default_Handler('WSH_INBOUND_UTIL_PKG.GET_OUTERMOST_LPN',l_module_name);
3394 IF l_debug_on THEN
3395 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3396 END IF;
3397
3398 END GET_OUTERMOST_LPN;
3399
3400
3401 --HACMS {
3402 FUNCTION Is_Routing_Response_Send(p_delivery_detail_id NUMBER,
3403 x_routing_response_id OUT NOCOPY NUMBER) RETURN boolean
3404 IS
3405
3406 l_debug_on BOOLEAN;
3407 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Is_Routing_Response_Send';
3408
3409 CURSOR get_routing_response IS
3410 SELECT wnd.routing_response_id
3411 FROM wsh_new_deliveries wnd,
3412 wsh_delivery_assignments_v wda
3413 WHERE wnd.delivery_id = wda.delivery_id
3414 AND wda.delivery_detail_id = p_delivery_detail_id
3415 AND wnd.routing_response_id IS NOT NULL;
3416
3417 l_status boolean:=false;
3418 BEGIN
3419 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3420 IF l_debug_on IS NULL THEN
3421 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3422 END IF;
3423
3424 IF l_debug_on THEN
3425 WSH_DEBUG_SV.push(l_module_name);
3426 WSH_DEBUG_SV.log(l_module_name,'p_delivery_detail_id',p_delivery_detail_id);
3427 END IF;
3428
3429 OPEN get_routing_response;
3430 FETCH get_routing_response INTO x_routing_response_id;
3431 IF (get_routing_response%FOUND) THEN
3432 l_status:=true;
3433 END IF;
3434 CLOSE get_routing_response;
3435
3436 IF l_debug_on THEN
3437 WSH_DEBUG_SV.log(l_module_name,'x_routing_response_id',x_routing_response_id);
3438 WSH_DEBUG_SV.pop(l_module_name);
3439 END IF;
3440
3441 RETURN l_status;
3442
3443 EXCEPTION
3444 WHEN OTHERS THEN
3445 IF l_debug_on THEN
3446 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
3447 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3448 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3449 END IF;
3450 RETURN false;
3451 END Is_Routing_Response_Send;
3452 --HACMS }
3453
3454 END WSH_INBOUND_UTIL_PKG;