WSH Mailing Label

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