[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
decode(P_STANDALONE ,'N', source_header_number,nvl(reference_number,source_header_number)) --STANDALONE CHANGES
FROM
WSH_DELIVERY_DETAILS
WHERE CONTAINER_FLAG = 'N'
AND DELIVERY_DETAIL_ID = V_DELIVERY_DETAIL_ID;
SELECT
DELIVERY_DETAIL_ID
FROM
WSH_DELIVERY_ASSIGNMENTS_V DA
START WITH DELIVERY_DETAIL_ID = P_DELIVERY_DETAIL_ID
CONNECT BY prior DELIVERY_DETAIL_ID = PARENT_DELIVERY_DETAIL_ID;
SELECT
DELIVERY_DETAIL_ID
FROM
WSH_DELIVERY_DETAILS WDD
WHERE CONTAINER_NAME = P_CONTAINER_NAME
AND NVL(WDD.LINE_DIRECTION
,'O') IN ( 'O' , 'IO' )
AND CONTAINER_FLAG = 'Y';
LP_TRIP_STOP_ID := 'AND wnd.delivery_id in (select distinct delivery_id from wsh_delivery_legs where (pick_up_stop_id =
:P_TRIP_STOP_ID OR drop_off_stop_id = :P_TRIP_STOP_ID ))';
LP_TRIP_ID := 'AND wnd.delivery_id in (select distinct delivery_id from wsh_delivery_legs wdl , wsh_trip_stops wts
where wdl.pick_up_stop_id = wts.stop_id and
wts.trip_id = :P_TRIP_ID )';
LP_DATE_RANGE := 'AND wnd.delivery_id in (select distinct delivery_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date <= :P_DEPARTURE_DATE_HIGH))';
LP_DATE_RANGE := 'AND wnd.delivery_id in (select distinct delivery_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date >= :P_DEPARTURE_DATE_LOW))';
LP_DATE_RANGE := 'AND wnd.delivery_id in (select distinct delivery_id from wsh_delivery_legs where pick_up_stop_id in (select stop_id from wsh_trip_stops where planned_departure_date between :P_DEPARTURE_DATE_LOW and :P_DEPARTURE_DATE_HIGH))';
SELECT
HP.PARTY_NAME,
NVL(HP.PERSON_PRE_NAME_ADJUNCT
,HP.PERSON_TITLE) TITLE,
PS.ADDRESSEE
INTO SHIP_TO_CUST_NAME,L_PERSON_TITLE,L_ADDRESSEE
FROM
HZ_PARTY_SITES PS,
HZ_CUST_ACCT_SITES_ALL CA,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTIES HP
WHERE SU.SITE_USE_ID = C_SHIP_TO_SITE_USE_ID
AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID
AND HP.PARTY_ID = PS.PARTY_ID;
SELECT
MEANING || ' ' || SHIP_TO_CUST_NAME
INTO SHIP_TO_CUST_NAME
FROM
AR_LOOKUPS
WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
AND LOOKUP_TYPE = L_LOOKUP_TYPE;
SELECT
SHIP_METHOD_CODE
INTO L_SHIP_METHOD_CODE
FROM
WSH_NEW_DELIVERIES
WHERE DELIVERY_ID = DELIVERY_ID1;
SELECT
WT.SHIP_METHOD_CODE
INTO L_SHIP_METHOD_CODE
FROM
WSH_TRIPS WT,
WSH_TRIP_STOPS WTS,
WSH_DELIVERY_ASSIGNMENTS_V WDA,
WSH_DELIVERY_LEGS WDL
WHERE WTS.STOP_ID = WDL.PICK_UP_STOP_ID
--AND WDA.DELIVERY_ID = DELIVERY_ID
AND WDA.DELIVERY_ID = DELIVERY_ID1
AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
AND WTS.TRIP_ID = WT.TRIP_ID
AND ROWNUM = 1;
SELECT
SUBSTR(SHIP_METHOD_MEANING
,1
,50)
INTO L_SHIP_METHOD_MEANING
FROM
WSH_CARRIER_SERVICES
WHERE SHIP_METHOD_CODE = L_CARRIER;
SELECT
TERRITORY_SHORT_NAME
INTO L_SHIP_TO_COUNTRY_NAME
FROM
FND_TERRITORIES_VL
WHERE TERRITORY_CODE = SHIP_TO_COUNTRY;
SELECT
TERRITORY_SHORT_NAME
INTO L_DEL_TO_COUNTRY_NAME
FROM
FND_TERRITORIES_VL
WHERE TERRITORY_CODE = DEL_TO_COUNTRY;
SELECT
1
FROM
WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID = P_DEL_DET_ID_1
AND CONTAINER_FLAG = 'Y';
SELECT
DELIVERY_DETAIL_ID
FROM
WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID in (
SELECT
WDA.DELIVERY_DETAIL_ID
FROM
WSH_DELIVERY_ASSIGNMENTS_V WDA
START WITH WDA.PARENT_DELIVERY_DETAIL_ID = P_DEL_DET_ID_2
CONNECT BY prior WDA.DELIVERY_DETAIL_ID = PARENT_DELIVERY_DETAIL_ID )
AND SHIP_TO_CONTACT_ID is not null
AND CONTAINER_FLAG IN ( 'N' , 'Y' )
AND ROWNUM < 2;
SELECT
CONTACT_NAME,
CONTACT_PHONE_NUMBER
FROM
( SELECT
DISTINCT
CONTACT.PERSON_PRE_NAME_ADJUNCT || ' ' || SUBSTRB(CONTACT.PERSON_FIRST_NAME
,1
,40) || ' ' || SUBSTRB(CONTACT.PERSON_LAST_NAME
,1
,50) CONTACT_NAME,
DECODE(PHONES.CONTACT_POINT_TYPE
,'TLX'
,PHONES.TELEX_NUMBER
,PHONES.PHONE_NUMBER) CONTACT_PHONE_NUMBER
FROM
( SELECT
PARTY.PERSON_PRE_NAME_ADJUNCT,
PARTY.PERSON_FIRST_NAME,
PARTY.PERSON_LAST_NAME,
ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
FROM
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT,
HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID ) CONTACT,
( SELECT
CONT_POINT.PHONE_LINE_TYPE PHONE_LINE_TYPE,
CONT_POINT.CONTACT_POINT_TYPE CONTACT_POINT_TYPE,
CAR.CUST_ACCOUNT_ROLE_ID CUST_ACCOUNT_ROLE_ID,
CONT_POINT.TELEX_NUMBER TELEX_NUMBER,
CONT_POINT.PHONE_NUMBER PHONE_NUMBER,
NVL(CONT_POINT.PHONE_LINE_TYPE
,CONT_POINT.CONTACT_POINT_TYPE) PHONE_TYPE
FROM
HZ_CONTACT_POINTS CONT_POINT,
HZ_CUST_ACCOUNT_ROLES CAR
WHERE CAR.PARTY_ID = CONT_POINT.OWNER_TABLE_ID
AND CONT_POINT.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND CONT_POINT.CONTACT_POINT_TYPE not in ( 'EDI' , 'EMAIL' , 'WEB' ) ) PHONES,
WSH_DELIVERY_DETAILS WDD
WHERE WDD.DELIVERY_DETAIL_ID = P_DEL_DET_ID_3
AND WDD.SHIP_TO_CONTACT_ID = contact.cust_account_role_id (+)
AND CONTACT.CUST_ACCOUNT_ROLE_ID = phones.cust_account_role_id (+)
AND phones.phone_type (+) = 'PHONE' )
WHERE ROWNUM < 2;