metafresh logo and link to homepage

IT FRESH-152

IT FRESH-152

Extract statistics fields from C_BPartner and put them to a new table called C_BPartner_Stats

Testcase to check if the C_BPartner_Stats table updates accordingly

C_BPartner_Stats table

  1. go to business partner window, C_BPartner_Stats subtab

C_BPartner_Stats after invoice

  1. Create new purchase invoice, manually

  2. add a product, a quantity and a price. Complete
  3. Wait a bit and run the sql you’ll find at the bottom of the page
  4. Create a sales invoice, manually

  5. repeat steps 2 and 3
  6. create a sales and a purchase invoice, automatically, from orders:
  7. Wait a bit and run the sql you’ll find at the bottom of the page

C_BPartner_Stats after credit memo

  1. open one of the sales invoices you did above and press on “create credit memo” button, select a doc subtype, ok
  2. Wait a bit and run the sql you’ll find at the bottom of the page
  3. on the same invoice press on “Adjustment Charge” button, select a doc subtype, press ok

  4. complete the new invoice
  5. Wait a bit and run the sql you’ll find at the bottom of the page
  6. go to sales invoice window and create a new credit memo invoice, manually, complete
  7. Wait a bit and run the sql you’ll find at the bottom of the page
  8. go to purchase invoice window and create a new credit memo invoice, manually, complete
  9. Wait a bit and run the sql you’ll find at the bottom of the page

C_BPartner_Stats after payments

  1. go to payment windows and create a new one: incoming

  2. add your sales invoice there, complete

  3. Wait a bit and run the sql you’ll find at the bottom of the page
  4. create another payment: outgoing, and add your purchase invoice there

  5. Wait a bit and run the sql you’ll find at the bottom of the page
  6. go to pay selection window and create a new one
  7. Wait a bit and run the sql you’ll find at the bottom of the page

sql function to compare the results

SELECT
COALESCE((SELECT SUM(currencyBase(invoiceOpen(i.C_Invoice_ID,i.C_InvoicePaySchedule_ID),i.C_Currency_ID,i.DateInvoiced, i.AD_Client_ID,i.AD_Org_ID)) FROM C_Invoice_v i
WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND i.IsSOTrx='Y' AND i.IsPaid='N' AND i.DocStatus IN ('CO','CL')),0) AS SO_Credit_Used,
bps.SO_CreditUsed,
COALESCE((SELECT SUM(currencyBase(invoiceOpen(i.C_Invoice_ID,i.C_InvoicePaySchedule_ID),i.C_Currency_ID,i.DateInvoiced, i.AD_Client_ID,i.AD_Org_ID)*i.MultiplierAP) FROM C_Invoice_v i
WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND i.IsPaid='N' AND i.DocStatus IN ('CO','CL')),0) -
COALESCE((SELECT SUM(currencyBase(Paymentavailable(p.C_Payment_ID),p.C_Currency_ID,p.DateTrx,p.AD_Client_ID,p.AD_Org_ID)) FROM C_Payment_v p
WHERE p.C_BPartner_ID=bp.C_BPartner_ID AND p.IsAllocated='N'
AND p.C_Charge_ID IS NULL AND p.DocStatus IN ('CO','CL')),0) as Total_Open_Balance,
bps.TotalOpenBalance,
COALESCE ((SELECT SUM(currencyBase(i.GrandTotal,i.C_Currency_ID,i.DateInvoiced, i.AD_Client_ID,i.AD_Org_ID)) FROM C_Invoice_v i
WHERE i.C_BPartner_ID=bp.C_BPartner_ID AND i.IsSOTrx='Y' AND i.DocStatus IN ('CO','CL')),0) as Actual_LifeTIme_Value,
bps.ActualLifeTImeValue
FROM C_BPartner bp join C_BPartner_Stats bps on bps.C_BPartner_ID = bp.c_bpartner_ID WHERE bp.C_BPartner_ID= your_BP_id;

Zur Quelldatei auf github.com