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