Home » Developer & Programmer » Reports & Discoverer » bal qty calculation throgh formula (report 6i)
bal qty calculation throgh formula [message #331456] Thu, 03 July 2008 07:04 Go to next message
mk_sahu
Messages: 6
Registered: March 2007
Junior Member
I have two queries in my report in separate group, one is for issue and second for recieving:-

1)query
select a.challan_no,a.CHALLAN_DT,a.ITEM_DESC,sum(a.ISS_QTY_NOS),sum(a.ISS_QTY_wt),
b.vend_code,c.vend_name
from jw_issue_det a,jw_issue_mas b,vendor_mas c
where
a.challan_no=b.challan_no and
a.challan_dt=b.challan_dt and
b.vend_code=c.vend_code and a.challan_dt>= : From_Dt and a.challan_dt <= :To_Dt and b.vend_code = :Vendor_Name
group by b.vend_code,c.vend_name,a.challan_no,a.CHALLAN_DT,a.ITEM_DESC
2)query
elect challan_no,CHALLAN_DT,ITEM_DESC,sum(recd_QTY_NOS),sum(recd_QTY_wt)
from jw_recd_det
group by challan_no,CHALLAN_DT,ITEM_DESC

Now I want to know can I calculate balance qty throgh formula coulmn. pl provide code to me for it, right now I am using the following code in formula, but it is not working

function CF_1Formula return Number is
bal_nos number;
begin
select (:sum_a_iss_qty_nos - :sum_recd_qty_nos) into bal_nos from dual;
return(bal_nos);
end;

Thanks!!
Re: bal qty calculation throgh formula [message #331697 is a reply to message #331456] Fri, 04 July 2008 12:17 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Hi

Can you show me image of your current report ?

Can you make image of report which you require?

why two seprates groups?

-Dude


Re: bal qty calculation throgh formula [message #331714 is a reply to message #331456] Fri, 04 July 2008 22:36 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi

As dude said u can join the group and u can achieve what u are expection.

Better u show your datamodel image.
Run report image.


Kanish
Re: bal qty calculation throgh formula [message #331728 is a reply to message #331697] Sat, 05 July 2008 01:16 Go to previous messageGo to next message
mk_sahu
Messages: 6
Registered: March 2007
Junior Member
Dear sir,

I am attaching the screen shots of the report, I want to calculate balance qty=Issue_qty-recd-qty,
e.g
Issue qty=100,recd qty=50
then bal_qty=100-50=50
if there is no recieving then
bal_qty=100-0=100

Thanks for the the help
Re: bal qty calculation throgh formula [message #331753 is a reply to message #331456] Sat, 05 July 2008 04:05 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

You should try it by write the query using Union like Below
select a.challan_no,a.CHALLAN_DT,a.ITEM_DESC,a.recd_QTY_NOS,a.recd_QTY_wt,a.ISS_QTY_NOS,a.ISS_QTY_wt,
b.vend_code,c.vend_name

(
(
select a.challan_no,a.CHALLAN_DT,a.ITEM_DESC,(0) "recd_QTY_NOS",(0) "recd_QTY_wt",
sum(a.ISS_QTY_NOS),sum(a.ISS_QTY_wt),
b.vend_code,c.vend_name
from jw_issue_det a,jw_issue_mas b,vendor_mas c
where
a.challan_no=b.challan_no and
a.challan_dt=b.challan_dt and
b.vend_code=c.vend_code and a.challan_dt>= : From_Dt and a.challan_dt <= :To_Dt and b.vend_code = :Vendor_Name
group by b.vend_code,c.vend_name,a.challan_no,a.CHALLAN_DT,a.ITEM_DESC
)
Union
(
Select challan_no,CHALLAN_DT,ITEM_DESC,sum(recd_QTY_NOS),sum(recd_QTY_wt),(0) "ISS_QTY_NOS",(0) "ISS_QTY_wt"
from jw_recd_det
group by challan_no,CHALLAN_DT,ITEM_DESC 
)
)
a



Then Using the Function by

function CF_CbnoFormula return Number is
Cf_cbno Number(15,3);

Begin

Cf_cbNo := :recd_QTY_NOS - ISS_QTY_NOS;


Return(Cf_cbno);

End;
Re: bal qty calculation throgh formula [message #333341 is a reply to message #331456] Fri, 11 July 2008 05:35 Go to previous message
mk_sahu
Messages: 6
Registered: March 2007
Junior Member
It shows syantax error and union works for the same field of the different queries.
Previous Topic: How to connect SAP database from oracle report?
Next Topic: displaying header with datbase value
Goto Forum:
  


Current Time: Fri Dec 09 17:22:36 CST 2016

Total time taken to generate the page: 0.23641 seconds