WSH Mailing Label

Description
Categories: BI Publisher, Logistics
Application: Shipping Execution
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 NameSQL textValidation
LPN
 
LOV Oracle
Delivery Name
 
LOV Oracle
Warehouse
 
LOV Oracle
Freight Carrier
 
LOV Oracle
Stop Planned Depart Date (High)
 
Date
Stop Planned Depart Date (Low)
 
Date
Trip Stop
 
LOV Oracle
Trip
 
LOV Oracle