Home » Developer & Programmer » Reports & Discoverer » getting years (Oracle Applications)
icon4.gif  getting years [message #426230] Wed, 14 October 2009 08:52 Go to next message
harshas
Messages: 1
Registered: October 2009
Location: Hyderabad
Junior Member
hi , here is my problem

i want to get sum of quantity in previous_year,current_year,previous_month,previous_month,previous_week,current_week
if P_date=01-jan-2009 then how to get previous month ie,dec-2008,
also week 31-dec-2008............
here is my loop
how can we manage




this my cursor
cursor oe_cur(c_previous_year date, c_current_year date)
IS
SELECT sum(ordered_quantity) Total_sum
,creation_date
,sold_to_org_id
,inventory_item_id
FROM oe_order_lines_all
WHERE creation_date between c_previous_year and c_current_year
and sold_to_org_id= p_cust_id
group by creation_date
,sold_to_org_id
,inventory_item_id;
--here begin
begin
select to_char(p_date, 'yyyy')
into y from dual;
select to_char('01-JAN-'||(y-1))
INTO v_previous_y from dual;
select to_char('31-DEC-'||y)
INTO v_current_y from dual;


for c_rec in oe_cur(v_previous_y,v_current_y)
loop

v_system_year := to_char(c_rec.creation_date,'yyyy') ;
v_system_month := to_char(c_rec.creation_date,'mm');
v_system_week := to_char(c_rec.creation_date,'w');

v_current_year := to_char(p_date,'yyyy');
v_current_month := to_char(p_date,'mm');
v_current_week := to_char(p_date,'w');

v_previous_year := (v_current_year)-1;
v_previous_month := (v_current_month)-1;
v_previous_week := (v_current_week)-1;

--dbms_output.put_line ('previous_year:'||v_previous_year);

if (v_system_year = v_current_year) then
v_current_year_qty := (c_rec.Total_sum)+ v_current_year_qty;

if (v_system_month = 1) then
v_system_month1 := 12;
v_current_month_qty := (c_rec.Total_sum) + v_current_month_qty ;


if (v_system_month = v_current_month) then
v_current_month_qty := (c_rec.Total_sum) + v_current_month_qty ;

if (v_system_week = v_current_week ) then
v_current_week_qty :=(c_rec.total_sum)+v_current_week_qty;
end if;
if(v_system_week = v_previous_week ) then
v_previous_week_qty :=(c_rec.total_sum)+v_previous_week_qty;
end if;
end if;
if(v_system_month = v_previous_month) then
v_previous_month_qty := (c_rec.Total_sum)+ v_previous_month_qty ;
end if;
end if;

if (v_system_year = v_previous_year) then
v_previous_year_qty := (c_rec.Total_sum)+ v_previous_year_qty;
end if;
end loop;
select customer_name
into v_customer_name
from ra_customers rc
where customer_id =p_cust_id ;

dbms_output.put_line ('Customer Name: ' || v_customer_name ||' '||
--'Item: ' || v_segment1||' '||
' Last Year qty: '|| v_previous_year_qty ||' '||
' current Year qty : '||v_current_year_qty ||' '||
' Previous Month qty: '||v_previous_month_qty || ' '||
' Curreent Month qty: '||v_current_month_qty || ' '||
' Last week : '|| v_previous_week_qty|| ' '||
' Current Week:' || v_current_week_qty);

Re: getting years [message #426234 is a reply to message #426230] Wed, 14 October 2009 09:09 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) When posting code can you please use code tags, it makes it a lot easier to read - see the orafaq forum guide if you're not sure how.
2) I'm not sure what your exact problem is. You have code that generates totals, does it not do what you want?
You need to be clearer on what the issue is what output you want in the end.
Re: getting years [message #426235 is a reply to message #426230] Wed, 14 October 2009 09:10 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
To get the previous month use the add_months function
Previous Topic: Barcode print
Next Topic: Place holder related issue
Goto Forum:
  


Current Time: Sat Dec 10 16:58:10 CST 2016

Total time taken to generate the page: 0.04815 seconds