Payment Voucher

Payment Voucher's Database

UI Name	Database	Type	Remarks
Date	cb.DOCDATE	Date	E.g.: 2024-11-15
Voucher No	cb.DOCNO	String	E.g.: PV12345
Payment To	cb.DESCRIPTION	String	E.g.: Vendor ABC
Payment By	ga.DESCRIPTION	String	E.g.: Bank XYZ
Currency	cb.CURRENCYCODE	String	E.g.: USD
Bank Charge	cb.BANKCHARGE	Number	E.g.: 10.00
Cheque No	cb.CHEQUENUMBER	String	E.g.: 123456
Project	cb.PROJECT	String	E.g.: Project A
From Doc	cb.FROMDOC	String	E.g.: Doc123
Agent	cb.AGENT	String	E.g.: Agent X
Area	cb.AREA	String	E.g.: Area 51
Cancelled	cb.CANCELLED	Boolean	E.g.: true
Local Net Total	cb.LOCALDOCAMT	Number	E.g.: 1000.00
Net Total	cb.DOCAMT	Number	E.g.: 1000.00
Seq	dtl.SEQ	Number	E.g.: 1
G/L Code	dtl.CODE	String	E.g.: GL123
GL Description	ga2.DESCRIPTION	String	E.g.: General Ledger
Description (Detail)	dtl.DESCRIPTION	String	E.g.: Payment for services
Project (Detail)	dtl.PROJECT	String	E.g.: Project B
Company Name	dtl.COMPANYNAME	String	E.g.: Company XYZ
GST No	dtl.GSTNO	String	E.g.: GST123456
Inv No	dtl.GST_DOCNO	String	E.g.: INV12345
Amount	dtl.AMOUNT	Number	E.g.: 500.00
Tax	dtl.TAX	Number	E.g.: 50.00
Tax Rate	dtl.TAXRATE	String	E.g.: 10%
Tax Inclusive	dtl.TAXINCLUSIVE	Boolean	E.g.: true
Tax Amt	dtl.TAXAMT	Number	E.g.: 50.00
Sync From	'ABC COMPANY'	String	E.g.: ABC COMPANY
Last Modified	a.DOCDATETIME	Timestamp	E.g.: 2024-11-15 07:48:46.563

Payment Voucher's Sample Code

Remember to change to the correct field before proceeding to run ANY script.

SQL Script

This is a sample SQL, may not be as same as your database.

WITH AuditData AS (
    SELECT 
        SUBSTRING(a.REF FROM POSITION(', ' IN a.REF) + 2) AS AUDITKEY,
        a.REF,
        MAX(a.DOCDATETIME) AS DOCDATETIME
    FROM AUDIT a
    WHERE a.REF LIKE 'GL_CB, %'
    GROUP BY a.REF
)
SELECT FIRST 3
    cb.DOCDATE docDate,
    cb.DOCNO voucherNo,
    cb.DESCRIPTION paymentTo,
    ga.DESCRIPTION paymentBy,
    cb.CURRENCYCODE currency,
    cb.BANKCHARGE bankCharge,
    cb.CHEQUENUMBER chequeNo,
    cb.PROJECT project,
    cb.FROMDOC fromDoc,
    cb.AGENT agent,
    cb.AREA area, 
    cb.CANCELLED cancelled,
    cb.LOCALDOCAMT localNetTotal,
    cb.DOCAMT netTotalRm,
    dtl.SEQ seq,
    dtl.CODE glCode,
    ga2.DESCRIPTION glDescription,
    dtl.DESCRIPTION dtlDescription,
    dtl.PROJECT dtlProject,
    dtl.COMPANYNAME companyName,
    dtl.GSTNO gstNo,
    dtl.GST_DOCNO invoiceNo,
    dtl.AMOUNT amount,
    dtl.TAX tax,
    dtl.TAXRATE taxRate,
    dtl.TAXINCLUSIVE taxInclusive,
    dtl.TAXAMT taxAmount,
    '{YOUR_COMPANY}' syncFrom,
    a.DOCDATETIME lastModified
FROM AuditData a
JOIN GL_CB cb ON cb.DOCKEY = a.AUDITKEY
JOIN GL_CBDTL dtl ON cb.DOCKEY = dtl.DOCKEY
JOIN GL_ACC ga ON cb.PAYMENTMETHOD = ga.CODE
JOIN GL_ACC ga2 ON ga2.CODE = dtl.CODE
WHERE cb.DOCTYPE = 'PV' AND a.DOCDATETIME > TIMESTAMP '{LAST_MODIFIED_DATE_TIME}';

jobs.json

Logic Script