Vendor Ledger

SELECT
  `tabGL Entry`.`posting_date` AS `posting_date`,                -- 1
  `tabGL Entry`.`voucher_type` AS `voucher_type`,                -- 2
  `tabGL Entry`.`voucher_no` AS `voucher_no`,                    -- 3
  `tabGL Entry`.`account` AS `account`,                          -- 4
  `tabGL Entry`.`party` AS `party`,                              -- 5
  `tabGL Entry`.`against` AS `against`,                          -- 6
  `tabPurchase Invoice Item`.`item_name` AS `pur_item`,          -- 10
  LPAD(FORMAT(`tabPurchase Invoice Item`.`qty`, 2), 15, ' ') AS `pur_qty`,  -- 11 (right-aligned)
  FORMAT(`tabPurchase Invoice Item`.`rate`, 2) AS `pur_rate`,    -- 12
  LPAD(FORMAT(`tabGL Entry`.`debit`, 2), 15, ' ') AS `debit`,    -- 13 (right-aligned)
  LPAD(FORMAT(`tabGL Entry`.`credit`, 2), 15, ' ') AS `credit`,  -- 14 (right-aligned)
  LPAD(
    FORMAT(
      SUM(`tabGL Entry`.`debit` - `tabGL Entry`.`credit`) OVER (
        PARTITION BY `tabGL Entry`.`account`, `tabGL Entry`.`party`
        ORDER BY `tabGL Entry`.`posting_date`, `tabGL Entry`.`voucher_no`, `tabGL Entry`.`name`
      ),
      2
    ),
    15,
    ' '
  ) AS `balance`                                                 -- 15 (right-aligned)
FROM
  `tabGL Entry`
LEFT JOIN `tabPurchase Invoice Item` ON `tabGL Entry`.`voucher_no` = `tabPurchase Invoice Item`.`parent`
WHERE
  `tabGL Entry`.`posting_date` BETWEEN %(start_date)s AND %(end_date)s
  AND `tabGL Entry`.`account` LIKE %(account)s
  AND `tabGL Entry`.`company` = %(company)s
  AND `tabGL Entry`.`party_type` = %(party_type)s
  AND `tabGL Entry`.`party` = %(party)s
  AND `tabGL Entry`.`is_cancelled` = 0
ORDER BY
  `tabGL Entry`.`posting_date` ASC,
  `tabGL Entry`.`voucher_no` ASC,
  `tabGL Entry`.`name` ASC;






No.
Label
Fieldtype
Fieldname
Mandatory
Options

1
start_date
Date
start_date


2
end_date
Date
end_date


3
company
Link
company

Company

4
Account
Link
account

Account

5
party_type
Link
party_type

Party Type

6
Party
Dynamic Link
party

party_type