Purchase Query through Control Account - Correction

[Former Member]

Dear Experts,

                    Can anybody help me to correct the below query, where I need to incorporate the Control Account (OINV-Ctlaccount) as one another selection parameter in the existing one.

Try this :-

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE

@StartDate DATETIME,
@EndDate DATETIME,
@Dummy INTEGER

SELECT TOP 1 @Dummy = DocNum
FROM OPCH T0
WHERE T0.DocDate >= [%1]
AND T0.DocDate <= [%2]

SELECT

@StartDate= '[%1]'
,
@EndDate = '[%2]'

SELECT DISTINCT
T0.DocEntry,
T0.DocNum AS 'A/P Inv Num',
CONVERT(VARCHAR(10), T0.DocDate, 3) AS 'Bill Date',
T0.CardName AS 'Supplier Name',
-- T1.Dscription AS 'Item Description',
----T1.Quantity,
--T1.Price AS 'Price',
--T1.TaxCode,
SUM(T1.LineTotal) AS 'Asscessable Value',
MAX(T1.TaxCode),
--t1.vatsum as 'TaxAmount',
T0.TOTALEXPNS AS [Freight Amount],
(SUM(T1.LineTotal)+SUM(T1.VATSUM)+T0.TOTALEXPNS+T0.rounddif) as [TotAmt],
(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType IN (11,-90)
AND PCH4.DocEntry = T0.DocEntry
-- AND PCH4.LineNum = T1.LineNum
) AS 'BED',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType IN( -60,12)
AND PCH4.DocEntry = T0.DocEntry
-- AND PCH4.LineNum = T1.LineNum
) AS 'Cess',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType IN (-55,13)
AND PCH4.DocEntry = T0.DocEntry
-- AND PCH4.LineNum = T1.LineNum
) AS 'HeCess',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType IN (5)
AND PCH4.DocEntry = T0.DocEntry
-- AND PCH4.LineNum = T1.LineNum
) AS 'Service',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType IN (6)
AND PCH4.DocEntry = T0.DocEntry
-- AND PCH4.LineNum = T1.LineNum
) AS 'Cess_ST',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType IN (-10)
AND PCH4.DocEntry = T0.DocEntry
-- AND PCH4.LineNum = T1.LineNum
) AS 'HSCess_ST',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType IN (1,15)
AND PCH4.DocEntry = T0.DocEntry
-- AND PCH4.LineNum = T1.LineNum
) AS 'VAT',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)
FROM PCH4
WHERE PCH4.StaType = 4
AND PCH4.DocEntry = T0.DocEntry
-- AND PCH4.LineNum = T1.LineNum
) AS 'CST',
T0.Comments
--,ISNULL(T10.WTCODE,0) AS [WTCODE],ISNULL(T10.RATE,0) AS [RATE],ISNULL(T10.WTAMNT,0) AS [WTAMNT]
FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN PCH5 T10 ON T0.DOCENTRY = T10.ABSENTRY
LEFT JOIN OACT T99 ON T99.ACCTCODE = T1.ACCTCODE
WHERE T0.DocDate >= @StartDate
AND T0.DocDate <= @EndDate
AND T1.TargetType < > 19
--AND T0.DOCENTRY = 8679
AND (T99.ACCTname = '[%3]' OR '[%3]' = '')
GROUP BY T0.DOCENTRY,T0.DOCNUM,T0.DOCDATE,T0.CARDNAME,T0.TOTALEXPNS,T0.ROUNDDIF,T0.COMMENTS

FOR BROWSE

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks in Advance,

Bhoopathi.K

[kothandaraman_nagarajan · Accepted solution]

Hi,

1. If run above query, getting error message.

2. Already above query is added account name from OACT. Why you need from OPCH table?

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Dear Nagarajan,

                       Please find the attached Screen shot, where the red color marked area should be my selection parameter.

Regards,

Bhoopathi.K

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

DECLARE

@StartDate DATETIME,

@EndDate DATETIME,

@Dummy INTEGER

SELECT TOP 1 @Dummy = DocNum

FROM OPCH T0

WHERE T0.DocDate >= [%1]

AND T0.DocDate <= [%2]

SELECT

@StartDate= '[%1]'

,

@EndDate = '[%2]'

SELECT DISTINCT

T0.DocEntry,

T0.DocNum AS 'A/P Inv Num',

CONVERT(VARCHAR(10), T0.DocDate, 3) AS 'Bill Date',

T0.CardName AS 'Supplier Name',

-- T1.Dscription AS 'Item Description',

----T1.Quantity,

--T1.Price AS 'Price',

--T1.TaxCode,

SUM(T1.LineTotal) AS 'Asscessable Value',

MAX(T1.TaxCode),

--t1.vatsum as 'TaxAmount',

T0.TOTALEXPNS AS [Freight Amount],

(SUM(T1.LineTotal)+SUM(T1.VATSUM)+T0.TOTALEXPNS+T0.rounddif) as [TotAmt],

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (11,-90)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'BED',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN( -60,12)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'Cess',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (-55,13)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'HeCess',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (5)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'Service',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (6)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'Cess_ST',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (-10)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'HSCess_ST',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (1,15)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'VAT',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = 4

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'CST',

T0.Comments

--,ISNULL(T10.WTCODE,0) AS [WTCODE],ISNULL(T10.RATE,0) AS [RATE],ISNULL(T10.WTAMNT,0) AS [WTAMNT]

FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN PCH5 T10 ON T0.DOCENTRY = T10.ABSENTRY

LEFT JOIN OACT T99 ON T99.ACCTCODE = T1.ACCTCODE

WHERE T0.DocDate >= @StartDate

AND T0.DocDate <= @EndDate

AND T1.TargetType < > 19

--AND T0.DOCENTRY = 8679

AND (T99.ACCTname = '[%3]' OR '[%3]' = '') OR  (T99.[AcctCode] = '[%4]' OR '[%4]' = '')

GROUP BY T0.DOCENTRY,T0.DOCNUM,T0.DOCDATE,T0.CARDNAME,T0.TOTALEXPNS,T0.ROUNDDIF,T0.COMMENTS

FOR BROWSE

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Dear Nagarajan,

                    The above query pulls out the data from other Control accounts also. I want exactly from the selected ledger only.

Regards,

Bhoopathi.K

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

DECLARE

@StartDate DATETIME,

@EndDate DATETIME,

@Dummy INTEGER

SELECT TOP 1 @Dummy = DocNum

FROM OPCH T0

WHERE T0.DocDate >= [%1]

AND T0.DocDate <= [%2]

SELECT

@StartDate= '[%1]'

,

@EndDate = '[%2]'

SELECT DISTINCT

T0.DocEntry,

T0.DocNum AS 'A/P Inv Num',

CONVERT(VARCHAR(10), T0.DocDate, 3) AS 'Bill Date',

T0.CardName AS 'Supplier Name',

-- T1.Dscription AS 'Item Description',

----T1.Quantity,

--T1.Price AS 'Price',

--T1.TaxCode,

SUM(T1.LineTotal) AS 'Asscessable Value',

MAX(T1.TaxCode),

--t1.vatsum as 'TaxAmount',

T0.TOTALEXPNS AS [Freight Amount],

(SUM(T1.LineTotal)+SUM(T1.VATSUM)+T0.TOTALEXPNS+T0.rounddif) as [TotAmt],

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (11,-90)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'BED',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN( -60,12)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'Cess',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (-55,13)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'HeCess',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (5)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'Service',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (6)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'Cess_ST',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (-10)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'HSCess_ST',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (1,15)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'VAT',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = 4

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'CST',

T0.Comments

--,ISNULL(T10.WTCODE,0) AS [WTCODE],ISNULL(T10.RATE,0) AS [RATE],ISNULL(T10.WTAMNT,0) AS [WTAMNT]

FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN PCH5 T10 ON T0.DOCENTRY = T10.ABSENTRY

LEFT JOIN OACT T99 ON T99.ACCTCODE = T0.[CtlAccount]

WHERE T0.DocDate >= @StartDate

AND T0.DocDate <= @EndDate

AND T1.TargetType < > 19

--AND T0.DOCENTRY = 8679

AND (T99.ACCTname = '[%3]' OR '[%3]' = '') OR  (T99.[AcctCode] = '[%4]' OR '[%4]' = '')

GROUP BY T0.DOCENTRY,T0.DOCNUM,T0.DOCDATE,T0.CARDNAME,T0.TOTALEXPNS,T0.ROUNDDIF,T0.COMMENTS

FOR BROWSE

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Dear Nagarajan,

                      Still picks the values from other ledgers(Other than what I have selected) and also from another periods having same same invoice number.

Regards,

Bhoopathi.K

[kothandaraman_nagarajan · Accepted solution]

Hi,

You can query either by account name or code, not possible by both parameter in same query.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Dear Nagarajan,

                           I will be very much happy, if I can take from account name. But, I need the values should be exact.

Regards,

Bhoopathi.K

Nagarajan K wrote:

Hi,

You can query either by account name or code, not possible by both parameter in same query.

Thanks & Regards,
Nagarajan

[kothandaraman_nagarajan · Accepted solution]

Hi,

Check this:

DECLARE

@StartDate DATETIME,

@EndDate DATETIME,

@Dummy INTEGER

SELECT TOP 1 @Dummy = DocNum

FROM OPCH T0

WHERE T0.DocDate >= [%1]

AND T0.DocDate <= [%2]

SELECT

@StartDate= '[%1]'

,

@EndDate = '[%2]'

SELECT DISTINCT

T0.DocEntry,

T0.DocNum AS 'A/P Inv Num',

CONVERT(VARCHAR(10), T0.DocDate, 3) AS 'Bill Date',

T0.CardName AS 'Supplier Name',

-- T1.Dscription AS 'Item Description',

----T1.Quantity,

--T1.Price AS 'Price',

--T1.TaxCode,

SUM(T1.LineTotal) AS 'Asscessable Value',

MAX(T1.TaxCode),

--t1.vatsum as 'TaxAmount',

T0.TOTALEXPNS AS [Freight Amount],

(SUM(T1.LineTotal)+SUM(T1.VATSUM)+T0.TOTALEXPNS+T0.rounddif) as [TotAmt],

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (11,-90)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'BED',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN( -60,12)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'Cess',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (-55,13)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'HeCess',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (5)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'Service',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (6)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'Cess_ST',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (-10)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'HSCess_ST',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType IN (1,15)

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'VAT',

(SELECT DISTINCT ISNULL (SUM(PCH4.TaxSum),0)

FROM PCH4

WHERE PCH4.StaType = 4

AND PCH4.DocEntry = T0.DocEntry

-- AND PCH4.LineNum = T1.LineNum

) AS 'CST',

T0.Comments

--,ISNULL(T10.WTCODE,0) AS [WTCODE],ISNULL(T10.RATE,0) AS [RATE],ISNULL(T10.WTAMNT,0) AS [WTAMNT]

FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN PCH5 T10 ON T0.DOCENTRY = T10.ABSENTRY

LEFT JOIN OACT T99 ON T99.ACCTCODE = T0.[CtlAccount]

WHERE T0.DocDate >= @StartDate

AND T0.DocDate <= @EndDate

AND T1.TargetType < > 19

--AND T0.DOCENTRY = 8679

AND (T99.ACCTname = '[%3]' OR '[%3]' = '')

GROUP BY T0.DOCENTRY,T0.DOCNUM,T0.DOCDATE,T0.CARDNAME,T0.TOTALEXPNS,T0.ROUNDDIF,T0.COMMENTS

FOR BROWSE

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi Nagarajan,

                 Thanks.

Regards,

Bhoopathi.K

[Former Member]

Hi Bhoopati,

Please correct me if am wrong.

You want to include OINV.Ctlaccount or OPCH.Ctlaccount

As this query is for Purchase Invoice.

--

--

Regards::::

Atul Chakraborty

[Former Member]

Dear Atul Chakraborty,

                    You are right. I want to include OPCH.Ctlaccount. I need details of purchase invoice only.

Regards,

Bhoopathi.K