VIEW: APPS.FTE_FPA_BOLS_V

Source

View Text - Preformatted

SELECT wdi.entity_id, wt.trip_id, wdi.sequence_number bill_of_lading_number, wdl.doc_date_issued bill_of_lading_date, wnd.initial_pickup_date, wnd.ultimate_dropoff_date, wdl.gross_weight, wdl.weight_uom_code, wdl.volume, wdl.volume_uom_code, hou.name shipper_name, hrl.address_line_1 shipper_address1, hrl.address_line_2 shipper_address2, hrl.address_line_3 shipper_address3, hrl.town_or_city shipper_city, hrl.telephone_number_1 shipper_phone_number, hrl.telephone_number_2 shipper_fax, hrl.postal_code shipper_postal_code, hrl.country shipper_country, nvl(hp.person_first_name, hp.party_name) customer_name, hp.person_last_name , hp.party_id, wloc2.address1 customer_address1, wloc2.address2 customer_address2, wloc2.address3 customer_address3, wloc2.city customer_city, wloc2.state customer_state, wloc2.postal_code customer_postal_code, wloc2.country customer_country, hrl.region_2 shipper_state 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, HZ_CUST_ACCOUNTS hca, HZ_PARTIES hp, HR_LOCATIONS_ALL hrl, WSH_LOCATIONS wloc2 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 = hrl.location_id AND wts2.stop_location_id = wloc2.wsh_location_id AND wnd.organization_id = hou.organization_id AND wnd.customer_id = hca.cust_account_Id(+) AND hca.party_id = hp.party_id(+) and wt.mode_of_transport = 'TRUCK' Union select -9999,to_number(null),null,to_date(null),to_date(null),to_date(null),to_number(null),null,to_number(null),null,null,null,null,null,null,null,null,null,null,null,null,to_number(null),null,null,null,null,null,null,null,null from dual

View Text - HTML Formatted

SELECT WDI.ENTITY_ID
, WT.TRIP_ID
, WDI.SEQUENCE_NUMBER BILL_OF_LADING_NUMBER
, WDL.DOC_DATE_ISSUED BILL_OF_LADING_DATE
, WND.INITIAL_PICKUP_DATE
, WND.ULTIMATE_DROPOFF_DATE
, WDL.GROSS_WEIGHT
, WDL.WEIGHT_UOM_CODE
, WDL.VOLUME
, WDL.VOLUME_UOM_CODE
, HOU.NAME SHIPPER_NAME
, HRL.ADDRESS_LINE_1 SHIPPER_ADDRESS1
, HRL.ADDRESS_LINE_2 SHIPPER_ADDRESS2
, HRL.ADDRESS_LINE_3 SHIPPER_ADDRESS3
, HRL.TOWN_OR_CITY SHIPPER_CITY
, HRL.TELEPHONE_NUMBER_1 SHIPPER_PHONE_NUMBER
, HRL.TELEPHONE_NUMBER_2 SHIPPER_FAX
, HRL.POSTAL_CODE SHIPPER_POSTAL_CODE
, HRL.COUNTRY SHIPPER_COUNTRY
, NVL(HP.PERSON_FIRST_NAME
, HP.PARTY_NAME) CUSTOMER_NAME
, HP.PERSON_LAST_NAME
, HP.PARTY_ID
, WLOC2.ADDRESS1 CUSTOMER_ADDRESS1
, WLOC2.ADDRESS2 CUSTOMER_ADDRESS2
, WLOC2.ADDRESS3 CUSTOMER_ADDRESS3
, WLOC2.CITY CUSTOMER_CITY
, WLOC2.STATE CUSTOMER_STATE
, WLOC2.POSTAL_CODE CUSTOMER_POSTAL_CODE
, WLOC2.COUNTRY CUSTOMER_COUNTRY
, HRL.REGION_2 SHIPPER_STATE
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
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, HR_LOCATIONS_ALL HRL
, WSH_LOCATIONS WLOC2
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 = HRL.LOCATION_ID
AND WTS2.STOP_LOCATION_ID = WLOC2.WSH_LOCATION_ID
AND WND.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND WND.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID = HP.PARTY_ID(+)
AND WT.MODE_OF_TRANSPORT = 'TRUCK' UNION SELECT -9999
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM DUAL