[Home] [Help]
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
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'
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,
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'
310: WDD.CUSTOMER_ID CUSTOMER_ID
311: FROM WSH_DELIVERY_DETAILS WDD,
312: WSH_DELIVERY_ASSIGNMENTS WDA,
313: HZ_PARTY_SITES HPS,
314: WSH_LOCATIONS WSL,
315: HZ_CUST_ACCT_SITES_ALL HCAS,
316: HZ_CUST_ACCOUNT_ROLES HCAR
317: WHERE WDA.delivery_id = p_delivery_id
318: AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
390: l_internal_loc_id := NULL;
391: l_internal_org_id := NULL;
392: FOR l_del_loc_info_rec in l_get_del_loc_info_csr(p_entity_in_rec.entity_id_tbl(i)) LOOP
393: --{
394: WSH_LOCATIONS_PKG.Convert_internal_cust_location(
395: p_internal_cust_location_id => l_del_loc_info_rec.SHIP_TO_LOCATION_ID,
396: x_internal_org_location_id => l_internal_loc_id,
397: x_return_status => l_return_status);
398: --
647: WTS.STOP_LOCATION_ID LOCATION_ID,
648: WTS.STOP_ID
649: FROM WSH_TRIP_STOPS WTS,
650: WSH_OTM_LOCATIONS_GTMP WLT,
651: WSH_LOCATIONS WL
652: WHERE WTS.TRIP_ID = WLT.LOCATION_ID
653: AND WLT.LOCATION_TYPE = 'TRIP'
654: --bug 6770323: Modified AND condition
655: AND WL.WSH_LOCATION_ID = NVL(WTS.PHYSICAL_LOCATION_ID, WTS.STOP_LOCATION_ID);
911:
912: -- bug 5118375
913: -- For every Carrier Site created,
914: -- we need to check if the location is created in
915: -- wsh_locations or not. This is required
916: -- the location may or may not be inserted into into wsh_locations
917: -- by the time this conc. program is run.
918: l_location_id := null;
919: FOR i in p_entity_in_rec.entity_id_tbl.first..p_entity_in_rec.entity_id_tbl.last LOOP
912: -- bug 5118375
913: -- For every Carrier Site created,
914: -- we need to check if the location is created in
915: -- wsh_locations or not. This is required
916: -- the location may or may not be inserted into into wsh_locations
917: -- by the time this conc. program is run.
918: l_location_id := null;
919: FOR i in p_entity_in_rec.entity_id_tbl.first..p_entity_in_rec.entity_id_tbl.last LOOP
920: --{
1127: cursor l_get_state_code_csr(p_location_id IN NUMBER) is
1128: select wr.state_code
1129: from wsh_regions wr,
1130: wsh_region_locations wrl,
1131: wsh_locations wl
1132: where wl.wsh_location_id = p_location_id
1133: and wl.wsh_location_id = wrl.location_id
1134: and wrl.region_type = 1
1135: and wrl.region_id = wr.region_id;
1410: --FND_LANGUAGES FNDL,
1411: FND_TERRITORIES FNDTR,
1412: WSH_OTM_LOCATIONS_GTMP WLT, -- global temp table
1413: MTL_PARAMETERS MP,
1414: WSH_LOCATIONS WL
1415: WHERE WLT.LOCATION_ID = WL.WSH_LOCATION_ID
1416: AND WLT.LOCATION_TYPE = 'ORG_LOC'
1417: AND WL.LOCATION_SOURCE_CODE = 'HR'
1418: AND WL.SOURCE_LOCATION_ID = HRL.LOCATION_ID
1444: to_date('1900/01/01 00:00:01', 'YYYY/MM/DD HH24:MI:SS')
1445: )
1446: ) LAST_UPDATE_DATE
1447: FROM HZ_LOCATIONS HZL,
1448: WSH_LOCATIONS WL,
1449: FND_TERRITORIES FNDTR,
1450: WSH_OTM_LOCATIONS_GTMP WLT,
1451: HZ_PARTIES HP,
1452: HZ_CUST_ACCOUNTS HCA
1541: WSH_CARRIERS WCV,
1542: WSH_CARRIER_SITES WCS,
1543: HZ_PARTY_SITES HPS,
1544: HZ_PARTIES HP,
1545: WSH_LOCATIONS WSL
1546: WHERE WCV.CARRIER_ID = HPS.PARTY_ID
1547: AND HPS.LOCATION_ID = HZL.LOCATION_ID
1548: AND WSL.SOURCE_LOCATION_ID = HZL.LOCATION_ID
1549: AND WSL.LOCATION_SOURCE_CODE = 'HZ'
2901: cursor l_get_stop_loc_csr is
2902: SELECT WL.LOCATION_SOURCE_CODE LOC_TYPE,
2903: WL.SOURCE_LOCATION_ID LOCATION_ID
2904: FROM WSH_TRIP_STOPS WTS,
2905: WSH_LOCATIONS WL
2906: WHERE WTS.STOP_ID = p_stop_id
2907: AND WL.WSH_LOCATION_ID = nvl(WTS.PHYSICAL_LOCATION_ID,WTS.STOP_LOCATION_ID);
2908: --AND WTS.TMS_INTERFACE_FLAG ='ASP';
2909:
3130: END IF;
3131: --
3132: x_return_status := wsh_util_core.g_ret_sts_success;
3133: --
3134: WSH_LOCATIONS_PKG.Convert_internal_cust_location(
3135: p_internal_cust_location_id => p_location_id,
3136: x_internal_org_location_id => l_int_location_id,
3137: x_return_status =>l_return_status);
3138: --