Average Rate

[mirza_rehan]

Assalam-o-Alaikum

I got working project of my client someone work on that project but unfortunatly he leave this project so i got it.

He use same tables for In and Out

SQL> desc enInOut_M
Name Null? Type
----------------------------------------- -------- -------------
TRNO NOT NULL VARCHAR2(13)
REFTRNO VARCHAR2(13)
TRDATE DATE
TRNTYPID CHAR(1) -- Its is Transaction type (Gate Pass, Goods Receip Note)
DEPTID VARCHAR2(5)
PRTID VARCHAR2(5)
VHCLNO VARCHAR2(15)
REMARKS VARCHAR2(500)
STATUS CHAR(1)


SQL> desc enInOut_D
Name Null? Type
----------------------------------------- -------- -------------
ENTNO NOT NULL VARCHAR2(9)
TRNO VARCHAR2(13)
REFENTNO VARCHAR2(9)
ORDID VARCHAR2(13)
ORDTYPID CHAR(1)
LOTNO NUMBER(6)
DOCNO VARCHAR2(15)
ITMID VARCHAR2(12)
QTY NUMBER(14,5)
RATE NUMBER(14,5)0
GST NUMBER(5,2)
FLG NUMBER(2)
AVGRATE NUMBER(14,5) -- I add this field
AVCONITMRATE NUMBER(14,5)-- I add this field

--------------------------------------------------------------------------------------

AvConItmRate is a Rate for conversion Item for which three Item issue and receive 1 item and in conversion case in a Rate field conversion rate is entered and it comes from Order


i write these code for make average rate please review and tell me is it right or not?? or i miss some thing???

Create or Replace Function Avg_Rate (P_ItmID in Varchar2,
p_TrNo in Varchar2,
p_TrDate in Date)
Return Number
is
Cursor Avg_Cur is
select 1 SeqNo,M.TrNo TrNo,M.TrDate TrDate,D.ItmID,Nvl(D.Qty,0) Qty,
Nvl(D.Rate,0) + Nvl(D.AvConItmRate,0) Rate,Nvl(D.Qty,0) * (Nvl(D.Rate,0) + Nvl(D.AvConItmRate,0)) Amount
from enInOut_M M, enInOut_D D
where M.TrNo = D.TrNo
and M.TrDate <= p_TrDate
and D.ItmID = P_ItmID
and D.Flg = 1
Union All
select 2 SeqNo,M.TrNo TrNo,M.TrDate TrDate,D.ItmID,Nvl(D.Qty,0) Qty,
Nvl(AvgRate,0) Rate, Nvl(Qty,0) * Nvl(AvgRate,0) Amount
from enInOut_M M, enInOut_D D
where M.TrNo = D.TrNo
and M.TrNo <> p_TrNo
and M.TrDate <= p_TrDate
and D.ItmID = P_ItmID
and D.Flg = -1
Order By 3,1;
v_Qty Number(14,5) := 0;
v_Rate Number(14,5) := 0;
v_Value Number(14,5) := 0;
Begin
For Rate_Cur in Avg_Cur Loop
if Rate_Cur.SeqNo = 1 then
v_Qty := Nvl(v_Qty,0) + Nvl(Rate_Cur.Qty,0);
v_Value := Nvl(v_Value,0) + Nvl(Rate_Cur.Amount,0);
if v_Qty <> 0 then
v_Rate := Nvl(v_Value,0) / v_Qty;
Else
v_Rate := 0;
End If;
ElsIf Rate_Cur.SeqNo = 2 then
v_Qty := Nvl(v_Qty,0) - Nvl(Rate_Cur.Qty,0);
v_Value := Nvl(v_Value,0) - Nvl(Rate_Cur.Amount,0);
if v_Qty <> 0 then
v_Rate := Nvl(v_Value,0) / v_Qty;
Else
v_Rate := 0;
End If;
End If;
End Loop;
Return(v_Rate);
End;

waiting for your urgent & positive reply

Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807


When ever you pray
Please remember me and my family

[admin]

It would be more helpful if you will do the following,

1- paste create table scripts instead of desc.
2- some sample data with insert statement.
3- Few test results you peroformed.

Hope you got my point.

[mirza_rehan]

Assalam-o-Alaikum

Basicly it work well but i am fist time use same table for IN and OUT transaction....... i want some one audit it and tell me if any problem exists.....

I give you create table statements but in this many foreign keys available which disturb u

there is not attachmen option so i will mail u??? at admin@erpstuff.com ?? or any other address????

please reply me


Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807

When ever you pray
Please remember me and my family

[admin]

Email at the same and I will upload in this thread.

[mirza_rehan]

Mr. Admin i was send you scirpt, please audit i and reply promptly. According to me it work well but please audit it

Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807

When ever you pray
Please remember me and my family

[mirza_rehan]

Mr. Admin i was send you scirpt yesterday, but there is not reply ???? please audit it and reply promptly. According to me it work well bu i need some one else audit it..............

Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807


When ever you pray
Please remember me and my family

[admin]

Pl download the scripts to create tables and procedure for the audit purpose.


[img]/images/icon_zip.gif[/img] <b>Attachment:</b> scripts.zip <i>( 730bytes )</i>

[mirza_rehan]

Mr. Admin you are not audit this script you just put my scrits hers?????

Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807

When ever you pray
Please remember me and my family

[admin]

Pl read my above thread,

" Email at the same and I will upload in this thread."