Exdcel VBA to Verify Pricing

[Johnny48]

Hello,

I need to verify pricing before importing sales orders into accpac 6.0/MSSQL

I have the vba I need, however having trouble getting the correct SQL query to return rows with information needed by VBA procedure to verify pricing.

Fields needed to verify pricing:
ICPRICP.UNITPRICE,
ICPRICP.DPRICETYPE,
ICPRICP.PRICELIST,
ICPRICP.SALESTART,
ICPRICP.SALEEND,
ICPRIC.PRICESTART,
ICPRIC.PRICEEND,
ICCUPR.FIXPRICE -> IF CONTRACT PRICING EXIST RETURN
ICCUPR.STARTDATE -> IF CONTRACT PRICING EXIST RETURN
ICCUPR.USELOWEST -> IF CONTRACT PRICING EXIST RETURN
ICCUPR.EXPIRE -> IF CONTRACT PRICING EXIST RETURN

The below does not work...

SELECT     ICCUPR.FIXPRICE, ICPRICP.UNITPRICE, ICCUPR.STARTDATE, ICCUPR.USELOWEST, ICCUPR.EXPIRE, ICPRICP.DPRICETYPE, ICPRICP.PRICELIST, 
                      ICPRICP.SALESTART, ICPRICP.SALEEND, ICPRIC.PRICESTART, ICPRIC.PRICEEND, ARCSP.IDCUST, ARCSP.IDCUSTSHPT, ICPRICP.ITEMNO
FROM         ARCSP LEFT OUTER JOIN
                      ICCUPR ON ARCSP.IDCUST = ICCUPR.CUSTNO LEFT OUTER JOIN
                      ICPRICP ON ICPRICP.ITEMNO = ICCUPR.ITEMNO AND ARCSP.PRICLIST = ICPRICP.PRICELIST LEFT OUTER JOIN
                      ICPRIC ON ICPRIC.PRICELIST = ICCUPR.PRICELIST AND ICPRICP.ITEMNO = ICPRIC.ITEMNO AND ICPRICP.PRICELIST = ICPRIC.PRICELIST
WHERE     (ARCSP.IDCUST = '11647') AND (ICPRICP.ITEMNO = '104884')
ORDER BY ICPRICP.DPRICETYPE

Any help would be great !

 

[Johnny48]

Still testing...but the below seems to do it !:

SELECT ICPRICP.UNITPRICE, ICPRICP.DPRICETYPE, ICPRICP.PRICELIST, ICPRICP.SALESTART, ICPRICP.SALEEND, ICPRIC.PRICESTART, ICPRIC.PRICEEND, ARCSP.IDCUST, ARCSP.IDCUSTSHPT, ICPRICP.ITEMNO, ICCUPR.EXPIRE, ICCUPR.FIXPRICE, ICCUPR.STARTDATE, ICCUPR.USELOWEST
FROM ((ARCSP LEFT OUTER JOIN ICPRICP ON ARCSP.PRICLIST = ICPRICP.PRICELIST) LEFT OUTER JOIN ICPRIC ON (ICPRICP.ITEMNO = ICPRIC.ITEMNO) AND (ICPRICP.PRICELIST = ICPRIC.PRICELIST)) LEFT OUTER JOIN ICCUPR ON (ICPRICP.PRICELIST = ICCUPR.PRICELIST) AND (ICPRICP.ITEMNO = ICCUPR.ITEMNO) AND (ARCSP.IDCUST = ICCUPR.CUSTNO)
WHERE (((ARCSP.IDCUST)='22701') AND ((ARCSP.IDCUSTSHPT)='00011') AND ((ICPRICP.ITEMNO)='104994'))
ORDER BY ICPRICP.DPRICETYPE;

 

[DjangMan]

When you say that you're importing - are you importing using a macro (populating the fields through code) or are you importing using an XML template (to simulate File, Import)?

The reason I ask is if you're importing via VBA code then you can check the pricing by creating an order with the views that you never post. That way you can let Accpac tell you if the price is correct without having to create a complicated SQL query. Not that I want to stop you from creating that query but in 5.6 and later the logic for finding the unit price became much more complicated that in prior versions. Things to consider: sale price dates, contract pricing by category and by item, pricing by unit of measure and pricing by weight and customer discount levels. You've got some of those in your query - just need to account for the others. Perhaps some sub-selects...

 

[Johnny48]

Thanks DjangMan.

We import via CSV files, I use VBA/Excel and yes with convoluted logic to test for the various pricing combos...as our company has «fairly» simple costing I let VBA handle it...even sends HTML email to admins on every scanned batch !

The reason being:
1- EDI software we use exports csv formats only, so I get Excel to scan the csv's and price audit
2- any other way seemed more complicated for me, however in light of your comment...I will explore further this option

The VBA:
1- imports the «Order.csv» & «Order_Details.csv» from an EDI export routine (Leaving csv files un touched as opening with excel will change format and Accpac will not recognize!--csv files default to open with Excel..we're all creatures of habit)
2- Runs a pricing check that highlights and notes the anomalies and sends a templated HTML email to a list of admins with summary.

Cheers,
Johnny