|Report displays two lines instead of one line [message #276561]
||Thu, 25 October 2007 08:48
Registered: October 2007
Location: hyderabad ,india
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"
,sum(moq.transaction_quantity) "On Hand Quantity"
,ool.schedule_ship_date "Schedule Ship Date"
ool.ordered_quantity "Ordered Quantity"
from mtl_system_items_b msi
-- 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
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
Registered: March 2005
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:|
FROM (SELECT a.c_id
FROM table_a a
WHERE b.some_id = a.some_id
GROUP BY a.c_id
FROM table_c c
WHERE c.some_id = d.some_id
GROUP BY c.c_id
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.