[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';
SELECT name,
customer_id,
organization_id,
ultimate_dropoff_location_id
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,
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 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 = c_delivery_id
AND wdd.container_flag = 'N'
GROUP BY ship_to_site_use_id
ORDER BY cnt DESC;
SELECT location
FROM hz_cust_site_uses_all
WHERE site_use_id = c_site_use_id;
SELECT hcsu.location
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 = c_ship_to_location_id
AND hcas.cust_account_id = c_customer_id
AND (hcas.org_id IS NULL OR hcas.org_id = c_org_id)
AND hcas.org_id = hcsu.org_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 = c_ship_to_location_id
AND hca.cust_account_id = hcar.cust_account_id
AND hcar.related_cust_account_id = c_customer_id
AND hcar.ship_to_flag = 'Y'
AND (hcas.org_id IS NULL OR hcas.org_id = c_org_id)
AND hcas.org_id = hcsu.org_id ;
SELECT hp.party_name
FROM hz_parties hp,
hz_cust_accounts hca
WHERE hca.party_id = hp.party_id
AND hca.cust_account_id = c_customer_id;
SELECT packing_slip_number
INTO x_pack_slip_num
FROM wsh_packing_slips_db_v
WHERE delivery_id = p_delivery_id;
SELECT wdi.sequence_number
INTO x_bill_of_lading_num
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_document_instances wdi
WHERE wnd.delivery_id = p_delivery_id
AND wnd.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wts.stop_location_id = wnd.initial_pickup_location_id
AND wdi.entity_id = wdl.delivery_leg_id
AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
AND wdi.document_type = 'BOL';
SELECT wdd.ship_to_contact_id
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = c_detail_id
AND wdd.container_flag = 'N';
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 rowidtochar(min(rowid)),count(*),sum(to_number(SERIAL_PREFIX))
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id
FROM WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID = cp_delivery_detail_id
AND SOURCE_CODE = 'OE');
SELECT to_number(SERIAL_PREFIX)
FROM mtl_serial_numbers_temp
WHERE rowidtochar(rowid) = cp_wsn_rowid;
select concatenated_segments
into x_locator_code
from mtl_item_locations_kfv
where inventory_location_id = p_locator_id;
select sum(requested_quantity)
into x_open_quantity
from wsh_delivery_details
where source_line_id = p_src_line_id
and source_code = 'OE'
and released_status in ('N','R','S','Y');
select sum(requested_quantity)
into x_bo_quantity
from wsh_delivery_details
where source_line_id = p_src_line_id
and source_code = 'OE'
and released_status = 'B';
select oh.order_number,
ol.line_number,
nvl(wth.document_type,'SalesOrder'),
-- Distributed - TPW Changes
ol.source_document_type_id,
ol.source_document_id,
ol.source_document_line_id
into x_document_id,
x_line_number,
x_document_type,
-- Distributed - TPW Changes
l_source_document_type_id,
l_source_document_id,
l_source_document_line_id
from oe_order_lines_all ol,
oe_order_headers_all oh,
wsh_transactions_history wth
where ol.line_id = p_src_line_id
and ol.header_id = oh.header_id
and oh.header_id = wth.entity_number (+)
and wth.entity_type(+) = 'ORDER'
and wth.document_type(+) = 'SR'
and wth.document_direction(+) = 'I'
and wth.transaction_status(+) = 'SC'
and rownum < 2;
select ph.segment1,
pl.line_num,
'InternalRequisition'
into x_document_id,
x_line_number,
x_document_type
from po_requisition_headers_all ph,
po_requisition_lines_all pl
where ph.requisition_header_id = pl.requisition_header_id
and pl.requisition_line_id = l_source_document_line_id
and ph.requisition_header_id = l_source_document_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 ol.ship_to_org_id,
ol.ship_to_contact_id,
ol.deliver_to_org_id,
ol.deliver_to_contact_id
FROM wsh_delivery_details wdd,
oe_order_lines_all ol
WHERE wdd.delivery_detail_id = c_detail_id
AND wdd.source_line_id = ol.line_id
AND wdd.source_code = 'OE'
AND wdd.container_flag = 'N';
SELECT
distinct wclv.customer_id party_id,
wclv.customer_name party_name,
wclv.location partner_location,
wclv.duns_number duns_number,
wclv.site_use_id address_id,
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.site_use_id = c_site_id
AND wclv.org_id = c_opunit_id
AND wclv.customer_status = 'A'
AND wclv.cust_acct_site_status = 'A'
AND wclv.site_use_status = 'A';
SELECT organization_id,
initial_pickup_location_id,
intmed_ship_to_location_id,
pooled_ship_to_location_id
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
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
FROM wsh_ship_from_org_locations_v wsfl,
hr_locations_all hl
WHERE wsfl.wsh_location_id = c_loc_id
AND wsfl.source_location_id = hl.location_id;
SELECT
distinct wclv.customer_name party_name,
wclv.location partner_location,
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 = c_loc_id
AND wclv.org_id = nvl(c_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 ol.ship_to_org_id,
ol.invoice_to_org_id
FROM wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
oe_order_lines_all ol
WHERE wnd.delivery_id = p_delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_code = 'OE'
AND wdd.source_line_id = ol.line_id
AND rownum < 2;
SELECT
DISTINCT wclv.customer_id party_id,
wclv.customer_name party_name,
wclv.location partner_location,
wclv.duns_number duns_number,
wclv.site_use_id address_id,
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.site_use_id = c_site_id
AND wclv.org_id = c_opunit_id
AND wclv.customer_status = 'A'
AND wclv.cust_acct_site_status = 'A'
AND wclv.site_use_status = 'A';
select distinct ol.ship_to_contact_id
into l_ship_to_contact_id
from wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
oe_order_lines_all ol
where wnd.delivery_id = p_delivery_id
and wnd.delivery_id = wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.source_code = 'OE'
and wdd.source_line_id = ol.line_id;
select distinct ol.invoice_to_contact_id
into l_bill_to_contact_id
from wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
oe_order_lines_all ol
where wnd.delivery_id = p_delivery_id
and wnd.delivery_id = wda.delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.source_code = 'OE'
and wdd.source_line_id = ol.line_id;
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';
select wc.freight_code, wcs.service_level, wcs.mode_of_transport
into x_carrier_code, x_service_level, x_mode_of_transport
from wsh_carrier_services wcs,
wsh_carriers wc
where wc.carrier_id = wcs.carrier_id
and wcs.ship_method_code = p_ship_method_code;
select organization_id,
organization_name,
wsh_location_id,
address1,
address2,
address3,
address4,
city,
country,
county,
postal_code,
province,
state
from wsh_ship_from_org_locations_v
where wsh_location_id = c_loc_id
and organization_id = c_organization_id;
SELECT HP.PARTY_NAME
FROM HZ_PARTIES HP,
HZ_CUST_ACCOUNTS HCA
WHERE HP.PARTY_ID = HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID = c_customer_id;
select customer_id,
customer_name,
site_use_id,
address1,
address2,
address3,
address4,
city,
country,
county,
postal_code,
province,
state
from wsh_customer_locations_v
where site_use_id = c_site_use_id;
select organization_id, ship_from_location_id, customer_id,
ship_to_site_use_id, ship_to_contact_id,
invoice_to_site_use_id, invoice_to_contact_id,
deliver_to_site_use_id, deliver_to_contact_id
into l_organization_id, l_ship_from_location_id, l_customer_id,
l_ship_to_site_use_id, l_ship_to_contact_id,
l_invoice_to_site_use_id, l_invoice_to_contact_id,
l_deliver_to_site_use_id, l_deliver_to_contact_id
from wsh_shipment_batches
where batch_id = p_batch_id;
select order_quantity_uom,
ol.line_number,
msik.concatenated_segments,
msik.description,
ol.unit_selling_price,
ol.packing_instructions,
ol.shipping_instructions,
ol.request_date,
ol.schedule_ship_date,
ol.shipment_priority_code,
ol.ship_tolerance_above,
ol.ship_tolerance_below,
set_name,
customer_item_number,
ol.cust_po_number,
ol.subinventory,
ol.ordered_quantity,
ol.line_set_id,
-- Bug 9234726: Querying Item Id and Item's Primary UOM
msik.primary_uom_code,
ol.inventory_item_id
into x_line_quantity_uom,
x_line_number,
x_item_number,
x_item_description,
x_unit_selling_price,
x_packing_instructions,
x_shipping_instructions,
x_request_date,
x_schedule_date,
x_shipment_priority_code,
x_ship_tolerance_above,
x_ship_tolerance_below,
x_set_name,
x_customer_item_number,
x_cust_po_number,
x_subinventory,
x_line_quantity,
l_line_set_id,
l_req_qty_uom,
l_inv_item_id
from oe_order_lines_all ol,
mtl_system_items_kfv msik,
oe_sets,
mtl_customer_items
where ol.line_id = p_reference_line_id
and ol.inventory_item_id = msik.inventory_item_id
and ol.ship_from_org_id = msik.organization_id
and ol.ship_set_id = set_id (+)
and decode(ol.item_type_code, 'CUST', ol.ordered_item_id, null) = customer_item_id (+);
select sum(ol1.ordered_quantity)
into x_line_quantity
from oe_order_lines_all ol1
where ol1.line_set_id = l_line_set_id;