Search 800 + Posts

Apr 20, 2010

Queries to connect RA_CUSTOMER_TRX_ALL andOE_ORDER_HEADERS_ALL/LINES_ALL


SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from
RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h
where 1=1
and interface_header_context = 'ORDER ENTRY'
and interface_header_attribute1 = to_char(h.order_number)



SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num,
rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line
from
RA_CUSTOMER_TRX_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order = h.order_number


SELECT rl.interface_status,rl.line_number Invoice_line_num,
h.order_number, l.line_id
from RA_INTERFACE_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order_line IS NOT NULL;


3 comments:

  1. Some Update in the existing Query

    Select Rl.Customer_Trx_Line_Id,Rl.Customer_Trx_Id,Rl.Line_Number,rh.trx_number,
    Rl.Interface_Line_Attribute1,H.Order_Number, L.Line_Id, Rl.Sales_Order_Line,rl.quantity_ordered,rl.quantity_invoiced,Rl.Extended_Amount
    from
    Ra_Customer_Trx_Lines_All Rl,
    RA_CUSTOMER_TRX_ALL rh,
    oe_order_lines_all l,
    oe_order_headers_all h
    where line_type = 'LINE'
    and interface_line_context = 'ORDER ENTRY'
    and h.header_id = l.header_id
    and interface_line_attribute6 = to_char(l.line_id)
    And Interface_Line_Attribute1 = To_Char(H.Order_Number)
    and rh.customer_trx_id =rl.customer_trx_id
    and sales_order = h.order_number

    ReplyDelete