Home » Developer & Programmer » Reports & Discoverer » Report Query
Report Query [message #175690] Sun, 04 June 2006 20:59 Go to next message
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 Go to previous message
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

Report message to a moderator

Previous Topic: Curved Graphs
Next Topic: Rep-50159, mail in report 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Nov 26 12:44:47 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.