VIEW: APPS.WMS_WP_WWB_ORDERS_V

Source

View Text - Preformatted

SELECT order_number,
Max(customer),
Round(avg(planned_fill_rate)),
Round(avg(release_fill_rate)),
Round(avg(pick_fill_rate)),
Max(order_type),
Max(customer_id),
Max(cust_po_number),
Max(ordered_date),
Max(order_request_date),
Max(ORDER_PROGRESS),
wave_header_id
from
(SELECT DISTINCT WDD.SOURCE_HEADER_NUMBER ORDER_NUMBER,
HP.PARTY_NAME CUSTOMER,
wwlv1.planned_fill_rate,
wwlv1.release_fill_rate,
wwlv1.pick_fill_rate,
WDD.SOURCE_HEADER_TYPE_NAME ORDER_TYPE,
WDD.CUSTOMER_ID,
WDD.CUST_PO_NUMBER,
OOH.ORDERED_DATE,
OOH.REQUEST_DATE Order_Request_date,
decode(wwlv1.distinct_count,1,'','Partially ')||Decode(wwlv1.max_line_progress,1,'Ready to Release',2,'Backordered',3,'Crossdock Planned',4,'Replenishment Created',5,'Tasked',6,'Picked',
7,'Picked',9,'In Packing',8,'Staged',10,'Loaded to Dock',11,'Shipped') ORDER_PROGRESS,
wwl.wave_header_id
FROM
WSH_DELIVERY_DETAILS WDD,
HZ_PARTIES HP,
hz_cust_accounts CUST_ACCT,
OE_ORDER_HEADERS_ALL OOH,
WMS_WP_WAVE_LINES WWL,
(SELECT Max(line_progress_id) max_line_progress,order_number,Avg(release_fill_rate) release_fill_rate,
Avg(planned_fill_rate) planned_fill_rate,
Avg(pick_fill_rate) pick_fill_rate,
Count(DISTINCT line_progress_id) distinct_count,wave_header_id FROM wms_wp_wwb_lines_v
 GROUP BY order_number,wave_header_id) wwlv1
WHERE
HP.party_id = cust_acct.party_id
and cust_acct.status = 'A'
and cust_acct.cust_account_id= wdd.customer_id
AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID
AND WWL.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
and wwl.organization_id = wdd.organization_id
AND wwlv1.order_number = wwl.source_header_number
AND wwlv1.wave_header_id=wwl.wave_header_id)
GROUP BY order_number,wave_header_id
ORDER BY wave_header_id
   

View Text - HTML Formatted

SELECT ORDER_NUMBER
, MAX(CUSTOMER)
, ROUND(AVG(PLANNED_FILL_RATE))
, ROUND(AVG(RELEASE_FILL_RATE))
, ROUND(AVG(PICK_FILL_RATE))
, MAX(ORDER_TYPE)
, MAX(CUSTOMER_ID)
, MAX(CUST_PO_NUMBER)
, MAX(ORDERED_DATE)
, MAX(ORDER_REQUEST_DATE)
, MAX(ORDER_PROGRESS)
, WAVE_HEADER_ID FROM (SELECT DISTINCT WDD.SOURCE_HEADER_NUMBER ORDER_NUMBER
, HP.PARTY_NAME CUSTOMER
, WWLV1.PLANNED_FILL_RATE
, WWLV1.RELEASE_FILL_RATE
, WWLV1.PICK_FILL_RATE
, WDD.SOURCE_HEADER_TYPE_NAME ORDER_TYPE
, WDD.CUSTOMER_ID
, WDD.CUST_PO_NUMBER
, OOH.ORDERED_DATE
, OOH.REQUEST_DATE ORDER_REQUEST_DATE
, DECODE(WWLV1.DISTINCT_COUNT
, 1
, ''
, 'PARTIALLY ')||DECODE(WWLV1.MAX_LINE_PROGRESS
, 1
, 'READY TO RELEASE'
, 2
, 'BACKORDERED'
, 3
, 'CROSSDOCK PLANNED'
, 4
, 'REPLENISHMENT CREATED'
, 5
, 'TASKED'
, 6
, 'PICKED'
, 7
, 'PICKED'
, 9
, 'IN PACKING'
, 8
, 'STAGED'
, 10
, 'LOADED TO DOCK'
, 11
, 'SHIPPED') ORDER_PROGRESS
, WWL.WAVE_HEADER_ID FROM WSH_DELIVERY_DETAILS WDD
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS CUST_ACCT
, OE_ORDER_HEADERS_ALL OOH
, WMS_WP_WAVE_LINES WWL
, (SELECT MAX(LINE_PROGRESS_ID) MAX_LINE_PROGRESS
, ORDER_NUMBER
, AVG(RELEASE_FILL_RATE) RELEASE_FILL_RATE
, AVG(PLANNED_FILL_RATE) PLANNED_FILL_RATE
, AVG(PICK_FILL_RATE) PICK_FILL_RATE
, COUNT(DISTINCT LINE_PROGRESS_ID) DISTINCT_COUNT
, WAVE_HEADER_ID
FROM WMS_WP_WWB_LINES_V GROUP BY ORDER_NUMBER
, WAVE_HEADER_ID) WWLV1 WHERE HP.PARTY_ID = CUST_ACCT.PARTY_ID AND CUST_ACCT.STATUS = 'A' AND CUST_ACCT.CUST_ACCOUNT_ID= WDD.CUSTOMER_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WWL.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WWL.ORGANIZATION_ID = WDD.ORGANIZATION_ID AND WWLV1.ORDER_NUMBER = WWL.SOURCE_HEADER_NUMBER AND WWLV1.WAVE_HEADER_ID=WWL.WAVE_HEADER_ID) GROUP BY ORDER_NUMBER
, WAVE_HEADER_ID ORDER BY WAVE_HEADER_ID