Average Rate
-
- Posts: 132
- Joined: Sun Apr 02, 2006 10:36 am
- Location: Pakistan
Average Rate
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
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
-
- Posts: 132
- Joined: Sun Apr 02, 2006 10:36 am
- Location: Pakistan
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
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
-
- Posts: 132
- Joined: Sun Apr 02, 2006 10:36 am
- Location: Pakistan
-
- Posts: 132
- Joined: Sun Apr 02, 2006 10:36 am
- Location: Pakistan
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
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
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>
[img]/images/icon_zip.gif[/img] <b>Attachment:</b> scripts.zip <i>( 730bytes )</i>
-
- Posts: 132
- Joined: Sun Apr 02, 2006 10:36 am
- Location: Pakistan
Who is online
Users browsing this forum: No registered users and 0 guests