|
|
|
|
| Re: How to use Formula column in my report [message #408626 is a reply to message #408616] |
Wed, 17 June 2009 02:08   |
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
|
Member |
 
|
|
Thanks for the response
I got some issue here when i tried your way
I have taken the main query as
select a.customer_name,d.description ,c.request_date
from
ra_customers a,
oe_order_headers_all b,
oe_order_lines_all c,
mtl_system_items_b d
where
a.customer_id=b.sold_to_org_id and
b.header_id=c.header_id and
c.inventory_item_id=d.inventory_item_id
and taken a field as LAST_WEEK and given source as formula column
The code for formula column is
function CF_1Formula return Number is
x number;
begin
select sum(c.ordered_quantity) into x
from oe_order_headers_all b,
oe_order_lines_all c
where
to_char(c.request_date) =to_char(to_date('23-JAN-09')-7)
and b.sold_to_org_id =1697
and b.header_id=c.header_id
and c.request_date > '01-JAN-09'
and c.inventory_item_id =65938
group by b.sold_to_org_id,c.ordered_item, c.inventory_item_id, c.request_date;
return x;
end;
But in the report there is coming many rows it suppose to come only single row...
Kindly help me out
and even i have to get the ordered quantity according to the dates with respect to each customers
|
|
|
|
|
|
|
|
| Re: How to use Formula column in my report [message #408851 is a reply to message #408630] |
Thu, 18 June 2009 01:14 |
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
|
Member |
|
|
select x.sold_to_org_id, x.ordered_item,x.inventory_item_id, sum(x.week1) week1, sum(x.week2) week2, sum(x.week3) week3, sum(x.week4) week4, sum(x.week5) week5,
case
when sum(x.week1) is not null and sum(x.week2) is not null and sum(x.week1) > sum(x.week2) or sum(x.week2) > sum(x.week1) then
sum(x.week1)-sum(x.week2)
when sum(x.week2) is not null and sum(x.week3) is not null and sum(x.week2) > sum(x.week3) or sum(x.week3) > sum(x.week2) then
sum(x.week2)-sum(x.week3)
when sum(x.week3) is not null and sum(x.week4) is not null and sum(x.week3) > sum(x.week4) or sum(x.week4) > sum(x.week3) then
sum(x.week3)-sum(x.week4)
when sum(x.week4) is not null and sum(x.week5) is not null and sum(x.week4) > sum(x.week5) or sum(x.week4) > sum(x.week5) then
sum(x.week4)-sum(x.week5)
else null
end as Difference
from
(
select b.sold_to_org_id,c.ordered_item, c.inventory_item_id,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 1 then (c.ordered_quantity)
else null
end as WEEK1,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 2 then (c.ordered_quantity)
else null
end as WEEK2,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 3 then (c.ordered_quantity)
else null
end as WEEK3,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 4 then (c.ordered_quantity)
else null
end as WEEK4,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 5 then (c.ordered_quantity)
else null
end as WEEK5
from oe_order_headers_all b,
oe_order_lines_all c
where
b.sold_to_org_id IN (1697,1723,1714,19099,1714,2431) and
b.header_id=c.header_id
and c.request_date > '01-JAN-09'
and c.inventory_item_id IN (65938,1449,43520,17352,43940,67277)
) x
group by x.sold_to_org_id,x.ordered_item, x.inventory_item_id
This Query is not working properly as per my requirement i need to get the difference from any two particular weeks. How can i assign number to the week columns amd used that in my difference column to get the difference between two columns.
Kindly help me..ASAP it would be better.
Thanks in advance
Aiyaz
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
