Hi
Ich suche eine SQL Funktion, mit welcher man den offenen Betrag einer Zahlung (noch nicht einer Rechnung oder einem anderen Beleg zugeordnet) finden kann. Gibt es eine solche Funktion? Kann ich den offenen Betrag im Fenster Zahlung anzeigen?
Vielen Dank für Eure Hilfe!
Simon
Hi Simon!
Yes, exists already such function and is called paymentavailable(p_c_payment_id numeric).
You can use it very easy: select * from paymentavailable(c_payment_id).
If you have an amount different from 0, then that is unallocated amount.
The open amount can not be seen in the payment window.
Best Regards,
Cristina
Hi Cristina
Thank you very much for your help.
Would it be possible to add the info via virtual column with something like
select * from paymentavailable((select c_payment_id from c_payment where c_payment_id = c_payment.c_paymant.id))
in the payment window?
Many thanks again for your help!
Hi,
I think (paymentavailable(c_payment_id)) should be working as you have c_payment_id already in the record set.
cheers,
Norbert
Hi
Thank you, I will give it a try.
cheers
Hi,
I tried to use the function as stated above and added it to a new column in the payment window. If i try to open the window now, i get the following error:
org.postgresql.util.PSQLException: ERROR: operator does not exist: numeric = character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 1
(Can also provide the complete error message with the sql if needed)
Do you have any idea how to solve this?
Many thanks
Simon
Error Message:
org.postgresql.util.PSQLException: ERROR: operator does not exist: numeric = character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 1561 SQL: SELECT master.* , (SELECT NULLIF (TRIM(C_DocType.Name), '') FROM C_DocType WHERE C_DocType.C_DocType_ID=master.C_DocType_ID ) AS C_DocType_ID$Display , (SELECT NULLIF (TRIM(AD_Org.Name), '') FROM AD_Org WHERE AD_Org.AD_Org_ID=master.AD_Org_ID ) AS AD_Org_ID$Display , (SELECT concat_ws('_', NULLIF (TRIM(C_BP_BankAccount.Name), ''), NULLIF (TRIM(C_BP_BankAccount.A_Name), ''), NULLIF (TRIM(C_BP_BankAccount.AccountNo), ''), ( select concat_ws('_', NULLIF (TRIM(C_Bank.Name), ''), NULLIF (TRIM(C_Bank.RoutingNo), '')) from C_Bank where C_Bank.C_Bank_ID=C_BP_BankAccount.C_Bank_ID)) FROM C_BP_BankAccount WHERE C_BP_BankAccount.C_BP_BankAccount_ID=master.C_BP_BankAccount_ID ) AS C_BP_BankAccount_ID$Display , (SELECT concat_ws('_', NULLIF (TRIM(C_BPartner.Name), ''), NULLIF (TRIM(C_BPartner.Value), '')) FROM C_BPartner WHERE C_BPartner.C_BPartner_ID=master.C_BPartner_ID ) AS C_BPartner_ID$Display , (SELECT concat_ws('_', NULLIF (TRIM(C_Invoice.DocumentNo), ''), CAST (C_Invoice.DateInvoiced::date AS Text), CAST (C_Invoice.GrandTotal AS Text)) FROM C_Invoice WHERE C_Invoice.C_Invoice_ID=master.C_Invoice_ID ) AS C_Invoice_ID$Display , (SELECT NULLIF (TRIM(C_Currency.ISO_Code), '') FROM C_Currency WHERE C_Currency.C_Currency_ID=master.C_Currency_ID ) AS C_Currency_ID$Display , (SELECT AD_Ref_List.Name FROM AD_Ref_List WHERE AD_Ref_List.Value=master.DocStatus AND AD_Ref_List.AD_Reference_ID=131) AS DocStatus$Display , (SELECT NULLIF (TRIM(C_BankStatement.DocumentNo), '') FROM C_BankStatement WHERE C_BankStatement.C_BankStatement_ID=master.ESR_Payment_Action ) AS ESR_Payment_Action$Display , null AS _sel_parent_IntKey1 ,_sel_Line FROM ( SELECT C_Payment.C_DocType_ID AS C_DocType_ID , C_Payment.AD_Org_ID AS AD_Org_ID , C_Payment.DocumentNo AS DocumentNo , C_Payment.IsReceipt AS IsReceipt , C_Payment.C_BP_BankAccount_ID AS C_BP_BankAccount_ID , C_Payment.DateTrx AS DateTrx , C_Payment.DateAcct AS DateAcct , C_Payment.Description AS Description , C_Payment.C_BPartner_ID AS C_BPartner_ID , C_Payment.C_Invoice_ID AS C_Invoice_ID , C_Payment.C_Order_ID AS C_Order_ID , C_Payment.C_Project_ID AS C_Project_ID , C_Payment.C_Charge_ID AS C_Charge_ID , C_Payment.IsPrepayment AS IsPrepayment , C_Payment.C_Activity_ID AS C_Activity_ID , C_Payment.C_Campaign_ID AS C_Campaign_ID , C_Payment.AD_OrgTrx_ID AS AD_OrgTrx_ID , C_Payment.User1_ID AS User1_ID , C_Payment.User2_ID AS User2_ID , C_Payment.PayAmt AS PayAmt , C_Payment.C_Currency_ID AS C_Currency_ID , C_Payment.C_ConversionType_ID AS C_ConversionType_ID , C_Payment.DiscountAmt AS DiscountAmt , C_Payment.WriteOffAmt AS WriteOffAmt , C_Payment.IsOverUnderPayment AS IsOverUnderPayment , C_Payment.OverUnderAmt AS OverUnderAmt , C_Payment.TenderType AS TenderType , C_Payment.RoutingNo AS RoutingNo , C_Payment.AccountNo AS AccountNo , C_Payment.CheckNo AS CheckNo , C_Payment.Micr AS Micr , C_Payment.CreditCardType AS CreditCardType , C_Payment.TrxType AS TrxType , C_Payment.CreditCardNumber AS CreditCardNumber , C_Payment.CreditCardVV AS CreditCardVV , C_Payment.CreditCardExpMM AS CreditCardExpMM , C_Payment.CreditCardExpYY AS CreditCardExpYY , C_Payment.A_Name AS A_Name , C_Payment.A_Street AS A_Street , C_Payment.A_City AS A_City , C_Payment.A_Zip AS A_Zip , C_Payment.A_State AS A_State , C_Payment.A_Country AS A_Country , C_Payment.A_Ident_DL AS A_Ident_DL , C_Payment.A_Ident_SSN AS A_Ident_SSN , C_Payment.A_EMail AS A_EMail , C_Payment.TaxAmt AS TaxAmt , C_Payment.PONum AS PONum , C_Payment.OProcessing AS OProcessing , C_Payment.Orig_TrxID AS Orig_TrxID , C_Payment.IsApproved AS IsApproved , C_Payment.IsOnlineApproved AS IsOnlineApproved , C_Payment.R_Result AS R_Result , C_Payment.R_RespMsg AS R_RespMsg , C_Payment.R_PnRef AS R_PnRef , C_Payment.R_AuthCode AS R_AuthCode , C_Payment.R_AvsZip AS R_AvsZip , C_Payment.R_AvsAddr AS R_AvsAddr , C_Payment.DocStatus AS DocStatus , C_Payment.DocAction AS DocAction , C_Payment.IsSelfService AS IsSelfService , C_Payment.Posted AS Posted , C_Payment.IsAllocated AS IsAllocated , C_Payment.IsReconciled AS IsReconciled , (select esr_payment_action from esr_importline esr where esr.c_payment_id = c_payment.c_payment_id) AS ESR_Payment_Action , (select paymentavailable(c_payment.c_payment_id)) AS paymentam_not_allocated , C_Payment.AD_Client_ID AS AD_Client_ID , C_Payment.C_Payment_ID AS C_Payment_ID , C_Payment.IsActive AS IsActive , C_Payment.Processed AS Processed , C_Payment.Processing AS Processing , C_Payment.R_Info AS R_Info , C_Payment.VoiceAuthCode AS VoiceAuthCode , C_Payment.C_PaymentBatch_ID AS C_PaymentBatch_ID , C_Payment.IsOnline AS IsOnline , C_Payment.IsDelayedCapture AS IsDelayedCapture , C_Payment.ChargeAmt AS ChargeAmt , C_Payment.R_AuthCode_DC AS R_AuthCode_DC , C_Payment.R_CVV2Match AS R_CVV2Match , C_Payment.R_PnRef_DC AS R_PnRef_DC , C_Payment.Swipe AS Swipe , C_Payment.C_CashBook_ID AS C_CashBook_ID , C_Payment.Ref_Payment_ID AS Ref_Payment_ID , C_Payment.Updated AS Updated , C_Payment.UpdatedBy AS UpdatedBy , C_Payment.Created AS Created , C_Payment.CreatedBy AS CreatedBy , C_Payment.Reversal_ID AS Reversal_ID , C_Payment.CCPaymentState AS CCPaymentState , C_Payment.IsAutoAllocateAvailableAmt AS IsAutoAllocateAvailableAmt , sel.Line AS _sel_Line , sel.UUID AS _sel_UUID , sel.IntKey1 AS _sel_IntKey1 , (case when C_Payment.C_Payment_ID is null then 'Y' else 'N' end) AS _sel_IsRecordMissing FROM T_WEBUI_ViewSelection sel LEFT OUTER JOIN C_Payment ON (C_Payment.C_Payment_ID=sel.IntKey1) WHERE sel.UUID=? ) master WHERE _sel_Line BETWEEN ? AND ? ORDER BY _sel_Line SQL params: [195-dbd3a55318d641a8a12dcab85b21e57d, 1, 20]
that is the position of the error WITHIN the SQL statement beginning at “SQL:”.
When you check on that position with notepad++ or similar you will find:
(SELECT NULLIF (TRIM(C_BankStatement.DocumentNo), '') FROM C_BankStatement WHERE C_BankStatement.C_BankStatement_ID=master.ESR_Payment_Action ) AS ESR_Payment_Action$Display
so I assume not the function is the problem but another virtual column.
Norbert