Recurring

From eStream Software
Revision as of 01:18, 29 January 2019 by Twfaung (talk | contribs) (→‎Quotation)

Introduction

This Customisation is for simple calculation for the management property which normally monthly charge Maintenance, Sinking Fund & etc.
QR Recurring.jpg

Sample Database

History New/Updates/Changes

Coming Soon...

  • Move Footer Count to Company Name
  • Increase Form width to 1024
  • Remove all Tax Code in Sample Data
  • Add Use Doc Tax Code option for Doc Listing
  • Allow Multi range price for Utility

04 Oct 2018

  • Fixed Amount not correct if Qty = Min Charge Amt Qty

15 Aug 2018

  • Reduce the Doc & Item Project Length at Customer Payment
  • Set to 0 if using both Rebate & Min Charge if below Min Charge amount

11 Jul 2018

  • Allow more then 1 Stock Group for SQFT in Maintain Customer
  • Add UDF_UseSQFT in QT Detail - True Qty = UDF_BuildUp(Maintain Customer)
  • Add UDF_UseARPrice in QT Detail - True UnitPrice = UDF_UPSQFT(Maintain Customer)
  • Add Last Payment Date, Amount & Outstanding Information in Invoice Detail Screen
  • Add Document & Item Project selection Posting for Customer Due

15 Feb 2018

  • Generate by SQFT by Customer
  • Add Cust Local - Payment Listing (GST 1) - Knock Off Doc No Set - Cross
  • Add Sales Customer Price History - ItemCode - Cross
  • Allow to change Header Description for DN Overdue
  • Set Default TaxRate 0% for Payment DN overdue

21 Mar 2017

  • Change Header Description for Posting DN to Late Payment Charges for

25 Jan 2017

  • Add Pre-Post DN at Customer Payment.
  • Add Sales Document Listing Recurring.

16 Sep 2016

  • Fixed access violation error if No Record Found prompt.

12 Jan 2016

  • Enable GAS Reading

25 Dec 2015

  • Add 0Sales Invoice 7 (GST 2)-Recurring-Maintenance Report
  • Add 0Sales Invoice 7 (GST 2)-Recurring-Meter Report
  • Enable GAS Reading
  • Allow Select Multi DocNo Set for Different Reading
  • Fixed bug LeadTime=0 - Will being override by Rebate when is below Min Charge (i.e. will set to 0 if less then Min Charge)
  • Fixed Cust Statement 06 Mths 1 Report Not shown Address 1 & take too much space at footer

26 Sep 2015

  • Remove 5 Cent rounding Script & use build in 5 Cent rounding
  • Fixed Customise Statement report not follow company profile information
  • Set Customer Payment UDF_PostDN Default Value = False

12 Aug 2015

  • Fixed Posting Overdue DN should excluding the GST amount from the calculation

Know Bugs

Todo

  • OVERDUE Qty := Qty + LeadTime for AR Payment

Modules Require

  • DIY Field
  • DIY Script
  • Multiple Pricing (Price Tag)
  • Multiple Document Number Set (Optional)

Settings

Maintain User

Menu: Tools | Maintain User...

Untick the Access Right for Prompt Replace Unit Price Dialog under the Group : Sales

Maintain Customer

Menu: Customer | Maintain Customer...

  • Below fields MUST had Value (minimum is 0 not empty or Null)
- UDF_WaterReading
- UDF_ElectricReading
- UDF_GasReading
- UDF_IWKReading
- Price Tag
  • Minimum UDF Fields Required
Name Data Type Size Sub Size Caption Required Default Value Display Format
WaterReading Float 10 4 UDF_WaterReading False 0 #,0;-#,0
ElectricReading Float 10 4 UDF_ElectricReading False 0 #,0;-#,0
GasReading Float 10 4 UDF_GasReading False 0 #,0;-#,0
IWKReading Float 10 4 UDF_IWKReading False 0 #,0;-#,0
UseARPrice Boolean Use Maintain Customer Price False False
UPSQFT Float 10 4 Unit Price/SQFT False 0 #,0.00;-#,0.00
BuildUp Float 10 4 Build Up (sf) False 0 #,0.00;-#,0.00
Template.Tips-01.jpg
UseARPrice & UPSQFT is use for Recurring by square feet (SQFT)
UseARPrice
True = the Unit Price is by Customer
False = the Unit Price is from Maintain Item Ref Price
The Item Code List is filter by Stock Group Code LIKE MAINTENANCE%

Quotation

Menu: Sales | Quotation...

  • DocNo Format := CompanyCategoryCode;Type
 eg
 B-Shop;Main  => For Maintenance
 B-Shop;Water => For Water Billing
 B-Shop;Park  => For Car Park
  • For Meter Reading (eg Water, Electric & etc), Detail can be empty cause system will auto APPEND the itemcode
Template.Tips-01.jpg Note:

- Master Description if using

- 1.Generate Maintenance it will use the Quotation Master Description.
- 2.Generate Meter Reading (eg Water, Electric & etc), it will shown how much the meter usage
[Master Description (MMM YYYY) - CurrentReading -PreviousReading=Usage@UnitPrice].
- 2.Generate Meter Reading with "Include Grid Detail Info." Option tick it will use the Quotation Master Description.

- UDF_UseSQFT

- True := Qty = UDF_BuildUp from Maintain Customer
- False := Qty = Quotation Qty

- UDF_UseARPrice

- True := UnitPrice = UDF_UPSQFT from Maintain Customer
- False := UnitPrice = Quotation UnitPrice
Recurring-13.jpg
  • List Field to be copy from Quotation to Invoice
Master Detail
  • DocNoEx
  • DocDate
  • Terms
  • Agent
  • Description
  • DocRef1
  • DocRef2
  • DocRef3
  • DocRef4
  • Project
  • Validity
  • DeliveryTerm
  • CC
  • Number
  • ItemCode
  • Description
  • Description2
  • Description3
  • Remark1
  • Remark2
  • Location
  • Project
  • DeliveryDate
  • Qty
  • UOM
  • UnitPrice
  • Tax
  • Disc
  • Minimum UDF Fields Required (Header Fields)
Name Data Type Caption Required Default Value
DocNoSet Integer UDF_DocNoSet False 0
Type Integer UDF_Type False 0
IsPrcRange Boolean UDF_IsPrcRange False False


  • Minimum UDF Fields Required (Item Fields)
Name Data Type Caption Required Default Value Display Format
From Integer UDF_From False 0 #,0;-#,0
To Integer UDF_To False 0 #,0;-#,0
UseSQFT Boolean UDF_UseSQFT False False
UseARPrice Boolean UDF_UseARPrice False False

Invoice

Menu: Sales | Invoice...

  • Minimum UDF Fields Required (Items Fields)
Name Data Type Size Sub Size Caption Required Default Value Display Format
LastReading Float 10 4 UDF_LastReading False 0 #,0;-#,0
CurrReading Float 10 4 UDF_CurrReading False 0 #,0;-#,0

Debit Note

Menu: Sales | Debit Note...

  • Minimum UDF Fields Required (Items Fields)
Name Data Type Size Caption Required Default Value Display Format
IVDocNo String 20 UDF_IVDocNo False
IVDocDate Date UDF_IVDocDate
IVDueDate Date UDF_IVDueDate
IVDocAmt Currency UDF_IVDocAmt False 0 #,0.00;-#,0.00
IVOSAmt Currency UDF_IVOSAmt False 0 #,0.00;-#,0.00

Stock Item

Menu: Stock | Maintain Stock Item...

  • Item Code to be create
  • WATER
  • ELECTRIC
  • GAS
  • IWK
  • RTN5Cents => For Rounding Mechanism
  • OVERDUE => For Overdue Charge (Just set at Ref Price & enter 0.1 for 10% pa)
  • Price Tag => For Different UnitPrice for Meter Reading (eg WATER, ELECTRIC & etc)
Use Disc column as Rebate & Min Charge => Format := Rebate+MinCharge
eg
Lead Time Type Description Input Value
0 Both Rebate 11.75 & MinCharge 5 11.75+5
1 Rebate Only Rebate 12.30 & MinCharge 0 12.30+0
2 Min Charge Only Rebate 0 & MinCharge 5 0+5
  • Lead Time => 0: Both, 1: Rebate Only, 2: Min Charge Only
  • For MinCharge make sure it had Value else set at 1
Recurring-07.jpg
Template.Tips-01.jpg How System calculate Min. Qty for Min. Charge?
  • Using above example => Min. Qty := 5 / 0.3121 = 16.0205
  • if the Qty is below or equal 16.0205 then Amount will Reset to 5

Customer Payment

Menu: Customer | Customer Payment...

  • Auto Generate Sales DN for overdue IV if UDF_PostDN (in DIY Fields) Default Value is set to True (Default is False)
  • Only Knock-off to DN will not regenerate DN again if overdue
  • Formula for Age
    Age := DocDate - Knock off Invoice Due Date
  • Formula For Qty
    if Age < DayOf(DocDate) then
      Qty := Age else
      Qty := DayOf(DocDate);
  • Formula for UnitPrice
    Unit Price := Rate/365 * Knock off amount (Excluding GST)
  • Minimum UDF Fields Required (Header Fields)
Name Data Type Size Caption Required Default Value
PostDN Boolean UDF_PostDN False False
TaxRate String 10 UDF_TaxRate False 0%

Customer Due Listing

Menu: Customer | Print Customer Due Document...

  • Auto Generate Sales Debit Note for overdue Invoice & Debit Note (can untick the option if don't wanted to be included before click Apply button)
  • It will Generate 1 Customer Code 1 Sales Debit Note
  • The rate is from Ref Price of OVERDUE itemcode (just enter 0.1 for 10% pa)
  • Formula Qty
    if Age < DaysInAMonth(edDueDate.Date) then
      Qty := Age else
      Qty := DaysInAMonth(edDueDate.Date);
  • Formula UnitPrice
    Unit Price := Rate/365 * Outstanding amount (Excluding GST)


Recurring-06.jpg
01. Set the Date to generate Overdue
02. Tick the following option
  • Invoice
  • Debit Note (if you wanted to include in calculation overdue)
  • Overdue
03. Click Apply button
04. Select & Enter the DN Information
05. Click 1. Post DN button

Sales Document Listing

Menu: Sales | Print Sales Document Listing...

  • Is a batch
- Delete Document
- Copy From 1 Document type & Paste To Same or Other Document Type

Base on the parameter selected & result shown in the grid

Recurring-01.jpg

Batch Delete Document

Below is example to delete Invoice

01. Select Invoice Listing
02. Select Range Date or any parameter you prefer
03. Click Apply button
04. Click Delete button
Template.Warning-01.jpg

Only Select follow filter

  • Date
  • Customer
  • Agent
  • Area
  • Company Category
  • Doc. Project

Batch Copy & Paste Document

Below is example to Copy from Invoice to Invoice

01. Select Invoice Listing
02. Select Range Date or any parameter you prefer
03. Click Apply button
04. In the section A (See icon A)
Field Description
Doc Date Document Date to Set
To Document Document Type To Post/Copy To (eg Invoice)
Header Desc Document Header Description (Must Had Value)
1st Item Desc
  • Description for 1st Row of Detail.
  • If Leave Empty will copy from Original Source (i.e no changes)
05. Click Post button

Steps

Maintain Customer

Menu: Customer | Maintain Customer...

Generating Maintenance - SQFT

Recurring-10.jpg
01. Click 1. Gen/B button.
Recurring-11.jpg
02. Untick any Customer you don't wanted to Generate
03. Select the Item Code.
04. Enter the Item Description.
05. Enter the Document Date.
06. Select Document Number Set to use
07. Click Post to generate
Template.Tips-01.jpg
If system prompt "Do you want to set "xxxxxx" as your default document running number set?"
Just click Yes to avoid keep prompt again
Template.Warning-01.jpg
Make sure the Build Up (sf), Use Maintain Customer Price & Unit Price/SQFT
is shown at Browse Screen before Click the Gen/B button

Quotation

Menu: Sales | Quotation...

Generating Maintenance

Recurring-02.jpg
01. Enter/Change the Description to be shown in Sales Invoice & Customer Statement
02. Enter/Change the Date & Terms & other Information in Detail
03. Select Recurring Type (i.e. Maintenance).
04. Select Document Number Set to use.
05. Click Save button.
06. Click 3. Generate Bill button.
Recurring-04.jpg
01. Untick any Customer you don't wanted to Generate
02. Click Ok button to generate

Generating Meter Reading

Recurring-03.jpg
01. Enter/Change the Description to be shown in Sales Invoice & Customer Statement
02. Enter/Change the Date & Terms & other Information in Detail
03. Select Recurring Type (i.e. Water Reading).
04. Select Document Number Set to use.
05. Click Save button.
06. Click 3. Generate Bill button.
Recurring-05.jpg
01. Untick any Customer you don't wanted to Generate
02. Enter the Current Reading for each Customer
03. Tick Include Grid Detail Info. (optional)
- if you wanted combine Generate Maintenance
- Must use the Generate Maintenance Template(Quotation)
04. Click Ok button to generate
Template.Tips-01.jpg
If system prompt "Do you want to set "xxxxxx" as your default document running number set?"
Just click Yes to avoid keep prompt again

Customer Payment

Menu: Customer | Customer Payment...

Recurring-08.jpg

Below is example for Generating Due Interest from Customer Payment

01. Click New button
02. Select Customer Code
03. Enter & Select the DN Information
04. Click 0. Show Due IV/DN... button
Recurring-09.jpg
05. May untick which don't wanted to include the Due Interest Calculation.
06. Click Post DN Button to Post/Generate Due Interest DN
07. Click Close
Template.Warning-01.jpg

Make sure the UDF_PostDN Default is Set to False else will double posting

Template.Tips-01.jpg

The Age calculate to System Working Date.
The UnitPrice is round to 4 Decimal Point.
The GST Rate field is use to Pre-Calculate the Due Amount before posting Only.
The GST rate is still base on the GST Tax code you set in Maintain Item or Tools | option or Maintain Customer.

FAQ

Where to Generate the Cust Local - Payment Listing (GST 1) - Knock Off Doc No Set - Cross report?

Menu : Customer | Print Customer Document Listing... | Customer Payment Listing

How the Column Description shown in Cust Local - Payment Listing (GST 1) - Knock Off Doc No Set - Cross report?

Menu : Tools | Maintain Document Number...

It use
- Description field as Caption
- First 2 Character Format field as Grouping (eg Format RE-IV-%.5d it will group by RE)

Where to Generate the Sales Customer Price History - ItemCode - Cross report?

Menu : Sales | Print Print Sales Price History...

Select Customer in Group/Sort By : Option

My company not using Rebate & Min. Charge. What Input Value I need to set?

Just set the Discount Value to 0+0

Why the value is always at Min. Charge even Qty is exceed the Min Charge value?

This only happen if you had use Rebate & Min Charge together (See below Example)
Discount Value 5+2 with Unit Price 0.1245 (Min Qty 16.0643)
Current Qty is 23
System will show as 2 (Min Charge) because 23*0.1245-5(rebate) = -2.14

See also

Retrieved from "https://wiki.sql.com.my/index.php?title=Recurring&oldid=9283"
Last modified
This page was last edited on 29 January 2019, at 01:18.