Query conversion in oracle

How could i change given query

select
msi.attribute1 ref_no,
msi.description,
wdj.attribute10 order_id,
wdj.net_quantity,
” Rec_date,
” Qty,
” packing_dated,
trunc(sysdate) issue_date,
hca.ACCOUNT_NUMBER||’-‘||ooha.cust_po_number order_no,
ooha.order_number sale_order_no,
mci.attribute5 etching,
we.wip_entity_name,
weC.wip_entity_name “Relevant ASM job”
from wip_discrete_jobs wdj,
wip_entities we,
oe_order_headers_all ooha,
oe_order_lines_all oola,
mtl_customer_items mci,
mtl_system_items_b msi,
fnd_flex_values_vl ffvv,
hz_cust_accounts hca,
wip_discrete_jobs wdjC,
wip_entities weC
where
wdj.wip_entity_id = we.wip_entity_id
and ooha.header_id=wdj.attribute10
and ooha.header_id=oola.header_id
and oola.line_id=wdj.attribute9
and oola.ordered_item_id=mci.customer_item_id
and wdj.primary_item_id=msi.inventory_item_id
and msi.segment2 = ffvv.FLEX_VALUE
and mci.customer_id=hca.cust_account_id
AND wdjC.wip_entity_id = weC.wip_entity_id(+)
AND wdjC.attribute1(+) = we.wip_entity_name
and wdj.organization_id = msi.organization_id
and ffvv.FLEX_VALUE_SET_ID = ‘1014875’
and wdj.attribute10 = :order_id

to

select
msi.attribute1 ref_no,
msi.description,
wdj.attribute10 order_id,
wdj.net_quantity,
” Rec_date,
” Qty,
” packing_dated,
trunc(sysdate) issue_date,
hca.ACCOUNT_NUMBER||’-‘||ooha.cust_po_number order_no,
ooha.order_number sale_order_no,
mci.attribute5 etching,
we.wip_entity_name
from wip_discrete_jobs wdj
join wip_entities we on wdj.wip_entity_id = we.wip_entity_id
join oe_order_headers_all ooha on ooha.header_id=wdj.attribute10
join oe_order_lines_all oola on ooha.header_id=oola.header_id and oola.line_id=wdj.attribute9
join mtl_customer_items mci on oola.ordered_item_id=mci.customer_item_id
join mtl_system_items_b msi on wdj.primary_item_id=msi.inventory_item_id
join fnd_flex_values_vl ffvv on msi.segment2 = ffvv.FLEX_VALUE
join hz_cust_accounts hca on mci.customer_id=hca.cust_account_id
and wip_discrete_jobs wdjP inner join wip_entities weP on wdjP.WIP_ENTITY_ID=weP.WIP_ENTITY_ID
and wdj.organization_id = msi.organization_id
and ffvv.FLEX_VALUE_SET_ID = ‘1014875’
and wdj.attribute10 = :order_id


Source: oracle

Leave a Reply