Hello!
I'm stuck and i'm a bit confused what to do.
I made a query that shows data from sales order, the query is :
"
select
oha.order_number "Order number",
oha.ordered_date "Order date",
oha.cust_po_number "PO Number",
ott.name "Order type",
ola.line_number "Line number",
msi.segment1 "Item code",
msi.description "Item name",
--wdd.lot_number "LOT number",
ola.ordered_quantity "Ordered quantity",
ola.order_quantity_uom "Ordered UOM",
oha.transactional_curr_code "Currency",
ola.unit_selling_price "Unit sell price",
(ola.ordered_quantity*ola.unit_selling_price) "Line sell price",
ola.shipping_quantity "Shipped quantity",
ola.shipping_quantity_uom "Shipped quantity UOM",
(nvl(ola.shipping_quantity,0)*ola.unit_selling_price) "Shipped price",
case when oha.transactional_curr_code = 'HUF' THEN (nvl(ola.shipping_quantity,0)*ola.unit_selling_price) else (nvl(ola.shipping_quantity,0)*ola.unit_selling_price*rcta.EXCHANGE_RATE) end "Shipped price in HUF",
ola.ACTUAL_SHIPMENT_DATE "Ship date",
extract ( YEAR FROM ola.ACTUAL_SHIPMENT_DATE) "Shipped year",
extract (MONTH FROM ola.ACTUAL_SHIPMENT_DATE) "Shipped month",
extract ( DAY FROM ola.ACTUAL_SHIPMENT_DATE ) "Shipped day",
ol.meaning "Order status",
hca.account_number "Cardcode",
bill_p.party_name "Cardname",
DECODE (bill_loc.city,
NULL, NULL,
bill_loc.city || ', '
)
|| DECODE (bill_loc.state,
NULL, bill_loc.province || ', ',
bill_loc.state || ', '
)
|| DECODE (bill_loc.postal_code,
NULL, NULL,
bill_loc.postal_code || ', '
)
|| DECODE (bill_loc.country, NULL, NULL, bill_loc.country) "Shipping address",
bill_loc.address1 "Shipping street",
ship_loc.city "Shipping city",
ship_loc.country "Shipping country code",
ft.territory_short_name "Shipping country",
(select name
from ra_salesreps_all
where salesrep_id = oha.salesrep_id
and org_id = oha.org_id) "Sales person name"
from oe_order_headers_all oha,
oe_order_lines_all ola,
oe_transaction_types_tl ott,
mtl_system_items_b msi,
hz_cust_site_uses_all bill_su,
hz_party_sites bill_ps,
hz_locations bill_loc,
hz_cust_acct_sites_all bill_cas,
hz_cust_accounts bill_ca,
hz_parties bill_p,
hz_cust_site_uses_all ship_su,
hz_party_sites ship_ps,
hz_locations ship_loc,
hz_cust_acct_sites_all ship_cas,
-- wsh_deliverables_v wd
fnd_territories_tl ft,
oe_lookups ol,
hz_cust_accounts_all hca,
ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla
--wsh_delivery_details wdd
where oha.header_id = ola.header_id
and bill_p.party_id = hca.party_id
--and wdd.source_header_id = ola.header_id
--and wdd.source_line_id = ola.line_id
and oha.ORDER_TYPE_ID = ott.transaction_type_id
and ott.language = userenv('LANG')
and ola.inventory_item_id = msi.inventory_item_id
and ola.ship_from_org_id = msi.organization_id
and oha.invoice_to_org_id = bill_su.site_use_id(+)
and bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
and bill_cas.party_site_id = bill_ps.party_site_id(+)
and bill_loc.location_id(+) = bill_ps.location_id
and bill_cas.cust_account_id = bill_ca.cust_account_id
and bill_ca.party_id = bill_p.party_id
and oha.ship_to_org_id = ship_su.site_use_id(+)
and ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
and ship_cas.party_site_id = ship_ps.party_site_id(+)
and ship_loc.location_id(+) = ship_ps.location_id
and ship_loc.country = ft.territory_code
and ft.language = userenv('LANG')
and ol.lookup_type = 'FLOW_STATUS'
and ol.lookup_code = oha.flow_status_code
and ola.shipping_quantity IS NOT NULL
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.interface_line_attribute6 = TO_CHAR (ola.line_id)
AND rctla.interface_line_attribute1 = TO_CHAR (oha.order_number)
--and oha.order_number = 1000914
"
The query works perfect except one case.
If i have sublines , not classical lines ( see the attachments ) in that case the query only show the first subline value.
Also if I connect wsh_deliverables_v tables to get the LOT numbers the lines will multiple and i have no idea why
Thank you for your help in advanced,
Zsolt