SELECT
DISTINCT
oeh.flow_status_code header_status,
oel.flow_status_code line_status,
DECODE (wdd.released_status,
'S', 'SUBMITTED FOR RELEASE',
'R', 'READY TO RELEASE',
'B', 'BACKORDERED',
'Y', 'STAGED',
'C', 'SHIPPED',
'D', 'CANCELLED')
released_status,
nvl(oel.invoice_interface_status_code, 'NO') ar_interfaced,
oeh.creation_date,
(SELECT user_name
FROM apps.fnd_user
WHERE user_id = oeh.created_by)
created_by,
(SELECT user_name
FROM apps.fnd_user
WHERE user_id = oeh.last_updated_by)
last_upd_by,
(SELECT name
FROM apps.oe_order_sources
WHERE order_source_id = oeh.order_source_id)
hdr_source,
oeh.header_id,
oeh.org_id header_org_id,
(SELECT hou.name
FROM hr_operating_units hou
WHERE hou.organization_id = oeh.org_id)
header_ou,
oeh.order_number,
(SELECT ott.name
FROM apps.oe_transaction_types_tl ott
WHERE ott.transaction_type_id = oeh.order_type_id)
order_type,
oeh.cust_po_number,
oeh.fob_point_code,
oeh.shipping_method_code,
(SELECT name
FROM apps.ra_salesreps_all rsa
WHERE rsa.salesrep_id = oeh.salesrep_id)
salesrep_name,
oeh.transactional_curr_code trx_curr,
oeh.ship_to_contact_id, -- contact_id in ra_contacts, ship to contact
oeh.sold_to_contact_id, -- contact_id in ra_contacts, customer level contact
oeh.ship_to_org_id, -- site_use_id in hz_cust_site_uses_all
oeh.sold_to_org_id, -- cust_account_id in hz_cust_accounts
oel.creation_date line_creation_date,
oel.last_update_date line_upd_date,
(SELECT user_name
FROM apps.fnd_user
WHERE user_id = oel.last_updated_by)
line_upd_by,
oel.line_id,
RTRIM (
oel.line_number
|| '.'
|| oel.shipment_number
|| '.'
|| oel.option_number
|| '.'
|| oel.component_number,
'.')
line_number,
oel.org_id line_org_id,
(SELECT hou.name
FROM hr_operating_units hou
WHERE hou.organization_id = oel.org_id)
line_ou,
(SELECT ROUND (SUM (cic.item_cost), 2)
FROM apps.cst_item_costs cic
WHERE cic.inventory_item_id = MSI.inventory_item_id
AND cic.organization_id = MSI.organization_id
AND cic.cost_type_id = 1)
cogs,
oel.unit_selling_price usp,
oel.tax_code,
oel.tax_value,
oel.ordered_quantity ord_qty,
(SELECT ott.name
FROM apps.oe_transaction_types_tl ott
WHERE ott.transaction_type_id = oel.line_type_id)
line_type,
oel.schedule_ship_date,
oel.promise_date,
oel.latest_acceptable_date latest_acc_date,
oel.request_date,
oel.request_id,
(SELECT a.order_number
FROM apps.oe_order_headers_all a, apps.oe_order_lines_all b
WHERE a.header_id = b.header_id AND b.line_id = oel.attribute17)
icso_number,
oel.attribute17 icso_line_id,
(SELECT RTRIM (
oola.line_number
|| '.'
|| oola.shipment_number
|| '.'
|| oola.option_number
|| '.'
|| oola.component_number,
'.')
FROM apps.oe_order_lines_all oola
WHERE oola.line_id = oel.attribute17)
icso_line_num,
NVL (oel.attribute18, 'EXTERNAL SO') attribute18,
oel.attribute19 pto_model,
oel.global_attribute1 flag_3A4,
oeh.Global_Attribute3 hdr_attr3,
oel.Global_Attribute3 line_attr3,
oeh.Global_Attribute4 hdr_attr4,
oel.Global_Attribute4 line_attr4,
oel.booked_flag,
oel.open_flag,
oeh.booked_date,
(SELECT set_name
FROM apps.oe_sets
WHERE set_id = oel.ship_set_id)
ship_set_name,
oel.subinventory,
oel.top_model_line_id,
msi.item_type,
msi.inventory_item_id item_id,
msi.segment1 item_num,
msi.shippable_item_flag,
(SELECT ood.organization_name
FROM apps.org_organization_definitions ood
WHERE ood.organization_id = msi.default_shipping_org)
default_shipping_org,
oel.ship_from_org_id,
(SELECT ood.organization_name
FROM apps.org_organization_definitions ood
WHERE ood.organization_id = oel.ship_from_org_id)
ship_from_org,
wdd.last_update_date del_upd_date,
(SELECT user_name
FROM apps.fnd_user
WHERE user_id = wdd.last_updated_by)
del_upd_by,
wda.delivery_id,
wdd.delivery_detail_id,
wdd.move_order_line_id,
wdd.request_id del_request_id,
mtrl.from_subinventory_code from_subinv,
mtrl.to_subinventory_code to_subinv,
mtrl.quantity_delivered qty_delivered,
mtrl.pick_slip_number,
mtrl.pick_slip_date,
oel.actual_shipment_date
FROM apps.oe_order_headers_all oeh,
apps.oe_order_lines_all oel,
wsh.wsh_delivery_details wdd,
apps.wsh_delivery_assignments wda,
apps.mtl_system_items msi,
apps.mtl_txn_request_lines mtrl
WHERE oeh.header_id = oel.header_id
AND oel.line_id = wdd.source_line_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
AND msi.inventory_item_id = oel.inventory_item_id
AND oel.ship_from_org_id = msi.organization_id
AND mtrl.line_id(+) = wdd.move_order_line_id
AND oeh.order_number IN ('1885000297')
--AND oeh.header_id = '1344613'
--AND oeh.order_type_id <> 1037
--AND oel.global_attribute1 = 'TRANSMITTED'
--AND oel.org_id = 26
-- AND oel.line_id IN (6269374)
--AND oel.line_number = 104357064
--AND oel.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
--AND oel.inventory_item_id in (1928882, 1928414, 1927654, 1927835)
--AND msi.organization_id = 26
--AND msi.segment1 = '22013144'
--AND msi.shippable_item_flag = 'Y'
--AND wda.delivery_id = '2242405'
--AND wdd.delivery_detail_id IN (969822688, 969917104, 969917105, 969917106, 969917107)
--AND mtrl. pick_slip_number = '2246389'
ORDER BY line_number, line_id, delivery_id
Is this query deliver the order to cash flow .i want to know that is it ok from order place to invoice generation process. Waiting for your reply.
ReplyDeleteThis query gives details only from OM, INV modules, but you can join AR tables to get invoice details as well
ReplyDelete