Create Sales YTD in Macola ES Cicmpy ( Cust Table) 1

[jbphmd]

Moving from Progression to ES. I have Reports that show Customer Sales YTD ( Field not avail in ES). Does anyone have a Stored Procedure to Calculate Sales YTD and Update Cicmpy - NumberField1 ?

Thanks for the Help

 

[kscheeler]

Personally, I would read this information from the OEHDRHST_SQL and OELINHST_SQL tables. I was never one to trust the accumulators in the customer / vendor tables. I always have to help clients correct them because they were either reset too early or not at all or for whatever other reason.

In any case, in ES the system calculates the sales and cost information from the statistics page with a SQL query of both the banktransactions and gbkmut tables. If you run profiler and then click on the statistics tab and then check profiler, you'll see the query. I believe with the conversion, you will only start to see sales/cost information going forward.

You could create a sql job that runs daily that recalculates YTD sales from OEHDRHST_SQL and OELINHST_SQL (linked on ord_type, ord_no and inv_no) and then update the cicmpy table with that information.



Kevin Scheeler

 

[dgillz]

Kevin,

Followup question for you. If you click on the statistics tab and then click on the sales data and hit cntrl-Q, you get the following code:

SELECT * FROM #SalesStatistics259E247402F34D1A83897E02A88C0E4E

This is the same every time except the last 32 characters which change every time. However I cannot find a view or stored procedure that is called 'SalesStatistics' or anything even close. I was hoping to find this to see the actual code they use.

By the way I left the customer maintenance screen open while I was looking for the view or sp in enterprise manager.

Any insights appreciated.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[kscheeler]

Don,

You need to run profiler to see the full code.

Here's the code that's created from profiler to create the grid (using customer 901 from the demo company)

---------------------------------------------------------

SELECT
Period,
SUM(Sales) AS Sales,
SUM(Cogs) AS COGS,
SUM(Invoices) AS Invoices,
SUM(Paid) AS Paid,
SUM(SalesLY) AS 'Sales(LY)',
SUM(CogsLY) AS 'COGS(LY)',
SUM(InvoicesLY) AS 'Invoices(LY)',
SUM(PaidLY) AS 'Paid(LY)'

FROM

(
---------Select the Sales of goods---------
SELECT
d.debcode,
p.bkjrcode,
g.periode,
SUM(CASE p.bkjrcode WHEN 2006 THEN -g.bdr_hfl ELSE 0 END) AS Sales,
SUM(CASE p.bkjrcode WHEN 2005 THEN -g.bdr_hfl ELSE 0 END) AS SalesLY,
0,0,0,0,0,0
FROM gbkmut g
INNER JOIN grtbk k ON g.reknr = k.reknr AND (k.omzrek = 'J' OR (k.omzrek='N' AND g.freefield3='Converted Transaction GL' AND g.bdr_hfl<0))--Revenue Account, To exclude freight and miscellaneous, make these accounts N
INNER JOIN divisiondebtors d ON g.debnr = d.debtor AND g.companycode = d.division
INNER JOIN perdat p ON g.datum BETWEEN p.bgdatum AND p.eddatum AND p.bkjrcode IN (2006,2005)
WHERE g.transsubtype IN ('K','C') AND g.transtype IN ('N','C','P') AND g.faktuurnr IS NOT NULL AND d.debcode = ' 901'
GROUP BY d.debcode,p.bkjrcode,g.periode

UNION
---------Select the cost of goods---------
SELECT
d.debcode,
p.bkjrcode,
g.periode,
0,0,
SUM(CASE p.bkjrcode WHEN 2006 THEN g.bdr_hfl ELSE 0 END) AS Cogs,
SUM(CASE p.bkjrcode WHEN 2005 THEN g.bdr_hfl ELSE 0 END) AS CogsLY,
0,0,0,0
FROM gbkmut g
--NOTE: Some think the k.omzrek = 'K' clause below shouldn't be there.
INNER JOIN grtbk k ON g.reknr = k.reknr AND k.omzrek = 'K'
INNER JOIN divisiondebtors d ON g.companycode = d.division
INNER JOIN perdat p ON g.datum BETWEEN p.bgdatum AND p.eddatum AND p.bkjrcode IN (2006,2005)
INNER JOIN (

SELECT DISTINCT g1.bkstnr
FROM gbkmut g1
INNER JOIN grtbk k1 on g1.reknr = k1.reknr AND k1.omzrek = 'D'
INNER JOIN divisiondebtors d1 ON g1.debnr = d1.debtor AND g1.companycode = d1.division
WHERE g1.transsubtype IN ('K','C') AND
g1.transtype IN ('N','C','P') AND
g1.faktuurnr IS NOT NULL AND
d1.debcode = ' 901') AS F1 ON g.bkstnr = F1.bkstnr
WHERE g.aantal > 0 AND g.transtype IN ('N','C','P') AND d.debcode = ' 901'
GROUP BY d.debcode,p.bkjrcode,g.periode


UNION
---------Select the Invoice count---------
SELECT
d.debcode,
p.bkjrcode,
g.periode,
0,0,0,0,
COUNT(CASE p.bkjrcode WHEN 2006 THEN g.faktuurnr ELSE NULL END) AS Invoices,
COUNT(CASE p.bkjrcode WHEN 2005 THEN g.faktuurnr ELSE NULL END) AS InvoicesLY,
0,0
FROM gbkmut g
INNER JOIN grtbk k ON g.reknr = k.reknr AND k.omzrek = 'D'
INNER JOIN divisiondebtors d ON g.debnr = d.debtor AND g.companycode = d.division
INNER JOIN perdat p ON g.datum BETWEEN p.bgdatum AND p.eddatum AND p.bkjrcode IN (2006,2005)
WHERE g.transsubtype IN ('K','C') AND g.transtype IN ('N','C','P') AND g.faktuurnr IS NOT NULL AND d.debcode = ' 901'
GROUP BY d.debcode,p.bkjrcode,g.periode

UNION
---------Select the Paid count------------------------------------------------------------------------
SELECT
s.debcode,
p.bkjrcode,
p.per_fin,
0,0,0,0,0,0,
COUNT(CASE p.bkjrcode WHEN 2006 THEN s.invoicenumber ELSE NULL END) AS Paid,
COUNT(CASE p.bkjrcode WHEN 2005 THEN s.invoicenumber ELSE NULL END) AS PaidLY
FROM (
SELECT d.debcode,w.invoicenumber,MAX(w.matchid) as matchid,MAX(s.valuedate) AS valuedate
FROM banktransactions w
INNER JOIN divisiondebtors d ON w.debtornumber = d.debtor AND w.bedrnr = d.division
INNER JOIN banktransactions s ON w.matchid = s.id
WHERE d.debcode = ' 901' AND w.matchid IS NOT NULL AND w.invoicenumber IS NOT NULL AND s.transactiontype = 'Z'
GROUP BY d.debcode,w.invoicenumber) AS s
INNER JOIN perdat p ON s.valuedate BETWEEN p.bgdatum AND p.eddatum AND p.bkjrcode IN (2006,2005)
INNER JOIN (
SELECT DISTINCT g1.faktuurnr
FROM gbkmut g1
INNER JOIN grtbk k1 on g1.reknr = k1.reknr AND k1.omzrek = 'D'
INNER JOIN divisiondebtors d1 ON g1.debnr = d1.debtor AND g1.companycode = d1.division
WHERE g1.transsubtype IN ('K','C') AND
g1.transtype IN ('N','C','P') AND
g1.faktuurnr IS NOT NULL AND
d1.debcode = ' 901')
AS F2 ON s.invoicenumber = F2.faktuurnr
GROUP BY s.debcode,p.bkjrcode,p.per_fin
UNION
SELECT ' 901', '0000', ' 1', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 2', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 3', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 4', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 5', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 6', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 7', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 8', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 9', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 10', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 11', 0, 0, 0, 0, 0, 0, 0, 0
UNION
SELECT ' 901', '0000', ' 12', 0, 0, 0, 0, 0, 0, 0, 0

) AS AAA (Debcode, FiscalYear, Period, Sales, SalesLY, Cogs, CogsLY, Invoices, InvoicesLY, Paid, PaidLY)
GROUP BY Debcode, Period


---------------------------------------------------------



Kevin Scheeler

 

[jbphmd]

Thank You for your Help - I think I will use a Stored Procedure to update the YTD sales from the OEHDRHST,OELINHST - I was wondering if anyone has that code?