[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT routing_req_id
FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = p_delId;
SELECT receipt_number
FROM wsh_inbound_txn_history wth
WHERE wth.transaction_id = p_routreqId
AND wth.transaction_type = 'ROUTING_REQUEST';
SELECT wdl.pick_up_stop_id, wt.trip_id, wnd.name, wt.planned_flag, wnd.planned_flag
FROM wsh_trip_stops wts, wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_Trips wt
WHERE wnd.delivery_id = p_delId
and wnd.delivery_id = wdl.delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and wnd.initial_pickup_location_id = wts.stop_location_id
and wts.trip_id = wt.trip_id;
SELECT nvl(wts.physical_stop_id,drop_off_stop_id) drop_off_stop_id
FROM wsh_trip_stops wts, wsh_new_deliveries wnd, wsh_delivery_legs wdl
WHERE wnd.delivery_id = p_delId
and wnd.delivery_id = wdl.delivery_id
and wdl.drop_off_stop_id = wts.stop_id
and wnd.ultimate_dropoff_location_id = wts.stop_location_id;
SELECT MAX(revision_number)
INTO l_revNum
FROM wsh_inbound_txn_history
WHERE shipment_header_id = l_deliveryId
AND transaction_type = 'ROUTING_RESPONSE'
AND status = 'GENERATED';
UpdateTxnHistory(p_deliveryId => l_deliveryId,
p_TxnId => l_TxnId,
p_RevNum => l_RevNum,
x_Status => l_Status);
SELECT user_name
FROM fnd_user
WHERE user_id = p_UserId;
SELECT initial_pickup_location_id, party_id, vendor_id, name -- IB-Phase-2
FROM wsh_new_deliveries
WHERE delivery_id = p_delivId;
SELECT hrel.party_id, contact_person.party_id, email_record.email_address, hrel.end_date
FROM hz_party_sites hps,
hz_party_site_uses hpsu,
hz_parties contact_person,
hz_org_contacts supplier_contact,
hz_contact_points phone_record,
hz_contact_points email_record,
hz_relationships hrel
WHERE hps.party_site_id = hpsu.party_site_id
AND hpsu.site_use_type = 'SUPPLIER_SHIP_FROM'
AND hrel.subject_id = contact_person.party_id
AND hrel.subject_table_name = 'HZ_PARTIES'
AND hrel.subject_type = 'PERSON'
AND hrel.object_id = hps.party_id
AND hrel.object_table_name = 'HZ_PARTIES'
AND hrel.object_type = 'ORGANIZATION'
AND hrel.relationship_code = 'CONTACT_OF'
AND hrel.directional_flag = 'F'
AND supplier_contact.party_relationship_id =hrel.relationship_id
AND supplier_contact.party_site_id = hps.party_site_id
AND phone_record.owner_table_name(+) = 'HZ_PARTIES'
AND phone_record.owner_table_id(+) = hrel.party_id
AND phone_record.contact_point_type(+) = 'PHONE'
AND email_record.owner_table_name = 'HZ_PARTIES'
AND email_record.owner_table_id = hrel.party_id
AND email_record.contact_point_type = 'EMAIL'
AND hps.location_id = p_locId
AND hps.party_id = p_delpartyId;
SELECT display_name
FROM wf_roles
WHERE name = 'HZ_PARTY:' || p_relId;
SELECT fu.user_name, fu.email_address
FROM fnd_user fu, hz_relationships hz
WHERE hz.subject_id = p_vendorId
AND hz.object_id = fu.person_party_id --IB-phase-2 Vendor Merge
AND hz.subject_type = 'ORGANIZATION'
AND hz.object_type = 'PERSON'
AND hz.relationship_type = 'POS_EMPLOYMENT'
AND hz.relationship_code = 'EMPLOYER_OF'
AND hz.subject_table_name = 'HZ_PARTIES'
AND hz.object_table_name = 'HZ_PARTIES'
AND hz.status = 'A'
AND hz.start_date <= sysdate
AND hz.end_date >= sysdate;
SELECT transaction_id
FROM wsh_inbound_txn_history
WHERE shipment_header_id = p_delId
AND transaction_type = 'ROUTING_RESPONSE'
ORDER BY revision_number DESC;
SELECT name, organization_id, vendor_id
FROM wsh_new_deliveries
WHERE delivery_id = p_delId;
SELECT receipt_number
FROM wsh_inbound_txn_history
WHERE shipment_header_id = p_delId
ORDER BY receipt_date DESC;
SELECT WSH_ROUTING_RESPONSE_S.nextval INTO l_receiptNum FROM dual;
PROCEDURE UpdateTxnHistory(p_deliveryId IN NUMBER,
p_TxnId IN NUMBER,
p_RevNum IN NUMBER,
x_Status OUT NOCOPY VARCHAR2) IS
--
l_Status VARCHAR2(10);
l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UpdateTxnHistory';
UPDATE wsh_new_deliveries
SET routing_response_id = p_TxnId,
last_update_date = l_Date
WHERE delivery_id = p_deliveryId;
wsh_debug_sv.logmsg(l_moduleName, 'No. of rows updated', SQL%ROWCOUNT);
wsh_debug_sv.logmsg(l_moduleName,'** Input record to update_txn_History **');
WSH_INBOUND_TXN_HISTORY_PKG.update_txn_history(p_txn_history_rec => l_txnHistoryRec,
x_return_status => l_Status);
wsh_debug_sv.log(l_moduleName, 'Return status after update_Txn_history', l_Status);
wsh_util_core.default_handler('WSH_ROUTING_RESPONSE_PKG.UpdateTxnHistory');
END UpdateTxnHistory;
SELECT wts.trip_id,wnd.organization_id,wnd.shipping_control
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts
WHERE wnd.delivery_id = p_delivery_id
AND wnd.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wnd.initial_pickup_location_id = wts.stop_location_id;
SELECT 1
FROM hz_relationships rel,
wsh_carrier_sites wcs,
hz_party_sites hps,
hz_org_contacts hoc,
hz_contact_points hcp,
hz_contact_points hcp2,
hz_parties party_rel,
wsh_org_carrier_sites wocs
WHERE wcs.carrier_site_id = hps.party_site_id
AND hps.party_id = rel.object_id
AND hps.party_site_id = hoc.party_site_id
AND hoc.party_relationship_id = rel.relationship_id
AND party_rel.party_id = rel.subject_id
AND hcp.owner_table_id = rel.party_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND wcs.carrier_id= p_carrier_id
AND hoc.decision_maker_flag = 'Y'
AND hcp2.owner_table_id(+) = rel.party_id
AND hcp2.contact_point_type(+) = 'PHONE'
AND hcp2.owner_table_name(+) = 'HZ_PARTIES'
AND wcs.carrier_site_id = wocs.carrier_site_id
AND wocs.organization_id = p_organization_id;
SELECT carrier_id,mode_of_transport,service_level, load_tender_status
FROM wsh_trips
WHERE trip_id = p_trip_id;
SELECT party_name
FROM wsh_carriers, hz_parties
WHERE carrier_id =party_id
AND carrier_id= p_carrier_id;
SELECT source_header_id, source_line_id,
po_shipment_line_id, source_blanket_reference_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wda.delivery_id=p_delivery_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND nvl(line_direction,'O') not in ('O','IO')
AND source_code='PO';
SELECT *
FROM wsh_new_deliveries
WHERE delivery_id = p_deliveryId
FOR UPDATE NOWAIT;
PROCEDURE FTERRESP_SELECTOR(itemType IN VARCHAR2,
itemKey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT NOCOPY VARCHAR2) IS
--
l_userId NUMBER;
l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FTERRESP_SELECTOR';
wsh_debug_sv.push(l_moduleName, 'FTERRESP_SELECTOR');
SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
INTO l_clientOrgId FROM DUAL;
wsh_debug_sv.logmsg(l_moduleName, 'SELECTOR: PROFILE ORG = WF ORG');
END FTERRESP_SELECTOR;