Discount on 30 Days 2

[TariqMehmod]

Dear Experts

I am working on 30 Days Trade Credit and refer to these links

https://blog.apruve.com/the-difference-between-net-30-and-due-in-30-days

https://crushthecpaexam.com/accounting-glossary/2-10-n-30/

In short:

Net 30 refers to the trade credit offered to a customer for the sale of goods or services.
Net 30 means that if the amount due is paid within 30 days, the customer will enjoy a 5% discount.

I have this data

[pre]

* Invoice Table
CREATE CURSOR invoice(no n(4),date d(8),amount n(5),due d(8),discount n(5))
INSERT INTO invoice values(1,CTOD('01/04/2019'),1500,CTOD('01/05/2019'),0)
INSERT INTO invoice values(1,CTOD('15/04/2019'),2500,CTOD('15/05/2019'),0)
INSERT INTO invoice values(1,CTOD('28/04/2019'),1800,CTOD('28/05/2019'),0)

* recovery Table
CREATE CURSOR recovery(no n(4),date d(8),amount n(5))
INSERT INTO recovery VALUES (1, CTOD('03/04/2019'), 300)
INSERT INTO recovery VALUES (2, CTOD('05/04/2019'), 800)
INSERT INTO recovery VALUES (3, CTOD('30/04/2019'), 400)
INSERT INTO recovery VALUES (4, CTOD('01/05/2019'), 400)
INSERT INTO recovery VALUES (5, CTOD('08/05/2019'), 300)
INSERT INTO recovery VALUES (6, CTOD('10/05/2019'), 500)
INSERT INTO recovery VALUES (7, CTOD('12/05/2019'), 100)
INSERT INTO recovery VALUES (8, CTOD('15/05/2019'), 600)
INSERT INTO recovery VALUES (9, CTOD('17/05/2019'), 800)
INSERT INTO recovery VALUES (10,CTOD('18/05/2019'), 100)
INSERT INTO recovery VALUES (11,CTOD('19/05/2019'), 300)
INSERT INTO recovery VALUES (12,CTOD('22/05/2019'), 400)
[/pre]

More Explanation:

Conclusion:
If an Invoice gets recovery within due date then
I want to fill discount column in invoice table like shown in above image

Please help

 

[GriffMG]

Do you match your individual receipts to specific invoices, from your example it would appear not.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[TariqMehmod]

Sir the references of sale invoice number and recovery number has no connection.

The main object is to sum recovery within due date.
Suppose Topmost Invoice 1 must search recovery payments within due date, if found then allot 5% discount.

Date of First Invoice = 01/04/2019
amount = 1500
Due date = 01/05/2019

it must check recovery till 01/05/2019
it is 300+800+400=1500

So this invoice will get discount as

1500*5/100=75

and so on.

 

[GriffMG]

You would need a modification to your data structure I think, adding two fields to the invoices table : Recovered n(5) and LastRec d(8).
On your recovery table you could add a recovered n(5) field too - so you can process only those records that have not yet been processed.

Then scan your recovery table, processing only those records that have not been fully recovered yet, looking for invoices that are not yet fully recovered and
allocate (essentially) that part of the receipt which will fit (is less than or equal to the invoices unrecovered total) and sum that into the recovered field,
replacing the invoices.LastRec date with the date of the recovery. Also replace the Recovered field in the recovery table with the amount you added to the invoice
recovery field.

If at that point the invoices.recovered amount is equal to the invoice amount and the LastRec date is less than (or equal perhaps) to the due date, you could calculate
the discount for that invoice record.

If the recovery record still has any outstanding value (amount-recovered > 0) continue to the next available invoice record and reprocess as above.
If it has no outstanding value (amount-recovered = 0) move to the next recovery record and process as above.

Does that help?

The LastRec field in the invoices table is not essential for processing as you should have that to hand when running through the two tables, but it would probably help you with debugging later.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[TariqMehmod]

Sir this part is out my thiking

[pre]If the recovery record still has any outstanding value (amount-recovered > 0) continue to the next available invoice record and reprocess as above.
If it has no outstanding value (amount-recovered = 0) move to the next recovery record and process as above.
[/pre]

can you please provide me some codes?

Thanks

 

[Koen Piller]

Tarig,

you must correct your data.
There is no connection between your tables invoice and recovery.

Koen

 

[GriffMG]

This is perhaps not quite what you seem to be asking, because I think your example perhaps deallocates 'failed' recoveries and then needs them reprocessed.
Whereas this code only processes them once, and in the order they are found... but it makes sense to me.

PRIVATE m.RECOVERYAMT

CLOSE ALL
CLEAR

m.RECOVERYAMT = 0
CREATE CURSOR INVOICE(NO N(4),DATE D(8),AMOUNT N(5),DUE D(8),DISCOUNT N(5), RECOVERED N(5), LASTREC D(8))
INSERT INTO INVOICE VALUES(1,CTOD('01/04/2019'),1500,CTOD('01/05/2019'),0,0,CTOD("//"))
INSERT INTO INVOICE VALUES(2,CTOD('15/04/2019'),2500,CTOD('15/05/2019'),0,0,CTOD("//"))
INSERT INTO INVOICE VALUES(3,CTOD('28/04/2019'),1800,CTOD('28/05/2019'),0,0,CTOD("//"))
* recovery Table
CREATE CURSOR RECOVERY(NO N(4),DATE D(8),AMOUNT N(5), RECOVERED N(5))
INSERT INTO RECOVERY VALUES (1,	CTOD('03/04/2019'),	300,0)
INSERT INTO RECOVERY VALUES (2,	CTOD('05/04/2019'),	800,0)
INSERT INTO RECOVERY VALUES (3,	CTOD('30/04/2019'),	400,0)
INSERT INTO RECOVERY VALUES (4,	CTOD('01/05/2019'),	400,0)
INSERT INTO RECOVERY VALUES (5,	CTOD('08/05/2019'),	300,0)
INSERT INTO RECOVERY VALUES (6,	CTOD('10/05/2019'),	500,0)
INSERT INTO RECOVERY VALUES (7,	CTOD('12/05/2019'),	100,0)
INSERT INTO RECOVERY VALUES (8,	CTOD('15/05/2019'),	600,0)
INSERT INTO RECOVERY VALUES (9,	CTOD('17/05/2019'),	800,0)
INSERT INTO RECOVERY VALUES (10,CTOD('18/05/2019'),	100,0)
INSERT INTO RECOVERY VALUES (11,CTOD('19/05/2019'),	300,0)
INSERT INTO RECOVERY VALUES (12,CTOD('22/05/2019'),	400,0)


SELECT RECOVERY
GO TOP

DO WHILE .NOT. EOF()
	** only process recoveries not already processed
	IF RECOVERY.RECOVERED < RECOVERY.AMOUNT
		SELECT INVOICE
		GO TOP
		DO WHILE .NOT. EOF()
			IF INVOICE.RECOVERED < INVOICE.AMOUNT
				IF RECOVERY.AMOUNT < (INVOICE.AMOUNT - INVOICE.RECOVERED)
					m.RECOVERYAMT = RECOVERY.AMOUNT
				ELSE
					m.RECOVERYAMT = (INVOICE.AMOUNT - INVOICE.RECOVERED)
				ENDIF
				REPLACE INVOICE.RECOVERED WITH INVOICE.RECOVERED + m.RECOVERYAMT
				REPLACE INVOICE.LASTREC WITH RECOVERY.DATE
				SELECT RECOVERY
				REPLACE RECOVERY.RECOVERED WITH RECOVERY.RECOVERED + M.RECOVERYAMT
				SELECT INVOICE
				? "!"
			ENDIF
			IF INVOICE.RECOVERED = INVOICE.AMOUNT
				IF INVOICE.LASTREC <= INVOICE.DUE
					REPLACE INVOICE.DISCOUNT WITH INVOICE.AMOUNT *.05
				ENDIF
			ENDIF
			IF RECOVERY.RECOVERED = RECOVERY.AMOUNT
				SELECT INVOICE
				GO BOTTOM
			ENDIF
			SKIP
		ENDDO
	ENDIF
	SELECT RECOVERY
	SKIP
ENDDO

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[TariqMehmod]

Thanks Thanks Thanks

Sir you have provided about 80% solution.

But I need some more modification like this

[pre]CREATE CURSOR RECOVERY(NO N(4),DATE D(8),AMOUNT N(5), RECOVERED N(5))
INSERT INTO RECOVERY VALUES (1, CTOD('03/04/2019'), 300,0)
INSERT INTO RECOVERY VALUES (2, CTOD('05/04/2019'), 800,0)
INSERT INTO RECOVERY VALUES (3, CTOD('30/04/2019'), 400,0)
* the above data belongs to topmost invoice of date 01/04/2019 amounting 1500


INSERT INTO RECOVERY VALUES (4, CTOD('01/05/2019'), 400,0)
INSERT INTO RECOVERY VALUES (5, CTOD('08/05/2019'), 300,0)
INSERT INTO RECOVERY VALUES (6, CTOD('10/05/2019'), 500,0)
INSERT INTO RECOVERY VALUES (7, CTOD('12/05/2019'), 100,0)
INSERT INTO RECOVERY VALUES (8, CTOD('15/05/2019'), 600,0)
&& the above payment is against invoice 3 (28/04/2019) amounting 2500

INSERT INTO RECOVERY VALUES (9, CTOD('17/05/2019'), 800,0)
&& 2700 for 2nd invoice (15/04/2019) but not within due date
&& in this case control must go to record number 4
&& and calculate for invoice 3 (28/04/2019)


INSERT INTO RECOVERY VALUES (10,CTOD('18/05/2019'), 100,0)
INSERT INTO RECOVERY VALUES (11,CTOD('19/05/2019'), 300,0)
INSERT INTO RECOVERY VALUES (12,CTOD('22/05/2019'), 400,0)

&& it must calculate all entries in recovery dbf.[/pre]

After that the result will be look like this

Please

 

[Olaf Doschke]

Please simply learn what related data and relationships and foreign keys are. Your recovery records need a field to refer to which invoice each record belongs to. This is simply incomplete data.

Just because you tell us in this case the first 3 recovery records are about invoice1, the next 6 are about invoice 2 and the last 3 about invoice 3, that this is always the case.

In a system of a shop that has many customers, many invoices and likely even rates paid for an older invoice after a new purchase was made, you can't even just make the assignment based on payment after invoice date or based on sum of payments accumulating the invoice amount.

You can't have this computed from your data, you can't force a relationship where it's missing.

You're still lacking database design principles.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

I think that is doable, but I can't help this evening.

You would need to 'wind back' the 'transaction' and move forward on that invoice, then return to the previous invoice... ad nauseum.
Not an efficient process.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[TariqMehmod]

Sir OLAF,

Recovery records are not linking with any invoice.

All is based on due date. Where first invoice completes its payments within due date then it gets discount.

Then control searchs payments in recovery for next invoice. If it is within due date then apply discount othewise zero.

Regards

 

[TariqMehmod]

Dear GiffMG

You are right. You have really understood what I want.

Thanks.

 

[Olaf Doschke]

Tariq, you can't handle it this way, when you have multiple open invoices of multiple customers there have to be references what payment is for what.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TariqMehmod]

Sir Olaf,

I am talking about only 1 customer not multiple.

I shsll msnage others if I get suceess with 1.

Regards

 

[GriffMG]

scalabality

His recoverables are not rigidly linked to invoices and he seems to want to not 'disadvantage' his customers

If the OP set out to design a system, he might link receipts to invoices, but he lives in the real world, where .
he gets payments and no remittance advices.

I live quite near there, 85% of invoices match remittances, but 15% are pure guesswork

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Griff, I thought of payment in rates, they would still be for a certain invoice.

If this is really about the type of receipts on a business account not making use of a reference given in the invoice and thus not assignable, I'd solve that with a customer account (with balance, debit credit transactions) and deduce records referring to the invoices in chronological order by assigning the incoming payments to the invoices yourself, in that act you feed another table and you will come to the point where one payment finishes one invoice and a rest amount can be assigned to the next open invoice. So you're designing your data with the assignments and create those missing references. You then can even assign payments for the advantage of your customers to the second invoice before the first one is finished, if that's already past the due date for rebates and truly act in favor of them, even though they pay only partially.

But the thought remains: If the data is unfortunately structured you restructure it as you need it. So you split up the payments so they work out. You don't have to "solve the puzzle with the given pieces", the amounts can be split as you need them. So you create new records in a new table where a single assignment doesn't work as the amount has to be split. Well, then split it, make two records with partial amounts. You're still the designer of your data world and not a victim of reality. You don't wrap your brain around an unfortunate or impossible situation by sticking to the data as is, you solve it. Is it really that hard to be creative?

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[TariqMehmod]

These codes work fine for me.
Thanks to all participants

[pre]* Invoice Table
SET DATE BRITISH

CREATE CURSOR invoice(no n(4),date d(8),amount n(5),due d(8),discount n(5),pdate d, PAID I)
INSERT INTO invoice values(1,CTOD('01/04/2019'),1500,CTOD('01/05/2019'),0, CTOD('//'),0)
INSERT INTO invoice values(1,CTOD('15/04/2019'),2500,CTOD('15/05/2019'),0, CTOD('//'),0)
INSERT INTO invoice values(1,CTOD('28/04/2019'),1800,CTOD('28/05/2019'),0, CTOD('//'),0)

* recovery Table
CREATE CURSOR recovery(no n(4),date d(8),amount n(5), BALANCE I)
INSERT INTO recovery VALUES (1, CTOD('03/04/2019'), 300,0)
INSERT INTO recovery VALUES (2, CTOD('05/04/2019'), 800,0)
INSERT INTO recovery VALUES (3, CTOD('30/04/2019'), 400,0)
INSERT INTO recovery VALUES (4, CTOD('01/05/2019'), 400,0)
INSERT INTO recovery VALUES (5, CTOD('08/05/2019'), 300,0)
INSERT INTO recovery VALUES (6, CTOD('10/05/2019'), 500,0)
INSERT INTO recovery VALUES (7, CTOD('12/05/2019'), 100,0)
INSERT INTO recovery VALUES (8, CTOD('15/05/2019'), 600,0)
INSERT INTO recovery VALUES (9, CTOD('17/05/2019'), 800,0)
INSERT INTO recovery VALUES (10,CTOD('18/05/2019'), 100,0)
INSERT INTO recovery VALUES (11,CTOD('19/05/2019'), 300,0)
INSERT INTO recovery VALUES (12,CTOD('22/05/2019'), 400,0)

REPLACE ALL BALANCE WITH AMOUNT


SELECT INVOICE

SCAN
lnAmt = INVOICE.AMOUNT

SELECT RECOVERY
SCAN FOR BALANCE > 0

DO CASE

CASE lnAmt > RECOVERY.BALANCE
lnAmt = lnAmt - (RECOVERY.BALANCE)

REPLACE INVOICE.PAID WITH INVOICE.PAID +RECOVERY.BALANCE
replace RECOVERY.BALANCE WITH 0
REPLACE INVOICE.PDATE WITH RECOVERY.DATE


CASE lnAmt =< RECOVERY.BALANCE

REPLACE INVOICE.PAID WITH INVOICE.PAID + lnAmt
REPLACE RECOVERY.BALANCE WITH RECOVERY.BALANCE - lnAmt
REPLACE INVOICE.PDATE WITH RECOVERY.DATE
lnAmt = 0
ENDCASE

IF lnAmt = 0
EXIT
ENDIF

ENDSCAN

ENDSCAN

SELECT INVOICE
REPLACE discount WITH amount*5/100 FOR pdate =<DUE AND amount = paid
BROWSE[/pre]

 

[Olaf Doschke]

OK, at least you got the idea to split the amount of a recovery record.

Just nite, I guess it's okay to change invoice.paid as you go, changing recovery records while processing them means they're only temp data, otherwise you're destroying original data. It's up to you if that's okay with you, but if your recovery table isn't temp cursor but a DBF you have a problem when this DBF becomes corrupt after invoice.paid already is rasied and before recovery.balance is lowered.

Bye, Olaf.

PS: your REPLACE of the discount will not forward the discount to the customer, I don't see how that has any effect. That discount should reduce the invoice amount, so when a sufficient payment was made before duedate the invoice is already paid if the recovery amount is invoice.amount minus the possible discount. Or in other words, the amount to reach for full payment depends on the pay date, that's missing.

I assume you handle this in a next step, where you award the customer with all discounts, but then you first need to pay the full amount to get something back and that's not how shop systems would work.

The way you do it, a customer would not get a discount of $5 on a $100 invoice when paying $95, you only award the discount of $5 after the full $100 are paid. You want to zero the rest amount due when 95% of the nominal amount are reached before the due date and only if the sum of payments doesn't reach 95% at due date the full nominal amount is needed to close the invoice.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

Hi

Very pleased you got sorted.

Glad I could help a little.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​