[Home] [Help]
PACKAGE BODY: APPS.WSH_SHIPPING_PARAMS_PVT
Source
1 PACKAGE BODY WSH_SHIPPING_PARAMS_PVT as
2 /* $Header: WSHSPRMB.pls 120.9 2011/06/29 09:44:14 ashimalh ship $ */
3
4 --
5 -- Package data types
6 --
7
8 TYPE Parameter_Rec_Tab_Typ IS TABLE OF Parameter_Rec_Typ INDEX BY BINARY_INTEGER;
9 TYPE Global_Parameters_rec_Tab_Typ IS TABLE OF Global_Parameters_Rec_Typ INDEX BY BINARY_INTEGER;
10 TYPE parameter_values_tbl IS TABLE OF parameter_value_rec_typ INDEX BY BINARY_INTEGER;
11
12 --
13 -- Package Variables
14 --
15
16 g_parameters Parameter_Rec_Tab_Typ;
17 g_global_parameters Global_Parameters_rec_Tab_Typ;
18 g_parameter_values parameter_values_tbl;
19
20 C_ERROR_STATUS CONSTANT VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_ERROR;
21 C_WARNING_STATUS CONSTANT VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_WARNING;
22 C_SUCCESS_STATUS CONSTANT VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
23 C_UNEXP_ERROR_STATUS CONSTANT VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
24
25 --
26 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_SHIPPING_PARAMS_PVT';
27 --
28
29 /* Local Procedure to write debug information */
30 Procedure print_debug_info(
31 p_module_name IN VARCHAR2,
32 p_print_info IN VARCHAR2,
33 x_param_info IN parameter_rec_typ,
34 x_param_value_info IN parameter_value_rec_typ,
35 x_return_status OUT NOCOPY VARCHAR2);
36
37 /*+=======================================================================================
38 API name : Get
39 Type : Private.
40 IN Parameters : Organization_id
41 P_client_id : Client Id.
42 OUT Parameters : Shipping Parameter Record for individual Organization and client_id passed.
43 In addition it also tells if the organization is a process organization.
44
45 Description : This procedure caches the shipping Parameters in the Record Type
46 Collection for Reference.
47
48 +=======================================================================================*/
49
50 PROCEDURE Get
51 (p_organization_id IN NUMBER,
52 p_client_id IN NUMBER DEFAULT NULL, -- LSP PROJECT.
53 x_param_info OUT NOCOPY Parameter_Rec_Typ,
54 x_return_status OUT NOCOPY VARCHAR2) IS
55
56 -- HW OPMCONV - Retrieve CHECK_ON_HAND
57 CURSOR c_get_param (v_organization_id NUMBER) IS
58 SELECT
59 SHIP_CONFIRM_RULE_ID,
60 AUTOPACK_LEVEL,
61 TASK_PLANNING_FLAG,
62 EXPORT_SCREENING_FLAG,
63 APPENDING_LIMIT,
64 IGNORE_INBOUND_TRIP,
65 PACK_SLIP_REQUIRED_FLAG,
66 PICK_SEQUENCE_RULE_ID,
67 PICK_GROUPING_RULE_ID,
68 PRINT_PICK_SLIP_MODE,
69 PICK_RELEASE_REPORT_SET_ID,
70 AUTOCREATE_DEL_ORDERS_FLAG,
71 DEFAULT_STAGE_SUBINVENTORY,
72 DEFAULT_STAGE_LOCATOR_ID,
73 AUTODETAIL_PR_FLAG,
74 ENFORCE_PACKING_FLAG,
75 GROUP_BY_CUSTOMER_FLAG,
76 GROUP_BY_FOB_FLAG,
77 GROUP_BY_FREIGHT_TERMS_FLAG,
78 GROUP_BY_INTMED_SHIP_TO_FLAG,
79 GROUP_BY_SHIP_METHOD_FLAG,
80 GROUP_BY_CARRIER_FLAG,
81 ATTRIBUTE_CATEGORY,
82 ATTRIBUTE1,
83 ATTRIBUTE2,
84 ATTRIBUTE3,
85 ATTRIBUTE4,
86 ATTRIBUTE5,
87 ATTRIBUTE6,
88 ATTRIBUTE7,
89 ATTRIBUTE8,
90 ATTRIBUTE9,
91 ATTRIBUTE10,
92 ATTRIBUTE11,
93 ATTRIBUTE12,
94 ATTRIBUTE13,
95 ATTRIBUTE14,
96 ATTRIBUTE15,
97 CREATION_DATE,
98 CREATED_BY,
99 LAST_UPDATE_DATE,
100 LAST_UPDATED_BY,
101 LAST_UPDATE_LOGIN,
102 PROGRAM_APPLICATION_ID,
103 PROGRAM_ID,
104 PROGRAM_UPDATE_DATE,
105 REQUEST_ID,
106 PICK_SLIP_LINES,
107 AUTOCREATE_DELIVERIES_FLAG,
108 FREIGHT_CLASS_CAT_SET_ID,
109 COMMODITY_CODE_CAT_SET_ID,
110 ENFORCE_SHIP_SET_AND_SMC,
111 AUTO_SEND_DOC_FLAG,
112 ITM_ADDITIONAL_COUNTRY_CODE,
113 AUTO_SELECT_CARRIER,
114 GOODS_DISPATCHED_ACCOUNT,
115 LOCATION_ID,
116 ORGANIZATION_ID,
117 WEIGHT_UOM_CLASS,
118 VOLUME_UOM_CLASS,
119 WEIGHT_VOLUME_FLAG,
120 INV_CONTROLS_CONTAINER_FLAG,
121 PERCENT_FILL_BASIS_FLAG,
122 TRIP_REPORT_SET_ID,
123 DELIVERY_REPORT_SET_ID,
124 AUTOCREATE_DEL_ORDERS_PR_FLAG,
125 FPA_POSITIVE_TOL_AMT,
126 FPA_NEGATIVE_TOL_AMT,
127 FPA_POSITIVE_TOL_PERCENTAGE,
128 FPA_NEGATIVE_TOL_PERCENTAGE,
129 FPA_DEFAULT_FREIGHT_ACCOUNT,
130 AUTO_APPLY_ROUTING_RULES,
131 AUTO_CALC_FGT_RATE_CR_DEL,
132 AUTO_CALC_FGT_RATE_APPEND_DEL,
133 AUTO_CALC_FGT_RATE_SC_DEL,
134 RAISE_BUSINESS_EVENTS,
135 ENABLE_TRACKING_WFS,
136 ENABLE_SC_WF,
137 null PROCESS_FLAG,
138 -- HW OPMCON. get check_on_hand
139 CHECK_ON_HAND,
140 --OTM R12
141 MAX_NET_WEIGHT,
142 MAX_GROSS_WEIGHT,
143 nvl(OTM_ENABLED,'N'), --OTM R12 Org-Specific
144 nvl(DYNAMIC_REPLENISHMENT_FLAG,'N'), --bug# 6689448 (replenishment project)
145 nvl(DOCK_APPT_SCHEDULING_FLAG,'N'), --bug 6700792: OTM Dock Door Sched Proj
146 nvl(RETAIN_NONSTAGED_DET_FLAG,'N'), --Bug 7131800
147 -- Bug 8446283 (Added wt/vol UOM codes on shipping parameters forms)
148 WEIGHT_UOM_CODE,
149 VOLUME_UOM_CODE
150 -- Bug 8446283 : end
151 FROM WSH_SHIPPING_PARAMETERS
152 WHERE ORGANIZATION_ID = v_organization_id;
153
154 l_found BOOLEAN := FALSE;
155 l_param_info Parameter_Rec_Typ;
156 i NUMBER;
157 l_client_params inv_cache.ct_rec_type; -- LSP PROJECT
158
159 l_debug_on BOOLEAN;
160 --
161 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET';
162 --
163 l_msg_data VARCHAR(2000);
164
165 BEGIN
166 --
167 --
168 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
169 --
170 IF l_debug_on IS NULL
171 THEN
172 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
173 END IF;
174 --
175 IF l_debug_on THEN
176 WSH_DEBUG_SV.push(l_module_name);
177 --
178 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
179 WSH_DEBUG_SV.log(l_module_name,'P_CLIENT_ID',P_CLIENT_ID); -- LSP PROJECT
180 END IF;
181 --
182 x_return_status := FND_API.G_RET_STS_SUCCESS;
183 --
184 -- Search table if it has any values
185
186 IF g_parameters.count <> 0 THEN
187 --{
188 -- Check if parameters have already been fetched
189 FOR i IN g_parameters.FIRST..g_parameters.LAST LOOP
190 --{
191 IF g_parameters(i).organization_id = p_organization_id THEN
192 l_found := TRUE;
193 x_param_info := g_parameters(i);
194 EXIT;
195 END IF;
196 --}
197 END LOOP;
198 --}
199 END IF;
200 IF NOT l_found THEN
201 --{
202 -- Need to fetch and cache parameter info
203 OPEN c_get_param(p_organization_id);
204 FETCH c_get_param
205 INTO l_param_info;
206 IF c_get_param%NOTFOUND THEN
207 --{
208 --Bug 3539616 : Error message shown when Shipping Parameters are not defined
209 FND_MESSAGE.Set_Name('WSH', 'WSH_SHP_NOT_FOUND');
210 FND_MESSAGE.Set_Token('ORG_NAME', wsh_util_core.get_org_name(p_organization_id));
211 FND_MSG_PUB.Add; --BUG#12599249
212 IF l_debug_on THEN
213 WSH_DEBUG_SV.log(l_module_name,'ERROR: ','Shipping Parameters not defined for Organization '||p_organization_id);
214 END IF;
215 RAISE NO_DATA_FOUND;
216 --}
217 END IF;
218 --
219 -- Debug Statements
220 --
221 -- HW OPMCONV - Removed call to check for process
222 -- Insert into cached info into PL/SQL table
223 IF g_parameters.count = 0 THEN
224 g_parameters(1) := l_param_info;
225 ELSE
226 i := g_parameters.LAST + 1;
227 g_parameters(i) := l_param_info;
228 END IF;
229 x_param_info := l_param_info;
230 --}
231 END IF;
232 --
233 --
234 -- LSP PROJECT : Get Client related setup parameters and overrite the org parameters.
235 IF (p_client_id IS NOT NULL AND WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'L' ) THEN
236 --{ client validation
237 --
238 IF l_debug_on THEN
239 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling INV_CACHE.GET_CLIENT_DEFAULT_PARAMETERS', WSH_DEBUG_SV.C_PROC_LEVEL);
240 END IF;
241 inv_cache.get_client_default_parameters (
242 p_client_id => p_client_id,
243 x_client_parameters_rec => l_client_params,
244 x_return_status => x_return_status);
245 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
246 IF l_debug_on THEN
247 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occured in get_client_default_parameters => ' || x_return_status, WSH_DEBUG_SV.C_ERR_LEVEL);
248 END IF;
249 RAISE NO_DATA_FOUND;
250 END IF;
251 --
252 x_param_info.ship_confirm_rule_id := l_client_params.client_rec.ship_confirm_rule_id;
253 x_param_info.autocreate_del_orders_flag := l_client_params.client_rec.autocreate_del_orders_flag;
254 x_param_info.group_by_customer_flag := l_client_params.client_rec.group_by_customer_flag;
255 x_param_info.group_by_fob_flag := l_client_params.client_rec.group_by_fob_flag;
256 x_param_info.group_by_freight_terms_flag := l_client_params.client_rec.group_by_freight_terms_flag;
257 x_param_info.group_by_intmed_ship_to_flag := 'N';
258 x_param_info.group_by_ship_method_flag := l_client_params.client_rec.group_by_ship_method_flag;
259 x_param_info.group_by_carrier_flag := 'N';
260 x_param_info.otm_enabled := l_client_params.client_rec.otm_enabled;
261 x_param_info.delivery_report_set_id := l_client_params.client_rec.delivery_report_set_id;
262 --}
263 END IF;
264 -- LSP PROJECT : end
265 --
266 -- Debug Statements
267 IF l_debug_on THEN
268 --
269 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.SHIP_CONFIRM_RULE_ID',X_PARAM_INFO.SHIP_CONFIRM_RULE_ID);
270 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTOPACK_LEVEL',X_PARAM_INFO.AUTOPACK_LEVEL);
271 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TASK_PLANNING_FLAG',X_PARAM_INFO.TASK_PLANNING_FLAG);
272 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.EXPORT_SCREENING_FLAG',X_PARAM_INFO.EXPORT_SCREENING_FLAG);
273 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.APPENDING_LIMIT',X_PARAM_INFO.APPENDING_LIMIT);
274 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.IGNORE_INBOUND_TRIP',X_PARAM_INFO.IGNORE_INBOUND_TRIP);
275 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PACK_SLIP_REQUIRED_FLAG',X_PARAM_INFO.PACK_SLIP_REQUIRED_FLAG);
276 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PICK_SEQUENCE_RULE_ID',X_PARAM_INFO.PICK_SEQUENCE_RULE_ID);
277 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PICK_GROUPING_RULE_ID',X_PARAM_INFO.PICK_GROUPING_RULE_ID);
278 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PRINT_PICK_SLIP_MODE',X_PARAM_INFO.PRINT_PICK_SLIP_MODE);
279 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PICK_RELEASE_REPORT_SET_ID',X_PARAM_INFO.PICK_RELEASE_REPORT_SET_ID);
280 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTOCREATE_DEL_ORDERS_FLAG',X_PARAM_INFO.AUTOCREATE_DEL_ORDERS_FLAG);
281 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEFAULT_STAGE_SUBINVENTORY',X_PARAM_INFO.DEFAULT_STAGE_SUBINVENTORY);
282 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEFAULT_STAGE_LOCATOR_ID',X_PARAM_INFO.DEFAULT_STAGE_LOCATOR_ID);
283 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTODETAIL_PR_FLAG',X_PARAM_INFO.AUTODETAIL_PR_FLAG);
284 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ENFORCE_PACKING_FLAG',X_PARAM_INFO.ENFORCE_PACKING_FLAG);
285 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GROUP_BY_CUSTOMER_FLAG',X_PARAM_INFO.GROUP_BY_CUSTOMER_FLAG);
286 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GROUP_BY_FOB_FLAG',X_PARAM_INFO.GROUP_BY_FOB_FLAG);
287 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GROUP_BY_FREIGHT_TERMS_FLAG',X_PARAM_INFO.GROUP_BY_FREIGHT_TERMS_FLAG);
288 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GROUP_BY_INTMED_SHIP_TO_FLAG',X_PARAM_INFO.GROUP_BY_INTMED_SHIP_TO_FLAG);
289 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GROUP_BY_SHIP_METHOD_FLAG',X_PARAM_INFO.GROUP_BY_SHIP_METHOD_FLAG);
290 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GROUP_BY_CARRIER_FLAG',X_PARAM_INFO.GROUP_BY_CARRIER_FLAG);
291 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE_CATEGORY',X_PARAM_INFO.ATTRIBUTE_CATEGORY);
292 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE1',X_PARAM_INFO.ATTRIBUTE1);
293 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE2',X_PARAM_INFO.ATTRIBUTE2);
294 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE3',X_PARAM_INFO.ATTRIBUTE3);
295 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE4',X_PARAM_INFO.ATTRIBUTE4);
296 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE5',X_PARAM_INFO.ATTRIBUTE5);
297 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE6',X_PARAM_INFO.ATTRIBUTE6);
298 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE7',X_PARAM_INFO.ATTRIBUTE7);
299 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE8',X_PARAM_INFO.ATTRIBUTE8);
300 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE9',X_PARAM_INFO.ATTRIBUTE9);
301 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE10',X_PARAM_INFO.ATTRIBUTE10);
302 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE11',X_PARAM_INFO.ATTRIBUTE11);
303 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE12',X_PARAM_INFO.ATTRIBUTE12);
304 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE13',X_PARAM_INFO.ATTRIBUTE13);
305 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE14',X_PARAM_INFO.ATTRIBUTE14);
306 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE15',X_PARAM_INFO.ATTRIBUTE15);
307 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.CREATION_DATE',X_PARAM_INFO.CREATION_DATE);
308 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.CREATED_BY',X_PARAM_INFO.CREATED_BY);
309 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_DATE',X_PARAM_INFO.LAST_UPDATE_DATE);
310 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATED_BY',X_PARAM_INFO.LAST_UPDATED_BY);
311 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_LOGIN',X_PARAM_INFO.LAST_UPDATE_LOGIN);
312 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PROGRAM_APPLICATION_ID',X_PARAM_INFO.PROGRAM_APPLICATION_ID);
313 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PROGRAM_ID',X_PARAM_INFO.PROGRAM_ID);
314 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PROGRAM_UPDATE_DATE',X_PARAM_INFO.PROGRAM_UPDATE_DATE);
315 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.REQUEST_ID',X_PARAM_INFO.REQUEST_ID);
316 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PICK_SLIP_LINES',X_PARAM_INFO.PICK_SLIP_LINES);
317 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTOCREATE_DELIVERIES_FLAG',X_PARAM_INFO.AUTOCREATE_DELIVERIES_FLAG);
318 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.FREIGHT_CLASS_CAT_SET_ID',X_PARAM_INFO.FREIGHT_CLASS_CAT_SET_ID);
319 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.COMMODITY_CODE_CAT_SET_ID',X_PARAM_INFO.COMMODITY_CODE_CAT_SET_ID);
320 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ENFORCE_SHIP_SET_AND_SMC',X_PARAM_INFO.ENFORCE_SHIP_SET_AND_SMC);
321 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTO_SEND_DOC_FLAG',X_PARAM_INFO.AUTO_SEND_DOC_FLAG);
322 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ITM_ADDITIONAL_COUNTRY_CODE',X_PARAM_INFO.ITM_ADDITIONAL_COUNTRY_CODE);
323 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTO_SELECT_CARRIER',X_PARAM_INFO.AUTO_SELECT_CARRIER);
324 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GOODS_DISPATCHED_ACCOUNT',X_PARAM_INFO.GOODS_DISPATCHED_ACCOUNT);
325 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LOCATION_ID',X_PARAM_INFO.LOCATION_ID);
326 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ORGANIZATION_ID',X_PARAM_INFO.ORGANIZATION_ID);
327 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.WEIGHT_UOM_CLASS',X_PARAM_INFO.WEIGHT_UOM_CLASS);
328 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.VOLUME_UOM_CLASS',X_PARAM_INFO.VOLUME_UOM_CLASS);
329 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.WEIGHT_VOLUME_FLAG',X_PARAM_INFO.WEIGHT_VOLUME_FLAG);
330 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.INV_CONTROLS_CONTAINER_FLAG',X_PARAM_INFO.INV_CONTROLS_CONTAINER_FLAG);
331 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PERCENT_FILL_BASIS_FLAG',X_PARAM_INFO.PERCENT_FILL_BASIS_FLAG);
332 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TRIP_REPORT_SET_ID',X_PARAM_INFO.TRIP_REPORT_SET_ID);
333 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DELIVERY_REPORT_SET_ID',X_PARAM_INFO.DELIVERY_REPORT_SET_ID);
334 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTOCREATE_DEL_ORDERS_PR_FLAG',X_PARAM_INFO.AUTOCREATE_DEL_ORDERS_PR_FLAG);
335 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.FPA_POSITIVE_TOL_AMT',X_PARAM_INFO.FPA_POSITIVE_TOL_AMT);
336 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.FPA_NEGATIVE_TOL_AMT',X_PARAM_INFO.FPA_NEGATIVE_TOL_AMT);
337 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.FPA_POSITIVE_TOL_PERCENTAGE',X_PARAM_INFO.FPA_POSITIVE_TOL_PERCENTAGE);
338 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.FPA_NEGATIVE_TOL_PERCENTAGE',X_PARAM_INFO.FPA_NEGATIVE_TOL_PERCENTAGE);
339 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.FPA_DEFAULT_FREIGHT_ACCOUNT',X_PARAM_INFO.FPA_DEFAULT_FREIGHT_ACCOUNT);
340 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTO_APPLY_ROUTING_RULES',X_PARAM_INFO.AUTO_APPLY_ROUTING_RULES);
341 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTO_CALC_FGT_RATE_CR_DEL',X_PARAM_INFO.AUTO_CALC_FGT_RATE_CR_DEL);
342 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTO_CALC_FGT_RATE_APPEND_DEL',X_PARAM_INFO.AUTO_CALC_FGT_RATE_APPEND_DEL);
343 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTO_CALC_FGT_RATE_SC_DEL',X_PARAM_INFO.AUTO_CALC_FGT_RATE_SC_DEL);
344 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.RAISE_BUSINESS_EVENTS',X_PARAM_INFO.RAISE_BUSINESS_EVENTS);
345 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ENABLE_TRACKING_WFS',X_PARAM_INFO.ENABLE_TRACKING_WFS);
346 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ENABLE_SC_WF',X_PARAM_INFO.ENABLE_SC_WF);
347 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PROCESS_FLAG',X_PARAM_INFO.PROCESS_FLAG);
348 -- HW OPMCONV - Print value of CHECK_ON_HAND
349 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.CHECK_ON_HAND',X_PARAM_INFO.CHECK_ON_HAND);
350 --OTM R12
351 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.MAX_NET_WEIGHT',X_PARAM_INFO.MAX_NET_WEIGHT);
352 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.MAX_GROSS_WEIGHT',X_PARAM_INFO.MAX_GROSS_WEIGHT);
353 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.OTM_ENABLED',X_PARAM_INFO.OTM_ENABLED); --OTM R12 Org-Specific
354 --
355 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DYNAMIC_REPLENISHMENT_FLAG',X_PARAM_INFO.DYNAMIC_REPLENISHMENT_FLAG); --bug# 6689448 (replenishment project)
356 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DOCK_APPT_SCHEDULING_FLAG',X_PARAM_INFO.DOCK_APPT_SCHEDULING_FLAG); --bug 6700792: OTM Dock Door Appt Sched Proj
357 --Bug 7131800
358 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.RETAIN_NONSTAGED_DET_FLAG',X_PARAM_INFO.RETAIN_NONSTAGED_DET_FLAG);
359 --Bug 7131800
360 -- Bug 8446283
361 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.WEIGHT_UOM_CODE',X_PARAM_INFO.WEIGHT_UOM_CODE);
362 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.VOLUME_UOM_CODE',X_PARAM_INFO.VOLUME_UOM_CODE);
363 -- Bug 8446283
364 END IF;
365
366
367
368 --
369 -- Debug Statements
370 --
371 IF l_debug_on THEN
372 WSH_DEBUG_SV.pop(l_module_name);
373 END IF;
374 --
375 EXCEPTION
376 WHEN NO_DATA_FOUND THEN
377 IF c_get_param%ISOPEN THEN
378 CLOSE c_get_param;
379 END IF;
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 --
382 -- Debug Statements
383 --
384 IF l_debug_on THEN
385 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
386 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
387 END IF;
388 --
389 WHEN OTHERS THEN
390 FND_MESSAGE.Set_Name('WSH','WSH_UNEXP_ERROR');
391 FND_MESSAGE.Set_Token('PACKAGE','WSH_SHIPPING_PARAMS_PVT');
392 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
393 FND_MESSAGE.Set_Token('ORA_TEXT','');
394 IF c_get_param%ISOPEN THEN
395 CLOSE c_get_param;
396 END IF;
397 x_return_status := FND_API.G_RET_STS_ERROR;
398
399 --
400 -- Debug Statements
401 --
402 IF l_debug_on THEN
403 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
404 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
405 END IF;
406 --
407 END Get;
408
409
410 /*======================================================================================+
411 API name : Get_Global_Parameters
412 Type : Private.
413 IN Parameters : None.
414 OUT Parameters : record for global parameters.
415 Description : This procedure caches the global Parameters in the Record Type
416 Collection for Reference.
417 +=======================================================================================*/
418
419 Procedure Get_Global_Parameters(
420 x_Param_Info OUT NOCOPY Global_Parameters_Rec_Typ,
421 x_return_status OUT NOCOPY VARCHAR2) IS
422
423 CURSOR c_get_global_param IS
424 SELECT
425 AUTO_RATE_TP_REL_TRIPS,
426 TL_PRIN_COST_ALLOC_BASIS,
427 TL_DISTANCE_ALLOC_BASIS,
428 TL_STOP_COST_ALLOC_BASIS,
429 AUTOFIRM_LOAD_TENDERED_TRIPS,
430 CONSOLIDATE_BO_LINES,
431 GU_WEIGHT_CLASS,
432 GU_WEIGHT_UOM,
433 GU_VOLUME_UOM,
434 GU_VOLUME_CLASS,
435 GU_DISTANCE_CLASS,
436 GU_DISTANCE_UOM,
437 GU_DIMENSION_CLASS,
438 GU_DIMENSION_UOM,
439 GU_CURRENCY_COUNTRY,
440 GU_CURRENCY_UOM,
441 GU_TIME_CLASS,
442 GU_TIME_UOM,
443 DEF_MILE_CALC_ON_CUST_FAC,
444 DEF_MILE_CALC_ON_SUPP_FAC,
445 DEF_MILE_CALC_ON_ORG_FAC,
446 DEF_MILE_CALC_ON_CARR_FAC,
447 TL_HWAY_DIS_EMP_CONSTANT,
448 AVG_HWAY_SPEED,
449 DISTANCE_UOM,
450 TIME_UOM,
451 UOM_FOR_NUM_OF_UNITS,
452 PALLET_ITEM_TYPE,
453 ATTRIBUTE_CATEGORY,
454 ATTRIBUTE1,
455 ATTRIBUTE2,
456 ATTRIBUTE3,
457 ATTRIBUTE4,
458 ATTRIBUTE5,
459 ATTRIBUTE6,
460 ATTRIBUTE7,
461 ATTRIBUTE8,
462 ATTRIBUTE9,
463 ATTRIBUTE10,
464 ATTRIBUTE11,
465 ATTRIBUTE12,
466 ATTRIBUTE13,
467 ATTRIBUTE14,
468 ATTRIBUTE15,
469 CREATION_DATE,
470 CREATED_BY,
471 LAST_UPDATE_DATE,
472 LAST_UPDATED_BY,
473 LAST_UPDATE_LOGIN,
474 DEFER_INTERFACE,
475 ENFORCE_SHIP_METHOD,
476 ALLOW_FUTURE_SHIP_DATE,
477 RATE_IB_DELS_FGT_TERM,
478 SKIP_RATE_OB_DELS_FGT_TERM,
479 DEL_DATE_CALC_METHOD,
480 RATE_DS_DELS_FGT_TERM_ID,
481 RAISE_BUSINESS_EVENTS,
482 ENABLE_TRACKING_WFS,
483 ENABLE_SC_WF,
484 EXPAND_CARRIER_RANKINGS,
485 DEFER_PLAN_SHIPMENT_INTERFACE --bug 7491598
486 FROM WSH_GLOBAL_PARAMETERS;
487
488 l_found BOOLEAN := FALSE;
489 l_param_info Global_Parameters_Rec_Typ;
490 i NUMBER;
491
492 l_debug_on BOOLEAN;
493 --
494 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_GLOBAL_PARAMETERS';
495 --
496
497 BEGIN
498
499 --
500 --
501 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
502 --
503 IF l_debug_on IS NULL
504 THEN
505 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
506 END IF;
507 --
508 IF l_debug_on THEN
509 WSH_DEBUG_SV.push(l_module_name);
510 END IF;
511 --
512 x_return_status := FND_API.G_RET_STS_SUCCESS;
513
514 -- Search table if it has global parameters already populated.
515
516 IF g_global_parameters.count <> 0 THEN
517
518 x_param_info := g_global_parameters(1);
519 --
520 -- Debug Statements
521 IF l_debug_on THEN
522 --
523 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTO_RATE_TP_REL_TRIPS',X_PARAM_INFO.AUTO_RATE_TP_REL_TRIPS);
524 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TL_PRIN_COST_ALLOC_BASIS',X_PARAM_INFO.TL_PRIN_COST_ALLOC_BASIS);
525 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TL_DISTANCE_ALLOC_BASIS',X_PARAM_INFO.TL_DISTANCE_ALLOC_BASIS);
526 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TL_STOP_COST_ALLOC_BASIS',X_PARAM_INFO.TL_STOP_COST_ALLOC_BASIS);
527 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTOFIRM_LOAD_TENDERED_TRIPS',X_PARAM_INFO.AUTOFIRM_LOAD_TENDERED_TRIPS);
528 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.CONSOLIDATE_BO_LINES',X_PARAM_INFO.CONSOLIDATE_BO_LINES);
529 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_WEIGHT_CLASS',X_PARAM_INFO.GU_WEIGHT_CLASS);
530 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_WEIGHT_UOM',X_PARAM_INFO.GU_WEIGHT_UOM);
531 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_VOLUME_UOM',X_PARAM_INFO.GU_VOLUME_UOM);
532 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_VOLUME_CLASS',X_PARAM_INFO.GU_VOLUME_CLASS);
533 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_DISTANCE_CLASS',X_PARAM_INFO.GU_DISTANCE_CLASS);
534 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_DISTANCE_UOM',X_PARAM_INFO.GU_DISTANCE_UOM);
535 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_DIMENSION_CLASS',X_PARAM_INFO.GU_DIMENSION_CLASS);
536 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_DIMENSION_UOM',X_PARAM_INFO.GU_DIMENSION_UOM);
537 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_CURRENCY_COUNTRY',X_PARAM_INFO.GU_CURRENCY_COUNTRY);
538 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_CURRENCY_UOM',X_PARAM_INFO.GU_CURRENCY_UOM);
539 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_TIME_CLASS',X_PARAM_INFO.GU_TIME_CLASS);
540 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_TIME_UOM',X_PARAM_INFO.GU_TIME_UOM);
541 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEF_MILE_CALC_ON_CUST_FAC',X_PARAM_INFO.DEF_MILE_CALC_ON_CUST_FAC);
542 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEF_MILE_CALC_ON_SUPP_FAC',X_PARAM_INFO.DEF_MILE_CALC_ON_SUPP_FAC);
543 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEF_MILE_CALC_ON_ORG_FAC',X_PARAM_INFO.DEF_MILE_CALC_ON_ORG_FAC);
544 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEF_MILE_CALC_ON_CARR_FAC',X_PARAM_INFO.DEF_MILE_CALC_ON_CARR_FAC);
545 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TL_HWAY_DIS_EMP_CONSTANT',X_PARAM_INFO.TL_HWAY_DIS_EMP_CONSTANT);
546 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AVG_HWAY_SPEED',X_PARAM_INFO.AVG_HWAY_SPEED);
547 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DISTANCE_UOM',X_PARAM_INFO.DISTANCE_UOM);
548 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TIME_UOM',X_PARAM_INFO.TIME_UOM);
549 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.UOM_FOR_NUM_OF_UNITS',X_PARAM_INFO.UOM_FOR_NUM_OF_UNITS);
550 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PALLET_ITEM_TYPE',X_PARAM_INFO.PALLET_ITEM_TYPE);
551 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE_CATEGORY',X_PARAM_INFO.ATTRIBUTE_CATEGORY);
552 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE1',X_PARAM_INFO.ATTRIBUTE1);
553 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE2',X_PARAM_INFO.ATTRIBUTE2);
554 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE3',X_PARAM_INFO.ATTRIBUTE3);
555 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE4',X_PARAM_INFO.ATTRIBUTE4);
556 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE5',X_PARAM_INFO.ATTRIBUTE5);
557 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE6',X_PARAM_INFO.ATTRIBUTE6);
558 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE7',X_PARAM_INFO.ATTRIBUTE7);
559 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE8',X_PARAM_INFO.ATTRIBUTE8);
560 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE9',X_PARAM_INFO.ATTRIBUTE9);
561 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE10',X_PARAM_INFO.ATTRIBUTE10);
562 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE11',X_PARAM_INFO.ATTRIBUTE11);
563 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE12',X_PARAM_INFO.ATTRIBUTE12);
564 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE13',X_PARAM_INFO.ATTRIBUTE13);
565 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE14',X_PARAM_INFO.ATTRIBUTE14);
566 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE15',X_PARAM_INFO.ATTRIBUTE15);
567 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.CREATION_DATE',X_PARAM_INFO.CREATION_DATE);
568 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.CREATED_BY',X_PARAM_INFO.CREATED_BY);
569 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_DATE',X_PARAM_INFO.LAST_UPDATE_DATE);
570 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATED_BY',X_PARAM_INFO.LAST_UPDATED_BY);
571 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_LOGIN',X_PARAM_INFO.LAST_UPDATE_LOGIN);
572 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ENFORCE_SHIP_METHOD',X_PARAM_INFO.ENFORCE_SHIP_METHOD);
573 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ALLOW_FUTURE_SHIP_DATE',X_PARAM_INFO.ALLOW_FUTURE_SHIP_DATE);
574 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEFER_INTERFACE',X_PARAM_INFO.DEFER_INTERFACE);
575 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.RATE_IB_DELS_FGT_TERM',X_PARAM_INFO.RATE_IB_DELS_FGT_TERM);
576 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.SKIP_RATE_OB_DELS_FGT_TERM',X_PARAM_INFO.SKIP_RATE_OB_DELS_FGT_TERM);
577 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEL_DATE_CALC_METHOD',X_PARAM_INFO.DEL_DATE_CALC_METHOD);
578 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.RATE_DS_DELS_FGT_TERM_ID',X_PARAM_INFO.RATE_DS_DELS_FGT_TERM_ID);
579 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.RAISE_BUSINESS_EVENTS',X_PARAM_INFO.RAISE_BUSINESS_EVENTS);
580 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ENABLE_TRACKING_WFS',X_PARAM_INFO.ENABLE_TRACKING_WFS);
581 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ENABLE_SC_WF',X_PARAM_INFO.ENABLE_SC_WF);
582 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.EXPAND_CARRIER_RANKINGS',X_PARAM_INFO.EXPAND_CARRIER_RANKINGS);
583 --bug 7491598 DEFER OTM-PLANNED SHIPMENT INTERFACES ENHANCEMENT
584 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEFER_PLAN_SHIPMENT_INTERFACE',X_PARAM_INFO.DEFER_PLAN_SHIPMENT_INTERFACE);
585 END IF;
586 -- Debug Statements
587 --
588 IF l_debug_on THEN
589 WSH_DEBUG_SV.pop(l_module_name);
590 END IF;
591 --
592 RETURN;
593
594 END IF;
595
596 -- Need to fetch and cache global parameter info
597 OPEN c_get_global_param;
598 FETCH c_get_global_param
599 INTO l_param_info;
600 IF c_get_global_param%NOTFOUND THEN
601 FND_MESSAGE.Set_Name('WSH','WSH_PARAM_NOT_DEFINED');
602 RAISE NO_DATA_FOUND;
603 END IF;
604
605 -- Insert cached info into PL/SQL table
606
607 IF g_global_parameters.count = 0 THEN
608 g_global_parameters(1) := l_param_info;
609 ELSE
610 i := g_global_parameters.LAST + 1;
611 g_global_parameters(i) := l_param_info;
612 END IF;
613
614 x_param_info := l_param_info;
615
616 -- Debug Statements
617
618 IF l_debug_on THEN
619 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTO_RATE_TP_REL_TRIPS',X_PARAM_INFO.AUTO_RATE_TP_REL_TRIPS);
620 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TL_PRIN_COST_ALLOC_BASIS',X_PARAM_INFO.TL_PRIN_COST_ALLOC_BASIS);
621 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TL_DISTANCE_ALLOC_BASIS',X_PARAM_INFO.TL_DISTANCE_ALLOC_BASIS);
622 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TL_STOP_COST_ALLOC_BASIS',X_PARAM_INFO.TL_STOP_COST_ALLOC_BASIS);
623 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTOFIRM_LOAD_TENDERED_TRIPS',X_PARAM_INFO.AUTOFIRM_LOAD_TENDERED_TRIPS);
624 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.CONSOLIDATE_BO_LINES',X_PARAM_INFO.CONSOLIDATE_BO_LINES);
625 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_WEIGHT_CLASS',X_PARAM_INFO.GU_WEIGHT_CLASS);
626 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_WEIGHT_UOM',X_PARAM_INFO.GU_WEIGHT_UOM);
627 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_VOLUME_UOM',X_PARAM_INFO.GU_VOLUME_UOM);
628 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_VOLUME_CLASS',X_PARAM_INFO.GU_VOLUME_CLASS);
629 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_DISTANCE_CLASS',X_PARAM_INFO.GU_DISTANCE_CLASS);
630 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_DISTANCE_UOM',X_PARAM_INFO.GU_DISTANCE_UOM);
631 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_DIMENSION_CLASS',X_PARAM_INFO.GU_DIMENSION_CLASS);
632 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_DIMENSION_UOM',X_PARAM_INFO.GU_DIMENSION_UOM);
633 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_CURRENCY_COUNTRY',X_PARAM_INFO.GU_CURRENCY_COUNTRY);
634 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_CURRENCY_UOM',X_PARAM_INFO.GU_CURRENCY_UOM);
635 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_TIME_CLASS',X_PARAM_INFO.GU_TIME_CLASS);
636 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.GU_TIME_UOM',X_PARAM_INFO.GU_TIME_UOM);
637 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEF_MILE_CALC_ON_CUST_FAC',X_PARAM_INFO.DEF_MILE_CALC_ON_CUST_FAC);
638 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEF_MILE_CALC_ON_SUPP_FAC',X_PARAM_INFO.DEF_MILE_CALC_ON_SUPP_FAC);
639 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEF_MILE_CALC_ON_ORG_FAC',X_PARAM_INFO.DEF_MILE_CALC_ON_ORG_FAC);
640 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEF_MILE_CALC_ON_CARR_FAC',X_PARAM_INFO.DEF_MILE_CALC_ON_CARR_FAC);
641 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TL_HWAY_DIS_EMP_CONSTANT',X_PARAM_INFO.TL_HWAY_DIS_EMP_CONSTANT);
642 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AVG_HWAY_SPEED',X_PARAM_INFO.AVG_HWAY_SPEED);
643 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DISTANCE_UOM',X_PARAM_INFO.DISTANCE_UOM);
644 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.TIME_UOM',X_PARAM_INFO.TIME_UOM);
645 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.UOM_FOR_NUM_OF_UNITS',X_PARAM_INFO.UOM_FOR_NUM_OF_UNITS);
646 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PALLET_ITEM_TYPE',X_PARAM_INFO.PALLET_ITEM_TYPE);
647 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE_CATEGORY',X_PARAM_INFO.ATTRIBUTE_CATEGORY);
648 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE1',X_PARAM_INFO.ATTRIBUTE1);
649 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE2',X_PARAM_INFO.ATTRIBUTE2);
650 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE3',X_PARAM_INFO.ATTRIBUTE3);
651 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE4',X_PARAM_INFO.ATTRIBUTE4);
652 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE5',X_PARAM_INFO.ATTRIBUTE5);
653 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE6',X_PARAM_INFO.ATTRIBUTE6);
654 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE7',X_PARAM_INFO.ATTRIBUTE7);
655 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE8',X_PARAM_INFO.ATTRIBUTE8);
656 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE9',X_PARAM_INFO.ATTRIBUTE9);
657 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE10',X_PARAM_INFO.ATTRIBUTE10);
658 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE11',X_PARAM_INFO.ATTRIBUTE11);
659 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE12',X_PARAM_INFO.ATTRIBUTE12);
660 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE13',X_PARAM_INFO.ATTRIBUTE13);
661 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE14',X_PARAM_INFO.ATTRIBUTE14);
662 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ATTRIBUTE15',X_PARAM_INFO.ATTRIBUTE15);
663 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.CREATION_DATE',X_PARAM_INFO.CREATION_DATE);
664 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.CREATED_BY',X_PARAM_INFO.CREATED_BY);
665 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_DATE',X_PARAM_INFO.LAST_UPDATE_DATE);
666 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATED_BY',X_PARAM_INFO.LAST_UPDATED_BY);
667 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_LOGIN',X_PARAM_INFO.LAST_UPDATE_LOGIN);
668 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ENFORCE_SHIP_METHOD',X_PARAM_INFO.ENFORCE_SHIP_METHOD);
669 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ALLOW_FUTURE_SHIP_DATE',X_PARAM_INFO.ALLOW_FUTURE_SHIP_DATE);
670 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEFER_INTERFACE',X_PARAM_INFO.DEFER_INTERFACE);
671 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.RATE_IB_DELS_FGT_TERM',X_PARAM_INFO.RATE_IB_DELS_FGT_TERM);
672 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.SKIP_RATE_OB_DELS_FGT_TERM',X_PARAM_INFO.SKIP_RATE_OB_DELS_FGT_TERM);
673 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEL_DATE_CALC_METHOD',X_PARAM_INFO.DEL_DATE_CALC_METHOD);
674 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.RATE_DS_DELS_FGT_TERM_ID',X_PARAM_INFO.RATE_DS_DELS_FGT_TERM_ID);
675 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.RAISE_BUSINESS_EVENTS',X_PARAM_INFO.RAISE_BUSINESS_EVENTS);
676 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ENABLE_TRACKING_WFS',X_PARAM_INFO.ENABLE_TRACKING_WFS);
677 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.ENABLE_SC_WF',X_PARAM_INFO.ENABLE_SC_WF);
678 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.EXPAND_CARRIER_RANKINGS',X_PARAM_INFO.EXPAND_CARRIER_RANKINGS);
679 --bug 7491598 DEFER OTM-PLANNED SHIPMENT INTERFACES ENHANCEMENT
680 WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.DEFER_PLAN_SHIPMENT_INTERFACE',X_PARAM_INFO.DEFER_PLAN_SHIPMENT_INTERFACE);
681 END IF;
682 --
683 -- Debug Statements
684 --
685 IF l_debug_on THEN
686 WSH_DEBUG_SV.pop(l_module_name);
687 END IF;
688 --
689 EXCEPTION
690 WHEN NO_DATA_FOUND THEN
691 IF c_get_global_param%ISOPEN THEN
692 CLOSE c_get_global_param;
693 END IF;
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 --
696 -- Debug Statements
697 --
698 IF l_debug_on THEN
699 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
700 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
701 END IF;
702 --
703 WHEN OTHERS THEN
704 FND_MESSAGE.Set_Name('WSH','WSH_UNEXP_ERROR');
705 FND_MESSAGE.Set_Token('PACKAGE','WSH_SHIPPING_PARAMS_PVT');
706 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
707 FND_MESSAGE.Set_Token('ORA_TEXT','');
708 IF c_get_global_param%ISOPEN THEN
709 CLOSE c_get_global_param;
710 END IF;
711 x_return_status := FND_API.G_RET_STS_ERROR;
712
713 --
714 -- Debug Statements
715 --
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '
718 || SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
719 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
720 END IF;
721 --
722 END Get_Global_Parameters;
723
724
725 Procedure Get( x_param_value_info IN OUT NOCOPY parameter_value_rec_typ,
726 x_return_status OUT NOCOPY VARCHAR2) is
727
728 cursor get_paramter_values_csr(p_organization_id in number) is
729 select class_code
730 , param_name
731 , decode(class_code
732 ,'ROUTING_RULES',nvl(param_value, 'N'),param_value) param_value
733 , param_data_type
734 from wsh_shipping_parameter_values
735 where organization_id = p_organization_id ;
736
737 type l_varchar2_tbl is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
738 l_param_value l_varchar2_tbl;
739 l_parameter_name wsh_util_core.column_tab_type;
740 l_param_value_info parameter_value_rec_typ;
741 temp_param_value_info parameter_value_rec_typ;
742 l_selective boolean;
743 l_row_count number;
744 l_found boolean ;
745 l_debug_on boolean;
746 x_row_count number;
747 l_return_status varchar2(10);
748 l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET ';
749
750
751 Begin
752 x_return_status := wsh_util_core.g_ret_sts_success;
753
754 If x_param_value_info.param_name.count > 0 then
755 l_selective := TRUE;
756 l_parameter_name := x_param_value_info.param_name;
757 Else
758 l_selective := FALSE;
759 End If;
760
761 l_param_value_info.organization_id := x_param_value_info.organization_id ;
762 temp_param_value_info := x_param_value_info;
763 x_param_value_info.class_code.delete;
764 x_param_value_info.param_name.delete;
765 x_param_value_info.param_data_type.delete;
766 x_param_value_info.param_value_num.delete;
767 x_param_value_info.param_value_chr.delete;
768 x_param_value_info.param_value_date.delete;
769
770 If g_parameter_values.count > 0 then
771 <<OUTER>>
772 For i in g_parameter_values.FIRST..g_parameter_values.LAST
773 Loop
774 If g_parameter_values(i).organization_id = temp_param_value_info.organization_id and l_selective then
775 l_found := TRUE;
776 If temp_param_value_info.param_name.count > 0 then
777 For j in temp_param_value_info.param_name.first.. temp_param_value_info.param_name.last
778 Loop
779 If g_parameter_values(i).param_name.count > 0 then
780 For k in g_parameter_values(i).param_name.first.. g_parameter_values(i).param_name.last
781 Loop
782 If g_parameter_values(i).param_name(k) = temp_param_value_info.param_name(j) then
783 x_row_count := nvl(x_row_count,0) + 1;
784 x_param_value_info.class_code(x_row_count) := g_parameter_values(i).class_code(k);
785 x_param_value_info.param_name(x_row_count) := g_parameter_values(i).param_name(k);
786 x_param_value_info.param_data_type(x_row_count) := g_parameter_values(i).param_data_type(k);
787 x_param_value_info.param_value_chr(x_row_count) := g_parameter_values(i).param_value_chr(k);
788 x_param_value_info.param_value_num(x_row_count) := g_parameter_values(i).param_value_num(k);
789 x_param_value_info.param_value_date(x_row_count) := g_parameter_values(i).param_value_date(k);
790 End If;
791 End Loop;
792 End If;
793 End Loop;
794 End If;
795 If l_found = TRUE then
796 exit OUTER;
797 End If;
798 Elsif g_parameter_values(i).organization_id = x_param_value_info.organization_id and not l_selective then
799 x_param_value_info := g_parameter_values(i);
800 l_found := TRUE;
801 exit OUTER;
802 End If;
803 End Loop;
804
805 If l_found and l_debug_on then
806 print_debug_info(l_module_name
807 ,'WSH_SHIPPING_PARAMETER_VALUES'
808 ,null
809 ,x_param_value_info
810 ,l_return_status);
811 WSH_DEBUG_SV.pop(l_module_name);
812 End If;
813 If l_found then
814 return;
815 End If;
816 End If;
817
818 Open get_paramter_values_csr(x_param_value_info.organization_id);
819 Fetch get_paramter_values_csr bulk collect into
820 l_param_value_info.class_code
821 ,l_param_value_info.param_name
822 ,l_param_value
823 ,l_param_value_info.param_data_type;
824 Close get_paramter_values_csr;
825
826 If l_param_value_info.param_name.COUNT > 0 then
827 For i in l_param_value_info.param_name.FIRST..l_param_value_info.param_name.LAST
828 Loop
829 If l_param_value_info.param_data_type(i) = 'VARCHAR2' then
830 l_param_value_info.param_value_num(i) := NULL;
831 l_param_value_info.param_value_chr(i) := l_param_value(i);
832 l_param_value_info.param_value_date(i):= NULL;
833 Elsif l_param_value_info.param_data_type(i) = 'DATE' then
834 l_param_value_info.param_value_num(i) := NULL;
835 l_param_value_info.param_value_chr(i) := NULL;
836 l_param_value_info.param_value_date(i):= fnd_date.canonical_to_date(l_param_value(i));
837 Elsif l_param_value_info.param_data_type(i) = 'NUMBER' then
838 l_param_value_info.param_value_num(i) := fnd_number.canonical_to_number(l_param_value(i));
839 l_param_value_info.param_value_chr(i) := NULL;
840 l_param_value_info.param_value_date(i):= NULL;
841 End If;
842 End Loop;
843 End if;
844
845 If l_selective then
846 l_row_count := 0;
847 For i in l_parameter_name.FIRST..l_parameter_name.LAST
848 Loop
849 If l_param_value_info.param_name.count > 0 then
850 For j in l_param_value_info.param_name.FIRST..l_param_value_info.param_name.LAST
851 Loop
852 If l_parameter_name(i) = l_param_value_info.param_name(j) then
853 l_row_count := l_row_count+1;
854 x_param_value_info.class_code(l_row_count) := l_param_value_info.class_code(j);
855 x_param_value_info.param_name(l_row_count) := l_param_value_info.param_name(j);
856 x_param_value_info.param_data_type(l_row_count) := l_param_value_info.param_data_type(j);
857 x_param_value_info.param_value_num(l_row_count) := l_param_value_info.param_value_num(j);
858 x_param_value_info.param_value_chr(l_row_count) := l_param_value_info.param_value_chr(j);
859 x_param_value_info.param_value_date(l_row_count):= l_param_value_info.param_value_date(j);
860 End If;
861 End Loop;
862 End If;
863 End Loop;
864 Else
865 x_param_value_info := l_param_value_info;
866 End If;
867
868 g_parameter_values(nvl(g_parameter_values.count,0)+1) := l_param_value_info;
869
870 If l_debug_on then
871 wsh_debug_sv.pop(l_module_name);
872 End If;
873
874 Exception
875 When others then
876 x_return_status := wsh_util_core.g_ret_sts_error;
877 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
878 fnd_message.set_token('PACKAGE',l_module_name);
879 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
880 fnd_message.set_token('ORA_TEXT',SQLERRM);
881 If l_debug_on then
882 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
883 wsh_debug_sv.log(l_module_name,'x_return_status = : ',x_return_status);
884 wsh_debug_sv.pop(l_module_name);
885 End If;
886
887 End Get;
888
889
890 Procedure insert_parameter_values (p_ins_ship_par_val_rec parameter_value_rec_typ ) is
891 l_user_id number:= fnd_global.user_id;
892 l_login_id number:= fnd_global.login_id;
893 Begin
894 forall i in p_ins_ship_par_val_rec.class_code.first..p_ins_ship_par_val_rec.class_code.last
895 insert into wsh_shipping_parameter_values(
896 organization_id,
897 class_code,
898 param_name,
899 param_value,
900 param_data_type,
901 creation_date,
902 created_by,
903 last_update_date,
904 last_updated_by,
905 last_update_login)
906 values (p_ins_ship_par_val_rec.organization_id
907 ,p_ins_ship_par_val_rec.class_code(i)
908 ,p_ins_ship_par_val_rec.param_name(i)
909 ,p_ins_ship_par_val_rec.param_value_chr(i)
910 ,p_ins_ship_par_val_rec.param_data_type(i)
911 ,sysdate
912 ,l_user_id
913 ,sysdate
914 ,l_user_id
915 ,l_login_id);
916 End insert_parameter_values;
917
918
919 Procedure create_parameter(
920 p_ship_par_rec IN parameter_rec_typ,
921 p_ship_par_val_rec IN parameter_value_rec_typ,
922 x_return_status OUT NOCOPY VARCHAR2) is
923
924 type l_parameter_value_type is table of wsh_shipping_parameter_values.PARAM_VALUE%TYPE index by binary_integer;
925 type l_parameter_name_type is table of wsh_shipping_parameter_values.PARAM_NAME%TYPE index by binary_integer;
926
927 l_freight_terms_value l_parameter_value_type;
928 l_param_name l_parameter_name_type;
929 l_param_value_tbl l_parameter_value_type;
930
931 l_ship_par_val_rec parameter_value_rec_typ;
932 l_freight_terms_rec parameter_value_rec_typ;
933
934 l_user_id number:= fnd_global.user_id;
935 l_login_id number:= fnd_global.login_id;
936 l_debug_on boolean;
937 l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_PARAMETER';
938 l_return_status varchar2(1);
939 l_org_name varchar2(2000) := wsh_util_core.get_org_name(p_ship_par_rec.organization_id);
940 handle_exception exception;
941 l_freight_count number;
942 l_param_count number ;
943 l_sqlerrm varchar2(2000);
944
945 Begin
946 x_return_status := wsh_util_core.g_ret_sts_success;
947 l_debug_on := wsh_debug_interface.g_debug;
948 If l_debug_on is null then
949 l_debug_on := wsh_debug_sv.is_debug_enabled;
950 End If;
951 If l_debug_on then
952 wsh_debug_sv.push(l_module_name);
953 print_debug_info(l_module_name
954 ,'ALL'
955 ,p_ship_par_rec
956 ,p_ship_par_val_rec
957 ,l_return_status);
958 End If;
959
960 Begin
961 --bug# 6689448 (replenishment project): added the field dynamic_replenishment_flag
962 Insert into wsh_shipping_parameters (
963 goods_dispatched_account,
964 location_id,
965 organization_id,
966 weight_uom_class,
967 volume_uom_class,
968 weight_volume_flag,
969 inv_controls_container_flag,
970 percent_fill_basis_flag,
971 trip_report_set_id,
972 delivery_report_set_id,
973 pack_slip_required_flag,
974 pick_sequence_rule_id,
975 pick_grouping_rule_id,
976 print_pick_slip_mode,
977 pick_release_report_set_id,
978 autocreate_del_orders_flag,
979 default_stage_subinventory,
980 default_stage_locator_id,
981 autodetail_pr_flag,
982 enforce_packing_flag,
983 group_by_customer_flag,
984 group_by_fob_flag,
985 group_by_freight_terms_flag,
986 group_by_intmed_ship_to_flag,
987 group_by_ship_method_flag,
988 group_by_carrier_flag,
989 attribute_category,
990 attribute1,
991 attribute2,
992 attribute3,
993 attribute4,
994 attribute5,
995 attribute6,
996 attribute7,
997 attribute8,
998 attribute9,
999 attribute10,
1000 attribute11,
1001 attribute12,
1002 attribute13,
1003 attribute14,
1004 attribute15,
1005 creation_date,
1006 created_by,
1007 last_update_date,
1008 last_updated_by,
1009 last_update_login,
1010 pick_slip_lines,
1011 autocreate_deliveries_flag,
1012 freight_class_cat_set_id,
1013 commodity_code_cat_set_id,
1014 enforce_ship_set_and_smc,
1015 auto_send_doc_flag,
1016 itm_additional_country_code,
1017 auto_select_carrier,
1018 ship_confirm_rule_id,
1019 autopack_level,
1020 task_planning_flag,
1021 appending_limit,
1022 export_screening_flag,
1023 autocreate_del_orders_pr_flag,
1024 fpa_positive_tol_amt,
1025 fpa_negative_tol_amt,
1026 fpa_positive_tol_percentage,
1027 fpa_negative_tol_percentage,
1028 fpa_default_freight_account,
1029 auto_apply_routing_rules,
1030 auto_calc_fgt_rate_cr_del,
1031 auto_calc_fgt_rate_append_del,
1032 auto_calc_fgt_rate_sc_del,
1033 raise_business_events,
1034 enable_tracking_wfs,
1035 enable_sc_wf,
1036 check_on_hand,
1037 --OTM R12
1038 max_net_weight,
1039 max_gross_weight,
1040 --OTM R12 Org-Specific
1041 otm_enabled,
1042 dynamic_replenishment_flag,
1043 -- OTM Dock Door Appt Sched Proj
1044 dock_appt_scheduling_flag,
1045 retain_nonstaged_det_flag, --Bug 7131800
1046 -- Bug 8446283 (Added wt/vol UOM codes on shipping parameters forms)
1047 weight_uom_code,
1048 volume_uom_code
1049 -- Bug 8446283 : end
1050 )
1051 Values ( p_ship_par_rec.goods_dispatched_account,
1052 p_ship_par_rec.location_id,
1053 p_ship_par_rec.organization_id,
1054 p_ship_par_rec.weight_uom_class,
1055 p_ship_par_rec.volume_uom_class,
1056 p_ship_par_rec.weight_volume_flag,
1057 p_ship_par_rec.inv_controls_container_flag,
1058 p_ship_par_rec.percent_fill_basis_flag,
1059 p_ship_par_rec.trip_report_set_id,
1060 p_ship_par_rec.delivery_report_set_id,
1061 p_ship_par_rec.pack_slip_required_flag,
1062 p_ship_par_rec.pick_sequence_rule_id,
1063 p_ship_par_rec.pick_grouping_rule_id,
1064 p_ship_par_rec.print_pick_slip_mode,
1065 p_ship_par_rec.pick_release_report_set_id,
1066 p_ship_par_rec.autocreate_del_orders_flag,
1067 p_ship_par_rec.default_stage_subinventory,
1068 p_ship_par_rec.default_stage_locator_id,
1069 p_ship_par_rec.autodetail_pr_flag,
1070 p_ship_par_rec.enforce_packing_flag,
1071 p_ship_par_rec.group_by_customer_flag,
1072 p_ship_par_rec.group_by_fob_flag,
1073 p_ship_par_rec.group_by_freight_terms_flag,
1074 p_ship_par_rec.group_by_intmed_ship_to_flag,
1075 p_ship_par_rec.group_by_ship_method_flag,
1076 p_ship_par_rec.group_by_carrier_flag,
1077 p_ship_par_rec.attribute_category,
1078 p_ship_par_rec.attribute1,
1079 p_ship_par_rec.attribute2,
1080 p_ship_par_rec.attribute3,
1081 p_ship_par_rec.attribute4,
1082 p_ship_par_rec.attribute5,
1083 p_ship_par_rec.attribute6,
1084 p_ship_par_rec.attribute7,
1085 p_ship_par_rec.attribute8,
1086 p_ship_par_rec.attribute9,
1087 p_ship_par_rec.attribute10,
1088 p_ship_par_rec.attribute11,
1089 p_ship_par_rec.attribute12,
1090 p_ship_par_rec.attribute13,
1091 p_ship_par_rec.attribute14,
1092 p_ship_par_rec.attribute15,
1093 nvl(p_ship_par_rec.creation_date,sysdate),
1094 nvl(p_ship_par_rec.created_by,l_user_id),
1095 nvl(p_ship_par_rec.last_update_date,sysdate),
1096 nvl(p_ship_par_rec.last_updated_by,l_user_id),
1097 nvl(p_ship_par_rec.last_update_login,l_login_id),
1098 p_ship_par_rec.pick_slip_lines,
1099 p_ship_par_rec.autocreate_deliveries_flag,
1100 p_ship_par_rec.freight_class_cat_set_id,
1101 p_ship_par_rec.commodity_code_cat_set_id,
1102 p_ship_par_rec.enforce_ship_set_and_smc,
1103 p_ship_par_rec.auto_send_doc_flag,
1104 p_ship_par_rec.itm_additional_country_code,
1105 p_ship_par_rec.auto_select_carrier,
1106 p_ship_par_rec.ship_confirm_rule_id,
1107 p_ship_par_rec.autopack_level,
1108 p_ship_par_rec.task_planning_flag,
1109 p_ship_par_rec.appending_limit,
1110 p_ship_par_rec.export_screening_flag,
1111 p_ship_par_rec.autocreate_del_orders_pr_flag,
1112 p_ship_par_rec.fpa_positive_tol_amt,
1113 p_ship_par_rec.fpa_negative_tol_amt,
1114 p_ship_par_rec.fpa_positive_tol_percentage,
1115 p_ship_par_rec.fpa_negative_tol_percentage,
1116 p_ship_par_rec.fpa_default_freight_account,
1117 p_ship_par_rec.auto_apply_routing_rules,
1118 p_ship_par_rec.auto_calc_fgt_rate_cr_del,
1119 p_ship_par_rec.auto_calc_fgt_rate_append_del,
1120 p_ship_par_rec.auto_calc_fgt_rate_sc_del,
1121 p_ship_par_rec.raise_business_events,
1122 p_ship_par_rec.enable_tracking_wfs,
1123 p_ship_par_rec.enable_sc_wf,
1124 p_ship_par_rec.check_on_hand,
1125 --OTM R12
1126 p_ship_par_rec.max_net_weight,
1127 p_ship_par_rec.max_gross_weight,
1128 --OTM R12 Org-Specific
1129 p_ship_par_rec.otm_enabled,
1130 p_ship_par_rec.dynamic_replenishment_flag,
1131 --OTM Dock Door App Sched Proj
1132 p_ship_par_rec.dock_appt_scheduling_flag,
1133 --Bug 7131800
1134 p_ship_par_rec.retain_nonstaged_det_flag,
1135 -- Bug 8446283 : begin
1136 p_ship_par_rec.weight_uom_code,
1137 p_ship_par_rec.volume_uom_code
1138 -- Bug 8446283 : end
1139 );
1140 Exception
1141 When others then
1142 If l_debug_on then
1143 wsh_debug_sv.log(l_module_name,'After insert into WSH_SHIPPING_PARAMETERS SQLERRM : ',sqlerrm);
1144 End If;
1145 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1146 fnd_message.set_token('PACKAGE',l_module_name);
1147 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1148 fnd_message.set_token('ORA_TEXT',SQLERRM);
1149 raise handle_exception;
1150 End;
1151 If l_debug_on then
1152 wsh_debug_sv.log(l_module_name,'After insert into WSH_SHIPPING_PARAMETERS SQLERRM : ',sqlerrm);
1153 End If;
1154
1155
1156 If p_ship_par_val_rec.class_code.count > 0 then /* Start of Main If logic */
1157 For i in p_ship_par_val_rec.class_code.FIRST..p_ship_par_val_rec.class_code.LAST
1158 Loop
1159 If p_ship_par_val_rec.param_name(i) = 'FREIGHT_TERMS' then
1160 l_freight_count := nvl(l_freight_terms_rec.class_code.count,0) + 1;
1161 l_freight_terms_rec.class_code(l_freight_count) := p_ship_par_val_rec.class_code(i);
1162 l_freight_terms_rec.param_name(l_freight_count) := p_ship_par_val_rec.param_name(i);
1163 l_freight_terms_rec.param_data_type(l_freight_count) := p_ship_par_val_rec.param_data_type(i);
1164 l_freight_terms_rec.param_value_chr(l_freight_count) := p_ship_par_val_rec.param_value_chr(i);
1165 Else
1166 l_param_count := nvl(l_ship_par_val_rec.class_code.count,0) + 1;
1167 l_ship_par_val_rec.organization_id := p_ship_par_val_rec.organization_id;
1168 l_ship_par_val_rec.class_code(l_param_count) := p_ship_par_val_rec.class_code(i);
1169 l_ship_par_val_rec.param_name(l_param_count) := p_ship_par_val_rec.param_name(i);
1170 l_ship_par_val_rec.param_data_type(l_param_count) := p_ship_par_val_rec.param_data_type(i);
1171
1172 If p_ship_par_val_rec.param_data_type(i) = 'VARCHAR2' then
1173 If p_ship_par_val_rec.param_name(i) = 'SKIP_RTNG_RULE_AC_TRIP' and
1174 p_ship_par_rec.auto_apply_routing_rules <> 'D' then
1175 l_ship_par_val_rec.param_value_chr(l_param_count) := 'N';
1176 Else
1177 l_ship_par_val_rec.param_value_chr(l_param_count) := p_ship_par_val_rec.param_value_chr(i);
1178 End If;
1179 Elsif p_ship_par_val_rec.param_data_type(i) = 'NUMBER' then
1180 l_ship_par_val_rec.param_value_chr(l_param_count) := FND_NUMBER.NUMBER_TO_CANONICAL(p_ship_par_val_rec.param_value_num(i));
1181 Elsif p_ship_par_val_rec.param_data_type(i) = 'DATE' then
1182 l_ship_par_val_rec.param_value_chr(l_param_count) := fnd_date.date_to_canonical(p_ship_par_val_rec.param_value_date(i)) ;
1183 End If;
1184
1185 End If;
1186 End Loop;
1187
1188 If l_freight_terms_rec.class_code.count > 0 then
1189
1190 Begin
1191 Delete from wsh_shipping_parameter_values
1192 Where organization_id = l_freight_terms_rec.organization_id
1193 and class_code = 'FREIGHT_TERMS'
1194 and param_name = 'FREIGHT_TERMS';
1195 If l_debug_on then
1196 wsh_debug_sv.log(l_module_name,'After delete of Freight Terms from wsh_shipping_parameter_values : ',sqlerrm);
1197 End If;
1198 Exception
1199 When others then
1200 If l_debug_on then
1201 wsh_debug_sv.log(l_module_name,'After delete of Freight Terms from wsh_shipping_parameter_values : ',sqlerrm);
1202 End If;
1203 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1204 fnd_message.set_token('PACKAGE',l_module_name);
1205 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1206 fnd_message.set_token('ORA_TEXT',SQLERRM);
1207 Raise handle_exception;
1208 End;
1209
1210 l_freight_terms_rec.organization_id := p_ship_par_val_rec.organization_id;
1211 create_freight_terms(l_freight_terms_rec
1212 ,l_return_status);
1213 If l_return_status <> wsh_util_core.g_ret_sts_success then
1214 If l_debug_on then
1215 wsh_debug_sv.log(l_module_name,'After call to create_freight_terms procedure l_return_status = ',l_return_status);
1216 End If;
1217 raise handle_exception;
1218 End If;
1219 If l_debug_on then
1220 wsh_debug_sv.log(l_module_name,'After call to create_freight_terms procedure l_return_status = ',l_return_status);
1221 End If;
1222 End If;
1223
1224 If l_debug_on then
1225 wsh_debug_sv.log(l_module_name,'Values to be inserted into WSH_SHIPPING_PARAMETER_VALUES as follows');
1226 print_debug_info(l_module_name
1227 ,'WSH_SHIPPING_PARAMETER_VALUES'
1228 ,p_ship_par_rec
1229 ,l_ship_par_val_rec
1230 ,l_return_status);
1231 End If;
1232
1233
1234 If l_ship_par_val_rec.class_code.count > 0 then
1235 Begin
1236 insert_parameter_values (l_ship_par_val_rec);
1237 /*
1238 FORALL i in l_ship_par_val_rec.class_code.FIRST..l_ship_par_val_rec.class_code.LAST
1239 INSERT INTO wsh_shipping_parameter_values(
1240 organization_id,
1241 class_code,
1242 param_name,
1243 param_value,
1244 param_data_type,
1245 creation_date,
1246 created_by,
1247 last_update_date,
1248 last_updated_by,
1249 last_update_login)
1250 VALUES (l_ship_par_val_rec.organization_id,
1251 l_ship_par_val_rec.class_code(i),
1252 l_ship_par_val_rec.param_name(i),
1253 l_ship_par_val_rec.param_value_chr(i),
1254 p_ship_par_val_rec.param_data_type(i),
1255 sysdate,
1256 l_user_id,
1257 sysdate,
1258 l_user_id,
1259 l_login_id);
1260 */
1261
1262 If l_debug_on then
1263 wsh_debug_sv.log(l_module_name,'After insert of values into wsh_shipping_parameter_values table : ',sqlerrm);
1264 End If;
1265
1266 Exception
1267 When Others then
1268 If l_debug_on then
1269 wsh_debug_sv.log(l_module_name,'After insert of values into wsh_shipping_parameter_values table : ',sqlerrm);
1270 End If;
1271 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1272 fnd_message.set_token('PACKAGE',l_module_name);
1273 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1274 fnd_message.set_token('ORA_TEXT',SQLERRM);
1275 raise handle_exception;
1276 End;
1277 End If;
1278
1279 End If; /* End of Main If logic */
1280
1281 If l_debug_on then
1282 wsh_debug_sv.log(l_module_name,'After insert into WSH_SHIPPING_PARAMETER_VALUES SQLERRM : ',sqlerrm);
1283 wsh_debug_sv.pop(l_module_name);
1284 End If;
1285
1286 Exception
1287 When handle_exception then
1288 x_return_status := wsh_util_core.g_ret_sts_error;
1289 If l_debug_on then
1290 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
1291 wsh_debug_sv.log(l_module_name,'x_return_status = : ',x_return_status);
1292 wsh_debug_sv.pop(l_module_name);
1293 End If;
1294
1295 When others then
1296 x_return_status := wsh_util_core.g_ret_sts_error;
1297 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1298 fnd_message.set_token('PACKAGE',l_module_name);
1299 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1300 fnd_message.set_token('ORA_TEXT',SQLERRM);
1301 If l_debug_on then
1302 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
1303 wsh_debug_sv.log(l_module_name,'x_return_status = : ',x_return_status);
1304 wsh_debug_sv.pop(l_module_name);
1305 End If;
1306
1307 End create_parameter ;
1308
1309
1310 Procedure update_parameter(
1311 p_ship_par_rec IN parameter_rec_typ,
1312 p_ship_par_val_rec IN parameter_value_rec_typ,
1313 x_return_status OUT NOCOPY VARCHAR2) is
1314
1315 cursor l_shipping_param_values_csr ( p_organization_id in number ) is
1316 select param_name
1317 ,count(*) record_count
1318 from wsh_shipping_parameter_values
1319 where organization_id = p_organization_id
1320 and param_name in (
1321 'BOL_TEMPLATE'
1322 ,'EVAL_RULE_BASED_ON_SHIPTO_CUST'
1323 ,'MBOL_TEMPLATE'
1324 ,'PACKSLIP_TEMPLATE'
1325 ,'SKIP_RTNG_RULE_AC_TRIP')
1326 group by param_name;
1327
1328 type l_param_value_tbl_type is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1329 type l_param_name_tbl_type is table of wsh_shipping_parameter_values.param_name%TYPE index by binary_integer;
1330 type l_param_class_code_tbl_type is table of wsh_shipping_parameter_values.class_code%TYPE index by binary_integer;
1331 type l_param_data_type_tbl_type is table of wsh_shipping_parameter_values.param_data_type%TYPE index by binary_integer;
1332 type l_number_tbl is table of number index by binary_integer;
1333
1334 l_param_name_tbl l_param_name_tbl_type;
1335 l_record_count_tbl l_number_tbl;
1336 l_user_id number := fnd_global.user_id;
1337 l_login_id number := fnd_global.login_id;
1338 l_all_update boolean := FALSE;
1339 l_all_insert boolean := FALSE;
1340 l_update_insert boolean := FALSE;
1341 l_record_exists boolean := FALSE;
1342 ins_count number;
1343 upd_count number;
1344 l_debug_on boolean;
1345 l_org_name varchar2(2000) := wsh_util_core.get_org_name(p_ship_par_rec.organization_id);
1346 l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_PARAMETER';
1347 l_freight_count number ;
1348 l_return_status varchar2(10);
1349 l_freight_terms varchar2(240);
1350
1351 l_freight_terms_value l_param_value_tbl_type;
1352 l_freight_terms_rec parameter_value_rec_typ;
1353 l_ins_ship_par_val_rec parameter_value_rec_typ;
1354 l_upd_ship_par_val_rec parameter_value_rec_typ;
1355
1356 Cursor check_duplicate_csr(p_param_name in varchar2
1357 ,p_param_value in varchar2
1358 ,p_class_code in varchar2
1359 ,p_organization_id in number ) is
1360 Select param_value
1361 from wsh_shipping_parameter_values
1362 where param_name = p_param_name
1363 and class_code = p_class_code
1364 and param_value = p_param_value
1365 and organization_id = p_organization_id;
1366
1367 handle_exception exception;
1368
1369
1370 Begin
1371 x_return_status := wsh_util_core.g_ret_sts_success;
1372 l_debug_on := wsh_debug_interface.g_debug;
1373 If l_debug_on is null then
1374 l_debug_on := wsh_debug_sv.is_debug_enabled;
1375 End If;
1376 If l_debug_on then
1377 wsh_debug_sv.push(l_module_name);
1378 End If;
1379 Begin
1380 UPDATE wsh_shipping_parameters set
1381 goods_dispatched_account = p_ship_par_rec.goods_dispatched_account,
1382 location_id = p_ship_par_rec.location_id,
1383 organization_id = p_ship_par_rec.organization_id,
1384 weight_uom_class = p_ship_par_rec.weight_uom_class,
1385 volume_uom_class = p_ship_par_rec.volume_uom_class,
1386 weight_volume_flag = p_ship_par_rec.weight_volume_flag,
1387 inv_controls_container_flag = p_ship_par_rec.inv_controls_container_flag,
1388 percent_fill_basis_flag = p_ship_par_rec.percent_fill_basis_flag,
1389 trip_report_set_id = p_ship_par_rec.trip_report_set_id,
1390 delivery_report_set_id = p_ship_par_rec.delivery_report_set_id,
1391 pack_slip_required_flag = p_ship_par_rec.pack_slip_required_flag,
1392 pick_sequence_rule_id = p_ship_par_rec.pick_sequence_rule_id,
1393 pick_grouping_rule_id = p_ship_par_rec.pick_grouping_rule_id,
1394 print_pick_slip_mode = p_ship_par_rec.print_pick_slip_mode,
1395 pick_release_report_set_id = p_ship_par_rec.pick_release_report_set_id,
1396 autocreate_del_orders_flag = p_ship_par_rec.autocreate_del_orders_flag,
1397 default_stage_subinventory = p_ship_par_rec.default_stage_subinventory,
1398 default_stage_locator_id = p_ship_par_rec.default_stage_locator_id,
1399 autodetail_pr_flag = p_ship_par_rec.autodetail_pr_flag,
1400 enforce_packing_flag = p_ship_par_rec.enforce_packing_flag,
1401 group_by_customer_flag = p_ship_par_rec.group_by_customer_flag,
1402 group_by_fob_flag = p_ship_par_rec.group_by_fob_flag,
1403 group_by_freight_terms_flag = p_ship_par_rec.group_by_freight_terms_flag,
1404 group_by_intmed_ship_to_flag = p_ship_par_rec.group_by_intmed_ship_to_flag,
1405 group_by_ship_method_flag = p_ship_par_rec.group_by_ship_method_flag,
1406 group_by_carrier_flag = p_ship_par_rec.group_by_carrier_flag,
1407 attribute_category = p_ship_par_rec.attribute_category,
1408 attribute1 = p_ship_par_rec.attribute1,
1409 attribute2 = p_ship_par_rec.attribute2,
1410 attribute3 = p_ship_par_rec.attribute3,
1411 attribute4 = p_ship_par_rec.attribute4,
1412 attribute5 = p_ship_par_rec.attribute5,
1413 attribute6 = p_ship_par_rec.attribute6,
1414 attribute7 = p_ship_par_rec.attribute7,
1415 attribute8 = p_ship_par_rec.attribute8,
1416 attribute9 = p_ship_par_rec.attribute9,
1417 attribute10 = p_ship_par_rec.attribute10,
1418 attribute11 = p_ship_par_rec.attribute11,
1419 attribute12 = p_ship_par_rec.attribute12,
1420 attribute13 = p_ship_par_rec.attribute13,
1421 attribute14 = p_ship_par_rec.attribute14,
1422 attribute15 = p_ship_par_rec.attribute15,
1423 last_update_date = nvl(p_ship_par_rec.last_update_date,sysdate),
1424 last_updated_by = nvl(p_ship_par_rec.last_updated_by,l_user_id),
1425 last_update_login = nvl(p_ship_par_rec.last_update_login,l_login_id),
1426 pick_slip_lines = p_ship_par_rec.pick_slip_lines,
1427 autocreate_deliveries_flag = p_ship_par_rec.autocreate_deliveries_flag,
1428 freight_class_cat_set_id = p_ship_par_rec.freight_class_cat_set_id,
1429 commodity_code_cat_set_id = p_ship_par_rec.commodity_code_cat_set_id,
1430 enforce_ship_set_and_smc = p_ship_par_rec.enforce_ship_set_and_smc,
1431 auto_send_doc_flag = p_ship_par_rec.auto_send_doc_flag,
1432 itm_additional_country_code = p_ship_par_rec.itm_additional_country_code,
1433 auto_select_carrier = p_ship_par_rec.auto_select_carrier,
1434 ship_confirm_rule_id = p_ship_par_rec.ship_confirm_rule_id,
1435 autopack_level = p_ship_par_rec.autopack_level,
1436 task_planning_flag = p_ship_par_rec.task_planning_flag,
1437 appending_limit = p_ship_par_rec.appending_limit,
1438 export_screening_flag = p_ship_par_rec.export_screening_flag,
1439 autocreate_del_orders_pr_flag = p_ship_par_rec.autocreate_del_orders_pr_flag,
1440 fpa_positive_tol_amt = p_ship_par_rec.fpa_positive_tol_amt,
1441 fpa_negative_tol_amt = p_ship_par_rec.fpa_negative_tol_amt,
1442 fpa_positive_tol_percentage = p_ship_par_rec.fpa_positive_tol_percentage,
1443 fpa_negative_tol_percentage = p_ship_par_rec.fpa_negative_tol_percentage,
1444 fpa_default_freight_account = p_ship_par_rec.fpa_default_freight_account,
1445 auto_apply_routing_rules = p_ship_par_rec.auto_apply_routing_rules,
1446 auto_calc_fgt_rate_cr_del = p_ship_par_rec.auto_calc_fgt_rate_cr_del,
1447 auto_calc_fgt_rate_append_del = p_ship_par_rec.auto_calc_fgt_rate_append_del,
1448 auto_calc_fgt_rate_sc_del = p_ship_par_rec.auto_calc_fgt_rate_sc_del,
1449 raise_business_events = p_ship_par_rec.raise_business_events,
1450 enable_tracking_wfs = p_ship_par_rec.enable_tracking_wfs,
1451 enable_sc_wf = p_ship_par_rec.enable_sc_wf,
1452 check_on_hand = p_ship_par_rec.check_on_hand,
1453 --OTM R12
1454 max_net_weight = p_ship_par_rec.max_net_weight,
1455 max_gross_weight = p_ship_par_rec.max_gross_weight,
1456 --OTM R12 Org-Specific
1457 otm_enabled = p_ship_par_rec.otm_enabled,
1458 dynamic_replenishment_flag = p_ship_par_rec.dynamic_replenishment_flag, --bug# 6689448 (replenishment project)
1459 dock_appt_scheduling_flag = p_ship_par_rec.dock_appt_scheduling_flag, --bug 6700792: OTM Dock Door App Sched Proj
1460 retain_nonstaged_det_flag = p_ship_par_rec.retain_nonstaged_det_flag, --Bug 7131800
1461 -- Bug 8446283 (Added wt/vol UOM codes on shipping parameters forms)
1462 weight_uom_code = p_ship_par_rec.weight_uom_code,
1463 volume_uom_code = p_ship_par_rec.volume_uom_code
1464 -- Bug 8446283 : end
1465 WHERE organization_id = p_ship_par_rec.organization_id;
1466
1467 If l_debug_on then
1468 wsh_debug_sv.log(l_module_name,'After update of values in wsh_shipping_parameters table : ',sqlerrm);
1469 End If;
1470
1471 Exception
1472 When others then
1473 If l_debug_on then
1474 wsh_debug_sv.log(l_module_name,'After update of values in wsh_shipping_parameters table : ',sqlerrm);
1475 End If;
1476 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1477 fnd_message.set_token('PACKAGE',l_module_name);
1478 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1479 fnd_message.set_token('ORA_TEXT',SQLERRM);
1480 raise handle_exception;
1481 End;
1482
1483
1484 If p_ship_par_val_rec.class_code.count > 0 then /* Start of Main IF logic */
1485 open l_shipping_param_values_csr(p_ship_par_val_rec.organization_id);
1486 fetch l_shipping_param_values_csr bulk collect into
1487 l_param_name_tbl,
1488 l_record_count_tbl;
1489 close l_shipping_param_values_csr;
1490
1491 If l_param_name_tbl.count = 0 then
1492 l_all_insert := TRUE;
1493 End If;
1494
1495 For i in p_ship_par_val_rec.class_code.FIRST..p_ship_par_val_rec.class_code.LAST
1496 Loop
1497 If p_ship_par_val_rec.param_name(i) = 'FREIGHT_TERMS' then
1498 l_freight_count := nvl(l_freight_terms_rec.class_code.count,0) + 1;
1499 l_freight_terms_rec.class_code(l_freight_count) := p_ship_par_val_rec.class_code(i);
1500 l_freight_terms_rec.param_name(l_freight_count) := p_ship_par_val_rec.param_name(i);
1501 l_freight_terms_rec.param_data_type(l_freight_count) := p_ship_par_val_rec.param_data_type(i);
1502 l_freight_terms_rec.param_value_chr(l_freight_count) := p_ship_par_val_rec.param_value_chr(i);
1503 Else
1504 If l_all_insert then
1505 ins_count := nvl(l_ins_ship_par_val_rec.class_code.count,0) + 1;
1506 l_ins_ship_par_val_rec.class_code(ins_count) := p_ship_par_val_rec.class_code(i);
1507 l_ins_ship_par_val_rec.param_name(ins_count) := p_ship_par_val_rec.param_name(i);
1508 l_ins_ship_par_val_rec.param_data_type(ins_count) := p_ship_par_val_rec.param_data_type(i);
1509
1510 If p_ship_par_val_rec.param_data_type(i) = 'VARCHAR2' then
1511 l_ins_ship_par_val_rec.param_value_chr(ins_count) := p_ship_par_val_rec.param_value_chr(i);
1512 Elsif p_ship_par_val_rec.param_data_type(i) = 'NUMBER' then
1513 l_ins_ship_par_val_rec.param_value_chr(ins_count) :=
1514 FND_NUMBER.NUMBER_TO_CANONICAL(p_ship_par_val_rec.param_value_num(i));
1515 Elsif p_ship_par_val_rec.param_data_type(i) = 'DATE' then
1516 l_upd_ship_par_val_rec.param_value_chr(ins_count) :=
1517 fnd_date.date_to_canonical(p_ship_par_val_rec.param_value_date(i)) ;
1518 End If;
1519 Else
1520 <<INNER>>
1521 For j in l_param_name_tbl.FIRST..l_param_name_tbl.LAST
1522 Loop
1523 l_record_exists := FALSE;
1524 If (p_ship_par_val_rec.param_name(i) = l_param_name_tbl(j)) then
1525 l_record_exists := TRUE;
1526 Exit INNER;
1527 End If;
1528 End Loop;
1529 If l_record_exists then
1530 upd_count := nvl(l_upd_ship_par_val_rec.class_code.count,0) + 1;
1531 l_upd_ship_par_val_rec.class_code(upd_count) := p_ship_par_val_rec.class_code(i);
1532 l_upd_ship_par_val_rec.param_name(upd_count) := p_ship_par_val_rec.param_name(i);
1533 l_upd_ship_par_val_rec.param_data_type(upd_count) := p_ship_par_val_rec.param_data_type(i);
1534
1535 If p_ship_par_val_rec.param_data_type(i) = 'VARCHAR2' then
1536 l_upd_ship_par_val_rec.param_value_chr(upd_count) := p_ship_par_val_rec.param_value_chr(i);
1537 Elsif p_ship_par_val_rec.param_data_type(i) = 'NUMBER' then
1538 l_upd_ship_par_val_rec.param_value_chr(upd_count) :=
1539 FND_NUMBER.NUMBER_TO_CANONICAL(p_ship_par_val_rec.param_value_num(i));
1540 Elsif p_ship_par_val_rec.param_data_type(i) = 'DATE' then
1541 l_upd_ship_par_val_rec.param_value_chr(upd_count) :=
1542 fnd_date.date_to_canonical(p_ship_par_val_rec.param_value_date(i)) ;
1543 End If;
1544 l_record_exists := FALSE;
1545 Else
1546 ins_count := nvl(l_ins_ship_par_val_rec.class_code.count,0) + 1;
1547 l_ins_ship_par_val_rec.class_code(ins_count) := p_ship_par_val_rec.class_code(i);
1548 l_ins_ship_par_val_rec.param_name(ins_count) := p_ship_par_val_rec.param_name(i);
1549 l_ins_ship_par_val_rec.param_data_type(ins_count) := p_ship_par_val_rec.param_data_type(i);
1550
1551 If p_ship_par_val_rec.param_data_type(i) = 'VARCHAR2' then
1552 l_ins_ship_par_val_rec.param_value_chr(ins_count) := p_ship_par_val_rec.param_value_chr(i);
1553 Elsif p_ship_par_val_rec.param_data_type(i) = 'NUMBER' then
1554 l_ins_ship_par_val_rec.param_value_chr(ins_count) :=
1555 FND_NUMBER.NUMBER_TO_CANONICAL(p_ship_par_val_rec.param_value_num(i));
1556 Elsif p_ship_par_val_rec.param_data_type(i) = 'DATE' then
1557 l_upd_ship_par_val_rec.param_value_chr(ins_count) :=
1558 fnd_date.date_to_canonical(p_ship_par_val_rec.param_value_date(i)) ;
1559 End If;
1560 End If;
1561 End If;
1562 End If;
1563 End Loop;
1564
1565 If l_freight_terms_rec.class_code.count > 0 then
1566 l_freight_terms_rec.organization_id := p_ship_par_val_rec.organization_id;
1567 Begin
1568 Delete from wsh_shipping_parameter_values
1569 Where organization_id = l_freight_terms_rec.organization_id
1570 and class_code = 'FREIGHT_TERMS'
1571 and param_name = 'FREIGHT_TERMS';
1572 If l_debug_on then
1573 wsh_debug_sv.log(l_module_name,'After delete of Freigth Terms from wsh_shipping_parameter_value : ',sqlerrm);
1574 End If;
1575 Exception
1576 When others then
1577 If l_debug_on then
1578 wsh_debug_sv.log(l_module_name,'After delete of Freigth Terms from wsh_shipping_parameter_value : ',sqlerrm);
1579 End If;
1580 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1581 fnd_message.set_token('PACKAGE',l_module_name);
1582 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1583 fnd_message.set_token('ORA_TEXT',SQLERRM);
1584 raise handle_exception;
1585 End;
1586 create_freight_terms(l_freight_terms_rec
1587 ,l_return_status);
1588 If l_return_status <> wsh_util_core.g_ret_sts_success then
1589 If l_debug_on then
1590 wsh_debug_sv.log(l_module_name,'After call to create_freight_terms procedure l_return_status = ',l_return_status);
1591 End If;
1592 raise handle_exception;
1593 End If;
1594 End If;
1595
1596 If l_all_insert then
1597 If l_ins_ship_par_val_rec.class_code.COUNT > 0 then
1598 If l_debug_on then
1599 wsh_debug_sv.log(l_module_name,'In l_all_insert condition of If statement');
1600 End If;
1601 l_ins_ship_par_val_rec.organization_id := p_ship_par_val_rec.organization_id;
1602 Begin
1603 insert_parameter_values (l_ins_ship_par_val_rec);
1604 If l_debug_on then
1605 wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_value table : ',sqlerrm);
1606 End If;
1607 Exception
1608 When others then
1609 If l_debug_on then
1610 wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_value table : ',sqlerrm);
1611 End If;
1612 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1613 fnd_message.set_token('PACKAGE',l_module_name);
1614 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1615 fnd_message.set_token('ORA_TEXT',SQLERRM);
1616 raise handle_exception;
1617 End;
1618 End If;
1619 Else
1620 If l_ins_ship_par_val_rec.class_code.count > 0 then
1621 l_ins_ship_par_val_rec.organization_id := p_ship_par_val_rec.organization_id;
1622 If l_debug_on then
1623 wsh_debug_sv.log(l_module_name,'Going for insert in ELSE part of l_all_insert condition If statement');
1624 End If;
1625 Begin
1626 insert_parameter_values (l_ins_ship_par_val_rec);
1627 If l_debug_on then
1628 wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
1629 End If;
1630 Exception
1631 When others then
1632 If l_debug_on then
1633 wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_value table : ',sqlerrm);
1634 End If;
1635 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1636 fnd_message.set_token('PACKAGE',l_module_name);
1637 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1638 fnd_message.set_token('ORA_TEXT',SQLERRM);
1639 raise handle_exception;
1640 End;
1641 End If;
1642
1643 If l_upd_ship_par_val_rec.class_code.count > 0 then
1644 l_upd_ship_par_val_rec.organization_id := p_ship_par_val_rec.organization_id;
1645
1646 If l_debug_on then
1647 wsh_debug_sv.log(l_module_name,'Going for update in ELSE part of l_all_insert condition If statement');
1648 End If;
1649
1650 Begin
1651 forall i in l_upd_ship_par_val_rec.class_code.first..l_upd_ship_par_val_rec.class_code.last
1652 update wsh_shipping_parameter_values
1653 set param_value = l_upd_ship_par_val_rec.param_value_chr(i)
1654 , creation_date = sysdate
1655 , created_by = l_user_id
1656 , last_update_date = sysdate
1657 , last_updated_by = l_user_id
1658 , last_update_login= l_login_id
1659 where organization_id = l_upd_ship_par_val_rec.organization_id
1660 and param_data_type = l_upd_ship_par_val_rec.param_data_type(i)
1661 and param_name = l_upd_ship_par_val_rec.param_name(i);
1662
1663 If l_debug_on then
1664 wsh_debug_sv.log(l_module_name,'After update of values in wsh_shipping_parameter_values table : ',sqlerrm);
1665 End If;
1666 Exception
1667 When others then
1668 If l_debug_on then
1669 wsh_debug_sv.log(l_module_name,'After update of values in wsh_shipping_parameter_value table : ',sqlerrm);
1670 End If;
1671 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1672 fnd_message.set_token('PACKAGE',l_module_name);
1673 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1674 fnd_message.set_token('ORA_TEXT',SQLERRM);
1675 Raise handle_exception;
1676 End;
1677 End If;
1678 End If;
1679 End If; /* End of Main IF logic */
1680
1681 If l_debug_on then
1682 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
1683 wsh_debug_sv.pop(l_module_name);
1684 End If;
1685
1686 Exception
1687 When handle_exception then
1688 If l_debug_on then
1689 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
1690 wsh_debug_sv.pop(l_module_name);
1691 End If;
1692 x_return_status := wsh_util_core.g_ret_sts_error;
1693
1694 When others then
1695 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1696 fnd_message.set_token('PACKAGE',l_module_name);
1697 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1698 fnd_message.set_token('ORA_TEXT',SQLERRM);
1699 If l_debug_on then
1700 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
1701 wsh_debug_sv.pop(l_module_name);
1702 End If;
1703 x_return_status := wsh_util_core.g_ret_sts_error;
1704
1705 End update_parameter;
1706
1707
1708 Procedure lock_record(
1709 p_organization_id IN NUMBER,
1710 p_last_update_date IN DATE,
1711 x_return_status OUT NOCOPY VARCHAR2) is
1712
1713 Cursor get_last_update_date_csr is
1714 select last_update_date
1715 from wsh_shipping_parameters
1716 where organization_id = p_organization_id;
1717
1718 Cursor l_lock_csr is
1719 select 1
1720 from wsh_shipping_parameters
1721 where organization_id = p_organization_id
1722 for update nowait;
1723
1724 l_id number :=0;
1725 l_debug_on boolean;
1726 l_date date;
1727 l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_RECORD';
1728 handle_exception exception;
1729 others exception;
1730
1731 Begin
1732 x_return_status := wsh_util_core.G_RET_STS_SUCCESS;
1733 l_debug_on := wsh_debug_interface.g_debug;
1734 If l_debug_on is null then
1735 l_debug_on := wsh_debug_sv.is_debug_enabled;
1736 End If;
1737 If l_debug_on then
1738 wsh_debug_sv.push(l_module_name);
1739 End If;
1740
1741 Open get_last_update_date_csr;
1742 Fetch get_last_update_date_csr into l_date;
1743 Close get_last_update_date_csr;
1744
1745 If l_date is not null then
1746 If l_date <> p_last_update_date then
1747 fnd_message.set_name('FND' , 'FND_RECORD_CHANGED_ERROR');
1748 Raise handle_exception;
1749 End If;
1750
1751 End If;
1752
1753 Open l_lock_csr;
1754 Fetch l_lock_csr into l_id ;
1755 Close l_lock_csr;
1756
1757 If l_id is null then
1758 x_return_status := wsh_util_core.G_RET_STS_ERROR;
1759 fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
1760 If l_debug_on then
1761 wsh_debug_sv.log(l_module_name,'Value of L_ID = ',l_id);
1762 wsh_debug_sv.log(l_module_name,'Record dosent exists for the ORG ID ',p_organization_id);
1763 wsh_debug_sv.log(l_module_name,'x_return_status ',x_return_status);
1764 End If;
1765 raise others;
1766 End If;
1767
1768 If l_debug_on then
1769 wsh_debug_sv.log(l_module_name,'x_return_status ',x_return_status);
1770 wsh_debug_sv.pop(l_module_name);
1771 End If;
1772
1773 Exception
1774 When handle_exception then
1775 x_return_status := wsh_util_core.g_ret_sts_error;
1776 If l_debug_on then
1777 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
1778 wsh_debug_sv.log(l_module_name,'x_return_status : ',x_return_status);
1779 wsh_debug_sv.pop(l_module_name);
1780 End If;
1781 When Others then
1782 fnd_message.set_name('FND', 'FND_LOCK_RECORD_ERROR');
1783 x_return_status := wsh_util_core.G_RET_STS_UNEXP_ERROR;
1784 If l_debug_on then
1785 wsh_debug_sv.log(l_module_name,'Unexpected Error Occured During Locking for Org Id ',p_organization_id);
1786 wsh_debug_sv.log(l_module_name,'SQLERRM = ', SQLERRM );
1787 wsh_debug_sv.log(l_module_name,'x_return_status ',x_return_status);
1788 wsh_debug_sv.pop(l_module_name);
1789 End If;
1790 End lock_record;
1791
1792
1793 Procedure get_layout_template (
1794 p_concprg_name IN VARCHAR2,
1795 p_code_field IN VARCHAR2,
1796 x_name_field OUT NOCOPY VARCHAR2,
1797 x_return_status OUT NOCOPY VARCHAR )is
1798
1799 Cursor get_template_name_csr is
1800 select template_name
1801 from xdo_templates_vl
1802 where data_source_code = p_concprg_name
1803 and template_code = p_code_field;
1804
1805 get_template_name_rec get_template_name_csr%ROWTYPE;
1806 l_debug_on boolean;
1807 l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LAYOUT_TEMPLATE';
1808
1809 Begin
1810 x_return_status := wsh_util_core.G_RET_STS_SUCCESS;
1811 l_debug_on := wsh_debug_interface.g_debug;
1812 If l_debug_on is null then
1813 l_debug_on := wsh_debug_sv.is_debug_enabled;
1814 End If;
1815 If l_debug_on then
1816 wsh_debug_sv.push(l_module_name);
1817 wsh_debug_sv.log(l_module_name,'p_concprg_name ',p_concprg_name);
1818 wsh_debug_sv.log(l_module_name,'p_code_field ',p_code_field);
1819 End If;
1820
1821 Open get_template_name_csr;
1822 Fetch get_template_name_csr into get_template_name_rec;
1823 Close get_template_name_csr;
1824 x_name_field := get_template_name_rec.template_name;
1825
1826 If l_debug_on then
1827 wsh_debug_sv.log(l_module_name,'x_name_field ',x_name_field);
1828 wsh_debug_sv.pop(l_module_name);
1829 End If;
1830
1831 Exception
1832 When Others then
1833 x_return_status := wsh_util_core.G_RET_STS_ERROR;
1834 If l_debug_on then
1835 wsh_debug_sv.log(l_module_name,'Unexpected Error Occured while getting tamplate name ',p_concprg_name||' '||p_code_field);
1836 wsh_debug_sv.log(l_module_name,'SQLERRM = ', SQLERRM );
1837 wsh_debug_sv.log(l_module_name,'x_return_status ',x_return_status);
1838 wsh_debug_sv.pop(l_module_name);
1839 End If;
1840
1841 End get_layout_template;
1842
1843
1844 Procedure get(
1845 p_organization_id IN NUMBER,
1846 x_param_info OUT NOCOPY parameter_rec_typ,
1847 x_param_value_info OUT NOCOPY parameter_value_rec_typ,
1848 x_return_status OUT NOCOPY VARCHAR2) IS
1849
1850 type l_varchar2_tb is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1851 l_param_value l_varchar2_tb;
1852
1853 cursor get_paramter_values_csr(p_organization_id in number) is
1854 select class_code
1855 , param_name
1856 , decode(class_code
1857 ,'ROUTING_RULES',nvl(param_value, 'N'),param_value) param_value
1858 , param_data_type
1859 from wsh_shipping_parameter_values
1860 where organization_id = p_organization_id ;
1861
1862 l_param_info parameter_Rec_Typ;
1863 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET';
1864 l_found boolean := FALSE;
1865 l_row_count number;
1866 l_debug_on boolean;
1867 l_msg_data varchar2(2000);
1868 l_return_status varchar2(1);
1869
1870 others exception;
1871
1872 Begin
1873 x_return_status := FND_API.G_RET_STS_SUCCESS;
1874 l_debug_on := wsh_debug_interface.g_debug;
1875 IF l_debug_on IS NULL THEN
1876 l_debug_on := wsh_debug_sv.is_debug_enabled;
1877 END IF;
1878 IF l_debug_on THEN
1879 wsh_debug_sv.push(l_module_name);
1880 wsh_debug_sv.log(l_module_name,'P_ORGANIZATION_ID',p_organization_id);
1881 END IF;
1882
1883 ------------------------------------------------------------------------
1884 -- Get values from wsh_shipping_parameters table . -
1885 ------------------------------------------------------------------------
1886 get(p_organization_id => p_organization_id
1887 ,x_param_info => l_param_info
1888 ,x_return_status => l_return_status);
1889
1890 If l_return_status <> fnd_api.G_RET_STS_SUCCESS then
1891 x_return_status := l_return_status;
1892 return;
1893 Else
1894 x_param_info := l_param_info;
1895 End If;
1896
1897 ------------------------------------------------------------------------
1898 -- Get values from wsh_shipping_parameter_values table .Don't raise --
1899 -- error is records dosen't exists. --
1900 ------------------------------------------------------------------------
1901 l_found := FALSE;
1902 If g_parameter_values.count > 0 then
1903 For i in g_parameter_values.FIRST..g_parameter_values.LAST
1904 Loop
1905 If g_parameter_values(i).organization_id = p_organization_id then
1906 x_param_value_info := g_parameter_values(i);
1907 l_found := TRUE;
1908 exit;
1909 End If;
1910 End Loop;
1911 If l_found and l_debug_on then
1912 print_debug_info(l_module_name
1913 ,'WSH_SHIPPING_PARAMETER_VALUES'
1914 ,x_param_info
1915 ,x_param_value_info
1916 ,l_return_status);
1917 WSH_DEBUG_SV.pop(l_module_name);
1918 End If;
1919 If l_found then
1920 return;
1921 End If;
1922 End If;
1923
1924 Open get_paramter_values_csr(p_organization_id);
1925 Fetch get_paramter_values_csr bulk collect into
1926 x_param_value_info.class_code
1927 ,x_param_value_info.param_name
1928 ,l_param_value
1929 ,x_param_value_info.param_data_type;
1930 Close get_paramter_values_csr;
1931 If l_debug_on then
1932 wsh_debug_sv.log(l_module_name,'After select from wsh_shipping_parameter_values : ',sqlerrm);
1933 wsh_debug_sv.log(l_module_name,'Row_Count = : ',x_param_value_info.param_name.count);
1934 End If;
1935
1936 If x_param_value_info.param_name.COUNT > 0 then
1937 For i in x_param_value_info.param_name.FIRST..x_param_value_info.param_name.LAST
1938 Loop
1939 If x_param_value_info.param_data_type(i) = 'VARCHAR2' then
1940 x_param_value_info.param_value_num(i) := NULL;
1941 x_param_value_info.param_value_chr(i) := l_param_value(i);
1942 x_param_value_info.param_value_date(i):= NULL;
1943 Elsif x_param_value_info.param_data_type(i) = 'DATE' then
1944 x_param_value_info.param_value_num(i) := NULL;
1945 x_param_value_info.param_value_chr(i) := NULL;
1946 x_param_value_info.param_value_date(i):= fnd_date.canonical_to_date(l_param_value(i));
1947 Elsif x_param_value_info.param_data_type(i) = 'NUMBER' then
1948 x_param_value_info.param_value_num(i) := fnd_number.canonical_to_number(l_param_value(i));
1949 x_param_value_info.param_value_chr(i) := NULL;
1950 x_param_value_info.param_value_date(i):= NULL;
1951 End If;
1952 End Loop;
1953 End if;
1954
1955 If l_found and l_debug_on then
1956 print_debug_info(l_module_name
1957 ,'WSH_SHIPPING_PARAMETER_VALUES'
1958 ,x_param_info
1959 ,x_param_value_info
1960 ,l_return_status);
1961 End If;
1962
1963 If x_param_value_info.param_name.COUNT > 0 then
1964 l_row_count := g_parameter_values.count + 1;
1965 g_parameter_values(l_row_count).organization_id := p_organization_id;
1966 g_parameter_values(l_row_count).class_code := x_param_value_info.class_code;
1967 g_parameter_values(l_row_count).param_name := x_param_value_info.param_name;
1968 g_parameter_values(l_row_count).param_data_type := x_param_value_info.param_data_type;
1969 g_parameter_values(l_row_count).param_value_num := x_param_value_info.param_value_num;
1970 g_parameter_values(l_row_count).param_value_chr := x_param_value_info.param_value_chr;
1971 g_parameter_values(l_row_count).param_value_date := x_param_value_info.param_value_date;
1972 End If;
1973
1974 If l_debug_on then
1975 wsh_debug_sv.pop(l_module_name);
1976 End If;
1977
1978 Exception
1979 When Others Then
1980 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1981 fnd_message.set_token('PACKAGE','WSH_SHIPPING_PARAMS_PVT');
1982 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1983 fnd_message.set_token('ORA_TEXT','');
1984 x_return_status := FND_API.G_RET_STS_ERROR;
1985 If l_debug_on Then
1986 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1987 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1988 END IF;
1989 END Get;
1990
1991
1992 Procedure create_freight_terms(
1993 p_freight_terms_rec IN parameter_value_rec_typ,
1994 x_return_status OUT NOCOPY VARCHAR2)is
1995
1996 type l_parameter_value_type is table of wsh_shipping_parameter_values.PARAM_VALUE%TYPE index by binary_integer;
1997
1998 l_freight_terms_value l_parameter_value_type;
1999 l_org_name varchar2(2000) := wsh_util_core.get_org_name(p_freight_terms_rec.organization_id);
2000 l_user_id number:= fnd_global.user_id;
2001 l_login_id number:= fnd_global.login_id;
2002 l_debug_on boolean;
2003 l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_FREIGHT_TERMS';
2004 l_return_status varchar2(10);
2005 l_freight_terms varchar2(240);
2006
2007 Cursor check_duplicate_csr(p_param_name in varchar2
2008 ,p_param_value in varchar2
2009 ,p_class_code in varchar2
2010 ,p_organization_id in number ) is
2011 Select param_value
2012 from wsh_shipping_parameter_values
2013 where param_name = p_param_name
2014 and class_code = p_class_code
2015 and param_value = p_param_value
2016 and organization_id = p_organization_id;
2017
2018 handle_exception exception;
2019
2020 Begin
2021
2022 x_return_status := fnd_api.g_ret_sts_success;
2023
2024 l_debug_on := wsh_debug_interface.g_debug;
2025 If l_debug_on is null then
2026 wsh_debug_sv.push(l_module_name);
2027 l_debug_on := wsh_debug_sv.is_debug_enabled;
2028 End If;
2029 If l_debug_on then
2030 wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2031 print_debug_info(l_module_name
2032 ,'WSH_SHIPPING_PARAMETER_VALUES'
2033 ,null
2034 ,p_freight_terms_rec
2035 ,l_return_status);
2036 End If;
2037
2038 If p_freight_terms_rec.class_code.count > 0 then
2039 For i in p_freight_terms_rec.class_code.FIRST..p_freight_terms_rec.class_code.LAST
2040 Loop
2041 If l_freight_terms_value.COUNT > 0 then
2042 For j in l_freight_terms_value.FIRST..l_freight_terms_value.LAST
2043 Loop
2044 If l_freight_terms_value(j) = p_freight_terms_rec.param_value_chr(i) then
2045 If l_debug_on then
2046 wsh_debug_sv.log(l_module_name, 'Going to raise others exception , Duplicate records exists for freight terms ');
2047 End If;
2048 fnd_message.set_name('WSH','WSH_FREIGHT_ORG_ASSGN_EXISTS');
2049 fnd_message.set_token('FREIGHT_TERMS',p_freight_terms_rec.param_value_chr(i));
2050 fnd_message.set_token('ORG_NAME',l_org_name);
2051 Raise handle_exception;
2052 End If;
2053 End Loop;
2054 l_freight_terms_value(nvl(l_freight_terms_value.count,0)+1) := p_freight_terms_rec.param_value_chr(i);
2055 Else
2056 l_freight_terms_value(nvl(l_freight_terms_value.count,0)+1) := p_freight_terms_rec.param_value_chr(i);
2057 End If;
2058
2059 open check_duplicate_csr(p_freight_terms_rec.param_name(i)
2060 ,p_freight_terms_rec.param_value_chr(i)
2061 ,p_freight_terms_rec.class_code(i)
2062 ,p_freight_terms_rec.organization_id);
2063 fetch check_duplicate_csr into l_freight_terms;
2064 close check_duplicate_csr;
2065
2066 If l_freight_terms is not null then
2067 If l_debug_on then
2068 wsh_debug_sv.log(l_module_name, 'One of the inserted records matches existing record in wsh_shipping_parameter_values table');
2069 wsh_debug_sv.log(l_module_name, 'Duplicate Freight Terms is ',p_freight_terms_rec.param_value_chr(i));
2070 wsh_debug_sv.log(l_module_name, 'For Organization id ',p_freight_terms_rec.organization_id);
2071 End If;
2072 fnd_message.set_name('FND','FND_RECORD_CHANGED_ERROR');
2073 Raise handle_exception;
2074 End If;
2075
2076 End Loop;
2077
2078 If l_debug_on then
2079 wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2080 wsh_debug_sv.log(l_module_name,'Before insert of values into wsh_shipping_parameter_values table : ');
2081 End If;
2082
2083 If p_freight_terms_rec.param_name.count > 0 then
2084 Begin
2085 FORALL i in p_freight_terms_rec.class_code.FIRST..p_freight_terms_rec.class_code.LAST
2086 Insert into wsh_shipping_parameter_values(
2087 organization_id,
2088 class_code,
2089 param_name,
2090 param_value,
2091 param_data_type,
2092 creation_date,
2093 created_by,
2094 last_update_date,
2095 last_updated_by,
2096 last_update_login)
2097 Values (p_freight_terms_rec.organization_id,
2098 p_freight_terms_rec.class_code(i),
2099 p_freight_terms_rec.param_name(i),
2100 p_freight_terms_rec.param_value_chr(i),
2101 p_freight_terms_rec.param_data_type(i),
2102 sysdate,
2103 l_user_id,
2104 sysdate,
2105 l_user_id,
2106 l_login_id);
2107
2108 If l_debug_on then
2109 wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
2110 End If;
2111 Exception
2112 When others then
2113 If l_debug_on then
2114 wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
2115 End If;
2116 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
2117 fnd_message.set_token('PACKAGE',l_module_name);
2118 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
2119 fnd_message.set_token('ORA_TEXT',SQLERRM);
2120 raise handle_exception;
2121 End;
2122 End If;
2123 End If;
2124
2125 If l_debug_on then
2126 wsh_debug_sv.log(l_module_name,'After insert into wsh_shipping_parameter_values SQLERRM : ',SQLERRM);
2127 wsh_debug_sv.pop(l_module_name);
2128 End If;
2129
2130 Exception
2131 When handle_exception then
2132 x_return_status := wsh_util_core.g_ret_sts_error;
2133 If l_debug_on then
2134 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
2135 wsh_debug_sv.log(l_module_name,'x_return_status : ',x_return_status);
2136 wsh_debug_sv.pop(l_module_name);
2137 End If;
2138 When others then
2139 x_return_status := wsh_util_core.g_ret_sts_error;
2140 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
2141 fnd_message.set_token('PACKAGE',l_module_name);
2142 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
2143 fnd_message.set_token('ORA_TEXT',SQLERRM);
2144 If l_debug_on then
2145 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
2146 wsh_debug_sv.log(l_module_name,'x_return_status : ',x_return_status);
2147 wsh_debug_sv.pop(l_module_name);
2148 End If;
2149
2150 End create_freight_terms;
2151
2152
2153
2154 Procedure update_freight_terms(
2155 p_freight_terms_rec IN parameter_value_rec_typ,
2156 row_id IN row_id_tbl,
2157 x_return_status OUT NOCOPY VARCHAR2) is
2158
2159 Cursor check_duplicate_csr(p_row_id in varchar2
2160 ,p_param_name in varchar2
2161 ,p_param_value in varchar2
2162 ,p_class_code in varchar2
2163 ,p_organization_id in number ) is
2164 Select param_value
2165 from wsh_shipping_parameter_values
2166 where rowid <> p_row_id
2167 and param_name = p_param_name
2168 and class_code = p_class_code
2169 and param_value = p_param_value
2170 and organization_id = p_organization_id;
2171
2172 l_user_id number:= fnd_global.user_id;
2173 l_login_id number:= fnd_global.login_id;
2174 l_debug_on boolean;
2175 l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_FREIGHT_TERMS';
2176 l_return_status varchar2(10);
2177 l_org_name varchar2(2000) := wsh_util_core.get_org_name(p_freight_terms_rec.organization_id);
2178 l_freight_terms varchar2(240);
2179 handle_exception exception;
2180
2181 Begin
2182
2183 x_return_status := fnd_api.g_ret_sts_success;
2184
2185 l_debug_on := wsh_debug_interface.g_debug;
2186 If l_debug_on is null then
2187 l_debug_on := wsh_debug_sv.is_debug_enabled;
2188 End If;
2189 If l_debug_on then
2190 wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2191 print_debug_info(l_module_name
2192 ,'WSH_SHIPPING_PARAMETER_VALUES'
2193 ,null
2194 ,p_freight_terms_rec
2195 ,l_return_status);
2196 End If;
2197
2198 If p_freight_terms_rec.class_code.count > 0 then
2199
2200 For i in row_id.first..row_id.last
2201 Loop
2202 open check_duplicate_csr(row_id(i)
2203 ,p_freight_terms_rec.param_name(i)
2204 ,p_freight_terms_rec.param_value_chr(i)
2205 ,p_freight_terms_rec.class_code(i)
2206 ,p_freight_terms_rec.organization_id);
2207 fetch check_duplicate_csr into l_freight_terms;
2208 close check_duplicate_csr;
2209
2210 If l_freight_terms is not null then
2211 If l_debug_on then
2212 wsh_debug_sv.log(l_module_name, 'Going to raise others exception , Duplicate records exists for freight terms ');
2213 End If;
2214 fnd_message.set_name('WSH','WSH_FREIGHT_ORG_ASSGN_EXISTS');
2215 fnd_message.set_token('FREIGHT_TERMS',p_freight_terms_rec.param_value_chr(i));
2216 fnd_message.set_token('ORG_NAME',l_org_name);
2217 Raise handle_exception;
2218 End If;
2219 End Loop;
2220
2221 If l_debug_on then
2222 wsh_debug_sv.log(l_module_name, 'Before update of wsh_shipping_parameter_values table');
2223 End If;
2224
2225 If row_id.count > 0 then
2226 Begin
2227 Forall i in row_id.first..row_id.last
2228 Update wsh_shipping_parameter_values
2229 Set param_value = p_freight_terms_rec.param_value_chr(i),
2230 creation_date = sysdate,
2231 created_by = l_user_id,
2232 last_update_date = sysdate,
2233 last_updated_by = l_user_id,
2234 last_update_login = l_login_id
2235 Where rowid = row_id(i);
2236 Exception
2237 When others then
2238 If l_debug_on then
2239 wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
2240 End If;
2241 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
2242 fnd_message.set_token('PACKAGE',l_module_name);
2243 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
2244 fnd_message.set_token('ORA_TEXT',SQLERRM);
2245 raise handle_exception;
2246 End;
2247 End If;
2248
2249 If sql%notfound then
2250 fnd_message.set_name('FND' , 'FORM_RECORD_CHANGED');
2251 Raise handle_exception;
2252 End If;
2253
2254 If l_debug_on then
2255 wsh_debug_sv.log(l_module_name, 'After update of wsh_shipping_parameter_values SQLERRM',SQLERRM);
2256 End If;
2257 End If;
2258
2259 If l_debug_on then
2260 wsh_debug_sv.pop(l_module_name);
2261 End If;
2262
2263 Exception
2264 When handle_exception then
2265 x_return_status := wsh_util_core.g_ret_sts_error;
2266 If l_debug_on then
2267 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
2268 wsh_debug_sv.log(l_module_name,'x_return_status : ',x_return_status);
2269 wsh_debug_sv.pop(l_module_name);
2270 End If;
2271 When others then
2272 x_return_status := wsh_util_core.g_ret_sts_error;
2273 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
2274 fnd_message.set_token('PACKAGE',l_module_name);
2275 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
2276 fnd_message.set_token('ORA_TEXT',SQLERRM);
2277 If l_debug_on then
2278 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
2279 wsh_debug_sv.log(l_module_name,'x_return_status : ',x_return_status);
2280 wsh_debug_sv.pop(l_module_name);
2281 End If;
2282
2283 End update_freight_terms;
2284
2285
2286
2287 Procedure delete_freight_terms(
2288 p_freight_terms_rec IN parameter_value_rec_typ,
2289 row_id IN row_id_tbl,
2290 x_return_status OUT NOCOPY VARCHAR2) is
2291
2292 l_debug_on boolean;
2293 l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_FREIGHT_TERMS';
2294 l_return_status varchar2(10);
2295 Others Exception;
2296
2297 Begin
2298
2299 x_return_status := fnd_api.g_ret_sts_success;
2300
2301 l_debug_on := wsh_debug_interface.g_debug;
2302 If l_debug_on is null then
2303 l_debug_on := wsh_debug_sv.is_debug_enabled;
2304 End If;
2305 If l_debug_on then
2306 wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2307 wsh_debug_sv.log(l_module_name, 'row_id.count ', row_id.count);
2308 print_debug_info(l_module_name
2309 ,'WSH_SHIPPING_PARAMETER_VALUES'
2310 ,null
2311 ,p_freight_terms_rec
2312 ,l_return_status);
2313 End If;
2314
2315 If row_id.count > 0 then
2316 Forall i in row_id.first..row_id.last
2317 Delete from wsh_shipping_parameter_values
2318 Where rowid = row_id(i);
2319 End If;
2320
2321 If l_debug_on then
2322 wsh_debug_sv.pop(l_module_name);
2323 End If;
2324
2325 Exception
2326 When others then
2327 x_return_status := wsh_util_core.g_ret_sts_error;
2328 fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
2329 fnd_message.set_token('PACKAGE',l_module_name);
2330 fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
2331 fnd_message.set_token('ORA_TEXT',SQLERRM);
2332 If l_debug_on then
2333 wsh_debug_sv.log(l_module_name,'SQLERRM : ',sqlerrm);
2334 wsh_debug_sv.log(l_module_name,'x_return_status : ',x_return_status);
2335 wsh_debug_sv.pop(l_module_name);
2336 End If;
2337
2338 End delete_freight_terms;
2339
2340
2341
2342 Procedure print_debug_info(
2343 p_module_name IN VARCHAR2,
2344 p_print_info IN VARCHAR2,
2345 x_param_info IN parameter_rec_typ,
2346 x_param_value_info IN parameter_value_rec_typ,
2347 x_return_status OUT NOCOPY VARCHAR2) is
2348
2349 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRINT_DEBUG_INFO';
2350
2351 Begin
2352
2353 x_return_status := fnd_api.g_ret_sts_success;
2354 If p_print_info = 'WSH_SHIPPING_PARAMETERS' OR p_print_info = 'ALL' then
2355 wsh_debug_sv.log(p_module_name,'x_param_info.ship_confirm_rule_id',x_param_info.ship_confirm_rule_id);
2356 wsh_debug_sv.log(p_module_name,'x_param_info.autopack_level',x_param_info.autopack_level);
2357 wsh_debug_sv.log(p_module_name,'x_param_info.task_planning_flag',x_param_info.task_planning_flag);
2358 wsh_debug_sv.log(p_module_name,'x_param_info.export_screening_flag',x_param_info.export_screening_flag);
2359 wsh_debug_sv.log(p_module_name,'x_param_info.appending_limit',x_param_info.appending_limit);
2360 wsh_debug_sv.log(p_module_name,'x_param_info.ignore_inbound_trip',x_param_info.ignore_inbound_trip);
2361 wsh_debug_sv.log(p_module_name,'x_param_info.pack_slip_required_flag',x_param_info.pack_slip_required_flag);
2362 wsh_debug_sv.log(p_module_name,'x_param_info.pick_sequence_rule_id',x_param_info.pick_sequence_rule_id);
2363 wsh_debug_sv.log(p_module_name,'x_param_info.pick_grouping_rule_id',x_param_info.pick_grouping_rule_id);
2364 wsh_debug_sv.log(p_module_name,'x_param_info.print_pick_slip_mode',x_param_info.print_pick_slip_mode);
2365 wsh_debug_sv.log(p_module_name,'x_param_info.pick_release_report_set_id',x_param_info.pick_release_report_set_id);
2366 wsh_debug_sv.log(p_module_name,'x_param_info.autocreate_del_orders_flag',x_param_info.autocreate_del_orders_flag);
2367 wsh_debug_sv.log(p_module_name,'x_param_info.default_stage_subinventory',x_param_info.default_stage_subinventory);
2368 wsh_debug_sv.log(p_module_name,'x_param_info.default_stage_locator_id',x_param_info.default_stage_locator_id);
2369 wsh_debug_sv.log(p_module_name,'x_param_info.autodetail_pr_flag',x_param_info.autodetail_pr_flag);
2370 wsh_debug_sv.log(p_module_name,'x_param_info.enforce_packing_flag',x_param_info.enforce_packing_flag);
2371 wsh_debug_sv.log(p_module_name,'x_param_info.group_by_customer_flag',x_param_info.group_by_customer_flag);
2372 wsh_debug_sv.log(p_module_name,'x_param_info.group_by_fob_flag',x_param_info.group_by_fob_flag);
2373 wsh_debug_sv.log(p_module_name,'x_param_info.group_by_freight_terms_flag',x_param_info.group_by_freight_terms_flag);
2374 wsh_debug_sv.log(p_module_name,'x_param_info.group_by_intmed_ship_to_flag',x_param_info.group_by_intmed_ship_to_flag);
2375 wsh_debug_sv.log(p_module_name,'x_param_info.group_by_ship_method_flag',x_param_info.group_by_ship_method_flag);
2376 wsh_debug_sv.log(p_module_name,'x_param_info.group_by_carrier_flag',x_param_info.group_by_carrier_flag);
2377 wsh_debug_sv.log(p_module_name,'x_param_info.attribute_category',x_param_info.attribute_category);
2378 wsh_debug_sv.log(p_module_name,'x_param_info.attribute1',x_param_info.attribute1);
2379 wsh_debug_sv.log(p_module_name,'x_param_info.attribute2',x_param_info.attribute2);
2380 wsh_debug_sv.log(p_module_name,'x_param_info.attribute3',x_param_info.attribute3);
2381 wsh_debug_sv.log(p_module_name,'x_param_info.attribute4',x_param_info.attribute4);
2382 wsh_debug_sv.log(p_module_name,'x_param_info.attribute5',x_param_info.attribute5);
2383 wsh_debug_sv.log(p_module_name,'x_param_info.attribute6',x_param_info.attribute6);
2384 wsh_debug_sv.log(p_module_name,'x_param_info.attribute7',x_param_info.attribute7);
2385 wsh_debug_sv.log(p_module_name,'x_param_info.attribute8',x_param_info.attribute8);
2386 wsh_debug_sv.log(p_module_name,'x_param_info.attribute9',x_param_info.attribute9);
2387 wsh_debug_sv.log(p_module_name,'x_param_info.attribute10',x_param_info.attribute10);
2388 wsh_debug_sv.log(p_module_name,'x_param_info.attribute11',x_param_info.attribute11);
2389 wsh_debug_sv.log(p_module_name,'x_param_info.attribute12',x_param_info.attribute12);
2390 wsh_debug_sv.log(p_module_name,'x_param_info.attribute13',x_param_info.attribute13);
2391 wsh_debug_sv.log(p_module_name,'x_param_info.attribute14',x_param_info.attribute14);
2392 wsh_debug_sv.log(p_module_name,'x_param_info.attribute15',x_param_info.attribute15);
2393 wsh_debug_sv.log(p_module_name,'x_param_info.creation_date',x_param_info.creation_date);
2394 wsh_debug_sv.log(p_module_name,'x_param_info.created_by',x_param_info.created_by);
2395 wsh_debug_sv.log(p_module_name,'x_param_info.last_update_date',x_param_info.last_update_date);
2396 wsh_debug_sv.log(p_module_name,'x_param_info.last_updated_by',x_param_info.last_updated_by);
2397 wsh_debug_sv.log(p_module_name,'x_param_info.last_update_login',x_param_info.last_update_login);
2398 wsh_debug_sv.log(p_module_name,'x_param_info.program_application_id',x_param_info.program_application_id);
2399 wsh_debug_sv.log(p_module_name,'x_param_info.program_id',x_param_info.program_id);
2400 wsh_debug_sv.log(p_module_name,'x_param_info.program_update_date',x_param_info.program_update_date);
2401 wsh_debug_sv.log(p_module_name,'x_param_info.request_id',x_param_info.request_id);
2402 wsh_debug_sv.log(p_module_name,'x_param_info.pick_slip_lines',x_param_info.pick_slip_lines);
2403 wsh_debug_sv.log(p_module_name,'x_param_info.autocreate_deliveries_flag',x_param_info.autocreate_deliveries_flag);
2404 wsh_debug_sv.log(p_module_name,'x_param_info.freight_class_cat_set_id',x_param_info.freight_class_cat_set_id);
2405 wsh_debug_sv.log(p_module_name,'x_param_info.commodity_code_cat_set_id',x_param_info.commodity_code_cat_set_id);
2406 wsh_debug_sv.log(p_module_name,'x_param_info.enforce_ship_set_and_smc',x_param_info.enforce_ship_set_and_smc);
2407 wsh_debug_sv.log(p_module_name,'x_param_info.auto_send_doc_flag',x_param_info.auto_send_doc_flag);
2408 wsh_debug_sv.log(p_module_name,'x_param_info.itm_additional_country_code',x_param_info.itm_additional_country_code);
2409 wsh_debug_sv.log(p_module_name,'x_param_info.auto_select_carrier',x_param_info.auto_select_carrier);
2410 wsh_debug_sv.log(p_module_name,'x_param_info.goods_dispatched_account',x_param_info.goods_dispatched_account);
2411 wsh_debug_sv.log(p_module_name,'x_param_info.location_id',x_param_info.location_id);
2412 wsh_debug_sv.log(p_module_name,'x_param_info.organization_id',x_param_info.organization_id);
2413 wsh_debug_sv.log(p_module_name,'x_param_info.weight_uom_class',x_param_info.weight_uom_class);
2414 wsh_debug_sv.log(p_module_name,'x_param_info.volume_uom_class',x_param_info.volume_uom_class);
2415 wsh_debug_sv.log(p_module_name,'x_param_info.weight_volume_flag',x_param_info.weight_volume_flag);
2416 wsh_debug_sv.log(p_module_name,'x_param_info.inv_controls_container_flag',x_param_info.inv_controls_container_flag);
2417 wsh_debug_sv.log(p_module_name,'x_param_info.percent_fill_basis_flag',x_param_info.percent_fill_basis_flag);
2418 wsh_debug_sv.log(p_module_name,'x_param_info.trip_report_set_id',x_param_info.trip_report_set_id);
2419 wsh_debug_sv.log(p_module_name,'x_param_info.delivery_report_set_id',x_param_info.delivery_report_set_id);
2420 wsh_debug_sv.log(p_module_name,'x_param_info.autocreate_del_orders_pr_flag',x_param_info.autocreate_del_orders_pr_flag);
2421 wsh_debug_sv.log(p_module_name,'x_param_info.fpa_positive_tol_amt',x_param_info.fpa_positive_tol_amt);
2422 wsh_debug_sv.log(p_module_name,'x_param_info.fpa_negative_tol_amt',x_param_info.fpa_negative_tol_amt);
2423 wsh_debug_sv.log(p_module_name,'x_param_info.fpa_positive_tol_percentage',x_param_info.fpa_positive_tol_percentage);
2424 wsh_debug_sv.log(p_module_name,'x_param_info.fpa_negative_tol_percentage',x_param_info.fpa_negative_tol_percentage);
2425 wsh_debug_sv.log(p_module_name,'x_param_info.fpa_default_freight_account',x_param_info.fpa_default_freight_account);
2426 wsh_debug_sv.log(p_module_name,'x_param_info.auto_apply_routing_rules',x_param_info.auto_apply_routing_rules);
2427 wsh_debug_sv.log(p_module_name,'x_param_info.auto_calc_fgt_rate_cr_del',x_param_info.auto_calc_fgt_rate_cr_del);
2428 wsh_debug_sv.log(p_module_name,'x_param_info.auto_calc_fgt_rate_append_del',x_param_info.auto_calc_fgt_rate_append_del);
2429 wsh_debug_sv.log(p_module_name,'x_param_info.auto_calc_fgt_rate_sc_del',x_param_info.auto_calc_fgt_rate_sc_del);
2430 wsh_debug_sv.log(p_module_name,'x_param_info.raise_business_events',x_param_info.raise_business_events);
2431 wsh_debug_sv.log(p_module_name,'x_param_info.enable_tracking_wfs',x_param_info.enable_tracking_wfs);
2432 wsh_debug_sv.log(p_module_name,'x_param_info.enable_sc_wf',x_param_info.enable_sc_wf);
2433 wsh_debug_sv.log(p_module_name,'x_param_info.process_flag',x_param_info.process_flag);
2434 wsh_debug_sv.log(p_module_name,'x_param_info.check_on_hand ',x_param_info.check_on_hand );
2435 --OTM R12
2436 wsh_debug_sv.log(p_module_name,'x_param_info.max_net_weight ',x_param_info.max_net_weight );
2437 wsh_debug_sv.log(p_module_name,'x_param_info.max_gross_weight ',x_param_info.max_gross_weight );
2438 --OTM R12 Org-Specific
2439 wsh_debug_sv.log(p_module_name,'x_param_info.otm_enabled ',x_param_info.otm_enabled );
2440 wsh_debug_sv.log(p_module_name,'x_param_info.dynamic_replenishment_flag ',x_param_info.dynamic_replenishment_flag ); --bug# 6689448 (replenishment project)
2441 wsh_debug_sv.log(p_module_name,'x_param_info.dock_appt_scheduling_flag ',x_param_info.dock_appt_scheduling_flag ); --bug 6700792: OTM Dock Door App Sched Proj
2442 wsh_debug_sv.log(p_module_name,'x_param_info.retain_nonstaged_det_flag ',x_param_info.retain_nonstaged_det_flag ); --Bug 7131800
2443 -- Bug 8446283
2444 wsh_debug_sv.log(p_module_name,'x_param_info.weight_uom_code ',x_param_info.weight_uom_code);
2445 wsh_debug_sv.log(p_module_name,'x_param_info.volume_uom_code ',x_param_info.volume_uom_code);
2446 -- Bug 8446283
2447 End If;
2448
2449 If p_print_info = 'WSH_SHIPPING_PARAMETER_VALUES' OR p_print_info = 'ALL' then
2450 wsh_debug_sv.log(p_module_name,'x_param_value_info.organization_id',x_param_value_info.organization_id);
2451 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_name.COUNT',x_param_value_info.param_name.COUNT);
2452 If x_param_value_info.param_name.COUNT > 0 then
2453 For i in x_param_value_info.param_name.FIRST..x_param_value_info.param_name.LAST
2454 Loop
2455 if x_param_value_info.class_code.count >= i then
2456 wsh_debug_sv.log(p_module_name,'x_param_value_info.class_code('||i||')',x_param_value_info.class_code(i));
2457 Else
2458 wsh_debug_sv.log(p_module_name,'x_param_value_info.class_code.count = ',x_param_value_info.class_code.count);
2459 End If;
2460 If x_param_value_info.param_name.count >= i then
2461 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_name('||i||')',x_param_value_info.param_name(i));
2462 Else
2463 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_name.count = ',x_param_value_info.param_name.count);
2464 End If;
2465 If x_param_value_info.param_data_type.count >= i then
2466 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_data_type('||i||')',x_param_value_info.param_data_type(i));
2467 Else
2468 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_data_type.count = ',x_param_value_info.param_data_type.count);
2469 End If;
2470 If x_param_value_info.param_value_num.count >= i then
2471 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_value_num('||i||')',x_param_value_info.param_value_num(i));
2472 Else
2473 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_value_num.count = ',x_param_value_info.param_value_num.count);
2474 End If;
2475 If x_param_value_info.param_value_chr.count >= i then
2476 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_value_chr('||i||')',x_param_value_info.param_value_chr(i));
2477 Else
2478 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_value_chr.count = ',x_param_value_info.param_value_chr.count);
2479 End If;
2480 If x_param_value_info.param_value_date.count >= i then
2481 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_value_date('||i||')',x_param_value_info.param_value_date(i));
2482 Else
2483 wsh_debug_sv.log(p_module_name,'x_param_value_info.param_value_date.count = ',x_param_value_info.param_value_date.count);
2484 End If;
2485 End Loop;
2486 End If;
2487 End If;
2488
2489 Exception
2490 When others then
2491 wsh_debug_sv.log(l_module_name,'Exception raised from print log info. Wont affect the program flow .SQLERRM : ',sqlerrm);
2492 End print_debug_info;
2493
2494
2495 END WSH_SHIPPING_PARAMS_PVT;