From f597270f9ee6f86c04085e7b2f0e70e81b382f50 Mon Sep 17 00:00:00 2001 From: blake Date: Fri, 25 Mar 2016 16:42:15 -0500 Subject: [PATCH] LP1174498 Payment by billing type breakdown Corrected a situation where account_adjustment payments were not paying the intended billing line items. The new function algorithm attempts to find a billing line item for the exact amount of the payment in the case of account_adjustment payment type. Signed-off-by: blake Signed-off-by: Jason Boyer --- Open-ILS/src/sql/Pg/080.schema.money.sql | 322 +++++++++++-------- ...money.materialized_payment_by_billing_type.sql} | 343 +++++++++++++-------- 2 files changed, 405 insertions(+), 260 deletions(-) rename Open-ILS/src/sql/Pg/upgrade/{XXXX.money.materialized_payment_by_billing_type => XXXX.money.materialized_payment_by_billing_type.sql} (58%) diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index ed7aac30f1..7ab81e96d5 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -802,8 +802,6 @@ CREATE TABLE money.materialized_payment_by_billing_type ( billing_type TEXT, payment_ou INT, billing_ou INT, --- Debug billing_leftover NUMERIC(8,2), --- Debug payment_leftover NUMERIC(8,2), CONSTRAINT x_p_b_once UNIQUE (xact,payment,billing) ); @@ -815,7 +813,6 @@ CREATE INDEX p_by_b_payment_ts_idx CREATE OR REPLACE FUNCTION money.payment_by_billing_type ( p_xact BIGINT ) RETURNS SETOF money.materialized_payment_by_billing_type AS $$ - DECLARE current_result money.materialized_payment_by_billing_type%ROWTYPE; current_payment money.payment_view%ROWTYPE; @@ -825,11 +822,16 @@ DECLARE payment_offset INT := 0; billing_offset INT := 0; billing_ou INT := 0; - payment_ou INT := 0; - fast_forward BOOLEAN := FALSE; - maintain_billing_remainder BOOLEAN := FALSE; - billing_loop INT := -1; - billing_row_count INT := 0; + payment_ou INT := 0; + fast_forward BOOLEAN := FALSE; + maintain_billing_remainder BOOLEAN := FALSE; + billing_loop INT := -1; + billing_row_count INT := 0; + current_billing_id BIGINT := 0; + billing_id_used BIGINT ARRAY; + billing_l INT := 0; + continuing_payment BOOLEAN := FALSE; + continuing_payment_last_row BOOLEAN := FALSE; BEGIN /* This algorithm is fairly straight-forward. We take a transaction id @@ -849,19 +851,19 @@ BEGIN SELECT circ_lib INTO billing_ou FROM action.circulation WHERE id = p_xact; END IF; - -- figure out how many billing rows there are - FOR current_billing IN - SELECT * + -- figure out how many billing rows there are + FOR current_billing IN + SELECT * FROM money.billing WHERE xact = p_xact -- AND NOT voided ORDER BY billing_ts - LOOP - billing_row_count = billing_row_count + 1; - END LOOP; + LOOP + billing_row_count = billing_row_count + 1; + END LOOP; -- Loop through the positive payments - FOR current_payment IN - SELECT * + FOR current_payment IN + SELECT * FROM money.payment_view WHERE xact = p_xact AND NOT voided @@ -869,10 +871,10 @@ BEGIN ORDER BY payment_ts LOOP - payment_remainder = current_payment.amount; - -- With every new payment row, we need to fast forward - -- the billing lines up to the last paid billing row - fast_forward := TRUE; + payment_remainder = current_payment.amount; + -- With every new payment row, we need to fast forward + -- the billing lines up to the last paid billing row + fast_forward := TRUE; IF current_payment.payment_type IN ( 'cash_payment', 'check_payment', 'credit_card_payment' ) THEN SELECT ws.owning_lib INTO payment_ou FROM money.bnm_desk_payment p @@ -883,7 +885,7 @@ BEGIN END IF; -- Were we looking at a billing from a previous step in the loop? - IF billing_remainder > 0.0 THEN + IF billing_remainder > 0.0 THEN current_result.xact = p_xact; current_result.payment = current_payment.id; current_result.billing = current_billing.id; @@ -893,63 +895,73 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; --- Debug current_result.billing_leftover = billing_remainder; --- Debug current_result.payment_leftover = payment_remainder; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; billing_remainder = billing_remainder - payment_remainder; payment_remainder = 0.0; payment_offset = payment_offset + 1; - -- If it is equal then we need to close up the billing line and move to the next - -- This prevents 0 amounts applied to billing lines - IF billing_remainder = payment_remainder THEN - billing_remainder = 0.0; - billing_offset = billing_offset + 1; - ELSE - maintain_billing_remainder := TRUE; - END IF; - + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); + ELSE + maintain_billing_remainder := TRUE; + END IF; + ELSE current_result.amount = billing_remainder; payment_remainder = payment_remainder - billing_remainder; billing_remainder = 0.0; - billing_offset = billing_offset + 1; + billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); + continuing_payment := TRUE; END IF; RETURN NEXT current_result; - -- Done paying the billing rows when we run out of rows to pay (out of bounds) - EXIT WHEN billing_offset = billing_row_count+1; + -- Done paying the billing rows when we run out of rows to pay (out of bounds) + EXIT WHEN array_length(billing_id_used, 1) = billing_row_count; END IF; CONTINUE WHEN payment_remainder = 0.0; -- next billing, please + billing_loop := -1; FOR current_billing IN - SELECT * + SELECT * FROM money.billing WHERE xact = p_xact - -- AND NOT voided - -- Gotta put the voided billing rows at the bottom (last) + -- Gotta put the voided billing rows at the bottom (last) ORDER BY voided,billing_ts LOOP - -- Fast forward through the rows until we get to the billing row - -- where we left off - IF fast_forward THEN - billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1 - CONTINUE WHEN billing_loop <> billing_offset; - END IF; - - -- Keep the old remainder if we were in the middle of a billing row - IF NOT maintain_billing_remainder THEN - billing_remainder = current_billing.amount; - END IF; - - maintain_billing_remainder := FALSE; - billing_loop := -1; - fast_forward := FALSE; - + billing_loop = billing_loop + 1; + + -- Skip billing rows that we have already paid + IF billing_id_used @> ARRAY[current_billing.id] THEN CONTINUE; + END IF; + + IF maintain_billing_remainder THEN + CONTINUE WHEN current_billing.id <> current_billing_id; + -- Account adjustment - we expect to pay billing rows that are identical amounts + ELSE IF current_payment.payment_type = 'account_adjustment' THEN + -- Go ahead and allow the row through when it's the last row and we still haven't found one with equal payment amount + CONTINUE WHEN ( ( current_billing.amount <> current_payment.amount ) AND ( billing_loop + 1 <> billing_row_count ) ); + END IF; + END IF; + + -- Keep the old remainder if we were in the middle of a billing row + IF NOT maintain_billing_remainder THEN + billing_remainder = current_billing.amount; + END IF; + + maintain_billing_remainder := FALSE; + fast_forward := FALSE; + current_billing_id := current_billing.id; + continuing_payment := FALSE; + current_result.xact = p_xact; current_result.payment = current_payment.id; current_result.billing = current_billing.id; @@ -959,38 +971,92 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; --- Debug current_result.billing_leftover = billing_remainder; --- Debug current_result.payment_leftover = payment_remainder; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; billing_remainder = billing_remainder - payment_remainder; - IF NOT current_billing.voided THEN - payment_remainder = 0.0; + payment_remainder = 0.0; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); END IF; - -- If it is equal then we need to close up the billing line and move to the next - -- This prevents 0 amounts applied to billing lines - IF billing_remainder = payment_remainder THEN - billing_remainder = 0.0; - billing_offset = billing_offset + 1; - END IF; ELSE current_result.amount = billing_remainder; - IF NOT current_billing.voided THEN - payment_remainder = payment_remainder - billing_remainder; + payment_remainder = payment_remainder - billing_remainder; + continuing_payment := TRUE; + IF billing_loop + 1 = billing_row_count THEN + -- We have a situation where we are on the last billing row and we are in the middle of a payment row + -- We need to start back at the beginning of the billing rows and pay + continuing_payment_last_row := TRUE; END IF; billing_remainder = 0.0; billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); END IF; - + RETURN NEXT current_result; + IF continuing_payment_last_row THEN + -- This should only occur when the account_adjustment's do not line up exactly with the billing + -- So we are going to pay some other type of billing row with this odd account_adjustment + -- And we need to stay in the current_payment row while doing so + billing_loop := -1; + FOR current_billing IN + SELECT * + FROM money.billing + WHERE xact = p_xact + ORDER BY voided,billing_ts + LOOP + billing_loop = billing_loop + 1; + -- Skip billing rows that we have already paid + IF billing_id_used @> ARRAY[current_billing.id] THEN CONTINUE; END IF; + + billing_remainder = current_billing.amount; + current_billing_id := current_billing.id; + continuing_payment := FALSE; + + current_result.xact = p_xact; + current_result.payment = current_payment.id; + current_result.billing = current_billing.id; + current_result.payment_ts = current_payment.payment_ts; + current_result.billing_ts = current_billing.billing_ts; + current_result.payment_type = current_payment.payment_type; + current_result.billing_type = current_billing.billing_type; + current_result.payment_ou = payment_ou; + current_result.billing_ou = billing_ou; + + IF billing_remainder >= payment_remainder THEN + current_result.amount = payment_remainder; + billing_remainder = billing_remainder - payment_remainder; + payment_remainder = 0.0; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); + END IF; + ELSE + current_result.amount = billing_remainder; + payment_remainder = payment_remainder - billing_remainder; + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); + END IF; + + RETURN NEXT current_result; + EXIT WHEN payment_remainder = 0.0; + END LOOP; + END IF; EXIT WHEN payment_remainder = 0.0; END LOOP; payment_offset = payment_offset + 1; - -- Done paying the billing rows when we run out of rows to pay (out of bounds) - EXIT WHEN billing_offset = billing_row_count+1; + -- Done paying the billing rows when we run out of rows to pay (out of bounds) + EXIT WHEN array_length(billing_id_used, 1) = billing_row_count; END LOOP; @@ -998,23 +1064,23 @@ BEGIN billing_remainder := 0.0; payment_offset := 0; billing_offset := 0; - billing_row_count := 0; - billing_loop := -1; - + billing_row_count := 0; + billing_loop := -1; + -- figure out how many voided billing rows there are - FOR current_billing IN + FOR current_billing IN SELECT * FROM money.billing WHERE xact = p_xact AND voided ORDER BY billing_ts - LOOP - billing_row_count = billing_row_count + 1; - END LOOP; - + LOOP + billing_row_count = billing_row_count + 1; + END LOOP; + -- Loop through the negative payments, these are refunds on voided billings - FOR current_payment IN - SELECT * + FOR current_payment IN + SELECT * FROM money.payment_view WHERE xact = p_xact AND NOT voided @@ -1032,10 +1098,10 @@ BEGIN END IF; payment_remainder = -current_payment.amount; -- invert - -- With every new payment row, we need to fast forward - -- the billing lines up to the last paid billing row - fast_forward := TRUE; - + -- With every new payment row, we need to fast forward + -- the billing lines up to the last paid billing row + fast_forward := TRUE; + -- Were we looking at a billing from a previous step in the loop? IF billing_remainder > 0.0 THEN @@ -1048,61 +1114,58 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; --- Debug current_result.billing_leftover = billing_remainder; --- Debug current_result.payment_leftover = payment_remainder; IF billing_remainder >= payment_remainder THEN - current_result.amount = payment_remainder; - billing_remainder = billing_remainder - payment_remainder; - payment_remainder = 0.0; - payment_offset = payment_offset + 1; - -- If it is equal then we need to close up the billing line and move to the next - -- This prevents 0 amounts applied to billing lines - IF billing_remainder = payment_remainder THEN - billing_remainder = 0.0; - billing_offset = billing_offset + 1; - ELSE - maintain_billing_remainder := TRUE; - END IF; + current_result.amount = payment_remainder; + billing_remainder = billing_remainder - payment_remainder; + payment_remainder = 0.0; + payment_offset = payment_offset + 1; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + ELSE + maintain_billing_remainder := TRUE; + END IF; ELSE - current_result.amount = billing_remainder; + current_result.amount = billing_remainder; payment_remainder = payment_remainder - billing_remainder; billing_remainder = 0.0; - billing_offset = billing_offset + 1; + billing_offset = billing_offset + 1; END IF; current_result.amount = -current_result.amount; RETURN NEXT current_result; - -- Done paying the billing rows when we run out of rows to pay (out of bounds) - EXIT WHEN billing_offset = billing_row_count+1; + -- Done paying the billing rows when we run out of rows to pay (out of bounds) + EXIT WHEN billing_offset = billing_row_count + 1; END IF; CONTINUE WHEN payment_remainder = 0.0; -- next billing, please - - FOR current_billing IN + billing_loop := -1; + FOR current_billing IN SELECT * FROM money.billing WHERE xact = p_xact AND voided ORDER BY billing_ts LOOP - -- Fast forward through the rows until we get to the billing row - -- where we left off - IF fast_forward THEN - billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1 - CONTINUE WHEN billing_loop <> billing_offset; - END IF; - - -- Keep the old remainder if we were in the middle of a billing row - IF NOT maintain_billing_remainder THEN - billing_remainder = current_billing.amount; - END IF; - - maintain_billing_remainder := FALSE; - billing_loop := -1; - fast_forward := FALSE; + billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1 + -- Fast forward through the rows until we get to the billing row + -- where we left off + IF fast_forward THEN + CONTINUE WHEN billing_loop <> billing_offset; + END IF; + + -- Keep the old remainder if we were in the middle of a billing row + IF NOT maintain_billing_remainder THEN + billing_remainder = current_billing.amount; + END IF; + + maintain_billing_remainder := FALSE; + fast_forward := FALSE; current_result.xact = p_xact; current_result.payment = current_payment.id; @@ -1113,26 +1176,24 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; --- Debug current_result.billing_leftover = billing_remainder; --- Debug current_result.payment_leftover = payment_remainder; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; billing_remainder = billing_remainder - payment_remainder; payment_remainder = 0.0; - -- If it is equal then we need to close up the billing line and move to the next - -- This prevents 0 amounts applied to billing lines - IF billing_remainder = payment_remainder THEN - billing_remainder = 0.0; - billing_offset = billing_offset + 1; - END IF; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + END IF; ELSE current_result.amount = billing_remainder; payment_remainder = payment_remainder - billing_remainder; billing_remainder = 0.0; billing_offset = billing_offset + 1; END IF; - + current_result.amount = -current_result.amount; RETURN NEXT current_result; @@ -1141,8 +1202,8 @@ BEGIN END LOOP; payment_offset = payment_offset + 1; - -- Done paying the billing rows when we run out of rows to pay (out of bounds) - EXIT WHEN billing_offset = billing_row_count+1; + -- Done paying the billing rows when we run out of rows to pay (out of bounds) + EXIT WHEN billing_offset = billing_row_count + 1; END LOOP; @@ -1151,7 +1212,6 @@ END; $$ LANGUAGE PLPGSQL; - CREATE OR REPLACE FUNCTION money.payment_by_billing_type ( range_start TIMESTAMPTZ, range_end TIMESTAMPTZ, @@ -1163,7 +1223,7 @@ DECLARE current_result money.materialized_payment_by_billing_type%ROWTYPE; BEGIN - -- first, we find transactions at specified locations involivng + -- first, we find transactions at specified locations involving -- positve, unvoided payments within the specified range FOR current_transaction IN SELECT DISTINCT x.id diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type b/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type.sql similarity index 58% rename from Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type rename to Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type.sql index 1aedfc7d6b..abdcb2ea51 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.money.materialized_payment_by_billing_type.sql @@ -15,8 +15,10 @@ CREATE TABLE money.materialized_payment_by_billing_type ( billing_type TEXT, payment_ou INT, billing_ou INT, --- Debug billing_leftover NUMERIC(8,2), --- Debug payment_leftover NUMERIC(8,2), +-- Debug billing_leftover NUMERIC(8,2), +-- Debug payment_leftover NUMERIC(8,2), +-- Debug used_billings BIGINT[], +-- Debug billing_l INT, CONSTRAINT x_p_b_once UNIQUE (xact,payment,billing) ); @@ -38,11 +40,17 @@ DECLARE payment_offset INT := 0; billing_offset INT := 0; billing_ou INT := 0; - payment_ou INT := 0; - fast_forward BOOLEAN := FALSE; - maintain_billing_remainder BOOLEAN := FALSE; - billing_loop INT := -1; - billing_row_count INT := 0; + payment_ou INT := 0; + fast_forward BOOLEAN := FALSE; + maintain_billing_remainder BOOLEAN := FALSE; + billing_loop INT := -1; + billing_row_count INT := 0; + current_billing_id BIGINT := 0; + billing_id_used BIGINT ARRAY; + billing_l INT := 0; + continuing_payment BOOLEAN := FALSE; + continuing_payment_last_row BOOLEAN := FALSE; + BEGIN /* This algorithm is fairly straight-forward. We take a transaction id @@ -62,19 +70,19 @@ BEGIN SELECT circ_lib INTO billing_ou FROM action.circulation WHERE id = p_xact; END IF; - -- figure out how many billing rows there are - FOR current_billing IN - SELECT * + -- figure out how many billing rows there are + FOR current_billing IN + SELECT * FROM money.billing WHERE xact = p_xact -- AND NOT voided ORDER BY billing_ts - LOOP - billing_row_count = billing_row_count + 1; - END LOOP; + LOOP + billing_row_count = billing_row_count + 1; + END LOOP; -- Loop through the positive payments - FOR current_payment IN - SELECT * + FOR current_payment IN + SELECT * FROM money.payment_view WHERE xact = p_xact AND NOT voided @@ -82,10 +90,10 @@ BEGIN ORDER BY payment_ts LOOP - payment_remainder = current_payment.amount; - -- With every new payment row, we need to fast forward - -- the billing lines up to the last paid billing row - fast_forward := TRUE; + payment_remainder = current_payment.amount; + -- With every new payment row, we need to fast forward + -- the billing lines up to the last paid billing row + fast_forward := TRUE; IF current_payment.payment_type IN ( 'cash_payment', 'check_payment', 'credit_card_payment' ) THEN SELECT ws.owning_lib INTO payment_ou FROM money.bnm_desk_payment p @@ -96,7 +104,7 @@ BEGIN END IF; -- Were we looking at a billing from a previous step in the loop? - IF billing_remainder > 0.0 THEN + IF billing_remainder > 0.0 THEN current_result.xact = p_xact; current_result.payment = current_payment.id; current_result.billing = current_billing.id; @@ -106,63 +114,77 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; --- Debug current_result.billing_leftover = billing_remainder; --- Debug current_result.payment_leftover = payment_remainder; +-- Debug current_result.billing_leftover = billing_remainder; +-- Debug current_result.payment_leftover = payment_remainder; +-- Debug current_result.used_billings = billing_id_used; +-- Debug current_result.billing_l = billing_loop; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; billing_remainder = billing_remainder - payment_remainder; payment_remainder = 0.0; payment_offset = payment_offset + 1; - -- If it is equal then we need to close up the billing line and move to the next - -- This prevents 0 amounts applied to billing lines - IF billing_remainder = payment_remainder THEN - billing_remainder = 0.0; - billing_offset = billing_offset + 1; - ELSE - maintain_billing_remainder := TRUE; - END IF; - + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); + ELSE + maintain_billing_remainder := TRUE; + END IF; + ELSE current_result.amount = billing_remainder; payment_remainder = payment_remainder - billing_remainder; billing_remainder = 0.0; - billing_offset = billing_offset + 1; + billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); + continuing_payment := TRUE; END IF; RETURN NEXT current_result; - -- Done paying the billing rows when we run out of rows to pay (out of bounds) - EXIT WHEN billing_offset = billing_row_count+1; + -- Done paying the billing rows when we run out of rows to pay (out of bounds) + EXIT WHEN array_length(billing_id_used, 1) = billing_row_count; END IF; CONTINUE WHEN payment_remainder = 0.0; -- next billing, please + billing_loop := -1; FOR current_billing IN - SELECT * + SELECT * FROM money.billing WHERE xact = p_xact - -- AND NOT voided - -- Gotta put the voided billing rows at the bottom (last) + -- Gotta put the voided billing rows at the bottom (last) ORDER BY voided,billing_ts LOOP - -- Fast forward through the rows until we get to the billing row - -- where we left off - IF fast_forward THEN - billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1 - CONTINUE WHEN billing_loop <> billing_offset; - END IF; - - -- Keep the old remainder if we were in the middle of a billing row - IF NOT maintain_billing_remainder THEN - billing_remainder = current_billing.amount; - END IF; - - maintain_billing_remainder := FALSE; - billing_loop := -1; - fast_forward := FALSE; - + billing_loop = billing_loop + 1; + + -- Skip billing rows that we have already paid + IF billing_id_used @> ARRAY[current_billing.id] THEN CONTINUE; + END IF; + + IF maintain_billing_remainder THEN + CONTINUE WHEN current_billing.id <> current_billing_id; + -- Account adjustment - we expect to pay billing rows that are identical amounts + ELSE IF current_payment.payment_type = 'account_adjustment' THEN + -- Go ahead and allow the row through when it's the last row and we still haven't found one with equal payment amount + CONTINUE WHEN ( ( current_billing.amount <> current_payment.amount ) AND ( billing_loop + 1 <> billing_row_count ) ); + END IF; + END IF; + + -- Keep the old remainder if we were in the middle of a billing row + IF NOT maintain_billing_remainder THEN + billing_remainder = current_billing.amount; + END IF; + + maintain_billing_remainder := FALSE; + fast_forward := FALSE; + current_billing_id := current_billing.id; + continuing_payment := FALSE; + current_result.xact = p_xact; current_result.payment = current_payment.id; current_result.billing = current_billing.id; @@ -172,38 +194,100 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; --- Debug current_result.billing_leftover = billing_remainder; --- Debug current_result.payment_leftover = payment_remainder; +-- Debug current_result.billing_leftover = billing_remainder; +-- Debug current_result.payment_leftover = payment_remainder; +-- Debug current_result.used_billings = billing_id_used; +-- Debug current_result.billing_l = billing_loop; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; billing_remainder = billing_remainder - payment_remainder; - IF NOT current_billing.voided THEN - payment_remainder = 0.0; + payment_remainder = 0.0; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); END IF; - -- If it is equal then we need to close up the billing line and move to the next - -- This prevents 0 amounts applied to billing lines - IF billing_remainder = payment_remainder THEN - billing_remainder = 0.0; - billing_offset = billing_offset + 1; - END IF; ELSE current_result.amount = billing_remainder; - IF NOT current_billing.voided THEN - payment_remainder = payment_remainder - billing_remainder; + payment_remainder = payment_remainder - billing_remainder; + continuing_payment := TRUE; + IF billing_loop + 1 = billing_row_count THEN + -- We have a situation where we are on the last billing row and we are in the middle of a payment row + -- We need to start back at the beginning of the billing rows and pay + continuing_payment_last_row := TRUE; END IF; billing_remainder = 0.0; billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); END IF; - + RETURN NEXT current_result; + IF continuing_payment_last_row THEN + -- This should only occur when the account_adjustment's do not line up exactly with the billing + -- So we are going to pay some other type of billing row with this odd account_adjustment + -- And we need to stay in the current_payment row while doing so + billing_loop := -1; + FOR current_billing IN + SELECT * + FROM money.billing + WHERE xact = p_xact + ORDER BY voided,billing_ts + LOOP + billing_loop = billing_loop + 1; + -- Skip billing rows that we have already paid + IF billing_id_used @> ARRAY[current_billing.id] THEN CONTINUE; END IF; + + billing_remainder = current_billing.amount; + current_billing_id := current_billing.id; + continuing_payment := FALSE; + + current_result.xact = p_xact; + current_result.payment = current_payment.id; + current_result.billing = current_billing.id; + current_result.payment_ts = current_payment.payment_ts; + current_result.billing_ts = current_billing.billing_ts; + current_result.payment_type = current_payment.payment_type; + current_result.billing_type = current_billing.billing_type; + current_result.payment_ou = payment_ou; + current_result.billing_ou = billing_ou; +-- Debug current_result.billing_leftover = billing_remainder; +-- Debug current_result.payment_leftover = payment_remainder; +-- Debug current_result.used_billings = billing_id_used; +-- Debug current_result.billing_l = billing_loop; + + IF billing_remainder >= payment_remainder THEN + current_result.amount = payment_remainder; + billing_remainder = billing_remainder - payment_remainder; + payment_remainder = 0.0; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); + END IF; + ELSE + current_result.amount = billing_remainder; + payment_remainder = payment_remainder - billing_remainder; + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + billing_id_used = array_append( billing_id_used, current_billing_id ); + END IF; + + RETURN NEXT current_result; + EXIT WHEN payment_remainder = 0.0; + END LOOP; + END IF; EXIT WHEN payment_remainder = 0.0; END LOOP; payment_offset = payment_offset + 1; - -- Done paying the billing rows when we run out of rows to pay (out of bounds) - EXIT WHEN billing_offset = billing_row_count+1; + -- Done paying the billing rows when we run out of rows to pay (out of bounds) + EXIT WHEN array_length(billing_id_used, 1) = billing_row_count; END LOOP; @@ -211,23 +295,23 @@ BEGIN billing_remainder := 0.0; payment_offset := 0; billing_offset := 0; - billing_row_count := 0; - billing_loop := -1; - + billing_row_count := 0; + billing_loop := -1; + -- figure out how many voided billing rows there are - FOR current_billing IN + FOR current_billing IN SELECT * FROM money.billing WHERE xact = p_xact AND voided ORDER BY billing_ts - LOOP - billing_row_count = billing_row_count + 1; - END LOOP; - + LOOP + billing_row_count = billing_row_count + 1; + END LOOP; + -- Loop through the negative payments, these are refunds on voided billings - FOR current_payment IN - SELECT * + FOR current_payment IN + SELECT * FROM money.payment_view WHERE xact = p_xact AND NOT voided @@ -245,10 +329,10 @@ BEGIN END IF; payment_remainder = -current_payment.amount; -- invert - -- With every new payment row, we need to fast forward - -- the billing lines up to the last paid billing row - fast_forward := TRUE; - + -- With every new payment row, we need to fast forward + -- the billing lines up to the last paid billing row + fast_forward := TRUE; + -- Were we looking at a billing from a previous step in the loop? IF billing_remainder > 0.0 THEN @@ -261,61 +345,61 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; --- Debug current_result.billing_leftover = billing_remainder; --- Debug current_result.payment_leftover = payment_remainder; +-- Debug current_result.billing_leftover = billing_remainder; +-- Debug current_result.payment_leftover = payment_remainder; +-- Debug current_result.billing_l = billing_loop; IF billing_remainder >= payment_remainder THEN - current_result.amount = payment_remainder; - billing_remainder = billing_remainder - payment_remainder; - payment_remainder = 0.0; - payment_offset = payment_offset + 1; - -- If it is equal then we need to close up the billing line and move to the next - -- This prevents 0 amounts applied to billing lines - IF billing_remainder = payment_remainder THEN - billing_remainder = 0.0; - billing_offset = billing_offset + 1; - ELSE - maintain_billing_remainder := TRUE; - END IF; + current_result.amount = payment_remainder; + billing_remainder = billing_remainder - payment_remainder; + payment_remainder = 0.0; + payment_offset = payment_offset + 1; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + ELSE + maintain_billing_remainder := TRUE; + END IF; ELSE - current_result.amount = billing_remainder; + current_result.amount = billing_remainder; payment_remainder = payment_remainder - billing_remainder; billing_remainder = 0.0; - billing_offset = billing_offset + 1; + billing_offset = billing_offset + 1; END IF; current_result.amount = -current_result.amount; RETURN NEXT current_result; - -- Done paying the billing rows when we run out of rows to pay (out of bounds) - EXIT WHEN billing_offset = billing_row_count+1; + -- Done paying the billing rows when we run out of rows to pay (out of bounds) + EXIT WHEN billing_offset = billing_row_count + 1; END IF; CONTINUE WHEN payment_remainder = 0.0; -- next billing, please - - FOR current_billing IN + billing_loop := -1; + FOR current_billing IN SELECT * FROM money.billing WHERE xact = p_xact AND voided ORDER BY billing_ts LOOP - -- Fast forward through the rows until we get to the billing row - -- where we left off - IF fast_forward THEN - billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1 - CONTINUE WHEN billing_loop <> billing_offset; - END IF; - - -- Keep the old remainder if we were in the middle of a billing row - IF NOT maintain_billing_remainder THEN - billing_remainder = current_billing.amount; - END IF; - - maintain_billing_remainder := FALSE; - billing_loop := -1; - fast_forward := FALSE; + billing_loop = billing_loop + 1; -- first iteration billing_loop=0, it starts at -1 + -- Fast forward through the rows until we get to the billing row + -- where we left off + IF fast_forward THEN + CONTINUE WHEN billing_loop <> billing_offset; + END IF; + + -- Keep the old remainder if we were in the middle of a billing row + IF NOT maintain_billing_remainder THEN + billing_remainder = current_billing.amount; + END IF; + + maintain_billing_remainder := FALSE; + fast_forward := FALSE; current_result.xact = p_xact; current_result.payment = current_payment.id; @@ -326,26 +410,27 @@ BEGIN current_result.billing_type = current_billing.billing_type; current_result.payment_ou = payment_ou; current_result.billing_ou = billing_ou; --- Debug current_result.billing_leftover = billing_remainder; --- Debug current_result.payment_leftover = payment_remainder; +-- Debug current_result.billing_leftover = billing_remainder; +-- Debug current_result.payment_leftover = payment_remainder; +-- Debug current_result.billing_l = billing_loop; IF billing_remainder >= payment_remainder THEN current_result.amount = payment_remainder; billing_remainder = billing_remainder - payment_remainder; payment_remainder = 0.0; - -- If it is equal then we need to close up the billing line and move to the next - -- This prevents 0 amounts applied to billing lines - IF billing_remainder = payment_remainder THEN - billing_remainder = 0.0; - billing_offset = billing_offset + 1; - END IF; + -- If it is equal then we need to close up the billing line and move to the next + -- This prevents 0 amounts applied to billing lines + IF billing_remainder = payment_remainder THEN + billing_remainder = 0.0; + billing_offset = billing_offset + 1; + END IF; ELSE current_result.amount = billing_remainder; payment_remainder = payment_remainder - billing_remainder; billing_remainder = 0.0; billing_offset = billing_offset + 1; END IF; - + current_result.amount = -current_result.amount; RETURN NEXT current_result; @@ -354,8 +439,8 @@ BEGIN END LOOP; payment_offset = payment_offset + 1; - -- Done paying the billing rows when we run out of rows to pay (out of bounds) - EXIT WHEN billing_offset = billing_row_count+1; + -- Done paying the billing rows when we run out of rows to pay (out of bounds) + EXIT WHEN billing_offset = billing_row_count + 1; END LOOP; @@ -376,7 +461,7 @@ DECLARE current_result money.materialized_payment_by_billing_type%ROWTYPE; BEGIN - -- first, we find transactions at specified locations involivng + -- first, we find transactions at specified locations involving -- positve, unvoided payments within the specified range FOR current_transaction IN SELECT DISTINCT x.id -- 2.11.0