Home » SQL & PL/SQL » SQL & PL/SQL » Disperse credit notes between groups of invoices (Oracle12c Release 12.1.0.2.0 )
Disperse credit notes between groups of invoices [message #684067] Wed, 31 March 2021 07:18 Go to next message
Heavyd
Messages: 7
Registered: February 2021
Junior Member
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production


drop table invoices;

create table invoices
( invoiceid  number
, customerid number
, type       varchar2(1)
, amount     number
);

insert into invoices(invoiceid, customerid, type, amount)
values(1, 1, 'I', 100);

insert into invoices(invoiceid, customerid, type, amount)
values(2, 1, 'I', 25);

insert into invoices(invoiceid, customerid, type, amount)
values(3, 1, 'I', 15);

insert into invoices(invoiceid, customerid, type, amount)
values(4, 1, 'I', 30);

insert into invoices(invoiceid, customerid, type, amount)
values(5, 1, 'C', -20);

insert into invoices(invoiceid, customerid, type, amount)
values(6, 1, 'C', -30);

insert into invoices(invoiceid, customerid, type, amount)
values(7, 1, 'C', -65);

insert into invoices(invoiceid, customerid, type, amount)
values(8, 2, 'I', 35);

insert into invoices(invoiceid, customerid, type, amount)
values(9, 2, 'I', 50);

insert into invoices(invoiceid, customerid, type, amount)
values(10, 3, 'I', 20);

insert into invoices(invoiceid, customerid, type, amount)
values(11, 3, 'C', -30);

insert into invoices(invoiceid, customerid, type, amount)
values(12, 4, 'C', -50);


select * from invoices

I'm looking for an algorithm to disperse credit notes optimally between invoices.
So I want to group invoices and credit notes by customer in groups/buckets of 4. And the sum of each group must be > 0.
The group of 4 is just to make testing easier, in reality this will be greater. I think it will be 20.
An Invoice amount is positive, a Credit note amount is negative.
A group must contain invoices and credit notes (if there are any credit notes) and the balance must be > 0. Thus a group can't contain only credit notes.
My guess is to start with the largest amounts first for invoices and credit notes.


Desired result:
Transaction		invoiceid	customerid		type		amount
A			1			1		I		100
A			4			1		I		30
A			6			1		C		-65
A			7			1		C		-30
==> balance is 35 (this is just info, not needed in result set)

B			2			1		I		25
B			3			1		I		15
B			5			1		C		-20
==> balance is 20

C			8			2		I		35
C			9			2		I		50
==> balance is 85

D			10			3		I		20
==> balance is 20

Credit note 11 with an amount of -30 can't be used because 20-30 < 0

Credit note 12 with an amount of -50 can't be used because the customer has no invoices.

I hope it's clear what the algorithm must do.
In short, total of invoices must stay > total of credit notes in each group of 4 per customer

Is an elegant sql solution possible for this or will this be heavy pl/sql programming? Help is much appreciated Smile
Disperse credit notes between groups of invoices [message #684068 is a reply to message #684067] Wed, 31 March 2021 07:19 Go to previous message
Heavyd
Messages: 7
Registered: February 2021
Junior Member
Double post.

[Updated on: Thu, 01 April 2021 06:05]

Report message to a moderator

Previous Topic: query display like aging
Next Topic: filtering for records based on select criteria
Goto Forum:
  


Current Time: Sat May 08 21:09:30 CDT 2021