WSH Master Bill of Lading PDF Output

SELECT
            wt.trip_id,
            to_char(wt.trip_id) trip_id_chr,
            wt.name,
            wt.status_code trip_status,
            wpt.name Prior_Trip,
            wdi.sequence_number mbol_number,
            wsh_util_core.get_item_name(msi.inventory_item_id,
                                        msi.organization_id) vehicle_type,
            wt.vehicle_number,
            hp.party_name carrier_name,
            wt.routing_instructions,
            wt.mode_of_transport,
            wt.service_level,
            wts.stop_id,
            to_char(wts.stop_id) stop_id_chr,
            wts.stop_sequence_number,
            wts.stop_location_id,
            wl.address1,
            wl.address2,
            wl.address3,
            wl.address4,
            wl.city,
            nvl(nvl(wl.province,wl.state),wl.county) region,
            wl.postal_code,
            wl.country,
            wts.status_code,
            wts.departure_seal_code seal_code,
            wts.status_code Stop_status,
            wts.departure_gross_weight,
            wts.departure_net_weight,
            wts.weight_uom_code weight_uom,
            wts.departure_volume,
            wts.volume_uom_code volume_uom,
            FND_DATE.DATE_TO_CANONICAL(wts.planned_arrival_date) planned_arrival_date,
            FND_DATE.DATE_TO_CANONICAL(wts.actual_arrival_date) actual_arrival_date,
            FND_DATE.DATE_TO_CANONICAL(wts.planned_departure_date) planned_departure_date,
            FND_DATE.DATE_TO_CANONICAL(wts.actual_departure_date) actual_departure_date,
            wc.scac_code scac_code,
            wt.attribute1   trip_attribute1,
            wt.attribute2   trip_attribute2,
            wt.attribute3   trip_attribute3,
            wt.attribute4   trip_attribute4,
            wt.attribute5   trip_attribute5,
            wt.attribute6   trip_attribute6,
            wt.attribute7   trip_attribute7,
            wt.attribute8   trip_attribute8,
            wt.attribute9   trip_attribute9,
            wt.attribute10  trip_attribute10,
            wt.attribute11  trip_attribute11,
            wt.attribute12  trip_attribute12,
            wt.attribute13  trip_attribute13,
            wt.attribute14  trip_attribute14,
            wt.attribute15  trip_attribute15,
                WSH_WSHRDMBL_XMLP_PKG.cf_shipper_nameformula(wts.stop_id) CF_SHIPPER_NAME,
                WSH_WSHRDMBL_XMLP_PKG.cf_trip_statusformula(wt.status_code) CF_Trip_status,
                WSH_WSHRDMBL_XMLP_PKG.cf_mode_meanformula(wt.mode_of_transport) CF_Mode_mean,
                WSH_WSHRDMBL_XMLP_PKG.cf_service_levelformula(wt.service_level) CF_Service_level,
                WSH_WSHRDMBL_XMLP_PKG.cf_address_trips_stopsformula(wl.address1, wl.address2, wl.address3, wl.address4) CF_address_trips_stops,
                WSH_WSHRDMBL_XMLP_PKG.cf_stop_statusformula(wts.status_code) CF_Stop_status,
                WSH_WSHRDMBL_XMLP_PKG.cf_weight_uomformula(wts.weight_uom_code) CF_Weight_Uom,
                WSH_WSHRDMBL_XMLP_PKG.cf_volume_uomformula(wts.volume_uom_code) CF_Volume_uom
            FROM
            wsh_trips wt,
            wsh_trips wpt,
            wsh_document_instances wdi,
            hz_parties hp,
            mtl_system_items msi,
            wsh_trip_stops wts,
            wsh_locations wl,
            wsh_carriers wc
            WHERE
            wt.arrive_after_trip_id = wpt.trip_id(+)
            AND wt.trip_id = wts.trip_id
            AND wt.carrier_id = hp.party_id
            AND wt.trip_id = wdi.entity_id
            AND wdi.entity_name = 'WSH_TRIPS'
            AND wt.vehicle_item_id = msi.inventory_item_id(+)
            AND wts.stop_location_id=wl.wsh_location_id
            AND wt.vehicle_organization_id = msi.organization_id(+)
            AND wt.trip_id = :P_TRIP_ID
            AND wt.carrier_id = wc.carrier_id
            order by wts.stop_sequence_number