| Author |
 Topic  |
|
|
AndroBast
Starting Member
Indonesia
6 Posts |
 Posted - 03/06/2013 : 06:48:54
|
Dear Experts,
I am trying to use this following query to track the balance quantity of Purchase Order (PO) after receipt and return. What I mean here is the shortage quantity of PO after receipt or / and return partially. What I mean is the remain PO quantity sum from the supplier.
Here is the developed query but not success to solve my problem:
select por1.LineNum+1"linenum", por1.ItemCode, s.baseentry, s.returnRunningTotalQty"Qty Return",
isnull(por1.quantity,0)"Qty PO", (isnull(s.[remaining quantity after return],0))"running total GRPO", s.DocEntry"GRPO docentry",
isnull(por1.Quantity,0)-ISNULL(s.[remaining quantity after return],0)"shortage"
from
por1 inner join
( select pdn.DocEntry, pdn.BaseEntry, pdn.BaseLine, pdn.grporunningtotalqty, pdn.GRQty, rpd.ReturnRunningTotalQty, rpd.GRTQty, rpd.baseline"grtbaseline",
CASE when isnull(rpd.GRTQty,0) = 0
then isnull(pdn.grporunningtotalqty,0)
when isnull(rpd.GRTQty,0) > 0
then (isnull(pdn.grporunningtotalqty,0) - isnull(rpd.ReturnRunningTotalQty,0)) end as 'remaining quantity after return'
from
( select distinct
--a.Itemcode,
a.docentry,
a.Quantity"GRQty", a.LineNum,
a.Baseref,
a.baseline,
a2.basetype,
a2.baseentry,
a2.u_sol_return,
grporunningtotalqty = SUM(isnull(a2.quantity,0))
FROM pdn1 a
INNER JOIN pdn1 a2 ON a.DocEntry >= a2.DocEntry
--and a.BaseLine >= a2.baseline
where a.BaseEntry = a2.BaseEntry and a.BaseLine = a2.BaseLine
and a.LineNum = a2.LineNum
--(a2.U_RETURN = 'reject' or a.U_RETURN is null)
--and a.BaseLine = '0'
GROUP BY
a.Itemcode, a.LineNum, a.BaseLine, a.DocEntry,
a.quantity, a2.BaseType,
a.Baseref, a2.BaseEntry,
a2.u_sol_return
) pdn
inner join OPDN on pdn.docentry = opdn.docentry
left join
(
select distinct
--a.Itemcode,
a.Quantity"GRTQty",
a.Baseref,
a.baseline,
a2.basetype,
a2.baseentry,
a2.u_sol_return,
ReturnRunningTotalQty = SUM(isnull(a.Quantity,0))
FROM rpd1 a
INNER JOIN rpd1 a2 ON a.DocEntry >= a2.DocEntry
where a.BaseEntry >= a2.BaseEntry and a.itemcode = a2.itemcode and a.BaseLine >= a2.baseline
and (a2.U_SOL_RETURN = 'reject')
--and a.LineNum = a2.LineNum
--and a.BaseEntry = '1953'
--and a.quantity >= a2.quantity
GROUP BY
a.Itemcode, a.BaseLine,
a.quantity, a2.BaseType,
a.Baseref, a2.BaseEntry,
a2.u_sol_return
--order by a.BaseEntry
--) rpd1
) RPD on
rpd.BaseEntry = pdn.DocEntry and
rpd.BaseRef = opdn.DocNum and
rpd.BaseLine = pdn.LineNum and
rpd.BaseType = opdn.ObjType
) s
on s.BaseEntry = por1.DocEntry and s.BaseLine = por1.LineNum
--and s.grtbaseline >= por1.LineNum
where s.BaseEntry = '1000'
order by por1.LineNum asc
The detail can be seen in this link:
http://scn.sap.com/thread/3319700
there is a screen shot of the result in the link. The thread in the forum created by my work mate.
Could you please help me to solve the issue because my query can't give the expected result ? Thank you
Andro |
|
|
MIK_2008
Aged Yak Warrior
Pakistan
822 Posts |
Posted - 03/06/2013 : 07:04:05
|
How come if you provide sample data in the form of Insert statements and the desired output based on that sample data? e.g.
Cheers
MIK |
 |
|
|
AndroBast
Starting Member
Indonesia
6 Posts |
Posted - 03/06/2013 : 19:08:47
|
quote:
Originally posted by MIK_2008
How come if you provide sample data in the form of Insert statements and the desired output based on that sample data? e.g.
Cheers
MIK
Thanks for your reply.
Do you mean I have to use insert statement rather than subquery ? Could you please give an example ? I do not still understand. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/06/2013 : 23:27:38
|
quote:
Originally posted by AndroBast
quote:
Originally posted by MIK_2008
How come if you provide sample data in the form of Insert statements and the desired output based on that sample data? e.g.
Cheers
MIK
Thanks for your reply.
Do you mean I have to use insert statement rather than subquery ? Could you please give an example ? I do not still understand.
The suggestion was to post some sample data and explain your requirement
That will be easier for others to understand what issue is about
see below on guidelines
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
AndroBast
Starting Member
Indonesia
6 Posts |
Posted - 03/07/2013 : 00:44:09
|
Dear All,
Thanks for your replies.
The sample of my questions are as follows:
1. The user of the application created 1 purchase order
with details as follows:
The table of purchase orders are OPOR & POR1, the primary key to link the table is docentry.
Data input by user are in the table as follows:
A. POR1, (the field or column tables are: itemcode, linenum, quantity, docentry). OPOR is the header table only that recorded supplier properties and document total amount.
1. linenum : 0
2. itemcode : CX1006
3. quantity : 1000
4. docentry : 1671 (autocreated by application)
1. linenum : 1
2. Itemcode : CX1006
3. Quantity : 2000
4. docentry : 1671 1671 (autocreated by application)
Then the item CX1006 received from supplier.
The tables to record it are OPDN & PDN1.
Data input by user are in the table as follows:
A. PDN1, (the field or column tables are: itemcode, linenum, quantity, docentry, baseline, baseref, baseentry).
OPDN is the header table only that recorded supplier properties and document total amount. Here are the data input in the PDN1:
1. linenum : 0
2. Itemcode : CX1006
3. Quantity : 900
4. docentry : 1984 (autocreated by application)
5. baseentry : 1671 (autocreated by application)
6. baseline : 0
B. PDN1
1. linenum : 0
2. Itemcode : CX1006
3. Quantity : 100
4. docentry : 1985 (autocreated by application)
5. baseentry : 1671 (autocreated by application)
6. baseline : 0
C. PDN1
1. linenum : 0
2. Itemcode : CX1006
3. Quantity : 1200
4. docentry : 1986 (autocreated by application)
5. baseentry : 1671 (autocreated by application)
6. Baseline : 1
C. PDN1
1. linenum : 0
2. Itemcode : CX1006
3. Quantity : 500
4. docentry : 1987 (autocreated by application)
5. baseentry : 1671 (autocreated by application)
6. Baseline : 1
The user return some quantity of item CX1006. The returned quantity is 200
The tables are ORPD and RPD1. ORPD is the header table only that recorded supplier properties and document total amount.
The data input in the RPD1 are as follows:
1. linenum : 0
2. Itemcode : CX1006
3. Quantity : 200
4. docentry : 1984 (autocreated by application)
5. baseentry : 1671 (autocreated by application)
6. baseline : 0
Based on the above data, it summarrized as follows:
1. CX1006 for both linenum 0 & 1 are received 4 times with quantity per each are:
900, 100, 1200 and 500
Total received are : 2700 but returned 200. Remaining quantity to received are 500
2. using the query, the running shortage or cumulative shortage in the last row is only 300. The query I made can result in 500.
Based on the above sample, I really expected that the problem I submit here could be solved.
Regards,
Andro
|
|
|
|
AndroBast
Starting Member
Indonesia
6 Posts |
Posted - 03/08/2013 : 02:35:27
|
Dear All,
here is the sample of my question:
item PO Qty Received Return Shortage
CX1006 1000 900 0 100
CX1006 1000 100 0 0
CX1006 2000 1200 200 1000
CX1006 2000 500 0 500
CX1006 3000 3000 0 0
Purchase order (PO) table : POR1
Received Qty table : PDN1
Return Qty table : RPD1
Thank you |
Edited by - AndroBast on 03/08/2013 02:38:30 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/08/2013 : 05:41:38
|
so what should be your output?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
AndroBast
Starting Member
Indonesia
6 Posts |
Posted - 03/08/2013 : 11:44:09
|
quote:
Originally posted by visakh16
so what should be your output?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
My sample is the expected output but using the query I have developed, I can't get the expected output.
Here is the result of my query:
No Item Line Expected Qty (Purchase Order) Received Qty (Received Order) Return Qty (return document) Shortage
1 CX1006 1 1000 900 0 100
2 CX1006 1 1000 100 0 0
3 CX1006 2 2000 1200 200 1000
4 CX1006 2 2000 500 0 300
5 CX1006 3 3000 3000 0 0
The result of the number four in the shortage column should be 500 but using my query I only get 300. I need your help, all guys here in this forum to solve the problem. Thank you
|
Edited by - AndroBast on 03/10/2013 21:18:01 |
|
|
|
AndroBast
Starting Member
Indonesia
6 Posts |
Posted - 03/12/2013 : 21:27:40
|
quote:
Originally posted by AndroBast
My sample is the expected output but using the query I have developed, I can't get the expected output.
Here is the result of my query:
No Item Line Expected Qty (Purchase Order) Received Qty (Received Order) Return Qty (return document) Shortage
1 CX1006 1 1000 900 0 100
2 CX1006 1 1000 100 0 0
3 CX1006 2 2000 1200 200 1000
4 CX1006 2 2000 500 0 300
5 CX1006 3 3000 3000 0 0
The result of the number four in the shortage column should be 500 but using my query I only get 300. I need your help, all guys here in this forum to solve the problem. Thank you
Hi Experts,
Just want to inform that my problem is similar with the problem in this link :
http://stackoverflow.com/questions/6180874/sql-running-subtraction-and-deviation?rq=1
but there is no good return effect in the shortage calculation. So, in my problem, there is also good return that will affect the shortage. I need a good return qty column. And the calculation of the shortage will PO qty-REceived qty+good return qty.
I appreciate your help so much. TIA
Andro |
|
|
| |
Topic |
|
|
|
Cumulative shortage query
