Home » Applications » Oracle Fusion Apps & E-Business Suite » Join between oe_order_lines_all and mtl_onhand_quantities_detail (R12I)
Join between oe_order_lines_all and mtl_onhand_quantities_detail [message #404697] Fri, 22 May 2009 19:12 Go to next message
svk579
Messages: 9
Registered: April 2009
Location: brea
Junior Member
Hello Experts,

Can you please let me know how to join
oe_order_lines_All and mtl_onhand_quantities_detail.

I have joined in this way, please let me know if there are any more joins to be taken care off.

Select ooha.order_number,
moqd.primary_transaction_quantity onhand_qty
from oe_order_headers_all ooha
,oe_order_lines_all oola
,mtl_onhand_quantities_detail moqd
where 1=1
and ooha.header_id = oola.header_id
and oola.ship_from_org_id = moqd.organization_id
and oola.inventory_item_id = moqd.inventory_item_id;

I am getting more rows than needed. Please let me know.

Thanks in advance.


Re: Join between oe_order_lines_all and mtl_onhand_quantities_detail [message #404699 is a reply to message #404697] Fri, 22 May 2009 19:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT ooha.order_number,
       moqd.primary_transaction_quantity onhand_qty
FROM   oe_order_headers_all ooha,
       oe_order_lines_all oola,
       mtl_onhand_quantities_detail moqd
WHERE  1 = 1
       AND ooha.header_id = oola.header_id
       AND oola.ship_from_org_id = moqd.organization_id
       AND oola.inventory_item_id = moqd.inventory_item_id; 


SELECT ooha.order_number,
       moqd.primary_transaction_quantity onhand_qty
FROM   oe_order_headers_all ooha,
       mtl_onhand_quantities_detail moqd
WHERE  ooha.header_id IN (SELECT oola.header_id
                          FROM oe_order_lines_all oola
                          WHERE oola.ship_from_org_id = moqd.organization_id
                          AND oola.inventory_item_id = moqd.inventory_item_id);


Any better?
Re: Join between oe_order_lines_all and mtl_onhand_quantities_detail [message #404705 is a reply to message #404699] Fri, 22 May 2009 23:39 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
IN with a correlated sub-query?
Why can't we use EXISTS?

By
Vamsi
Re: Join between oe_order_lines_all and mtl_onhand_quantities_detail [message #404747 is a reply to message #404697] Sat, 23 May 2009 03:50 Go to previous messageGo to next message
svk579
Messages: 9
Registered: April 2009
Location: brea
Junior Member
hey black swan,

there is no improvement with that query.

@ vamsi krishna: how to use exists in that query. can you please post the query.

Thanks
Re: Join between oe_order_lines_all and mtl_onhand_quantities_detail [message #404776 is a reply to message #404747] Sat, 23 May 2009 11:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I've asked to use exists because of performance reasons.

Anyway, provide us the sample data in all the tables.
What you are getting as the output?
What you need as the output?

By
Vamsi
Re: Join between oe_order_lines_all and mtl_onhand_quantities_detail [message #404784 is a reply to message #404697] Sat, 23 May 2009 13:34 Go to previous messageGo to next message
svk579
Messages: 9
Registered: April 2009
Location: brea
Junior Member
Hi Vamsi,

This is what i want.

I shld calculate the availability by using the formula for all open purchase orders and open sales orders.

Availability = (ONHAND_QTY + PURCHASE_ORDER_QTY) - SALES_ORDER_QTY

I wrote a query to calculate onhand_qty and sales_order_qty. I have no issues with the query below. Check it below:
select ooha.order_number,oola.ordered_item, sum(primary_transaction_quantity) onhand_qty
,(oola.ordered_quantity)
from oe_order_headers_all ooha
,oe_order_lines_all oola
,mtl_onhand_quantities_detail moqd
where 1=1
and ooha.header_id = oola.header_id
and oola.ship_from_org_id = moqd.organization_id
and oola.inventory_item_id = moqd.inventory_item_id
and ooha.org_id = 204
and oola.flow_status_code = 'AWAITING_SHIPPING'
and line_id IN (
SELECT wdd.source_line_id
FROM wsh_delivery_details wdd
WHERE source_code = 'OE'
AND wdd.source_line_id = line_id
GROUP BY source_line_id
HAVING SUM (DECODE (wdd.released_status, 'Y', 1, 0)) < SUM (1)
AND SUM (DECODE (wdd.released_status, 'Y', 1, 0)) = 0)
group by ooha.order_number,oola.ordered_item,oola.ordered_quantity

I shld calcuate the purchase_order_qty for all open purchase orders. I used the following query and i am not getting all the rows i want. Once i am done with this query, i shld merge it with the query above.Please find the query below:
select pha.segment1
,pla.quantity
from po_headers_all pha
,po_lines_all pla
,po_system_parameters psp
where 1=1
and pha.po_header_id = pla.po_header_id
and pha.authorization_status = 'APPROVED'
and NVL(pha.closed_code,'OPEN') <> 'FINALLY CLOSED'
and (psp.org_id IN (
SELECT prl.org_id
FROM po_line_locations_all pll,
po_requisition_lines_all prl
WHERE prl.line_location_id = pll.line_location_id
AND pll.po_line_id = pla.po_line_id))

Please let me know if you have any questions.

Thanks

[Updated on: Sat, 23 May 2009 13:34]

Report message to a moderator

Re: Join between oe_order_lines_all and mtl_onhand_quantities_detail [message #404786 is a reply to message #404697] Sat, 23 May 2009 15:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>there is no improvement with that query.
SELECT DISTINCT oola.header_id

above will eliminate the duplicates
Re: Join between oe_order_lines_all and mtl_onhand_quantities_detail [message #404792 is a reply to message #404784] Sat, 23 May 2009 23:22 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Do you meant to say, you have corrected your first query (from the OP) and getting problems with second one?
Still you haven't answered my questions.
Quote:
Anyway, provide us the sample data in all the tables.
What you are getting as the output?
What you need as the output?
The following doesn't serve any purpose.
Quote:
I used the following query and i am not getting all the rows i want.
Are you getting some rows and missing some one?
(Or) Aren't you getting any rows?
You are using an MO enabled table po_system_parameters? Are you calling required APIs before selecting data from the query?

@BlackSwan,
I don't think DISTINCT will make any sense in an IN clause.


By
Vamsi

[Updated on: Sat, 23 May 2009 23:30]

Report message to a moderator

Re: Join between oe_order_lines_all and mtl_onhand_quantities_detail [message #404828 is a reply to message #404697] Sun, 24 May 2009 21:06 Go to previous message
svk579
Messages: 9
Registered: April 2009
Location: brea
Junior Member
Yes vamsi,

you are right. the first query is working. i have issues with the second query. before running the second query i am running the following dbms script:
exec dbms_application_info.set_client_info(org_id);
i am using 204 as the org_id.

Previous Topic: Aim phases
Next Topic: creating procedures push and pop
Goto Forum:
  


Current Time: Fri Dec 09 16:00:43 CST 2016

Total time taken to generate the page: 0.18992 seconds