[Home] [Help]
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'
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
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
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
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'
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