|
SELECT a.business_unit , a.demand_source , a.source_bus_unit , a.order_no , a.order_int_line_no , a.sched_line_nbr , a.inv_item_id , a.demand_line_no , a.ship_id , a.ship_id_est , a.ship_cust_name1 , a.ship_to_cust_id , adr.cust_contact , a.carrier_id , a.ship_type_id , ' ' , %subrec(address_sbr2, adr) , %DatePart(a.ship_dttm) , 0 , 0 , a.net_unit_price , 0 , a.bill_of_lading , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , 0 , c.ship_cntr_id , b.ship_length , b.ship_height , b.ship_width , b.gross_weight , b.unit_measure_wt , b.unit_measure_dim , b.oversized , b.add_handling , ' ' , 0 , 0 , a.unit_measure_vol , ' ' , a.unit_measure_ship , a.freight_terms , ' ' , a.route_cd , a.cntrct_id , a.frt_rule_cd , d.freight_vendor , ' ' , a.qty_pack_base , b.parent_ship_cntr , b.gross_weight_user , b.override , b.frt_ref_no , b.pro_number , b.arrival_date , 0 , 0 , d.interlink_dll FROM ps_ship_cntr_hdr b , ps_ship_cntr_inv c , ps_carrier_id d , PS_IN_DEMAND A LEFT OUTER JOIN PS_IN_DEMAND_ADDR ADR ON %Join(common_keys, in_demand a , in_demand_addr Adr) WHERE a.business_unit = c.business_unit AND a.demand_source = c.demand_source AND a.source_bus_unit = c.source_bus_unit AND a.order_no = c.order_no AND a.order_int_line_no = c.order_int_line_no AND a.sched_line_nbr = c.sched_line_nbr AND a.inv_item_id = c.inv_item_id AND a.demand_line_no = c.demand_line_no AND b.business_unit = c.business_unit AND b.ship_cntr_id = c.ship_cntr_id AND b.parent_ship_cntr = ' ' AND a.demand_source = 'OM' AND ((a.in_fulfill_state BETWEEN '50' AND '60') OR (A.in_fulfill_state = '70' AND omb_process_date IS NOT NULL)) AND a.carrier_id = d.carrier_id AND d.setid = ( SELECT e.setid FROM ps_set_cntrl_rec e WHERE e.setcntrlvalue = a.business_unit AND e.recname = 'CARRIER_ID') AND d.effdt=( SELECT MAX(effdt) FROM ps_carrier_id WHERE d.setid=setid AND d.carrier_id=carrier_id AND effdt<=%CurrentDateIn) AND d.eff_status='A'
|
BUSINESS_UNIT