[Home] [Help]
PACKAGE BODY: APPS.WSH_OTM_REF_DATA_GEN_PKG
Source
1 PACKAGE BODY WSH_OTM_REF_DATA_GEN_PKG as
2 /* $Header: WSHTMRGB.pls 120.5.12010000.3 2008/08/11 10:21:19 mvudugul ship $ */
3
4 --
5
6 TYPE IN_REC_TYPE IS RECORD(
7 dummy1 NUMBER
8 );
9 --
10
11 --
12 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_OTM_REF_DATA_GEN_PKG';
13 --
14 PROCEDURE INSERT_ROW_IN_LOC_GTMP
15 (
16 p_location_id IN NUMBER,
17 p_corporation_id IN NUMBER,
18 p_location_type IN VARCHAR2,
19 x_return_status OUT NOCOPY VARCHAR2
20 )
21 IS
22 --{
23 --}
24 --
25 l_debug_on BOOLEAN;
26 --
27 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW_IN_LOC_GTMP';
28 --
29 BEGIN
30 --{
31 --
32 --
33 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
34 --
35 IF l_debug_on IS NULL
36 THEN
37 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
38 END IF;
39 --
40 --
41 -- Debug Statements
42 --
43 IF l_debug_on THEN
44 WSH_DEBUG_SV.push(l_module_name);
45 --
46 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
47 WSH_DEBUG_SV.log(l_module_name,'P_CORPORATION_ID',P_CORPORATION_ID);
48 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
49 END IF;
50 --
51 x_return_status := wsh_util_core.g_ret_sts_success;
52 --
53 insert into WSH_OTM_LOCATIONS_GTMP
54 (location_id, corporation_id, location_type)
55 values( p_location_id,p_corporation_id,p_location_type);
56 --
57 --}
58 --
59 -- Debug Statements
60 --
61 IF l_debug_on THEN
62 WSH_DEBUG_SV.pop(l_module_name);
63 END IF;
64 --
65 EXCEPTION
66 --{
67 WHEN OTHERS THEN
68 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
69 --}
70 --
71 -- Debug Statements
72 --
73 IF l_debug_on THEN
74 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
75 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
76 END IF;
77 --
78 END INSERT_ROW_IN_LOC_GTMP;
79
80 function GET_STOP_CORP_ID
81 (
82 p_stop_id IN NUMBER,
83 p_loc_type IN VARCHAR2
84 ) RETURN NUMBER
85 IS
86 cursor l_stop_dd_org_csr(p_stop_id IN NUMBER) is
87 SELECT HOU.ORGANIZATION_ID
88 FROM HR_ALL_ORGANIZATION_UNITS HOU,
89 WSH_LOCATIONS WL,
90 WSH_NEW_DELIVERIES WND,
91 WSH_DELIVERY_LEGS WDL,
92 WSH_TRIP_STOPS WTS
93 WHERE WTS.STOP_ID = p_stop_id
94 AND HOU.LOCATION_ID = WL.SOURCE_LOCATION_ID
95 AND WL.LOCATION_SOURCE_CODE = 'HR'
96 AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID)
97 AND (WTS.STOP_ID = WDL.PICK_UP_STOP_ID
98 OR WTS.STOP_ID = WDL.DROP_OFF_STOP_ID
99 )
100 AND WDL.DELIVERY_ID = WND.DELIVERY_ID
101 AND HOU.ORGANIZATION_ID = WND.ORGANIZATION_ID;
102
103 --bug 6770323: modified cursor to join with hr_locations_all table
104 cursor l_stop_org_csr(p_stop_id IN NUMBER) is
105 SELECT HL.INVENTORY_ORGANIZATION_ID
106 FROM HR_LOCATIONS_ALL HL,
107 WSH_LOCATIONS WL,
108 WSH_TRIP_STOPS WTS
109 WHERE WTS.STOP_ID = p_stop_id
110 AND HL.LOCATION_ID = WL.SOURCE_LOCATION_ID
111 AND WL.LOCATION_SOURCE_CODE = 'HR'
112 AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID);
113
114 cursor l_stop_dd_cust_csr(p_stop_id IN NUMBER) is
115 SELECT HCA.CUST_ACCOUNT_ID
116 FROM HZ_CUST_ACCOUNTS HCA,
117 HZ_PARTIES HP,
118 HZ_PARTY_SITES HPS,
119 WSH_LOCATIONS WL,
120 WSH_NEW_DELIVERIES WND,
121 WSH_DELIVERY_LEGS WDL,
122 WSH_TRIP_STOPS WTS,
123 WSH_DELIVERY_ASSIGNMENTS WDA,
124 WSH_DELIVERY_DETAILS WDD
125 WHERE WTS.STOP_ID = p_stop_id
126 AND HPS.LOCATION_ID = WL.SOURCE_LOCATION_ID
127 AND WL.LOCATION_SOURCE_CODE = 'HZ'
128 AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID)
129 AND HPS.PARTY_ID = HP.PARTY_ID
130 AND HP.PARTY_ID = HCA.PARTY_ID
131 AND (WTS.STOP_ID = WDL.PICK_UP_STOP_ID
132 OR WTS.STOP_ID = WDL.DROP_OFF_STOP_ID
133 )
134 AND WDL.DELIVERY_ID = WND.DELIVERY_ID
135 AND WND.DELIVERY_ID = WDA.DELIVERY_ID
136 AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
137 AND HCA.CUST_ACCOUNT_ID = WDD.CUSTOMER_ID;
138
139 cursor l_stop_cust_csr(p_stop_id IN NUMBER) is
140 SELECT HCA.CUST_ACCOUNT_ID
141 FROM HZ_CUST_ACCOUNTS HCA,
142 HZ_PARTIES HP,
143 HZ_PARTY_SITES HPS,
144 WSH_LOCATIONS WL,
145 WSH_TRIP_STOPS WTS
146 WHERE WTS.STOP_ID = p_stop_id
147 AND HPS.LOCATION_ID = WL.SOURCE_LOCATION_ID
148 AND WL.LOCATION_SOURCE_CODE = 'HZ'
149 AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID)
150 AND HPS.PARTY_ID = HP.PARTY_ID
151 AND HP.PARTY_ID = HCA.PARTY_ID;
152
153 l_corporation_id NUMBER;
154 --
155 l_debug_on BOOLEAN;
156 --
157 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_STOP_CORP_ID';
158 --
159 BEGIN
160 --{
161 --
162 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
163 --
164 IF l_debug_on IS NULL
165 THEN
166 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
167 END IF;
168 --
169 --
170 -- Debug Statements
171 --
172 IF l_debug_on THEN
173 WSH_DEBUG_SV.push(l_module_name);
174 --
175 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
176 WSH_DEBUG_SV.log(l_module_name,'P_LOC_TYPE',P_LOC_TYPE);
177 END IF;
178 --
179 IF (p_loc_type = 'HR') THEN
180 open l_stop_dd_org_csr(p_stop_id);
181 fetch l_stop_dd_org_csr into l_corporation_id;
182 close l_stop_dd_org_csr;
183
184 IF l_debug_on THEN
185 WSH_DEBUG_SV.log(l_module_name,'l_corporation_id after l_pickup_stop_org_csr',l_corporation_id);
186 END IF;
187
188 IF (l_corporation_id is null) THEN
189 open l_stop_org_csr(p_stop_id);
190 fetch l_stop_org_csr into l_corporation_id;
191 close l_stop_org_csr;
192
193 IF l_debug_on THEN
194 WSH_DEBUG_SV.log(l_module_name,'l_corporation_id after l_dropoff_stop_org_csr',l_corporation_id);
195 END IF;
196 END IF;
197 ELSIF (p_loc_type = 'HZ') THEN
198 open l_stop_dd_cust_csr(p_stop_id);
199 fetch l_stop_dd_cust_csr into l_corporation_id;
200 close l_stop_dd_cust_csr;
201
202 IF l_debug_on THEN
203 WSH_DEBUG_SV.log(l_module_name,'l_corporation_id after l_pickup_stop_cust_csr',l_corporation_id);
204 END IF;
205
206
207 IF (l_corporation_id is null) THEN
208 open l_stop_cust_csr(p_stop_id);
209 fetch l_stop_cust_csr into l_corporation_id;
210 close l_stop_cust_csr;
211
212 IF l_debug_on THEN
213 WSH_DEBUG_SV.log(l_module_name,'l_corporation_id after l_dropoff_stop_cust_csr',l_corporation_id);
214 END IF;
215 END IF;
216
217 ELSE
218 l_corporation_id := null;
219 END IF;
220 -- Debug Statements
221 --
222 IF l_debug_on THEN
223 WSH_DEBUG_SV.pop(l_module_name);
224 --
225 END IF;
226 return l_corporation_id;
227 --
228 --}
229 END GET_STOP_CORP_ID;
230
231 PROCEDURE EXTRACT_DLVY_INFO
232 (
233 p_entity_in_rec IN WSH_OTM_ENTITY_REC_TYPE,
234 p_transmission_id IN NUMBER,
235 x_return_status OUT NOCOPY VARCHAR2
236 )
237 IS
238 --{
239 -- local variables
240 l_return_status VARCHAR2(1);
241 l_num_errors NUMBER := 0;
242 l_num_warnings NUMBER := 0;
243 i NUMBER;
244
245 l_customer_id NUMBER;
246 l_st_loc_id NUMBER;
247 l_contact_id NUMBER;
248 l_site_use_id NUMBER;
249 l_prev_contact_id NUMBER;
250 l_cnt NUMBER;
251 l_contact_exists VARCHAR2(1);
252
253 l_internal_loc_exists VARCHAR2(1);
254 l_internal_org_id NUMBER;
255 l_internal_loc_id NUMBER;
256
257 -- cursors
258 cursor l_get_del_loc_info_csr(p_delivery_id IN NUMBER) is
259 SELECT WND.ULTIMATE_DROPOFF_LOCATION_ID SHIP_TO_LOCATION_ID,
260 WND.CUSTOMER_ID,
261 WND.INITIAL_PICKUP_LOCATION_ID SHIP_FROM_LOCATION_ID,
262 WND.ORGANIZATION_ID,
263 WLT1.LOCATION_ID WLT1_ST_LOC_ID,
264 WLT1.CORPORATION_ID WLT1_CUST_ID,
265 WLT2.LOCATION_ID WLT2_SF_LOC_ID,
266 WLT2.CORPORATION_ID WLT2_ORG_ID
267 FROM WSH_NEW_DELIVERIES WND,
268 WSH_OTM_LOCATIONS_GTMP WLT1,
269 WSH_OTM_LOCATIONS_GTMP WLT2
270 WHERE WND.DELIVERY_ID = p_delivery_id
271 AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WLT1.LOCATION_ID (+)
272 AND WND.CUSTOMER_ID = WLT1.CORPORATION_ID (+)
273 AND WND.INITIAL_PICKUP_LOCATION_ID = WLT2.LOCATION_ID (+)
274 AND WND.ORGANIZATION_ID = WLT2.CORPORATION_ID (+)
275 AND WLT1.LOCATION_TYPE (+) = 'CUST_LOC'
276 AND WLT2.LOCATION_TYPE (+) = 'ORG_LOC';
277
278 cursor l_get_dd_loc_info_csr(p_delivery_id IN NUMBER) is
279 SELECT DISTINCT WDD.SHIP_TO_LOCATION_ID,
280 WDD.CUSTOMER_ID
281 FROM WSH_DELIVERY_DETAILS WDD,
282 WSH_DELIVERY_ASSIGNMENTS WDA
283 WHERE WDA.DELIVERY_ID = p_delivery_id
284 AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
285 AND WDD.container_flag = 'N'
286 AND NOT EXISTS (
287 SELECT 'X'
288 FROM WSH_OTM_LOCATIONS_GTMP
289 WHERE LOCATION_TYPE = 'CUST_LOC'
290 AND LOCATION_ID = WDD.SHIP_TO_LOCATION_ID
291 AND CORPORATION_ID = WDD.CUSTOMER_ID
292 );
293
294
295 cursor l_ship_site_use_csr (p_delivery_id IN NUMBER) is
296 SELECT WDD.SHIP_TO_SITE_USE_ID, COUNT(*) CNT
297 FROM WSH_DELIVERY_ASSIGNMENTS WDA,
298 WSH_DELIVERY_DETAILS WDD
299 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
300 AND WDA.DELIVERY_ID = P_DELIVERY_ID
301 AND WDD.CONTAINER_FLAG = 'N'
302 GROUP BY WDD.SHIP_TO_SITE_USE_ID
303 ORDER BY CNT desc;
304
305 -- OTM R12, glog project
306 -- Remove the join condition between HCAR.CUST_ACCOUNT_ID
307 -- and WDD.CUSTOMER_ID
308 cursor l_del_contact_csr(p_delivery_id IN NUMBER) is
309 SELECT DISTINCT HCAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID,
310 WDD.CUSTOMER_ID CUSTOMER_ID
311 FROM WSH_DELIVERY_DETAILS WDD,
312 WSH_DELIVERY_ASSIGNMENTS WDA,
313 HZ_PARTY_SITES HPS,
314 WSH_LOCATIONS WSL,
315 HZ_CUST_ACCT_SITES_ALL HCAS,
316 HZ_CUST_ACCOUNT_ROLES HCAR
317 WHERE WDA.delivery_id = p_delivery_id
318 AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
319 AND WDD.SHIP_TO_LOCATION_ID = WSL.WSH_LOCATION_ID
320 AND WSL.SOURCE_LOCATION_ID = HPS.LOCATION_ID
321 AND HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
322 AND HCAS.CUST_ACCT_SITE_ID = HCAR.CUST_ACCT_SITE_ID
323 AND HCAR.STATUS = 'A';
324
325 cursor l_check_contacts_exists_csr (p_contact_id IN NUMBER,
326 p_location_id IN NUMBER,
327 p_corporation_id IN NUMBER
328 ) is
329 SELECT 'X'
330 FROM WSH_OTM_LOC_CONTACTS_GTMP
331 WHERE CONTACT_ID = p_contact_id
332 AND LOCATION_ID = p_location_id
333 AND CORPORATION_ID = p_corporation_id
334 AND LOCATION_TYPE = 'CUST_LOC';
335
336 --bug 6770323: modified cursor to join with hr_locations_all table
337 cursor l_loc_to_org_csr (p_loc_id NUMBER) IS
338 SELECT inventory_organization_id
339 FROM hr_locations_all
340 WHERE location_id = p_loc_id;
341
342 cursor l_check_loc_exists_csr (p_location_id IN NUMBER,
343 p_corporation_id IN NUMBER
344 ) is
345 SELECT 'X'
346 FROM WSH_OTM_LOCATIONS_GTMP
347 WHERE LOCATION_ID = p_location_id
348 AND (p_corporation_id is NULL OR CORPORATION_ID = p_corporation_id)
349 AND LOCATION_TYPE = 'ORG_LOC';
350
351
352
353 --}
354 --
355 l_debug_on BOOLEAN;
356 --
357 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'EXTRACT_DLVY_INFO';
358 --
359 BEGIN
360 --{
361 --
362 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
363 --
364 IF l_debug_on IS NULL
365 THEN
366 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
367 END IF;
368 --
369 --
370 -- Debug Statements
371 --
372 IF l_debug_on THEN
373 WSH_DEBUG_SV.push(l_module_name);
374 --
375 WSH_DEBUG_SV.log(l_module_name,'P_TRANSMISSION_ID',P_TRANSMISSION_ID);
376 END IF;
377 --
378 x_return_status := wsh_util_core.g_ret_sts_success;
379 --
380 IF ( p_entity_in_rec.entity_id_tbl.count > 0
381 AND p_entity_in_rec.ENTITY_TYPE = 'DELIVERY'
382 )
383 THEN
384 --{
385 FOR i in p_entity_in_rec.entity_id_tbl.first..p_entity_in_rec.entity_id_tbl.last LOOP
386 --{
387 IF l_debug_on THEN
388 WSH_DEBUG_SV.log(l_module_name,'Delivery_Id('||i||')',p_entity_in_rec.entity_id_tbl(i));
389 END IF;
390 l_internal_loc_id := NULL;
391 l_internal_org_id := NULL;
392 FOR l_del_loc_info_rec in l_get_del_loc_info_csr(p_entity_in_rec.entity_id_tbl(i)) LOOP
393 --{
394 WSH_LOCATIONS_PKG.Convert_internal_cust_location(
395 p_internal_cust_location_id => l_del_loc_info_rec.SHIP_TO_LOCATION_ID,
396 x_internal_org_location_id => l_internal_loc_id,
397 x_return_status => l_return_status);
398 --
399 -- Debug Statements
400 --
401 IF l_debug_on THEN
402 WSH_DEBUG_SV.log(l_module_name,'l_return_status after calling Convert_internal_cust_location',l_return_status);
403 WSH_DEBUG_SV.log(l_module_name,'l_internal_loc_id',l_internal_loc_id);
404 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
405 END IF;
406 --
407 wsh_util_core.api_post_call(
408 p_return_status => l_return_status,
409 x_num_warnings => l_num_warnings,
410 x_num_errors => l_num_errors);
411
412
413 --
414 -- Ship To
415 l_st_loc_id := l_del_loc_info_rec.SHIP_TO_LOCATION_ID;
416 l_customer_id := l_del_loc_info_rec.CUSTOMER_ID;
417 IF (l_del_loc_info_rec.CUSTOMER_ID IS NOT NULL
418 and l_del_loc_info_rec.WLT1_ST_LOC_ID IS NULL
419 and l_internal_loc_id IS NULL )
420 THEN
421 --{
422
423 INSERT_ROW_IN_LOC_GTMP
424 (
425 p_location_id => l_del_loc_info_rec.SHIP_TO_LOCATION_ID,
426 p_corporation_id => l_del_loc_info_rec.CUSTOMER_ID,
427 p_location_type => 'CUST_LOC',
428 x_return_status => l_return_status
429 );
430
431 --
432 -- Debug Statements
433 --
434 IF l_debug_on THEN
435 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
436 END IF;
437 --
438 wsh_util_core.api_post_call(
439 p_return_status => l_return_status,
440 x_num_warnings => l_num_warnings,
441 x_num_errors => l_num_errors);
442
443 --}
444 ELSIF (l_internal_loc_id is not null) THEN
445 --{
446 open l_loc_to_org_csr(l_internal_loc_id);
447 fetch l_loc_to_org_csr into l_internal_org_id;
448 close l_loc_to_org_csr;
449
450 open l_check_loc_exists_csr(l_internal_loc_id,l_internal_org_id);
451 fetch l_check_loc_exists_csr into l_internal_loc_exists;
452 close l_check_loc_exists_csr;
453
454 IF (l_internal_loc_exists IS NULL) THEN
455 --{
456 INSERT_ROW_IN_LOC_GTMP
457 (
458 p_location_id => l_internal_loc_id,
459 p_corporation_id => l_internal_org_id,
460 p_location_type => 'ORG_LOC',
461 x_return_status => l_return_status
462 );
463 --
464 -- Debug Statements
465 --
466 IF l_debug_on THEN
467 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling INSERT_ROW_IN_LOC_GTMP is', l_return_status);
468 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
469 END IF;
470 --
471 wsh_util_core.api_post_call(
472 p_return_status => l_return_status,
473 x_num_warnings => l_num_warnings,
474 x_num_errors => l_num_errors);
475 --}
476 END IF;
477
478 l_internal_loc_exists := NULL;
479
480 --}
481 END IF;
482 --
483 -- Ship From
484 IF (l_del_loc_info_rec.ORGANIZATION_ID IS NOT NULL
485 AND l_del_loc_info_rec.WLT2_SF_LOC_ID IS NULL
486 AND nvl(l_internal_loc_id,-99999) <> l_del_loc_info_rec.SHIP_FROM_LOCATION_ID
487 )
488 THEN
489 --{
490
491 INSERT_ROW_IN_LOC_GTMP
492 (
493 p_location_id => l_del_loc_info_rec.SHIP_FROM_LOCATION_ID,
494 p_corporation_id => l_del_loc_info_rec.ORGANIZATION_ID,
495 p_location_type => 'ORG_LOC',
496 x_return_status => l_return_status
497 );
498
499 --
500 -- Debug Statements
501 --
502 IF l_debug_on THEN
503 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling INSERT_ROW_IN_LOC_GTMP is', l_return_status);
504 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
505 END IF;
506 --
507 wsh_util_core.api_post_call(
508 p_return_status => l_return_status,
509 x_num_warnings => l_num_warnings,
510 x_num_errors => l_num_errors);
511 --}
512 END IF;
513 --}
514 END LOOP;
515 --
516 --
517 IF (l_customer_id is NULL and l_internal_loc_id IS NULL) THEN
518 --{
519 FOR dd_loc_info_rec in l_get_dd_loc_info_csr(p_entity_in_rec.entity_id_tbl(i)) LOOP
520 --{
521 INSERT_ROW_IN_LOC_GTMP
522 (
523 p_location_id => dd_loc_info_rec.SHIP_TO_LOCATION_ID,
524 p_corporation_id => dd_loc_info_rec.CUSTOMER_ID,
525 p_location_type => 'CUST_LOC',
526 x_return_status => l_return_status
527 );
528
529 --
530 -- Debug Statements
531 --
532 IF l_debug_on THEN
533 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling INSERT_ROW_IN_LOC_GTMP is', l_return_status);
534 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
535 END IF;
536 --
537 wsh_util_core.api_post_call(
538 p_return_status => l_return_status,
539 x_num_warnings => l_num_warnings,
540 x_num_errors => l_num_errors);
541 --}
542 END LOOP;
543 --}
544 END IF;
545
546 -- Nullifying it so that it does not hold any values,
547 -- if the cursor below does not fetch anything
548 l_customer_id := NULL;
549 l_prev_contact_id := -999;
550 l_site_use_id := NULL;
551 IF (l_internal_loc_id IS NULL) THEN
552 --{
553 -- bug 5124820
554 -- Now, we will be sending all the contacts for a given
555 -- Customer Site.
556 for del_contact_rec in l_del_contact_csr(p_entity_in_rec.entity_id_tbl(i)) loop
557 --{
558 IF l_debug_on THEN
559 WSH_DEBUG_SV.log(l_module_name,'ContactId for delivery is', del_contact_rec.contact_id);
560 WSH_DEBUG_SV.log(l_module_name,'Prev Contact Id for delivery is', l_prev_contact_id);
561 WSH_DEBUG_SV.log(l_module_name,'location_id',l_st_loc_id);
562 WSH_DEBUG_SV.log(l_module_name,'customer_id',del_contact_rec.customer_id);
563 END IF;
564
565 l_contact_exists := null;
566
567 IF (l_prev_contact_id <> del_contact_rec.contact_id) THEN
568 --{
569 open l_check_contacts_exists_csr(del_contact_rec.contact_id, l_st_loc_id,del_contact_rec.customer_id);
570 fetch l_check_contacts_exists_csr into l_contact_exists;
571 close l_check_contacts_exists_csr;
572 IF l_debug_on THEN
573 WSH_DEBUG_SV.log(l_module_name,'contact exists',l_contact_exists);
574 END IF;
575 IF (l_contact_exists IS NULL) THEN
576 --{
577 insert into WSH_OTM_LOC_CONTACTS_GTMP
578 (contact_id, location_id, corporation_id, location_type)
579 values(del_contact_rec.contact_id, l_st_loc_id,del_contact_rec.customer_id, 'CUST_LOC');
580 --}
581 END IF;
582 --}
583 END IF;
584 l_prev_contact_id := del_contact_rec.contact_id;
585 --}
586 END LOOP;
587 --}
588 END IF;
589 --}
590 END LOOP;
591 --}
592 END IF;
593 --
594 IF (l_num_warnings > 0 AND x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
595 --
596 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
597 --
598 ELSE
599 --
600 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
601 --
602 END IF;
603 --}
604 --
605 -- Debug Statements
606 --
607 IF l_debug_on THEN
608 WSH_DEBUG_SV.pop(l_module_name);
609 END IF;
610 --
611 END EXTRACT_DLVY_INFO;
612
613 PROCEDURE EXTRACT_TRIP_INFO
614 (
615 p_entity_in_rec IN WSH_OTM_ENTITY_REC_TYPE,
616 p_transmission_id IN NUMBER,
617 x_return_status OUT NOCOPY VARCHAR2
618 )
619 IS
620 --{
621 -- Local Variables
622 l_entity_rec WSH_OTM_ENTITY_REC_TYPE := WSH_OTM_ENTITY_REC_TYPE(NULL,NULL,NULL,NULL,WSH_OTM_RD_NUM_TBL_TYPE(),
623 WSH_OTM_RD_NUM_TBL_TYPE());
624 l_return_status VARCHAR2(1);
625 l_num_errors NUMBER := 0;
626 l_num_warnings NUMBER := 0;
627 l_corporation_id NUMBER;
628 l_location_type VARCHAR2(50);
629 l_loc_exists VARCHAR2(1);
630 -- cursors
631 cursor l_get_del_csr is
632 SELECT DISTINCT WDL.DELIVERY_ID
633 FROM WSH_DELIVERY_LEGS WDL,
634 WSH_TRIP_STOPS WTS,
635 WSH_OTM_LOCATIONS_GTMP WLT
636 WHERE WTS.TRIP_ID = WLT.LOCATION_ID
637 AND WLT.LOCATION_TYPE = 'TRIP'
638 AND (WTS.STOP_ID = WDL.PICK_UP_STOP_ID
639 OR WTS.STOP_ID = WDL.DROP_OFF_STOP_ID
640 );
641 --AND WTS.TMS_INTERFACE_FLAG ='ASP';
642
643 -- We cannot add the join to WSH_OTM_LOCATIONS_GTMP for 'CUST_LOC' or 'ORG_LOC'
644 -- because we don't know the corporation_id
645 cursor l_get_stops_csr is
646 SELECT WL.LOCATION_SOURCE_CODE LOC_TYPE,
647 WTS.STOP_LOCATION_ID LOCATION_ID,
648 WTS.STOP_ID
649 FROM WSH_TRIP_STOPS WTS,
650 WSH_OTM_LOCATIONS_GTMP WLT,
651 WSH_LOCATIONS WL
652 WHERE WTS.TRIP_ID = WLT.LOCATION_ID
653 AND WLT.LOCATION_TYPE = 'TRIP'
654 --bug 6770323: Modified AND condition
655 AND WL.WSH_LOCATION_ID = NVL(WTS.PHYSICAL_LOCATION_ID, WTS.STOP_LOCATION_ID);
656 --AND WTS.TMS_INTERFACE_FLAG ='ASP';
657
658 cursor l_check_loc_exists_csr(
659 p_location_id IN NUMBER,
660 p_corp_id IN NUMBER,
661 p_loc_type IN VARCHAR2) is
662 select 'X'
663 from WSH_OTM_LOCATIONS_GTMP
664 where location_id = p_location_id
665 AND (p_corp_id is NULL OR corporation_id = p_corp_id)
666 and location_type = p_loc_type;
667
668
669 --}
670 --
671 l_debug_on BOOLEAN;
672 --
673 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'EXTRACT_TRIP_INFO';
674 --
675 BEGIN
676 --{
677 --
678 --
679 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
680 --
681 IF l_debug_on IS NULL
682 THEN
683 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
684 END IF;
685 --
686 --
687 -- Debug Statements
688 --
689 IF l_debug_on THEN
690 WSH_DEBUG_SV.push(l_module_name);
691 --
692 WSH_DEBUG_SV.log(l_module_name,'P_TRANSMISSION_ID',P_TRANSMISSION_ID);
693 END IF;
694 --
695 x_return_status := wsh_util_core.g_ret_sts_success;
696 --
697 IF ( p_entity_in_rec.entity_id_tbl.count > 0
698 AND p_entity_in_rec.entity_type = 'TRIP'
699 )
700 THEN
701 --{
702 FORALL i in p_entity_in_rec.entity_id_tbl.first..p_entity_in_rec.entity_id_tbl.last
703 insert into wsh_otm_locations_gtmp
704 (location_id,
705 location_type
706 )
707 values
708 (
709 p_entity_in_rec.entity_id_tbl(i),
710 'TRIP'
711 );
712
713 open l_get_del_csr;
714 fetch l_get_del_csr bulk collect into l_entity_rec.entity_id_tbl;
715 close l_get_del_csr;
716
717 l_entity_rec.entity_type := 'DELIVERY';
718
719 IF (l_entity_rec.entity_id_tbl.count > 0) THEN
720 --{
721 EXTRACT_DLVY_INFO
722 (
723 p_entity_in_rec => l_entity_rec,
724 p_transmission_id => p_transmission_id,
725 x_return_status => l_return_status
726 );
727
728 --
729 -- Debug Statements
730 --
731 IF l_debug_on THEN
732 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
733 END IF;
734 --
735 wsh_util_core.api_post_call(
736 p_return_status => l_return_status,
737 x_num_warnings => l_num_warnings,
738 x_num_errors => l_num_errors);
739 --}
740 END IF;
741
742 -- Now, loop through all the stops in the trip to fetch the corresponding locatioins
743 FOR l_stop_rec in l_get_stops_csr LOOP
744 --{
745 l_corporation_id := NULL;
746 l_location_type := NULL;
747 l_loc_exists := NULL;
748
749 IF (l_stop_rec.loc_type = 'HR') THEN
750 l_location_type := 'ORG_LOC';
751 ELSIF (l_stop_rec.loc_type = 'HZ') THEN
752 l_location_type := 'CUST_LOC';
753 END IF;
754 --
755 l_corporation_id := get_stop_corp_id(l_stop_rec.stop_id, l_stop_rec.loc_type);
756 --
757
758 open l_check_loc_exists_csr(l_stop_rec.location_id, l_corporation_id,l_location_type);
759 fetch l_check_loc_exists_csr into l_loc_exists;
760 close l_check_loc_exists_csr;
761
762 IF (l_loc_exists IS NULL) THEN
763 --{
764 INSERT_ROW_IN_LOC_GTMP
765 (
766 p_location_id => l_stop_rec.location_id,
767 p_corporation_id => l_corporation_id,
768 p_location_type => l_location_type,
769 x_return_status => l_return_status
770 );
771
772 --
773 -- Debug Statements
774 --
775 IF l_debug_on THEN
776 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
777 END IF;
778 --
779 wsh_util_core.api_post_call(
780 p_return_status => l_return_status,
781 x_num_warnings => l_num_warnings,
782 x_num_errors => l_num_errors);
783
784 --}
785 END IF;
786 --}
787 END LOOP;
788 --}
789 END IF;
790 --
791 IF (l_num_warnings > 0 AND x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
792 --
793 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
794 --
795 ELSE
796 --
797 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
798 --
799 END IF;
800 --
801 --}
802 --
803 -- Debug Statements
804 --
805 IF l_debug_on THEN
806 WSH_DEBUG_SV.pop(l_module_name);
807 END IF;
808 --
809 EXCEPTION
810 --{
811 WHEN FND_API.G_EXC_ERROR THEN
812 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
813 --
814 -- Debug Statements
815 --
816 IF l_debug_on THEN
817 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
818 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
819 END IF;
820 --
821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
822 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
823 --
824 -- Debug Statements
825 --
826 IF l_debug_on THEN
827 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
828 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
829 END IF;
830 --
831 WHEN OTHERS THEN
832 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
833 --}
834 --
835 -- Debug Statements
836 --
837 IF l_debug_on THEN
838 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
839 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
840 END IF;
841 --
842 END EXTRACT_TRIP_INFO;
843
844 PROCEDURE EXTRACT_CARRIER_INFO
845 (
846 p_entity_in_rec IN WSH_OTM_ENTITY_REC_TYPE,
847 p_transmission_id IN NUMBER,
848 x_return_status OUT NOCOPY VARCHAR2
849 )
850 IS
851 --{
852 i NUMBER := 0;
853
854 -- bug 5118375
855 l_location_id NUMBER;
856 l_return_status VARCHAR2(1);
857 l_num_errors NUMBER := 0;
858 l_num_warnings NUMBER := 0;
859
860 cursor l_get_loc_id_csr (p_party_id IN NUMBER, p_party_site_id IN NUMBER) IS
861 select location_id
862 from hz_party_sites
863 where party_site_id = p_party_site_id
864 and party_id = p_party_id;
865 -- bug 5118375
866
867 --}
868 --
869 l_debug_on BOOLEAN;
870 --
871 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'EXTRACT_CARRIER_INFO';
872 --
873 BEGIN
874 --{
875 --
876 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
877 --
878 IF l_debug_on IS NULL
879 THEN
880 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
881 END IF;
882 --
883 --
884 -- Debug Statements
885 --
886 IF l_debug_on THEN
887 WSH_DEBUG_SV.push(l_module_name);
888 --
889 WSH_DEBUG_SV.log(l_module_name,'P_TRANSMISSION_ID',P_TRANSMISSION_ID);
890 END IF;
891 --
892 x_return_status := wsh_util_core.g_ret_sts_success;
893 --
894 IF ( p_entity_in_rec.entity_id_tbl.count > 0
895 AND p_entity_in_rec.entity_id_tbl.count = p_entity_in_rec.parent_entity_id_tbl.count
896 )
897 THEN
898 --{
899 FORALL i in p_entity_in_rec.entity_id_tbl.first..p_entity_in_rec.entity_id_tbl.last
900 insert into wsh_otm_locations_gtmp
901 (location_id,
902 corporation_id,
903 location_type
904 )
905 values
906 (
907 p_entity_in_rec.entity_id_tbl(i),
908 p_entity_in_rec.parent_entity_id_tbl(i),
909 'CAR_LOC'
910 );
911
912 -- bug 5118375
913 -- For every Carrier Site created,
914 -- we need to check if the location is created in
915 -- wsh_locations or not. This is required
916 -- the location may or may not be inserted into into wsh_locations
917 -- by the time this conc. program is run.
918 l_location_id := null;
919 FOR i in p_entity_in_rec.entity_id_tbl.first..p_entity_in_rec.entity_id_tbl.last LOOP
920 --{
921 --
922 --
923 l_location_id := null;
924 --
925 open l_get_loc_id_csr(p_entity_in_rec.parent_entity_id_tbl(i),p_entity_in_rec.entity_id_tbl(i));
926 fetch l_get_loc_id_csr into l_location_id;
927 close l_get_loc_id_csr;
928 --
929 IF l_debug_on THEN
930 --
931 WSH_DEBUG_SV.log(l_module_name,'party_site_id ('||i||')',p_entity_in_rec.entity_id_tbl(i));
932 WSH_DEBUG_SV.log(l_module_name,'party_id ('||i||')',p_entity_in_rec.parent_entity_id_tbl(i));
933 WSH_DEBUG_SV.log(l_module_name,'l_location_id',l_location_id);
934 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_LOCATION',WSH_DEBUG_SV.C_PROC_LEVEL);
935 --
936 END IF;
937 --
938 wsh_util_validate.validate_location
939 (
940 p_location_id => l_location_id,
941 p_location_code => NULL,
942 x_return_status => l_return_status
943 );
944
945 --
946 -- Debug Statements
947 --
948 IF l_debug_on THEN
949 WSH_DEBUG_SV.log(l_module_name,'l_return_status', l_return_status);
950 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
951 END IF;
952 --
953 wsh_util_core.api_post_call(
954 p_return_status => l_return_status,
955 x_num_warnings => l_num_warnings,
956 x_num_errors => l_num_errors);
957 --
958 --
959 --}
960 END LOOP;
961 -- bug 5118375
962 --}
963 END IF;
964 --}
965 --
966 -- Debug Statements
967 --
968 IF l_debug_on THEN
969 WSH_DEBUG_SV.pop(l_module_name);
970 END IF;
971 --
972 EXCEPTION
973 --{
974 WHEN OTHERS THEN
975 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
976 --
977 -- Debug Statements
978 --
979 IF l_debug_on THEN
980 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
981 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
982 END IF;
983 --
984 --}
985 END EXTRACT_CARRIER_INFO;
986
987 --- wms-otm-proj
988 PROCEDURE EXTRACT_ORG_INFO
989 (
990 p_entity_in_rec IN WSH_OTM_ENTITY_REC_TYPE,
991 p_transmission_id IN NUMBER,
992 x_return_status OUT NOCOPY VARCHAR2
993 )
994 IS
995 --{
996 i NUMBER := 0;
997
998 l_location_id NUMBER;
999 l_gtmp_location_id NUMBER;
1000 l_return_status VARCHAR2(1);
1001 l_num_errors NUMBER := 0;
1002 l_num_warnings NUMBER := 0;
1003
1004 cursor l_get_org_loc_id_csr (c_organization_id IN NUMBER) IS
1005 select SFOV.location_id FROM_LOCATION_ID, WLT.LOCATION_ID GTMP_LOCATION_ID
1006 from WSH_SHIP_FROM_ORGS_V SFOV,
1007 WSH_OTM_LOCATIONS_GTMP WLT
1008 WHERE SFOV.organization_id = c_organization_id
1009 AND SFOV.ORGANIZATION_ID = WLT.CORPORATION_ID (+)
1010 AND WLT.LOCATION_TYPE (+) = 'ORG_LOC';
1011
1012 --}
1013 --
1014 l_debug_on BOOLEAN;
1015 --
1016 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'EXTRACT_ORG_INFO';
1017 --
1018 BEGIN
1019 --{
1020 --
1021 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1022 --
1023 IF l_debug_on IS NULL
1024 THEN
1025 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1026 END IF;
1027 --
1028 --
1029 -- Debug Statements
1030 --
1031 IF l_debug_on THEN
1032 WSH_DEBUG_SV.push(l_module_name);
1033 --
1034 WSH_DEBUG_SV.log(l_module_name,'P_TRANSMISSION_ID',P_TRANSMISSION_ID);
1035 END IF;
1036 --
1037 x_return_status := wsh_util_core.g_ret_sts_success;
1038 --
1039 IF ( p_entity_in_rec.entity_id_tbl.count > 0)
1040 THEN
1041 --{
1042 FOR i in p_entity_in_rec.entity_id_tbl.first..p_entity_in_rec.entity_id_tbl.last LOOP
1043 --{
1044 --
1045 l_location_id := null;
1046 l_gtmp_location_id := null;
1047 --
1048 open l_get_org_loc_id_csr(p_entity_in_rec.entity_id_tbl(i));
1049 fetch l_get_org_loc_id_csr into l_location_id, l_gtmp_location_id;
1050 close l_get_org_loc_id_csr;
1051 --
1052 -- gtmp should be Null for a New Insert since it is from GTmp table
1053 IF (l_location_id IS NOT NULL and l_gtmp_location_id IS NULL )
1054 THEN
1055 --{
1056
1057 INSERT_ROW_IN_LOC_GTMP
1058 (
1059 p_location_id => l_location_id,
1060 p_corporation_id => p_entity_in_rec.entity_id_tbl(i),
1061 p_location_type => 'ORG_LOC',
1062 x_return_status => l_return_status
1063 );
1064 --
1065 --}
1066 END IF;
1067 --
1068 -- Debug Statements
1069 --
1070 IF l_debug_on THEN
1071 WSH_DEBUG_SV.log(l_module_name,'return status from insert into locGtmp ', l_return_status);
1072 WSH_DEBUG_SV.log(l_module_name,'org_id ('||i||')',p_entity_in_rec.entity_id_tbl(i));
1073 WSH_DEBUG_SV.log(l_module_name,'from_location_id', l_location_id);
1074 WSH_DEBUG_SV.log(l_module_name,'gtmp_location_id', l_gtmp_location_id);
1075 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1076 --
1077 END IF;
1078 --
1079 --
1080 wsh_util_core.api_post_call(
1081 p_return_status => l_return_status,
1082 x_num_warnings => l_num_warnings,
1083 x_num_errors => l_num_errors);
1084
1085 --}
1086 END LOOP;
1087 --}
1088 END IF;
1089 --}
1090 x_return_status := l_return_status;
1091 --
1092 -- Debug Statements
1093 --
1094 IF l_debug_on THEN
1095 WSH_DEBUG_SV.pop(l_module_name);
1096 END IF;
1097 --
1098 EXCEPTION
1099 --{
1100 WHEN OTHERS THEN
1101 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1102 --
1103 IF l_get_org_loc_id_csr%ISOPEN THEN
1104 CLOSE l_get_org_loc_id_csr;
1105 END IF;
1106 --
1107 -- Debug Statements
1108 --
1109 IF l_debug_on THEN
1110 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1111 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1112 END IF;
1113 --
1114 --}
1115 END EXTRACT_ORG_INFO;
1116 --
1117 --
1118 FUNCTION GET_STATE_CODE
1119 (
1120 p_location_id IN NUMBER,
1121 p_state IN VARCHAR2
1122 ) RETURN VARCHAR2
1123 IS
1124 --{
1125 l_state_code VARCHAR2(100);
1126
1127 cursor l_get_state_code_csr(p_location_id IN NUMBER) is
1128 select wr.state_code
1129 from wsh_regions wr,
1130 wsh_region_locations wrl,
1131 wsh_locations wl
1132 where wl.wsh_location_id = p_location_id
1133 and wl.wsh_location_id = wrl.location_id
1134 and wrl.region_type = 1
1135 and wrl.region_id = wr.region_id;
1136 --}
1137 --
1138 l_debug_on BOOLEAN;
1139 --
1140 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_STATE_CODE';
1141 --
1142 BEGIN
1143 --{
1144 --
1145 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1146 --
1147 IF l_debug_on IS NULL
1148 THEN
1149 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1150 END IF;
1151 --
1152 --
1153 -- Debug Statements
1154 --
1155 IF l_debug_on THEN
1156 WSH_DEBUG_SV.push(l_module_name);
1157 --
1158 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
1159 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1160 END IF;
1161 --
1162 open l_get_state_code_csr(p_location_id);
1163 fetch l_get_state_code_csr into l_state_code;
1164 close l_get_state_code_csr;
1165
1166 IF (l_state_code is null and length(p_state) = 2 ) THEN
1167 l_state_code := p_state;
1168 END IF;
1169 --
1170 -- Debug Statements
1171 --
1172 IF l_debug_on THEN
1173 WSH_DEBUG_SV.log(l_module_name,'l_state_code',l_state_code);
1174 WSH_DEBUG_SV.pop(l_module_name);
1175 END IF;
1176 --
1177 return l_state_code;
1178 --}
1179 EXCEPTION
1180 --{
1181 WHEN OTHERS THEN
1182 --
1183 -- Debug Statements
1184 --
1185 IF l_debug_on THEN
1186 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1187 WSH_DEBUG_SV.pop(l_module_name);
1188 END IF;
1189 return NULL;
1190 --
1191 --}
1192 END GET_STATE_CODE;
1193
1194 PROCEDURE EXTEND_LOCATIONS_TBL
1195 (
1196 p_tbl_extend_index IN NUMBER,
1197 x_locations_tbl IN OUT NOCOPY WSH_OTM_LOCATIONS_TBL_TYPE
1198 )
1199 IS
1200 --
1201 l_debug_on BOOLEAN;
1202 --
1203 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'EXTEND_LOCATIONS_TBL';
1204 --
1205 BEGIN
1206 --{
1207 --
1208 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1209 --
1210 IF l_debug_on IS NULL
1211 THEN
1212 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1213 END IF;
1214 --
1215 --
1216 -- Debug Statements
1217 --
1218 IF l_debug_on THEN
1219 WSH_DEBUG_SV.push(l_module_name);
1220 --
1221 WSH_DEBUG_SV.log(l_module_name,'P_TBL_EXTEND_INDEX',P_TBL_EXTEND_INDEX);
1222 END IF;
1223 --
1224 x_locations_tbl.extend;
1225 x_locations_tbl(p_tbl_extend_index) := WSH_OTM_LOCATIONS_REC_TYPE
1226 (NULL,
1227 NULL,
1228 NULL,
1229 NULL,
1230 NULL,
1231 NULL,
1232 NULL,
1233 NULL,
1234 NULL,
1235 NULL,
1236 NULL,
1237 NULL,
1238 NULL,
1239 NULL,
1240 NULL,
1241 NULL,
1242 NULL,
1243 NULL,
1244 WSH_OTM_SERVICE_PROV_TBL_TYPE(),
1245 WSH_OTM_LOC_ADDR_TBL_TYPE(),
1246 WSH_OTM_LOC_REF_NUM_TBL_TYPE(),
1247 WSH_OTM_LOC_CONTACT_TBL_TYPE()
1248 );
1249 --}
1250 --
1251 -- Debug Statements
1252 --
1253 IF l_debug_on THEN
1254 WSH_DEBUG_SV.pop(l_module_name);
1255 END IF;
1256 --
1257 END EXTEND_LOCATIONS_TBL;
1258
1259
1260 PROCEDURE EXTND_ASSIGN_LOC_REF_NUM_TBL
1261 (
1262 p_domain_name IN VARCHAR2,
1263 p_qualifier IN VARCHAR2,
1264 p_value IN VARCHAR2,
1265 x_ref_num_tbl IN OUT NOCOPY WSH_OTM_LOC_REF_NUM_TBL_TYPE
1266 )
1267 IS
1268 --
1269 l_debug_on BOOLEAN;
1270 --
1271 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'EXTND_ASSIGN_LOC_REF_NUM_TBL';
1272
1273 l_count NUMBER;
1274 --
1275 BEGIN
1276 --{
1277 --
1278 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1279 --
1280 IF l_debug_on IS NULL
1281 THEN
1282 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1283 END IF;
1284 --
1285 --
1286 -- Debug Statements
1287 --
1288 IF l_debug_on THEN
1289 WSH_DEBUG_SV.push(l_module_name);
1290 --
1291 WSH_DEBUG_SV.log(l_module_name,'p_qualifier',p_qualifier);
1292 WSH_DEBUG_SV.log(l_module_name,'p_domain_name',p_domain_name);
1293 WSH_DEBUG_SV.log(l_module_name,'p_value',p_value);
1294 END IF;
1295 --
1296 l_count := x_ref_num_tbl.count + 1;
1297 --
1298 --
1299 IF l_debug_on THEN
1300 --
1301 WSH_DEBUG_SV.log(l_module_name,'l_count',l_count);
1302 --
1303 END IF;
1304 --
1305 IF (p_value IS NOT NULL AND p_domain_name IS NOT NULL AND p_qualifier IS NOT NULL) THEN
1306 x_ref_num_tbl.extend;
1307 x_ref_num_tbl(l_count):= WSH_OTM_LOC_REF_NUM_REC_TYPE(NULL,NULL,NULL);
1308 x_ref_num_tbl(l_count).LOC_REF_NUM_QUALIFIER_XID := p_qualifier;
1309 x_ref_num_tbl(l_count).LOC_REF_NUM_VALUE := p_value;
1310 x_ref_num_tbl(l_count).LOC_REF_NUM_QUALIFIER_DN := p_domain_name;
1311 END IF;
1312 --}
1313 --
1314 -- Debug Statements
1315 --
1316 IF l_debug_on THEN
1317 WSH_DEBUG_SV.pop(l_module_name);
1318 END IF;
1319 --
1320 END EXTND_ASSIGN_LOC_REF_NUM_TBL;
1321
1322 PROCEDURE EXTRACT_LOCATION_INFO
1323 (
1324 p_in_rec IN IN_REC_TYPE,
1325 p_transmission_id IN NUMBER,
1326 p_entity_type IN VARCHAR2,
1327 x_loc_xmission_rec OUT NOCOPY WSH_OTM_LOC_XMISSION_REC_TYPE,
1328 x_return_status OUT NOCOPY VARCHAR2
1329 )
1330 IS
1331 --{
1332 -- local variables
1333 i NUMBER;
1334 j NUMBER;
1335 k NUMBER;
1336 l_return_status VARCHAR2(1);
1337 l_num_errors NUMBER := 0;
1338 l_num_warnings NUMBER := 0;
1339
1340 l_state_code VARCHAR2(2);
1341 l_country_code VARCHAR2(3);
1342 l_domain_name VARCHAR2(50);
1343 l_pub_dn_name CONSTANT VARCHAR2(50) := 'PUBLIC';
1344 l_username VARCHAR2(100);
1345 l_password VARCHAR2(100);
1346 l_org_cust_loc_role VARCHAR2(20) := 'SHIPFROM/SHIPTO';
1347 l_car_loc_role VARCHAR2(20) := 'CARRIER';
1348 l_substitute_entity VARCHAR2(50);
1349 l_last_update_date DATE;
1350 l_send_allowed BOOLEAN := true;
1351 l_contact_ph VARCHAR2(4000);
1352
1353 l_ref_num_value VARCHAR2(101);
1354 l_ref_num_dn_name VARCHAR2(50);
1355 l_ref_num_qual VARCHAR2(50);
1356
1357 l_customer_id NUMBER;
1358 l_prev_customer_id NUMBER;
1359 l_carrier_id NUMBER;
1360 l_prev_carrier_id NUMBER;
1361
1362 l_profile_queried BOOLEAN := false;
1363
1364 l_address_line VARCHAR2(32767);
1365
1366 --contact related variables
1367 TYPE char500_tab_type IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
1368 l_contact_first_name_tbl char500_tab_type;
1369 l_contact_last_name_tbl char500_tab_type;
1370 l_contact_ph_cntr_code_tbl wsh_util_core.Column_Tab_Type;
1371 l_contact_ph_area_code_tbl wsh_util_core.Column_Tab_Type;
1372 l_contact_ph_number_tbl wsh_util_core.Column_Tab_Type;
1373 l_contact_email_addr_tbl char500_tab_type;
1374 l_contact_job_title_tbl char500_tab_type;
1375 l_contact_id_tbl wsh_util_core.id_tab_type;
1376 l_contact_last_upd_date_tbl wsh_util_core.Date_Tab_Type;
1377
1378 -- eco 5381528
1379 l_dispatch_loc CONSTANT VARCHAR2(20) := 'DISPATCH LOCATION';
1380 l_supplier_site_ref_value VARCHAR2(1000);
1381
1382 -- cursors
1383
1384 -- Cursor to get the Org Location
1385 cursor l_org_loc_csr is
1386 select 'ORG-'|| decode(ORG.ORGANIZATION_ID,NULL,'000',ORG.ORGANIZATION_ID)||'-'|| HRL.LOCATION_ID LOCATION_XID,
1387 ORG.NAME LOCATION_NAME,
1388 HRL.ADDRESS_LINE_1,
1389 HRL.ADDRESS_LINE_2,
1390 HRL.ADDRESS_LINE_3,
1391 HRL.TOWN_OR_CITY,
1392 nvl(HRL.REGION_2,HRL.REGION_1) PROVINCE,
1393 --nvl(HRL.REGION_2,HRL.REGION_1) PROVINCE_CODE,
1394 HRL.POSTAL_CODE,
1395 FNDTR.ISO_TERRITORY_CODE COUNTRY,
1396 HRL.TELEPHONE_NUMBER_1 PHONE1,
1397 HRL.TELEPHONE_NUMBER_2 PHONE2,
1398 HRL.TELEPHONE_NUMBER_3 FAX,
1399 ORG.NAME,
1400 MP.ORGANIZATION_CODE,
1401 WL.WSH_LOCATION_ID,
1402 HRL.LOCATION_ID,
1403 ORG.ORGANIZATION_ID,
1404 GREATEST(HRL.LAST_UPDATE_DATE,
1405 nvl(ORG.LAST_UPDATE_DATE, to_date('1900/01/01 00:00:01', 'YYYY/MM/DD HH24:MI:SS'))
1406 ) LAST_UPDATE_DATE
1407 from HR_LOCATIONS_ALL HRL,
1408 HR_ALL_ORGANIZATION_UNITS ORG,
1409 --HR_ALL_ORGANIZATION_UNITS_TL ORGL,
1410 --FND_LANGUAGES FNDL,
1411 FND_TERRITORIES FNDTR,
1412 WSH_OTM_LOCATIONS_GTMP WLT, -- global temp table
1413 MTL_PARAMETERS MP,
1414 WSH_LOCATIONS WL
1415 WHERE WLT.LOCATION_ID = WL.WSH_LOCATION_ID
1416 AND WLT.LOCATION_TYPE = 'ORG_LOC'
1417 AND WL.LOCATION_SOURCE_CODE = 'HR'
1418 AND WL.SOURCE_LOCATION_ID = HRL.LOCATION_ID
1419 AND WLT.CORPORATION_ID = ORG.ORGANIZATION_ID (+)
1420 AND ORG.ORGANIZATION_ID = MP.ORGANIZATION_ID (+)
1421 --AND WLT.CORPORATION_ID = ORGL.ORGANIZATION_ID (+)
1422 --AND ORGL.LANGUAGE = FNDL.LANGUAGE_CODE (+)
1423 --AND FNDL.INSTALLED_FLAG (+) = 'B'
1424 AND HRL.COUNTRY = FNDTR.TERRITORY_CODE(+);
1425
1426 -- Cursor to get the Customer Location
1427 cursor l_cust_loc_csr is
1428 SELECT HZL.LOCATION_ID,
1429 HZL.ADDRESS1,
1430 HZL.ADDRESS2,
1431 HZL.ADDRESS3,
1432 HZL.ADDRESS4,
1433 HZL.CITY,
1434 nvl(HZL.STATE,HZL.PROVINCE) PROVINCE,
1435 HZL.STATE,
1436 HZL.POSTAL_CODE POSTAL_CODE,
1437 FNDTR.ISO_TERRITORY_CODE COUNTRY,
1438 HP.PARTY_NAME,
1439 HCA.CUST_ACCOUNT_ID,
1440 HCA.ACCOUNT_NUMBER,
1441 WL.WSH_LOCATION_ID,
1442 GREATEST(HZL.LAST_UPDATE_DATE,
1443 nvl(GREATEST(HP.LAST_UPDATE_DATE,HCA.LAST_UPDATE_DATE),
1444 to_date('1900/01/01 00:00:01', 'YYYY/MM/DD HH24:MI:SS')
1445 )
1446 ) LAST_UPDATE_DATE
1447 FROM HZ_LOCATIONS HZL,
1448 WSH_LOCATIONS WL,
1449 FND_TERRITORIES FNDTR,
1450 WSH_OTM_LOCATIONS_GTMP WLT,
1451 HZ_PARTIES HP,
1452 HZ_CUST_ACCOUNTS HCA
1453 WHERE HZL.LOCATION_ID = WL.SOURCE_LOCATION_ID
1454 AND WL.LOCATION_SOURCE_CODE = 'HZ'
1455 AND FNDTR.TERRITORY_CODE (+) = HZL.COUNTRY
1456 AND WL.WSH_LOCATION_ID = WLT.LOCATION_ID
1457 AND WLT.LOCATION_TYPE = 'CUST_LOC'
1458 AND WLT.CORPORATION_ID = HCA.CUST_ACCOUNT_ID (+)
1459 AND HCA.PARTY_ID = HP.PARTY_ID (+);
1460
1461 -- Cursor to get the Customer Location Contact Information
1462 cursor l_ship_to_contact_csr (p_location_id IN NUMBER,
1463 p_corp_id IN NUMBER) is
1464 SELECT PER_CONTACT.PERSON_FIRST_NAME,
1465 PER_CONTACT.PERSON_LAST_NAME,
1466 PHONE_CONTACT.PHONE_COUNTRY_CODE,
1467 PHONE_CONTACT.PHONE_AREA_CODE,
1468 PHONE_CONTACT.PHONE_NUMBER,
1469 PER_CONTACT. EMAIL_ADDRESS,
1470 HOC.JOB_TITLE,
1471 HCAR.CUST_ACCOUNT_ROLE_ID,
1472 GREATEST(
1473 GREATEST(
1474 PHONE_CONTACT.LAST_UPDATE_DATE,
1475 GREATEST(
1476 HREL.LAST_UPDATE_DATE,
1477 GREATEST(
1478 HOC.LAST_UPDATE_DATE,
1479 HCAR.LAST_UPDATE_DATE
1480 )
1481 )
1482 ),
1483 PER_CONTACT.LAST_UPDATE_DATE
1484 ) LAST_UPDATE_DATE
1485 FROM HZ_CUST_ACCOUNT_ROLES HCAR,
1486 HZ_RELATIONSHIPS HREL,
1487 HZ_ORG_CONTACTS HOC,
1488 HZ_CONTACT_POINTS PHONE_CONTACT,
1489 HZ_PARTIES PER_CONTACT,
1490 WSH_OTM_LOC_CONTACTS_GTMP WLCT
1491 WHERE HREL.PARTY_ID = HCAR.PARTY_ID
1492 AND HCAR.ROLE_TYPE = 'CONTACT'
1493 AND HREL.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID
1494 AND HREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1495 AND HREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1496 AND HREL.SUBJECT_TYPE = 'PERSON'
1497 AND HREL.DIRECTIONAL_FLAG = 'F'
1498 AND HREL.SUBJECT_ID = PER_CONTACT.PARTY_ID
1499 AND PHONE_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
1500 AND PHONE_CONTACT.OWNER_TABLE_ID(+) = HREL.PARTY_ID
1501 AND PHONE_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
1502 AND PHONE_CONTACT.PHONE_LINE_TYPE(+) = 'GEN'
1503 AND PHONE_CONTACT.PRIMARY_FLAG(+) = 'Y'
1504 AND HCAR.CUST_ACCOUNT_ROLE_ID = WLCT.CONTACT_ID
1505 AND WLCT.LOCATION_TYPE = 'CUST_LOC'
1506 AND WLCT.LOCATION_ID = p_location_id
1507 AND (WLCT.CORPORATION_ID = p_corp_id or p_corp_id is NULL)
1508 ORDER BY LAST_UPDATE_DATE DESC;
1509
1510 -- Cursor to get the Carrier Location
1511
1512 cursor l_carrier_loc_csr is
1513 SELECT WCV.CARRIER_ID,
1514 HPS.PARTY_SITE_ID CARRIER_SITE_ID,
1515 HP.PARTY_NAME CARRIER_NAME,
1516 substrb(HP.PARTY_NAME,1,10)||','||substrb(HZL.CITY,1,10)||','|| substrb(HZL.STATE,1,4)||','||substrb(HZL.COUNTRY,1,2) LOCATION_NAME,
1517 HZL.ADDRESS1,
1518 HZL.ADDRESS2,
1519 HZL.ADDRESS3,
1520 HZL.ADDRESS4,
1521 HZL.CITY CITY,
1522 nvl(HZL.STATE,HZL.PROVINCE) PROVINCE,
1523 --nvl(HZL.STATE,HZL.PROVINCE) PROVINCE_CODE,
1524 HZL.POSTAL_CODE POSTAL_CODE,
1525 FNDTR.ISO_TERRITORY_CODE COUNTRY,
1526 nvl(WCS.SUPPLIER_SITE_ID,WCV.SUPPLIER_SITE_ID) SUPPLIER_SITE_ID,
1527 WCV.SUPPLIER_ID,
1528 WCV.SUPPLIER_SITE_ID CAR_SUPPLIER_SITE_ID, -- bug#7218387: needs to pass supplier_site_id at carrier level.
1529 WCV.SCAC_CODE,
1530 HZL.LOCATION_ID,
1531 WSL.WSH_LOCATION_ID,
1532 HPS.PARTY_SITE_NUMBER,
1533 HPS.PARTY_SITE_NUMBER CARRIER_SITE_NUMBER,
1534 HZL.LAST_UPDATE_DATE HZL_LAST_UPD_DATE,
1535 WCV.LAST_UPDATE_DATE WCV_LAST_UPD_DATE,
1536 HPS.LAST_UPDATE_DATE HPS_LAST_UPD_DATE,
1537 HP.LAST_UPDATE_DATE HP_LAST_UPD_DATE
1538 FROM HZ_LOCATIONS HZL,
1539 FND_TERRITORIES FNDTR,
1540 WSH_OTM_LOCATIONS_GTMP wlt,
1541 WSH_CARRIERS WCV,
1542 WSH_CARRIER_SITES WCS,
1543 HZ_PARTY_SITES HPS,
1544 HZ_PARTIES HP,
1545 WSH_LOCATIONS WSL
1546 WHERE WCV.CARRIER_ID = HPS.PARTY_ID
1547 AND HPS.LOCATION_ID = HZL.LOCATION_ID
1548 AND WSL.SOURCE_LOCATION_ID = HZL.LOCATION_ID
1549 AND WSL.LOCATION_SOURCE_CODE = 'HZ'
1550 AND FNDTR.TERRITORY_CODE (+) = HZL.COUNTRY
1551 AND HPS.PARTY_SITE_ID = WLT.LOCATION_ID
1552 AND WLT.LOCATION_TYPE = 'CAR_LOC'
1553 AND WCV.CARRIER_ID = WLT.CORPORATION_ID
1554 AND WCV.CARRIER_ID = HP.PARTY_ID
1555 AND HPS.PARTY_SITE_ID = WCS.CARRIER_SITE_ID(+);
1556 --
1557 --Bug #7274527 :get the operating unit associated to supplier site
1558 CURSOR c_get_ou(p_supplier_site_id NUMBER)IS
1559 SELECT org_id
1560 FROM ap_supplier_sites_all
1561 WHERE vendor_site_id = p_supplier_site_id
1562 AND ROWNUM =1;
1563
1564 l_opertaing_unit_id NUMBER;
1565 --
1566 --Bug #7274527 : end
1567
1568
1569 --}
1570 --
1571 l_debug_on BOOLEAN;
1572 --
1573 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'EXTRACT_LOCATION_INFO';
1574 --
1575 BEGIN
1576 --{
1577 --
1578 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1579 --
1580 IF l_debug_on IS NULL
1581 THEN
1582 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1583 END IF;
1584 --
1585 --
1586 -- Debug Statements
1587 --
1588 IF l_debug_on THEN
1589 WSH_DEBUG_SV.push(l_module_name);
1590 --
1591 WSH_DEBUG_SV.log(l_module_name,'P_TRANSMISSION_ID',P_TRANSMISSION_ID);
1592 END IF;
1593 --
1594 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1595
1596 x_loc_xmission_rec := WSH_OTM_LOC_XMISSION_REC_TYPE(NULL, NULL,WSH_OTM_LOCATIONS_TBL_TYPE());
1597 --x_loc_xmission_rec.LOCATIONS_TBL := WSH_OTM_LOCATIONS_TBL_TYPE();
1598
1599 i := x_loc_xmission_rec.LOCATIONS_TBL.count+1;
1600
1601 -- Get the profile values
1602
1603 FND_PROFILE.Get('WSH_OTM_DOMAIN_NAME',l_domain_name);
1604 FND_PROFILE.Get('WSH_OTM_CORP_COUNTRY_CODE',l_country_code);
1605
1606 IF (l_domain_name is null) THEN
1607 --{
1608 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_DOMAIN_NOT_SET_ERR');
1609 x_return_status := wsh_util_core.G_RET_STS_UNEXP_ERROR;
1610 wsh_util_core.add_message(x_return_status, l_module_name);
1611 IF l_debug_on THEN
1612 WSH_DEBUG_SV.logmsg(l_module_name,'Error: The profile WSH_OTM_DOMAIN_NAME is set to NULL. Please correct the profile value');
1613 END IF;
1614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1615 --}
1616 END IF;
1617
1618 IF (l_country_code is null) THEN
1619 --{
1620 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_CNTR_CODE_NOT_SET_ERR');
1621 x_return_status := wsh_util_core.G_RET_STS_UNEXP_ERROR;
1622 wsh_util_core.add_message(x_return_status, l_module_name);
1623 IF l_debug_on THEN
1624 WSH_DEBUG_SV.logmsg(l_module_name,'Error: The profile WSH_OTM_CORP_COUNTRY_CODE is set to NULL. Please correct the profile value');
1625 END IF;
1626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1627 --}
1628 END IF;
1629
1630 -- Extracting the Org Location Information
1631 FOR org_loc_rec in l_org_loc_csr LOOP
1632 --{
1633
1634 --
1635 -- Debug Statements
1636 --
1637 IF l_debug_on THEN
1638 --
1639 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.LOCATION_XID',org_loc_rec.LOCATION_XID);
1640 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.LOCATION_NAME',org_loc_rec.LOCATION_NAME);
1641 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.ADDRESS_LINE_1',org_loc_rec.ADDRESS_LINE_1);
1642 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.ADDRESS_LINE_2',org_loc_rec.ADDRESS_LINE_2);
1643 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.ADDRESS_LINE_3',org_loc_rec.ADDRESS_LINE_3);
1644 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.TOWN_OR_CITY',org_loc_rec.TOWN_OR_CITY);
1645 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.PROVINCE',org_loc_rec.PROVINCE);
1646 --WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.PROVINCE_CODE',org_loc_rec.PROVINCE_CODE);
1647 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.POSTAL_CODE',org_loc_rec.POSTAL_CODE);
1648 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.COUNTRY',org_loc_rec.COUNTRY);
1649 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.PHONE1',org_loc_rec.PHONE1);
1650 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.PHONE2',org_loc_rec.PHONE2);
1651 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.FAX',org_loc_rec.FAX);
1652 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.NAME',org_loc_rec.NAME);
1653 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.ORGANIZATION_CODE',org_loc_rec.ORGANIZATION_CODE);
1654 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.WSH_LOCATION_ID',org_loc_rec.WSH_LOCATION_ID);
1655 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.LOCATION_ID',org_loc_rec.LOCATION_ID);
1656 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.ORGANIZATION_ID',org_loc_rec.ORGANIZATION_ID);
1657 WSH_DEBUG_SV.log(l_module_name,'org_loc_rec.LAST_UPDATE_DATE',org_loc_rec.LAST_UPDATE_DATE);
1658 --
1659 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_OTM_SYNC_REF_DATA_PKG.IS_REF_DATA_SEND_REQD',WSH_DEBUG_SV.C_PROC_LEVEL);
1660 --
1661 END IF;
1662 --
1663 WSH_OTM_SYNC_REF_DATA_PKG.IS_REF_DATA_SEND_REQD
1664 (
1665 P_ENTITY_ID => org_loc_rec.location_id,
1666 P_PARENT_ENTITY_ID => org_loc_rec.organization_id,
1667 P_ENTITY_TYPE => 'ORG_LOC',
1668 P_ENTITY_UPDATED_DATE => org_loc_rec.last_update_date,
1669 X_SUBSTITUTE_ENTITY => l_substitute_entity,
1670 P_TRANSMISSION_ID => P_TRANSMISSION_ID,
1671 X_SEND_ALLOWED => l_send_allowed,
1672 X_RETURN_STATUS => l_return_status
1673 );
1674
1675 --
1676 -- Debug Statements
1677 --
1678 IF l_debug_on THEN
1679 WSH_DEBUG_SV.log(l_module_name,'l_send_allowed', l_send_allowed);
1680 WSH_DEBUG_SV.log(l_module_name,'Calling Entity Type from Send_LOcations', p_entity_type);
1681 WSH_DEBUG_SV.log(l_module_name,'l_return_status', l_return_status);
1682 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1683 END IF;
1684 --
1685 wsh_util_core.api_post_call(
1686 p_return_status => l_return_status,
1687 x_num_warnings => l_num_warnings,
1688 x_num_errors => l_num_errors);
1689 -- wms-otm-proj if the Call to Send_LOCATIONs is from Entity_type 'ORG_LOC' then
1690 -- we need to Send it despite the l_send_allowed being FALSE
1691 IF ( (l_send_allowed) OR (p_entity_type = 'ORG_LOC') ) THEN
1692 --{
1693
1694 -- Initially we need extend the locations table for every record.
1695 EXTEND_LOCATIONS_TBL
1696 (
1697 p_tbl_extend_index => i,
1698 x_locations_tbl => x_loc_xmission_rec.LOCATIONS_TBL
1699 );
1700
1701
1702 x_loc_xmission_rec.LOCATIONS_TBL(i).TXN_CODE := 'IU';
1703 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID := org_loc_rec.LOCATION_XID;
1704 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID IS NOT NULL) THEN
1705 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_DN := l_domain_name;
1706 END IF;
1707 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_NAME := substrb(org_loc_rec.LOCATION_NAME,1,120);
1708 x_loc_xmission_rec.LOCATIONS_TBL(i).CITY := substrb(org_loc_rec.TOWN_OR_CITY,1,30);
1709 x_loc_xmission_rec.LOCATIONS_TBL(i).PROVINCE_CODE := GET_STATE_CODE(org_loc_rec.WSH_LOCATION_ID,org_loc_rec.province);
1710 -- eco 5192928
1711 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).PROVINCE_CODE IS NULL)
1712 THEN
1713 --{
1714 x_loc_xmission_rec.LOCATIONS_TBL(i).PROVINCE := substrb(org_loc_rec.province,1,30);
1715 --}
1716 END IF;
1717 -- eco 5192928
1718 x_loc_xmission_rec.LOCATIONS_TBL(i).POSTAL_CODE := substrb(org_loc_rec.POSTAL_CODE,1,15);
1719 x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_XID := substrb(org_loc_rec.COUNTRY,1,3);
1720 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_XID IS NOT NULL) THEN
1721 x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_DN := l_pub_dn_name;
1722 END IF;
1723
1724 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_XID := l_org_cust_loc_role;
1725
1726 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_XID IS NOT NULL) THEN
1727 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_DN := l_pub_dn_name;
1728 END IF;
1729
1730 x_loc_xmission_rec.LOCATIONS_TBL(i).CORPORATION := substrb(org_loc_rec.NAME,1,30);
1731
1732 IF (l_substitute_entity IS NOT NULL) THEN
1733 x_loc_xmission_rec.LOCATIONS_TBL(i).SUBSTITUTE_LOCATION_XID := l_substitute_entity;
1734 x_loc_xmission_rec.LOCATIONS_TBL(i).SUBSTITUTE_LOCATION_DN := l_domain_name;
1735 END IF;
1736
1737 k := 0;
1738 FOR j in 1..3 LOOP
1739 --{
1740 IF (j = 1)
1741 OR
1742 (j = 2 AND org_loc_rec.ORGANIZATION_CODE IS NOT NULL)
1743 OR
1744 (j = 3 AND org_loc_rec.NAME IS NOT NULL)
1745 THEN
1746 k := k + 1;
1747 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL.extend;
1748 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL(k) := WSH_OTM_LOC_REF_NUM_REC_TYPE(NULL,NULL,NULL);
1749 END IF;
1750
1751 IF ( j = 1 ) THEN
1752 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL(k).LOC_REF_NUM_QUALIFIER_XID := 'ORIGIN';
1753 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL(k).LOC_REF_NUM_VALUE := 'ORGANIZATION';
1754 ELSIF (j = 2 AND org_loc_rec.ORGANIZATION_CODE IS NOT NULL) THEN
1755 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL(k).LOC_REF_NUM_QUALIFIER_XID := 'ORGID';
1756 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL(k).LOC_REF_NUM_VALUE := org_loc_rec.ORGANIZATION_CODE;
1757 ELSIF (j=3 AND org_loc_rec.NAME IS NOT NULL) THEN
1758 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL(k).LOC_REF_NUM_QUALIFIER_XID := 'ORGNM';
1759 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL(k).LOC_REF_NUM_VALUE :=substrb(org_loc_rec.NAME,1,101);
1760 END IF;
1761
1762 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL(k).LOC_REF_NUM_QUALIFIER_XID IS NOT NULL) THEN
1763 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL(k).LOC_REF_NUM_QUALIFIER_DN := l_pub_dn_name;
1764 END IF;
1765 --}
1766 END LOOP;
1767
1768 l_address_line := org_loc_rec.ADDRESS_LINE_1 || ' ' || org_loc_rec.ADDRESS_LINE_2 || ' ' || org_loc_rec.ADDRESS_LINE_3;
1769
1770 IF l_debug_on THEN
1771 WSH_DEBUG_SV.log(l_module_name,'l_address_line', l_address_line);
1772 END IF;
1773
1774 j := lengthb(l_address_line);
1775 k := 1;
1776 WHILE (j > 0) LOOP
1777 --{
1778 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL.extend;
1779 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL(k) := WSH_OTM_LOC_ADDR_REC_TYPE(NULL,NULL);
1780 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL(k).SEQ_NUMBER := k;
1781 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL(k).ADRESS_LINE := substrb(l_address_line,1,55);
1782 l_address_line := substrb(l_address_line,56);
1783 j := j - 55;
1784 k := k + 1;
1785 --}
1786 END LOOP;
1787
1788 IF ( org_loc_rec.PHONE1 IS NOT NULL OR org_loc_rec.PHONE2 IS NOT NULL OR org_loc_rec.FAX IS NOT NULL) THEN
1789 --{
1790 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL.extend;
1791 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(1) := WSH_OTM_LOC_CONTACT_REC_TYPE
1792 (NULL,
1793 NULL,
1794 NULL,
1795 NULL,
1796 NULL,
1797 NULL,
1798 NULL,
1799 NULL,
1800 NULL,
1801 NULL
1802 );
1803 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(1).CONTACT_XID := org_loc_rec.LOCATION_XID;
1804 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(1).CONTACT_DN := l_domain_name;
1805 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(1).PHONE1 := substrb(org_loc_rec.PHONE1,1,80);
1806 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(1).PHONE2 := substrb(org_loc_rec.PHONE2,1,80);
1807 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(1).FAX := substrb(org_loc_rec.FAX,1,80);
1808 --}
1809 END IF;
1810
1811 i := i + 1;
1812 --}
1813 END IF;
1814 --}
1815 END LOOP;
1816
1817 i := x_loc_xmission_rec.LOCATIONS_TBL.count+1;
1818 IF l_debug_on THEN
1819 WSH_DEBUG_SV.log(l_module_name,'i is ', i);
1820 END IF;
1821 -- Extracting the Cust Location Information
1822 FOR cust_loc_rec in l_cust_loc_csr LOOP
1823 --{
1824
1825 -- Initially we need extend the locations table for every record.
1826 l_substitute_entity := NULL;
1827 l_customer_id := cust_loc_rec.cust_account_id;
1828
1829 IF (l_contact_id_tbl.count > 0) THEN
1830 --{
1831 l_contact_first_name_tbl.delete;
1832 l_contact_last_name_tbl.delete;
1833 l_contact_ph_cntr_code_tbl.delete;
1834 l_contact_ph_area_code_tbl.delete;
1835 l_contact_ph_number_tbl.delete;
1836 l_contact_email_addr_tbl.delete;
1837 l_contact_job_title_tbl.delete;
1838 l_contact_id_tbl.delete;
1839 l_contact_last_upd_date_tbl.delete;
1840 --}
1841 END IF;
1842
1843 IF l_debug_on THEN
1844 WSH_DEBUG_SV.log(l_module_name,'location_id', cust_loc_rec.wsh_location_id);
1845 WSH_DEBUG_SV.log(l_module_name,'customer_id', cust_loc_rec.CUST_ACCOUNT_ID);
1846 END IF;
1847 open l_ship_to_contact_csr(cust_loc_rec.wsh_location_id, cust_loc_rec.CUST_ACCOUNT_ID);
1848 fetch l_ship_to_contact_csr bulk collect into
1849 l_contact_first_name_tbl,
1850 l_contact_last_name_tbl,
1851 l_contact_ph_cntr_code_tbl,
1852 l_contact_ph_area_code_tbl,
1853 l_contact_ph_number_tbl,
1854 l_contact_email_addr_tbl,
1855 l_contact_job_title_tbl,
1856 l_contact_id_tbl,
1857 l_contact_last_upd_date_tbl;
1858 close l_ship_to_contact_csr;
1859
1860 IF l_debug_on THEN
1861 WSH_DEBUG_SV.log(l_module_name,'count of contact table', l_contact_id_tbl.count);
1862 END IF;
1863 IF (l_contact_id_tbl.count > 0 ) THEN
1864 IF l_debug_on THEN
1865 WSH_DEBUG_SV.log(l_module_name,'l_contact_last_upd_date_tbl(1)', l_contact_last_upd_date_tbl(1));
1866 END IF;
1867 l_last_update_date := greatest(nvl(l_contact_last_upd_date_tbl(1),cust_loc_rec.last_update_date),cust_loc_rec.last_update_date);
1868 ELSE
1869 l_last_update_date := cust_loc_rec.last_update_date;
1870 END IF;
1871
1872 --
1873 -- Debug Statements
1874 --
1875 IF l_debug_on THEN
1876 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_OTM_SYNC_REF_DATA_PKG.IS_REF_DATA_SEND_REQD',WSH_DEBUG_SV.C_PROC_LEVEL);
1877 END IF;
1878 --
1879 WSH_OTM_SYNC_REF_DATA_PKG.IS_REF_DATA_SEND_REQD
1880 (
1881 P_ENTITY_ID => cust_loc_rec.wsh_location_id,
1882 P_PARENT_ENTITY_ID => cust_loc_rec.cust_account_id,
1883 P_ENTITY_TYPE => 'CUST_LOC',
1884 P_ENTITY_UPDATED_DATE => l_last_update_date,
1885 X_SUBSTITUTE_ENTITY => l_substitute_entity,
1886 P_TRANSMISSION_ID => P_TRANSMISSION_ID,
1887 X_SEND_ALLOWED => l_send_allowed,
1888 X_RETURN_STATUS => l_return_status
1889 );
1890
1891 --
1892 -- Debug Statements
1893 --
1894 IF l_debug_on THEN
1895 WSH_DEBUG_SV.log(l_module_name,'l_send_allowed', l_send_allowed);
1896 WSH_DEBUG_SV.log(l_module_name,'l_return_status', l_return_status);
1897 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1898 END IF;
1899 --
1900 wsh_util_core.api_post_call(
1901 p_return_status => l_return_status,
1902 x_num_warnings => l_num_warnings,
1903 x_num_errors => l_num_errors);
1904
1905 IF l_debug_on THEN
1906 --
1907 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.LOCATION_ID',cust_loc_rec.LOCATION_ID);
1908 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.ADDRESS1',cust_loc_rec.ADDRESS1);
1909 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.ADDRESS2',cust_loc_rec.ADDRESS2);
1910 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.ADDRESS3',cust_loc_rec.ADDRESS3);
1911 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.ADDRESS4',cust_loc_rec.ADDRESS4);
1912 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.CITY',cust_loc_rec.CITY);
1913 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.PROVINCE',cust_loc_rec.PROVINCE);
1914 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.STATE',cust_loc_rec.STATE);
1915 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.POSTAL_CODE',cust_loc_rec.POSTAL_CODE);
1916 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.COUNTRY',cust_loc_rec.COUNTRY);
1917 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.PARTY_NAME',cust_loc_rec.PARTY_NAME);
1918 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.CUST_ACCOUNT_ID',cust_loc_rec.CUST_ACCOUNT_ID);
1919 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.ACCOUNT_NUMBER',cust_loc_rec.ACCOUNT_NUMBER);
1920 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.WSH_LOCATION_ID',cust_loc_rec.WSH_LOCATION_ID);
1921 WSH_DEBUG_SV.log(l_module_name,'cust_loc_rec.LAST_UPDATE_DATE',cust_loc_rec.LAST_UPDATE_DATE);
1922 WSH_DEBUG_SV.log(l_module_name,'l_last_update_date', l_last_update_date);
1923 WSH_DEBUG_SV.log(l_module_name,'l_send_allowed', l_send_allowed);
1924 --
1925 END IF;
1926 --
1927 IF (l_send_allowed) THEN
1928 --{
1929 --
1930 --
1931 IF (cust_loc_rec.CUST_ACCOUNT_ID IS NOT NULL and (nvl(l_prev_customer_id,-999) <> nvl(l_customer_id,-998))) THEN
1932 --{
1933 EXTEND_LOCATIONS_TBL
1934 (
1935 p_tbl_extend_index => i,
1936 x_locations_tbl => x_loc_xmission_rec.LOCATIONS_TBL
1937 );
1938
1939 x_loc_xmission_rec.LOCATIONS_TBL(i).TXN_CODE := 'IU';
1940 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID := 'CUS-' || cust_loc_rec.CUST_ACCOUNT_ID;
1941
1942 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID IS NOT NULL) THEN
1943 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_DN := l_domain_name;
1944 END IF;
1945
1946 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_NAME := substrb(cust_loc_rec.PARTY_NAME,1,120);
1947 x_loc_xmission_rec.LOCATIONS_TBL(i).CORPORATION := substrb(cust_loc_rec.PARTY_NAME,1,30);
1948 x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_XID := substrb(l_country_code,1,3);
1949
1950 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_XID IS NOT NULL) THEN
1951 x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_DN := l_pub_dn_name;
1952 END IF;
1953
1954 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_XID := 'CUSTOMER';
1955
1956 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_XID IS NOT NULL) THEN
1957 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_DN := l_pub_dn_name;
1958 END IF;
1959
1960 FOR j in 1..3 LOOP
1961 --{
1962 --
1963 l_ref_num_dn_name := l_pub_dn_name;
1964 l_ref_num_value := NULL;
1965 l_ref_num_qual := NULL;
1966 IF ( j = 1 ) THEN
1967 l_ref_num_qual := 'ORIGIN';
1968 l_ref_num_value := 'CUSTOMER';
1969 ELSIF (j = 2) THEN
1970 l_ref_num_qual := 'CUSID';
1971 l_ref_num_value := substrb(cust_loc_rec.ACCOUNT_NUMBER,1,101);
1972 ELSIF (j=3) THEN
1973 l_ref_num_qual := 'CUSNM';
1974 l_ref_num_value := substrb(cust_loc_rec.PARTY_NAME,1,101);
1975 END IF;
1976 --
1977 EXTND_ASSIGN_LOC_REF_NUM_TBL
1978 (
1979 p_domain_name => l_ref_num_dn_name,
1980 p_qualifier => l_ref_num_qual,
1981 p_value => l_ref_num_value,
1982 x_ref_num_tbl => x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL
1983 );
1984 --
1985 --}
1986 END LOOP;
1987
1988 i := i + 1;
1989
1990 --}
1991 END IF;
1992 -- Initially we need extend the locations table for every record.
1993 EXTEND_LOCATIONS_TBL
1994 (
1995 p_tbl_extend_index => i,
1996 x_locations_tbl => x_loc_xmission_rec.LOCATIONS_TBL
1997 );
1998
1999
2000 x_loc_xmission_rec.LOCATIONS_TBL(i).TXN_CODE := 'IU';
2001
2002 IF (cust_loc_rec.CUST_ACCOUNT_ID IS NOT NULL) THEN
2003 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID := 'CUS-'||cust_loc_rec.CUST_ACCOUNT_ID ||'-' || cust_loc_rec.LOCATION_ID;
2004 ELSE
2005 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID := 'CUS-000-' || cust_loc_rec.LOCATION_ID;
2006 END IF;
2007
2008 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID IS NOT NULL) THEN
2009 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_DN := l_domain_name;
2010 END IF;
2011 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_NAME := substrb(cust_loc_rec.PARTY_NAME,1,10) ||','||substrb(cust_loc_rec.CITY,1,10)||','||substrb(cust_loc_rec.PROVINCE,1,4)||','||cust_loc_rec.COUNTRY;
2012 x_loc_xmission_rec.LOCATIONS_TBL(i).CITY := substrb(cust_loc_rec.CITY,1,30);
2013 x_loc_xmission_rec.LOCATIONS_TBL(i).PROVINCE_CODE := GET_STATE_CODE(cust_loc_rec.WSH_LOCATION_ID,cust_loc_rec.PROVINCE);
2014
2015 -- eco 5192928
2016 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).PROVINCE_CODE IS NULL)
2017 THEN
2018 --{
2019 x_loc_xmission_rec.LOCATIONS_TBL(i).PROVINCE := substrb(cust_loc_rec.PROVINCE,1,30);
2020 --}
2021 END IF;
2022 -- eco 5192928
2023 x_loc_xmission_rec.LOCATIONS_TBL(i).POSTAL_CODE := substrb(cust_loc_rec.POSTAL_CODE,1,15);
2024 x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_XID := substrb(cust_loc_rec.COUNTRY,1,3);
2025 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_XID IS NOT NULL) THEN
2026 x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_DN := l_pub_dn_name;
2027 END IF;
2028
2029 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_XID := l_org_cust_loc_role;
2030
2031 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_XID IS NOT NULL) THEN
2032 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_DN := l_pub_dn_name;
2033 END IF;
2034
2035 IF (cust_loc_rec.CUST_ACCOUNT_ID IS NOT NULL) THEN
2036 x_loc_xmission_rec.LOCATIONS_TBL(i).PARENT_LOCATION_XID := 'CUS-' || cust_loc_rec.CUST_ACCOUNT_ID;
2037 x_loc_xmission_rec.LOCATIONS_TBL(i).PARENT_LOCATION_DN := l_domain_name;
2038 END IF;
2039
2040 IF (l_substitute_entity IS NOT NULL) THEN
2041 x_loc_xmission_rec.LOCATIONS_TBL(i).SUBSTITUTE_LOCATION_XID := l_substitute_entity;
2042 x_loc_xmission_rec.LOCATIONS_TBL(i).SUBSTITUTE_LOCATION_DN := l_domain_name;
2043 END IF;
2044
2045
2046 FOR j in 1..3 LOOP
2047 --{
2048 --
2049 l_ref_num_dn_name := l_pub_dn_name;
2050 l_ref_num_value := NULL;
2051 l_ref_num_qual := NULL;
2052 IF ( j = 1 ) THEN
2053 l_ref_num_qual := 'ORIGIN';
2054 l_ref_num_value := 'CUSTOMER';
2055 ELSIF (j = 2) THEN
2056 l_ref_num_qual := 'CUSID';
2057 l_ref_num_value := substrb(cust_loc_rec.ACCOUNT_NUMBER,1,101);
2058 ELSIF (j=3) THEN
2059 l_ref_num_qual := 'CUSNM';
2060 l_ref_num_value := substrb(cust_loc_rec.PARTY_NAME,1,101);
2061 END IF;
2062 --
2063 EXTND_ASSIGN_LOC_REF_NUM_TBL
2064 (
2065 p_domain_name => l_ref_num_dn_name,
2066 p_qualifier => l_ref_num_qual,
2067 p_value => l_ref_num_value,
2068 x_ref_num_tbl => x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL
2069 );
2070 --
2071 --}
2072 END LOOP;
2073
2074 l_address_line := cust_loc_rec.ADDRESS1 || ' ' || cust_loc_rec.ADDRESS2 || ' ' || cust_loc_rec.ADDRESS3|| ' ' || cust_loc_rec.ADDRESS4;
2075
2076 j := lengthb(l_address_line);
2077 k := 1;
2078 WHILE (j > 0) LOOP
2079 --{
2080 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL.extend;
2081 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL(k) := WSH_OTM_LOC_ADDR_REC_TYPE(NULL,NULL);
2082 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL(k).SEQ_NUMBER := k;
2083 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL(k).ADRESS_LINE := substrb(l_address_line,1,55);
2084 l_address_line := substrb(l_address_line,56);
2085 j := j - 55;
2086 k := k + 1;
2087 --}
2088 END LOOP;
2089
2090 IF ( l_contact_id_tbl.count > 0 ) THEN
2091 --{
2092 FOR k in l_contact_id_tbl.first..l_contact_id_tbl.last LOOP
2093 --{
2094 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL.extend;
2095 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(k) := WSH_OTM_LOC_CONTACT_REC_TYPE
2096 (NULL,
2097 NULL,
2098 NULL,
2099 NULL,
2100 NULL,
2101 NULL,
2102 NULL,
2103 NULL,
2104 NULL,
2105 NULL
2106 );
2107 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(k).CONTACT_XID := l_contact_id_tbl(k);
2108 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(k).CONTACT_DN := l_domain_name;
2109 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(k).TXN_CODE := 'IU';
2110
2111 IF l_debug_on THEN
2112 --
2113 WSH_DEBUG_SV.log(l_module_name,'l_contact_ph_cntr_code_tbl('||k||')',l_contact_ph_cntr_code_tbl(k));
2114 WSH_DEBUG_SV.log(l_module_name,'l_contact_ph_area_code_tbl('||k||')',l_contact_ph_area_code_tbl(k));
2115 WSH_DEBUG_SV.log(l_module_name,'l_contact_ph_number_tbl('||k||')',l_contact_ph_number_tbl(k));
2116 WSH_DEBUG_SV.log(l_module_name,'l_contact_email_addr_tbl('||k||')',l_contact_email_addr_tbl(k));
2117 WSH_DEBUG_SV.log(l_module_name,'l_contact_first_name_tbl('||k||')',l_contact_first_name_tbl(k));
2118 WSH_DEBUG_SV.log(l_module_name,'l_contact_last_name_tbl('||k||')',l_contact_last_name_tbl(k));
2119 WSH_DEBUG_SV.log(l_module_name,'l_contact_job_title_tbl('||k||')',l_contact_job_title_tbl(k));
2120 --
2121 END IF;
2122 l_contact_ph := NULL;
2123
2124 IF (l_contact_ph_cntr_code_tbl(k) IS NOT NULL) THEN
2125 l_contact_ph := l_contact_ph_cntr_code_tbl(k) || '-';
2126 END IF;
2127 IF (l_contact_ph_area_code_tbl(k) IS NOT NULL) THEN
2128 l_contact_ph := l_contact_ph || l_contact_ph_area_code_tbl(k) || '-';
2129 END IF;
2130 IF (l_contact_ph_number_tbl(k) IS NOT NULL) THEN
2131 l_contact_ph := l_contact_ph || l_contact_ph_number_tbl(k);
2132 END IF;
2133
2134 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(k).PHONE1 := substrb(l_contact_ph,1,80);
2135 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(k).EMAIL_ADDRESS := substrb(l_contact_email_addr_tbl(k),1,60);
2136 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(k).FIRST_NAME := substrb(l_contact_first_name_tbl(k),1,20);
2137 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(k).LAST_NAME := substrb(l_contact_last_name_tbl(k),1,30);
2138 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_CONTACT_TBL(k).JOB_TITLE := substrb(l_contact_job_title_tbl(k),1,60);
2139 --}
2140 END LOOP;
2141 --}
2142 END IF;
2143 i := i + 1;
2144 --}
2145 END IF;
2146 --
2147 l_prev_customer_id := l_customer_id;
2148 --
2149 --}
2150 END LOOP;
2151
2152 i := x_loc_xmission_rec.LOCATIONS_TBL.count+1;
2153 --
2154 FOR carrier_loc_rec in l_carrier_loc_csr LOOP
2155 --{
2156
2157 l_carrier_id := carrier_loc_rec.carrier_id;
2158
2159 IF NOT (l_profile_queried) THEN
2160 --{
2161
2162 FND_PROFILE.Get('WSH_OTM_USER_ID',x_loc_xmission_rec.username);
2163 FND_PROFILE.Get('WSH_OTM_PASSWORD',x_loc_xmission_rec.password);
2164
2165 IF l_debug_on THEN
2166 WSH_DEBUG_SV.log(l_module_name,'x_loc_xmission_rec.username', x_loc_xmission_rec.username);
2167 WSH_DEBUG_SV.log(l_module_name,'x_loc_xmission_rec.password', x_loc_xmission_rec.password);
2168 END IF;
2169
2170 IF (x_loc_xmission_rec.username is null) THEN
2171 --{
2172 FND_MESSAGE.SET_NAME('WSH','WSH_PROFILE_NOT_SET_ERR');
2173 FND_MESSAGE.SET_TOKEN('PRF_NAME','WSH_OTM_USER_ID');
2174 x_return_status := wsh_util_core.G_RET_STS_UNEXP_ERROR;
2175 wsh_util_core.add_message(x_return_status, l_module_name);
2176 IF l_debug_on THEN
2177 WSH_DEBUG_SV.logmsg(l_module_name,'Error: The profile WSH_OTM_USER_ID is set to NULL. Please correct the profile value');
2178 END IF;
2179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2180 --}
2181 END IF;
2182
2183 IF (x_loc_xmission_rec.password is null) THEN
2184 --{
2185 FND_MESSAGE.SET_NAME('WSH','WSH_PROFILE_NOT_SET_ERR');
2186 FND_MESSAGE.SET_TOKEN('PRF_NAME','WSH_OTM_PASSWORD');
2187 x_return_status := wsh_util_core.G_RET_STS_UNEXP_ERROR;
2188 wsh_util_core.add_message(x_return_status, l_module_name);
2189 IF l_debug_on THEN
2190 WSH_DEBUG_SV.logmsg(l_module_name,'Error: The profile WSH_OTM_PASSWORD is set to NULL. Please correct the profile value');
2191 END IF;
2192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2193 --}
2194 END IF;
2195 --
2196 l_profile_queried := true;
2197 --}
2198 END IF;
2199 l_last_update_date := GREATEST (carrier_loc_rec.HZL_LAST_UPD_DATE, GREATEST (carrier_loc_rec.WCV_LAST_UPD_DATE, GREATEST (carrier_loc_rec.HPS_LAST_UPD_DATE, carrier_loc_rec.HP_LAST_UPD_DATE)));
2200 /*
2201 -- commented out this code because for Carriers we are not implementing the logic
2202 -- of storing already sent carriers
2203 -- Debug Statements
2204 --
2205 IF l_debug_on THEN
2206 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_OTM_SYNC_REF_DATA_PKG.IS_REF_DATA_SEND_REQD',WSH_DEBUG_SV.C_PROC_LEVEL);
2207 END IF;
2208 --
2209
2210 WSH_OTM_SYNC_REF_DATA_PKG.IS_REF_DATA_SEND_REQD
2211 (
2212 P_ENTITY_ID => carrier_loc_rec.location_id,
2213 P_PARENT_ENTITY_ID => carrier_loc_rec.carrier_id,
2214 P_ENTITY_TYPE => 'CAR_LOC',
2215 P_ENTITY_UPDATED_DATE => l_last_update_date,
2216 X_SUBSTITUTE_ENTITY => l_substitute_entity,
2217 P_TRANSMISSION_ID => P_TRANSMISSION_ID,
2218 X_SEND_ALLOWED => l_send_allowed,
2219 X_RETURN_STATUS => l_return_status
2220 );
2221
2222 --
2223 -- Debug Statements
2224 --
2225 IF l_debug_on THEN
2226 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling IS_REF_DATA_SEND_REQD is', l_return_status);
2227 WSH_DEBUG_SV.log(l_module_name,'l_send_allowed', l_send_allowed);
2228 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2229 END IF;
2230 --
2231 wsh_util_core.api_post_call(
2232 p_return_status => l_return_status,
2233 x_num_warnings => l_num_warnings,
2234 x_num_errors => l_num_errors);
2235 */
2236
2237 IF l_debug_on THEN
2238 --
2239 WSH_DEBUG_SV.log(l_module_name,'Current carrier_id',carrier_loc_rec.CARRIER_ID);
2240 WSH_DEBUG_SV.log(l_module_name,'Previous carrier_id', l_prev_carrier_id);
2241 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.CARRIER_SITE_ID',carrier_loc_rec.CARRIER_SITE_ID);
2242 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.CARRIER_NAME',carrier_loc_rec.CARRIER_NAME);
2243 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.LOCATION_NAME',carrier_loc_rec.LOCATION_NAME);
2244 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.ADDRESS1',carrier_loc_rec.ADDRESS1);
2245 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.ADDRESS2',carrier_loc_rec.ADDRESS2);
2246 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.ADDRESS3',carrier_loc_rec.ADDRESS3);
2247 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.ADDRESS4',carrier_loc_rec.ADDRESS4);
2248 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.CITY',carrier_loc_rec.CITY);
2249 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.PROVINCE',carrier_loc_rec.PROVINCE);
2250 --WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.PROVINCE_CODE',carrier_loc_rec.PROVINCE_CODE);
2251 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.POSTAL_CODE',carrier_loc_rec.POSTAL_CODE);
2252 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.COUNTRY',carrier_loc_rec.COUNTRY);
2253 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.SUPPLIER_ID',carrier_loc_rec.SUPPLIER_ID);
2254 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.SCAC_CODE',carrier_loc_rec.SCAC_CODE);
2255 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.LOCATION_ID',carrier_loc_rec.LOCATION_ID);
2256 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.WSH_LOCATION_ID',carrier_loc_rec.WSH_LOCATION_ID);
2257 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.HZL_LAST_UPD_DATE',carrier_loc_rec.HZL_LAST_UPD_DATE);
2258 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.WCV_LAST_UPD_DATE',carrier_loc_rec.WCV_LAST_UPD_DATE);
2259 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.HPS_LAST_UPD_DATE',carrier_loc_rec.HPS_LAST_UPD_DATE);
2260 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.HP_LAST_UPD_DATE',carrier_loc_rec.HP_LAST_UPD_DATE);
2261 WSH_DEBUG_SV.log(l_module_name,'carrier_loc_rec.SUPPLIER_SITE_ID',carrier_loc_rec.SUPPLIER_SITE_ID);
2262 WSH_DEBUG_SV.log(l_module_name,'l_last_update_date', l_last_update_date);
2263 --
2264 END IF;
2265
2266 IF (nvl(l_prev_carrier_id,-999) <> nvl(l_carrier_id,-998)) THEN
2267 --{
2268 -- Initially we need extend the locations table for every record.
2269 EXTEND_LOCATIONS_TBL
2270 (
2271 p_tbl_extend_index => i,
2272 x_locations_tbl => x_loc_xmission_rec.LOCATIONS_TBL
2273 );
2274
2275
2276 x_loc_xmission_rec.LOCATIONS_TBL(i).TXN_CODE := 'IU';
2277 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID := 'CAR-' || carrier_loc_rec.CARRIER_ID;
2278
2279 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID IS NOT NULL) THEN
2280 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_DN := l_domain_name;
2281 END IF;
2282
2283 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_NAME := substrb(carrier_loc_rec.CARRIER_NAME,1,120);
2284 x_loc_xmission_rec.LOCATIONS_TBL(i).CORPORATION := substrb(carrier_loc_rec.CARRIER_NAME,1,30);
2285 x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_XID := l_country_code;
2286
2287 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_XID IS NOT NULL) THEN
2288 x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_DN := l_pub_dn_name;
2289 END IF;
2290
2291 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_XID := 'CARRIER';
2292
2293 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_XID IS NOT NULL) THEN
2294 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_DN := l_pub_dn_name;
2295 END IF;
2296
2297 IF (carrier_loc_rec.SUPPLIER_ID IS NOT NULL) THEN
2298 --{
2299 x_loc_xmission_rec.LOCATIONS_TBL(i).SERVICE_PROV_TBL.extend;
2300 x_loc_xmission_rec.LOCATIONS_TBL(i).SERVICE_PROV_TBL(1) := WSH_OTM_SERVICE_PROV_REC_TYPE(NULL,NULL,NULL);
2301 x_loc_xmission_rec.LOCATIONS_TBL(i).SERVICE_PROV_TBL(1).SERVICE_PROV_QUALIFIER_XID := 'SUPPLIER_ID';
2302 x_loc_xmission_rec.LOCATIONS_TBL(i).SERVICE_PROV_TBL(1).SERVICE_PROV_QUALIFIER_DN := l_pub_dn_name;
2303 x_loc_xmission_rec.LOCATIONS_TBL(i).SERVICE_PROV_TBL(1).SERVICE_PROV_ALIAS_VALUE := 'SUP-' || carrier_loc_rec.SUPPLIER_ID;
2304 --}
2305 END IF;
2306
2307 FOR j in 1..3 LOOP
2308 --{
2309 l_ref_num_dn_name := l_pub_dn_name;
2310 l_ref_num_value := NULL;
2311 l_ref_num_qual := NULL;
2312 IF ( j = 1 ) THEN
2313 l_ref_num_qual := 'ORIGIN';
2314 l_ref_num_value := 'CARRIER';
2315 ELSIF (j = 2) THEN
2316 l_ref_num_qual := 'CARID';
2317 l_ref_num_value := substrb(carrier_loc_rec.CARRIER_ID,1,101);
2318 ELSIF (j=3 AND carrier_loc_rec.SCAC_CODE IS NOT NULL) THEN
2319 l_ref_num_qual := 'CARNM';
2320 l_ref_num_value := substrb(carrier_loc_rec.SCAC_CODE,1,101);
2321 END IF;
2322 --
2323 EXTND_ASSIGN_LOC_REF_NUM_TBL
2324 (
2325 p_domain_name => l_ref_num_dn_name,
2326 p_qualifier => l_ref_num_qual,
2327 p_value => l_ref_num_value,
2328 x_ref_num_tbl => x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL
2329 );
2330 --
2331 --}
2332 END LOOP;
2333 -- Bug#7218387: Needs to pass supplier_id and supplier_site_id at carrier level
2334 -- as a reference numbers to OTM.
2335 IF (carrier_loc_rec.SUPPLIER_ID IS NOT NULL) THEN
2336 --{
2337 --
2338 --
2339 EXTND_ASSIGN_LOC_REF_NUM_TBL(
2340 p_domain_name => l_pub_dn_name,
2341 p_qualifier => 'SUPPLIER_ID',
2342 p_value => 'SUP-' || carrier_loc_rec.SUPPLIER_ID,
2343 x_ref_num_tbl => x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL
2344 );
2345 --
2346 --
2347 --}
2348 END IF;
2349 IF (carrier_loc_rec.car_supplier_site_id IS NOT NULL) THEN
2350 --{
2351 IF (carrier_loc_rec.SUPPLIER_ID IS NOT NULL) THEN
2352 l_supplier_site_ref_value := 'SUP-'||carrier_loc_rec.SUPPLIER_ID || '-' ||carrier_loc_rec.CAR_SUPPLIER_SITE_ID;
2353 ELSE
2354 l_supplier_site_ref_value := 'SUP-000-'||carrier_loc_rec.CAR_SUPPLIER_SITE_ID;
2355 END IF;
2356 --
2357 EXTND_ASSIGN_LOC_REF_NUM_TBL(
2358 p_domain_name => l_pub_dn_name,
2359 p_qualifier => 'SUPPLIER_SITE_ID',
2360 p_value => l_supplier_site_ref_value,
2361 x_ref_num_tbl => x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL
2362 );
2363 --
2364 --
2365 -- Bug# 7274527: Needs to pass operating Unit Id associated to supplier site
2366 -- to OTM as carrier service provider reference number.
2367 OPEN c_get_ou(carrier_loc_rec.CAR_SUPPLIER_SITE_ID);
2368 FETCH c_get_ou INTO l_opertaing_unit_id;
2369 CLOSE c_get_ou;
2370 IF (l_opertaing_unit_id IS NOT NULL) THEN
2371 EXTND_ASSIGN_LOC_REF_NUM_TBL(
2372 p_domain_name => l_pub_dn_name,
2373 p_qualifier => 'ORGID',
2374 p_value => l_opertaing_unit_id,
2375 x_ref_num_tbl => x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL
2376 );
2377 END IF;
2378 -- Bug# 7274527: end
2379 --}
2380 END IF;
2381 -- Bug#7218387: End
2382
2383 i := i + 1;
2384 --}
2385 END IF;
2386
2387 -- We need extend the locations table for every record.
2388 EXTEND_LOCATIONS_TBL
2389 (
2390 p_tbl_extend_index => i,
2391 x_locations_tbl => x_loc_xmission_rec.LOCATIONS_TBL
2392 );
2393
2394
2395 x_loc_xmission_rec.LOCATIONS_TBL(i).TXN_CODE := 'IU';
2396 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID := 'CAR-' || carrier_loc_rec.CARRIER_ID || '-'|| carrier_loc_rec.LOCATION_ID;
2397 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_XID IS NOT NULL) THEN
2398 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_DN := l_domain_name;
2399 END IF;
2400 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_NAME := substrb(carrier_loc_rec.LOCATION_NAME,1,120);
2401 x_loc_xmission_rec.LOCATIONS_TBL(i).CITY := substrb(carrier_loc_rec.CITY,1,30);
2402 x_loc_xmission_rec.LOCATIONS_TBL(i).PROVINCE_CODE := GET_STATE_CODE(carrier_loc_rec.WSH_LOCATION_ID,carrier_loc_rec.province);
2403 -- eco 5192928
2404 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).PROVINCE_CODE IS NULL)
2405 THEN
2406 --{
2407 x_loc_xmission_rec.LOCATIONS_TBL(i).PROVINCE := substrb(carrier_loc_rec.province,1,30);
2408 --}
2409 END IF;
2410 -- eco 5192928
2411 x_loc_xmission_rec.LOCATIONS_TBL(i).POSTAL_CODE := substrb(carrier_loc_rec.POSTAL_CODE,1,15);
2412 x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_XID := substrb(carrier_loc_rec.COUNTRY,1,3);
2413 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_XID IS NOT NULL) THEN
2414 x_loc_xmission_rec.LOCATIONS_TBL(i).COUNTRY_CODE_DN := l_pub_dn_name;
2415 END IF;
2416 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_XID := l_dispatch_loc; -- eco 5381528
2417 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_XID IS NOT NULL) THEN
2418 x_loc_xmission_rec.LOCATIONS_TBL(i).LOCATION_ROLE_DN := l_pub_dn_name;
2419 END IF;
2420 x_loc_xmission_rec.LOCATIONS_TBL(i).PARENT_LOCATION_XID := 'CAR-' || carrier_loc_rec.CARRIER_ID;
2421 IF (x_loc_xmission_rec.LOCATIONS_TBL(i).PARENT_LOCATION_XID IS NOT NULL) THEN
2422 x_loc_xmission_rec.LOCATIONS_TBL(i).PARENT_LOCATION_DN := l_domain_name;
2423 END IF;
2424 --
2425 --
2426 FOR j in 1..4 LOOP
2427 --{
2428 l_ref_num_dn_name := l_pub_dn_name;
2429 l_ref_num_value := NULL;
2430 l_ref_num_qual := NULL;
2431
2432 IF ( j = 1 ) THEN
2433 l_ref_num_qual := 'ORIGIN';
2434 l_ref_num_value := 'CARRIER';
2435 ELSIF (j = 2) THEN
2436 l_ref_num_qual := 'CARID';
2437 l_ref_num_value := substrb(carrier_loc_rec.CARRIER_ID,1,101);
2438 ELSIF (j=3 AND carrier_loc_rec.SCAC_CODE IS NOT NULL) THEN
2439 l_ref_num_qual := 'CARNM';
2440 l_ref_num_value := substrb(carrier_loc_rec.SCAC_CODE,1,101);
2441 ELSIF (j=4) THEN
2442 l_ref_num_qual := 'LOCID';
2443 l_ref_num_value := substrb(carrier_loc_rec.CARRIER_SITE_NUMBER,1,101);
2444 END IF;
2445 --
2446 EXTND_ASSIGN_LOC_REF_NUM_TBL
2447 (
2448 p_domain_name => l_ref_num_dn_name,
2449 p_qualifier => l_ref_num_qual,
2450 p_value => l_ref_num_value,
2451 x_ref_num_tbl => x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL
2452 );
2453 --
2454 --}
2455 END LOOP;
2456 --
2457
2458 -- eco 5381528
2459 IF (carrier_loc_rec.SUPPLIER_ID IS NOT NULL) THEN
2460 --{
2461 --
2462 --
2463 EXTND_ASSIGN_LOC_REF_NUM_TBL
2464 (
2465 p_domain_name => l_pub_dn_name,
2466 p_qualifier => 'SUPPLIER_ID',
2467 p_value => 'SUP-' || carrier_loc_rec.SUPPLIER_ID,
2468 x_ref_num_tbl => x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL
2469 );
2470 --
2471 --
2472 --}
2473 END IF;
2474
2475 IF (carrier_loc_rec.SUPPLIER_SITE_ID IS NOT NULL) THEN
2476 --{
2477
2478 IF (carrier_loc_rec.SUPPLIER_ID IS NOT NULL) THEN
2479 l_supplier_site_ref_value := 'SUP-'||carrier_loc_rec.SUPPLIER_ID || '-' ||carrier_loc_rec.SUPPLIER_SITE_ID;
2480 ELSE
2481 l_supplier_site_ref_value := 'SUP-000-'||carrier_loc_rec.SUPPLIER_SITE_ID;
2482 END IF;
2483 --
2484 EXTND_ASSIGN_LOC_REF_NUM_TBL
2485 (
2486 p_domain_name => l_pub_dn_name,
2487 p_qualifier => 'SUPPLIER_SITE_ID',
2488 p_value => l_supplier_site_ref_value,
2489 x_ref_num_tbl => x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL
2490 );
2491 --
2492 -- Bug# 7274527: Needs to pass operating Unit Id associated to supplier site
2493 -- to OTM as carrier service provider reference number.
2494 OPEN c_get_ou(carrier_loc_rec.SUPPLIER_SITE_ID);
2495 FETCH c_get_ou INTO l_opertaing_unit_id;
2496 CLOSE c_get_ou;
2497 IF (l_opertaing_unit_id IS NOT NULL) THEN
2498 EXTND_ASSIGN_LOC_REF_NUM_TBL(
2499 p_domain_name => l_pub_dn_name,
2500 p_qualifier => 'ORGID',
2501 p_value => l_opertaing_unit_id,
2502 x_ref_num_tbl => x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_REF_NUM_TBL
2503 );
2504 END IF;
2505 -- Bug# 7274527: end
2506 --}
2507 END IF;
2508 -- eco 5381528
2509
2510 l_address_line := carrier_loc_rec.ADDRESS1 || ' ' || carrier_loc_rec.ADDRESS2 || ' ' || carrier_loc_rec.ADDRESS3 || ' ' || carrier_loc_rec.ADDRESS4;
2511
2512 j := lengthb(l_address_line);
2513 k := 1;
2514 WHILE (j > 0) LOOP
2515 --{
2516 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL.extend;
2517 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL(k) := WSH_OTM_LOC_ADDR_REC_TYPE(NULL,NULL);
2518 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL(k).SEQ_NUMBER := k;
2519 x_loc_xmission_rec.LOCATIONS_TBL(i).LOC_ADDR_TBL(k).ADRESS_LINE := substrb(l_address_line,1,55);
2520 l_address_line := substrb(l_address_line,56);
2521 j := j - 55;
2522 k := k + 1;
2523 --}
2524 END LOOP;
2525
2526 l_prev_carrier_id := l_carrier_id;
2527
2528 i := i + 1;
2529 --}
2530 END LOOP;
2531 --
2532 IF (l_num_warnings > 0 AND x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2533 --
2534 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2535 --
2536 ELSE
2537 --
2538 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2539 --
2540 END IF;
2541 --
2542 --}
2543 --
2544 -- Debug Statements
2545 --
2546 IF l_debug_on THEN
2547 WSH_DEBUG_SV.pop(l_module_name);
2548 END IF;
2549 --
2550 EXCEPTION
2551 --{
2552 WHEN OTHERS THEN
2553 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2554 --}
2555 --
2556 -- Debug Statements
2557 --
2558 IF l_debug_on THEN
2559 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2560 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2561 END IF;
2562 --
2563 END EXTRACT_LOCATION_INFO;
2564
2565 PROCEDURE SEND_LOCATIONS
2566 (
2567 p_entity_in_rec IN WSH_OTM_ENTITY_REC_TYPE,
2568 x_loc_xmission_rec OUT NOCOPY WSH_OTM_LOC_XMISSION_REC_TYPE,
2569 x_transmission_id OUT NOCOPY NUMBER,
2570 x_return_status OUT NOCOPY VARCHAR2,
2571 x_msg_data OUT NOCOPY VARCHAR2
2572 )
2573 IS
2574 --{
2575 --local variables
2576 l_return_status VARCHAR2(1);
2577 l_num_errors NUMBER := 0;
2578 l_num_warnings NUMBER := 0;
2579 l_details VARCHAR2(32767);
2580 l_summary VARCHAR2(32767);
2581 l_tkt_valid VARCHAR2(1);
2582 l_msg_count NUMBER;
2583
2584 l_transmission_id NUMBER;
2585 l_entity_rec WSH_OTM_ENTITY_REC_TYPE := WSH_OTM_ENTITY_REC_TYPE(NULL,NULL,NULL,NULL,WSH_OTM_RD_NUM_TBL_TYPE(),
2586 WSH_OTM_RD_NUM_TBL_TYPE());
2587 l_in_rec IN_REC_TYPE;
2588
2589 -- cursors
2590 --}
2591 --
2592 l_debug_on BOOLEAN;
2593 --
2594 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SEND_LOCATIONS';
2595 --
2596 BEGIN
2597 --{
2598 --
2599 --
2600 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2601 --
2602 IF l_debug_on IS NULL
2603 THEN
2604 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2605 END IF;
2606 --
2607 --
2608 -- Debug Statements
2609 --
2610 IF l_debug_on THEN
2611 WSH_DEBUG_SV.push(l_module_name);
2612 WSH_DEBUG_SV.log(l_module_name,'entity type is ', p_entity_in_rec.entity_type);
2613 END IF;
2614 --
2615 x_return_status := wsh_util_core.g_ret_sts_success;
2616
2617 select WSH_OTM_SYNC_REF_DATA_LOG_S.nextval into l_transmission_id from dual;
2618 --
2619 IF (p_entity_in_rec.entity_type = 'TRIP') THEN
2620 --{
2621 EXTRACT_TRIP_INFO
2622 (
2623 p_entity_in_rec => p_entity_in_rec,
2624 p_transmission_id => l_transmission_id,
2625 x_return_status => l_return_status
2626 );
2627
2628 --
2629 -- Debug Statements
2630 --
2631 IF l_debug_on THEN
2632 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2633 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling EXTRACT_TRIP_INFO is', l_return_status);
2634 END IF;
2635 --
2636 wsh_util_core.api_post_call(
2637 p_return_status => l_return_status,
2638 x_num_warnings => l_num_warnings,
2639 x_num_errors => l_num_errors);
2640
2641 --}
2642 ELSIF (p_entity_in_rec.entity_type = 'DELIVERY') THEN
2643 --{
2644
2645 EXTRACT_DLVY_INFO
2646 (
2647 p_entity_in_rec => p_entity_in_rec,
2648 p_transmission_id => l_transmission_id,
2649 x_return_status => l_return_status
2650 );
2651
2652 --
2653 -- Debug Statements
2654 --
2655 IF l_debug_on THEN
2656 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2657 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling EXTRACT_TRIP_INFO is', l_return_status);
2658 END IF;
2659 --
2660 wsh_util_core.api_post_call(
2661 p_return_status => l_return_status,
2662 x_num_warnings => l_num_warnings,
2663 x_num_errors => l_num_errors);
2664
2665 --}
2666 ELSIF (p_entity_in_rec.entity_type = 'CARRIER') THEN
2667 --{
2668 VALIDATE_TKT
2669 (
2670 p_operation => p_entity_in_rec.operation,
2671 p_argument => p_entity_in_rec.argument,
2672 p_ticket => p_entity_in_rec.ticket,
2673 x_tkt_valid => l_tkt_valid,
2674 x_return_status => l_return_status,
2675 x_msg_data => x_msg_data
2676 );
2677
2678 --
2679 -- Debug Statements
2680 --
2681 IF l_debug_on THEN
2682 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling VALIDATE_TKT is', l_return_status);
2683 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2684 END IF;
2685 --
2686 wsh_util_core.api_post_call(
2687 p_return_status => l_return_status,
2688 x_num_warnings => l_num_warnings,
2689 x_num_errors => l_num_errors);
2690
2691
2692 EXTRACT_CARRIER_INFO
2693 (
2694 p_entity_in_rec => p_entity_in_rec,
2695 p_transmission_id => l_transmission_id,
2696 x_return_status => l_return_status
2697 );
2698
2699 --
2700 -- Debug Statements
2701 --
2702 IF l_debug_on THEN
2703 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2704 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling EXTRACT_CARRIER_INFO is', l_return_status);
2705 END IF;
2706 --
2707 wsh_util_core.api_post_call(
2708 p_return_status => l_return_status,
2709 x_num_warnings => l_num_warnings,
2710 x_num_errors => l_num_errors);
2711
2712 --}
2713 -- wms-otm , for creating Locations for the Orgs. involved in WMS DockDoor syncronization
2714 ELSIF (p_entity_in_rec.entity_type = 'ORG_LOC') THEN
2715 --{
2716 VALIDATE_TKT
2717 (
2718 p_operation => p_entity_in_rec.operation,
2719 p_argument => p_entity_in_rec.argument,
2720 p_ticket => p_entity_in_rec.ticket,
2721 x_tkt_valid => l_tkt_valid,
2722 x_return_status => l_return_status,
2723 x_msg_data => x_msg_data
2724 );
2725
2726 --
2727 -- Debug Statements
2728 --
2729 IF l_debug_on THEN
2730 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling VALIDATE_TKT is', l_return_status);
2731 WSH_DEBUG_SV.logmsg(l_module_name,'calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2732 END IF;
2733 --
2734 wsh_util_core.api_post_call(
2735 p_return_status => l_return_status,
2736 x_num_warnings => l_num_warnings,
2737 x_num_errors => l_num_errors);
2738
2739 --wms-otm-proj New Procedure to Extract Locations for the Org
2740 EXTRACT_ORG_INFO
2741 (
2742 p_entity_in_rec => p_entity_in_rec,
2743 p_transmission_id => l_transmission_id,
2744 x_return_status => l_return_status
2745 );
2746
2747 --
2748 -- Debug Statements
2749 --
2750 IF l_debug_on THEN
2751 WSH_DEBUG_SV.logmsg(l_module_name,'OrgAfterCalling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2752 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling EXTRACT_ORG_INFO is', l_return_status);
2753 END IF;
2754 --
2755 wsh_util_core.api_post_call(
2756 p_return_status => l_return_status,
2757 x_num_warnings => l_num_warnings,
2758 x_num_errors => l_num_errors);
2759
2760 --} -- wms-otm for ORG_LOC
2761 ELSE
2762 --{
2763 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_INVALID_ENTITY');
2764 FND_MESSAGE.SET_TOKEN('ENTITY',p_entity_in_rec.entity_type);
2765 x_return_status := wsh_util_core.G_RET_STS_UNEXP_ERROR;
2766 wsh_util_core.add_message(x_return_status, l_module_name);
2767 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2768 --}
2769 END IF;
2770
2771 -- wms-otm-proj : Added p_entity_type to Distinguish ORG_LOC separately
2772 -- since ORG_LOC would require a to send Location XML everytime Unlike Other Entity Types
2773 EXTRACT_LOCATION_INFO
2774 (
2775 p_in_rec => l_in_rec,
2776 p_transmission_id => l_transmission_id,
2777 p_entity_type => p_entity_in_rec.entity_type,
2778 x_loc_xmission_rec => x_loc_xmission_rec,
2779 x_return_status => l_return_status
2780 );
2781
2782 --
2783 -- Debug Statements
2784 --
2785 IF l_debug_on THEN
2786 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling EXTRACT_LOCATION_INFO is', l_return_status);
2787 WSH_DEBUG_SV.log(l_module_name,'Number of records finally being sent to GC3 is',x_loc_xmission_rec.LOCATIONS_TBL.count);
2788 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2789 END IF;
2790 --
2791 wsh_util_core.api_post_call(
2792 p_return_status => l_return_status,
2793 x_num_warnings => l_num_warnings,
2794 x_num_errors => l_num_errors);
2795
2796 WSH_UTIL_CORE.Get_Messages
2797 (
2798 p_init_msg_list => 'T',
2799 x_summary => l_summary,
2800 x_details => l_details,
2801 x_count => l_msg_count
2802 );
2803
2804 x_msg_data := l_summary || ' ' || l_details;
2805
2806 IF (x_loc_xmission_rec.LOCATIONS_TBL.count >0) THEN
2807 x_transmission_id := l_transmission_id;
2808 END IF;
2809
2810
2811 IF (l_num_warnings > 0 AND x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2812 --
2813 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2814 --
2815 ELSE
2816 --
2817 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2818 --
2819 END IF;
2820
2821 --
2822 -- Debug Statements
2823 --
2824 IF l_debug_on THEN
2825 WSH_DEBUG_SV.pop(l_module_name);
2826 END IF;
2827 --
2828 --}
2829 EXCEPTION
2830 --{
2831 WHEN FND_API.G_EXC_ERROR THEN
2832 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2833 --
2834 -- Debug Statements
2835 --
2836 WSH_UTIL_CORE.Get_Messages
2837 (
2838 p_init_msg_list => 'T',
2839 x_summary => l_summary,
2840 x_details => l_details,
2841 x_count => l_msg_count
2842 );
2843
2844 x_msg_data := x_msg_data || l_summary || ' ' || l_details;
2845
2846 IF l_debug_on THEN
2847 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2848 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2849 END IF;
2850 --
2851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2852 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2853 --
2854 WSH_UTIL_CORE.Get_Messages
2855 (
2856 p_init_msg_list => 'T',
2857 x_summary => l_summary,
2858 x_details => l_details,
2859 x_count => l_msg_count
2860 );
2861
2862 x_msg_data := l_summary || ' ' || l_details;
2863
2864 --
2865 -- Debug Statements
2866 --
2867 IF l_debug_on THEN
2868 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2869 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2870 END IF;
2871 --
2872 WHEN OTHERS THEN
2873 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2874 --
2875 WSH_UTIL_CORE.Get_Messages
2876 (
2877 p_init_msg_list => 'T',
2878 x_summary => l_summary,
2879 x_details => l_details,
2880 x_count => l_msg_count
2881 );
2882
2883 x_msg_data := l_summary || ' ' || l_details || SQLERRM;
2884
2885 --
2886 -- Debug Statements
2887 --
2888 IF l_debug_on THEN
2889 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2890 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2891 END IF;
2892 --
2893 --}
2894 END SEND_LOCATIONS;
2895
2896 function GET_STOP_LOCATION_XID
2897 (
2898 p_stop_id IN NUMBER
2899 ) RETURN VARCHAR2
2900 IS
2901 cursor l_get_stop_loc_csr is
2902 SELECT WL.LOCATION_SOURCE_CODE LOC_TYPE,
2903 WL.SOURCE_LOCATION_ID LOCATION_ID
2904 FROM WSH_TRIP_STOPS WTS,
2905 WSH_LOCATIONS WL
2906 WHERE WTS.STOP_ID = p_stop_id
2907 AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID);
2908 --AND WTS.TMS_INTERFACE_FLAG ='ASP';
2909
2910 l_corporation_id NUMBER;
2911 l_location_id NUMBER;
2912 l_location_xid VARCHAR2(100);
2913 l_corp_type VARCHAR2(100);
2914
2915 --
2916 l_debug_on BOOLEAN;
2917 --
2918 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_STOP_LOCATION_XID';
2919 --
2920 BEGIN
2921 --{
2922 --
2923 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2924 --
2925 IF l_debug_on IS NULL
2926 THEN
2927 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2928 END IF;
2929 --
2930 --
2931 -- Debug Statements
2932 --
2933 IF l_debug_on THEN
2934 WSH_DEBUG_SV.push(l_module_name);
2935 --
2936 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
2937 END IF;
2938 --
2939 FOR l_stop_loc_rec in l_get_stop_loc_csr LOOP
2940 --{
2941 l_corporation_id := get_stop_corp_id(p_stop_id,l_stop_loc_rec.loc_type);
2942 l_location_id := l_stop_loc_rec.location_id;
2943 IF (l_stop_loc_rec.loc_type = 'HR') THEN
2944 l_corp_type := 'ORG';
2945 ELSE
2946 l_corp_type := 'CUS';
2947 END IF;
2948 IF l_debug_on THEN
2949 WSH_DEBUG_SV.log(l_module_name,'location type',l_stop_loc_rec.loc_type);
2950 WSH_DEBUG_SV.log(l_module_name,'location id',l_stop_loc_rec.location_id);
2951 WSH_DEBUG_SV.log(l_module_name,'corporation id',l_corporation_id);
2952 END IF;
2953 --}
2954 END LOOP;
2955
2956 IF (l_corporation_id is null) THEN
2957 l_location_xid := l_corp_type || '-000-' || l_location_id;
2958 ELSE
2959 l_location_xid := l_corp_type || '-' ||l_corporation_id||'-' || l_location_id;
2960 END IF;
2961 --
2962 -- Debug Statements
2963 --
2964 IF l_debug_on THEN
2965 WSH_DEBUG_SV.pop(l_module_name);
2966 --
2967 END IF;
2968 return l_location_xid;
2969 --
2970
2971 --}
2972 END GET_STOP_LOCATION_XID;
2973
2974 PROCEDURE VALIDATE_TKT
2975 (
2976 p_operation IN VARCHAR2,
2977 p_argument IN VARCHAR2,
2978 p_ticket IN VARCHAR2,
2979 x_tkt_valid OUT NOCOPY VARCHAR2,
2980 x_return_status OUT NOCOPY VARCHAR2,
2981 x_msg_data OUT NOCOPY VARCHAR2
2982 )
2983 IS
2984 --{
2985 -- Variables
2986 l_ticket VARCHAR2(500);
2987 l_end_date DATE;
2988
2989 l_is_tkt_valid boolean := false;
2990 l_operation FND_HTTP_TICKETS.OPERATION%TYPE;
2991 l_argument FND_HTTP_TICKETS.ARGUMENT%TYPE;
2992 --
2993 l_debug_on BOOLEAN;
2994 --
2995 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_TKT';
2996 --
2997 --}
2998 BEGIN
2999 --{
3000 --
3001 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3002 --
3003 IF l_debug_on IS NULL
3004 THEN
3005 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3006 END IF;
3007 --
3008 --
3009 -- Debug Statements
3010 --
3011 IF l_debug_on THEN
3012 WSH_DEBUG_SV.push(l_module_name);
3013 --
3014 WSH_DEBUG_SV.log(l_module_name,'p_operation',p_operation);
3015 WSH_DEBUG_SV.log(l_module_name,'p_argument',p_argument);
3016 WSH_DEBUG_SV.log(l_module_name,'p_ticket',p_ticket);
3017 END IF;
3018 --
3019 x_tkt_valid := 'F';
3020 x_return_status := wsh_util_core.G_RET_STS_SUCCESS;
3021
3022 IF l_debug_on THEN
3023 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FND_HTTP_TICKET.CHECK_TICKET',WSH_DEBUG_SV.C_PROC_LEVEL);
3024 END IF;
3025
3026 l_is_tkt_valid := FND_HTTP_TICKET.CHECK_TICKET
3027 (
3028 p_ticket => p_ticket,
3029 p_operation => l_operation,
3030 p_argument => l_argument
3031 );
3032
3033 IF l_debug_on THEN
3034 WSH_DEBUG_SV.log(l_module_name,'l_operation',l_operation);
3035 WSH_DEBUG_SV.log(l_module_name,'l_argument',l_argument);
3036 END IF;
3037 IF (
3038 l_is_tkt_valid
3039 and l_operation = p_operation
3040 and l_argument = p_argument
3041 ) THEN
3042 --{
3043 IF l_debug_on THEN
3044 WSH_DEBUG_SV.logmsg(l_module_name,'Tkt is valid');
3045 END IF;
3046 x_tkt_valid := 'T';
3047 --}
3048 ELSE
3049 --{
3050 IF l_debug_on THEN
3051 WSH_DEBUG_SV.logmsg(l_module_name,'Tkt is invalid');
3052 END IF;
3053 x_tkt_valid := 'F';
3054 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_INVALID_TKT');
3055 FND_MESSAGE.SET_TOKEN('TICKET',p_ticket);
3056 x_return_status := wsh_util_core.G_RET_STS_ERROR;
3057 x_msg_data :=FND_MESSAGE.GET;
3058 --}
3059 END IF;
3060 --
3061 -- Debug Statements
3062 --
3063 IF l_debug_on THEN
3064 WSH_DEBUG_SV.pop(l_module_name);
3065 --
3066 END IF;
3067 --}
3068 EXCEPTION
3069 --{
3070 WHEN OTHERS THEN
3071 --
3072 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3073 x_tkt_valid := 'F';
3074 x_msg_data := SQLERRM;
3075
3076 --
3077 -- Debug Statements
3078 --
3079 IF l_debug_on THEN
3080 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3081 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3082 END IF;
3083 --
3084 --}
3085 END VALIDATE_TKT;
3086
3087 procedure GET_INT_LOCATION_XID
3088 (
3089 p_location_id IN NUMBER,
3090 x_location_xid OUT NOCOPY VARCHAR2,
3091 x_return_status OUT NOCOPY VARCHAR2
3092 )
3093 IS
3094 l_corporation_id NUMBER;
3095 l_int_location_id NUMBER;
3096 l_location_xid VARCHAR2(100);
3097 l_corp_type VARCHAR2(100) := 'ORG';
3098 l_return_status VARCHAR2(10);
3099 l_num_errors NUMBER;
3100 l_num_warnings NUMBER;
3101
3102 --bug 6770323: modified cursor to join with hr_locations_all table
3103 cursor l_loc_to_org_csr (p_loc_id NUMBER) IS
3104 SELECT inventory_organization_id
3105 FROM hr_locations_all
3106 WHERE location_id = p_loc_id;
3107
3108 --
3109 l_debug_on BOOLEAN;
3110 --
3111 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_INT_LOCATION_XID';
3112 --
3113 BEGIN
3114 --{
3115 --
3116 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3117 --
3118 IF l_debug_on IS NULL
3119 THEN
3120 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3121 END IF;
3122 --
3123 --
3124 -- Debug Statements
3125 --
3126 IF l_debug_on THEN
3127 WSH_DEBUG_SV.push(l_module_name);
3128 --
3129 WSH_DEBUG_SV.log(l_module_name,'p_location_id',p_location_id);
3130 END IF;
3131 --
3132 x_return_status := wsh_util_core.g_ret_sts_success;
3133 --
3134 WSH_LOCATIONS_PKG.Convert_internal_cust_location(
3135 p_internal_cust_location_id => p_location_id,
3136 x_internal_org_location_id => l_int_location_id,
3137 x_return_status =>l_return_status);
3138 --
3139 -- Debug Statements
3140 --
3141 IF l_debug_on THEN
3142 WSH_DEBUG_SV.log(l_module_name,'l_return_status after calling Convert_internal_cust_location',l_return_status);
3143 WSH_DEBUG_SV.log(l_module_name,'l_int_location_id',l_int_location_id);
3144 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3145 END IF;
3146 --
3147 wsh_util_core.api_post_call(
3148 p_return_status => l_return_status,
3149 x_num_warnings => l_num_warnings,
3150 x_num_errors => l_num_errors);
3151
3152 IF (l_int_location_id IS NOT NULL) THEN
3153 --{
3154 --
3155 open l_loc_to_org_csr(l_int_location_id);
3156 fetch l_loc_to_org_csr into l_corporation_id;
3157 close l_loc_to_org_csr;
3158 --
3159 IF l_debug_on THEN
3160 WSH_DEBUG_SV.log(l_module_name,'l_corporation_id',l_corporation_id);
3161 END IF;
3162 --
3163 IF (l_corporation_id is null) THEN
3164 l_location_xid := l_corp_type || '-000-' || l_int_location_id;
3165 ELSE
3166 l_location_xid := l_corp_type || '-' ||l_corporation_id||'-' || l_int_location_id;
3167 END IF;
3168 --
3169 x_location_xid := l_location_xid;
3170 --}
3171 END IF;
3172 --
3173 -- Debug Statements
3174 --
3175 IF l_debug_on THEN
3176 WSH_DEBUG_SV.log(l_module_name,'x_location_xid',x_location_xid);
3177 WSH_DEBUG_SV.pop(l_module_name);
3178 --
3179 END IF;
3180 --
3181 --}
3182 EXCEPTION
3183 --{
3184 WHEN FND_API.G_EXC_ERROR THEN
3185 --
3186 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3187 --
3188
3189 IF l_debug_on THEN
3190 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3191 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3192 END IF;
3193 --
3194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3195 --
3196 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3197 --
3198 -- Debug Statements
3199 --
3200 IF l_debug_on THEN
3201 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3202 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3203 END IF;
3204 --
3205 WHEN OTHERS THEN
3206 --
3207 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3208 --
3209 -- Debug Statements
3210 --
3211 IF l_debug_on THEN
3212 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3213 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3214 END IF;
3215 --
3216 --}
3217 END GET_INT_LOCATION_XID;
3218
3219
3220 END WSH_OTM_REF_DATA_GEN_PKG;