Início / dba / Perguntas / 3415
Accepted
humble_coder
Asked: 2011-06-22 11:41:42 +0800 CST

É possível escrever uma consulta SQL que automaticamente reconcilie/"transacione" pagamentos e cobranças

  • 772

Ainda estou trabalhando no projeto mencionado aqui (http://dba.stackexchange.com/questions/2428/how-do-i-properly-design-a-many-to-many-charges-payments-accounting-system ).

O sistema deve dar ao usuário a opção de pagar quantias específicas em cobranças específicas ou fazer pagamentos genéricos "você descobre". Dada a estrutura da tabela que usamos (PAYMENTS, CHARGES, PAYMENTS_TO_CHARGES), estou tentando trapacear um pouco. Basicamente, estou procurando uma consulta SQL "reconciliar" incrivelmente elegante que fará o seguinte:

PASSO 1) Pegue todos os pagamentos com saldo restante (basicamente créditos)

PASSO 2) Pegue todas as cobranças com saldo restante (parcialmente pago, etc)

PASSO 3) Insira parcelas de pagamentos na tabela PAYMENTS_TO_CHARGES até que não haja mais crédito disponível ou não haja mais cobranças.

…Eu acho que tecnicamente isso não é tanto uma reconciliação quanto a criação de dados transacionais, mas você entendeu.

Os passos 1 e 2 são obviamente muito fáceis. É a Etapa 3 que é o assassino. Se não houver uma maneira elegante de fazer isso em SQL, suponho que irei com o antigo passo a passo codificado à mão para cada transação e postar payment_to_charge... apenas pensei em perguntar.

Desde já, obrigado!

EDIT 1: Eu criei esta consulta para determinar quais cobranças têm saldo restante, mas está me dando um erro dizendo "Coluna desconhecida 'remaining_balance' in 'where clausule'":

SELECT 
       charges.*
     , (charges.amount - transactions.total_paid) as remaining_balance 
FROM charges
   , (SELECT 
             charge_id
           , sum(amount) as total_paid 
      FROM payments_to_charges 
      GROUP BY charge_id) as transactions 
WHERE charges.member_id = 123 
  AND charges.id = transactions.charge_id 
  AND remaining_balance > 0 
  AND charges.active_on < NOW()

Tenho certeza de que alguns itens estão simplesmente fora de ordem, mas não consigo descobrir o que geralmente há de errado com essa consulta específica. Devo usar HAVING em vez de WHERE? Estou perdendo algo completamente óbvio?

transaction query

1 respostas

  1. Best Answer

    Você pode fazer o INSERT em Payemnts_To_Charges em uma instrução SQL, mas não tenho certeza se valeria a pena. Parece que isso seria mais fácil de construir, depurar e manter no código processual. Algo assim:

    CREATE TABLE Payments (PaymentId Number(10), Amount Number(6,2));
CREATE TABLE Charges  (ChargeID Number(10), Amount Number(6,2));
CREATE TABLE Payments_To_Charges 
   (PaymentID Number(10), ChargeID Number(10), Amount Number(6,2));

INSERT INTO Payments VALUES (1,4);
INSERT INTO Payments VALUES (2,4);

INSERT INTO Charges VALUES (1,2);
INSERT INTO Charges VALUES (2,5);
INSERT INTO Charges VALUES (3,6);
INSERT INTO Charges VALUES (4,4);
INSERT INTO Charges VALUES (5,10);

Declare
   vPaymentAmount Payments.Amount%Type;
   vAppliedAmount Payments_To_Charges.Amount%Type;
Begin
   For vPayment In (SELECT PaymentID, Amount FROM Payments) Loop
      vPaymentAmount := vPayment.Amount;      
      For vCharge In (
            SELECT ChargeID, Amount FROM
            (
               SELECT ChargeID, Amount - 
                  NVL((SELECT SUM(Amount) FROM Payments_To_Charges pc 
                       WHERE pc.ChargeID = c.ChargeID),0) Amount
               FROM Charges c            
            ) WHERE Amount > 0
         ) Loop
         vAppliedAmount := LEAST(vPaymentAmount, vCharge.Amount);
         INSERT INTO Payments_To_Charges (PaymentID, ChargeID, Amount)
            VALUES (vPayment.PaymentID, vCharge.ChargeID, vAppliedAmount);
         vPaymentAmount := vPaymentAmount - vAppliedAmount;
         If (vPaymentAmount = 0) Then
            Exit;
         End If;
      End Loop;
   End Loop;
End;
/

SELECT * FROM Payments_To_Charges;

    Atualizar:

    restante_equilíbrio é o problema. Você não pode fazer referência ao valor com alias na cláusula WHERE. Você pode transformar a consulta em uma subconsulta e adicionar essa condição em um nível superior ou alterar o saldo_restante na cláusula WHERE para (charges.amount - transaction.total_paid).

    • 4

relate perguntas