Okay -
Background external software will be giving me data which I have to manipulate in order to integrate into GP.
Here's some sample data -
Create Table Demo
(
transaction_code varchar (30),
payment_date date,
payment_type varchar (30),
cust_id varchar (30),
currency varchar (10),
payment_amount float,
invoices varchar (30),
external_id varchar (30),
applied_amount float,
discount_taken float
)
Insert Demo
Values
('TEST12356','2024-04-11','CREDITCARD','ACME001','USD','1500.00','INV001','YAYPAY500','1000.00','0');
Insert Demo
Values
('TEST12356','2024-04-11','CREDITCARD','ACME001','USD','1500.00','INV002','YAYPAY500','1000.00','0');
Insert Demo
Values
('TEST12356','2024-04-11','CREDITCARD','ACME001','USD','1500.00','CREDIT001','YAYPAY500','-500.00','0');
Basically - the data says this - we are settling 2 Invoices (paying off) that total 2000.00 with a credit card payment of 1500 and a credit document worth 500.
The external app will always send me data that I have to figure out how to apply the credit and settle the Invoices.
I need to take this data:
And change the line with the credit to include an Invoice Document Number and applied amount. I played around with both a Pivot and a LAG (first time trying it). The math is straight forward - for each transaction code the payment amount (1500) will add up to the sum of applied_amount. I will never have more than 3 documents to apply to - I'm kind of stuck on where to start.
