1 PACKAGE BODY MSC_SCATP_PUB AS
2 /* $Header: MSCVATPB.pls 115.8 2003/06/26 07:52:24 rajjain ship $*/
3
4 -- savirine added parameters p_session_id and p_partner_site_id on Sep 10, 2001.
5
6 FUNCTION get_default_ship_method (p_from_location_id IN NUMBER,
7 p_from_instance_id IN NUMBER,
8 p_to_location_id IN NUMBER,
9 p_to_instance_id IN NUMBER,
10 p_session_id IN NUMBER,
11 p_partner_site_id IN NUMBER)
12 return VARCHAR2 IS
13
14 l_ship_method VARCHAR2(204);
15 l_level NUMBER;
16 CURSOR c_ship_method
17 IS
18 SELECT ship_method,
19 ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
20 FROM msc_interorg_ship_methods mism,
21 msc_regions_temp mrt
22 WHERE plan_id = -1
23 AND from_location_id = p_from_location_id
24 AND sr_instance_id = p_from_instance_id
25 AND mism.to_region_id = mrt.region_id
26 AND mrt.session_id = p_session_id
27 AND mrt.partner_site_id = p_partner_site_id
28 AND sr_instance_id2 = p_to_instance_id
29 AND default_flag = 1
30 ORDER BY 2;
31
32 BEGIN
33
34 BEGIN
35 -- bug 2958287
36 SELECT ship_method
37 INTO l_ship_method
38 FROM msc_interorg_ship_methods
39 WHERE plan_id = -1
40 AND from_location_id = p_from_location_id
41 AND sr_instance_id = p_from_instance_id
42 AND to_location_id = p_to_location_id
43 AND sr_instance_id2 = p_to_instance_id
44 and to_region_id is null
45 AND default_flag = 1
46 AND rownum = 1;
47
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 -- savirine added the following select statement on Aug 29, 2001
51 --- BUG 21130222: chnage sql into cursor to select right ship method
52 --- THE sql below selects the wrong ship method
53 /*SELECT ship_method
54 INTO l_ship_method
55 FROM msc_interorg_ship_methods mism,
56 msc_regions_temp mrt
57 WHERE plan_id = -1
58 AND from_location_id = p_from_location_id
59 AND sr_instance_id = p_from_instance_id
60 AND mism.to_region_id = mrt.region_id
61 AND mrt.session_id = p_session_id
62 AND mrt.partner_site_id = p_partner_site_id
63 AND sr_instance_id2 = p_to_instance_id
64 AND default_flag = 1
65 AND rownum = 1; */
66 OPEN c_ship_method;
67 FETCH c_ship_method INTO l_ship_method, l_level;
68 CLOSE c_ship_method;
69 --msc_sch_wb.atp_debug('l_ship_method := ' || l_ship_method);
70 --msc_sch_wb.atp_debug('l_level := ' || l_level);
71
72 END;
73
74 return l_ship_method;
75
76 EXCEPTION
77 WHEN NO_DATA_FOUND THEN
78 return null;
79 END get_default_ship_method;
80
81 FUNCTION get_default_intransit_time (p_from_location_id IN NUMBER,
82 p_from_instance_id IN NUMBER,
83 p_to_location_id IN NUMBER,
84 p_to_instance_id IN NUMBER,
85 p_session_id IN NUMBER,
86 p_partner_site_id IN NUMBER)
87 return NUMBER IS
88 l_intransit_time NUMBER;
89 l_level NUMBER;
90 CURSOR c_lead_time is
91 SELECT intransit_time,
92 ((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
93 FROM msc_interorg_ship_methods,
94 msc_regions_temp mrt
95 WHERE plan_id = -1
96 AND from_location_id = p_from_location_id
97 AND sr_instance_id = p_from_instance_id
98 AND to_region_id = mrt.region_id
99 AND mrt.session_id = p_session_id
100 AND mrt.partner_site_id = p_partner_site_id
101 AND sr_instance_id2 = p_to_instance_id
102 AND default_flag = 1
103 ORDER BY 2;
104
105 BEGIN
106
107 BEGIN
108 -- bug 2958287
109 SELECT intransit_time
110 INTO l_intransit_time
111 FROM msc_interorg_ship_methods
112 WHERE plan_id = -1
113 AND from_location_id = p_from_location_id
114 AND sr_instance_id = p_from_instance_id
115 AND to_location_id = p_to_location_id
116 AND sr_instance_id2 = p_to_instance_id
117 AND to_region_id is null
118 AND default_flag = 1
119 AND rownum = 1;
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 --- BUG 2113022. The SQL below selects wrond lead time
123 -- changed to cursor
124
125 /*
126 SELECT intransit_time
127 INTO l_intransit_time
128 FROM msc_interorg_ship_methods,
129 msc_regions_temp mrt
130 WHERE plan_id = -1
131 AND from_location_id = p_from_location_id
132 AND sr_instance_id = p_from_instance_id
133 AND to_region_id = mrt.region_id
134 AND mrt.session_id = p_session_id
135 AND mrt.partner_site_id = p_partner_site_id
136 AND sr_instance_id2 = p_to_instance_id
137 AND default_flag = 1
138 AND rownum = 1; */
139 OPEN c_lead_time;
140 FETCH c_lead_time INTO l_intransit_time, l_level;
141 CLOSE c_lead_time;
142
143 END;
144 return l_intransit_time;
145
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 return null;
149 END get_default_intransit_time;
150
151 FUNCTION get_ship_method (p_from_org_id IN NUMBER,
152 p_from_org_instance_id IN NUMBER,
153 p_to_org_id IN NUMBER,
154 p_to_org_instance_id IN NUMBER,
155 p_source_ship_method IN VARCHAR2,
156 p_receipt_org_id IN NUMBER )
157 return VARCHAR2 IS
158
159 l_ship_method VARCHAR2(30);
160
161 BEGIN
162
163
164 IF (p_receipt_org_id is NOT NULL and
165 p_source_ship_method is NOT NULL) THEN
166
167 return p_source_ship_method;
168
169 END IF;
170
171 -- bug 2958287
172 select ship_method
173 into l_ship_method
174 from msc_interorg_ship_methods
175 where plan_id = -1
176 and from_organization_id = p_from_org_id
177 and sr_instance_id = p_from_org_instance_id
178 and to_organization_id = p_to_org_id
179 and sr_instance_id2 = p_to_org_instance_id
180 and to_region_id is null
181 and default_flag = 1
182 and rownum = 1;
183
184 return l_ship_method;
185
186 EXCEPTION WHEN NO_DATA_FOUND THEN
187 return null;
188
189 END get_ship_method;
190
191 FUNCTION get_intransit_time (p_from_org_id IN NUMBER,
192 p_from_org_instance_id IN NUMBER,
193 p_to_org_id IN NUMBER,
194 p_to_org_instance_id IN NUMBER,
195 p_source_ship_method IN VARCHAR2,
196 p_receipt_org_id IN NUMBER)
197 return NUMBER IS
198 l_intransit_time NUMBER;
199
200 BEGIN
201
202 IF (p_receipt_org_id is NOT NULL and
203 p_source_ship_method is NOT NULL) THEN
204
205 BEGIN
206 -- bug 2958287
207 select intransit_time
208 into l_intransit_time
209 from msc_interorg_ship_methods
210 where plan_id = -1
211 and from_organization_id = p_from_org_id
212 and sr_instance_id = p_from_org_instance_id
213 and to_organization_id = p_to_org_id
214 and sr_instance_id2 = p_to_org_instance_id
215 and ship_method = p_source_ship_method
216 and to_region_id is null
217 and rownum = 1;
218
219 return l_intransit_time;
220
221 EXCEPTION WHEN NO_DATA_FOUND THEN
222 return null;
223
224 END;
225
226 END IF;
227
228
229 BEGIN
230 -- bug 2958287
231 select intransit_time
232 into l_intransit_time
233 from msc_interorg_ship_methods
234 where plan_id = -1
235 and from_organization_id = p_from_org_id
236 and sr_instance_id = p_from_org_instance_id
237 and to_organization_id = p_to_org_id
238 and sr_instance_id2 = p_to_org_instance_id
239 and to_region_id is null
240 and default_flag = 1
241 and rownum = 1;
242
243 return l_intransit_time;
244
245 EXCEPTION
246 WHEN NO_DATA_FOUND THEN
247 return null;
248 END;
249
250 END get_intransit_time;
251
252 FUNCTION get_weight_cost (p_from_org_id IN NUMBER,
253 p_from_org_instance_id IN NUMBER,
254 p_to_org_id IN NUMBER,
255 p_to_org_instance_id IN NUMBER,
256 p_source_ship_method IN VARCHAR2,
257 p_receipt_org_id IN NUMBER)
258 return NUMBER IS
259 l_weight_cost NUMBER;
260
261 BEGIN
262
263 IF (p_receipt_org_id is NOT NULL and
264 p_source_ship_method is NOT NULL) THEN
265
266 BEGIN
267 -- bug 2958287
268 select cost_per_weight_unit
269 into l_weight_cost
270 from msc_interorg_ship_methods
271 where plan_id = -1
272 and from_organization_id = p_from_org_id
273 and sr_instance_id = p_from_org_instance_id
274 and to_organization_id = p_to_org_id
275 and sr_instance_id2 = p_to_org_instance_id
276 and ship_method = p_source_ship_method
277 and to_region_id is null
278 and rownum = 1;
279
280 return l_weight_cost;
281
282 EXCEPTION WHEN NO_DATA_FOUND THEN
283 return null;
284 END;
285
286 END IF;
287
288 BEGIN
289 -- bug 2958287
290 select cost_per_weight_unit
291 into l_weight_cost
292 from msc_interorg_ship_methods
293 where plan_id = -1
294 and from_organization_id = p_from_org_id
295 and sr_instance_id = p_from_org_instance_id
296 and to_organization_id = p_to_org_id
297 and sr_instance_id2 = p_to_org_instance_id
298 and default_flag = 1
299 and to_region_id is null
300 and rownum = 1;
301
302 return l_weight_cost;
303
304 EXCEPTION
305 WHEN NO_DATA_FOUND THEN
306 return null;
307 END;
308
309 END get_weight_cost;
310
311 FUNCTION get_transport_cost (p_from_org_id IN NUMBER,
312 p_from_org_instance_id IN NUMBER,
313 p_to_org_id IN NUMBER,
314 p_to_org_instance_id IN NUMBER,
315 p_source_ship_method IN VARCHAR2,
316 p_receipt_org_id IN NUMBER)
317 return NUMBER IS
318 l_transport_cost NUMBER;
319
320 BEGIN
321 IF (p_receipt_org_id is NOT NULL and
322 p_source_ship_method is NOT NULL) THEN
323
324 BEGIN
325 -- bug 2958287
326 select transport_cap_over_util_cost
327 into l_transport_cost
328 from msc_interorg_ship_methods
329 where plan_id = -1
330 and from_organization_id = p_from_org_id
331 and sr_instance_id = p_from_org_instance_id
332 and to_organization_id = p_to_org_id
333 and sr_instance_id2 = p_to_org_instance_id
334 and ship_method = p_source_ship_method
335 and to_region_id is null
336 and rownum = 1;
337
338 return l_transport_cost;
339
340 EXCEPTION WHEN NO_DATA_FOUND THEN
341 return null;
342 END;
343
344 END IF;
345
346 BEGIN
347 -- bug 2958287
348 select transport_cap_over_util_cost
349 into l_transport_cost
350 from msc_interorg_ship_methods
351 where plan_id = -1
352 and from_organization_id = p_from_org_id
353 and sr_instance_id = p_from_org_instance_id
354 and to_organization_id = p_to_org_id
355 and sr_instance_id2 = p_to_org_instance_id
356 and default_flag = 1
357 and to_region_id is null
358 and rownum = 1;
359
360 return l_transport_cost;
361
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 return null;
365 END;
366
367 END get_transport_cost;
368
369 END MSC_SCATP_PUB;
[Home] [Help]