[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: --}
264: WLT1.CORPORATION_ID WLT1_CUST_ID,
265: WLT2.LOCATION_ID WLT2_SF_LOC_ID,
266: WLT2.CORPORATION_ID WLT2_ORG_ID
267: FROM WSH_NEW_DELIVERIES WND,
268: WSH_OTM_LOCATIONS_GTMP WLT1,
269: WSH_OTM_LOCATIONS_GTMP WLT2
270: WHERE WND.DELIVERY_ID = p_delivery_id
271: AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WLT1.LOCATION_ID (+)
272: AND WND.CUSTOMER_ID = WLT1.CORPORATION_ID (+)
265: WLT2.LOCATION_ID WLT2_SF_LOC_ID,
266: WLT2.CORPORATION_ID WLT2_ORG_ID
267: FROM WSH_NEW_DELIVERIES WND,
268: WSH_OTM_LOCATIONS_GTMP WLT1,
269: WSH_OTM_LOCATIONS_GTMP WLT2
270: WHERE WND.DELIVERY_ID = p_delivery_id
271: AND WND.ULTIMATE_DROPOFF_LOCATION_ID = WLT1.LOCATION_ID (+)
272: AND WND.CUSTOMER_ID = WLT1.CORPORATION_ID (+)
273: AND WND.INITIAL_PICKUP_LOCATION_ID = WLT2.LOCATION_ID (+)
284: AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
285: AND WDD.container_flag = 'N'
286: AND NOT EXISTS (
287: SELECT 'X'
288: FROM WSH_OTM_LOCATIONS_GTMP
289: WHERE LOCATION_TYPE = 'CUST_LOC'
290: AND LOCATION_ID = WDD.SHIP_TO_LOCATION_ID
291: AND CORPORATION_ID = WDD.CUSTOMER_ID
292: );
342: cursor l_check_loc_exists_csr (p_location_id IN NUMBER,
343: p_corporation_id IN NUMBER
344: ) is
345: SELECT 'X'
346: FROM WSH_OTM_LOCATIONS_GTMP
347: WHERE LOCATION_ID = p_location_id
348: AND (p_corporation_id is NULL OR CORPORATION_ID = p_corporation_id)
349: AND LOCATION_TYPE = 'ORG_LOC';
350:
631: cursor l_get_del_csr is
632: SELECT DISTINCT WDL.DELIVERY_ID
633: FROM WSH_DELIVERY_LEGS WDL,
634: WSH_TRIP_STOPS WTS,
635: WSH_OTM_LOCATIONS_GTMP WLT
636: WHERE WTS.TRIP_ID = WLT.LOCATION_ID
637: AND WLT.LOCATION_TYPE = 'TRIP'
638: AND (WTS.STOP_ID = WDL.PICK_UP_STOP_ID
639: OR WTS.STOP_ID = WDL.DROP_OFF_STOP_ID
639: OR WTS.STOP_ID = WDL.DROP_OFF_STOP_ID
640: );
641: --AND WTS.TMS_INTERFACE_FLAG ='ASP';
642:
643: -- We cannot add the join to WSH_OTM_LOCATIONS_GTMP for 'CUST_LOC' or 'ORG_LOC'
644: -- because we don't know the corporation_id
645: cursor l_get_stops_csr is
646: SELECT WL.LOCATION_SOURCE_CODE LOC_TYPE,
647: WTS.STOP_LOCATION_ID LOCATION_ID,
646: SELECT WL.LOCATION_SOURCE_CODE LOC_TYPE,
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
659: p_location_id IN NUMBER,
660: p_corp_id IN NUMBER,
661: p_loc_type IN VARCHAR2) is
662: select 'X'
663: from WSH_OTM_LOCATIONS_GTMP
664: where location_id = p_location_id
665: AND (p_corp_id is NULL OR corporation_id = p_corp_id)
666: and location_type = p_loc_type;
667:
699: )
700: THEN
701: --{
702: FORALL i in p_entity_in_rec.entity_id_tbl.first..p_entity_in_rec.entity_id_tbl.last
703: insert into wsh_otm_locations_gtmp
704: (location_id,
705: location_type
706: )
707: values
896: )
897: THEN
898: --{
899: FORALL i in p_entity_in_rec.entity_id_tbl.first..p_entity_in_rec.entity_id_tbl.last
900: insert into wsh_otm_locations_gtmp
901: (location_id,
902: corporation_id,
903: location_type
904: )
1003:
1004: cursor l_get_org_loc_id_csr (c_organization_id IN NUMBER) IS
1005: select SFOV.location_id FROM_LOCATION_ID, WLT.LOCATION_ID GTMP_LOCATION_ID
1006: from WSH_SHIP_FROM_ORGS_V SFOV,
1007: WSH_OTM_LOCATIONS_GTMP WLT
1008: WHERE SFOV.organization_id = c_organization_id
1009: AND SFOV.ORGANIZATION_ID = WLT.CORPORATION_ID (+)
1010: AND WLT.LOCATION_TYPE (+) = 'ORG_LOC';
1011:
1408: HR_ALL_ORGANIZATION_UNITS ORG,
1409: --HR_ALL_ORGANIZATION_UNITS_TL ORGL,
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'
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
1453: WHERE HZL.LOCATION_ID = WL.SOURCE_LOCATION_ID
1454: AND WL.LOCATION_SOURCE_CODE = 'HZ'
1536: HPS.LAST_UPDATE_DATE HPS_LAST_UPD_DATE,
1537: HP.LAST_UPDATE_DATE HP_LAST_UPD_DATE
1538: FROM HZ_LOCATIONS HZL,
1539: FND_TERRITORIES FNDTR,
1540: WSH_OTM_LOCATIONS_GTMP wlt,
1541: WSH_CARRIERS WCV,
1542: WSH_CARRIER_SITES WCS,
1543: HZ_PARTY_SITES HPS,
1544: HZ_PARTIES HP,