[Home] [Help]
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: --}
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,
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
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: );
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:
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
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,
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
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:
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
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: )
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:
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'
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'
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,