[Home] [Help]
PACKAGE BODY: APPS.WSH_PR_CREATE_DELIVERIES
Source
1 PACKAGE BODY WSH_PR_CREATE_DELIVERIES AS
2 /* $Header: WSHPRDLB.pls 115.4 99/07/16 08:19:53 porting ship $ */
3
4 --
5 -- Package
6 -- WSH_PR_CREATE_DELIVERIES
7 --
8 -- Purpose
9 --
10
11 --
12 -- PACKAGE CONSTANTS
13 --
14
15 SUCCESS CONSTANT BINARY_INTEGER := 0;
16 FAILURE CONSTANT BINARY_INTEGER := -1;
17
18 --
19 -- PACKAGE VARIABLES
20 --
21 initialized BOOLEAN := FALSE;
22 current_line BINARY_INTEGER := 1;
23 user_id BINARY_INTEGER;
24 login_id BINARY_INTEGER;
25
26 --
27 -- Name
28 -- FUNCTION Init
29 --
30 -- Purpose
31 -- Initializes the package
32 --
33 -- Return Values
34 -- -1 => Failure
35 -- 0 => Success
36 --
37
38 FUNCTION Init
39 RETURN BINARY_INTEGER IS
40 BEGIN
41
42 IF initialized = TRUE THEN
43 WSH_UTIL.Write_Log('Package already initialized for session');
44 RETURN SUCCESS;
45 END IF;
46
47 delivery_table.delete;
48 current_line := 1;
49 user_id := WSH_PR_PICKING_SESSION.user_id;
50 login_id := WSH_PR_PICKING_SESSION.login_id;
51 initialized := TRUE;
52
53 RETURN SUCCESS;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 WSH_UTIL.Default_Handler('WSH_PR_CREATE_DELIVERIES.Init');
58 RETURN FAILURE;
59
60 END Init;
61
62 --
63 -- Name
64 -- FUNCTION Get_Delivery
65 --
66 -- Purpose
67 -- Gets the delivery_id to be used in autocreate deliveries when
68 -- inserting picking line details
69 --
70 -- Arguments
71 -- p_header_id => order header id
72 -- p_ship_to_site_use_id => ship to site use id (ultimate ship to)
73 -- p_ship_method_code => ship method (freight carrier)
74 --
75 -- Return Values
76 -- -1 => Failure
77 -- others => delivery_id
78 --
79
80 FUNCTION Get_Delivery(
81 p_header_id IN BINARY_INTEGER,
82 p_ship_to_site_use_id IN BINARY_INTEGER,
83 p_ship_method_code IN VARCHAR2,
84 p_organization_id IN BINARY_INTEGER
85 )
86 RETURN BINARY_INTEGER IS
87
88 CURSOR get_delivery_info(x_header_id IN BINARY_INTEGER) IS
89 SELECT NVL(CUSTOMER_ID,-1),
90 NVL(FOB_CODE, 'XX'),
91 NVL(FREIGHT_TERMS_CODE, 'XX'),
92 CURRENCY_CODE
93 FROM SO_HEADERS_ALL
94 WHERE HEADER_ID = x_header_id;
95
96
97 CURSOR get_vol_weight_uom(x_uom_class IN VARCHAR2 ) IS
98 SELECT UOM_CODE
99 FROM MTL_UNITS_OF_MEASURE
100 WHERE BASE_UOM_FLAG = 'Y'
101 AND UOM_CLASS = x_uom_class ;
102
103 v_ship_to_site_use_id BINARY_INTEGER;
104 v_ship_method_code VARCHAR2(30);
105 v_customer_id BINARY_INTEGER;
106 v_fob_code VARCHAR2(30);
107 v_freight_terms_code VARCHAR2(30);
108 v_currency_code VARCHAR2(15);
109 v_delivery_name VARCHAR2(15);
110 v_delivery_id BINARY_INTEGER := -1;
111 v_header_id BINARY_INTEGER;
112 v_rowid VARCHAR2(20);
113 v_delivery_report_set_id NUMBER;
114 v_volume_uom_code VARCHAR2(3);
115 v_volume_uom_class VARCHAR2(10);
116 v_weight_uom_code VARCHAR2(3);
117 v_weight_uom_class VARCHAR2(10);
118
119 i BINARY_INTEGER;
120
121 BEGIN
122
123 -- Fetch all delivery parameters
124 OPEN get_delivery_info(p_header_id);
125 FETCH get_delivery_info
126 INTO v_customer_id,
127 v_fob_code,
128 v_freight_terms_code,
129 v_currency_code;
130
131 IF get_delivery_info%NOTFOUND THEN
132 WSH_UTIL.Write_Log('Error: Cannot find order header');
133 RETURN FAILURE;
134 END IF;
135
136 IF WSH_PR_PICKING_OBJECTS.g_use_autocreate_del_orders = 'Y' THEN
137 v_header_id := p_header_id;
138 ELSE
139 v_header_id := -1;
140 END IF;
141
142 IF p_ship_to_site_use_id IS NULL THEN
143 v_ship_to_site_use_id := -1;
144 ELSE
145 v_ship_to_site_use_id := p_ship_to_site_use_id;
146 END IF;
147
148 IF p_ship_method_code IS NULL THEN
149 v_ship_method_code := -1;
150 ELSE
151 v_ship_method_code := p_ship_method_code;
152 END IF;
153
154 -- Search table for this combination
155 FOR i IN 1..current_line-1 LOOP
156 IF ((delivery_table(i).header_id = v_header_id) AND
157 (delivery_table(i).ship_to_site_use_id = v_ship_to_site_use_id) AND
158 (delivery_table(i).ship_method_code = v_ship_method_code) AND
159 (delivery_table(i).customer_id = v_customer_id) AND
160 (delivery_table(i).fob_code = v_fob_code) AND
161 (delivery_table(i).freight_terms_code = v_freight_terms_code) AND
162 (delivery_table(i).currency_code = v_currency_code)) THEN
163 v_delivery_id := delivery_table(i).delivery_id;
164 WSH_UTIL.Write_Log('Found delivery_id ' || to_char(v_delivery_id) ||
165 ' in table');
166 END IF;
167
168 -- If found, return the associated delivery_id
169 IF v_delivery_id > 0 THEN
170 RETURN v_delivery_id;
174
171 END IF;
172
173 END LOOP;
175 -- Must create a new delivery
176 IF v_delivery_id = -1 THEN
177 WSH_UTIL.Write_Log('Will create a new delivery id');
178 WSH_UTIL.Write_Log('Calling WSH_DELIVERIES_PKG.Insert_Row');
179
180 v_rowid := NULL;
181 v_delivery_id := NULL;
182 v_delivery_name := NULL;
183 v_volume_uom_code := NULL ;
184 v_weight_uom_code := NULL ;
185
186 -- For new delivery we must default document set from WSH_PARAMETERS.
187 -- Bug 778917
188 wsh_parameters_pvt.get_param_value_num(p_organization_id,
189 'DELIVERY_REPORT_SET_ID',
190 v_delivery_report_set_id);
191 -- For new delivery we must default Volume/Weight UOM from WSH_PARAMETERS.
192 -- Bug 804131
193
194 wsh_parameters_pvt.get_param_value(p_organization_id,
195 'VOLUME_UOM_CLASS',
196 v_volume_uom_class);
197 WSH_UTIL.Write_Log('volume_uom_class = ' || v_volume_uom_class);
198
199 wsh_parameters_pvt.get_param_value(p_organization_id,
200 'WEIGHT_UOM_CLASS',
201 v_weight_uom_class);
202 WSH_UTIL.Write_Log('weight_uom_class = ' || v_weight_uom_class);
203
204 OPEN get_vol_weight_uom(v_volume_uom_class);
205 FETCH get_vol_weight_uom
206 INTO v_volume_uom_code ;
207 IF get_delivery_info%NOTFOUND THEN
208 WSH_UTIL.Write_Log('Warning: Cannot find UOM code for Volume. Using NULL ....');
209 END IF;
210 CLOSE get_vol_weight_uom ;
211
212 OPEN get_vol_weight_uom(v_weight_uom_class);
213 FETCH get_vol_weight_uom
214 INTO v_weight_uom_code ;
215 IF get_delivery_info%NOTFOUND THEN
216 WSH_UTIL.Write_Log('Warning: Cannot find UOM code for Weight. Using NULL ....');
217 END IF;
218 CLOSE get_vol_weight_uom ;
219
220
221 WSH_UTIL.Write_Log('org_id = ' || to_char(p_organization_id));
222 WSH_UTIL.Write_Log('cust_id = ' || to_char(v_customer_id));
223 WSH_UTIL.Write_Log('ship_to = ' || to_char(v_ship_to_site_use_id));
224 WSH_UTIL.Write_Log('user_id = ' || to_char(user_id));
225 WSH_UTIL.Write_Log('login_id = ' || to_char(login_id));
226 WSH_UTIL.Write_Log('volume_uom_code = ' || v_volume_uom_code);
227 WSH_UTIL.Write_Log('weight_uom_code = ' || v_weight_uom_code);
228
229 WSH_DELIVERIES_PKG1.Insert_Row(
230 X_Rowid => v_rowid,
231 X_Organization_Id => p_organization_id,
232 X_Delivery_Id => v_delivery_id,
233 X_Name => v_delivery_name,
234 X_Source_Code => 'S',
235 X_Planned_Departure_Id => '',
236 X_Actual_Departure_Id => '',
237 X_Status_Code => 'OP',
238 X_Loading_Order_Flag => NULL,
239 X_Date_Closed => NULL,
240 X_Report_Set_Id => v_delivery_report_set_id,
241 X_Sequence_Number => NULL,
242 X_Customer_Id => v_customer_id,
243 X_Ultimate_Ship_To_Id => p_ship_to_site_use_id,
247 X_Gross_Weight => NULL,
244 X_Intermediate_Ship_To_Id => NULL,
245 X_Pooled_Ship_To_Id => NULL,
246 X_Waybill => NULL,
248 X_Weight_Uom_Code => v_weight_uom_code,
249 X_Volume => NULL,
250 X_Volume_Uom_Code => v_volume_uom_code,
251 X_Picked_By_Id => NULL,
252 X_Packed_By_Id => NULL,
253 X_Expected_Arrival_Date => NULL,
254 X_Asn_Date_Sent => NULL,
255 X_Asn_Seq_Number => NULL,
256 X_Freight_Carrier_Code => p_ship_method_code,
257 X_Freight_Terms_Code => v_freight_terms_code,
258 X_Currency_Code => v_currency_code,
259 X_Fob_Code => v_fob_code,
260 X_Attribute_Category => NULL,
261 X_Attribute1 => NULL,
262 X_Attribute2 => NULL,
263 X_Attribute3 => NULL,
264 X_Attribute4 => NULL,
265 X_Attribute5 => NULL,
266 X_Attribute6 => NULL,
267 X_Attribute7 => NULL,
268 X_Attribute8 => NULL,
269 X_Attribute9 => NULL,
270 X_Attribute10 => NULL,
271 X_Attribute11 => NULL,
272 X_Attribute12 => NULL,
273 X_Attribute13 => NULL,
274 X_Attribute14 => NULL,
275 X_Attribute15 => NULL,
276 X_Creation_Date => SYSDATE,
277 X_Created_By => user_id,
278 X_Last_Update_Date => SYSDATE,
279 X_Last_Updated_By => user_id,
280 X_Last_Update_Login => login_id
281 );
282
283 WSH_UTIL.Write_Log('New delivery is ' || v_delivery_name);
284
285 -- Inserting row in delivery_table
286 delivery_table(current_line).header_id := v_header_id;
287 delivery_table(current_line).ship_to_site_use_id := v_ship_to_site_use_id;
288 delivery_table(current_line).ship_method_code := v_ship_method_code;
289 delivery_table(current_line).customer_id := v_customer_id;
290 delivery_table(current_line).fob_code := v_fob_code;
291 delivery_table(current_line).freight_terms_code := v_freight_terms_code;
292 delivery_table(current_line).currency_code := v_currency_code;
293 delivery_table(current_line).delivery_id := v_delivery_id;
294 current_line := current_line + 1;
295
296 END IF;
297
298 RETURN v_delivery_id;
299
300 EXCEPTION
301 WHEN OTHERS THEN
302 WSH_UTIL.Default_Handler('WSH_PR_CREATE_DELIVERIES.Get_Delivery');
303 RETURN FAILURE;
304
305 END Get_Delivery;
306
307
308 END WSH_PR_CREATE_DELIVERIES;