| Report displays two lines instead of one line [message #276561] |
Thu, 25 October 2007 08:48  |
we_jai@hotmail.com
Messages: 9
Registered: October 2007
Location: hyderabad ,india
|
Junior Member |
 
|
|
Hi all,
this is how my report is displaying
item schedule_date on had qty ordered qty
2841723 15-OCT-07 10772 231
2841723 15-OCT-07 10772 462
3191884 15-OCT-07 785 56
3191884 15-OCT-07 785 134
for the item 2841723 and 3191884 the ordered qty should be 693 and 190 both items should be displayed in single line
this is my query
select distinct msi.segment1 "Item Number"
,msi.description "item_description"
,sum(moq.transaction_quantity) "On Hand Quantity"
,ool.schedule_ship_date "Schedule Ship Date"
,
ool.ordered_quantity "Ordered Quantity"
,ool.FREIGHT_TERMS_CODE
from mtl_system_items_b msi
,mtl_onhand_quantities moq
,oe_order_headers ooh
,oe_order_lines ool
where msi.inventory_item_id=moq.inventory_item_id
and ooh.header_id=ool.header_id
and ooh.ORG_ID=ool.org_id
and msi.ORGANIZATION_ID=moq.ORGANIZATION_ID
and msi.INVENTORY_ITEM_ID=ool.ORDERED_ITEM_ID
and subinventory_code='FG'
-- and msi.segment1=:p_item_number -----'2841723'
and ool.schedule_ship_date between :p_schedule_ship_date_from and :p_schedule_ship_date_to
and ool.schedule_ship_date is not null
group by msi.segment1
,msi.description
--,moq.transaction_quantity
,ool.ordered_quantity
,ool.schedule_ship_date
,ool.FREIGHT_TERMS_CODE
order by msi.segment1
and i took summary with in the group with source ordered quantity
and populating the ordered qty col with this summary col but it is displaying two lines instead of one line.
CAN ANY ONE HELP ME PLEASE..
[EDITED by LF: removed a huge attachment. Please, read the OraFAQ Forum Guide to learn which image formats are acceptable.]
[Updated on: Fri, 26 October 2007 01:30] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Report displays two lines instead of one line [message #276706 is a reply to message #276620] |
Fri, 26 October 2007 01:10  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
How it works is: write two statements, one selecting the ordered quantity (and related data), one selecting the on hand quantity (and related data). In both queries, include the id's and other data you need to join the two results. It will look something like:
SELECT sub1.c_id
,sub1.some_other_data
,sub2.some_other_date
,sub1.b_quantity
,sub2.d_quantity
FROM (SELECT a.c_id
,a.some_other_data
,SUM(b.some_quantity) b_quantity
FROM table_a a
,table_b b
WHERE b.some_id = a.some_id
GROUP BY a.c_id
,a.some_other_data) sub1
,(SELECT c.c_id
,c.some_other_date
,SUM(d.some_quantity) d_quantity
FROM table_c c
,table_d d
WHERE c.some_id = d.some_id
GROUP BY c.c_id
,c.some_other_data) sub2
WHERE sub1.c_id = sub2.c_id
But again, functionally it makes no sense to select onhand quantity for a certain item on a certain shipping date.
Furthermore, as far as I recall the table onhand quantities does only contain current/actual data (so, the quantity TODAY). It's impossible to determine the onhand quantity on a certain day in the past using that table. You could figure out the onhand quantity on a date in the past using material transactions, but you don't want to go there.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
).