[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
msi.segment1,
msi.description,
lpad(to_char(wnd.initial_pickup_date,'MM/DD/YYYY'),12),
wnd.waybill,
sum(nvl(wdd.shipped_quantity,0))
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
mtl_system_items msi
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wnd.status_code in ('IT','CL')
AND wnd.initial_pickup_date > p_last_notif_date
AND wdd.inventory_item_id = msi.inventory_item_id
AND wdd.organization_id = msi.organization_id
AND wdd.source_header_id = p_source_header_id
AND wdd.source_code = p_source_code
AND nvl(wnd.shipment_direction, 'O') IN ('O','IO') --J Inbound Logistics jckwok
AND decode(p_contact_type,
'SHIP_TO',wdd.ship_to_contact_id,
'SOLD_TO',wdd.sold_to_contact_id,
wdd.customer_id) = p_contact_id
GROUP BY
wdd.source_header_number,
wdd.source_header_type_id,
wdd.source_line_id,
wdd.inventory_item_id,
msi.segment1,
msi.description,
wdd.src_requested_quantity,
wnd.initial_pickup_date,
wnd.waybill
HAVING
sum(nvl(wdd.shipped_quantity,0)) > 0;
SELECT
msi.segment1,
msi.description,
sum(nvl(wdd.requested_quantity,0))
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
mtl_system_items msi
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.date_scheduled < sysdate
--AND wdd.date_scheduled > p_last_notif_date
--AND wdd.released_status != 'C'
AND wda.delivery_id = wnd.delivery_id (+)
AND nvl(wnd.status_code,'XX') not in ('IT','CL')
AND wdd.inventory_item_id = msi.inventory_item_id
AND wdd.organization_id = msi.organization_id
AND wdd.source_header_id = p_source_header_id
AND wdd.source_code = p_source_code
AND nvl(wdd.line_direction, 'O') IN ('O','IO') --J Inbound Logistics jckwok
AND decode(p_contact_type,
'SHIP_TO',wdd.ship_to_contact_id,
'SOLD_TO',wdd.sold_to_contact_id,
wdd.customer_id) = p_contact_id
GROUP BY
wdd.source_header_number,
wdd.source_header_type_id,
wdd.source_line_id,
wdd.inventory_item_id,
msi.segment1,
msi.description,
wdd.src_requested_quantity
HAVING
sum(nvl(wdd.requested_quantity,0)) > 0;
l_rate_query := 'SELECT xmlelement("env:Envelope",XMLAttributes(''http://schemas.xmlsoap.org/soap/envelope/'' AS "xmlns:env"
,''http://www.ups.com/XMLSchema/XOLTWS/UPSS/v1.0'' AS " xmlns:ns1"
,''http://www.ups.com/XMLSchema/XOLTWS/Rate/v1.1'' AS " xmlns:ns2"
,''http://www.ups.com/XMLSchema/XOLTWS/Common/v1.0'' AS " xmlns:ns3")
,xmlelement("env:Header",xmlelement("ns1:UPSSecurity",
xmlelement("ns1:UsernameToken",
xmlelement("ns1:Username",:b1 ),
xmlelement("ns1:Password", :b2 )
),
xmlelement("ns1:ServiceAccessToken",
xmlelement("ns1:AccessLicenseNumber",:b3)
)
)
)
,xmlelement("env:Body",xmlelement("ns2:RateRequest",
xmlelement("ns3:Request",xmlelement("ns3:RequestOption",''Rate'')
,xmlelement("ns3:TransactionReference",xmlelement("ns3:CustomerContext",''Rating and Service''))),
xmlelement("ns2:PickupType", xmlelement("ns2:Code",:b4)),
xmlelement("ns2:Shipment", xmlelement("ns2:Shipper",xmlelement("ns2:Address",xmlelement("ns2:CountryCode",w1.country)))
, xmlelement("ns2:ShipTo",xmlelement("ns2:Address", xmlelement("ns2:City",w2.city)
, xmlelement("ns2:StateProvinceCode",w2.state)
, xmlelement("ns2:PostalCode", :b5)
, xmlelement("ns2:CountryCode",:b6)';
SELECT VALUE
INTO v_decimal
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
SELECT xmlelement("env:Envelope",XMLAttributes('http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:env",
'http://www.ups.com/XMLSchema/XOLTWS/UPSS/v1.0' AS " xmlns:ns1",
'http://www.ups.com/XMLSchema/XOLTWS/Track/v2.0' AS " xmlns:ns2",
'http://www.ups.com/XMLSchema/XOLTWS/Common/v1.0' AS " xmlns:ns3")
,xmlelement("env:Header",xmlelement("ns1:UPSSecurity",
xmlelement("ns1:UsernameToken",
xmlelement("ns1:Username",G_UPS_WS_USERNAME),
xmlelement("ns1:Password",G_UPS_WS_PASSWD)
),
xmlelement("ns1:ServiceAccessToken",
xmlelement("ns1:AccessLicenseNumber",G_UPS_ACCESS_TOKEN)
)
)
)
,xmlelement("env:Body",xmlelement("ns2:TrackRequest"
,xmlelement("ns3:Request",xmlelement("ns3:RequestOption",'1'))
,xmlelement("ns2:InquiryNumber",wdd.TRACKING_NUMBER
)
)
)
)
FROM wsh_delivery_details wdd
WHERE wdd.tracking_number= p_request_in.InquiryNumber;
x_pkg_detail_segment(l_index).PickupDate := to_date(l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:PickupDate/text()',l_namespace).getStringVal(),'YYYYMMDD');
WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').PickupDate :'||sqlerrm);
x_activity_detail.DELETE(l_act_index);
x_track_address.DELETE(l_act_index);
SELECT xmlelement("env:Envelope",XMLAttributes('http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:env"
,'http://www.ups.com/XMLSchema/XOLTWS/UPSS/v1.0' AS " xmlns:ns1"
,'http://www.ups.com/XMLSchema/XOLTWS/tnt/v1.0' AS " xmlns:ns2"
,'http://www.ups.com/XMLSchema/XOLTWS/Common/v1.0' AS " xmlns:ns3")
,xmlelement("env:Header",xmlelement("ns1:UPSSecurity",
xmlelement("ns1:UsernameToken",
xmlelement("ns1:Username",G_UPS_WS_USERNAME),
xmlelement("ns1:Password",G_UPS_WS_PASSWD)
),
xmlelement("ns1:ServiceAccessToken",
xmlelement("ns1:AccessLicenseNumber",G_UPS_ACCESS_TOKEN)
)
)
)
,xmlelement("env:Body",xmlelement("ns2:TimeInTransitRequest",
xmlelement("ns3:Request",NULL),
xmlelement("ns2:ShipFrom",
xmlelement("ns2:Address",
xmlelement("ns2:City",wl.city),
xmlelement("ns2:CountryCode",wl.country),
xmlelement("ns2:PostalCode",wl.postal_code)
)
),
xmlelement("ns2:ShipTo",
xmlelement("ns2:Address",
xmlelement("ns2:City",w2.city),
xmlelement("ns2:CountryCode",wl.country),
xmlelement("ns2:PostalCode",w2.postal_code)
)
),
xmlelement("ns2:Pickup",
--xmlelement("ns2:Date",TO_CHAR((CASE WHEN NVL(wdd.earliest_pickup_date,SYSDATE)< SYSDATE THEN SYSDATE ELSE NVL(wdd.earliest_pickup_date,SYSDATE) END),'YYYYMMDD'))
xmlelement("ns2:Date",TO_CHAR(SYSDATE,'YYYYMMDD'))
)
)
)
)
FROM wsh_locations wl
, wsh_locations w2
WHERE wl.source_location_id = p_request_in.ship_from_location_id
AND w2.source_location_id = p_request_in.ship_to_location_id;