Introduction:
This SQL query is used to fetching the data Backlog Orders with freight Details
Cause of the issue:
Business wants a report that details of Backlog Orders
How do we solve:
Create a report in BI publisher using the below SQL query to extract the Backlog Orders details.
SQL Query:
SELECT cust.customer_number “Customer Number”,
cust.customer_name “Customer Name”, cust.flex5 “Discrete Account”,
cust.customer_city “City”, cust.customer_state “State”,
ooh.order_number “Order Number”, ott.NAME “Order Type”,
ooh.cust_po_number “PO Number”, ooh.flow_status_code “Status”,
wnd.NAME “Delivery”, wt.NAME “Trip”, item_cat.segment1 “Business Unit”,
xda.ship_from “Ship From Wh”,
TO_CHAR (ool.creation_date, ‘DD-MON-YYYY’) ” Order Creation Date”,
TO_CHAR (ool.schedule_ship_date, ‘DD-MON-YYYY’) “ScheduleshipDate”,
TO_CHAR (wts.planned_arrival_date,
‘DD-MON-YYYY’
) “Planned Arrival Date”,
TO_CHAR (wts.planned_departure_date,
‘DD-MON-YYYY’
) “Planned Departure Date”,
TO_CHAR (wts.actual_arrival_date, ‘DD-MON-YYYY’) “Actual Arrival Date”,
TO_CHAR (wts.actual_departure_date,
‘DD-MON-YYYY’
) “Actual Departure Date”,
lk.meaning “Freight Terms”, wdd.ship_method_code “Ship Method Code”,
ool.ordered_item “Ordered Item”, msi.segment1 “Internal Item”,
msi.inventory_item_status_code “Item Status”,
DECODE (msi.planning_make_buy_code,
1, ‘Make’,
2, ‘Buy’,
‘Unknown’
) “Make Buy”,
msi.attribute2 “Case Qty”, msi.attribute10 “Pallet Qty”,
flv.meaning “Delivery Detail Line Status”,
ool.flow_status_code “Order Line Status”,
(NVL (wdd.requested_quantity, ool.ordered_quantity)) “Ordered Qty”,
(wdd.cancelled_quantity) “Cancelled Qty”,
(ool.shipped_quantity) “Shipped Quantity”,
TO_CHAR (ool.actual_shipment_date,
‘DD-MON-YYYY’
) “Actual Shipment Date”,
( ( NVL (wdd.requested_quantity, ool.ordered_quantity)
/ NVL (muc.conversion_rate, 1)
)
* ool.unit_selling_price
) “Order Line Total”
FROM dmc_customers@erpprod_to_sale cust,
oe_order_headers_all ooh,
oe_order_lines_all ool,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_trips wt,
apps.mtl_item_categories_v item_cat,
apps.xdmc_doc_appointments_b xda,
inv.mtl_system_items_b msi,
fnd_lookup_values lk,
apps.fnd_lookup_values flv,
oe_transaction_types_tl ott,
mtl_uom_conversions muc,
oe_transaction_types_all ota
WHERE ooh.header_id = ool.header_id
AND (ool.schedule_ship_date) >=
(TO_DATE (” || p_st_schedule_date || ”, ‘RRRR/MM/DD HH24:MI:SS’)
)
AND (ool.schedule_ship_date) <=
(TO_DATE (” || p_end_schedule_date || ”, ‘RRRR/MM/DD HH24:MI:SS’)
)
AND cust.site_use_id = ooh.ship_to_org_id
AND ool.header_id = wdd.source_header_id(+)
AND ool.line_id = wdd.source_line_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
AND wda.delivery_id = wnd.delivery_id(+)
AND wnd.delivery_id = wdl.delivery_id(+)
AND wdl.pick_up_stop_id = wts.stop_id(+)
AND wts.trip_id = wt.trip_id(+)
AND ool.inventory_item_id = item_cat.inventory_item_id
AND item_cat.organization_id = 1
AND item_cat.category_set_id = 1
AND wt.attribute13 = TO_CHAR (xda.dock_appointment_id(+))
AND ool.inventory_item_id = msi.inventory_item_id
AND ool.ship_from_org_id = msi.organization_id
AND ooh.freight_terms_code = lk.lookup_code(+)
AND lk.lookup_type(+) = ‘FREIGHT_TERMS’
AND lk.view_application_id(+) = 660
AND wdd.released_status = flv.lookup_code(+)
AND flv.lookup_type(+) = ‘PICK_STATUS’
AND flv.view_application_id(+) = 665
AND flv.security_group_id(+) = 0
AND ooh.order_type_id = ott.transaction_type_id(+)
AND ool.inventory_item_id = muc.inventory_item_id(+)
AND ool.pricing_quantity_uom = muc.uom_code(+)
AND ooh.order_type_id = ota.transaction_type_id(+)
AND ota.order_category_code <> ‘RETURN’
AND ool.flow_status_code = ‘CLOSED’
