Skip to Content
author's profile photo Former Member
Former Member

Wants Combain query of GRPO -Return- AP Invoice

Dear All,

Pls. help me for this query.

A) GRPO :

Vendor code

Vendor name

Doc Num

Doc status

Posting date

Total Before discount

Tax

Freight

Total Payment Due

B) IF GOODS RETURNS ARE DONE THEN:

Doc Num

Doc status

Posting date

Total Before discount

Tax

Freight

Total Payment Due

C) IF AFTER GRPO ,AP INVOICE DONE THEN:

Doc Num

Vendor referance Number.

Doc status

Posting date

Total Before discount

Tax

Freight

Total Payment Due

(A) ,(B),(C) should in a single query.

If possible pls. send me query.

Regards.

Mahesh.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Feb 12, 2010 at 09:53 AM

    All the inventory transactions are stored in OINM table.

    You can write a query to display the required values from OINM table

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 13, 2010 at 12:19 AM

    Try this one:

    SELECT DISTINCt T1.CARDCODE, T1.CARDNAME , T1.DOCNUM GRPO#,T1.DocStatus 'GRPO Status', T1.DOCDATE 'GRPO Date', T1.DocTotal 'GRPO Total', T3.DOCNUM Return#,T3.DocStatus 'Return Status', T3.DOCDATE 'Return Date', T3.DocTotal 'Return Total',T5.DOCNUM Invoice#,T5.DocStatus 'Invoice Status', T5.DOCDATE 'Invoice Date', (T5.DocTotal-T5.VatSum-T5.TotalExpns-T5.DiscSum) 'Total Before Discount', T5.VatSum 'Tax', T5.TotalExpns 'Freight', (T5.DocTotal - T5.PaidToDate) 'Total Payment Due'

    FROM DBO.PDN1 T0

    INNER JOIN DBO.OPDN T1 ON T1.DOCENTRY = T0.DOCENTRY

    LEFT JOIN DBO.RPD1 T2 ON T2.BASEENTRY = T1.DOCENTRY

    LEFT JOIN DBO.ORPD T3 ON T2.DOCENTRY = T3.DOCENTRY

    LEFT JOIN DBO.PCH1 T4 ON T4.BASEENTRY = T1.DOCENTRY

    LEFT JOIN DBO.OPCH T5 ON T5.DOCENTRY = T4.DOCENTRY

    WHERE T1.DOCNUM like '[%0\]%'

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.