|
SELECT d.business_unit , d.demand_source , d.source_bus_unit , d.order_no , d.order_int_line_no , d.sched_line_nbr , d.inv_item_id , d.demand_line_no , d.location , d.destin_bu , d.ship_to_cust_id , d.ship_id , d.load_id , d.carrier_id , d.ship_type_id , d.route_cd , m.descr , %DatePart(d.sched_dttm) , %TimePart(d.sched_dttm) , d.unit_of_measure , d.unit_measure_ship , d.qty_requested , d.qty_requested_base , d.qty_allocated , d.qty_alloc_base , d.qty_picked , d.qty_pick_base , d.qty_pick_ovr_base , d.qty_shipped_conv , d.qty_ship_base , d.last_qty_ship , d.last_qty_ship_base , d.qty_promised_base , d.qty_pack_base , ' ' , 'SH' , i.inv_item_weight , i.inv_item_volume , i.unit_measure_wt , i.unit_measure_vol , m.unit_measure_std , pick_batch_id , d.hold_flag , d.qty_backorder , d.qty_backorder_base , d.in_fulfill_state , d.in_zero_picked_flg , d.export , d.sched_dttm , d.ship_early_dttm , d.address_seq_num , d.addr_ovrd_level , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , d.ship_cust_name1 , d.ship_cust_name2 , d.ship_dttm , d.interunit_flg , d.interunit_id , d.interunit_line , d.freight_terms , d.sched_arrv_dttm , d.request_date , d.route_stop_nbr , d.bill_of_lading , d.single_ship_flag , d.parent_prod_id , '31' , d.lot_alloc_flg , d.optional_ship , d.delivery_id FROM ps_in_demand d , PS_MASTER_ITEM_TBL m , ps_inv_items i WHERE m.setid = ( SELECT setid FROM ps_set_cntrl_rec sc WHERE setcntrlvalue = d.business_unit AND recname = 'MASTER_ITEM_TBL') AND m.inv_item_id = d.inv_item_id AND d.in_fulfill_state BETWEEN '20' AND '70' AND i.setid = ( SELECT setid FROM ps_set_cntrl_rec sc WHERE setcntrlvalue = d.business_unit AND recname = 'INV_ITEMS') AND i.inv_item_id = d.inv_item_id AND i.effdt = ( SELECT MAX(effdt) FROM ps_inv_items i2 WHERE i2.inv_item_id = i.inv_item_id AND i2.setid = i.setid AND i2.effdt <= %CurrentDateIn) AND NOT EXISTS ( SELECT 'X' FROM ps_in_demand_addr a WHERE d.business_unit = a.business_unit AND d.demand_source = a.demand_source AND d.source_bus_unit = a.source_bus_unit AND d.order_no = a.order_no AND d.order_int_line_no = a.order_int_line_no AND d.sched_line_nbr = a.sched_line_nbr AND d.inv_item_id = a.inv_item_id AND d.demand_line_no = a.demand_line_no)
|
BUSINESS_UNIT