SELECT
wnd.delivery_id,
wnd.name delivery_name,
wdd.container_name,
wdd.ship_to_location_id,
nvl(wdd.deliver_to_location_id,wdd.ship_to_location_id) deliver_to_location_id,
max(wdd.delivery_detail_id) delivery_detail_id,
wdd.ship_to_site_use_id c_ship_to_site_use_id,
customers.customer_name,
contacts.title|| ' ' || contacts.first_name || ' ' || contacts.last_name contact_name,
phones.phone_number contact_phone_number,
hzp.party_name carrier_name,
wnd.waybill,
wdd.tracking_number,
wdd.organization_id del_organization_id,
loc.address1 ship_to_addr1,
loc.address2 ship_to_addr2,
loc.address3 ship_to_addr3,
loc.city || ', ' || nvl(nvl(loc.province,loc.state),loc.county) || ', ' || loc.postal_code ship_to_city_state,
loc.country ship_to_country,
loc1.address1 del_to_addr1,
loc1.address2 del_to_addr2,
loc1.address3 del_to_addr3,
loc1.city || ', ' || nvl(nvl(loc1.province,loc1.state),loc1.county) || ', ' || loc1.postal_code del_to_city_state,
loc1.country del_to_country,
WSH_WSHRDLAB_XMLP_PKG.cf_ship_to_country_nameformula(loc.country) CF_Ship_to_Country_name,
WSH_WSHRDLAB_XMLP_PKG.cf_contact_nameformula(contacts.title || ' ' || contacts.first_name || ' ' || contacts.last_name, phones.phone_number, max ( wdd.delivery_detail_id )) CF_Contact_Name,
WSH_WSHRDLAB_XMLP_PKG.cf_del_to_country_nameformula(loc1.country) CF_Del_to_Country_name,
WSH_WSHRDLAB_XMLP_PKG.cf_order_numberformula(max ( wdd.delivery_detail_id )) CF_order_number,
WSH_WSHRDLAB_XMLP_PKG.cf_ship_to_cust_nameformula(wdd.ship_to_site_use_id) CF_SHIP_TO_CUST_NAME,
WSH_WSHRDLAB_XMLP_PKG.cf_freight_carrierformula(hzp.party_name, wdd.organization_id, wnd.delivery_id) CF_FREIGHT_CARRIER,
WSH_WSHRDLAB_XMLP_PKG.CP_Contact_Phone_p CP_Contact_Phone,
WSH_WSHRDLAB_XMLP_PKG.CP_ADDRESSEE_p CP_ADDRESSEE,
WSH_WSHRDLAB_XMLP_PKG.CP_deliver_to_location_id_p CP_deliver_to_location_id,
WSH_WSHRDLAB_XMLP_PKG.CP_ship_to_address1_p CP_ship_to_address1,
WSH_WSHRDLAB_XMLP_PKG.CP_ship_to_address2_p CP_ship_to_address2,
WSH_WSHRDLAB_XMLP_PKG.CP_ship_to_address3_p CP_ship_to_address3,
WSH_WSHRDLAB_XMLP_PKG.CP_ship_to_csz_p CP_ship_to_csz,
WSH_WSHRDLAB_XMLP_PKG.CP_ship_to_country_p CP_ship_to_country,
WSH_WSHRDLAB_XMLP_PKG.CP_del_to_address1_p CP_del_to_address1,
WSH_WSHRDLAB_XMLP_PKG.CP_del_to_address2_p CP_del_to_address2,
WSH_WSHRDLAB_XMLP_PKG.CP_del_to_address3_p CP_del_to_address3,
WSH_WSHRDLAB_XMLP_PKG.CP_del_to_csz_p CP_del_to_csz,
WSH_WSHRDLAB_XMLP_PKG.CP_del_to_country_p CP_del_to_country
FROM
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_locations loc,
wsh_locations loc1,
( select substrb ( party.party_name, 1, 50 ) customer_name,
cust_acct.cust_account_id customer_id
from hz_parties party,
hz_cust_accounts cust_acct
where cust_acct.party_id = party.party_id
) customers,
( select party.person_pre_name_adjunct title,
substrb ( party.person_first_name,1,40) first_name,
substrb ( party.person_last_name,1,50) last_name,
acct_role.cust_account_role_id contact_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
) contacts,
( select decode(cont_point.contact_point_type,'TLX',cont_point.telex_number,cont_point.phone_number) phone_number,
car.cust_account_role_id contact_id,
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')
and cont_point.PRIMARY_FLAG ='Y'
) phones,
wsh_carriers wca,
hz_parties hzp
WHERE
wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.container_flag IN ('N', 'Y')
AND nvl(wnd.SHIPMENT_DIRECTION, 'O') IN ('O' ,'IO')
AND wnd.delivery_type = 'STANDARD'
AND wnd.customer_id = customers.customer_id(+)
AND wnd.carrier_id = wca.carrier_id (+)
AND wca.carrier_id = hzp.party_id (+)
AND wdd.ship_to_contact_id = contacts.contact_id(+)
AND wdd.ship_to_location_id = loc.wsh_location_id
AND nvl(wdd.deliver_to_location_id,wdd.ship_to_location_id) = loc1.wsh_location_id
AND contacts.contact_id = phones.contact_id(+)
&LP_CONTAINER_ID
&LP_MASTER_FLAG
&LP_DELIVERY_ID
&LP_FREIGHT_CODE
&LP_ORGANIZATION_ID
&LP_TRIP_STOP_ID
&LP_DATE_RANGE
&LP_TRIP_ID
group by
wnd.delivery_id,
wnd.name ,
wdd.container_name,
wdd.ship_to_location_id,
wdd.deliver_to_location_id,
wdd.ship_to_site_use_id ,
customers.customer_name,
contacts.title || ' ' || contacts.first_name || ' ' || contacts.last_name,
phones.phone_number,
hzp.party_name ,
wnd.waybill,
wdd.tracking_number,
wdd.organization_id,
loc.address1,
loc.address2,
loc.address3,
loc.city || ', ' || nvl(nvl(loc.province,loc.state),loc.county) || ', ' || loc.postal_code,
loc.country,
loc1.address1,
loc1.address2,
loc1.address3 ,
loc1.city || ', ' || nvl(nvl(loc1.province,loc1.state),loc1.county) || ', ' || loc1.postal_code ,
loc1.country
order by delivery_id |
