[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'
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
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: --
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);
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
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: --{
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;
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
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
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'
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:
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: --