Home » Developer & Programmer » Reports & Discoverer » Report Query
| Report Query [message #175690] |
Sun, 04 June 2006 20:59  |
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
|
Senior Member |
|
|
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  |
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: Sun Jun 30 12:07:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
