One of the critical components of the work execution is work orders( WO) which you will need to query million times if you have implemented the O2C Cycle. So cutting to the chase, here is the query which will fetch you Work Order details. Columns you’ll be able to query are:
1. WO Id
2. WO Number
3. Completion Date
4. WO Type
5. WO Subtype
6. Planned Start Date
7. Planned Completion Date
8. Actual Start Date
9. Actual Completion Date
10. WO Status
Since you are already looking for WO details, most probably you’ll need the query to connect sales order with work order too. Click here to get the query.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Here is the Work Order query:
SELECT wwob.work_order_id,
wwob.work_order_number,
nvl(to_char(wwob.actual_completion_date, ‘MM/DD/YYYY’),
to_char(wwob.planned_completion_date, ‘MM/DD/YYYY’)) wo_completion_date,
flv.meaning work_order_type,
flv_sub.meaning work_order_sub_type,
planned_start_date,
planned_completion_date,
actual_start_date,
actual_completion_date,
stat_tl.wo_status_name
FROM wie_work_orders_b wwob,
fnd_lookup_values flv,
fnd_lookup_values flv_sub,
wie_wo_statuses_tl stat_tl
where 1=1
AND wwob.work_order_number = :WO_NUMBER
AND flv.lookup_code = wwob.WORK_ORDER_TYPE
AND flv.lookup_type = ‘ORA_WIE_WO_TYPE’
AND flv_sub.lookup_code = wwob.work_order_sub_type
AND flv_sub.lookup_type = ‘ORA_WIE_WO_SUB_TYPE’
AND wwob.WORK_ORDER_STATUS_ID = stat_tl.WO_STATUS_ID
and stat_tl.language = USERENV(‘LANG’)
Related Posts
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
