Customer Payment

Customer Payment's Database

UI Name	Database	Type	Remarks
Date	pm.DOCDATE	Date	E.g.: 2024-11-15
O/R No	pm.DOCNO	String	E.g.: OR12345
Agent	pm.AGENT	String	E.g.: AGT001
Area	pm.AREA	String	E.g.: AREA01
Paid Amount	pm.DOCAMT	Number	E.g.: 1500.00
Customer Code	pm.CODE	String	E.g.: CUST001
Project	pm.PROJECT	String	E.g.: PROJ001
Cancelled	pm.CANCELLED	Boolean	E.g.: true
Non-Refundable	pm.NONREFUNDABLE	Boolean	E.g.: true
Paid By	ac.COMPANYNAME	String	E.g.: Company Name
Received In	ba.DESCRIPTION	String	E.g.: Bank Account
Bank Charge	pm.BANKCHARGE	Number	E.g.: 50.00
Cheque No	pm.CHEQUENUMBER	String	E.g.: CHQ123456
Description	pm.DESCRIPTION	String	E.g.: Payment for invoice
Type	ko.TODOCTYPE	String	E.g.: Invoice
Date (Details)	iv.DOCDATE	Date	E.g.: 2024-11-16
Doc No	iv.DOCNO	String	E.g.: DOC12345
Description (Details)	iv.DESCRIPTION	String	E.g.: Payment applied to invoice
Amount	iv.DOCAMT	Number	E.g.: 1500.00
Pay	ko.KOAMT	Number	E.g.: 1500.00
Sync From	'ABC COMPANY'	String	E.g.: ABC COMPANY
Last Modified	a.DOCDATETIME	Timestamp	E.g.: 2024-11-15 07:48:46.563

Customer Payment'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 'AR_PM, %'
    GROUP BY a.REF
)
SELECT FIRST 3
    pm.DOCDATE docDate,
    pm.DOCNO orNo,
    pm.AGENT agentCode,
    pm.AREA areaCode,
    pm.DOCAMT paidAmount,
    pm.CODE customerCode,
    pm.PROJECT projectCode,
    pm.CANCELLED isCancelled,
    pm.NONREFUNDABLE isNonRefundable,
    ac.COMPANYNAME paidBy,
    ba.DESCRIPTION receivedIn,
    pm.BANKCHARGE bankCharge,
    pm.CHEQUENUMBER chequeNo,
    pm.DESCRIPTION pmDescription,
    ko.TODOCTYPE documentType,
    iv.DOCDATE knockOffDate,
    iv.DOCNO documentNo,
    iv.DESCRIPTION knockOffDescription,
    iv.DOCAMT amount,
    ko.KOAMT payAmount,
    '{YOUR_COMPANY}' syncFrom,
    a.DOCDATETIME lastModified
FROM AuditData a
JOIN AR_PM pm ON a.AUDITKEY = pm.DOCKEY
JOIN AR_CUSTOMER ac ON pm.CODE = ac.CODE
JOIN GL_ACC ba ON pm.PAYMENTMETHOD = ba.CODE
JOIN AR_KNOCKOFF ko ON pm.DOCKEY = ko.FROMDOCKEY AND ko.FROMDOCTYPE = 'PM'
JOIN AR_IV iv ON ko.TODOCKEY = iv.DOCKEY
WHERE a.DOCDATETIME > TIMESTAMP '{LAST_MODIFIED_DATE_TIME}'

jobs.json

Logic Script