1 PACKAGE BODY WSH_CUSTOM_PUB as
2 /* $Header: WSHCSPBB.pls 120.2.12010000.2 2008/10/17 07:29:40 brana ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_CUSTOM_PUB';
4 --
5 -- Procedure: Delivery_Name
6 -- Parameters: All Attributes of a Delivery Record
7 -- Description: This procedure will create a delivery. It will
8 -- return to the use the delivery_id and name (if
9 -- not provided as a parameter.
10 --
11
12 FUNCTION Delivery_Name
13 (
14 p_delivery_id IN NUMBER,
15 p_delivery_info IN WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type
16 ) RETURN VARCHAR2 IS
17
18 BEGIN
19
20 RETURN (to_char(p_delivery_id));
21
22 END Delivery_Name;
23
24 FUNCTION Trip_Name
25 (
26 p_trip_id IN NUMBER,
27 p_trip_info IN wsh_trips_pvt.trip_rec_type
28 ) RETURN VARCHAR2 IS
29 BEGIN
30 RETURN (to_char(p_trip_id));
31 END Trip_Name;
32
33 --
34 -- Function: Run_PR_SMC_SS_Parallel
35 -- Description: This function is designed for the user to
36 -- customize the running of Pick Release for Ship Sets and SMCs
37 -- in parallel with Regular Items.
38 -- If this is set to 'Y', then Ship Sets/SMCs are not given a
39 -- priority over Regular Items. This can lead to scenarios where
40 -- Ship Sets/SMCs are backordered while Regular Items are picked.
41 -- Oracle Default: Ship Sets/SMCs are not run in Parallel
42 -- Function Default: 'N'
43 --
44
45 FUNCTION Run_PR_SMC_SS_Parallel RETURN VARCHAR2
46 IS
47
48 BEGIN
49 RETURN 'N';
50 END Run_PR_SMC_SS_Parallel;
51
52
53 --
54 -- Function: Credit_Check_Details_Option
55 -- Description: This function is designed for the user to
56 -- customize credit checking for details.
57 -- By default, credit check will be done for all details ('A')
58 -- If the credit check is to be run only for Non-Backordered details,
59 -- then this is set to 'R'.
60 -- If the credit check is to be run only for Backordered details,
61 -- then this is set to 'B'.
62 -- Oracle Default: Credit check for all details.
63 -- Function Default: 'A'
64 --
65
66 FUNCTION Credit_Check_Details_Option RETURN VARCHAR2
67 IS
68
69 BEGIN
70 RETURN 'A';
71 END Credit_Check_Details_Option;
72
73 --Added as a part of bugfix 4995478
74 -- Procedure: ui_location_code
75 -- Parameters:
76
77 -- 1. p_location_type 'HR' stands for internal location
78 -- p_location_type 'HZ' Stands for external location
79 -- 2. p_party_site_numberTbl corresponds to party_site_number in HZ tables (External location)
80 -- 3. p_location_codeTbl corresponds to Location_code in HR tables (Internal location)
81 -- 4. p_address_1Tbl/2Tbl corresponds to address1/2 in HZ table and address_line_1/2 in HR table.
82 -- 5. p_cityTbl corresponds to city in HZ table and town_or_city in HR table.
83 -- 6. p_stateTbl corresponds to state in HZ table region_2 in HR table
84 -- 7. p_provinceTbl corresponds to province in HZ table and region3 in HR table
85 -- 8. p_countyTbl corresponds to region1 in HR table
86 -- 9. p_postal_codeTbl and p_countryTbl corresponds to postal_code and country in HZ/HR table
87 --10. For p_location_type 'HR', p_party_site_numberTbl will be passed as NULL and
88 -- for p_location_type 'HZ', p_location_codeTbl will be passed as NULL.
89
90 -- Description :
91 -- 1) The procedure is designed for the user to customize the location (ui_location_code) information
92 --- displayed in Shipping Forms.
93 -- 2) All required parameter are passed for external and internal location.
94 -- 3)To use this procedure user has to set the value of PL/SQL variable
95 -- x_use_custom_ui_location to 'Y'.
96 -- 4) The function must not return more than 500 characters(or 500 bytes in multi-byte character set).
97 -- Please use substrb function to limit the total length and/or each individual component length..
98 --5) For custom changes to take affect,user has to run 'Import Shipping Location' Concurrent Program.
99
100 PROCEDURE ui_location_code (
101 p_location_type IN VARCHAR2,
102 p_location_idTbl IN WSH_LOCATIONS_PKG.ID_Tbl_Type,
103 p_address_1Tbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type,
104 p_address_2Tbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
105 p_countryTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
106 p_stateTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
107 p_provinceTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
108 p_countyTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
109 p_cityTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
110 p_postal_codeTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
111 p_party_site_numberTbl IN WSH_LOCATIONS_PKG.LocationCode_Tbl_Type,
112 p_location_codeTbl IN WSH_LOCATIONS_PKG.LocationCode_Tbl_Type,
113 x_use_custom_ui_location OUT NOCOPY VARCHAR2,
114 x_custom_ui_loc_codeTbl OUT NOCOPY WSH_LOCATIONS_PKG.LocationCode_Tbl_Type
115 ) IS
116 l_sqlcode NUMBER;
117 l_sqlerr VARCHAR2(2000);
118 BEGIN
119
120 x_use_custom_ui_location := 'N';
121 ---Sample code start--
122 /*
123 IF p_location_type = 'HZ' THEN
124
125 FOR i IN p_location_idTbl.FIRST..p_location_idTbl.LAST
126 LOOP
127 x_custom_ui_loc_codeTbl(i) := substrb((p_party_site_numberTbl(i)||' : '||p_address_1Tbl(i)||'-'||p_address_2Tbl(i)||'-'||p_cityTbl(i)||'-'||nvl(p_stateTbl(i),p_provinceTbl(i))||'-'|| p_postal_codeTbl(i)||'-'||p_countryTbl(i)),1,500);
128 END LOOP;
129 ELSIF p_location_type = 'HR' THEN
130
131 FOR i IN p_location_idTbl.FIRST..p_location_idTbl.LAST
132 LOOP
133 x_custom_ui_loc_codeTbl(i) := substrb((p_location_codeTbl(i)||' : '||p_address_1Tbl(i)||'-'||p_address_2Tbl(i)||'-'||p_cityTbl(i)||'-'||p_stateTbl(i)||'-'|| p_postal_codeTbl(i)||'-'||p_countryTbl(i)),1,500);
134 END LOOP;
135 END IF;
136
137 EXCEPTION
138 WHEN others THEN
139 l_sqlcode := SQLCODE;
140 l_sqlerr := SQLERRM;
141 WSH_UTIL_CORE.printmsg('In the Others Exception of WSH_CUSTOM_PUB.ui_location_code');
142 WSH_UTIL_CORE.printmsg(l_sqlcode);
143 WSH_UTIL_CORE.printmsg(l_sqlerr);
144 ---Sample code end --
145 */
146 END ui_location_code;
147
148
149
150 -- CUSTOMIZE THE PROCEDURE
151 PROCEDURE Shipped_Lines(
152 p_source_header_id in number,
153 p_source_code in varchar2,
154 p_contact_type in varchar2,
155 p_contact_id in number,
156 p_last_notif_date in date,
157 p_shipped out NOCOPY boolean,
158 p_shipped_lines out NOCOPY varchar2) IS
159
160 CURSOR c_shipped_lines(
161 p_source_header_id in number,
162 p_source_code in varchar2,
163 p_contact_type in varchar2,
164 p_contact_id in number,
165 p_last_notif_date in date) is
166 SELECT
167 msi.segment1,
168 msi.description,
169 lpad(to_char(wnd.initial_pickup_date,'MM/DD/YYYY'),12),
170 wnd.waybill,
171 sum(nvl(wdd.shipped_quantity,0))
172 FROM
173 wsh_delivery_details wdd,
174 wsh_delivery_assignments wda,
175 wsh_new_deliveries wnd,
176 mtl_system_items msi
177 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
178 AND wda.delivery_id = wnd.delivery_id
179 AND wnd.status_code in ('IT','CL')
180 AND wnd.initial_pickup_date > p_last_notif_date
181 AND wdd.inventory_item_id = msi.inventory_item_id
182 AND wdd.organization_id = msi.organization_id
183 AND wdd.source_header_id = p_source_header_id
184 AND wdd.source_code = p_source_code
185 AND nvl(wnd.shipment_direction, 'O') IN ('O','IO') --J Inbound Logistics jckwok
186 AND decode(p_contact_type,
187 'SHIP_TO',wdd.ship_to_contact_id,
188 'SOLD_TO',wdd.sold_to_contact_id,
189 wdd.customer_id) = p_contact_id
190 GROUP BY
191 wdd.source_header_number,
192 wdd.source_header_type_id,
193 wdd.source_line_id,
194 wdd.inventory_item_id,
195 msi.segment1,
196 msi.description,
197 wdd.src_requested_quantity,
198 wnd.initial_pickup_date,
199 wnd.waybill
200 HAVING
201 sum(nvl(wdd.shipped_quantity,0)) > 0;
202
203 l_shipped boolean;
204 l_shipped_lines varchar2(32750);
205 l_part_number varchar2(40);
206 l_part_desc varchar2(240);
207 l_ship_qty number;
208 l_ship_date varchar2(12);
209 l_waybill varchar2(30);
210
211 BEGIN
212
213
214 -- 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
215 l_shipped_lines := ' Quantites';
216 l_shipped_lines := l_shipped_lines|| fnd_global.newline;
217 l_shipped_lines := l_shipped_lines|| 'Part Number Part Description Ship Date Shipped Waybill ';
218 l_shipped_lines := l_shipped_lines|| fnd_global.newline;
219 l_shipped_lines := l_shipped_lines|| '------------------- --------------------------------- --------- ------------ ------------';
220
221 l_shipped := FALSE;
222 open c_shipped_lines(p_source_header_id, p_source_code, p_contact_type, p_contact_id, p_last_notif_date);
223 LOOP
224 fetch c_shipped_lines
225 into l_part_number,
226 l_part_desc,
227 l_ship_date,
228 l_waybill,
229 l_ship_qty;
230 exit when c_shipped_lines%NOTFOUND;
231 l_shipped := TRUE;
232
233 l_shipped_lines := l_shipped_lines|| fnd_global.newline;
234 l_shipped_lines := l_shipped_lines
235 || rpad(substr(l_part_number,1,19) ,19) ||' '
236 || rpad(substr(l_part_desc,1,33) ,33) ||' '
237 || lpad(substr(to_char(l_ship_qty),1,9),9) ||' '
238 || rpad(substr(l_ship_date,1,12) ,12) ||' '
239 || rpad(substr(l_waybill,1,12) ,12);
240 END LOOP;
241 close c_shipped_lines;
242
243 p_shipped := l_shipped;
244 if (l_shipped) then
245 p_shipped_lines := l_shipped_lines;
246 else
247 p_shipped_lines := NULL;
248 end if;
249
250 return;
251 END Shipped_Lines;
252
253 -- CUSTOMIZE THE PROCEDURE
254 PROCEDURE Backordered_Lines(
255 p_source_header_id in number,
256 p_source_code in varchar2,
257 p_contact_type in varchar2,
258 p_contact_id in number,
259 p_last_notif_date in date,
260 p_backordered out NOCOPY boolean,
261 p_backordered_lines out NOCOPY varchar2) IS
262 CURSOR c_backordered_lines(
263 p_source_header_id in number,
264 p_source_code in varchar2,
265 p_contact_type in varchar2,
266 p_contact_id in number,
267 p_last_notif_date in date) is
268 SELECT
269 msi.segment1,
270 msi.description,
271 sum(nvl(wdd.requested_quantity,0))
272 FROM
273 wsh_delivery_details wdd,
274 wsh_delivery_assignments wda,
275 wsh_new_deliveries wnd,
276 mtl_system_items msi
277 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
278 AND wdd.date_scheduled < sysdate
279 --AND wdd.date_scheduled > p_last_notif_date
280 --AND wdd.released_status != 'C'
281 AND wda.delivery_id = wnd.delivery_id (+)
282 AND nvl(wnd.status_code,'XX') not in ('IT','CL')
283 AND wdd.inventory_item_id = msi.inventory_item_id
284 AND wdd.organization_id = msi.organization_id
285 AND wdd.source_header_id = p_source_header_id
286 AND wdd.source_code = p_source_code
287 AND nvl(wdd.line_direction, 'O') IN ('O','IO') --J Inbound Logistics jckwok
288 AND decode(p_contact_type,
289 'SHIP_TO',wdd.ship_to_contact_id,
290 'SOLD_TO',wdd.sold_to_contact_id,
291 wdd.customer_id) = p_contact_id
292 GROUP BY
293 wdd.source_header_number,
294 wdd.source_header_type_id,
295 wdd.source_line_id,
296 wdd.inventory_item_id,
297 msi.segment1,
298 msi.description,
299 wdd.src_requested_quantity
300 HAVING
301 sum(nvl(wdd.requested_quantity,0)) > 0;
302
303 l_backordered boolean;
304 l_backordered_lines varchar2(32750);
305 l_part_number varchar2(40);
306 l_part_desc varchar2(240);
307 l_backorder_qty number;
308
309 BEGIN
310 -- 12345678901234567890123456789012345678901234567890123456789012345678901234567890
311 l_backordered_lines := ' Quantity';
312 l_backordered_lines := l_backordered_lines|| fnd_global.newline;
313 l_backordered_lines := l_backordered_lines|| 'Part Number Part Description Backordered';
314 l_backordered_lines := l_backordered_lines|| fnd_global.newline;
315 l_backordered_lines := l_backordered_lines|| '------------------- --------------------------------- -----------';
316
317 l_backordered := FALSE;
318 open c_backordered_lines(p_source_header_id, p_source_code, p_contact_type, p_contact_id, p_last_notif_date);
319 LOOP
320 fetch c_backordered_lines
321 into l_part_number,
322 l_part_desc,
323 l_backorder_qty;
324 exit when c_backordered_lines%NOTFOUND;
325 l_backordered := TRUE;
326
327 l_backordered_lines := l_backordered_lines|| fnd_global.newline;
328 l_backordered_lines := l_backordered_lines
329 || rpad(substr(l_part_number,1,19) ,19) ||' '
330 || rpad(substr(l_part_desc,1,33) ,33) ||' '
331 || lpad(substr(to_char(l_backorder_qty),1,11),11);
332 END LOOP;
333 close c_backordered_lines;
334
335 p_backordered := l_backordered;
336 if (l_backordered) then
337 p_backordered_lines := l_backordered_lines ;
338 else
339 p_backordered_lines := NULL;
340 end if;
341
342 END Backordered_Lines ;
343
344 -- CUSTOMIZE THIS PROCEDURE
345 PROCEDURE Start_Workflow(
346 p_source_header_id in number,
347 p_source_code in varchar2,
348 p_contact_type in varchar2,
349 p_contact_id in number,
350 p_result out NOCOPY boolean) IS
351 BEGIN
352 p_result := FALSE;
353 END Start_Workflow;
354
355 --PROCEDURE calculate_tp_dates
356 --Based on different parameters from OM, customers can customize their
357 --calculation of the TP dates (Earliest/Latest Ship Dates and Earliest/Latest Delivery Dates).
358 --These will be then used for population at the delivery detail level and will
359 --get propogated upto container or delivery levels at action points such as
360 --assign/pack etc.
361 --NOTE : x_modified out parameter must be returned as 'Y' in order to use this
362 --customized calculation
363
364 PROCEDURE calculate_tp_dates(
365 p_source_line_id NUMBER,
366 p_source_code IN VARCHAR2,
367 x_earliest_pickup_date OUT NOCOPY DATE,
368 x_latest_pickup_date OUT NOCOPY DATE,
369 x_earliest_dropoff_date OUT NOCOPY DATE,
370 x_latest_dropoff_date OUT NOCOPY DATE,
371 x_modified OUT NOCOPY VARCHAR2
372 ) IS
373
374 l_earliest_pickup_date DATE;
375 l_latest_pickup_date DATE;
376 l_earliest_dropoff_date DATE;
377 l_latest_dropoff_date DATE;
378
379 BEGIN
380 --x_modified must be changed to 'Y' if you're customizing this procedure
381 --to calculate dates on your own
382 x_modified:='N';
383 x_earliest_pickup_date :=l_earliest_pickup_date;
384 x_latest_pickup_date :=l_latest_pickup_date;
385 x_earliest_dropoff_date:=l_earliest_dropoff_date;
386 x_latest_dropoff_date :=l_latest_dropoff_date;
387
388 END calculate_tp_dates;
389
390 -- Procedure Override_RIQ_XML_Attributes
391 -- Provides a way to override the attributes: Weight, Volume, Item Dimensions: Length, Width and Height
392 -- for any of the following RIQ actions:
393 -- 1) Choose Ship Method
394 -- 2) Get Ship Method
395 -- 3) Get Ship Method and Rates
396 -- 4) Get Freight Rates
397 -- All the attributes values should be Non-Negative.
398 -- For the Header Level (Consolidation), p_line_id_tab will have more than 1
399 -- record containing all the order line_ids that have been consolidated at the header level
400 -- The only attributes that can be overridden at the Header Level are Weight and Volume.
401 -- For the Line Level/Ship Unit Level, p_line_id_tab will have only 1 record
402 -- with the order line_id and all the attributes can be overridden.
403 -- For Item Dimensions values to be sent as part of RIQ XML, the OTM Item Dimension UOM must be defined
404 -- and the Item Dimensions (Length, Width and Height) should all have valid values.
405 PROCEDURE Override_RIQ_XML_Attributes(
406 p_line_id_tab IN WSH_UTIL_CORE.Id_Tab_Type,
407 x_weight IN OUT NOCOPY NUMBER,
408 x_volume IN OUT NOCOPY NUMBER,
409 x_length IN OUT NOCOPY NUMBER,
410 x_height IN OUT NOCOPY NUMBER,
411 x_width IN OUT NOCOPY NUMBER,
412 x_return_status OUT NOCOPY VARCHAR2
413 ) IS
414
415 BEGIN
416
417 -- Initializing API return status to Success, please do not change this
418 x_return_status := FND_API.G_RET_STS_SUCCESS;
419
420 -- p_line_tab contains the Order Line_id(s) from oe_order_lines_all table
421 -- This can be used to derive the corresponding line level information
422
423 /* Sample Code : Please ensure that values are passed correctly back to caller
424 -- Header Level (p_line_id_tab contains all the order line_ids that are consolidated)
425 IF p_line_id_tab.COUNT > 1 THEN
426 x_weight :=
427 x_volume :=
428
429 -- Line Level for a specific order line_id
430 ELSIF p_line_id_tab.COUNT = 1 THEN
431 x_weight :=
432 x_volume :=
433 x_length :=
434 x_height :=
435 x_width :=
436
437 END IF;
438 */
439
440 EXCEPTION
441 WHEN others THEN
442 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
443 END Override_RIQ_XML_Attributes;
444
445 -- Bug 7131800
446 FUNCTION Cancel_Unpicked_Details_At_ITS(
447 p_source_header_id IN NUMBER,
448 p_source_line_id IN NUMBER,
449 p_source_line_set_id IN NUMBER,
450 p_remain_details_id IN WSH_UTIL_CORE.Id_Tab_Type
451 ) RETURN VARCHAR2 IS
452 l_debug_on BOOLEAN;
453 l_cancel_flag VARCHAR2(1):= 'Y'; -- default Value is to Cancel (old Behaviour)
454 --
455 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||G_PKG_NAME || '.' ||'Cancel_Unpicked_Details_At_ITS';
456 --
457 BEGIN
458 --
459 -- Debug Statements
460 --
461 --
462 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
463 --
464 IF l_debug_on IS NULL
465 THEN
466 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
467 END IF;
468 --
469 IF l_debug_on THEN
470 WSH_DEBUG_SV.push(l_module_name);
471 --
472 WSH_DEBUG_SV.log(l_module_name,'p_source_line_id ', p_source_line_id);
473 WSH_DEBUG_SV.log(l_module_name,'p_source_line_set_id ', p_source_line_set_id);
474 WSH_DEBUG_SV.log(l_module_name,'p_source_header_id ', p_source_header_id);
475 END IF;
476 --
477 -- { Section to be Modified by Customers
478
479 l_cancel_flag := 'Y';
480
481 -- } End Section to be Modified by Customers
482
483 ---
484 IF l_debug_on THEN
485 WSH_DEBUG_SV.log(l_module_name,'l_cancel_flag ', l_cancel_flag);
486 WSH_DEBUG_SV.pop(l_module_name);
487 END IF;
488
489 RETURN (l_cancel_flag);
490
491 END Cancel_Unpicked_Details_At_ITS;
492
493 END WSH_CUSTOM_PUB;
[Home] [Help]