WSH Mailing Label
Description
Categories: BI Publisher
Application: Shipping Execution
Source: Mailing Label (XML)
Short Name: WSHRDLAB_XML
DB package: WSH_WSHRDLAB_XMLP_PKG
Source: Mailing Label (XML)
Short Name: WSHRDLAB_XML
DB package: WSH_WSHRDLAB_XMLP_PKG
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Trip |
|
LOV Oracle | |
Trip Stop |
|
LOV Oracle | |
Stop Planned Depart Date (Low) |
|
Date | |
Stop Planned Depart Date (High) |
|
Date | |
Freight Carrier |
|
LOV Oracle | |
Warehouse |
|
LOV Oracle | |
Delivery Name |
|
LOV Oracle | |
LPN |
|
LOV Oracle |