[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wdd.source_header_number
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.container_flag= 'N'
AND rownum = 1;
SELECT name,
customer_id,
organization_id,
ultimate_dropoff_location_id --bug 3920178
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT wts1.Actual_Departure_Date,
wts2.Actual_Arrival_Date,
wt.Vehicle_Num_Prefix,
wt.Vehicle_Number,
wt.Route_ID,
wt.Routing_Instructions,
wts1.Departure_Seal_Code,
--Bug 3458160
wt.operator
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts1,
wsh_trip_stops wts2,
wsh_trips wt
WHERE wts1.trip_id = wt.trip_id
AND wts2.trip_id = wt.trip_id
AND wts1.stop_id = wdl.pick_up_stop_id
AND wts2.stop_id = wdl.drop_off_stop_id
AND wdl.delivery_id = p_delivery_id;
SELECT hp.party_name,
hca.account_number
FROM hz_parties hp,
hz_cust_accounts hca
WHERE hca.party_id = hp.party_id
AND hca.cust_account_id = p_customer_id;
SELECT wdd.ship_to_site_use_id ship_to_site_use_id , count(*) cnt
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wdd.container_flag = 'N'
GROUP BY ship_to_site_use_id
ORDER BY cnt DESC;
SELECT LOCATION, contact_id
FROM HZ_CUST_SITE_USES_ALL
WHERE site_use_id = p_site_use_id;
SELECT HCSU.LOCATION, HCSU.CONTACT_ID
FROM HZ_CUST_SITE_USES_ALL HCSU,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTY_SITES HPS
WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCSU.SITE_USE_CODE = 'SHIP_TO'
AND HCSU.STATUS = 'A'
AND HCAS.STATUS = 'A'
AND HCA.STATUS = 'A'
AND HPS.LOCATION_ID = p_ship_to_location_id
AND HCAS.CUST_ACCOUNT_ID = p_customer_id
AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id)
AND HCAS.ORG_ID = HCSU.ORG_ID ;
SELECT HCSU.LOCATION, HCSU.CONTACT_ID
FROM HZ_CUST_SITE_USES_ALL HCSU,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCT_RELATE_ALL HCAR
WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCSU.SITE_USE_CODE = 'SHIP_TO'
AND HCSU.STATUS = 'A'
AND HCAS.STATUS = 'A'
AND HCA.STATUS = 'A'
AND HPS.LOCATION_ID = p_ship_to_location_id
AND HCA.CUST_ACCOUNT_ID = HCAR.CUST_ACCOUNT_ID
AND HCAR.RELATED_CUST_ACCOUNT_ID = p_customer_id
AND HCAR.SHIP_TO_FLAG = 'Y'
AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id)
AND HCAS.ORG_ID = HCSU.ORG_ID ;
SELECT organization_id,
initial_pickup_location_id,
ultimate_dropoff_location_id,
intmed_ship_to_location_id,
pooled_ship_to_location_id,
currency_code
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
/* Patchset I: Locations Project. Select address components from
wsh_ship_from_org_locations_v */
CURSOR ship_from_info_cur(p_org_id NUMBER, p_loc_id NUMBER) IS
SELECT
WSFL.ORGANIZATION_NAME PARTY_NAME,
HL.LOCATION_CODE PARTNER_LOCATION,
0 DUNS_NUMBER,
NULL INTMED_SHIP_TO_LOCATION,
NULL POOLED_SHIP_TO_LOCATION_ID,
WSFL.ADDRESS1 ADDRESS1,
WSFL.ADDRESS2 ADDRESS2,
WSFL.ADDRESS3 ADDRESS3,
NULL ADDRESS4,
WSFL.CITY CITY,
WSFL.COUNTRY COUNTRY,
NULL COUNTY,
WSFL.POSTAL_CODE POSTAL_CODE,
WSFL.PROVINCE REGION,
WSFL.STATE STATE,
HL.TELEPHONE_NUMBER_2 FAX_NUMBER,
HL.TELEPHONE_NUMBER_1 TELEPHONE,
NULL URL
FROM
wsh_ship_from_org_locations_v WSFL,
HR_LOCATIONS_ALL HL
WHERE
WSFL.wsh_location_id = p_loc_id
AND WSFL.source_location_id = HL.location_id;
/* Patchset I: Locations Project. Selecting from wsh_customer_locations_v */
CURSOR ship_to_info_cur(p_loc_id NUMBER, p_opUnit_id NUMBER DEFAULT NULL) IS
SELECT
DISTINCT wclv.CUSTOMER_NAME PARTY_NAME,
wclv.LOCATION PARTNER_LOCATION,
NULL CURRENCY,
wclv.DUNS_NUMBER DUNS_NUMBER,
WCLV.ADDRESS1 ADDRESS1,
WCLV.ADDRESS2 ADDRESS2,
WCLV.ADDRESS3 ADDRESS3,
WCLV.ADDRESS4 ADDRESS4,
WCLV.CITY CITY,
WCLV.COUNTRY COUNTRY,
WCLV.COUNTY COUNTY,
WCLV.POSTAL_CODE POSTAL_CODE,
WCLV.PROVINCE REGION,
WCLV.STATE STATE
FROM
wsh_customer_locations_v wclv
WHERE
wclv.wsh_location_id = p_loc_id
and wclv.org_id = nvl(p_opUnit_id, wclv.org_id)
AND wclv.customer_status = 'A'
AND wclv.cust_acct_site_status = 'A'
AND wclv.site_use_status = 'A'
AND wclv.site_use_code = 'SHIP_TO';
SELECT hcp.raw_phone_number,
hcp.url
FROM hz_party_sites hps,
hz_contact_points hcp,
wsh_locations_hz_v wlhz
WHERE HCP.CONTACT_POINT_TYPE = 'PHONE'
AND HCP.PHONE_LINE_TYPE=l_line_type
AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
AND HPS.PARTY_SITE_ID = HCP.OWNER_TABLE_ID
AND wlhz.wsh_location_id = p_loc_id
AND wlhz.source_location_id = hps.location_id
ORDER BY hcp.primary_flag desc;
select wcs.ship_method_code
from wsh_carrier_services wcs,
wsh_carriers_v wcar
where wcar.carrier_name = p_carrier_name
and nvl(wcs.service_level, '!') = nvl(p_service_level, '!')
and nvl(wcs.mode_of_transport, '!') = nvl(p_mode_of_transport, '!')
and wcs.carrier_id = wcar.carrier_id;
SELECT HCSU.LOCATION --bug 3920178 , HP.PARTY_NAME, HP.PARTY_NUMBER
FROM
HZ_CUST_SITE_USES_ALL HCSU,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
WSH_LOCATIONS WL1
WHERE
WL1.wsh_location_id = p_loc_id AND
HCA.CUST_ACCOUNT_ID = p_cust_id AND --bugfix 3842898
(HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id) AND
WL1.SOURCE_LOCATION_ID = HPS.LOCATION_ID AND
WL1.LOCATION_SOURCE_CODE = 'HZ' AND
HCA.PARTY_ID = HP.PARTY_ID AND
HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID AND
HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND
HCSU.SITE_USE_CODE = 'SHIP_TO' AND
HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID AND
HCSU.STATUS = 'A' AND
HCAS.STATUS = 'A' AND
HCA.STATUS = 'A' AND
HCAS.ORG_ID = HCSU.ORG_ID AND
-- removed the NVL around the org_id k proj
--bug 3920178
HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
ORDER BY
HCSU.SITE_USE_CODE;
SELECT wdd.org_id org_id, count(*) cnt
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id IN
(SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda
START WITH delivery_detail_id = p_del_detail_id
CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id )
GROUP BY org_id
HAVING org_id IS NOT NULL
ORDER BY cnt desc;
SELECT rowidtochar(min(rowid)),count(*),sum(quantity)
FROM wsh_serial_numbers
WHERE delivery_detail_id = cp_delivery_detail_id;
SELECT quantity
FROM wsh_serial_numbers
WHERE rowidtochar(rowid) = cp_wsn_rowid;
SELECT LOCATION
FROM HZ_CUST_SITE_USES_ALL
WHERE site_use_id = p_site_use_id;
SELECT HCSU.LOCATION
FROM HZ_CUST_SITE_USES_ALL HCSU,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_PARTY_SITES HPS,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCT_RELATE_ALL HCAR
WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCSU.SITE_USE_CODE = 'SHIP_TO'
AND HCSU.STATUS = 'A'
AND HCAS.STATUS = 'A'
AND HCA.STATUS = 'A'
AND HPS.LOCATION_ID = p_ship_to_location_id
AND HCA.CUST_ACCOUNT_ID = HCAR.CUST_ACCOUNT_ID
AND HCAR.RELATED_CUST_ACCOUNT_ID = p_customer_id
AND HCAR.SHIP_TO_FLAG = 'Y'
AND (HCAS.ORG_ID IS NULL OR HCAS.ORG_ID = p_org_id)
AND HCAS.ORG_ID = HCSU.ORG_ID ;
SELECT HP.PARTY_NAME, HP.PARTY_NUMBER
FROM HZ_PARTIES HP,
HZ_CUST_ACCOUNTS HCA
WHERE HP.PARTY_ID = HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID = p_customer_id;
SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda
START WITH wda.parent_delivery_detail_id = p_delivery_detail_id
CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id;
SELECT wdd.ship_to_contact_id
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = p_detail_id
AND wdd.container_flag = 'N';
SELECT PER_CONTACT.PERSON_FIRST_NAME,
PER_CONTACT.PERSON_MIDDLE_NAME,
PER_CONTACT.PERSON_LAST_NAME,
PHONE_CONTACT.RAW_PHONE_NUMBER,
HREL.PARTY_ID
from HZ_CUST_ACCOUNT_ROLES HCAR,
HZ_RELATIONSHIPS HREL,
HZ_ORG_CONTACTS HOC,
HZ_CONTACT_POINTS PHONE_CONTACT,
HZ_PARTIES PER_CONTACT
WHERE HCAR.CUST_ACCOUNT_ROLE_ID = p_contact_id
AND HREL.PARTY_ID = HCAR.PARTY_ID
AND HCAR.ROLE_TYPE = 'CONTACT'
AND HREL.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID
AND HREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HREL.SUBJECT_TYPE = 'PERSON'
AND HREL.DIRECTIONAL_FLAG = 'F'
AND HREL.SUBJECT_ID = PER_CONTACT.PARTY_ID
AND PHONE_CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE_CONTACT.OWNER_TABLE_ID(+) = HREL.PARTY_ID
AND PHONE_CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND PHONE_CONTACT.PHONE_LINE_TYPE(+) = 'GEN'
AND PHONE_CONTACT.PRIMARY_FLAG(+) = 'Y';
SELECT RAW_PHONE_NUMBER
FROM HZ_CONTACT_POINTS
WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
AND OWNER_TABLE_ID = p_owner_tbl_id
AND CONTACT_POINT_TYPE = 'PHONE'
AND PHONE_LINE_TYPE = 'GEN';