Skip to Content

Linking Outgoing payments to G/L accounts

Hello Dear Experts,

I have the following query, I am trying yo link the G/L account names for each payment. However, my account names comes out blank. Can anybody help?

SELECT

T0.[DocDate] AS 'Payment Date',

T0.[DocNum] AS 'DocNum',

CASE WHEN T1.InvType = 18 THEN 'PU' WHEN T1.InvType = 30 THEN 'JE' END AS 'Document Type',

T0.[CardCode] AS 'Vendor Code',

T0.[CardName] AS 'Vendor Name',

T3.[DocNum] AS 'Vendor Invoice',

T3.[NumAtCard] AS 'Vendor Invoice Ref',

T3.[DocTotal] AS 'Invoice Amount',

Case When T0.[CashSum] > 0 Then 'Cash' When T0.[CreditSum] > 0 Then 'Credit Card' When T0.[TrsfrSum] > 0 Then 'Wire' Else 'Check' End AS 'Paid By',

T2.[CheckNum] AS 'Check #',

T0.[TrsfrRef] AS 'Wire Ref',

T0.[DocTotal] AS 'Paid Amount',

T4.[AcctName],

T5.[AcctName]

FROM OVPM T0

INNER JOIN VPM2 T1 ON T1.DocNum = T0.DocEntry

LEFT JOIN VPM1 T2 ON T1.DocNum = T2.DocNum

LEFT JOIN OPCH T3 ON T1.DocEntry = T3.DocEntry

LEFT JOIN VPM4 T4 ON T0.docnum = T4.DocNum

LEFT JOIN OACT T5 ON t4.acctcode = t5.AcctCode

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

7 Answers

  • Posted on Oct 15, 2019 at 04:54 AM

    try this

    it's working fine

    SELECT T0."DocDate" AS "Payment Date", T0."DocNum" AS "DocNum",
    CASE
    WHEN T1."InvType" = 18 THEN 'PU'
    WHEN T1."InvType" = 30 THEN 'JE'
    END AS "Document Type", T0."CardCode" AS "Vendor Code", T0."CardName" AS "Vendor Name",
    T3."DocNum" AS "Vendor Invoice", T3."NumAtCard" AS "Vendor Invoice Ref", T3."DocTotal" AS "Invoice Amount",
    CASE
    WHEN T0."CashSum" > 0 THEN 'Cash'
    WHEN T0."CreditSum" > 0 THEN 'Credit Card'
    WHEN T0."TrsfrSum" > 0 THEN 'Wire'
    ELSE 'Check'
    END AS "Paid By", T2."CheckNum" AS "Check #", T0."TrsfrRef" AS "Wire Ref", T0."DocTotal" AS "Paid Amount",
    T4."AcctName", OACT."AcctName"
    FROM OVPM T0
    INNER JOIN VPM2 T1 ON T1."DocNum" = T0."DocEntry"
    LEFT OUTER JOIN VPM1 T2 ON T1."DocNum" = T2."DocNum"
    LEFT OUTER JOIN OPCH T3 ON T1."DocEntry" = T3."DocEntry"
    LEFT OUTER JOIN VPM4 T4 ON T0."DocNum" = T4."DocNum"
    INNER JOIN OJDT ON T0."TransId" = OJDT."TransId"
    INNER JOIN JDT1 ON OJDT."TransId" = JDT1."TransId" AND JDT1."Debit"<>0
    INNER JOIN OACT ON OACT."AcctCode" = JDT1."Account"


    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 09, 2019 at 07:22 AM

    Sorry Brother but i got error while executing this

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 09, 2019 at 06:55 AM

    Dear @ahmet karakuza

    Can you please tell me which account Name you need it i mean Vendor name or you want see Journal Entry hitting.

    please check below Query

    SELECT T0.[DocDate] AS 'Payment Date', T0.[DocNum] AS 'DocNum', CASE WHEN T1.InvType = 18 THEN 'PU' WHEN T1.InvType = 30 THEN 'JE' END AS 'Document Type', T0.[CardCode] AS 'Vendor Code', T0.[CardName] AS 'Vendor Name', T3.[DocNum] AS 'Vendor Invoice', T3.[NumAtCard] AS 'Vendor Invoice Ref', T3.[DocTotal] AS 'Invoice Amount', Case When T0.[CashSum] > 0 Then 'Cash' When T0.[CreditSum] > 0 Then 'Credit Card' When T0.[TrsfrSum] > 0 Then 'Wire' Else 'Check' End AS 'Paid By', T2.[CheckNum] AS 'Check #', T0.[TrsfrRef] AS 'Wire Ref', T0.[DocTotal] AS 'Paid Amount', isnull(T3.CardNAme,T5.AcctName) 'Name', T4.[AcctName], T5.[AcctName] FROM OVPM T0 INNER JOIN VPM2 T1 ON T1.DocNum = T0.DocEntry LEFT JOIN VPM1 T2 ON T1.DocNum = T2.DocNum LEFT JOIN OPCH T3 ON T1.DocEntry = T3.DocEntry LEFT JOIN VPM4 T4 ON T0.docnum = T4.DocNum LEFT JOIN OACT T5 ON t4.acctcode = t5.AcctCode

    Regards,

    Brij

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 09, 2019 at 07:17 AM

    TRY THIS

    SELECT OVPM."DocDate",TO_VARCHAR(OVPM."DocNum") AS "DocNum",NNM1."BeginStr",OVPM."CounterRef", OVPM."Comments",OVPM."TransId",OVPM."JrnlMemo",JDT1."Debit", OACT."AcctCode", (CASE When IFNULL((Select "CardName" from OCRD Where "CardCode" = JDT1."ShortName"),'') = '' THEN OACT."AcctName" ELSE ( Select "CardName" from OCRD Where "CardCode" = JDT1."ShortName") END) AS "AcctName", (CASE WHEN OVPM."CashSum"!=0 AND OVPM."CheckSum" =0 AND OVPM."TrsfrSum"=0 THEN 'Cash' ELSE 'Bank' END) AS "ModeOfPayment", (CASE WHEN OVPM."CashSum" <> 0 AND OVPM."CheckSum" =0 AND OVPM."TrsfrSum"=0 THEN (select OACT."AcctName" from OACT where OACT."AcctCode"=OVPM."CashAcct") WHEN OVPM."CashSum"=0 AND OVPM."CheckSum" <> 0 AND OVPM."TrsfrSum"=0 THEN (select OACT."AcctName" from OACT where OACT."AcctCode"=VPM1."CheckAct") ELSE (select OACT."AcctName" from OACT where OACT."AcctCode"=OVPM."TrsfrAcct" ) END ) AS "VenderAccount", IFNULL(OLCT."Street",'') AS "compname", IFNULL(OLCT."Block",'') || ' ' || IFNULL(OLCT."City",'') AS "COMPADDRESS", IFNULL(OLCT.U_UNE_PHNO,'') || (CASE WHEN IFNULL(OLCT.U_UNE_PHN2,'') = '' THEN '' ELSE '/' END) || IFNULL(OLCT.U_UNE_PHN2,'') AS "Phone", OLCT.U_UNE_EMAL,OLCT.U_UNE_FAX FROM OVPM LEFT OUTER JOIN VPM1 ON VPM1."DocNum"=OVPM."DocNum" INNER JOIN OJDT ON OVPM."TransId" = OJDT."TransId" INNER JOIN JDT1 ON OJDT."TransId" = JDT1."TransId" AND JDT1."Debit"<>0 INNER JOIN OACT ON OACT."AcctCode" = JDT1."Account" LEFT OUTER JOIN OLCT ON OLCT."Location" = JDT1."Project" LEFT OUTER JOIN NNM1 ON NNM1."Series"=OVPM."Series" WHERE OVPM."DocEntry" =:DocEntry ORDER BY JDT1."DebCred" DESC; END ;


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 09, 2019 at 07:27 AM

    FOR sql

    SELECT OVPM."DocDate",(OVPM."DocNum") AS "DocNum",
    NNM1."BeginStr",
    OVPM."CounterRef",
    OVPM."Comments",OVPM."TransId",
    OVPM."JrnlMemo",JDT1."Debit", OACT."AcctCode",
    (CASE When ISNULL((Select "CardName"
    from OCRD Where "CardCode" = JDT1."ShortName"),'') = '' THEN OACT."AcctName"
    ELSE ( Select "CardName" from OCRD Where "CardCode" = JDT1."ShortName") END) AS "AcctName",
    (CASE WHEN OVPM."CashSum"!=0 AND OVPM."CheckSum" =0 AND OVPM."TrsfrSum"=0 THEN 'Cash' ELSE 'Bank' END) AS "ModeOfPayment",
    (CASE WHEN OVPM."CashSum" <> 0 AND OVPM."CheckSum" =0 AND OVPM."TrsfrSum"=0 THEN (select OACT."AcctName"
    from OACT
    where OACT."AcctCode"=OVPM."CashAcct") WHEN OVPM."CashSum"=0 AND OVPM."CheckSum" <> 0
    AND OVPM."TrsfrSum"=0 THEN (select OACT."AcctName" from OACT where OACT."AcctCode"=VPM1."CheckAct") ELSE (select OACT."AcctName"
    from OACT where OACT."AcctCode"=OVPM."TrsfrAcct" ) END ) AS "VenderAccount",
    ISNULL(OLCT."Street",'') AS "compname"
    FROM OVPM
    LEFT OUTER JOIN VPM1 ON VPM1."DocNum"=OVPM."DocNum"
    INNER JOIN OJDT ON OVPM."TransId" = OJDT."TransId"
    INNER JOIN JDT1 ON OJDT."TransId" = JDT1."TransId" AND JDT1."Debit"<>0
    INNER JOIN OACT ON OACT."AcctCode" = JDT1."Account"
    LEFT OUTER JOIN OLCT ON OLCT."Location" = JDT1."Project"
    LEFT OUTER JOIN NNM1 ON NNM1."Series"=OVPM."Series"
    WHERE OVPM."DocEntry" =:DocEntry
    ORDER BY JDT1."DebCred" DESC
    END

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 09, 2019 at 07:30 AM

    Dear

    ahmet karakuza

    This is your query

    SELECT T0."DocDate" AS "Payment Date", T0."DocNum" AS "DocNum",
    CASE
    WHEN T1."InvType" = 18 THEN 'PU'
    WHEN T1."InvType" = 30 THEN 'JE'
    END AS "Document Type", T0."CardCode" AS "Vendor Code", T0."CardName" AS "Vendor Name",
    T3."DocNum" AS "Vendor Invoice", T3."NumAtCard" AS "Vendor Invoice Ref", T3."DocTotal" AS "Invoice Amount",
    CASE
    WHEN T0."CashSum" > 0 THEN 'Cash'
    WHEN T0."CreditSum" > 0 THEN 'Credit Card'
    WHEN T0."TrsfrSum" > 0 THEN 'Wire'
    ELSE 'Check'
    END AS "Paid By", T2."CheckNum" AS "Check #", T0."TrsfrRef" AS "Wire Ref", T0."DocTotal" AS "Paid Amount",
    T4."AcctName", T5."AcctName"
    FROM OVPM T0
    INNER JOIN VPM2 T1 ON T1."DocNum" = T0."DocEntry"
    LEFT OUTER JOIN VPM1 T2 ON T1."DocNum" = T2."DocNum"
    LEFT OUTER JOIN OPCH T3 ON T1."DocEntry" = T3."DocEntry"
    LEFT OUTER JOIN VPM4 T4 ON T0."DocNum" = T4."DocNum"
    INNER JOIN OJDT ON T0."TransId" = OJDT."TransId"
    INNER JOIN JDT1 ON OJDT."TransId" = JDT1."TransId" AND JDT1."Debit"<>0
    INNER JOIN OACT ON OACT."AcctCode" = JDT1."Account"

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 09, 2019 at 07:26 AM
    -1

    CHANGE ACCORDINGLY

    IN SQL

    IFNULL (isnull)

    & || (CONCAT FUNCTION)

    Add a comment
    10|10000 characters needed characters exceeded