Home » Developer & Programmer » Reports & Discoverer » Report Query
Report Query [message #175690] |
Sun, 04 June 2006 20:59 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
samit_gandhi
Messages: 226 Registered: July 2005 Location: Hong Kong
|
Senior Member |
![samit_gandhi](/forum/theme/orafaq/images/yahoo.png)
|
|
Dear All,
I have a query which is used for counting my on hand stock. I have different design no. and we are manufacturing the goods of that design no. and separate each piece by its counter. I want the on hand stock of each counter. There are different stages of the design like purchase, sales, purchase return, sales return, consignment out, consignment in, consignment return etc. I want the on hand stock of the same. I have written the following query but it is not showing me perfect stock.
Is anyone can help me to solve this problem. I am stuck due to it.
(((SELECT A.REF_NO, A.REF_CODE,A.GROUP_CODE, A.DESCRIPTION, A.REF_GROUP, A.COMPANY_ID, B.COUNTER
FROM REF_MASTER A, PURCHASE_DETAIL B
WHERE A.REF_CODE=B.REF_CODE
AND A.GROUP_CODE=B.GROUP_CODE
AND A.COMPANY_ID=B.COMPANY_ID
AND A.COMPANY_ID=:FCOMP_ID)
UNION ALL
(SELECT A.REF_NO, A.REF_CODE,A.GROUP_CODE, A.DESCRIPTION, A.REF_GROUP, A.COMPANY_ID, B.COUNTER
FROM REF_MASTER A, SALES_RETURN_DETAIL B
WHERE A.REF_CODE=B.REF_CODE
AND A.GROUP_CODE=B.GROUP_CODE
AND A.COMPANY_ID=B.COMPANY_ID
AND A.COMPANY_ID=:FCOMP_ID)
UNION ALL
(SELECT A.REF_NO, A.REF_CODE,A.GROUP_CODE, A.DESCRIPTION, A.REF_GROUP, A.COMPANY_ID, B.COUNTER
FROM REF_MASTER A, LOT_TRANSFER_TO B
WHERE A.REF_CODE=B.REF_CODE
AND A.GROUP_CODE=B.GROUP_CODE
AND A.COMPANY_ID=B.COMPANY_ID
AND A.COMPANY_ID=:FCOMP_ID)
UNION ALL
(SELECT A.REF_NO, A.REF_CODE,A.GROUP_CODE, A.DESCRIPTION, A.REF_GROUP, A.COMPANY_ID, B.COUNTER
FROM REF_MASTER A, MEMO_PURCHASE_DETAIL B
WHERE A.REF_CODE=B.REF_CODE
AND A.GROUP_CODE=B.GROUP_CODE
AND A.COMPANY_ID=B.COMPANY_ID
AND A.COMPANY_ID=:FCOMP_ID)
UNION ALL
(SELECT A.REF_NO, A.REF_CODE,A.GROUP_CODE, A.DESCRIPTION, A.REF_GROUP, A.COMPANY_ID, B.COUNTER
FROM REF_MASTER A, MEMO_SALES_DETAIL_RETURN B
WHERE A.REF_CODE=B.STOCK_CODE
AND A.GROUP_CODE=B.GROUP_CODE
AND A.COMPANY_ID=B.COMPANY_ID
AND A.COMPANY_ID=:FCOMP_ID)
UNION ALL
(SELECT A.REF_NO, A.REF_CODE,A.GROUP_CODE, A.DESCRIPTION, A.REF_GROUP, A.COMPANY_ID, B.COUNTER
FROM REF_MASTER A, SALES_DETAIL B
WHERE A.REF_CODE=B.REF_CODE
AND A.GROUP_CODE=B.GROUP_CODE
AND A.COMPANY_ID=B.COMPANY_ID
AND A.COMPANY_ID=:FCOMP_ID
AND B.RETURN_QTY=1
AND B.SALES_THROUGH='MEMO'))
MINUS((SELECT A.REF_NO, A.REF_CODE,A.GROUP_CODE, A.DESCRIPTION, A.REF_GROUP, A.COMPANY_ID, B.COUNTER
FROM REF_MASTER A, SALES_DETAIL B
WHERE A.REF_CODE=B.REF_CODE
AND A.GROUP_CODE=B.GROUP_CODE
AND A.COMPANY_ID=B.COMPANY_ID
AND A.COMPANY_ID=:FCOMP_ID)
UNION ALL
(SELECT A.REF_NO, A.REF_CODE,A.GROUP_CODE, A.DESCRIPTION, A.REF_GROUP, A.COMPANY_ID, B.COUNTER
FROM REF_MASTER A, PURCHASE_RETURN_DETAIL B
WHERE A.REF_CODE=B.REF_CODE
AND A.GROUP_CODE=B.GROUP_CODE
AND A.COMPANY_ID=B.COMPANY_ID
AND A.COMPANY_ID=:FCOMP_ID)
UNION ALL
(SELECT A.REF_NO, A.REF_CODE,A.GROUP_CODE, A.DESCRIPTION, A.REF_GROUP, A.COMPANY_ID, B.COUNTER
FROM REF_MASTER A, LOT_TRANSFER_FROM B
WHERE A.REF_CODE=B.REF_CODE
AND A.GROUP_CODE=B.GROUP_CODE
AND A.COMPANY_ID=B.COMPANY_ID
AND A.COMPANY_ID=:FCOMP_ID)
UNION ALL
(SELECT A.REF_NO, A.REF_CODE,A.GROUP_CODE, A.DESCRIPTION, A.REF_GROUP, A.COMPANY_ID, B.COUNTER
FROM REF_MASTER A, MEMO_SALES_DETAIL B
WHERE A.REF_CODE=B.REF_CODE
AND A.GROUP_CODE=B.GROUP_CODE
AND A.COMPANY_ID=B.COMPANY_ID
AND A.COMPANY_ID=:FCOMP_ID)
))
waiting for your immediate reply.
thx in advance
samit
[Updated on: Sun, 04 June 2006 21:01] Report message to a moderator
|
|
|
Re: Report Query [message #175773 is a reply to message #175690] |
Mon, 05 June 2006 05:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
skooman
Messages: 913 Registered: March 2005 Location: Netherlands
|
Senior Member |
|
|
Hi Samit,
Since we don't have access to and/or knowlegde about the datamodel you're working on, you have to help us a bit more. What do you want the result to be exactly, what result do you get, etc?
Some (simplified) table creation scripts and inserts of sample data would be great.
Regards,
Sabine
|
|
|
Goto Forum:
Current Time: Wed Sep 11 20:54:02 CDT 2024
|