VIEW: APPS.WSH_BOLS_RD_V

Source

View Text - Preformatted

SELECT wdi.rowid, wdi.sequence_number, wdi.document_instance_id, wdl.booking_number, wdl.service_contract, wdl.carrier_export_ref, wdl.shipper_export_ref, wdl.doc_notify_party, wnd.problem_contact_reference, wdl.port_of_loading, wdl.port_of_discharge, wdl.aetc_number, wdl.shipper_signed_by, wdl.shipper_date, wdl.carrier_signed_by, wdl.carrier_date, wdl.pod_by, wdl.pod_date, wdl.booking_office, wdl.doc_issue_office, wdl.doc_issued_by, wdl.doc_date_issued, wnd.description, wdl.shipper_hm_by, wdl.shipper_hm_date, wdl.carrier_hm_by, wdl.carrier_hm_date, wnd.waybill, wnd.additional_shipment_info, wnd.fob_code, wnd.dock_code, wt.ship_method_code, wt.vehicle_number, wnd.delivery_id, wnd.name, wts1.stop_location_id, wsloc1.location_code||':'||nvl(wsloc1.city, substrb(wsloc1.address1, 1, 60)), wts2.stop_location_id, wsloc2.location_code||':'||nvl(wsloc2.city, substrb(wsloc2.address1, 1, 60)), wt.carrier_id, wt.ship_method_code, wt.trip_id, wt.name, wdi.document_type, wdi.status, wdi.final_print_date, wdi.entity_name, wdi.entity_id, wdi.doc_sequence_category_id, wdl.pod_flag, wnd.reason_of_transport, wnd.cod_amount, wnd.cod_currency_code, wnd.cod_remit_to, wnd.cod_charge_paid_by, wdl.bill_freight_to, wnd.carried_by, wdl.delivery_leg_id, wdl.sequence_number, wdl.loading_order_flag, wdl.gross_weight, wdl.net_weight, wdl.weight_uom_code, wdl.volume, wdl.volume_uom_code, wdl.load_tender_status, wsloc1.location_code||':'||nvl(wsloc1.city, substrb(wsloc1.address1, 1, 60)), hou.name, wsloc1.location_code||':'||nvl(wsloc1.city, substrb(wsloc1.address1, 1, 60)), wsloc1.address1, wsloc1.address2, wsloc1.address3, wsloc1.city, nvl(nvl(wsloc1.province, wsloc1.state), wsloc1.county), wsloc1.postal_code , wsloc1.country, NULL vendor_name, NULL description, NULL address_line_1, NULL address_line_2, NULL address_line_3 , NULL town_or_city, NULL, NULL postal_code, NULL country, substrb ( cust.party_name, 1, 50 ) /* customer_name */ , wsloc2.location_code||':'||nvl(wsloc2.city, substrb(wsloc2.address1, 1, 60)), wsloc2.address1, wsloc2.address2, wsloc2.address3, wsloc2.city, nvl(nvl(wsloc2.province, wsloc2.state), wsloc2.county), wsloc2.postal_code , wsloc2.country, wnd.organization_id, wt.carrier_reference_number, wt.seal_code trip_seal_code, wts1.departure_seal_code FROM wsh_document_instances wdi, wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_trip_stops wts1, wsh_trip_stops wts2, wsh_trips wt, hr_organization_units hou, ( select party.party_name, cust_acct.cust_account_id from hz_parties party, hz_cust_accounts cust_acct where cust_acct.party_id = party.party_id ) cust, wsh_locations wsloc1, wsh_locations wsloc2 WHERE wdi.entity_id = wdl.delivery_leg_id AND wdi.entity_name = 'WSH_DELIVERY_LEGS' AND wdi.document_type = 'BOL' AND wdi.status <> 'CANCELLED' AND wnd.delivery_id = wdl.delivery_id AND wdl.pick_up_stop_id = wts1.stop_id AND wdl.drop_off_stop_id = wts2.stop_id AND wts1.trip_id = wt.trip_id AND wts1.stop_location_id = wsloc1.wsh_location_id(+) AND wts2.stop_location_id = wsloc2.wsh_location_id(+) AND wnd.organization_id = hou.organization_id AND wnd.customer_id = cust.cust_account_id(+)

View Text - HTML Formatted

SELECT WDI.ROWID
, WDI.SEQUENCE_NUMBER
, WDI.DOCUMENT_INSTANCE_ID
, WDL.BOOKING_NUMBER
, WDL.SERVICE_CONTRACT
, WDL.CARRIER_EXPORT_REF
, WDL.SHIPPER_EXPORT_REF
, WDL.DOC_NOTIFY_PARTY
, WND.PROBLEM_CONTACT_REFERENCE
, WDL.PORT_OF_LOADING
, WDL.PORT_OF_DISCHARGE
, WDL.AETC_NUMBER
, WDL.SHIPPER_SIGNED_BY
, WDL.SHIPPER_DATE
, WDL.CARRIER_SIGNED_BY
, WDL.CARRIER_DATE
, WDL.POD_BY
, WDL.POD_DATE
, WDL.BOOKING_OFFICE
, WDL.DOC_ISSUE_OFFICE
, WDL.DOC_ISSUED_BY
, WDL.DOC_DATE_ISSUED
, WND.DESCRIPTION
, WDL.SHIPPER_HM_BY
, WDL.SHIPPER_HM_DATE
, WDL.CARRIER_HM_BY
, WDL.CARRIER_HM_DATE
, WND.WAYBILL
, WND.ADDITIONAL_SHIPMENT_INFO
, WND.FOB_CODE
, WND.DOCK_CODE
, WT.SHIP_METHOD_CODE
, WT.VEHICLE_NUMBER
, WND.DELIVERY_ID
, WND.NAME
, WTS1.STOP_LOCATION_ID
, WSLOC1.LOCATION_CODE||':'||NVL(WSLOC1.CITY
, SUBSTRB(WSLOC1.ADDRESS1
, 1
, 60))
, WTS2.STOP_LOCATION_ID
, WSLOC2.LOCATION_CODE||':'||NVL(WSLOC2.CITY
, SUBSTRB(WSLOC2.ADDRESS1
, 1
, 60))
, WT.CARRIER_ID
, WT.SHIP_METHOD_CODE
, WT.TRIP_ID
, WT.NAME
, WDI.DOCUMENT_TYPE
, WDI.STATUS
, WDI.FINAL_PRINT_DATE
, WDI.ENTITY_NAME
, WDI.ENTITY_ID
, WDI.DOC_SEQUENCE_CATEGORY_ID
, WDL.POD_FLAG
, WND.REASON_OF_TRANSPORT
, WND.COD_AMOUNT
, WND.COD_CURRENCY_CODE
, WND.COD_REMIT_TO
, WND.COD_CHARGE_PAID_BY
, WDL.BILL_FREIGHT_TO
, WND.CARRIED_BY
, WDL.DELIVERY_LEG_ID
, WDL.SEQUENCE_NUMBER
, WDL.LOADING_ORDER_FLAG
, WDL.GROSS_WEIGHT
, WDL.NET_WEIGHT
, WDL.WEIGHT_UOM_CODE
, WDL.VOLUME
, WDL.VOLUME_UOM_CODE
, WDL.LOAD_TENDER_STATUS
, WSLOC1.LOCATION_CODE||':'||NVL(WSLOC1.CITY
, SUBSTRB(WSLOC1.ADDRESS1
, 1
, 60))
, HOU.NAME
, WSLOC1.LOCATION_CODE||':'||NVL(WSLOC1.CITY
, SUBSTRB(WSLOC1.ADDRESS1
, 1
, 60))
, WSLOC1.ADDRESS1
, WSLOC1.ADDRESS2
, WSLOC1.ADDRESS3
, WSLOC1.CITY
, NVL(NVL(WSLOC1.PROVINCE
, WSLOC1.STATE)
, WSLOC1.COUNTY)
, WSLOC1.POSTAL_CODE
, WSLOC1.COUNTRY
, NULL VENDOR_NAME
, NULL DESCRIPTION
, NULL ADDRESS_LINE_1
, NULL ADDRESS_LINE_2
, NULL ADDRESS_LINE_3
, NULL TOWN_OR_CITY
, NULL
, NULL POSTAL_CODE
, NULL COUNTRY
, SUBSTRB ( CUST.PARTY_NAME
, 1
, 50 ) /* CUSTOMER_NAME */
, WSLOC2.LOCATION_CODE||':'||NVL(WSLOC2.CITY
, SUBSTRB(WSLOC2.ADDRESS1
, 1
, 60))
, WSLOC2.ADDRESS1
, WSLOC2.ADDRESS2
, WSLOC2.ADDRESS3
, WSLOC2.CITY
, NVL(NVL(WSLOC2.PROVINCE
, WSLOC2.STATE)
, WSLOC2.COUNTY)
, WSLOC2.POSTAL_CODE
, WSLOC2.COUNTRY
, WND.ORGANIZATION_ID
, WT.CARRIER_REFERENCE_NUMBER
, WT.SEAL_CODE TRIP_SEAL_CODE
, WTS1.DEPARTURE_SEAL_CODE
FROM WSH_DOCUMENT_INSTANCES WDI
, WSH_NEW_DELIVERIES WND
, WSH_DELIVERY_LEGS WDL
, WSH_TRIP_STOPS WTS1
, WSH_TRIP_STOPS WTS2
, WSH_TRIPS WT
, HR_ORGANIZATION_UNITS HOU
, ( SELECT PARTY.PARTY_NAME
, CUST_ACCT.CUST_ACCOUNT_ID
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID ) CUST
, WSH_LOCATIONS WSLOC1
, WSH_LOCATIONS WSLOC2
WHERE WDI.ENTITY_ID = WDL.DELIVERY_LEG_ID
AND WDI.ENTITY_NAME = 'WSH_DELIVERY_LEGS'
AND WDI.DOCUMENT_TYPE = 'BOL'
AND WDI.STATUS <> 'CANCELLED'
AND WND.DELIVERY_ID = WDL.DELIVERY_ID
AND WDL.PICK_UP_STOP_ID = WTS1.STOP_ID
AND WDL.DROP_OFF_STOP_ID = WTS2.STOP_ID
AND WTS1.TRIP_ID = WT.TRIP_ID
AND WTS1.STOP_LOCATION_ID = WSLOC1.WSH_LOCATION_ID(+)
AND WTS2.STOP_LOCATION_ID = WSLOC2.WSH_LOCATION_ID(+)
AND WND.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND WND.CUSTOMER_ID = CUST.CUST_ACCOUNT_ID(+)