ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F53C3869E053B46B63588994 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 Y WSH WSHRDLABX_XML US WSH Mailing Label PDF Output Application: Shipping Execution Source: Mailing Label PDF Output (XML) Short Name: WSHRDLABX_XML DB package: WSH_WSHRDLAB_XMLP_PKG BI Publisher &lp_container_id &lp_date_range &lp_delivery_id &lp_freight_code &lp_master_flag &lp_organization_id &lp_trip_id &lp_trip_stop_id :cp_addressee :cp_contact_phone :cp_del_to_address1 :cp_del_to_address2 :cp_del_to_address3 :cp_del_to_country :cp_del_to_csz :cp_deliver_to_location_id :cp_departure_date_high :cp_ship_to_address1 :cp_ship_to_address2 :cp_ship_to_address3 :cp_ship_to_country :cp_ship_to_csz :lp_container_id :lp_date_range :lp_delivery_id :lp_freight_code :lp_master_flag :lp_organization_id :lp_trip_id :lp_trip_stop_id :p_conc_request_id :p_container_id :p_delivery_id :p_departure_date_high :p_departure_date_low :p_freight_code :p_no_data_found :p_organization_id :p_standalone :p_trip_id :p_trip_stop_id 1 10 :p_trip_id LOV Oracle WSH_SRS_TRIPS Y select trip_id id, name value, null description from wsh_trips where nvl(shipments_type_flag,'O') in ('O','M') order by name US Trip 2 20 :p_trip_stop_id LOV Oracle WSH_SRS_TRIP_REL_STOPS_M Y select stop_id id, stop_description value, location_description description from wsh_srs_trip_stops_v where trip_id=:$flex$.wsh_srs_trips order by stop_description US Trip Stop 3 30 :p_departure_date_low Date US Stop Planned Depart Date (Low) 4 40 :p_departure_date_high Date US Stop Planned Depart Date (High) 5 50 :p_freight_code LOV Oracle WSH_SRS_FREIGHT_CARRIER Y Y select lookup_code id, meaning value, description description from fnd_lookup_values_vl where view_application_id=3 and lookup_type='SHIP_METHOD' and exists (select 1 from wsh_carrier_services wcs,wsh_carriers wc where wcs.ship_method_code=lookup_code and wcs.carrier_id=wc.carrier_id and nvl(wc.generic_flag,'N')='N') order by lookup_code US Freight Carrier 6 60 :p_organization_id LOV Oracle WSH_SRS_ORGANIZATIONS Y select organization_id id, organization_code value, organization_name description from org_organization_definitions order by organization_code select organization_code from org_organization_definitions where organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') Y US Warehouse 7 70 :p_delivery_id LOV Oracle WSH_SRS_DELIVERIES Y select delivery_id id, name value, null description from wsh_new_deliveries where organization_id=:$flex$.wsh_srs_organizations and nvl(shipment_direction,'O') in ('O','IO') and delivery_type='STANDARD' order by name US Delivery Name 8 80 :p_container_id LOV Oracle WSH_SRS_CONTAINERS Y Y select wdd.delivery_detail_id id, wdd.container_name value, null description from wsh_delivery_details wdd,wsh_delivery_assignments wda,wsh_new_deliveries wnd,wsh_delivery_legs wdl,wsh_trip_stops wts,wsh_trips wt where nvl(wdd.line_direction,'O') in ('O','IO') and wdd.container_flag='Y' and wda.delivery_detail_id=wdd.delivery_detail_id and wda.delivery_id=wnd.delivery_id(+) and wnd.delivery_id=wdl.delivery_id (+) and wdl.pick_up_stop_id=wts.stop_id (+) and wts.trip_id=wt.trip_id (+) order by value,description US LPN