SELECT d.party_name customer_name, d.party_number customer_number,
c.account_number customer_account_number,
a.purchase_order customer_po, a.trx_number invoice_number,
TO_CHAR (a.trx_date, 'DD-MON-YYYY') invoice_date,
b.sales_order sales_order_number,
TRIM (b.sales_order_line || '.' || TRIM (interface_line_attribute12)
) line_number,
msib.segment1 || '.' || segment2 item_code,
msib.description item_description, b.quantity_ordered,
b.quantity_invoiced invoice_qty, b.unit_selling_price,
b.extended_amount invoice_line_amount, b.sales_order_date order_date,
(SELECT price_list
FROM oe_order_headers_v
WHERE order_number = a.interface_header_attribute1) price_list,
(SELECT agr_name
FROM oe_order_headers_v oohv, qpbv_agreements qagr
WHERE oohv.order_number = a.interface_header_attribute1
AND oohv.agreement_id = qagr.agreement_id) agreement,
(SELECT order_type
FROM oe_order_headers_v
WHERE order_number = a.interface_header_attribute1) order_type,
(SELECT DISTINCT b.resource_name
FROM ra_salesreps_all a,
jtf_rs_all_resources_vl b,
oe_order_headers_all c
WHERE a.resource_id = b.resource_id
AND a.salesrep_id = c.salesrep_id
AND c.order_number = a.interface_header_attribute1)
sales_person
FROM ra_customer_trx_all a,
ra_customer_trx_lines_all b,
hz_cust_accounts_all c,
hz_parties d,
mtl_system_items_b msib
WHERE interface_header_context = 'ORDER ENTRY'
AND a.customer_trx_id = b.customer_trx_id
AND a.bill_to_customer_id = c.cust_account_id
AND c.party_id = d.party_id
AND b.inventory_item_id = msib.inventory_item_id
AND a.interface_header_attribute10 = msib.organization_id
--and b.sales_order in ('50101')
ORDER BY sales_order_date, 7, 8
No comments :
Post a Comment