出货相关SQL

select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id, wdd.released_status,wdd.creation_date
from wsh_delivery_details wdd,oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.released_status <> 'D'
and wdd.oe_interfaced_flag in ('N','P')
and wdd.source_line_id = ol.line_id
and trunc(wdd.creation_date) > sysdate -365
and ol.open_flag = 'N'
and ol.cancelled_flag = 'N'
and not exists ( select 'x'
from wsh_delivery_details wdd1
where wdd1.source_code = 'OE'
and wdd1.source_line_id = wdd.source_line_id
and wdd1.oe_interfaced_flag = 'Y')
order by 1,2,3
---
select wdd.source_header_number, wdd.source_line_id, wdd.delivery_detail_id,wdd.released_status,wdd.creation_date
from wsh_delivery_details wdd
where wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'N'
and wdd.released_status <> 'D'
and trunc(wdd.creation_date) > sysdate - 365
and exists ( select 'x'
from wsh_delivery_details wdd1
where wdd1.source_code = 'OE'
and wdd.source_line_id = wdd1.source_line_id
and wdd1.oe_interfaced_flag = 'Y')
order by 1,2,3
--
select distinct wdd.source_header_number, wts.stop_id, wts.trip_id
from wsh_delivery_details wdd,wsh_delivery_assignments wda,wsh_new_deliveries wnd, wsh_delivery_legs wdl,wsh_trip_stops wts
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and (wdd.oe_interfaced_flag in ('N','P') or wdd.inv_interfaced_flag in ('N','P'))
and wdd.delivery_detail_id = wda.delivery_detail_id
and trunc(wdd.creation_date) > sysdate -365
and wda.delivery_id = wnd.delivery_id
and wnd.delivery_id = wdl.delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and nvl(wts.pending_interface_flag,'X') <> 'Y'
order by 1
--
select wdd.source_header_number,wdd.source_header_id,wdd.source_line_id,wdd.delivery_detail_id
from wsh_delivery_details wdd,oe_order_lines_all ol
where wdd.source_code = 'OE'
and wdd.source_line_id = ol.line_id
and trunc(wdd.creation_date) > sysdate -365
and wdd.released_status <> 'D'
and nvl(wdd.inv_interfaced_flag,'N') in ('N','P')
and wdd.source_line_id = ol.line_id
and ol.source_document_type_id = 10
and not exists ( select 'x'
from po_requisition_lines_all pl,
po_req_distributions_all pd
where pl.requisition_line_id = ol.source_document_line_id
and pl.requisition_header_id = ol.source_document_id
and pl.requisition_line_id = pd.requisition_line_id)
order by 1,3,4
--
select distinct wdd.source_header_number, wdd.delivery_detail_id,wdd.creation_date
from wsh_delivery_details wdd
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and nvl(wdd.shipped_quantity,0) = 0
and wdd.oe_interfaced_flag = 'N'
and trunc(wdd.creation_date) > sysdate -365
order by 1,2
--
select wdd.source_header_number , wdd.delivery_detail_id, wdd.shipped_quantity,
wdd.transaction_temp_id, msi.serial_number_control_code
from wsh_delivery_details wdd, mtl_system_items msi
where wdd.released_status = 'C'
and wdd.source_code = 'OE'
and wdd.oe_interfaced_flag = 'Y'
and wdd.inv_interfaced_flag in ('N','P')
and wdd.inventory_item_id = msi.inventory_item_id
and wdd.organization_id = msi.organization_id
and wdd.transaction_temp_id is null
and exists (
select 'x'
from wsh_serial_numbers wsn
where wsn.delivery_detail_id = wdd.delivery_detail_id )
--
/* query to find exception_behavior. lookup values */
SELECT lookup_code, meaning, description, enabled_flag
FROM fnd_lookup_values_vl
WHERE (NVL ('', territory_code) = territory_code
OR territory_code IS NULL)
AND lookup_type = 'EXCEPTION_BEHAVIOR'
AND (lookup_type LIKE 'EXCEPTION_BEHAVIOR')
AND (view_application_id = 665)
AND (security_group_id = 0)
ORDER BY lookup_code

/* query to find exception_status lookup values */
SELECT lookup_code, meaning, description, enabled_flag
FROM fnd_lookup_values_vl
WHERE (NVL ('', territory_code) = territory_code
OR territory_code IS NULL)
AND lookup_type = 'EXCEPTION_STATUS'
AND (lookup_type LIKE 'EXCEPTION_STATUS')
AND (view_application_id = 665)
AND (security_group_id = 0)
ORDER BY lookup_code;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15218514/viewspace-676988/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15218514/viewspace-676988/