oracel ar

SELECT rc.customer_number, rc.customer_name,
rca.doc_sequence_value AS "傳票號碼", rca.trx_number AS "立帳編號",
rca.trx_date AS "立帳日期", rca.exchange_rate_type AS "匯率轉換型態",
rca.exchange_date AS "匯率轉換日期", rca.exchange_rate AS "匯率",
rca.invoice_currency_code AS "立帳幣別",
oh.transactional_curr_code AS "訂單幣別",
rca.cust_trx_type_id AS "立帳類型",
rca.batch_source_id AS "立帳來源", rcl.line_number AS "項次",
rcl.inventory_item_id AS "立帳料編", msi.segment1 "料號",
rcl.description AS "說明", rcl.quantity_ordered AS "訂單數量",
rcl.quantity_invoiced AS "立帳數量",
rcl.unit_selling_price AS "立帳單價",
ol.unit_selling_price AS "訂單單價", ol.tax_code AS "訂單稅別",
tv.tax_code AS "立帳稅別", rcl.sales_order AS "訂單單號",
rcl.sales_order_line AS "訂單明細項次",
rcl.sales_order_date AS "訂單日期",
rcl.gross_extended_amount AS "含稅金額",
rcl.interface_line_attribute3 AS "出貨號碼",
wnd.waybill AS "江氏出貨號",
ROUND (NVL (rcl.extended_amount, 0), 2) AS "原幣金額",
ROUND (NVL (rca.exchange_rate, 1), 0)
* NVL (rcl.extended_amount, 0) AS "轉換金額"
FROM ra_customer_trx_all rca,
ra_customer_trx_lines_all rcl,
mtl_system_items_b msi,
ra_customers rc,
oe_order_headers_all oh,
oe_order_lines_all ol,
oe_transaction_types_tl ott,
ar_vat_tax_all tv,
wsh_new_deliveries wnd
WHERE rca.customer_trx_id = rcl.customer_trx_id
AND rca.bill_to_customer_id = rc.customer_id
AND rcl.sales_order_source = 'ORDER ENTRY'
AND rcl.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = '105'
AND oh.header_id = ol.header_id
AND rcl.interface_line_context = 'ORDER ENTRY'
AND rcl.interface_line_attribute2 = ott.NAME
AND rcl.line_type = 'LINE'
AND rcl.interface_line_attribute6 = TO_CHAR (ol.line_id)
AND rcl.vat_tax_id = tv.vat_tax_id
AND rcl.interface_line_attribute3 = wnd.delivery_id
AND TO_CHAR (rca.trx_date, 'yyyy/mm/dd') >= '2007/03/01'
AND TO_CHAR (rca.trx_date, 'yyyy/mm/dd') <= '2007/03/31'
ORDER BY rc.customer_number, rcl.sales_order_line