Tuesday, June 10, 2014

Order Shipment Details and move order details in order managment of oracle Apps

SELECT DISTINCT wdd.batch_id move_order_no,oha.order_number, oha.orig_sys_document_ref ofs_number
               ,ot.NAME, wt.trip_id, wt.NAME trip_name
               ,wt.attribute3 shipment_id
            FROM wsh.wsh_trips wt
               ,wsh.wsh_trip_stops wts
               ,wsh.wsh_delivery_legs wdl
               ,wsh.wsh_delivery_assignments wda
               ,wsh.wsh_delivery_details wdd
               ,ont.oe_order_headers_all oha
               ,ont.oe_order_lines_all ohl
               ,ont.oe_transaction_types_tl ot                        --     ,
          WHERE oha.header_id = ohl.header_id
            AND wt.trip_id = wts.trip_id
            AND wdl.drop_off_stop_id = wts.stop_id
            AND wda.delivery_id = wdl.delivery_id
            AND wda.delivery_detail_id = wdd.delivery_detail_id
            AND wdd.source_header_id = oha.header_id
            AND ot.transaction_type_id = oha.order_type_id
            AND ot.LANGUAGE = 'US'
           -- AND wt.status_code = 'OP'
            and oha.order_number = 4736944

No comments:

Post a Comment