New Math: Symix vouchers payable report

E

Electron

New Member
I've been working on a report modeled after the Symix vouchers payable report. However, there is something I don't understand about it: sometimes Symix arbitrarily values a PO line at 0.00, other times it does the arithmetic and balances. I don't see the logic driving this.

Example: These two blanket POs both have a negative received amount, but only one shows a balance that adds up. The other is 0.00 instead of -12k, which is a mystery to me. If anyone knows what the logic is, it would be much appreciated.

PO that balances:
RecNotVch: -40.000 EA
Rec Amt: 15,262.41
Vch Amt: 17,106.81
Adj Amt: 0.00
V/P Amt: -1,844.40

PO that does not balance:
RecNotVch: -800.000 EA
Rec Amt: 6,888.12
Vch Amt: 19,737.00
Adj Amt: 0.08
V/P Amt: 0.00
 
R

reyeshe

New Member
Symix A/P

Looks interesting.

Can you provide the steps you have used on how I can re-produce the scenario? starting from creating PO's.

I'll see what I can do ;)
 
E

Electron

New Member
Hi reyeshe, thx for your help. I don't create POs or do any other Symix transactions, so I have no idea how these records came to be. I just do (a lot of) programming in support of Symix, such as reports & utilities.

Here is an excerpt from my report, hopefully I removed all the excess gobbletygook. It builds a temp table (ttReport) with aged PO data.

Note this line line: IF po.type = "B" AND vfLastVchVal = 0 THEN vfBalance = 0.
It's a total hack, representing what USUALLY happens, but not always. It applies well to blanket POs, but not well to regular POs. With it, this report and the Symix report agree within about $1000, against a total amount of about 1.3 million. I can see no logical reason to set a PO value to 0 when it doesn't = 0, but it's part of the pattern seen in the Symix report. I wish we had the source code for this report, I'd be all over that thing like white on rice - lol.

PROCEDURE BuildTempTable:
HIDE MESSAGE NO-PAUSE.
MESSAGE "Compiling report...".
DEF VAR vfBalance AS DECI NO-UNDO.
FOR EACH vendor NO-LOCK WHERE vcVendNum = "" OR vendor.vend-num = vcVendNum:
FIND vendaddr NO-LOCK WHERE vendaddr.vend-num = vendor.vend-num.
HIDE MESSAGE NO-PAUSE.
MESSAGE vendor.vend-num vendaddr.name.
FOR EACH po NO-LOCK WHERE po.vend-num = vendor.vend-num AND po.stat <> "C" AND (vcPoType = "A" OR vcPoType = po.type):
IF vcVendNum <> "" THEN DO:
HIDE MESSAGE NO-PAUSE.
MESSAGE "PO:" po.po-num.
END.
FOR EACH poitem NO-LOCK OF po WHERE poitem.stat <> "C":
IF poitem.qty-received - poitem.qty-voucher <> 0 THEN DO:
vfBalance = 0.
FOR EACH po-vch NO-LOCK WHERE po-vch.po-num = poitem.po-num AND po-vch.po-line = poitem.po-line AND po-vch.po-release = poitem.po-release:
IF po-vch.type = "R" THEN vfBalance = vfBalance + po-vch.item-cost * po-vch.qty-received.
ELSE IF po-vch.type = "W" THEN vfBalance = vfBalance - po-vch.item-cost * po-vch.qty-returned.
ELSE IF po-vch.type = "V" THEN vfBalance = vfBalance - po-vch.item-cost * po-vch.qty-vouchered.
ELSE IF po-vch.type = "A" THEN vfBalance = vfBalance + po-vch.item-cost * po-vch.qty-vouchered.
ELSE IF po-vch.type = "F" THEN vfBalance = vfBalance + po-vch.item-cost.
END.
IF po.type = "B" AND vfLastVchVal = 0 THEN vfBalance = 0.
CREATE ttReport.
ASSIGN
ttReport.VendNum = vendor.vend-num
ttReport.VendName = vendaddr.name
ttReport.PoNum = poitem.po-num
ttReport.PoLine = poitem.po-line
ttReport.PoRelease = poitem.po-release
ttReport.PoType = po.type
ttReport.QtyRecvd = poitem.qty-received
ttReport.QtyVouchered = poitem.qty-voucher
ttReport.QtyRecNotVch = poitem.qty-received - poitem.qty-voucher
ttReport.RecDate = poitem.rcvd-date
ttReport.UnVouchTot = vfBalance.
IF poitem.rcvd-date < (TODAY - 180) THEN ttReport.Aged180 = vfBalance.
ELSE IF poitem.rcvd-date < (TODAY - 120) THEN ttReport.Aged120 = vfBalance.
ELSE IF poitem.rcvd-date < (TODAY - 60) THEN ttReport.Aged60 = vfBalance.
ELSE IF poitem.rcvd-date < (TODAY - 30) THEN ttReport.Aged30 = vfBalance.
ELSE IF poitem.rcvd-date < (TODAY - 15) THEN ttReport.Aged15 = vfBalance.
ELSE ttReport.Aged0 = ttReport.UnVouchTot.
END.
END.
END.
END.
HIDE MESSAGE NO-PAUSE.
END.

Thx again for any light you can shed on this! -E.
 
R

reyeshe

New Member
New Math.

While I was browsing at your code, I have noticed that could this be somewhat similar to the report program po/po15-r.p? This program is also known as the Vouchers payable report.

The Vouchers Payable Report allows you to show purchase orders received but not yet vouchered. The report lists the To Be Vouchered purchase orders and the purchase orders with cost variances.

To access the report you can find it in

Vendor > PO Reports > Voucher and Vendor Reports > Vouchers Payable

Thanks. I hope that this could shed some light in your work.
 
E

Electron

New Member
That's the exact report, reyeshe. There's something in the report code that values POs at 0, even though they don't add up to 0 (as far as I can tell).

If you (or anyone) has this report source code, or can crack this mystery, it would be very much appreciated. e@smiths-manchesterct.com Thx!
 
E

Electron

New Member
Mystery solved

Here's the deal: when looking at the po-vch records, you have to keep track of the qty received & qty vouchered. When they are equal, set the V/P amount (vfBalance) to 0. Ex:


vfBalance = 0.
vfQty = 0.

FOR EACH po-vch NO-LOCK
WHERE po-vch.po-num = poitem.po-num
AND po-vch.po-line = poitem.po-line
AND po-vch.po-release = poitem.po-release:

IF po-vch.type = "R" THEN ASSIGN
vfBalance = vfBalance + po-vch.item-cost * po-vch.qty-received
vfQty = vfQty + po-vch.qty-received.

ELSE IF po-vch.type = "W" THEN ASSIGN
vfBalance = vfBalance - po-vch.item-cost * po-vch.qty-returned
vfQty = vfQty - po-vch.qty-returned.

ELSE IF po-vch.type = "V" THEN ASSIGN
vfBalance = vfBalance - po-vch.item-cost * po-vch.qty-vouchered
vfQty = vfQty - po-vch.qty-vouchered.

ELSE IF po-vch.type = "A" THEN ASSIGN
vfBalance = vfBalance + po-vch.item-cost * po-vch.qty-vouchered
vfQty = vfQty + po-vch.qty-vouchered.

ELSE IF po-vch.type = "F" THEN ASSIGN
vfBalance = vfBalance + po-vch.item-cost.

IF vfQty = 0 THEN vfBalance = 0.

END.


I'm not an accountant, but this makes sense. For example, if you received 100 items at $20 each, and vouchered 100 at $15 each, the amount in V/P should be 0.00, because the vouchered value is the one your financial folks have reviewed & approved.

If you then receive 50 items at $0.00 each (I don't know why that would happen, but it sometimes does) the qty received & qty vouchered wouldn't match, and the PO would appear on the V/P report. The recieved value would be 2000, the vouchered value 1500, the amount in V/P would be 0.00.

Mystery solved.
 
R

reyeshe

New Member
Good Work Sir Ed!
Until Next time ;) Sayonarra

Electron said:
Here's the deal: when looking at the po-vch records, you have to keep track of the qty received & qty vouchered. When they are equal, set the V/P amount (vfBalance) to 0. Ex:


vfBalance = 0.
vfQty = 0.

FOR EACH po-vch NO-LOCK
WHERE po-vch.po-num = poitem.po-num
AND po-vch.po-line = poitem.po-line
AND po-vch.po-release = poitem.po-release:

IF po-vch.type = "R" THEN ASSIGN
vfBalance = vfBalance + po-vch.item-cost * po-vch.qty-received
vfQty = vfQty + po-vch.qty-received.

ELSE IF po-vch.type = "W" THEN ASSIGN
vfBalance = vfBalance - po-vch.item-cost * po-vch.qty-returned
vfQty = vfQty - po-vch.qty-returned.

ELSE IF po-vch.type = "V" THEN ASSIGN
vfBalance = vfBalance - po-vch.item-cost * po-vch.qty-vouchered
vfQty = vfQty - po-vch.qty-vouchered.

ELSE IF po-vch.type = "A" THEN ASSIGN
vfBalance = vfBalance + po-vch.item-cost * po-vch.qty-vouchered
vfQty = vfQty + po-vch.qty-vouchered.

ELSE IF po-vch.type = "F" THEN ASSIGN
vfBalance = vfBalance + po-vch.item-cost.

IF vfQty = 0 THEN vfBalance = 0.

END.


I'm not an accountant, but this makes sense. For example, if you received 100 items at $20 each, and vouchered 100 at $15 each, the amount in V/P should be 0.00, because the vouchered value is the one your financial folks have reviewed & approved.

If you then receive 50 items at $0.00 each (I don't know why that would happen, but it sometimes does) the qty received & qty vouchered wouldn't match, and the PO would appear on the V/P report. The recieved value would be 2000, the vouchered value 1500, the amount in V/P would be 0.00.

Mystery solved.
Click to expand...
 
You must log in or register to reply here.
Top