Query's to check Order Management

-- Query to check all orders in interface with details

select h.attribute1, h.attribute10,orig_sys_document_ref,h.customer_number,h.price_list_id,r.customer_name,h.order_type,h.creation_date,h.attribute6,error_flag,booked_flag,h.request_date,h.attribute2,h.ship_to_site_int,h.payment_term,h.payment_term_id
from oe_headers_iface_all h,ra_customers r
where h.customer_number= r.customer_number
--and error_flag is null
---and error_flag ='Y'
--and h.order_type like '%PRO%'
--and h.customer_po_number in ('365')
and h.customer_number  in()
--and trunc(h.creation_date)= '10-DEC-2012'
order by customer_number,creation_date desc;
*********************************************************************************

---Update the ship_to in interface using PO#

Headers::

update oe_headers_iface_all
set ship_to_site_int='xxxx'
where orig_sys_document_ref ='xxxx;

Lines::

update oe_lines_iface_all
set global_attribute2='5465A'
where orig_sys_document_ref ='xxxxx';

*********************************************************************************
---Query to Book the Orders using Script::


declare
cursor stylprc is
select product_attr_val_disp, product_attr_value, operand from qp_list_lines_v where list_header_id=XXXX and
product_attr_value in (select distinct ordered_item_id from oe_order_lines_all
where header_id in ('XXXXX') and unit_selling_price is null);

begin
for sp in stylprc loop
begin
update oe_order_lines_all set unit_selling_price=sp.operand, unit_list_price=sp.operand where header_id in ('XXXXX')
and ordered_item_id=sp.product_attr_value and unit_selling_price is null;
end;
end loop;
end;
*********************************************************************************


-- Query to get ship to details for an order.

SELECT h.orig_sys_document_ref,
h.customer_number,
l.inventory_item_id,
l.inventory_item,
h.SHIP_TO_ADDRESS1,
h.SHIP_TO_ADDRESS2,
h.SHIP_TO_ADDRESS3,
h.SHIP_TO_ADDRESS4
FROM oe_headers_iface_all h,oe_lines_iface_all l
where h.orig_sys_document_ref =l.orig_sys_document_ref
and h.orig_sys_document_ref = 'xxxx';

*********************************************************************************

---Query to get duplicate records for a customer in interface

SELECT distinct ''''||cust_po_number||''''||',',customer_name
  FROM oe_order_headers_all l,ra_customers r
WHERE l.sold_to_org_id =r.customer_id
and cust_po_number IN (SELECT orig_sys_document_ref
                                   FROM oe_headers_iface_all                              
                                   WHERE customer_number = xxxx) ;