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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 #331728 is a reply to message #331697] |
Sat, 05 July 2008 01:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
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;
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 11:19:50 CDT 2024
|