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